zzstoatzz.io
prefect server in zig
1# migrations
2
3## alembic configuration
4
5- **location**: `_migrations/`
6- **config file**: `alembic.ini`
7- **filename format**: `YYYY_MM_DD_HHMMSS_REV_SLUG`
8- **template**: `script.py.mako` supports dialect-specific code via `${dialect}` variable
9
10## separate migration chains
11
12key design decision: **separate migration files per database dialect**
13
14```
15_migrations/versions/
16├── postgresql/ # 113 PostgreSQL migrations
17└── sqlite/ # 109 SQLite migrations
18```
19
20benefits:
21- different revision IDs but same semantic versioning timestamp
22- prevents cross-dialect conflicts
23- enables dialect-specific optimizations
24
25## migration notes tracking
26
27`MIGRATION-NOTES.md` documents schema changes with parallel revision IDs:
28
29```
30# Add `deployment_version` table
31SQLite: `bbca16f6f218`
32Postgres: `06b7c293bc09`
33
34# Add `labels` column to Flow, FlowRun, TaskRun, and Deployment
35SQLite: `5952a5498b51`
36Postgres: `68a44144428d`
37```
38
39creates natural merge conflicts if migrations diverge, keeping developers synchronized.
40
41## dialect-specific patterns
42
43### PostgreSQL migrations
44
45- uses native `postgresql.ENUM()` types
46- direct DDL operations (no batch mode needed)
47- supports `postgresql_using="gin"` for full-text search indexes
48- supports `postgresql_include` and `postgresql_where` clauses
49
50example:
51```python
52from sqlalchemy.dialects import postgresql
53
54deployment_status = postgresql.ENUM("READY", "NOT_READY", name="deployment_status")
55deployment_status.create(op.get_bind())
56op.add_column("deployment", sa.Column("status", deployment_status, ...))
57```
58
59### SQLite migrations
60
61- **batch mode required**: `render_as_batch=True` (SQLite requires table recreation for ALTER)
62- **PRAGMA foreign_keys management**: disabled during migrations
63- **transaction mode**: uses `SQLITE_BEGIN_MODE` context variable set to "IMMEDIATE"
64- **enum handling**: uses `sa.Enum()` which SQLite converts to VARCHAR
65
66example:
67```python
68with op.batch_alter_table("deployment", schema=None) as batch_op:
69 batch_op.add_column(
70 sa.Column("status", sa.Enum("READY", "NOT_READY", name="deployment_status"), ...)
71 )
72```
73
74## auto-generation logic (env.py)
75
76`include_object()` function filters out:
77- dialect-specific indexes that don't apply
78- trigram/GIN indexes on SQLite
79- functional indexes (asc/desc variants)
80- enum column mismatches between reflection and ORM
81
82## transaction handling
83
84- `transaction_per_migration=True` - each migration runs in its own transaction
85- SQLite disables foreign key constraints during migration
86- context variables manage SQLite transaction mode
87
88## thread safety
89
90`ALEMBIC_LOCK` prevents concurrent migration execution.
91
92## async support
93
94uses `run_async_from_worker_thread()` to run async migrations.