3. The code
A small notes bot: /note buy milk saves a note, /notes shows your last five.
package.json
{
"name": "my-grammy-bot",
"private": true,
"main": "index.js",
"dependencies": {
"grammy": "^1.46.0",
"mysql2": "^3.24.6"
}
}
index.js
const { Bot } = require("grammy");
const mysql = require("mysql2/promise");
// One pool for the whole bot. The values come from the Environment pane.
const pool = mysql.createPool({
host: process.env.DB_HOST,
port: Number(process.env.DB_PORT),
database: process.env.DB_NAME,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
connectionLimit: 5,
});
const bot = new Bot(process.env.TELEGRAM_BOT_TOKEN);
bot.command("note", async (ctx) => {
const text = ctx.match.trim();
if (!text) return ctx.reply("Write your note after the command, for example: /note buy milk");
if (text.length > 500) return ctx.reply("A note can be up to 500 characters.");
await pool.execute("INSERT INTO notes (user_id, text) VALUES (?, ?)", [ctx.from.id, text]);
await ctx.reply("Saved. Send /notes to see your last five.");
});
bot.command("notes", async (ctx) => {
const [rows] = await pool.execute(
"SELECT text FROM notes WHERE user_id = ? ORDER BY id DESC LIMIT 5",
[ctx.from.id],
);
if (rows.length === 0) return ctx.reply("You have no notes yet.");
await ctx.reply(rows.map((row) => `- ${row.text}`).join("\n"));
});
// Without this, an error in one command stops the whole bot.
bot.catch((err) => {
console.error(`Error while handling update ${err.ctx.update.update_id}:`, err.error);
});
async function main() {
await pool.query(
"CREATE TABLE IF NOT EXISTS notes (" +
"id INT AUTO_INCREMENT PRIMARY KEY, " +
"user_id BIGINT NOT NULL, " +
"text VARCHAR(500) NOT NULL, " +
"created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
"KEY user_id (user_id))",
);
console.log("Database ready");
// bot.start() only returns when the bot stops, so the ready line goes in onStart.
await bot.start({ onStart: (me) => console.log(`Logged in as @${me.username}`) });
}
main().catch((err) => {
console.error(err);
process.exit(1);
});
What it does: main() creates the notes table if it is missing and prints Database ready before bot.start() asks Telegram for messages. The pool is created once, at the top of the file, and every command uses it: pool.execute() borrows a connection, runs the query and gives the connection back. ctx.match is the text after the command: grammY removes the spaces in front of it and keeps the rest as typed, line breaks included. bot.catch() matters here: grammY's documentation says that without an error handler, an error in a handler stops a bot started with bot.start(), and a database error is exactly that kind of error.
The ? placeholders send the note to the database separately from the SQL, so a note such as '); DROP TABLE notes; -- is stored as plain text. In our test it was saved as row 2 and the table stayed. Never build the SQL with a template string.
Store Telegram IDs as BIGINT
Telegram's Bot API documentation says a user or chat ID "may have more than 32 significant bits" but has at most 52, so a 64-bit integer is safe. Its page on IDs gives the ranges: user IDs go up to 1,099,511,627,775, while group, supergroup and channel IDs are negative. Supergroups and channels run from -1,000,000,000,001 to -1,997,852,516,352 (for example -1001234567890). MySQL's INT stops at 2,147,483,647. In our test, with the same strict SQL mode our database server uses, saving the user ID 7,000,000,001 in an INT column failed. mysql2 gave the error code ER_WARN_DATA_OUT_OF_RANGE (errno 1264) with this message:
Out of range value for column 'user_id' at row 1
A BIGINT column stored it, and -1001234567890 too. Use plain BIGINT, not BIGINT UNSIGNED, so chat IDs fit as well.
4. Run it
- Upload
index.js and package.json in Files. Do not upload node_modules.
- Press Start and open Console. The start installs the packages from
package.json, then shows [PlayNHost] Starting your bot, Database ready and Logged in as @your_bot. That last line turns the status on Overview to Online in Telegram.
- In Telegram, send your bot
/note buy milk, then /notes. To look at the rows, open the database's page and click Open in phpMyAdmin.
In our test the stand-in for Telegram sent these commands and got these answers:
| Sent | Answer |
|---|
/note buy milk | Saved. Send /notes to see your last five. |
/note | Write your note after the command, for example: /note buy milk |
/note and 501 characters | A note can be up to 500 characters. |
/notes | The saved notes, newest first |
/notes from a second user | You have no notes yet. |
Many messages at once
grammY's documentation says bot.start() processes all updates sequentially: one command finishes before the next one starts. In our test the bot held 1 open connection after answering the 8 commands. connectionLimit: 5 matters once your code runs queries in parallel, or if you move to grammY runner, which handles updates concurrently: the pool never opens more than 5 connections, and an extra query waits for a free one.
A failed query does not stop the bot
In our test we deleted the table while the bot was running and sent /notes. bot.catch() printed Error while handling update 500001: Error: Table '...notes' doesn't exist with code: 'ER_NO_SUCH_TABLE', the bot sent no answer for that command, and it kept running: once the table was back, the next /note was saved. The person who sent the command sees nothing, so if a query can fail, wrap it in try/catch in the handler and answer with a short message.
A quiet night does not break the pool
Our database server closes a connection that has been idle for 8 hours. A bot that gets no messages for that long, overnight for example, finds its pooled connections closed. mysql2's pool deals with that by itself. We tested it with the idle limit lowered to 5 seconds: after 7 quiet seconds, the next /note was saved with no error.
Limits that matter for your pool
- 20 connections at once per database user. Every process that uses the same login counts. Connection 21 is refused with
ER_USER_LIMIT_REACHED. In our test a pool with connectionLimit: 30 failed with that error when 30 queries ran at once. A pool of 5 keeps the bot well under it.
- A query that runs longer than 120 seconds is stopped. MariaDB ends it with error 1969,
Query execution was interrupted (max_statement_time exceeded). In an earlier test of ours, on an account set up the same way, that came after 120.03 seconds. A bot's queries normally take milliseconds, so this only bites a runaway query.
- 500 MB per database. The Databases page shows how much is used. At 500 MB, writes are stopped until you delete rows.
- Server backups do not include the database. Export it from phpMyAdmin from time to time.
Errors and what they mean
These are the error codes mysql2 gave in our test:
ER_ACCESS_DENIED_ERROR ... Access denied for user: wrong DB_USER or DB_PASSWORD. After Reset password on the Connection card the old password stops working at once: replace DB_PASSWORD and restart the bot.ECONNREFUSED: nothing accepted the connection at that host and port. Check DB_HOST and DB_PORT. A missing DB_HOST also ends here, because mysql2 then connects to localhost, where no database runs: the variable is missing from Environment, or the bot was not restarted after adding it.ENOTFOUND: DB_HOST is misspelled, so the name does not exist.ETIMEDOUT: nothing answered in time. Whatever the message, a connection from your own computer fails: the database only accepts connections from PlayNHost servers and phpMyAdmin.ER_USER_LIMIT_REACHED ... has exceeded the 'max_user_connections' resource (current value: 20): too many connections open, see the limits above. For example a second copy of the bot, or a pool created for each message.ER_WARN_DATA_OUT_OF_RANGE (errno 1264, Out of range value for column 'user_id' at row 1): an ID column is INT. Change it to BIGINT.ER_NO_SUCH_TABLE (errno 1146): the table is missing. The code above creates it at start; if you made it by hand, check its name.
A wrong token shows up after the database part. With our fake token against the real Telegram, the bot printed Database ready, then stopped after about a quarter of a second with GrammyError: Call to 'deleteWebhook' failed! (401: Unauthorized). Copy the token again from @BotFather and paste it on the Bot page, pane Telegram. More Telegram errors: Fix Telegram bot 409 Conflict.
How much memory it uses
In our test the running bot used 67.6 MB of memory (resident) after answering the 8 commands, with its pool open, in a container capped at 512 MB like our smallest bot plan. In our earlier hosting test, grammY alone measured 60 MB (that bot stopped at a refused token, before handling any message), so the difference, about 8 MB, covers mysql2, the pool and the handled commands. Installing grammY and mysql2 with npm from nothing peaked at 140 MB, file cache included.
Same thing with another library
Need a server for your Telegram bot with a database included? See the Telegram bot plans.
