Database Migrations#

Phoenix uses Alembic for schema migrations, targeting both SQLite (default for local/notebook use) and PostgreSQL (production deployments). Migration scripts live in src/phoenix/db/migrations/versions/.

How Migrations Run#

migrate(engine, error_queue) in src/phoenix/db/migrate.py loads the Alembic config from alembic.ini, sets the migrations path, and calls command.upgrade(alembic_cfg, "head") 1. migrate_in_thread(engine) wraps this in a Thread + SimpleQueue for notebook environments where the main thread cannot block; any exception is re-raised as PhoenixMigrationError 1. Migrations run automatically at Phoenix startup before the server accepts requests.

src/phoenix/db/migrations/env.py is the Alembic environment config. It supports both async and sync SQLAlchemy engines, detects an already-running event loop, and passes a pre-existing connection via config.attributes["connection"] when called from migrate() 2. compare_type=True and transactional_ddl=True are set for proper DDL handling 2.

batch_alter_table: The SQLite/PostgreSQL Compatibility Pattern#

SQLite does not support ALTER TABLE for most DDL operations (dropping/renaming columns, adding constraints). Alembic's batch_alter_table works around this by reconstructing the entire table in a temporary copy. All Phoenix migrations that modify existing tables use this pattern.

A typical migration block:

with op.batch_alter_table("users") as batch_op:
    batch_op.add_column(sa.Column("auth_method", sa.String, nullable=True))

with op.batch_alter_table("users") as batch_op:
    batch_op.execute(...) # data backfill
    batch_op.alter_column("auth_method", nullable=False)
    batch_op.drop_constraint("old_check", type_="check")
    batch_op.create_check_constraint("new_check", "...")
    batch_op.drop_index("old_index")

The two-batch pattern (separate batch for data backfill) is required for SQLite: you cannot combine add_column (nullable), UPDATE, and alter_column (non-nullable) in a single batch because the reconstructed table won't have data yet 3.

Representative examples:

JSONB Compatibility#

Migrations that use JSON columns define a custom JSONB type with SQLAlchemy's @compiles so the column works on both backends: postgresql.JSONB() on Postgres and a plain JSONB string-type on SQLite 4 5.

Constraint Naming#

Phoenix follows a {type_prefix}_{table}_{columns} naming convention (e.g., pk_users, fk_span_annotations_span_rowid_spans, uq_span_annotations_name_span_rowid_identifier). Names exceeding PostgreSQL's 63-character identifier limit are shortened manually — e.g., uq_document_annotations_name_span_rowid_document_pos_identifier instead of the auto-generated (longer) variant 6.

Test Infrastructure#

Each migration has a paired integration test in tests/integration/db_migrations/.

Test package (__init__.py):

Test file pattern (see test_db_schema_6a88424799fe_update_users_with_auth_method.py, test_db_schema_2f9d1a65945f_annotation_config_migration.py, test_db_schema_a20694b15f82_cost.py):

Each test file defines _DOWN / _UP revision IDs, an abstract base class DBSchemaComparisonTest with _get_current_schema_info(db_backend) and _get_upgraded_schema_info(db_backend), and concrete subclasses per table. The test sequence is:

1. _verify_clean_state — start from scratch
2. _up to _DOWN — reach the pre-migration revision
3. Assert actual schema == expected pre-migration schema
4. _up to _UP — apply the migration under test
5. Assert actual schema == expected post-migration schema
6. _down to _DOWN — verify rollback
7. Assert actual schema == pre-migration schema again

Backend-Specific Index Differences#

PostgreSQL materializes primary key and unique constraints as real indexes visible in pg_index, while SQLite creates autoindex entries (e.g., sqlite_autoindex_users_1). The expected schema sets therefore branch on db_backend 8 9:

if db_backend == "postgresql":
    index_names.update({"pk_users", "uq_users_oauth2_client_id_oauth2_user_id"})
elif db_backend == "sqlite":
    index_names.update({"sqlite_autoindex_users_1"})
else:
    assert_never(db_backend)

assert_never from typing_extensions is used to get exhaustiveness checking — a compile-time guard against unhandled backends 10.

New-Table Tests#

When a migration creates a brand-new table (rather than altering an existing one), _get_current_schema_info returns None (table doesn't exist yet), and _get_table_schema_info also returns None when the table is absent — making the assert initial_info == current_info check still valid 11.

Key Source Files#