Telegram bots are quite easy to build. They can be up and running within few minutes. But managing users is another challenge, that requires database. As bots need to do work intelligently by recognising who asked for what. The structure of database for bots derives from the API methods of Telegram.
The database needs to consist of tables including tables for users, chats, messages, inline query, edited message, callback query, shipping query, pre checkout info, poll, poll votes, request limiter and so on. Some of these may be omitted if ther is no need for them. Fore example, if your bot does not have a channel membership, you might not need polls, and if you don't have a merchanise on Telegram to sell, you may skip shipping and checkout tables and similarly, if your bot does not work inline, inline query table can be skipped.
However, we will try to look into each of these one by one.
user Table
The most important table would be the database of users:
CREATE TABLE IF NOT EXISTS `user` (
`id` bigint COMMENT 'Unique identifier for this user or bot',
`is_bot` tinyint(1) DEFAULT 0 COMMENT 'True, if this user is a bot',
`first_name` CHAR(255) NOT NULL DEFAULT '' COMMENT 'User''s or bot''s first name',
`last_name` CHAR(255) DEFAULT NULL COMMENT 'User''s or bot''s last name',
`username` CHAR(191) DEFAULT NULL COMMENT 'User''s or bot''s username',
`language_code` CHAR(10) DEFAULT NULL COMMENT 'IETF language tag of the user''s language',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date update',
PRIMARY KEY (`id`),
KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;It is probably clear from the comments of the fields, what they are meant to be. This table will store the details of users that are interacting with your bot.
chat Table
CREATE TABLE IF NOT EXISTS `chat` (
`id` bigint COMMENT 'Unique identifier for this chat',
`type` ENUM('private', 'group', 'supergroup', 'channel') NOT NULL COMMENT 'Type of chat, can be either private, group, supergroup or channel',
`title` CHAR(255) DEFAULT '' COMMENT 'Title, for supergroups, channels and group chats',
`username` CHAR(255) DEFAULT NULL COMMENT 'Username, for private chats, supergroups and channels if available',
`first_name` CHAR(255) DEFAULT NULL COMMENT 'First name of the other party in a private chat',
`last_name` CHAR(255) DEFAULT NULL COMMENT 'Last name of the other party in a private chat',
`all_members_are_administrators` tinyint(1) DEFAULT 0 COMMENT 'True if a all members of this group are admins',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date update',
`old_id` bigint DEFAULT NULL COMMENT 'Unique chat identifier, this is filled when a group is converted to a supergroup',
PRIMARY KEY (`id`),
KEY `old_id` (`old_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;Now, this table will store the details about active chats, their types and few other meta data.
user_chat Table
CREATE TABLE IF NOT EXISTS `user_chat` (
`user_id` bigint COMMENT 'Unique user identifier',
`chat_id` bigint COMMENT 'Unique user or chat identifier',
PRIMARY KEY (`user_id`, `chat_id`),
FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`chat_id`) REFERENCES `chat` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;This table contains the mapping of user with respective chats. This is needed separately, due to the reason that a user might block your bot for some reasons and later turn up and start using the bot again. And for each time a new chat is started, there will be multiple chats. To avoid confusion, this table will store the mapping of latest chat with the user that is active.
message Table
CREATE TABLE IF NOT EXISTS `message` (
`chat_id` bigint COMMENT 'Unique chat identifier',
`sender_chat_id` bigint COMMENT 'Sender of the message, sent on behalf of a chat',
`id` bigint UNSIGNED COMMENT 'Unique message identifier',
`user_id` bigint NULL COMMENT 'Unique user identifier',
`date` timestamp NULL DEFAULT NULL COMMENT 'Date the message was sent in timestamp format',
`forward_from` bigint NULL DEFAULT NULL COMMENT 'Unique user identifier, sender of the original message',
`forward_from_chat` bigint NULL DEFAULT NULL COMMENT 'Unique chat identifier, chat the original message belongs to',
`forward_from_message_id` bigint NULL DEFAULT NULL COMMENT 'Unique chat identifier of the original message in the channel',
`forward_signature` TEXT NULL DEFAULT NULL COMMENT 'For messages forwarded from channels, signature of the post author if present',
`forward_sender_name` TEXT NULL DEFAULT NULL COMMENT 'Sender''s name for messages forwarded from users who disallow adding a link to their account in forwarded messages',
`forward_date` timestamp NULL DEFAULT NULL COMMENT 'date the original message was sent in timestamp format',
`reply_to_chat` bigint NULL DEFAULT NULL COMMENT 'Unique chat identifier',
`reply_to_message` bigint UNSIGNED DEFAULT NULL COMMENT 'Message that this message is reply to',
`via_bot` bigint NULL DEFAULT NULL COMMENT 'Optional. Bot through which the message was sent',
`edit_date` timestamp NULL DEFAULT NULL COMMENT 'Date the message was last edited in Unix time',
`media_group_id` TEXT COMMENT 'The unique identifier of a media message group this message belongs to',
`author_signature` TEXT COMMENT 'Signature of the post author for messages in channels',
`text` TEXT COMMENT 'For text messages, the actual UTF-8 text of the message max message length 4096 char utf8mb4',
`entities` TEXT COMMENT 'For text messages, special entities like usernames, URLs, bot commands, etc. that appear in the text',
`caption_entities` TEXT COMMENT 'For messages with a caption, special entities like usernames, URLs, bot commands, etc. that appear in the caption',
`audio` TEXT COMMENT 'Audio object. Message is an audio file, information about the file',
`document` TEXT COMMENT 'Document object. Message is a general file, information about the file',
`animation` TEXT COMMENT 'Message is an animation, information about the animation',
`game` TEXT COMMENT 'Game object. Message is a game, information about the game',
`photo` TEXT COMMENT 'Array of PhotoSize objects. Message is a photo, available sizes of the photo',
`sticker` TEXT COMMENT 'Sticker object. Message is a sticker, information about the sticker',
`video` TEXT COMMENT 'Video object. Message is a video, information about the video',
`voice` TEXT COMMENT 'Voice Object. Message is a Voice, information about the Voice',
`video_note` TEXT COMMENT 'VoiceNote Object. Message is a Video Note, information about the Video Note',
`caption` TEXT COMMENT 'For message with caption, the actual UTF-8 text of the caption',
`contact` TEXT COMMENT 'Contact object. Message is a shared contact, information about the contact',
`location` TEXT COMMENT 'Location object. Message is a shared location, information about the location',
`venue` TEXT COMMENT 'Venue object. Message is a Venue, information about the Venue',
`poll` TEXT COMMENT 'Poll object. Message is a native poll, information about the poll',
`dice` TEXT COMMENT 'Message is a dice with random value from 1 to 6',
`new_chat_members` TEXT COMMENT 'List of unique user identifiers, new member(s) were added to the group, information about them (one of these members may be the bot itself)',
`left_chat_member` bigint NULL DEFAULT NULL COMMENT 'Unique user identifier, a member was removed from the group, information about them (this member may be the bot itself)',
`new_chat_title` CHAR(255) DEFAULT NULL COMMENT 'A chat title was changed to this value',
`new_chat_photo` TEXT COMMENT 'Array of PhotoSize objects. A chat photo was change to this value',
`delete_chat_photo` tinyint(1) DEFAULT 0 COMMENT 'Informs that the chat photo was deleted',
`group_chat_created` tinyint(1) DEFAULT 0 COMMENT 'Informs that the group has been created',
`supergroup_chat_created` tinyint(1) DEFAULT 0 COMMENT 'Informs that the supergroup has been created',
`channel_chat_created` tinyint(1) DEFAULT 0 COMMENT 'Informs that the channel chat has been created',
`message_auto_delete_timer_changed` TEXT COMMENT 'MessageAutoDeleteTimerChanged object. Message is a service message: auto-delete timer settings changed in the chat',
`migrate_to_chat_id` bigint NULL DEFAULT NULL COMMENT 'Migrate to chat identifier. The group has been migrated to a supergroup with the specified identifier',
`migrate_from_chat_id` bigint NULL DEFAULT NULL COMMENT 'Migrate from chat identifier. The supergroup has been migrated from a group with the specified identifier',
`pinned_message` TEXT NULL COMMENT 'Message object. Specified message was pinned',
`invoice` TEXT NULL COMMENT 'Message is an invoice for a payment, information about the invoice',
`successful_payment` TEXT NULL COMMENT 'Message is a service message about a successful payment, information about the payment',
`connected_website` TEXT NULL COMMENT 'The domain name of the website on which the user has logged in.',
`passport_data` TEXT NULL COMMENT 'Telegram Passport data',
`proximity_alert_triggered` TEXT NULL COMMENT 'Service message. A user in the chat triggered another user''s proximity alert while sharing Live Location.',
`voice_chat_started` TEXT COMMENT 'VoiceChatStarted object. Message is a service message: voice chat started',
`voice_chat_ended` TEXT COMMENT 'VoiceChatEnded object. Message is a service message: voice chat ended',
`voice_chat_participants_invited` TEXT COMMENT 'VoiceChatParticipantsInvited object. Message is a service message: new participants invited to a voice chat',
`reply_markup` TEXT NULL COMMENT 'Inline keyboard attached to the message',
PRIMARY KEY (`chat_id`, `id`),
KEY `user_id` (`user_id`),
KEY `forward_from` (`forward_from`),
KEY `forward_from_chat` (`forward_from_chat`),
KEY `reply_to_chat` (`reply_to_chat`),
KEY `reply_to_message` (`reply_to_message`),
KEY `via_bot` (`via_bot`),
KEY `left_chat_member` (`left_chat_member`),
KEY `migrate_from_chat_id` (`migrate_from_chat_id`),
KEY `migrate_to_chat_id` (`migrate_to_chat_id`),
FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
FOREIGN KEY (`chat_id`) REFERENCES `chat` (`id`),
FOREIGN KEY (`forward_from`) REFERENCES `user` (`id`),
FOREIGN KEY (`forward_from_chat`) REFERENCES `chat` (`id`),
FOREIGN KEY (`reply_to_chat`, `reply_to_message`) REFERENCES `message` (`chat_id`, `id`),
FOREIGN KEY (`via_bot`) REFERENCES `user` (`id`),
FOREIGN KEY (`left_chat_member`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;It sure seems to be a quite big table. It will store all the messages exchanged between the user and the bot. (Do you remember that almost every API call resulted in Message as output: those will be stored in this table).
edited_message Table
CREATE TABLE IF NOT EXISTS `edited_message` (
`id` bigint UNSIGNED AUTO_INCREMENT COMMENT 'Unique identifier for this entry',
`chat_id` bigint COMMENT 'Unique chat identifier',
`message_id` bigint UNSIGNED COMMENT 'Unique message identifier',
`user_id` bigint NULL COMMENT 'Unique user identifier',
`edit_date` timestamp NULL DEFAULT NULL COMMENT 'Date the message was edited in timestamp format',
`text` TEXT COMMENT 'For text messages, the actual UTF-8 text of the message max message length 4096 char utf8',
`entities` TEXT COMMENT 'For text messages, special entities like usernames, URLs, bot commands, etc. that appear in the text',
`caption` TEXT COMMENT 'For message with caption, the actual UTF-8 text of the caption',
PRIMARY KEY (`id`),
KEY `chat_id` (`chat_id`),
KEY `message_id` (`message_id`),
KEY `user_id` (`user_id`),
FOREIGN KEY (`chat_id`) REFERENCES `chat` (`id`),
FOREIGN KEY (`chat_id`, `message_id`) REFERENCES `message` (`chat_id`, `id`),
FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;Telegram allows user to edit the messages and also it allows bots to do so. This table will store the edit history of the messages.
callback_query Table
CREATE TABLE IF NOT EXISTS `callback_query` (
`id` bigint UNSIGNED COMMENT 'Unique identifier for this query',
`user_id` bigint NULL COMMENT 'Unique user identifier',
`chat_id` bigint NULL COMMENT 'Unique chat identifier',
`message_id` bigint UNSIGNED COMMENT 'Unique message identifier',
`inline_message_id` CHAR(255) NULL DEFAULT NULL COMMENT 'Identifier of the message sent via the bot in inline mode, that originated the query',
`chat_instance` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Global identifier, uniquely corresponding to the chat to which the message with the callback button was sent',
`data` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Data associated with the callback button',
`game_short_name` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Short name of a Game to be returned, serves as the unique identifier for the game',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `chat_id` (`chat_id`),
KEY `message_id` (`message_id`),
FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
FOREIGN KEY (`chat_id`, `message_id`) REFERENCES `message` (`chat_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;This table will store the call back queries that occur when user presses the buttons in Telegram interface (e.g. custom Keyboard buttons).
inline_query Table
CREATE TABLE IF NOT EXISTS `inline_query` (
`id` bigint UNSIGNED COMMENT 'Unique identifier for this query',
`user_id` bigint NULL COMMENT 'Unique user identifier',
`location` CHAR(255) NULL DEFAULT NULL COMMENT 'Location of the user',
`query` TEXT NOT NULL COMMENT 'Text of the query',
`offset` CHAR(255) NULL DEFAULT NULL COMMENT 'Offset of the result',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;This table will store the queries received when bot has an active inline mode. This mode allows user to perform search within other chats, which fetches results from your bot and then they get posted to that chat.
chosen_inline_result Table
CREATE TABLE IF NOT EXISTS `chosen_inline_result` (
`id` bigint UNSIGNED AUTO_INCREMENT COMMENT 'Unique identifier for this entry',
`result_id` CHAR(255) NOT NULL DEFAULT '' COMMENT 'The unique identifier for the result that was chosen',
`user_id` bigint NULL COMMENT 'The user that chose the result',
`location` CHAR(255) NULL DEFAULT NULL COMMENT 'Sender location, only for bots that require user location',
`inline_message_id` CHAR(255) NULL DEFAULT NULL COMMENT 'Identifier of the sent inline message',
`query` TEXT NOT NULL COMMENT 'The query that was used to obtain the result',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;When a user chooses a particular inline result, it sends back an inline query to your bot for further actions. You can use this information for analytics or any other purpose that suit your needs.
poll Table
CREATE TABLE IF NOT EXISTS `poll` (
`id` bigint UNSIGNED COMMENT 'Unique poll identifier',
`question` text NOT NULL COMMENT 'Poll question',
`options` text NOT NULL COMMENT 'List of poll options',
`total_voter_count` int UNSIGNED COMMENT 'Total number of users that voted in the poll',
`is_closed` tinyint(1) DEFAULT 0 COMMENT 'True, if the poll is closed',
`is_anonymous` tinyint(1) DEFAULT 1 COMMENT 'True, if the poll is anonymous',
`type` char(255) COMMENT 'Poll type, currently can be “regular” or “quiz”',
`allows_multiple_answers` tinyint(1) DEFAULT 0 COMMENT 'True, if the poll allows multiple answers',
`correct_option_id` int UNSIGNED COMMENT '0-based identifier of the correct answer option. Available only for polls in the quiz mode, which are closed, or was sent (not forwarded) by the bot or to the private chat with the bot.',
`explanation` varchar(255) DEFAULT NULL COMMENT 'Text that is shown when a user chooses an incorrect answer or taps on the lamp icon in a quiz-style poll, 0-200 characters',
`explanation_entities` text DEFAULT NULL COMMENT 'Special entities like usernames, URLs, bot commands, etc. that appear in the explanation',
`open_period` int UNSIGNED DEFAULT NULL COMMENT 'Amount of time in seconds the poll will be active after creation',
`close_date` timestamp NULL DEFAULT NULL COMMENT 'Point in time (Unix timestamp) when the poll will be automatically closed',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;Polls are unique to Telegram that allow you to run polls (and even quizzes) in Telegram groups and channels.
poll_answer Table
CREATE TABLE IF NOT EXISTS `poll_answer` (
`poll_id` bigint UNSIGNED COMMENT 'Unique poll identifier',
`user_id` bigint NOT NULL COMMENT 'The user, who changed the answer to the poll',
`option_ids` text NOT NULL COMMENT '0-based identifiers of answer options, chosen by the user. May be empty if the user retracted their vote.',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
PRIMARY KEY (`poll_id`, `user_id`),
FOREIGN KEY (`poll_id`) REFERENCES `poll` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;This table will store the responses for polls created by the bot.
telegram_update Table
CREATE TABLE IF NOT EXISTS `telegram_update` (
`id` bigint UNSIGNED COMMENT 'Update''s unique identifier',
`chat_id` bigint NULL DEFAULT NULL COMMENT 'Unique chat identifier',
`message_id` bigint UNSIGNED DEFAULT NULL COMMENT 'New incoming message of any kind - text, photo, sticker, etc.',
`edited_message_id` bigint UNSIGNED DEFAULT NULL COMMENT 'New version of a message that is known to the bot and was edited',
`channel_post_id` bigint UNSIGNED DEFAULT NULL COMMENT 'New incoming channel post of any kind - text, photo, sticker, etc.',
`edited_channel_post_id` bigint UNSIGNED DEFAULT NULL COMMENT 'New version of a channel post that is known to the bot and was edited',
`inline_query_id` bigint UNSIGNED DEFAULT NULL COMMENT 'New incoming inline query',
`chosen_inline_result_id` bigint UNSIGNED DEFAULT NULL COMMENT 'The result of an inline query that was chosen by a user and sent to their chat partner',
`callback_query_id` bigint UNSIGNED DEFAULT NULL COMMENT 'New incoming callback query',
`shipping_query_id` bigint UNSIGNED DEFAULT NULL COMMENT 'New incoming shipping query. Only for invoices with flexible price',
`pre_checkout_query_id` bigint UNSIGNED DEFAULT NULL COMMENT 'New incoming pre-checkout query. Contains full information about checkout',
`poll_id` bigint UNSIGNED DEFAULT NULL COMMENT 'New poll state. Bots receive only updates about polls, which are sent or stopped by the bot',
`poll_answer_poll_id` bigint UNSIGNED DEFAULT NULL COMMENT 'A user changed their answer in a non-anonymous poll. Bots receive new votes only in polls that were sent by the bot itself.',
`my_chat_member_updated_id` BIGINT UNSIGNED NULL COMMENT 'The bot''s chat member status was updated in a chat. For private chats, this update is received only when the bot is blocked or unblocked by the user.',
`chat_member_updated_id` BIGINT UNSIGNED NULL COMMENT 'A chat member''s status was updated in a chat. The bot must be an administrator in the chat and must explicitly specify “chat_member” in the list of allowed_updates to receive these updates.',
PRIMARY KEY (`id`),
KEY `message_id` (`message_id`),
KEY `chat_message_id` (`chat_id`, `message_id`),
KEY `edited_message_id` (`edited_message_id`),
KEY `channel_post_id` (`channel_post_id`),
KEY `edited_channel_post_id` (`edited_channel_post_id`),
KEY `inline_query_id` (`inline_query_id`),
KEY `chosen_inline_result_id` (`chosen_inline_result_id`),
KEY `callback_query_id` (`callback_query_id`),
KEY `shipping_query_id` (`shipping_query_id`),
KEY `pre_checkout_query_id` (`pre_checkout_query_id`),
KEY `poll_id` (`poll_id`),
KEY `poll_answer_poll_id` (`poll_answer_poll_id`),
KEY `my_chat_member_updated_id` (`my_chat_member_updated_id`),
KEY `chat_member_updated_id` (`chat_member_updated_id`),
FOREIGN KEY (`chat_id`, `message_id`) REFERENCES `message` (`chat_id`, `id`),
FOREIGN KEY (`edited_message_id`) REFERENCES `edited_message` (`id`),
FOREIGN KEY (`chat_id`, `channel_post_id`) REFERENCES `message` (`chat_id`, `id`),
FOREIGN KEY (`edited_channel_post_id`) REFERENCES `edited_message` (`id`),
FOREIGN KEY (`inline_query_id`) REFERENCES `inline_query` (`id`),
FOREIGN KEY (`chosen_inline_result_id`) REFERENCES `chosen_inline_result` (`id`),
FOREIGN KEY (`callback_query_id`) REFERENCES `callback_query` (`id`),
FOREIGN KEY (`shipping_query_id`) REFERENCES `shipping_query` (`id`),
FOREIGN KEY (`pre_checkout_query_id`) REFERENCES `pre_checkout_query` (`id`),
FOREIGN KEY (`poll_id`) REFERENCES `poll` (`id`),
FOREIGN KEY (`poll_answer_poll_id`) REFERENCES `poll_answer` (`poll_id`),
FOREIGN KEY (`my_chat_member_updated_id`) REFERENCES `chat_member_updated` (`id`),
FOREIGN KEY (`chat_member_updated_id`) REFERENCES `chat_member_updated` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;Another big table! This will store the details of updates received on your webhook / fetched using long polling.
shipping_query Table
CREATE TABLE IF NOT EXISTS `shipping_query` (
`id` bigint UNSIGNED COMMENT 'Unique query identifier',
`user_id` bigint COMMENT 'User who sent the query',
`invoice_payload` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Bot specified invoice payload',
`shipping_address` CHAR(255) NOT NULL DEFAULT '' COMMENT 'User specified shipping address',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;If you have a merchanidse to sell on Telegram, then you can set up your shop on Telegram and accept payments using Telegram Bots. This table stores shipping details for a customer.
pre_checkout_query Table
CREATE TABLE IF NOT EXISTS `pre_checkout_query` (
`id` bigint UNSIGNED COMMENT 'Unique query identifier',
`user_id` bigint COMMENT 'User who sent the query',
`currency` CHAR(3) COMMENT 'Three-letter ISO 4217 currency code',
`total_amount` bigint COMMENT 'Total price in the smallest units of the currency',
`invoice_payload` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Bot specified invoice payload',
`shipping_option_id` CHAR(255) NULL COMMENT 'Identifier of the shipping option chosen by the user',
`order_info` TEXT NULL COMMENT 'Order info provided by the user',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;This table stores the details about invoice details including amount, date, link to shipping information and details about order information.
request_limiter Table
CREATE TABLE IF NOT EXISTS `request_limiter` (
`id` bigint UNSIGNED AUTO_INCREMENT COMMENT 'Unique identifier for this entry',
`chat_id` char(255) NULL DEFAULT NULL COMMENT 'Unique chat identifier',
`inline_message_id` char(255) NULL DEFAULT NULL COMMENT 'Identifier of the sent inline message',
`method` char(255) DEFAULT NULL COMMENT 'Request method',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;Telegram has set up limits for all bots that should not be exhausted. You need to be careful when sending messages to Telegram Servers, as exhausting this limit frequently may cause your bot to be blocked.
These limits are not too harsh, like this will allow your bot to send 30 messages per second. These limits can be checked from official Telegram page here.
You may need additional tables to store extra information required for your use case like saving preferences for users.
The basic logic for your bot will be:
- As soon as you get a message from user which starts with
/start(keeping in mind deep linking), you may add this user to the database. And also add this chat and link it against the given user. - Now, when the user sends you messages, it will arrive as update to your bot, this update will be stored in
telegram_updatetable. - Also, when you send messages to users, the replied object may be stored at
messagetable. - You may also use inline results to be listed in the respective tables and the results chosen from them.
- The poll results and polls itself may be stored in respective tables.
Now, that you logic for bot is ready, you may want to change a few settings for your bot. This can be done by talking with BotFather.
These articles are a part of Telegram bot series and will be using PHP as their coding language. But if you read through the code, you can easily adapt the code to be used in any language.