Migrations

How to version a schema with open()'s migrations option, and what happens when a migration in the middle of a list fails.

Basic usage

Pass migrations to open(). Each entry has a version and one or more statements:

await CapacitorSqlite.open({
  database: 'myapp',
  migrations: [
    {
      version: 1,
      statements: [
        'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)',
        'CREATE INDEX idx_users_name ON users (name)',
      ],
    },
    {
      version: 2,
      statements: ['ALTER TABLE users ADD COLUMN email TEXT'],
    },
  ],
});

open() reads PRAGMA user_version, then runs every migration whose version is greater than the stored value, in ascending order. After all pending migrations complete, it writes the highest applied version back to PRAGMA user_version. Migrations already applied on a previous launch are skipped automatically — the same migrations array is safe to pass on every open() call as your app evolves.

Versioning rules

• version must be a positive integer no greater than 2147483647 (2^31-1) — the same 32-bit ceiling SQLite itself uses for user_version, enforced identically on every platform.
• version values must be unique within one open() call; duplicates return MIGRATION_FAILED without applying anything from that call.
• Each entry's statements must each contain exactly one SQL statement — the same one-statement-per-string rule as execute().

Failure semantics: not atomic across versions

Each migration commits in its own transaction. If version 3 of a 5-version list fails, versions 1 and 2 remain durably applied — user_version included — even though open() as a whole returns MIGRATION_FAILED. Retrying open() with the same migrations list resumes from the first still-pending version; it does not restart from scratch or re-run already-applied versions.

Design each migration's statements to be safe to apply on its own, since a later version's failure will not undo an earlier version that already committed.

Reopening with pending migrations

If you call open() again for a database that is already open (same readonly and directory), the connection is reused, and any newly pending migration versions in the second call's migrations are applied before it resolves. A reopen with migrations while a manual transaction is active on that connection returns MIGRATION_FAILED instead of interleaving migration SQL with in-flight application transaction state.

Checking the current version

const schemaVersion = await CapacitorSqlite.getSchemaVersion({ database: 'myapp' });
if (schemaVersion.success) console.log(schemaVersion.data.version);

Next step

Transactions covers manual transaction control for application logic outside of migrations.