Database Migration: Safely Managing Dev and Production Environments

Source: Dev.to

This is Day 8 of Building SaaS Solo – Design, Implementation, and Operation Advent Calendar 2025.

The approach described in this article is something I arrived at through trial and error. If you know a better way, please let me know in the comments.

Common Migration Management Approaches

There are several ways to manage database migrations.

Using ORM Migration Features

ORMs like Drizzle ORM, Prisma, and TypeORM have built-in migration features.

# For Drizzle ORM
npx drizzle-kit generate  # Generate migrations from schema
npx drizzle-kit migrate   # Apply migrations

When you write table definitions in TypeScript, the ORM detects changes and automatically generates SQL such as ALTER TABLE.

Benefits

• Automatically generates migration SQL from code changes
• Manages application history in database tables
• Can be applied with a single command

Challenges

• Difficult to handle complex data migrations (e.g., transforming existing data)
• Sometimes hard to understand what will be executed

Challenges When Managing Multiple Environments

ORM migration features are convenient for single‑environment management, but separate development and production environments introduce challenges:

• Schema can get out of sync between dev and production
• Hard to track “applied to dev but not yet to production” state
• Difficult to know what was applied when

I faced these same challenges while developing Memoreru, my indie project, and through trial and error arrived at my current operational rules.

Migration Management for Indie Projects

Sequential File Management

Migration files are created by Claude Code and managed with sequential numbers.

database/migrations/
├── sql/
│   ├── 001_create_users_table.sql
│   ├── 002_create_posts_table.sql
│   ├── 003_add_user_profile.sql
│   ├── 004_add_status_column.sql
│   └── …
├── scripts/
│   └── migrate.sh
├── status.json
└── README.md

Benefits of sequential management

• Application order is clear at a glance
• File names show which point in time the schema represents
• Easy to identify differences between production and development

Why Manage SQL Files Directly

Instead of using Drizzle ORM’s drizzle-kit generate, I create SQL files directly. Schema definitions themselves remain managed with Drizzle ORM, preserving type safety.

Reasons for managing SQL files directly

• Easier to handle complex changes (those involving data migration)
• Complete understanding of what will be executed
• Simpler troubleshooting

Shared Migration Script for Dev and Production

Why a Shared Script

# Development environment
./database/migrations/scripts/migrate.sh dev 004_add_status_column.sql

# Production environment
./database/migrations/scripts/migrate.sh pro 004_add_status_column.sql

Benefits of a shared script

• Rehearsal effect – Running the same procedure in dev uncovers problems before production
• Unified procedure – Avoids accidents caused by divergent workflows
• Centralized logging – Execution logs from both environments share the same format

Environment‑Specific Differences

Entering the production connection string each time is tedious, but it acts as a safety measure to prevent accidental operations on the wrong database. I also avoid giving Claude Code the production DB credentials, eliminating the risk of AI‑driven mishaps. Backups are taken with pgAdmin before applying migrations in both environments.

Safety Mechanisms

The script includes:

• Confirmation flow – Prompts before applying migrations
• Connection test – Verifies DB connectivity first
• Automatic log saving – Stores logs in logs/migrations/ for later review

The specific implementation was generated by Claude Code; you can describe your requirements and let it produce a suitable script.

Centralized Status Management with status.json

{
  "lastUpdated": "2025-12-04",
  "environments": {
    "dev": {
      "name": "Development",
      "lastApplied": "004_add_status_column",
      "appliedAt": "2025-12-04"
    },
    "pro": {
      "name": "Production",
      "lastApplied": "003_add_user_profile",
      "appliedAt": "2025-11-30"
    }
  },
  "pending": {
    "pro": ["004_add_status_column"]
  }
}

Checking Pending Production Migrations

# Display pending list
jq '.pending.pro' database/migrations/status.json
# => ["004_add_status_column"]

You can see at a glance which migrations have been applied to dev but not yet to production.

Automatic Updates

After applying migrations, the script automatically updates status.json, eliminating manual edits and preventing forgotten updates.

Practical Tips

Tip 1: Make Destructive Changes Gradually

-- Step 1: Add new column
ALTER TABLE contents ADD COLUMN new_name TEXT;

-- Step 2: Migrate data
UPDATE contents SET new_name = old_name;

-- Step 3: Drop old column (in a separate migration)
ALTER TABLE contents DROP COLUMN old_name;

Checking application behavior between steps 2 and 3 minimizes impact if problems arise.

Tip 2: Prepare Rollback SQL

-- Migration
ALTER TABLE contents ADD COLUMN status TEXT DEFAULT 'draft';

-- Rollback (execute only when needed)
-- ALTER TABLE contents DROP COLUMN status;

Leave rollback statements as comments for quick reference.

Tip 3: Collaboration Rules with Claude Code

Document migration operation rules in CLAUDE.md:

## Migration Operations

- Don't execute SQL directly with `psql`  
- Always apply via `migrate.sh` script  
- Rehearse in dev environment before production  
- Commit `status.json` after applying

These rules prevent AI agents from accidentally running raw SQL.

Summary

What’s working well

• Sequential file management for chronological tracking
• Rehearsals with a shared dev/production script
• Centralized status management via status.json
• Confirmation flow to prevent mistakes

Things to be careful about

• Manual SQL management can become cumbersome as change volume grows
• Test complex data migrations with representative test data first
• Plan rollback procedures in advance

Even for indie development, establishing clear migration rules from the start helps avoid problems later.