Skip to content
Sapporta

Run migrations in deployed environments

View as Markdown

Sapporta treats schema authoring and migration application as separate release stages. Development generates and reviews SQL. Deployment applies those committed artifacts to durable storage before compatible application code serves requests.

The release gives one process ownership of the exact durable SQLite file during migration. Other writers remain stopped until the new application is ready.

Prepare the artifact in development

Section titled “Prepare the artifact in development”

After changing a schema file, generate a named migration and read the SQL. A rename prompt or destructive statement is a data decision, not a build detail.

Terminal window
SAPPORTA_DATA_DIR=data pnpm --filter ./packages/api db:generate --name add_task_events
# Review packages/api/migrations/*.sql and commit it with the schema change.
SAPPORTA_DATA_DIR=data pnpm --filter ./packages/api db:check

Every db:* script reads SAPPORTA_DATA_DIR from the environment and never loads .env.development. Pass the value that file records, so the commands work against the database pnpm dev opens.

Do not generate fresh SQL during deployment. The deployed artifact must be the same artifact reviewed with the application change.

Order the release around the database

Section titled “Order the release around the database”

For the task-events release, use this sequence:

  1. Quiesce every process that can write the database.
  2. Create and identify a restorable backup of the durable SQLite database.
  3. Run one migration job against that database, with the same SAPPORTA_DATA_DIR as the server.
  4. Start the new application code; its startup guard checks migration readiness.
  5. Run smoke tests and resume traffic.
Terminal window
export SAPPORTA_DATA_DIR=/srv/tasks/data
pnpm --filter ./packages/api db:migrate
pnpm start

The migration job stops when SAPPORTA_DATA_DIR is unset, so a missing setting cannot migrate some other database. The server prints Database: <path> when it starts, and a readiness failure names the database it checked.

The generated container follows the same ordering: its image sets SAPPORTA_DATA_DIR=/app/data, and its entrypoint runs the API package’s local Drizzle migration command and starts dist/boot.js only after migration succeeds.

If migration or readiness fails, do not start the new code. Restore or roll forward according to the reviewed SQL and backup plan. Restarting the same release does not make a destructive migration reversible, and rolling back JavaScript alone does not restore removed columns or transformed data.

db:check may still be used during development to check Drizzle migration history. It is not the live database readiness check. The startup guard is the authority for pending, missing, or modified migration artifacts.

Section titled “Related reference”