Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 37 ยท State and Storage

SQLite Storage for Bots

Store warnings, settings and balances for a Discord or Telegram bot in SQLite with node:sqlite: schema, upserts, transactions, migrations and backups.

When state must survive restarts, a database is the answer. For a bot that runs as one process, SQLite is an excellent first choice: it is a single file, needs no server, handles thousands of writes per second, and ships inside Node.js as the built-in node:sqlite module. No npm package is needed.

The examples here use an in-memory database (":memory:") so they run anywhere. In your own bot, replace that with a file path such as ./data/bot.db.

Opening a database and creating tables

Node.js Create a schema

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Store IDs as TEXT. That avoids the 64-bit precision problem from the previous lesson and works for both platforms in one table.

Prepared statements

Always pass values as parameters (? or :name), never by gluing strings together. A username like x'); DROP TABLE users;-- is harmless as a parameter and catastrophic in a string. Prepared statements are also faster when reused.

Node.js Insert and query with parameters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

run() gives you changes and lastInsertRowid, get() returns one row, all() returns an array. Rows come back as plain objects.

Upserts: insert or update in one step

Settings and counters need "create if missing, otherwise change". SQLite's INSERT ... ON CONFLICT DO UPDATE does that atomically, with no race between a read and a write.

Node.js XP counter with an upsert

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The composite primary key (guild_id, user_id) is what makes the conflict detection work, and it is also the index that makes leaderboards fast.

Transactions

When several statements must succeed together, such as moving coins from one user to another, wrap them in a transaction. If anything throws, roll everything back.

Node.js A safe coin transfer

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The CHECK (coins >= 0) rule is the real guard. The database refuses the second transfer, the code rolls back, and neither balance changes. Let the database enforce rules that must never be broken.

Settings as key-value rows

Per-guild settings (prefix, language, log channel) fit a small table. Read them through one helper with defaults.

NODE
const DEFAULTS = { prefix: "!", language: "en", logChannel: null };

function getSettings(db, guildId) {
  const row = db.prepare("SELECT json FROM guild_settings WHERE guild_id = ?").get(guildId);
  return { ...DEFAULTS, ...(row ? JSON.parse(row.json) : {}) };
}

Storing a JSON column keeps the schema stable when you add a new setting. See per-user and per-guild settings for the full pattern with caching.

Migrations

Your schema will change. Keep a version number in the database with PRAGMA user_version and apply numbered steps in order.

Node.js A minimal migration runner

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Never edit a migration that has already run in production; add a new one.

Operating notes

  • Turn on WAL mode (PRAGMA journal_mode = WAL) for a file database so reads do not block writes.
  • Back up with VACUUM INTO 'backup.db' or by copying the file while the bot is stopped. Copying a live database file can capture a half-written state.
  • One process only. Two bot processes writing the same SQLite file works but gets slow and error-prone; if you outgrow it, move to PostgreSQL.
  • In this site's runner the database lives only for the run. On a real server, mount the data folder on a volume (see hosting and process management).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why use parameters (?) instead of building SQL strings?

    Values bound as parameters are never parsed as SQL, so hostile input stays data. Prepared statements can also be reused.
  2. 2. What does ON CONFLICT DO UPDATE give you for a points counter?

    The insert and the update happen as one atomic statement, so two concurrent increments cannot overwrite each other.
  3. 3. In the transfer example, why did the second transfer leave both balances unchanged?

    The debit violated CHECK (coins >= 0), an error was thrown, and ROLLBACK discarded the partial work.
  4. 4. What is PRAGMA user_version commonly used for?

    It is a free integer in the database header, ideal for a schema version number.

Summary

  • node:sqlite gives a zero-dependency database; store IDs as text.
  • Use prepared statements with parameters, never string concatenation.
  • Upserts with RETURNING replace read-modify-write races.
  • Wrap multi-step changes in transactions and let constraints guard the rules.
  • Version the schema with numbered migrations and PRAGMA user_version.

Next: Per-user and per-guild settings