Dbmate

What it does

This skill, Dbmate, assists with managing database migrations using the dbmate tool. It provides instructions and guidance for creating, writing, and applying database migrations across different engines like SQLite, PostgreSQL, and MySQL. The primary purpose is to streamline the process of evolving a database schema while ensuring consistency and safety. This is specifically geared towards developers and DBAs working on projects that use dbmate.

When to use it

• When creating new database migrations for your project.
• When writing .sql migration files, ensuring they follow the required structure and guidelines.
• To apply all pending database migrations across multiple engines (PostgreSQL, MySQL, SQLite) as part of a development workflow.
• When needing to understand how to handle transactions safely within migrations.

Key capabilities

• Provides instructions for creating new migrations using dbmate --migrations-dir db/migrations/<engine> new "migration_name".
• Enforces a specific migration file structure with -- migrate:up and -- migrate:down sections.
• Offers guidelines on writing idempotent SQL statements and tailoring them to the database engine (PostgreSQL, MySQL, SQLite).
• Includes a script (./dev-scripts/migrate-all.py) for applying migrations across all supported engines and updating schema files.
• Prevents manual transaction block usage within migration scripts.

Example prompts

• "How do I create a new database migration?"
• "What is the correct format for a dbmate migration file?"
• "Run the script to apply all migrations."
• “Explain how to handle engine-specific SQL differences in my migrations.”

Tips & gotchas

• Do not manually edit files in db/schema/. They are auto-generated.
• Avoid wrapping SQL statements within BEGIN/COMMIT blocks, as dbmate handles transactions automatically.
• Ensure your SQL is tailored to the specific database engine being used (PostgreSQL, MySQL, or SQLite).