database-migration-patterns
Skill17 starsChanged 5 months agoApache-2.0Archived repositoryView on GitHub
Manage database schema changes safely with migration tools, zero-downtime strategies, and rollback procedures. Covers Alembic, SQL migrations, data migrations, and testing strategies. Triggers on database migration, schema changes, or Alembic configuration requests.
1 other project uses a version of this.
Other versions1 project uses a version of this.
Word for word · 1
- organvm-iv-taxis/a-i--skills★ 17100% same
Edited · 0
None found.
Wrote their own version · 0
None found.
How matches are found. Word for word: the exact same text. Edited: shares passages with this file (“99% same” means 99% of its five-word phrases also appear here; 10% or more counts). Their own version: similar meaning, or, weakest, the same name. We can’t tell which project had it first. Projects are counted among the 200 most-starred candidates, and mentions are links to the repository we found, up to 50 per site, so both counts are a floor.
In this repository170 other files it gives its agents.
- AGENTS.mdAGENTS.md
- docs/AGENTS.mdAGENTS.md
- CLAUDE.mdCLAUDE.md
- .github/copilot-instructions.mdCopilot instructions
- algorithmic-artSkill
- audio-engineering-patternsSkill
- canvas-designSkill
- creative-writing-craftSkill
- generative-art-algorithmsSkill
- generative-art-deploymentSkill
- generative-music-composerSkill
- interactive-theatre-designerSkill
- modular-synthesis-philosophySkill
- movement-notation-systemsSkill
- narratological-algorithmsSkill
- natural-center-extractionSkill
- react-three-fiber-patternsSkill
- reality-tv-narrative-analyzerSkill
- script-analysis-dramaturgicalSkill
- theme-factorySkill
- three-js-interactive-builderSkill
- corpus-persona-extractionSkill
- data-ingestion-pipelineSkill
- data-pipeline-architectSkill
- data-storytelling-analystSkill
- market-gap-analysisSkill
- ml-experiment-trackerSkill
- prompt-atom-formalizationSkill
- sql-query-optimizerSkill
- systemic-product-analystSkill
- time-series-analystSkill
- accessibility-patternsSkill
- agent-testing-patternsSkill
- api-design-patternsSkill
- artifacts-builderSkill
- backend-implementation-patternsSkill
- browser-extension-patternsSkill
- cli-tool-designSkill
- code-refactoring-patternsSkill
- code-review-checklistSkill
- coding-standards-enforcerSkill
- community-platform-patternsSkill
- configuration-managementSkill
- continuous-learning-agentSkill
- data-backup-patternsSkill
- deployment-cicdSkill
- docker-containerizationSkill
- dotfile-systems-architectSkill
- error-handling-logging-patternsSkill
- fastapi-patternsSkill
- feature-workflow-orchestratorSkill
- frontend-design-systemsSkill
- fullstack-starter-packSkill
- gcp-resource-optimizerSkill
- htmx-interaction-patternsSkill
- iterative-code-explorationSkill
- json-schema-designSkill
- mcp-builderSkill
- mcp-server-orchestratorSkill
- mobile-platform-architectSkill
- The first 60 shown.
The file
Files
Database Migration Patterns
Evolve database schemas safely with versioned, reversible, tested migrations.
Alembic Setup
# Initialize
alembic init alembic
# Create migration
alembic revision --autogenerate -m "add skills table"
# Run migrations
alembic upgrade head
# Rollback one step
alembic downgrade -1
# Show current state
alembic current
alembic historyConfiguration
# alembic/env.py
from app.models import Base
from app.config import settings
target_metadata = Base.metadata
def run_migrations_online():
connectable = create_async_engine(settings.database_url)
async with connectable.connect() as connection:
await connection.run_sync(do_run_migrations)
def do_run_migrations(connection):
context.configure(connection=connection, target_metadata=target_metadata)
with context.begin_transaction():
context.run_migrations()Migration Structure
"""add skills table
Revision ID: abc123
Revises: def456
Create Date: 2026-03-20 10:00:00
"""
from alembic import op
import sqlalchemy as sa
revision = "abc123"
down_revision = "def456"
def upgrade():
op.create_table(
"skills",
sa.Column("id", sa.String(64), primary_key=True),
sa.Column("name", sa.String(64), nullable=False, unique=True),
sa.Column("description", sa.Text, nullable=False),
sa.Column("category", sa.String(32), nullable=False),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
)
op.create_index("ix_skills_category", "skills", ["category"])
def downgrade():
op.drop_index("ix_skills_category")
op.drop_table("skills")Zero-Downtime Migration Strategies
Adding a Column
# Safe: add nullable column first
def upgrade():
op.add_column("skills", sa.Column("tier", sa.String(20), nullable=True))
# Later migration: backfill then add constraint
def upgrade():
op.execute("UPDATE skills SET tier = 'community' WHERE tier IS NULL")
op.alter_column("skills", "tier", nullable=False, server_default="community")Renaming a Column
# Phase 1: Add new column
def upgrade():
op.add_column("skills", sa.Column("skill_category", sa.String(32)))
op.execute("UPDATE skills SET skill_category = category")
# Phase 2: (after app updated to use new column)
def upgrade():
op.drop_column("skills", "category")Removing a Column
# Phase 1: Stop writing to column (app change)
# Phase 2: Remove column
def upgrade():
op.drop_column("skills", "deprecated_field")Adding an Index
# Use CONCURRENTLY for zero-downtime
def upgrade():
op.execute("CREATE INDEX CONCURRENTLY ix_skills_name ON skills (name)")
def downgrade():
op.drop_index("ix_skills_name")Data Migrations
"""backfill governance metadata
Revision ID: ghi789
"""
from alembic import op
import sqlalchemy as sa
def upgrade():
# Use raw SQL for large table updates
conn = op.get_bind()
conn.execute(sa.text("""
UPDATE skills
SET governance_phases = ARRAY['build']
WHERE governance_phases IS NULL
AND category IN ('development', 'data')
"""))
conn.execute(sa.text("""
UPDATE skills
SET governance_phases = ARRAY['prove']
WHERE governance_phases IS NULL
AND category IN ('security', 'documentation')
"""))
def downgrade():
conn = op.get_bind()
conn.execute(sa.text("UPDATE skills SET governance_phases = NULL"))Testing Migrations
import pytest
from alembic.config import Config
from alembic import command
@pytest.fixture
def alembic_config():
config = Config("alembic.ini")
config.set_main_option("sqlalchemy.url", test_database_url)
return config
def test_upgrade_downgrade(alembic_config):
# Full upgrade
command.upgrade(alembic_config, "head")
# Full downgrade
command.downgrade(alembic_config, "base")
# Back to head
command.upgrade(alembic_config, "head")
def test_migration_data_integrity(alembic_config, db_session):
command.upgrade(alembic_config, "head")
# Insert test data
db_session.execute(sa.text("INSERT INTO skills (id, name, description, category) VALUES ('t1', 'test', 'Test skill', 'dev')"))
db_session.commit()
# Verify data survives next migration
command.upgrade(alembic_config, "head")
result = db_session.execute(sa.text("SELECT name FROM skills WHERE id = 't1'"))
assert result.scalar() == "test"Migration Checklist
Before Creating
- Schema change designed for zero-downtime
- Both upgrade and downgrade paths defined
- Large table changes use batching/CONCURRENTLY
Before Deploying
- Migration tested against staging database
- Rollback tested (downgrade works)
- Data backup taken
- Estimated execution time for large tables
- Application compatible with both old and new schema
After Deploying
- Migration completed successfully
- Application functioning correctly
- No performance regressions
- Cleanup migration scheduled (if multi-phase)
Anti-Patterns
- No downgrade — Every migration must be reversible
- Destructive changes in one step — Use multi-phase for column renames/removals
- Mixing schema and data migrations — Separate into distinct revisions
- Manual SQL in production — All changes through versioned migrations
- Testing only upgrade — Test downgrade paths too
- Large table ALTER without CONCURRENTLY — Locks the table for the duration
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

