Change your schema without losing data

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.

🎯 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.

Why hand-editing breaks

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.
• A raw 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.

The idea: a version number

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

The migrations, in plain SQL

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 = N together 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.

The golden rule: append, never edit

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.

Dropping or changing a column

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.

On MySQL and PostgreSQL

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.)

Back up before you migrate

A migration edits real data, so protect it first — this is exactly the moment to have a snapshot.

• On a Falix server, your SQLite file lives in /home/container; take a snapshot on the Backups page (or download the file) before a risky change.
• For a managed database, take a database backup from the Databases page first — see Database backups.

Verify it works

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.

Troubleshooting

• 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.
• Migrations re-run every start — the database is in-memory or on a file that gets wiped. Point it at a real file that survives restarts.
• Data gone after a deploy — that's a reinstall or template switch wiping a local SQLite file, not a migration. See Storing data for your bot, and keep durable data in a managed database.

Next steps

• Evolve your bot's database without losing data
• Designing tables for your bot
• Database backups