migration-patterns

What problem does it solve?

This Skill provides a structured and safe approach to managing database schema changes for SQLite, ensuring that your application's database evolves consistently across different environments and deployments. It prevents data loss, schema inconsistencies, and simplifies the process of introducing new features that require database modifications.

Core Features & Use Cases

• Incremental Schema Evolution: Defines database changes as numbered SQL files, ensuring they are applied in a specific order and only once.
• Idempotent Operations: Emphasizes IF NOT EXISTS for tables and indexes, making migrations safe to re-run without causing errors.
• Dual Schema Management: Maintains both a base schema.sql (for fresh installs) and incremental migrations/*.sql (for updates), ensuring consistency.
• Best Practices: Enforces rules like "one feature per migration" and "never modify existing migrations" to maintain a clean and manageable migration history.
• Use Case: You need to add a new tags table and a contact_tags junction table to an existing CRM database. This Skill guides you through creating the migration file, updating the base schema, and ensuring these changes are applied correctly and safely to all deployed instances of your application.

Quick Start

Create a new SQL file in migrations/ named 00X_your_description.sql. Inside, use CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS for all statements. After creating the migration, also update your schema.sql file with the same changes. Run your application; the migration runner will automatically detect and apply pending migrations.