Database Migrations in PormG

PormG provides a structured way to manage database schema changes through migrations, inspired by Django but tailored for Julia and PostgreSQL, SQLite and MySQL workflows.

What Are Migrations?

Migrations are version-controlled scripts that describe changes to your database schema. They allow you to:

• Create new tables and fields
• Alter or remove existing fields
• Apply incremental changes
• Keep your database schema in sync with your Julia models
• Track migration history with checksums and status

How it Works: State-Based Reconciliation

PormG follows a State-Based migration philosophy (similar to modern tools like Flyway, Prisma, or Atlas, rather than purely change-based like standard Django).

1. Active Introspection: PormG reads your connection.yml file, connects to the specified live database, and introspects its actual physical schema.
2. Comparison: It compares that live schema against your in-memory Julia Models loaded in the current runtime session.
3. Diffing: It calculates the exact "delta" required to move the database to the target state defined in your code.
4. Generation: It produces a standalone Julia script (pending_migrations.jl) containing the DDL commands.

What this means in practice

Because every plan is a fresh diff between your models and the live database, PormG migrations behave differently from Django's migration graph — and the differences are deliberate:

• makemigrations never reads previous migration files. Each run compares your models against the live schema only; there is no dependency graph and no replay of earlier migrations to reconstruct state. Migration order does not accumulate — the database itself is the accumulated state.
• Migration files are an audit trail, not the source of truth. pending_migrations.jl and everything under applied_migrations/ record what was done; they are never re-read to plan or apply anything. Editing an already-applied file has no effect on future migrations — don't do it, it only desyncs the archive from the authoritative pormg_migrations table.
• You can regenerate freely. A pending draft you dislike can be dropped with discard_pending_migration("db") and re-generated from scratch; there is no graph to keep consistent.
• "Drift" means the live schema diverging from your models — an out-of-band ALTER/DROP, say — not an edited migration file. It is surfaced the normal way: the next makemigrations plans to reconcile it, and check("db"; kinds = [:schema_drift]) reports it without writing anything. (status() reports on the migration history, not the schema.) Verifying old migration-file checksums buys you nothing here.
• Both sides are compared as columns, not as field types. Each declared field compiles to a canonical description of the column it renders — type, nullability, key, uniqueness, default, foreign key, CHECKs, identity — and the live schema is read straight into the same description from the catalog. Two fields that render the same column (CharField, URLField and SlugField with the same length; a ForeignKey(unique = true) and a OneToOneField) are therefore one column to the diff, and nothing about a live column is inferred from which field type it "looks like".

What makemigrations Manages, Ignores, and Would Drop

makemigrations owns the tables your models declare: their columns, keys, the indexes and constraints a model can declare, and the tables no model declares any more. Everything else in the database falls into one of the groups below. "Destructive" means dry_run() lists the statement and migrate() refuses it without destructive = true (see Destructive Operations Safety).

When SQLite cannot confirm which tables are a virtual table's shadows on PormG's connection (its module is an extension your app loads elsewhere, such as sqlite-vec; the module does not report shadow tables; or SQLite is older than 3.37), every table named <name>_… is skipped and a warning lists them. Do not give your own tables that prefix: a model declaring one would plan CREATE TABLE and fail at migrate, because the table exists.

check("db"; kinds = [:schema_drift]) reports a finding for exactly the steps makemigrations would plan. So it never reports the objects PormG does not read. The one such object worth hearing about, an invalid PostgreSQL index, has its own class: kinds = [:invalid_index].

"Never read" does not mean "never affected". On SQLite a table rebuild drops and re-creates the views and triggers that name the table, as the table above says. On PostgreSQL a DROP TABLE uses CASCADE, and PormG refuses it only for the dependents PostgreSQL records. A trigger function written in PL/pgSQL that names the table is not one of them (see Deleting a Model).

To change an object outside this scope, such as a view, a trigger or a function, use your own SQL outside the migration plan. To keep makemigrations away from a table you manage that way, list it in ignore_tables:.

Index names and duplicate indexes

A field with db_index = true gets an index named <table>_<column>_idx, from the physical table and column (result_driverid_idx), the one-column spelling of the name a Models.Index derives. A name longer than the engine allows (63 bytes on PostgreSQL, 64 characters on MySQL) is cut and ends in 8 hex digits of its own hash, so two long names never meet. When another index already holds the name — one a renamed column or table left behind, since PormG never renames an existing index — the new index takes <table>_<column>_<8 hex>_idx instead. Every one of these names is the same on every run, so on PostgreSQL and SQLite a plan applied twice hits CREATE INDEX IF NOT EXISTS and creates nothing the second time.

The planner checks the name against the indexes it reads. It does not read an invalid PostgreSQL index or an index on an ignored table, so one of those holding the name makes the CREATE INDEX IF NOT EXISTS a no-op, and the next plan proposes it again. An invalid index is reported by check("db"; kinds = [:invalid_index]); drop it and the plan creates the index.

A Models.Index without name= can derive the same name as a db_index index: an Index over driverid and raceid and a column named driverid_raceid both derive result_driverid_raceid_idx. When neither exists yet, the Index keeps that name and the db_index index takes the hashed one. When the db_index index already holds it, makemigrations raises InvalidMigrationError: give the Index an explicit name=.

Indexes created before this naming carry a random suffix (result_driverid_a1b2c3d4_idx). They keep it: nothing reads an index name, so PormG does not rename them.

A column can end up carrying the same index more than once, under several names. A plan that read a column as unindexed created one more copy each time it ran, because a random name never matched IF NOT EXISTS. makemigrations reads every copy, and a copy here means a plain, non-unique one-column index with no WHERE, expression, direction, operator class or collation of its own:

• The field declares db_index = true — every copy but one is planned away, each as its own Remove duplicate index <index> on <column> step. The one kept is the copy named <table>_<column>_idx if there is one, otherwise the first by name.
• The field does not declare it — every copy is planned away in one plan: Remove index on <column> for one of them, Remove duplicate index … for the rest.

-- result.driverid declares db_index = true and carries three copies of its index:
DROP INDEX IF EXISTS "result_driverid_s21bkzlw_idx";
DROP INDEX IF EXISTS "result_driverid_zndqsv7m_idx";

Each is a DROP INDEX, so migrate() needs destructive = true. No data is lost: the copy kept indexes the same rows.

Terminology Mapping

If you are new to Django-style ORMs, the migration APIs map directly to standard universal database schema-management concepts:

Migration History Table

PormG uses a pormg_migrations table as the canonical runtime source of truth for tracking applied migrations. This table is created automatically when you run migrate() or init_migrations().

Each migration record contains:

• version: A unique timestamp-based identifier (YYYYMMDDHHmmssSSS)
• name: A human-readable migration name
• checksum: SHA-256 hash of the SQL content for integrity verification
• sql_content: The full SQL that was applied
• applied_at: Timestamp of when the migration was applied
• status: One of applied, failed
• is_destructive: Whether the migration contained destructive operations: a DROP, a TRUNCATE, or a DELETE with no WHERE (see Destructive Operations Safety)
• format_version: The frozen migration-format contract version (see Migration Format Stability)

Filesystem archives (applied_migrations/) remain useful for version control and review, but the history table is authoritative.

The exact on-disk file layout, checksum algorithm, and tracking-table columns are a stability contract — see Migration Format Stability.

Changing a Foreign Key

Changing what a foreign key points at is a change to the constraint, not to the column. PormG plans it when you change any of:

• the target model — ForeignKey(Status) → ForeignKey(RaceStatus) (a model bound RaceStatus maps to table racestatus; set db_table if you want another name)
• the target column — a different pk_field, or a parent whose key is renamed through db_column
• the referential action — on_delete = CASCADE → on_delete = SET_NULL

On PostgreSQL a constraint cannot be re-pointed in place, so the plan drops it and adds it back. Re-pointing Result.statusid from Status to a new RaceStatus model, with on_delete = CASCADE, generates:

ALTER TABLE "result" DROP CONSTRAINT "result_statusid_a1b2c3d4_fk";
ALTER TABLE "result" ADD CONSTRAINT "result_statusid_zmidlrtp_fk" FOREIGN KEY ("statusid") REFERENCES "racestatus" ("statusid") ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;

The dropped constraint is named from the live catalog; the new one gets a fresh random suffix, so the two never collide.

On SQLite the same change goes through the table rebuild described below, which re-renders the whole FOREIGN KEY ... REFERENCES ... ON DELETE clause from your model. MySQL drops and adds the key, as PostgreSQL does (DROP FOREIGN KEY, then ADD CONSTRAINT). The backends therefore agree on the outcome; only the DDL differs.

Renaming a field

A rename plans the rename. PostgreSQL and SQLite carry a column's own baggage across ALTER TABLE ... RENAME COLUMN — its indexes, its UNIQUE constraint, its PRIMARY KEY, and its FOREIGN KEY — so when nothing else about the field changed, that one statement is the whole plan. Renaming Result.statusid to Result.racestatusid, with everything else about the key unchanged:

ALTER TABLE "result" RENAME COLUMN "statusid" TO "racestatusid";

MySQL renames in one ALTER TABLE that also renames the column's own unique index, and drops and re-adds the CHECKs MySQL will not let a column take along (error 3959). That drop makes the rename destructive (MySQL Guide). That is all, on PostgreSQL and SQLite, and it holds for a plain field, an indexed field (ForeignKey sets db_index = true by default), a unique = true field, and one that is both.

The index keeps its old name. An index created for statusid stays result_statusid_idx while covering racestatusid. That is cosmetic: nothing reads the name — PormG matches an index by its real column membership — and makemigrations sees the column as indexed on both sides afterwards, so it plans nothing further. The alternative would be dropping and re-creating the index on every rename, which rebuilds it from scratch on a large table to change a string nobody reads.

The foreign key likewise keeps its pre-rename name, and that too is harmless: PormG looks a key up by its table and column, never by a name convention, so a later drop or re-point finds it.

When a rename carries more than a rename

A rename is the same column change with a new name, so if the field also changed, the plan carries that change too — the RENAME COLUMN first, then exactly what an ordinary alteration of that column would have emitted. Rename Result.statusid to Result.racestatusid and change the key — a different parent, a different target column, or a different ON DELETE; any of the three re-issues the constraint — and you get the drop, the rename, and the new constraint, in that order:

ALTER TABLE "result" DROP CONSTRAINT "result_statusid_a1b2c3d4_fk";

ALTER TABLE "result" RENAME COLUMN "statusid" TO "racestatusid";

ALTER TABLE "result" ADD CONSTRAINT "result_racestatusid_wpkbcx73_fk"
  FOREIGN KEY ("racestatusid") REFERENCES "status" ("statusid") ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;

The drop comes first and names the pre-rename column, because that is what the live catalog knows when the plan is built; the add names the new one, because by then the rename has run. On SQLite the same change is one table rebuild, emitted after the RENAME COLUMN.

Two renames on the same table in one migration are fine, and so is a rename alongside a new column or an ordinary column alteration: SQLite collapses them into a single rebuild placed after every rename and every ADD COLUMN, and that rebuild re-creates the renamed columns' secondary indexes under their new names.

Renaming a field that also changes db_index

Turning db_index on or off in the same change as the rename is planned in that migration, on every backend — a CREATE INDEX on the new column name, or a DROP INDEX of the live index. Leaving db_index alone plans nothing at all about the index: RENAME COLUMN carries an index with it on every engine, so the index keeps covering the column and only its name still mentions the old one. That stale name is deliberate — re-creating the index on every rename would rewrite a potentially large index to fix a string.

-- What UNIQUE constraints does the table actually have?
SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint
WHERE conrelid = 'driver'::regclass AND contype = 'u';

-- Look for duplicates FIRST — re-adding the constraint fails on dirty data,
-- and the window in which it was missing is when duplicates could arrive.
SELECT "driverslug", count(*) FROM "driver"
GROUP BY "driverslug" HAVING count(*) > 1;

ALTER TABLE "driver" ADD CONSTRAINT "driver_driverslug_key" UNIQUE ("driverslug");

SELECT conname FROM pg_constraint
WHERE conrelid = 'result'::regclass AND contype = 'f';

ALTER TABLE "result" DROP CONSTRAINT "result_statusid_a1b2c3d4_fk";

Renaming a model that has a many-to-many field

A ManyToManyField without through= gets an auto join table named after its model, with one column per end: Team.drivers is stored in team_drivers (id, team_id, driver_id). Rename the team table to squad and both names change with it, to squad_drivers and squad_id.

You answer one question, the one about the model. The join table and its column follow:

ALTER TABLE "team" RENAME TO "squad";
ALTER TABLE "team_drivers" RENAME TO "squad_drivers";
ALTER TABLE "squad_drivers" RENAME COLUMN "team_id" TO "squad_id";

Every link row is kept. The same holds when the model at the other end is renamed: driver_id follows driver, while the join table keeps its name.

A join table is followed only when it is unambiguous. Exactly one vanished table must have the same number of columns and keys to the same two tables once the rename is applied, and only one endpoint column may fit each end. Otherwise PormG asks, as it does for any other table or field. That happens, for example, with a self-referential relation whose from_… and to_… columns both changed.

Deleting a Model

Removing a model from models.jl plans a DROP TABLE for its table. That is destructive, so migrate asks first, or needs destructive = true when nothing can answer (see Destructive Operations Safety).

PormG manages tables, not the views, triggers and other objects built on them. It never drops one of those for you, and it does not drop a table while one of them still reads it. makemigrations fails instead with an InvalidMigrationError naming every object in the way, and writes no plan:

ERROR: Cannot drop table "driver": this migration drops it because no model declares it any more, but other objects still read it:
  - view "result_driver" reads "driver"
  - view "driver_standings" reads "driver"
PormG manages tables, not the views, triggers and other objects built on them, so it will not drop them for you: …

What counts as reading the table depends on the engine, because each one would lose the object differently:

• PostgreSQL drops a table with CASCADE, which PormG keeps for the foreign keys that point at it (see Statement Ordering). CASCADE would also remove everything else that depends on the table, without the plan saying so. PormG checks what PostgreSQL records as depending on the table: views and materialized views (and views built on those), rules on other tables, policies, functions with a SQL-standard body, a column of another table whose type is this table's row type (or an array of it), and another table's default that draws on this table's sequence. Two things are not seen. The first is a function whose body is a string, which covers PL/pgSQL (typically a trigger function) and LANGUAGE sql AS '…'. PostgreSQL records no dependency for such a body, so the function survives the drop and fails when it next runs. The second is an object created after makemigrations and before migrate: CASCADE still takes that one.
• SQLite would keep the objects, naming a table that no longer exists. Every later ALTER TABLE … RENAME in the database would then fail on them, and every table rebuild ends in one. PormG checks views that read the table, views built on those, INSTEAD OF triggers on them, and triggers on other tables that name any of them. It reads names, as the rebuild check below does, so a view with a column that shares the dropped table's name is refused too.
• MySQL has no CASCADE: PormG drops the table with the session's foreign-key checks off, so the keys of children the same plan alters dangle until their own steps drop them. PormG checks the views that read the table (information_schema.VIEW_TABLE_USAGE), and refuses a foreign key to it from a table the plan does not manage — an ignored table, an unmanaged model's, or one in another database.

Triggers on the dropped table go with it, on both engines, and never block the drop.

To get through, drop the listed views and triggers yourself and run makemigrations again. Re-create them against the new schema afterwards if you still need them. If you meant to rename the model rather than delete it, run makemigrations interactively and answer its rename question: a rename on its own keeps its views and triggers on both engines. On SQLite, a rename in the same migration as a table rebuild is refused when a view or trigger that rebuild carries names the renamed table (see the rebuild check below); apply the rename as a migration of its own first. With interactive = false, name the rename with renames = ["old_table" => "new_table"] instead (see Automation & CI/CD). Without a hint it plans the drop, unless the new model's columns match the old table's exactly, in which case it refuses to guess.

Statement Ordering

A migration's statements are applied in a fixed sequence of buckets, not in the order the plan file lists them:

1. CREATE TABLE (new models)
2. DROP TABLE
3. RENAME TABLE
4. RENAME COLUMN
5. Everything else — column alterations, ADD CONSTRAINT, DROP CONSTRAINT, DROP INDEX
6. Field CREATE INDEX

Within a bucket the order is stable but arbitrary — effectively alphabetical by table, because the plan is read back out of pending_migrations.jl by module binding name. It is not a dependency order, and PormG does not compute one.

That is safe rather than lucky, and it rests on three properties the test suite pins:

• PostgreSQL and MySQL never inline a foreign key in CREATE TABLE. Every key is a separate ALTER TABLE … ADD CONSTRAINT in bucket 5, so it runs after every CREATE TABLE. Two new tables that reference each other therefore apply in either order — which no dependency sort could achieve, because that is a cycle.
• DROP TABLE is DROP TABLE … CASCADE on PostgreSQL, so a parent can be dropped before its children are cleaned up. Because CASCADE also removes the children's constraints, PormG emits DROP CONSTRAINT IF EXISTS — otherwise removing a child's foreign-key field in the same migration that drops its parent would abort on a constraint the CASCADE had already taken. Those foreign keys are all the CASCADE is left to take: makemigrations refuses to drop a table while anything else depends on it (see Deleting a Model).
• SQLite suspends foreign-key enforcement for the whole migration (PRAGMA foreign_keys = OFF, restored by renewing the connection afterwards). Its inline REFERENCES clauses therefore constrain nothing while DDL is running, and SQLite resolves an FK's parent table lazily in any case.
• MySQL drops a table with foreign-key checks off (it has no CASCADE), so a parent can be dropped before its children's keys; each child's key is dropped by its own step in the same plan, and makemigrations refuses a key to it from a table the plan does not manage.

A renamed table is renamed before anything else touches it. When makemigrations asks whether a model with no table is a rename and you pick its former name, the plan holds ALTER TABLE "<old>" RENAME TO "<new>" in bucket 3, and every column change for that table is written against the new name — PormG reads the old table's live constraints and indexes at planning time, when the database still has nothing else. Tables whose foreign key points at the renamed one — including the renamed table itself, if it references itself — get no statement at all: both engines carry the constraint across a rename (PostgreSQL follows the table, SQLite rewrites the REFERENCES clause), so a rename on its own is not destructive. Because of this, makemigrations asks every table-rename question before any field-rename question. The exception is a table that also changes its key in the same migration, such as a different on_delete: that key is re-pointed at the new name, after the rename, and like any re-point it is destructive (see Changing a Foreign Key above).

Database-Specific Behavior

SQLite: Table Recreation

SQLite has limited ALTER TABLE support. It can rename tables/columns and add or drop plain columns, but it cannot change a column's type, modify nullability/UNIQUE/CHECK constraints in place, remove a foreign key (there is no ALTER TABLE ... DROP CONSTRAINT), or DROP COLUMN on a column that participates in a FOREIGN KEY, a UNIQUE constraint, the PRIMARY KEY, or any index.

To handle any of those changes, PormG automatically rebuilds the table from your model:

• Creates a new table with the desired schema.
• Copies existing data from the old table into it (surviving columns only).
• Re-creates the surviving indexes and foreign keys — an index referencing a dropped column is not re-created (see the expression-index note below).
• Drops the old table, renames the new one, and runs PRAGMA foreign_key_check to catch orphaned rows.
• Keeps the table's triggers, and the views and other tables' triggers that name it: those that would block the rename are dropped first, and all of them are re-created afterwards (see the triggers-and-views note below).

The rebuild is emitted as plain DDL that composes with the migration's transaction, so no data is lost and the remaining indexes, the constraints your models declare, triggers and views are preserved. A CHECK written by hand is not one of them — clauses no model can declare are the exception, see the last note below. This is what makes removing a foreign-key field or constraint, a UNIQUE column, a PRIMARY KEY column, or an indexed column work on SQLite even though DROP COLUMN/DROP CONSTRAINT alone cannot express it. Changes SQLite can do in place — adding a column, or dropping an ordinary column (not part of a FOREIGN KEY, UNIQUE, or PRIMARY KEY, and not referenced by an index) — use ALTER TABLE directly, without a rebuild.

This process is transparent to the user but may take longer on very large tables.

Adding a foreign key to an existing table. When the column already exists — a db_constraint = false key flipped back on, say — the rebuild renders the FOREIGN KEY clause from your model, so the constraint really is created, and PormG logs an @info noting that the table is being rebuilt, because that cost is worth knowing about on a large table. Re-pointing a key that already exists takes the same rebuild but logs nothing.

A foreign key arriving as a new column is created too, and usually without a rebuild. SQLite accepts an inline REFERENCES clause on ALTER TABLE ... ADD COLUMN provided the column is nullable and has no default, which is the ordinary shape of a new ForeignKey:

ALTER TABLE "result" ADD COLUMN "circuitid" INTEGER NULL REFERENCES "circuit"("circuitid") ON DELETE CASCADE;

Give the column a default and SQLite will not take the clause inline — PormG then adds the column and rebuilds the table from your model, which renders the FOREIGN KEY clause the same way CREATE TABLE does. PostgreSQL and MySQL are unaffected either way: each adds a separate named constraint with ADD CONSTRAINT.

┌ Warning: SQLite table rebuild will DROP an index PormG cannot re-create: it uses an
│ expression, a WHERE clause, an explicit COLLATE or a sort direction, and carries no
│ pormg:index marker, so no model declaration created it and none will bring it back.
│ Re-create it by hand after the migration if you still need it.
│   table = "driver"
│   index = "driver_surname_lower_idx"
│   dropped_columns = 1-element Vector{String}: …
│   definition = "CREATE INDEX \"driver_surname_lower_idx\" ON \"driver\" (lower(\"surname\"))"

┌ Warning: SQLite table rebuild will DROP clauses no model declaration can express: the table is
│ re-created from its model, which cannot hold them. Re-create them by hand after the migration if
│ you still need them.
│   table = "result"
│   clauses = 1-element Vector{String}: …

PostgreSQL: Advisory Locking

migrate() acquires a PostgreSQL session-level advisory lock before it writes anything — the history table and the configured extensions included — so a second migrator against the same database queues instead of interleaving its DDL. It waits up to lock_wait seconds (default 30) and then fails with an OperationalError naming the process that holds the lock, rather than proceeding unserialized. run_once data steps take the same lock. Running migrate() from several instances at boot: Deploying.

The key is the constant pormg::migrations, with no database or folder qualifier — deliberately. A PostgreSQL advisory lock is tagged with the database OID alongside the key, so the database is already the lock's namespace:

• Every configuration pointing at one database contends on one lock, including two different db/ config folders that resolve to the same server and database. Before #90 the key embedded the config folder name, so those two folders took two different locks and migrated one database concurrently.
• Two databases cannot collide on it, however identical the key, because their locks carry different database OIDs.

This is a guarantee about one database, not one server: migrate() against analytics does not block migrate() against billing on the same cluster, which is what you want.

PostgreSQL: Identity Columns

IDField() renders a PostgreSQL identity column, and generated_always = true makes it the stricter GENERATED ALWAYS AS IDENTITY — a column application code cannot supply a value for. Changing that declaration is a migration like any other, and PostgreSQL spells the three transitions differently:

The distinction between the first two matters because PostgreSQL rejects the wrong one: ADD GENERATED on a column that already is an identity fails with column "id" is already an identity column. Tightening a live key is therefore SET GENERATED ALWAYS, and it changes only the flavour — the sequence keeps its current value and no data is rewritten.

SQLite and MySQL have no equivalent. SQLite's identity is INTEGER PRIMARY KEY AUTOINCREMENT, which no ALTER can change, and MySQL's is AUTO_INCREMENT. Neither has an ALWAYS/BY DEFAULT distinction, so generated_always is a no-op on both, and a change to it correctly plans nothing.