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 changeTransaction Handling
[!IMPORTANT] DO NOT wrap your SQL inBEGIN/COMMITblocks.dbmateautomatically 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 EXISTSor 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/.