Migrations

Your site's SQLite database is shaped only by migrations: SQL files shipped in the release under private/migrations/. Handler code cannot create or alter tables (tenant SQL is data-only, see Context). Migrations also feed the schema snapshot that generates the table objects of the Database SDK.

Files

private/migrations/
  0001_create_posts.sql
  0002_add_post_status.sql

• Name: <number>_<description>.sql. The number starts at 1, has no gaps and no duplicates (0001, 0002, ...; zero-padding is for your sorting only, the value is parsed as a number). Anything else in the directory, any non-regular file, or a bad name rejects the deploy (invalid or duplicate migration filename).
• Files are UTF-8 text. A file may hold several statements.
• Limits: at most 128 migrations, 256 KiB per file, 2 MiB in total, and a 15 second execution budget for the whole batch.
• Never edit a migration that has been deployed. The site database remembers the highest applied number (SQLite user_version), and only files with a higher number are applied. Add a new numbered file instead. Migrations are forward-only; there is no down migration.

-- 0001_create_posts.sql
CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('draft', 'published')),
  published_at TEXT
);
CREATE INDEX posts_status ON posts(status);

Seed data belongs in a migration too (an INSERT in a migration runs once), not in request-time "bootstrap" code.

When migrations run

Two separate steps happen on every deploy that includes migrations:

1. Validation at upload. After the manifest is resolved, the server applies all migrations in order, in one transaction, to a fresh in-memory SQLite database. Any SQL error rejects the upload (invalid migrations: <file name>) and nothing is created. This is also how the schema snapshot is built.
2. The live migration, during the deploy. After the release record is created and before it becomes ready, the pending migrations (numbers above the site database's current version) are applied to the site's real database. This happens at deploy time, whether or not you activate: a staged release (activate=false) has already changed the live database when the deploy call returns. Activation only switches which release serves requests.

Status: documentation conflict. The design documents and the blog README say migrations run when a release is activated. The deploy code (DeploymentService::deploy_report) runs them during the deploy, before the release is ready, and activation does not run migrations. These pages follow the code. Consequence: the release that is currently active keeps serving against the migrated schema until you activate the new one, so write migrations that the old code can still run against (add columns and tables first, remove or rename later).

How the live migration runs (crates/microhost-cli/src/serve_migrations.rs):

• The site must be active (not paused). Live executions of the site are blocked and drained first (up to 10 seconds, otherwise the deploy fails and the site is unblocked).
• A backup is taken before changes (VACUUM INTO data/db.pre-migration-<stamp>-<n>.sqlite).
• All pending files run in one BEGIN IMMEDIATE transaction. Statements that manage transactions (BEGIN, COMMIT, SAVEPOINT) are denied inside migrations. user_version is set to the highest applied number, then the transaction commits. Any failure rolls the whole batch back and the release is marked failed.
• The resulting version must be inside the release's schemaCompat range, otherwise the deploy fails (candidate release does not support resulting database schema N). Remember the host default range is 0 to 5.
• The database grant is not needed to migrate; it is needed by your handlers afterwards.

What a migration may contain: CREATE, ALTER and DROP of tables and indexes, data statements (INSERT, UPDATE, DELETE), and the same function allowlist as tenant SQL. The migration authorizer also permits triggers and views. PRAGMA, ATTACH, ANALYZE, virtual tables, temporary objects and tables named _mh_* are refused. Table objects and the schema snapshot only cover tables.

Because the snapshot is built with plain SQLite, SQL that the snapshot accepts can still be refused by the live migration (for example a PRAGMA); in that case the deploy fails at step 2 with a migration error.

The schema snapshot

At upload the server reads the migrated in-memory database and seals db-schema.json into the release (its SHA-256 is recorded in resolved-manifest.meta.json; the file name is reserved). Contents, with formatVersion: 1:

• every user table (not views, not sqlite_* tables), sorted by name;
• each column in order: name, declaredType, nullable (false when NOT NULL or part of the primary key), primaryKey, and, when a simple CHECK (column IN ('a', 'b')) is unambiguous, enumValues.

microhost/db generates its table objects from this snapshot, once per release, and caches by release checksum. Consequences:

• A table must come from migrations to be importable. A table you create by another route (for example through the admin database console) has no table object. Use ctx.db or sql for it.
• The snapshot is part of the immutable release. A rollback or platform upgrade never recomputes it. A release created before snapshots existed has no table exports; redeploy it. Raw ctx.db keeps working for it.
• The snapshot describes your migrations, not the live database. If someone changes the live schema by other means, the objects can drift from reality.

An administrator can inspect the live schema (GET /admin/api/v1/sites/{id}/database/schema) and run queries in the site database console.

Rollback

Activating an older ready release is a rollback. It is refused unless the database's current version is inside that older release's schemaCompat. Migrations are never undone, so a rollback after a destructive migration runs old code against the new schema. The pre-migration backup files stay in the site's private data/ directory for operator recovery.