Skip to content

Database migrations ​

Tyrus migrations are numbered SQL files reviewed by the developer before application. Builds and normal database connections never create or apply migrations automatically. See Database schema commands for the full CLI reference.

Generate and review ​

tyrus db migrate generate <name> creates an initial migration from project Models when the configured database does not exist. Generated migrations validate every @foreignKey against the project Models for an initial schema or the current SQLite catalog for incremental changes. Initial migrations also require matching Model field types; incremental migrations check compatible SQLite type affinity. Both verify target table, columns, and uniqueness, including composite keys and unique indexes, in the replayed SQLite schema. SQLite identifier matching is case-insensitive during snapshot validation, while the snapshot preserves the spelling reported by the database. For an existing database, it requires a current schema snapshot and can create tables for new Models, add nullable columns or columns with literal defaults, and rebuild a table when a Model removes columns or changes primary-key, @unique, or outgoing foreign-key constraints. Rebuilds copy retained values and restore custom indexes, table triggers, and views from SQLite's schema catalog. Before saving generated SQL, the CLI replays it against an in-memory copy of the SQLite schema and validates the resulting views and triggers; unrelated views and triggers do not block a column removal, but dependencies that break or refer to a removed trigger field require a manually authored migration. Generation is also refused when a removed column is used by an index, another table references the table being rebuilt, or simultaneous additions prevent safe preservation. Applying a changed key or foreign-key constraint can fail when existing rows violate it; the SQLite transaction then rolls back. Changes to existing column definitions require a manually authored migration. The command writes a numbered SQL file and never changes the database. Names such as initial_schema below are examples; replace them with a descriptive name for your migration or seed.

Model migration matching follows SQLite's case-insensitive rules for table and column names, including foreign-key targets and physical column mappings. Two Models or fields cannot map to names that differ only by case.

Typical workflow ​

For an existing SQL database, edit the Model, compare it with the live database and current snapshot, generate a migration, review the numbered SQL file under db/migrations/, then apply it and refresh the snapshot. PostgreSQL and MySQL generate new Model tables and safe additive columns; changes that alter or remove existing definitions require a manually authored migration. In the example below, add_profile is a name chosen by the developer. For a new database, generate an initial migration from the Models, review and apply it, then run tyrus db introspect to create the snapshot.

sh
# Compare the edited Models with the snapshot and live database.
tyrus db diff
# Replace add_profile with a descriptive migration name.
tyrus db migrate generate add_profile
# Review and edit the generated SQL file before applying it.
tyrus db migrate apply
# Refresh the snapshot after the database schema changes.
tyrus db introspect
sh
# Replace `initial_schema` with a descriptive name for this migration.
tyrus db migrate generate initial_schema
tyrus db migrate status
tyrus db migrate apply
tyrus db introspect

# Replace `default_roles` with a descriptive name for this seed.
tyrus db seed create default_roles
tyrus db seed status
tyrus db seed apply

Inspect and edit generated SQL before applying it. SQLite type mappings that can lose values are rejected; u8[] model fields map to SQLite BLOB columns and the logical snapshot type bytes. After a migration changes the schema, run tyrus db introspect to refresh the local snapshot used by the compiler and Language Server. PostgreSQL and MySQL snapshots include visible base tables, columns, native column types, table engines where available, keys, indexes, foreign keys, and triggers; the snapshot stores a hashed target identifier without connection credentials.

Apply and track migrations ​

tyrus db migrate status lists applied and pending files without changing SQLite, PostgreSQL, or MySQL. tyrus db migrate apply applies PostgreSQL migrations in one transaction and records each file's checksum in _tyrus_migrations; MySQL records a started recovery state before each migration because DDL may commit implicitly. Concurrent PostgreSQL application uses a transaction-scoped advisory lock, and MySQL uses a named lock. The server migration validator recognizes PostgreSQL dollar-quoted bodies, escaped E'...' strings, and nested block comments, plus MySQL # comments, before checking transaction control. This lets PostgreSQL functions contain internal semicolons and procedural BEGIN/END without confusing the managed transaction checks. PostgreSQL rolls back a failed batch. If a MySQL migration fails, inspect the database and migration file before retrying; the incomplete history entry blocks automatic reapplication. Applied files cannot be edited or removed, and there is no automatic downgrade command.

PostgreSQL and MySQL Model migrations ​

For PostgreSQL and MySQL, tyrus db migrate generate <name> creates an initial migration from the project's Models without connecting to the database. Once migrations exist, incremental generation connects to the configured server, verifies that its schema matches the snapshot, and can create new Model tables, add columns, widen supported numeric types, and make required columns optional. Added columns must be nullable or have a literal default. A new @unique column is supported only when optional and without a default; adding @unique to an existing column generates a unique constraint, which can fail at apply time if duplicate values already exist, without rewriting those rows. New @foreignKey columns must be optional and have no default; adding a foreign key to an existing column is supported only when it is optional and its target table, unique key, and compatible native type exist in the snapshot. MySQL additionally requires InnoDB on both tables and matching signed integer widths recorded by introspection; PostgreSQL verifies the exact native type as well. Existing rows with orphaned values can make the generated foreign-key constraint fail when applied, without rewriting those rows. Widening is limited to smallint/integer/bigint and real/double transitions supported by the selected backend, requires native type metadata, and is refused for key, indexed, or related columns. Making a column optional preserves existing values and constraints; MySQL also requires native type metadata because its MODIFY COLUMN statement restates the full column definition. Changing or removing a literal default on an existing non-identity column generates ALTER COLUMN SET DEFAULT or DROP DEFAULT; expression defaults, narrowing, tightening nullability, primary-key changes, removing columns or constraints, changing existing foreign keys, and changing the timestamps option on an existing Model require a manually authored migration. Run tyrus db introspect again if an older snapshot lacks native type metadata required for safe type widening or foreign-key validation. The generator supports signed integer, floating-point, text and byte-array fields, required and optional fields, literal defaults supported by the backend, primary keys, unique constraints and validated @foreignKey relationships for new tables. PostgreSQL integer primary keys receive identity generation; MySQL integer primary keys receive AUTO_INCREMENT, and generated MySQL tables use InnoDB. The generator refuses unsupported types and MySQL text or binary keys/defaults. Use tyrus db migrate create <name> to create a backend-labeled SQL file for a manually authored follow-up migration. status, apply, introspect, check, and live-schema diff also support PostgreSQL and MySQL. Read-only Model-to-snapshot comparison supports SQLite, PostgreSQL, and MySQL; safe additive server changes are generated incrementally, while destructive or ambiguous operations remain manual.

Seeds ​

Create seed programs manually as .tyr files under db/seeds/. A seed can use the Model API or db.query; run one file with tyrus db apply default_roles.tyr, then check it with tyrus db seed status. The CLI records SHA-256 checksums in _tyrus_seeds (a table for SQLite, PostgreSQL, and MySQL, and a collection for MongoDB) and refuses to run an applied file again or accept changed or missing applied files. MongoDB seed history is managed by the same commands, but MongoDB schema migrations are not available. The seed program runs as a separate Tyrus process, so the CLI cannot wrap its arbitrary operations in one database transaction; use the database transaction API when several changes must be atomic. A failed or interrupted program remains marked started and blocks retries. Inspect the database, repair or undo partial changes, then remove that seed's _tyrus_seeds row or document before retrying; create a new seed file for later data changes.