Migrations API

Two halves:

• Running migrations: MigrationManager finds *.up.sql / *.down.sql files, applies pending ones and records them in a tracking table.
• Writing migrations: SchemaDiffer compares two schemas and writes up/down SQL. dart run gisila_orm:generate does this for you on every run (see CLI).

Most projects only use the CLI. Use this API to run migrations from your own code, for example at application startup or in tests.

import 'package:gisila_orm/gisila.dart';

final db = await Database.connect(await DatabaseConfig.fromEnvironment());
final manager = MigrationManager(db);
final result = await manager.up(await manager.discoverIn('lib'));
print('applied ${result.applied.length} in batch ${result.batch}');
await db.close();

MigrationManager

class MigrationManager {
  MigrationManager(Database database, {String trackingTable = 'gisila_migrations'});
}

When to use: to apply or roll back migrations programmatically. It is safe to run from several processes at once: up and down hold a Postgres advisory lock, so concurrent runs against the same database take turns.

Method	Signature	Description
discoverIn	Future<List<Migration>> discoverIn(String directory)	Recursively finds *.up.sql files, pairs each with a same-prefix *.down.sql, and sorts by id. Throws FileSystemException if the directory is missing.
up	Future<MigrationResult> up(List<Migration> discovered)	Applies every migration not yet recorded, in order, as one new batch.
down	Future<MigrationResult> down({required List<Migration> discovered, int steps = 1})	Rolls back the most recent steps batches, newest migration first.
listApplied	Future<List<AppliedMigration>> listApplied()	Recorded migrations, oldest first.
editedSinceApplied	Future<List<String>> editedSinceApplied(List<Migration> discovered)	Ids of applied migrations whose file no longer matches the stored checksum.
ensureSchema	Future<void> ensureSchema()	Creates the tracking table if needed. The other methods call it.

Discovery rules

• The id is the file name without .up.sql, for example 1730000000000_initial_schema. Ids sort as strings, so use a fixed-width prefix such as the millisecond timestamps the generator writes.
• Files ending in .gisila.up.sql (the generator’s full-schema script) are skipped when the directory holds any other migration. If they are the only ones, they are used, which suits projects that never ran generate.
• A missing .down.sql gives an empty downSql; rolling that migration back throws MigrationRollbackException.

How up runs

1. Takes the advisory lock (keyed on the current schema and tracking table name).
2. Reads the tracking table. If an applied migration’s file was edited since, throws MigrationChecksumException and applies nothing.
3. Runs each pending migration in its own transaction together with its tracking row, so a failure stops the batch but leaves earlier migrations committed.
4. Returns a MigrationResult with the applied migrations and the batch number.

The SQL is split into statements with splitSqlStatements, which drops bare BEGIN / COMMIT lines, so generated files that contain them still run inside the manager’s transaction.

Non-transactional migrations

Add this line anywhere in a migration file to run it outside a transaction:

-- gisila:no-transaction
CREATE INDEX CONCURRENTLY "idx_posts_title" ON "posts" ("title");

Statements then run one by one. If one fails, the earlier ones stay applied and the migration isn’t recorded, so write such migrations to be re-runnable (IF NOT EXISTS). Use it for statements Postgres refuses inside a transaction, such as CREATE INDEX CONCURRENTLY.

Tracking table

CREATE TABLE IF NOT EXISTS "gisila_migrations" (
  "id"         BIGSERIAL PRIMARY KEY,
  "migration"  VARCHAR(255) NOT NULL UNIQUE,
  "batch"      INTEGER      NOT NULL,
  "applied_at" TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "checksum"   VARCHAR(64)
);

Rows recorded before checksums existed have a null checksum; the next up fills it in from the current file.

Migration

class Migration {
  const Migration({required String id, required String upSql, String downSql = '', String? sourcePath});
}

One migration. Usually produced by discoverIn; construct one yourself to apply SQL that isn’t on disk (for example, migrations embedded in a compiled binary).

Member	Type	Description
id	String	Stable identifier recorded in the tracking table.
upSql	String	SQL to apply. May hold several statements.
downSql	String	SQL to roll back; empty when none.
sourcePath	String?	Where the up file was read from.
checksum	String	SHA-256 of upSql with CRLF normalised to LF.
upIsNonTransactional	bool	upSql contains -- gisila:no-transaction.
downIsNonTransactional	bool	Same for downSql.

const embedded = [
  Migration(
    id: '0001_create_notes',
    upSql: 'CREATE TABLE notes (id BIGSERIAL PRIMARY KEY, body TEXT NOT NULL);',
    downSql: 'DROP TABLE notes;',
  ),
];
await MigrationManager(db).up(embedded);

MigrationResult

Field	Type	Description
applied	List<Migration>	Applied by this up. Empty when up to date.
rolledBack	List<Migration>	Rolled back by this down.
batch	int	Batch number applied, or the last batch rolled back. 0 when nothing happened.

AppliedMigration

Field	Type	Description
id	String	Migration id.
appliedAt	DateTime	When it was applied.
batch	int	Batch number.
checksum	String?	Checksum stored at apply time.

MigrationChecksumException

class MigrationChecksumException implements Exception {
  final List<String> ids;
}

Thrown by up when applied migrations were edited afterwards. Restore the original files and put the change in a new migration. migrate status marks such files (edited after it was applied).

Other migration exports

Symbol	Description
String migrationChecksum(String sql)	The checksum function used for Migration.checksum.
List<String> splitSqlStatements(String sql)	Splits a script on top-level ;, respecting quotes, E'...' strings, dollar quoting ($$, $tag$) and nested block comments. Drops comments and bare BEGIN / COMMIT.
MigrationRollbackException	Thrown by down when a migration’s file or down SQL is missing. See Errors.

SchemaDiffer

class SchemaDiffer {
  SchemaDiff compareSchemas(SchemaDefinition oldSchema, SchemaDefinition newSchema);
  Future<void> generateMigrationFile(SchemaDiff diff, String outputPath, String migrationName,
      {bool allowDestructive = false});
}

When to use: generate already diffs against its snapshot. Use the differ directly to compare two arbitrary schema files, for example two git revisions (dart run gisila_orm:migrate diff wraps exactly this).

What it detects:

Change	Generated SQL
New / removed model	CREATE TABLE (plus indexes and FKs) / DROP TABLE
Changed db_table	ALTER TABLE ... RENAME TO ...
New / removed column	ADD COLUMN / DROP COLUMN
A column disappears and exactly one new column with the same type and nullability appears	RENAME COLUMN, marked as a guessed rename
Type, nullability or default change	ALTER COLUMN ... TYPE / SET NOT NULL / DROP NOT NULL / SET DEFAULT / DROP DEFAULT
Vector index_method, distance or is_index change	implicit vector index dropped and re-created
Scalar column becomes a foreign key (or back)	rename to/from <name>_id and FK add/drop
FK target or on_delete / on_update change	FK constraint dropped and re-added
Index and CHECK changes	CREATE INDEX / DROP INDEX, ADD CONSTRAINT / DROP CONSTRAINT
New enum / new enum value	CREATE TYPE / ALTER TYPE ... ADD VALUE IF NOT EXISTS
Removed enum	DROP TYPE (after tables)
New / removed many-to-many	junction CREATE TABLE / DROP TABLE
First vector column	CREATE EXTENSION IF NOT EXISTS vector; first

Not migrated: turning is_unique or is_index on or off for a column that already exists (the change is detected, but the generated SQL adds or drops no constraint or index), and is_index on a column added to an existing table. Use the indexes: block, or add the SQL to the migration by hand.

Models are matched by name. Renaming a model in YAML is a drop plus a create, which loses data; keep the model name and change db_table instead.

generateMigrationFile

Writes <outputPath>/<millis>_<migrationName>.up.sql and .down.sql, wrapped in BEGIN; / COMMIT;, with each step commented. Destructive steps are marked -- DESTRUCTIVE, guessed renames -- GUESSED RENAME.

Parameter	Type	Default	Description
diff	SchemaDiff	–	From compareSchemas. Throws ArgumentError when empty.
outputPath	String	–	Directory (created if missing).
migrationName	String	–	Slug appended to the timestamp.
allowDestructive	bool	false	Without it, a diff with destructive changes throws DestructiveMigrationException and writes nothing.

A change is destructive when it drops a table, column or enum, or changes a column’s type in a way that isn’t a widening. Widenings are SMALLINT → INTEGER → BIGINT, REAL → DOUBLE PRECISION, and a longer VARCHAR(n), VARCHAR or TEXT from VARCHAR(n).

import 'package:gisila_orm/gisila.dart';
import 'package:gisila_orm/generators/schema_parser.dart';

final oldSchema = await SchemaDefinition.fromFile('schema_v1.gisila.yaml');
final newSchema = await SchemaDefinition.fromFile('schema_v2.gisila.yaml');
final differ = SchemaDiffer();
final diff = differ.compareSchemas(oldSchema, newSchema);
for (final c in diff.changes) {
  print(c); // e.g. "Add column: posts.slug"
}
if (diff.isNotEmpty) {
  await differ.generateMigrationFile(diff, 'lib/models/migrations', 'add_slug');
}

SchemaDiff

Member	Type	Description
changes	List<SchemaChange>	Every detected change.
operations	List<MigrationOperation>	Up/down SQL per change, in apply order.
hasDestructiveChanges	bool	Any change can lose data.
destructiveChanges	List<SchemaChange>	Those changes.
guessedRenames	List<SchemaChange>	Column renames inferred from a drop plus an add. Check them.
isEmpty, isNotEmpty	bool	Whether anything changed.

SchemaChange, ChangeType, MigrationOperation

class SchemaChange {
  final ChangeType type;
  final String? tableName, columnName, oldName, newName;
  final Map<String, dynamic>? metadata; // {'guessed': true} for guessed renames
}

enum ChangeType {
  createTable, dropTable, renameTable, addColumn, dropColumn, modifyColumn, renameColumn,
  addIndex, dropIndex, addForeignKey, dropForeignKey, addEnum, dropEnum, addEnumValue,
  addCheck, dropCheck,
}

class MigrationOperation {
  final String upSql;
  final String downSql;
  final SchemaChange change;
}

SchemaChange.toString() gives a readable line such as Rename column: posts.name → title.

DestructiveMigrationException

class DestructiveMigrationException implements Exception {
  final List<SchemaChange> changes;
}

Thrown by generateMigrationFile (and so by generate and migrate diff) when a diff would lose data and allowDestructive is false. Its message lists the changes and tells you to re-run with --allow-destructive.

SchemaDefinition

SchemaDefinition lives in package:gisila_orm/generators/schema_parser.dart, which gisila.dart doesn’t export. Import it when you work with the differ.

Member	Description
SchemaDefinition.fromYaml(String yaml, {Uri? sourceUrl, ...})	Parses one schema. Throws SchemaValidationException.
SchemaDefinition.fromFile(String path)	Reads and parses a file.
SchemaDefinition.fromProject(List<File> files)	Parses and merges several files, resolving cross-file references.
models, enums	Parsed ModelDefinitions and EnumDefinitions.
getModel(name), getEnum(name), modelNames, enumNames, relationships	Lookups.

emitSchemaYaml(SchemaDefinition) in the same library serialises a schema back to YAML; generate uses it for its snapshot.

See also: Run migrations, Schema changes in production, Migrations concept.