Managing database migrations with dbmate
This skill provides comprehensive instructions for managing database migrations in this project using dbmate.
When creating new migrations, always follow the /migrate-new workflow. The primary tool for creating a migration is:
dbmate --migrations-dir db/migrations/<engine> new "migration_name"
Replace <engine> with sqlite, postgres, or mysql.
Migrations are stored in db/migrations/<engine>/ as .sql files. They follow a specific format:
Each migration file MUST have two sections:
-- migrate:up
-- SQL statements to apply the change
-- migrate:down
-- SQL statements to reverse the change
[!IMPORTANT] DO NOT wrap your SQL in
BEGIN/COMMITblocks.dbmateautomatically wraps each migration in a transaction. Adding manual transaction blocks will cause errors and may lead to inconsistent database states.
IF NOT EXISTS or similar where possible to make migrations safer (e.g., CREATE TABLE IF NOT EXISTS ...).db/schema/ manually. They are auto-generated.TEXT, JSONB).LONGTEXT for JSON-like data).ALTER TABLE support.For development, use ./dev-scripts/migrate-all.py. This script:
db/schema/.