dbmate

dbmate Skill

This skill provides comprehensive instructions for managing database migrations in this project using dbmate.

Creating Migrations

When creating new migrations, always follow the /migrate-new workflow. The primary tool for creating a migration is:

dbmate --migrations-dir db/migrations/ new "migration_name"

Replace with sqlite, postgres, or mysql.

Writing Migrations

Migrations are stored in db/migrations// as .sql files. They follow a specific format:

Structure

Each migration file MUST have two sections:

-- migrate:up -- SQL statements to apply the change

-- migrate:down -- SQL statements to reverse the change

Transaction Handling

[!IMPORTANT] DO NOT wrap your SQL in BEGIN/COMMIT blocks. dbmate automatically wraps each migration in a transaction. Adding manual transaction blocks will cause errors and may lead to inconsistent database states.

SQL Guidelines Idempotency: Use IF NOT EXISTS or similar where possible to make migrations safer (e.g., CREATE TABLE IF NOT EXISTS ...). Schema Files: NEVER edit files in db/schema/ manually. They are auto-generated. Engine-Specifics: Ensure you tailor the SQL for the specific database engine. PostgreSQL: Use appropriate types (e.g., TEXT, JSONB). MySQL: Use compatible types (e.g., LONGTEXT for JSON-like data). SQLite: Be mindful of limited ALTER TABLE support. Applying Migrations

For development, use ./dev-scripts/migrate-all.py. This script:

Applies migrations for all engines (PostgreSQL, MySQL, SQLite). Updates the schema files in db/schema/.