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/<engine> new "migration_name"

Replace <engine> with sqlite , postgres , or mysql .

Writing Migrations

Migrations are stored in db/migrations/<engine>/ 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/ .