Migrations & Seeding

Migrations & Seeding

Changing a model changes the shape your code expects. Changing a table changes the shape the database has. A migration is the recorded, ordered, reviewable step that moves the second to match the first.

Migrations are driven from the DatabaseManager your application already runs on. There is no second configuration file, and no dotted path to keep in step by hand.

Why you need migrations at all

DatabaseManager.init() can call generate_schemas(safe=True) on every startup. safe=True means “create tables that do not exist”. It issues no ALTER TABLE, ever.

So adding a field to a model and restarting gives you a running application whose code expects a column the table does not have. The failure appears at query time, as an OperationalError naming a column you can plainly see in your model file. That is the moment most people discover they needed migrations two weeks ago.

Use generate_schemas for tests and throwaway SQLite files. Use migrations for anything holding data you would be sad to lose, and turn generate_schemas off once you have them:

DatabaseConfig(url=..., generate_schemas=False)

Leave it on alongside migrations and you get tables created outside the migration history, which a later make then sees as new and generates a migration that fails to apply. It also has every process run DDL at once: an app, a worker and a scheduler sharing one SQLite file all raise “database is locked” on boot.

The setup

One description of how the project connects, shared by the application and its migrations.

app/database.py

from sillo.record import DatabaseConfig, DatabaseManager


def database() -> DatabaseManager:
    manager = DatabaseManager(DatabaseConfig(url=..., generate_schemas=False))
    manager.register_models("database.models").set_migrations("database.migrations")
    return manager

register_models must list every module containing models. Discovery is by module scan, so a model in a file nobody imports is invisible to the migration generator and its table silently never gets created.

set_migrations says where migration files are written. It defaults to database.migrations. Without a migrations package the app counts as unmigrated, and every command reports “no migrations” while doing nothing.

The commands

sillo.record.commands provides the operations as plain async functions, and your project decides what to call them:

a management script

import asyncio

from sillo.record.commands import init, make, migrate, plan, rollback

from app.database import database

asyncio.run(init(database()))              # once — create the migrations package
asyncio.run(make(database(), "add_slug"))  # after a model change
asyncio.run(migrate(database()))           # apply what is pending
asyncio.run(plan(database()))              # what would run, without running it
asyncio.run(rollback(database(), "0003_add_slug"))

Each takes the manager. A resolved configuration mapping, or a dotted path to one, is also accepted for tooling that only has that.

make diffs your models against the recorded migration state and writes a Python file to the migrations package. migrate runs the pending ones in order and records each in the tortoise_migrations table.

rollback names a target, not a number of steps

There is no “one step back”. Name the migration to stop at (rollback(database(), "0003_add_slug")) or "zero" to unapply everything. A bare name is qualified with the app label for you.

Always read the generated migration

The generated file is ordinary Python. Open it before applying it. The diff engine is good at additive changes and unreliable at others:

A renamed column is usually detected as a drop plus an add, which is a data-destroying operation dressed as a rename. Edit it into an ALTER TABLE ... RENAME COLUMN by hand.

A changed column type is emitted with no USING clause, which fails on PostgreSQL when the conversion is not implicit.

A new non-null column without a default fails on any table with existing rows. Add it nullable, backfill, then add the constraint, three migrations, not one.

sql() shows you what a migration will execute without executing it:

for statement in await sql(database(), "0003_add_slug"):
    print(statement)

SQLite cannot do most ALTER TABLE operations

SQLite supports adding a column and renaming a table. It cannot drop a column, change a type, or add a constraint. The engine works around this by recreating the table and copying data, which is slow, locks the table, and drops anything the recreation did not know to preserve.

If you develop on SQLite and deploy on PostgreSQL, your migrations are being tested against the wrong engine. Run them against a real PostgreSQL instance in CI before they reach production.

Adopting a database that already has tables

A project that ran on generate_schemas has the tables but no history. Write the migration that describes them, then record it without running it:

await make(database(), "initial")
await migrate(database(), fake=True)

The schema is now under migration control, and the next model change is an alteration of a known table rather than a table the engine has never seen.

Migrations in a deployment

Run migrations as a separate step before the new application version starts, not from application startup code. Starting three replicas that each try to migrate produces three concurrent schema changes and, on a good day, two failures.

a deployment step

sillo db:migrate && exec uvicorn myapp.app:app --host 0.0.0.0 --port 8000

That is fine for a single-instance deployment. For rolling deployments, make the step a job that runs once and gates the rollout, and keep each migration compatible with both the old and new application version. Add columns before the code that writes them, drop them a release after the code that read them is gone.

MigrationHelper

The commands are functions over MigrationHelper, which you can use directly when you want one object rather than repeated arguments:

from sillo.record import MigrationHelper

helper = MigrationHelper(database(), app="models")
await helper.make("add_posts")
await helper.upgrade()

Every method opens a connection, does its work and closes again, so it is safe to call from a short-lived script. That closing matters: an open connection keeps the event loop alive, and a script that finishes its migration and then hangs forever is usually this rather than a deadlock in the migration.

Seeders

Seeder collects rows and inserts them.

seeding

from sillo.record import Seeder

seeder = Seeder(db_manager)
seeder.seed(User, [
    {"email": "admin@example.com", "name": "Admin"},
    {"email": "user@example.com", "name": "User"},
])
seeder.seed(Post, [
    {"title": "Hello World", "body": "First post", "user_id": 1},
])

count = await seeder.run()

seed() returns the seeder, so calls chain. run() inserts in the order the calls were made (which is how you satisfy foreign keys, by seeding parents before children) and returns the number of rows created.

Rows are created with Model.create(), so casts, mutators, and ValidatesBeforeSaveMixin all apply. Note that lifecycle events do not fire, for the reason described in Scopes & Events.

Two limitations worth knowing:

run(batch_size=100) accepts batch_size and ignores it. The implementation is one create() per record. Seeding ten thousand rows is ten thousand round trips; use Model.bulk_create for that.

Seeder is not idempotent. Running it twice inserts everything twice, or fails on a unique constraint. Make production seeds safe to re-run:

an idempotent seed

async def seed_defaults():
    await Role.get_or_create({"label": "Administrator"}, slug="admin")
    await Role.get_or_create({"label": "Member"}, slug="member")

The db_manager argument is stored and never used, so passing None works. Pass the manager anyway. The parameter may become meaningful.

Fixtures

FixtureLoader reads JSON and JSONL files and inserts them.

loading fixtures

from sillo.record import FixtureLoader

loader = FixtureLoader("fixtures/")
inserted = await loader.load_all()      # every file, in sorted order
await loader.load("users")              # or just one

fixtures/
  01_users.json     [{"email": "a@b.com", "name": "Alice"}, ...]
  02_articles.jsonl {"title": "First"}
                    {"title": "Second"}

Files load in sorted order, so a numeric prefix is how you make parents load before children. Each file is inserted inside a transaction, so a row that violates a constraint leaves that table untouched rather than half-populated.

The model is resolved from the filename, ignoring case and a trailing plural: users.json finds User, categories.jsonl finds Category. When the name does not match, map it explicitly:

FixtureLoader("fixtures/", models={"people": User})

Resolution needs an open database

Model lookup reads the registry the ORM populates on init. Loading fixtures before the database is open raises LookupError listing “none” as the known models. That is the registry being empty, not your filename being wrong.

Choosing between the three

Tool	Use for	Runs when
Migrations	Schema changes	Deployment, once
Seeder / fixtures	Reference data: roles, plans, countries	Deployment or first boot
Factory	Randomised test data	Test setup

The distinction that matters: reference data is part of the application’s definition and belongs in version control next to the migrations. Test data is disposable and belongs in the test suite. Mixing them gives you production databases full of “Test User”.

What not to do

Do not rely on generate_schemas to evolve a schema. It creates and never alters.

Do not leave generate_schemas on once you have migrations. It creates tables the migration history does not know about.

Do not apply a generated migration unread. Renames appear as drop-plus-add.

Do not run migrations from application startup. Multiple replicas will race.

Do not test migrations only on SQLite if you deploy on PostgreSQL.

Do not make production seeds non-idempotent. Use get_or_create.

Do not commit the database file. Commit the migrations instead. They are the schema’s source of truth.

Performance notes

Seeder.run() is one INSERT per record with a full round trip each. At ten milliseconds of latency, ten thousand rows takes a hundred seconds. bulk_create with batch_size=500 takes about twenty statements.

On PostgreSQL, DDL is transactional and a failed migration leaves nothing behind. On MySQL, DDL causes an implicit commit, so a migration that fails halfway leaves the schema partially changed. Write MySQL migrations so each step is independently safe.

Adding an index on a large PostgreSQL table locks it for writes for the duration. Use CREATE INDEX CONCURRENTLY in a hand-edited migration, and note that it cannot run inside a transaction.

API reference

Name	Signature	Notes
commands.init	(database, *, app="models")	Creates the migrations package. Safe to re-run
commands.make	(database, name=None, *, app="models")	Writes a migration from model changes
commands.migrate	(database, *, target=None, fake=False, app="models")	Applies what is pending
commands.rollback	(database, target, *, fake=False, app="models")	target is required: no implicit one step
commands.plan	(database, *, target=None, app="models") -> list[str]	Shows without running
commands.sql	(database, migration, *, backward=False, app="models") -> list[str]	Needs a single app
DatabaseManager.register_models	(*modules) -> DatabaseManager	Chains
DatabaseManager.set_migrations	(module) -> DatabaseManager	Defaults to database.migrations
DatabaseManager.orm_config	(migrations=None) -> dict	The resolved mapping, for tooling outside sillo
MigrationHelper	(database, *, app=None)	Takes a manager, a mapping, or a dotted path
Seeder	(db_manager)	.seed(model, records), await .run()
Seeder.run	(*, batch_size=100) -> int	batch_size is ignored
FixtureLoader	(directory, *, models=None)	.load_all(), .load(name)

Related

• Record Overview: configuration, and why generate_schemas is not enough
• Models & Mixins: bulk_create for fast seeding
• Transactions & Factories: factories for test data
• Scopes & Events: why seeding does not fire lifecycle events
• Startup & Shutdown: why migrations do not belong in a startup hook