GuidesOnly show guides for your app
GuidesGuidesYour tables will change — a new column, a new table. Versioned migrations make each change run exactly once and keep old data intact, driven by SQLite's built-in user_version counter (with the plain schema_version table for MySQL and PostgreSQL).
Version one of your database is easy — you write a CREATE TABLE and move on. Then the app grows: you need a new column, a new table, a different shape. Changing a live schema by hand is where data goes to die. This guide shows the professional fix — versioned migrations — in plain SQL, so each change runs exactly once and everyone's existing data survives.
| At a glance | |
|---|---|
| You need | a database whose schema will change over time — a bot's SQLite file or a managed database |
| Helps to know | Just enough SQL |
| Time | about twenty-five minutes |
🎯 Building a bot? Evolve your bot's database without losing data has the ready-made JavaScript migration runner for
better-sqlite3. This guide is the plain-SQL idea behind it — read either first; they teach the same pattern.
CREATE TABLE IF NOT EXISTS is fine for version one — it makes the table if it's missing and does nothing if it's there. But it can't change a table that already exists, and the obvious next moves crash:
ALTER TABLE users ADD COLUMN level ... works once, then crashes with duplicate column name on the next start.CREATE TABLE (no IF NOT EXISTS) crashes with table already exists.You need each change to run exactly once, in order — on a brand-new empty database and on one that's a few versions behind. That's what a migration system does, and it needs only one thing: a place to remember which changes have already run.
Number your schema changes 1, 2, 3, … Store, inside the database, which number it's reached. On startup, run every migration whose number is higher than the stored one, then save the new number. A database that's current runs nothing.
SQLite gives you the counter for free. Every database file carries a private integer, PRAGMA user_version, that starts at 0 and is yours to use:
PRAGMA user_version; -- read it (0 on a fresh database)
PRAGMA user_version = 1; -- set it
Each migration is a block of SQL that ends by bumping the version. Written out, the first two look like this:
-- migration 1: the original schema
CREATE TABLE users (
user_id TEXT PRIMARY KEY,
xp INTEGER NOT NULL DEFAULT 0
);
PRAGMA user_version = 1;
-- migration 2: add a column, and backfill it from existing data
ALTER TABLE users ADD COLUMN level INTEGER NOT NULL DEFAULT 1;
UPDATE users SET level = 1 + (xp / 100);
PRAGMA user_version = 2;
Your startup code reads user_version and runs only the blocks numbered above it. On a fresh database it runs both and lands at 2; on a database already at 1 it runs only migration 2; on one at 2 it runs nothing. The backfill line matters — migration 2 doesn't just add an empty level column, it fills in a sensible value for users who already had XP, so nobody's row is left half-formed.
🎯 Good to know: Run each migration and its
PRAGMA user_version = Ntogether in a transaction (BEGIN; … COMMIT;). If the process is killed mid-change — an out-of-memory kill or a free-plan timer expiry — the whole step rolls back and the version never advances past a change that didn't finish. The next start retries it cleanly. The bot migration runner wraps every step this way.
When you need another change, add migration 3 to the end. Never edit a migration that has already shipped:
-- migration 3: a new table in a later release
CREATE TABLE warnings (
id INTEGER PRIMARY KEY,
user_id TEXT NOT NULL,
reason TEXT
);
PRAGMA user_version = 3;
Editing migration 1 after it shipped would desync every database that already ran the old version 1 — they'd never see your edit, because they're already past it. Appending is the one discipline this whole pattern rests on.
SQLite's ALTER TABLE can add a column or rename one — but it cannot drop a column or change its type directly. The standard move is to rebuild the table inside a migration: make the new-shape table, copy the data across, drop the old one, rename the new one into place.
-- migration 4: remove a column by rebuilding the table
CREATE TABLE users_new (
user_id TEXT PRIMARY KEY,
xp INTEGER NOT NULL DEFAULT 0
);
INSERT INTO users_new (user_id, xp) SELECT user_id, xp FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
PRAGMA user_version = 4;
It's more code, but it's a normal, well-documented step — the data lands in the new table before the old one is dropped, so nothing is lost.
Managed MySQL and PostgreSQL don't have user_version — so make your own counter with a tiny table:
CREATE TABLE IF NOT EXISTS schema_version (version INTEGER NOT NULL);
-- seed it once with: INSERT INTO schema_version (version) VALUES (0);
Read SELECT version FROM schema_version, run the migrations above it, and UPDATE schema_version SET version = N at the end of each — same idea, same append-only rule, same transaction wrapping. (These engines also support dropping columns directly, so the rebuild dance is SQLite-only.)
A migration edits real data, so protect it first — this is exactly the moment to have a snapshot.
/home/container; take a snapshot on the Backups page (or download the file) before a risky change.Run your startup twice and watch the console. The first run applies the pending migrations and logs them; the second run applies nothing — the database is already current, so the runner is silent. Confirm the payoff with a test row: after migration 2, a user who had xp = 500 still has xp = 500, now with level = 6 backfilled. The schema changed; the data didn't move.
duplicate column name on start — a migration ran twice, so the version bump isn't sticking. Make sure PRAGMA user_version = N is in the same step as the change, and you're not using an in-memory database (:memory:) that resets every launch.table already exists — a raw CREATE slipped in outside the version guard, or an old migration got edited. Every schema change goes through a numbered migration.