Generated SQL migrations for Python, TypeScript and Go projects¶
Migrax started as a tool for Java: it reads JPA entities and writes the SQL migrations that
bring the database in line with them. But the idea doesn't depend on Java. Every ORM knows
the tables it expects, and almost every ORM can print them as CREATE TABLE statements.
That SQL is all Migrax needs. Keep it in a schema file, and Migrax compares it with what the existing migrations build, writes the next numbered migration with its rollback script, and applies it. This article follows a small Python project through its first changes, then shows the same loop with Drizzle in TypeScript and GORM in Go.
Tested with
Migrax 0.17.2-rc.1, PostgreSQL 17, SQLAlchemy 2.1.6 (Python 3.13), Drizzle ORM 0.45.4 with drizzle-kit 0.31.11 (Node.js 24), GORM 1.31.2 (Go 1.25). Docker runs the throwaway databases. Every output below was copied from a real run.
The idea in one picture¶
models.py --export--> db/schema.sql --migrax generate--> db/migrations/0002_....sql
(your ORM) (the tables + rollback/0002_....sql
you want)
The schema file says what the tables should look like. The migrations say how to get there, one reviewed step at a time. Both are committed. Migrax never touches your models, and your application never needs Migrax at runtime: the migrations are plain SQL files.
A Python shop with SQLAlchemy¶
Two tables, customers and their orders:
from datetime import datetime
from decimal import Decimal
from sqlalchemy import ForeignKey, Numeric, String, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
pass
class Customer(Base):
__tablename__ = "customer"
id: Mapped[int] = mapped_column(primary_key=True)
email: Mapped[str] = mapped_column(String(150), unique=True)
name: Mapped[str] = mapped_column(String(100))
created_at: Mapped[datetime] = mapped_column(server_default=func.now())
class Order(Base):
__tablename__ = "orders"
id: Mapped[int] = mapped_column(primary_key=True)
customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id"), index=True)
total: Mapped[Decimal] = mapped_column(Numeric(12, 2))
status: Mapped[str] = mapped_column(String(20), server_default="new")
SQLAlchemy compiles a table into PostgreSQL's CREATE TABLE without connecting to anything.
A short script turns the models into the schema file:
"""Prints the tables of models.py as SQL, for Migrax's schema file."""
from sqlalchemy.dialects import postgresql
from sqlalchemy.schema import CreateIndex, CreateTable
from models import Base
dialect = postgresql.dialect()
for table in Base.metadata.sorted_tables:
print(f"{str(CreateTable(table).compile(dialect=dialect)).strip()};\n")
for index in sorted(table.indexes, key=lambda i: i.name):
print(f"{CreateIndex(index).compile(dialect=dialect)};\n")
Migrax reads its settings from the environment. The schema file is one more of them:
export MIGRAX_DATABASE_URL=jdbc:postgresql://localhost:55432/shop
export MIGRAX_DATABASE_USER=shop
export MIGRAX_DATABASE_PASSWORD=secret
export MIGRAX_LOCATIONS=filesystem:db/migrations
export MIGRAX_SCHEMA_FILE=db/schema.sql
export MIGRAX_DRIVERS=drivers # holds postgresql-42.7.14.jar
The first migration¶
$ python export_schema.py > db/schema.sql
$ migrax generate
Starting a throwaway postgresql database (postgres:17-alpine)...
Read db/schema.sql: 2 table(s).
No snapshot yet: using the current database schema as the baseline.
Created db/migrations/0001_initial.sql:
- create table customer
- create table orders
- add index ix_orders_customer_id on orders
- add foreign key orders_customer_id_fkey on orders
Rollback script written to db/migrations/rollback.
Review the SQL, then run 'migrax migrate'.
To read the file, Migrax runs it on a throwaway PostgreSQL in Docker, in the same version as your server, and reads the tables back. So whatever the database accepts, Migrax understands, and what it doesn't accept fails here, not in production. The migration it writes:
CREATE TABLE customer (id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL, email varchar(150) NOT NULL, name varchar(100) NOT NULL, created_at timestamp(6) DEFAULT now() NOT NULL, CONSTRAINT customer_pkey PRIMARY KEY (id), CONSTRAINT uk_customer_email UNIQUE (email));
CREATE TABLE orders (id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL, customer_id integer NOT NULL, total numeric(12,2) NOT NULL, status varchar(20) DEFAULT 'new' NOT NULL, CONSTRAINT orders_pkey PRIMARY KEY (id));
CREATE INDEX ix_orders_customer_id ON orders (customer_id);
ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customer (id);
Two details differ from SQLAlchemy's own SQL, and both are on purpose. SQLAlchemy writes
SERIAL; Migrax writes identity columns, which hand out ids the same way. And constraints
get names (uk_customer_email), so later migrations can refer to them.
$ migrax migrate
Applying migration '0001_initial.sql'...
Successfully applied migration '0001_initial.sql'.
Applied 1 migration(s); 1 total in db/migrations.
The application saves its first customer, Ada Lovelace, with one order of 42.50.
Adding a column¶
A phone number for customers:
Export again. Before writing anything, migrax check tells you what has no migration yet:
$ python export_schema.py > db/schema.sql
$ migrax check
Starting a throwaway postgresql database (postgres:17-alpine)...
Read db/schema.sql: 2 table(s).
1 change(s) in db/schema.sql have no migration:
- add column customer.phone
Run 'migrax generate' and commit the result.
$ echo $?
2
Exit code 2 means "changes without a migration", which is the line to put in CI. Then:
$ migrax generate
Created db/migrations/0002_add_customer_phone.sql:
- add column customer.phone
Rollback script written to db/migrations/rollback.
Review the SQL, then run 'migrax migrate'.
No database started this time: Migrax had read this version of the file a moment earlier and
cached what it found in .migrax/schema-model.json. The migration and its rollback script:
-- Rollback for 0002_add_customer_phone.sql. 'migrax rollback' runs it.
-- Note: deletes data written to customer.phone since the migration ran.
ALTER TABLE customer DROP COLUMN phone;
Renaming a column, keeping the data¶
name becomes full_name. Compared naively, that is a dropped column and a new one, and
Ada's name would be gone. Migrax notices the likely rename. In a terminal it asks; in a script,
where nobody can answer, it stops before dropping anything:
$ migrax generate
Possible rename: customer.name to customer.full_name. To keep the data, rerun with --rename customer.name=full_name.
2 change(s) for postgresql:
1. add column customer.full_name
ALTER TABLE customer ADD COLUMN full_name varchar(100) NOT NULL;
2. drop column customer.name [DESTRUCTIVE]
ALTER TABLE customer DROP COLUMN name;
error[MXE108]: 1 destructive change(s) detected (marked [DESTRUCTIVE] above).
Review them, then run 'migrax generate --allow-destructive'. If a column or table was renamed, pass --rename table.old=new or --rename-table old=new instead.
$ migrax generate --rename customer.name=full_name
Created db/migrations/0003_rename_customer_name.sql:
- rename column customer.name to full_name
Rollback script written to db/migrations/rollback.
db/migrations/0003_rename_customer_name.sql, statement 1: warning MX008
ALTER TABLE customer RENAME COLUMN name TO full_name
Application instances still running the old code use the old name and will fail.
Fix: Rename in steps: add the new name, write to both, switch reads, then remove the old name ('migrax generate --zero-downtime' does this). Or deploy during a maintenance window.
Review the SQL, then run 'migrax migrate'.
The warning is about deployment, not data: old instances that still query name break when
the column is renamed. After migrax migrate, a three-line script that lists the customers
through SQLAlchemy reads the row back under its new name:
Proving the migrations¶
The migrations now describe the schema in three steps. migrax verify checks that they
really build what the schema file says, on an empty throwaway database of your server's
version, and that every rollback script works:
$ migrax verify
Starting a throwaway postgresql database (postgres:17-alpine)...
Applying migration '0001_initial.sql'...
...
Applied 3 migration(s) to a fresh postgresql database.
Schema matches the schema file (structural comparison).
Rolling back migration '0003_rename_customer_name.sql'...
...
Rollback scripts: ok (3 rolled back and re-applied).
Verified.
And migrax status shows where the real database is:
$ migrax status
Migrations in db/migrations for jdbc:postgresql://localhost:55432/shop
[X] 0001_initial.sql applied 2026-10-26 16:02:43
[X] 0002_add_customer_phone.sql applied 2026-10-26 16:02:51
[X] 0003_rename_customer_name.sql applied 2026-10-26 16:02:58
3 applied.
Undoing the rename takes one command. In a terminal, rollback asks before it changes
anything; in a script, pass --yes:
$ migrax rollback --yes
Rolling back 1 migration(s), newest first:
0003_rename_customer_name.sql
Rolling back migration '0003_rename_customer_name.sql'...
Rolled back migration '0003_rename_customer_name.sql'.
Rolled back 1 migration(s).
The column is called name again, with Ada's name still in it. migrax migrate applies the
rename once more.
The same loop in TypeScript, with Drizzle¶
drizzle-kit export prints a Drizzle schema as SQL, without a database:
$ npx drizzle-kit export --dialect=postgresql --schema=./schema.ts > db/schema.sql
$ migrax generate
Starting a throwaway postgresql database (postgres:17-alpine)...
Read db/schema.sql: 2 table(s).
No snapshot yet: using the current database schema as the baseline.
Created db/migrations/0001_initial.sql:
- create table customer
- create table orders
- add index orders_customer on orders
- add foreign key orders_customer_id_customer_id_fk on orders
Rollback script written to db/migrations/rollback.
Review the SQL, then run 'migrax migrate'.
Adding phone: varchar("phone", { length: 30 }) to the customer table and exporting again
gives 0002_add_customer_phone.sql with the same single ALTER TABLE customer ADD COLUMN
as in Python. Drizzle's own names for indexes and foreign keys are kept as they are.
The same loop in Go, with GORM¶
GORM can't print its models as SQL, but its AutoMigrate creates them. Let it create the
tables in a database of their own (shop_dev, never the one Migrax migrates), and copy them
from there with pg_dump:
type Customer struct {
ID uint `gorm:"primaryKey"`
Email string `gorm:"size:150;not null;uniqueIndex"`
Name string `gorm:"size:100;not null"`
CreatedAt time.Time `gorm:"not null;default:now()"`
}
type Order struct {
ID uint `gorm:"primaryKey"`
CustomerID uint `gorm:"not null;index"`
Customer Customer
Total float64 `gorm:"type:numeric(12,2);not null"`
Status string `gorm:"size:20;not null;default:'new'"`
}
$ go run . # db.AutoMigrate(&Customer{}, &Order{}) on shop_dev
$ pg_dump --schema-only --no-owner --no-privileges shop_dev > db/schema.sql
$ migrax generate
Starting a throwaway postgresql database (postgres:17-alpine)...
Read db/schema.sql: 2 table(s).
No snapshot yet: using the current database schema as the baseline.
Created db/migrations/0001_initial.sql:
- create table customers
- create table orders
- add index idx_orders_customer_id on orders
- add foreign key fk_orders_customer on orders
Rollback script written to db/migrations/rollback.
Review the SQL, then run 'migrax migrate'.
Why not let AutoMigrate change the real database directly? It is fine in development, and
GORM is upfront about its limits: its
migration docs (as of October 2026) say it "WON'T
delete unused columns to protect your data" and that "at some point you may need to switch to
a versioned migrations strategy". With the dump in between, AutoMigrate stays where it is
convenient, and production gets reviewed, numbered migrations with rollback scripts, renames
that keep their data, and drops that need your confirmation.
In CI¶
Two lines catch a model change that has no migration:
python export_schema.py > db/schema.sql # or: npx drizzle-kit export ..., pg_dump ...
migrax check # exit code 2: a change has no migration yet
Commit db/schema.sql and the .migrax/ folder with every migration. While the schema file is
unchanged, migrax check reads the saved model and needs no Docker at all. Migrax itself runs
on Java 17 or newer; a Python, Node or Go build that has no Java can use the container image
ghcr.io/fsmutimeer/migrax instead.
What to know¶
- Read the migration before you commit it. It is plain SQL, generated, but yours.
- Views, functions, triggers and sequences don't belong in the schema file. Keep them in
repeatable migrations (
R__*.sql); identity columns work in the file. - Reshaping data is yours to write. Migrax moves the structure and keeps values through
renames. Splitting
full_nameinto first and last name needs a rule only you know: a migration written withmigrax new, between two generated ones. - MySQL and MariaDB work the same way. Compile with SQLAlchemy's
mysqldialect, or dump withmysqldump --no-data --skip-comments.
Summary¶
- Your ORM already describes the schema. Export it as SQL into a schema file: a short script
for SQLAlchemy,
drizzle-kit exportfor Drizzle,pg_dumpafterAutoMigratefor GORM. migrax generatewrites the next migration and its rollback script, asks about renames, and refuses to drop data without your confirmation.migrax checkin CI catches a model change without a migration, andmigrax verifyproves the migrations build exactly the schema in the file.- The migrations are plain SQL. Your application runs without Migrax.
The full reference, with MySQL and more ORMs: Schema file from your ORM.