Database Migration

Database migrations are versioned scripts that track changes to your database schema over time. Instead of manually running SQL commands on each environment, migrations ensure that every database — development, staging, production — stays in sync automatically.

Finch tracks applied migrations in a wa_migration table it creates automatically (per database), and provides two related but distinct app-runtime commands: migrate for MySQL, and migrate_sqlite for SQLite. Both are app-runtime commands — they run inside your actual FinchApp instance, not as a bare top-level finch flag. See Finch CLI for how the two command layers relate.

Migration Commands

Run these via finch run --args="..." / finch serve --args="...", or directly with dart run lib/app.dart ...:

Important: --sqlite on the migrate command only affects --init — it runs both database's pending migrations together as a convenience. It is ignored by --rollback and --list; migrate --rollback --sqlite still rolls back a MySQL migration. To roll back or list SQLite migrations, always use the dedicated migrate_sqlite command.

Creating a new migration file (as opposed to applying one) is a top-level finch CLI command instead — see Creating a Migration File below.

First-Time Setup

Run --init to apply all migrations to a fresh database:

# MySQL only
finch run --args="migrate --init"

# MySQL and SQLite together
finch run --args="migrate --init --and migrate_sqlite --init"
# or, equivalently, in one migrate call:
finch run --args="migrate --init --sqlite"

# SQLite only
finch run --args="migrate_sqlite --init"

Finch creates a wa_migration table on first run (per database) to track which files have already been executed. Subsequent calls to --init only apply the files that have not yet run.

Creating a Migration File

Generating a new migration file is a top-level finch CLI command (it doesn't need the app running, since it's just scaffolding a file):

finch migrate --create --name add_books_table
# Creates something like: migrations/z1700000000000_add_books_table_migration.sql

finch migrate --create --name add_books_table --sqlite
# Creates the SQLite equivalent under pathMigrationSQLite

The filename is prefixed with z<millisecondsSinceEpoch>_ (the leading z keeps timestamp-prefixed files sorting after any non-migration files) and slugifies the name you passed. The file is placed in the pathMigrationMySQL directory (or pathMigrationSQLite for SQLite), both configured in FinchConfigs:

FinchConfigs configs = FinchConfigs(
  pathMigrationMySQL:  pathTo('./migrations'),
  pathMigrationSQLite: pathTo('./migrations_sqlite'),
);

Whether --create generates a .sql file or a ready-made Dart DartMigration class boilerplate is controlled by the mysql_migrate.type / sqlite_migrate.type setting in your pubspec.yaml (sql by default) — see pubspec.yaml Configuration.

Migration File Format

Each .sql migration file has two sections: the forward migration (## NEW VERSION) and the rollback (## ROLL BACK):

-- 2024-01-15 12:00:00.000
-- MySQL Migration File
-- Name: add_books_table
-- ## NEW VERSION:

CREATE TABLE IF NOT EXISTS `books` (
  `id`             INT          NOT NULL AUTO_INCREMENT,
  `title`          VARCHAR(255) NOT NULL,
  `author`         VARCHAR(255) NOT NULL,
  `published_date` DATE         NOT NULL,
  `category_id`    INT          NULL,
  PRIMARY KEY (`id`)
);

-- ## ROLL BACK:

DROP TABLE IF EXISTS `books`;

Each migration (file-based or Dart-based) runs inside a database transaction: if any statement fails, the transaction is rolled back automatically and the migration is not recorded in wa_migration, so a fixed version of the same file will be picked up by the next --init.

Dart-Based Migrations

Instead of SQL files, you can define migrations in Dart by extending DartMigration. Build the up()/down() SQL with addSql(), and set target to choose MigrationTarget.mysql or MigrationTarget.sqlite:

import 'package:finch/mysql.dart';

class M1CreateUsers extends DartMigration {
  @override
  MigrationTarget get target => MigrationTarget.sqlite;

  M1CreateUsers() : super('m1_create_users');

  @override
  void up() {
    addSql('''
      CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        email TEXT
      );
    ''');
  }

  @override
  void down() {
    addSql('DROP TABLE IF EXISTS users;');
  }
}

Register migrations on the FinchApp instance. The name passed to super(...) must be unique across all registered migrations — it's what gets recorded in wa_migration, so renaming it later makes Finch think the migration was never applied:

final app = FinchApp(configs: configs)
  ..registerDartMigration([
    M1CreateUsers(),
    M2InsertUsers(),
  ]);

Once registered, migrate --init/--rollback/--list and their migrate_sqlite counterparts run the Dart migrations matching that target in addition to any .sql files in the migrations directory — you can freely mix both styles in the same project.

Rolling Back

# Undo the most recently applied MySQL migration
finch run --args="migrate --rollback"

# Undo the most recently applied SQLite migration — must use migrate_sqlite
finch run --args="migrate_sqlite --rollback"

# Undo the two most recent MySQL migrations
finch run --args="migrate --rollback 2"

The rollback executes the SQL in the ## ROLL BACK: section of the last applied file (or the down() method of the last applied Dart migration).

Listing Migration Status

finch run --args="migrate --list"        # MySQL
finch run --args="migrate_sqlite --list" # SQLite

This prints a table showing each migration file, whether it has been executed, and when.

Running Migrations on Startup

Run migrations automatically as part of your startup command — this is exactly what the example project's Docker image does (see Docker for Finch):

# In production startup (e.g., Docker CMD)
finch serve --args="migrate --init --and migrate_sqlite --init"

--and chains multiple app-runtime commands in a single --args string, so both databases get migrated before the server starts serving requests.

Always back up your production database before running migrations.