skills.rest
spences10
spences10COMMUNITY
Explore all skills by this author →
WebsiteGitHub

migration-patterns

Create idempotent SQLite migrations with ordered SQL files and tracking tables.

6|Updated Aug 8, 2023
Search Tags:#SQL#SQLite#Backend#Migrations#Deployment#Database#Schema Management
One-click install
npx skills add https://github.com/spences10/devhub-crm --skill migration-patterns
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: migration-patterns
Source: https://github.com/spences10/devhub-crm/tree/main/.claude/skills/migration-patterns
Command: npx skills add https://github.com/spences10/devhub-crm --skill migration-patterns

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes scripts (resource) and references (resource) and assets (resource) components.

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.

Frequently Asked Questions about migration-patterns

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I manage SQLite database schema changes safely across environments?▼

SQLite schema migrations use numbered SQL files applied in order with idempotent statements like CREATE TABLE IF NOT EXISTS. This ensures changes are applied exactly once across development, testing, staging, and production without errors or data loss.

What's the best way to structure database migrations for SQLite?▼

Create numbered migration files (00X_description.sql) in a migrations/ folder with one feature per file. Use CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS, maintain a base schema.sql for fresh installs, and never modify existing migrations.

How do I prevent schema inconsistencies when deploying SQLite database changes?▼

Track applied migrations in a dedicated table within your database. Execute migrations in zero-padded filename order, ensuring each runs only once and maintaining consistency across all deployed instances.

Can I use SQLite migrations for incremental feature rollouts?▼

Yes. Design each migration as a self-contained feature with its own tables and indexes. Apply them incrementally across environments in sequence, allowing you to deploy database changes alongside application updates safely.

Why should I maintain both schema.sql and migrations/ separately?▼

schema.sql provides the complete schema for fresh database installations, while migrations/ track incremental changes for existing deployments. This dual approach ensures new environments match production without replaying the entire migration history.

What happens if I modify or delete an existing SQLite migration file?▼

Never modify or delete applied migrations; this breaks the migration history and causes inconsistencies across environments. Create new migrations instead to add, alter, or remove schema elements safely.

Related Skills

Explore More Software Engineering Skills→
Prompt-or-Die-LabsPrompt-or-Die-Labs
7

drizzle-migration

Generate and apply Drizzle Kit migrations for PostgreSQL or SQLite schema changes.

Official
Intermediate
AgentiveCityAgentiveCity
2

cloudflare-d1-migrations-and-production-seeding

Manage Cloudflare D1 schema migrations and environment-specific data seeding.

Official
Advanced
ngxtmngxtm

SQLx Database

Execute compile-time checked asynchronous SQL queries in Rust with sqlx.

Community
Intermediate
openmeterioopenmeterio
2.2k

db-migration

Generate SQL migrations for ent framework schema changes on PostgreSQL.

Official
Intermediate