With sqflite, how do openDatabase's version, onCreate and onUpgrade work together when an app adds a column to an existing table in version 2?
answer
- the version lives in user_version
- fresh install: onCreate at latest schema
- upgrade: onUpgrade(old, new) once
- stepwise if (oldVersion < n)
- one exclusive transaction, then setVersion
basics
~20 sopenDatabase compares the requested version with the file's stored version. A new file runs onCreate, which must build the full current schema; an older file runs onUpgrade(db, oldVersion, newVersion) once, where you apply each step, such as ALTER TABLE ADD COLUMN.
solid answer
~40 ssqflite stores the schema version in SQLite's `user_version`. When I call `openDatabase(path, version: 2, onCreate: ..., onUpgrade: ...)`, a brand-new file gets `onCreate(db, 2)`, so `onCreate` must create the complete version-2 schema, including the new `category` column. A file at version 1 gets `onUpgrade(db, 1, 2)`, where I run `ALTER TABLE expenses ADD COLUMN category TEXT`. `onUpgrade` is called once with the old and new numbers, even across several versions, so I write it as cumulative `if (oldVersion < 2)`, `if (oldVersion < 3)` blocks. These callbacks run inside one exclusive transaction and the version is written only after they succeed, so a failed migration rolls back and is retried on the next open. `onConfigure` runs before all of it on every open.
code
dart · 29 linesimport 'package:path/path.dart' as p;
import 'package:sqflite/sqflite.dart';
Future<Database> openLedger() async {
final path = p.join(await getDatabasesPath(), 'ledger.db');
return openDatabase(
path,
version: 2,
onConfigure: (db) => db.execute('PRAGMA foreign_keys = ON'),
onCreate: (db, version) async {
// Fresh install: create the full, current (v2) schema.
await db.execute('''
CREATE TABLE expenses (
id INTEGER PRIMARY KEY AUTOINCREMENT,
amount_cents INTEGER NOT NULL,
note TEXT,
spent_at INTEGER NOT NULL,
category TEXT
)''');
},
onUpgrade: (db, oldVersion, newVersion) async {
// Existing install: apply every step after oldVersion, in order.
if (oldVersion < 2) {
await db.execute('ALTER TABLE expenses ADD COLUMN category TEXT');
}
// if (oldVersion < 3) { ... } goes here in the next release.
},
);
}go deeper
Recall that version, onCreate and onUpgrade drive schema changes, and that a new column needs both a create and an upgrade path.
Explain the callback order, the user_version storage, the single onUpgrade call with old and new numbers, and the transaction around it.
Write cumulative, never-edited migration steps, test real upgrade files, and decide deliberately what a downgrade does to user data.
Set migration policy for the team: append-only steps, upgrade tests in CI, and when a data-destroying downgrade is ever acceptable.
## Where the version lives A SQLite file carries a small integer header field, `user_version`, that applications may use freely. sqflite uses it as the **schema version**: `getVersion()` reads `PRAGMA user_version` and the open logic writes it after migrating. A file that has never been versioned reads as `0`. You pass the version your code expects to `openDatabase(path, version: 2, ...)`. If you pass `onCreate`, `onUpgrade` or `onDowngrade` without a `version`, sqflite throws an `ArgumentError`. ## The order of callbacks on open 1. **`onConfigure(db)`** — runs first, on every open, before any version check. Use it for per-connection settings such as `PRAGMA foreign_keys = ON`. 2. **Version check** — if the stored version differs from the requested one, sqflite opens an **exclusive transaction** and re-reads the version inside it. 3. **Exactly one of**: - stored version `0` → `onCreate(db, newVersion)`; if there is no `onCreate`, `onUpgrade(db, 0, newVersion)` instead; - stored version lower → `onUpgrade(db, oldVersion, newVersion)`; - stored version higher → `onDowngrade(db, oldVersion, newVersion)`. 4. **`setVersion(newVersion)`** — still inside the same transaction. 5. **`onOpen(db)`** — after the version is set, just before `openDatabase` returns. Because step 3 and step 4 share one transaction, a migration that throws leaves both the schema and the version untouched; the next launch tries again. ## The two paths a version-2 release must serve Imagine an expense ledger whose version 1 had `expenses(id, amount_cents, note, spent_at)` and whose version 2 adds a `category` column. | Device state | Callback that runs | What it must do | |---|---|---| | First install of v2 | `onCreate(db, 2)` | create the **full v2** table, `category` included | | Upgrading from v1 | `onUpgrade(db, 1, 2)` | `ALTER TABLE expenses ADD COLUMN category TEXT` | | Already at v2 | nothing | open as-is | | A v3 build was installed, then v2 again | `onDowngrade(db, 3, 2)` | usually `onDatabaseDowngradeDelete` | The classic bug is updating only one path: adding the column in `onUpgrade` but not in `onCreate`, so new users crash on the first insert with a category; or editing `onCreate` only, so upgraded users never get the column. ## Writing onUpgrade so it survives skipped releases `onUpgrade` is called **once**, with the old and new numbers — a user jumping from 1 to 4 gets `onUpgrade(db, 1, 4)`, not three calls. Write the migrations as cumulative, ordered blocks: - `if (oldVersion < 2) { add category }` - `if (oldVersion < 3) { create budgets table }` - `if (oldVersion < 4) { add an index }` Never edit a step that has shipped; add a new one. And never call `setVersion` yourself — the package documentation says migrations should happen through these callbacks when opening. ## Practical details - Each `execute` runs **one** SQL statement; statements separated by `;` are not supported, so create each table with its own call or a `Batch`. - Inside these callbacks you use the `db` argument directly; a `Batch` created there commits with the open transaction. - Hot reload does not reopen the database, so schema changes need a full restart. - `onDatabaseDowngradeDelete` deletes the file and runs `onCreate` — acceptable for caches, destructive for user data. - Test the upgrade path with a real version-1 file, not just a fresh install. ## Rolling a schema change out safely A schema change ships to devices you cannot touch, so treat each release's migration as permanent: 1. **Bump `version` and add one `if (oldVersion < n)` block** — never renumber or rewrite an earlier block, because some device somewhere is still at that version. 2. **Update `onCreate` to the full new schema in the same change**, so fresh installs and upgraded installs end up identical. 3. **Keep new columns nullable or give them a default.** `ALTER TABLE ... ADD COLUMN` on existing rows leaves them `NULL` unless a default is declared, and the ledger's old expenses simply have no category until the user sets one. 4. **Test both paths**: a fresh open at version 2, and a real version-1 file opened by the version-2 code. A unit test with a desktop SQLite implementation can do the second in milliseconds. 5. **Decide downgrade behaviour up front.** Leaving `onDowngrade` unset does not leave the file alone: sqflite still writes the lower version number while the newer schema stays in place, so a later upgrade re-runs steps that were already applied and can fail, for example on a duplicate column.
- What happens if onUpgrade throws halfway through?The callbacks and `setVersion` run in one exclusive transaction, so the partial schema change is rolled back and the stored version stays at the old number. `openDatabase` fails with the error, and the next open attempts the migration again.
- Where should PRAGMA foreign_keys = ON go, and why not in onCreate?In `onConfigure`. Foreign-key enforcement is a per-connection setting, and `onConfigure` runs on every open, before the version check. `onCreate` runs only once in the file's life, so the pragma would be off on every later launch.
- What does onDatabaseDowngradeDelete do?Passed as `onDowngrade`, it deletes the database and recreates it through `onCreate` when a lower version opens a newer file. It avoids crashes after a rollback to an older build, but it destroys all local data, so it suits caches rather than a user's ledger.
saying these in an interview costs you the question
- onCreate runs on every app launch.
- Only onUpgrade needs the new column; onCreate can stay at version 1.
- onUpgrade is called once per version step, so 1 to 3 calls it twice.
- Call db.setVersion() yourself after migrating.
- A failed migration leaves the database half-migrated at the new version.