Evolve your bot's database without losing data

Your bot ships with a database schema, and then it grows: a leveling bot wants a level column it didn't have on day one; a new feature needs a whole new table. Changing a live schema by hand is where data goes to die — you re-run a CREATE, it crashes; you add a column, it's already there; you clear the file to "start fresh," and everyone's XP is gone. The professional fix is versioned migrations, and SQLite has exactly the counter you need built in.

The problem with doing it by hand

CREATE TABLE IF NOT EXISTS is fine for version one — it makes the table if it's missing and no-ops if it's there. But it can't change an existing table. And the obvious next move breaks:

• Run ALTER TABLE users ADD COLUMN level ... on startup and it works once — then crashes with duplicate column name on the next restart.
• Skip the IF NOT EXISTS and it crashes with table already exists.

You need each change to run exactly once, in order, on every install — a brand-new empty database and one that's three versions behind. That's what a migration runner does.

The idea: user_version

SQLite keeps a private integer in every database file, PRAGMA user_version, which starts at 0 and is yours to use. Treat it as "how many migrations this database has already run." Your code carries an ordered list of migrations; on startup it runs the ones whose number is higher than the stored version, then writes the new number back. A database that's up to date runs nothing.

The migration runner

This is the whole engine — verified end to end on Falix's Node.js image:

const Database = require('better-sqlite3');
const db = new Database('bot.db');
db.pragma('journal_mode = WAL');

// Each function upgrades the schema by ONE version. Index 0 -> version 1, etc.
const migrations = [
  // v1: the original schema
  (d) => d.exec(`CREATE TABLE users (user_id TEXT PRIMARY KEY, xp INTEGER NOT NULL DEFAULT 0)`),

  // v2: a new table, added in a later release
  (d) => d.exec(`CREATE TABLE guild_settings (guild_id TEXT PRIMARY KEY, prefix TEXT NOT NULL DEFAULT '!')`),

  // v3: add a column AND backfill it from existing data
  (d) => {
    d.exec(`ALTER TABLE users ADD COLUMN level INTEGER NOT NULL DEFAULT 1`);
    d.exec(`UPDATE users SET level = 1 + (xp / 100)`);
  },
];

function migrate(d) {
  const current = d.pragma('user_version', { simple: true });
  for (let v = current; v < migrations.length; v++) {
    const step = d.transaction(() => {
      migrations[v](d);
      d.pragma(`user_version = ${v + 1}`);
    });
    step();               // runs the change and the version bump together
    console.log(`applied migration -> v${v + 1}`);
  }
  return d.pragma('user_version', { simple: true });
}

migrate(db);

Call migrate(db) once at startup, before your bot handles anything. On a fresh database it runs all three and lands at version 3. On a database already at version 2, it runs only v3. On one that's current, it runs nothing and prints nothing.

Why the transaction matters

Each migration and its version bump run inside db.transaction(...), so they commit together or not at all. If the process is killed mid-migration — an out-of-memory kill, a timer expiry — SQLite rolls the step back, and you never end up half-migrated with a bumped version. The next start simply retries that step cleanly.

🎯 Good to know: This is exactly the behavior you want on a free server, where a session timer can stop the process at any moment. The version only advances once a step has fully committed.

Adding your next migration

When you need another change, append a function to the array — never edit one that already shipped. Say a later release adds a warnings table:

// v4: append, don't touch v1–v3
(d) => d.exec(`CREATE TABLE warnings (id INTEGER PRIMARY KEY, user_id TEXT, reason TEXT)`),

Deploy it, restart, and every install — yours, and every user who runs your bot — moves from wherever it was up to v4, once, in order. Editing an old migration instead would desync databases that already ran the old version, which is the one thing this pattern exists to prevent.

What SQLite's ALTER can and can't do

SQLite's ALTER TABLE can add a column or rename a column/table — but it can't drop or retype a column directly. When you truly need that, the standard SQLite move is: create the new-shape table, copy the data across, drop the old one, and rename the new one into place — all inside a migration. It's more code, but it's a normal, well-documented step, not a workaround.

Back up before you migrate

A migration edits real data, so protect it first:

• Your bot.db lives in /home/container — a reinstall or application switch wipes it (the same warning as all local bot data). That's not a migration risk, but it is a data-loss risk, so take a snapshot on the Backups page (or download the file) before a risky change.
• For data that must never be lost, keep it in a managed database that outlives your server — the same versioned-migration idea applies there too.

Verify it works

Watch the console across two starts. On the deploy that adds a migration, you see it apply once:

applied migration -> v3

Restart again and that line is gone — the database is already at the latest version, so nothing runs. A test with an existing user row confirms the payoff: after the v3 migration, a user who had xp = 500 still has xp = 500, now with level = 6 backfilled — the old data survived the schema change untouched.

Troubleshooting

• duplicate column name on start — a migration ran twice, which means the version bump isn't sticking. Confirm the user_version write is inside the loop and the database isn't in-memory.
• table already exists — you added a raw CREATE outside the version guard, or edited an old migration. Every schema change goes through the array.
• Migrations re-run on every start — you opened an in-memory database (new Database(':memory:')), which resets each launch. Use a file path like bot.db.
• Data gone after a deploy — that's a reinstall/template wiping the local file, not the migration. See Storing data for your bot and back up first.

Next steps

• Storing data for your bot
• Evolving schemas: the plain-SQL view
• Automate and manage backups