Migrations

Startup schema migrations per connection string: define them for the core or Diesel backend, understand ascending-version ordering, revert, and the crash-safe backup-in-place protocol.

The plugin runs schema migrations per connection string, triggered on Database.load. Two engines, one model: backend-core uses an inline, tauri-plugin-sql-style engine; backend-diesel uses Diesel’s embed_migrations! + MigrationHarness. Both run under the same crash-safe backup protocol on disk.

Core backend (backend-core)

Define Migration values and register them on the builder, keyed by connection string:

use better_duck_tauri::{core::Builder, Migration, MigrationKind};

let migrations = vec![
    Migration { version: 1, description: "init".into(),
                sql: "CREATE TABLE t (id INTEGER)".into(), kind: MigrationKind::Up },
    Migration { version: 1, description: "init-down".into(),
                sql: "DROP TABLE t".into(), kind: MigrationKind::Down },
];

Builder::new().add_migrations("duckdb:app.db", migrations).build();

The Migration struct

version · i64

The ordering and identity key. Up migrations run in ascending order; a Down is matched to its Up by sharing the same version.

description · String

Human-readable label, recorded alongside the applied version.

sql · String

The SQL to run. May contain multiple ;-separated statements (run via execute_batch).

kind · MigrationKind

MigrationKind::Up (schema-forward) or MigrationKind::Down (revert).

add_migrations(conn_str, Vec<Migration>) is repeatable per connection string — later calls extend the list.

How it runs

On load, pending Up migrations run in ascending version order, each inside its own transaction. A failing migration rolls that one back and surfaces the error; already-committed earlier migrations stay (the backup protocol is the whole-batch safety net). Applied versions are tracked in a __better_duck_migrations table:

CREATE TABLE IF NOT EXISTS __better_duck_migrations (
  version     BIGINT PRIMARY KEY,
  description  VARCHAR NOT NULL,
  applied_at   TIMESTAMP NOT NULL DEFAULT current_timestamp
)

Re-running is idempotent: already-applied versions are skipped.

Revert

db.revert() (the revert command) runs the Down SQL of the highest applied version, in one transaction, deletes its bookkeeping row, and returns the reverted version (or null if nothing was applied).

Revert errors if the last-applied version has no matching Down migration (backend error: no down migration for version N). Author a Down for every Up you may need to roll back. Revert is a write, so it is rejected under read_only(true).

Diesel backend (backend-diesel)

Register Diesel embedded migrations instead of a Vec:

use better_duck_tauri::diesel::Builder;
use diesel_migrations::{embed_migrations, EmbeddedMigrations};

const MIGRATIONS: EmbeddedMigrations = embed_migrations!("migrations");

Builder::new().add_migrations("duckdb:app.db", MIGRATIONS).build();

On load, pending migrations run through Diesel’s MigrationHarness (run_pending_migrations), wrapped in the same crash-safe backup skeleton as the core engine. db.revert() calls the harness’s revert_last_migration and best-effort parses the Diesel version string (a numeric timestamp) to an i64. In-memory migrations run on the shared pool handle; read_only skips them.

See better-duck-diesel setup for authoring the embedded migration directory and the schema/DSL side.

Crash-safe backup protocol

On-disk migrations go through a backup-in-place protocol, because the per-migration transactions mean a mid-batch failure could otherwise leave earlier migrations committed with no clean rollback. It also guards against file corruption or an interrupted process. The sequence runs only when something is pending:

1. Recover an interrupted run

   If a <db>.migrate-journal exists from a prior interrupted run, restore from <db>.bak first (a journal with no backup is treated as stale and removed).

2. Checkpoint

   CHECKPOINT to fold the WAL into the main file so a file-level copy is self-contained.

3. Back up + journal

   Copy the file to <db>.bak (fsynced), then write the journal — its mere presence records “a migration is in flight and did not confirm success”.

4. Migrate, then clear

   Run the migrations. On success, delete the journal first, then the backup (so a crash in between leaves only a harmless stale backup). On failure, restore from the backup and surface the error.

Sidecar files live beside the database: <db>.bak and <db>.migrate-journal (and <db>.wal / <db>.tmp are cleaned on restore). The whole protocol runs before the long-lived connection handle is opened, so migration has exclusive file access.

Special cases:

• In-memory databases (duckdb::memory:) have no file — migrations run directly on the registered handle, with no backup protocol.
• read_only(true) skips migrations entirely (they are writes).

This migration backup is distinct from checkpointing. Checkpointing folds the WAL into the main file during normal operation; the backup protocol snapshots the whole file around a migration batch so it can be restored on failure.

See also

• Maintenance — the revert command in context.
• Registration — where add_migrations sits on the builder.
• Backends — the one API difference between the two builders.