Database Migration

Database migrations are versioned SQL 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 provides a built-in migrate command (and a migrate_sqlite alias for SQLite) that manages migration files and tracks which ones have been applied.

Migration Commands

All migration commands are run via dart run lib/app.dart migrate [options]:

For SQLite, add --sqlite to target the SQLite database instead of MySQL.

First-Time Setup

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

# MySQL
dart run lib/app.dart migrate --init

# SQLite
dart run lib/app.dart migrate --init --sqlite

Finch creates a _migrations table on first run 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

Generate a new timestamped migration file:

dart run lib/app.dart migrate --create --name add_books_table
# Creates: migrations/2024_01_15_120000_add_books_table.sql

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'),
);

Migration File Format

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

-- 2024-01-15 12:00:00
-- 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`;

Rolling Back

# Undo the most recently applied migration
dart run lib/app.dart migrate --rollback

# Undo the most recently applied SQLite migration
dart run lib/app.dart migrate --rollback --sqlite

The rollback executes the SQL in the ## ROLL BACK: section of the last applied file.

Listing Migration Status

dart run lib/app.dart migrate --list

This prints a table showing each migration file, when it was applied (or "pending" if not yet run).

Running Migrations on Startup

You can run migrations automatically when the app starts by adding the migrate command to the startup arguments:

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

Or in app.dart for development:

void main([List<String>? args]) async {
  // If no args are provided, run migrate --init automatically
  await app.start(args ?? ['migrate', '--init']);
}

Always back up your production database before running migrations.

• --init or -i: Initializes the migration table and executes all pending migrations.
• --create or -c: Creates a new migration file template.
• --name or -n: Name of migration file while creating.
• --rollback or -r: Rolls back the most recent migrations.
• --list or -l: Lists all migration files and their status.

This commands are available after you run the app (or while running the app in console mode). you can use the migrate command in your app.dart file. for example:

dart run example/lib/app.dart migrate --init

Add new migration file

You can add a new migration file by using the --create or -c option. for example:

Finch> migrate --create --name "add_users_table"

Migration file template

Migration file template is a simple sql file that contains two sections:

-- [DATE & TIME]
-- [MySQL | SQLite] Migration File 
-- Name: [NAME OF MIGRATION] 
-- ## NEW VERSION:

   [HERE GOES THE NEW VERSION SQL CODE]

-- ## ROLL BACK:
  
   [HERE GOES THE ROLLBACK SQL CODE]