Been working on a Next.js app over the last month or so and I've consistently run into issues deploying in production whenever there's a migration involved.

My best theory to why is that whenever a migration is needed, CC runs prisma migrate dev to create the migration file automatically, but that command isn't supported in a non-interactive mode so fails. CC addresses the failure by creating a manual migration file, applying it, and marking it as applied.

Everything is then peachy in dev.

But in production the build/app fails because the migration doesn't get applied. Most recently because the migration file doesn't exist for whatever reason.

I had a convo with CC about my theory and it seemed to agree using prisma migrate dev was the root cause and came up with a solution requiring three scripts (create, verify, apply).

Curious if this is genuinely the recommended approach as it seems like overkill for a very common workflow, or is there a simpler, more native way to guide CC in these situations?

Claude Code Chat Summary

Decision: Replace prisma migrate dev with a non-interactive pipeline using prisma migrate diff for Claude Code compatibility

Reason: prisma migrate dev requires interactive confirmation (data loss warnings, drift detection, migration naming). When Claude Code makes schema changes, this fails in non-interactive mode. The workaround of manually creating migration files and using prisma migrate resolve --applied was error-prone, leading to:

• Migration files not persisting correctly

• Incomplete or incorrect SQL

• Dev database out of sync with migration history

• Production deployments failing with "column does not exist" errors

Implementation:

• scripts/migrate-create.sh (npm run migrate:create <name>): Uses prisma migrate diff --from-schema-datasource --to-schema-datamodel to generate SQL non-interactively. Creates properly timestamped migration folder with migration.sql.

• scripts/migrate-verify.sh (npm run migrate:verify): Validates schema syntax, checks migration files exist, verifies migration status and drift.

• scripts/migrate-apply.sh (npm run migrate:apply): Applies pending migrations using prisma migrate deploy (non-interactive).

New Workflow:

1. Edit prisma/schema.prisma

2. npm run migrate:create <name> - generates migration

3. Review generated SQL

4. npm run migrate:verify - validates before commit

5. npm run migrate:apply - applies to dev DB

6. Test, then commit prisma/ directory

Key Decisions:

1. Use --from-schema-datasource (current DB state) not --from-migrations (requires shadow DB)

2. Verification catches drift before production deployment

3. Clear separation: create → verify → apply → commit

Alternatives Considered:

• prisma migrate dev --create-only: Still prompts in some scenarios

• Shadow database for --from-migrations: Adds complexity for little benefit

• Container-based interactive mode: Over-engineered

Trade-offs:

• Requires database running to create migrations (acceptable, needed for development anyway)

• --from-schema-datasource compares DB state not migration history (acceptable, catches same issues)

• More scripts to maintain (simple, well-documented)

Files Modified:

• scripts/migrate-create.sh (new)

• scripts/migrate-verify.sh (new)

• scripts/migrate-apply.sh (new)

• package.json (added 4 npm scripts)

Impact: Claude Code can now create valid, Prisma-generated migrations without interactive prompts. Verification step catches issues before deployment.

migrate-create.sh

#!/bin/bash
# Non-interactive migration creation for Claude Code
# Uses prisma migrate diff to generate SQL without prompts
# Usage: ./scripts/migrate-create.sh <migration_name>

set -e

# Validate input
if [ -z "$1" ]; then
  echo "Error: Migration name required"
  echo "Usage: ./scripts/migrate-create.sh <migration_name>"
  echo "Example: ./scripts/migrate-create.sh add_user_profile"
  exit 1
fi

MIGRATION_NAME="$1"
TIMESTAMP=$(date +%Y%m%d%H%M%S)
MIGRATION_DIR="prisma/migrations/${TIMESTAMP}_${MIGRATION_NAME}"

# Load environment variables if .env exists
if [ -f ".env" ]; then
  set -a
  source .env
  set +a
fi

# Check if database is running
echo "Checking database connection..."
if ! docker exec strmate-db pg_isready -U "${DB_USER:-postgres}" > /dev/null 2>&1; then
  echo "Error: Database is not running."
  echo "Start it with: docker compose up -d db"
  exit 1
fi
echo "Database is running."
echo ""

# Generate SQL diff between current database state and new schema
# Uses --from-schema-datasource to read current DB state (requires running DB)
echo "Generating migration SQL..."
SQL_OUTPUT=$(npx prisma migrate diff \
  --from-schema-datasource prisma/schema.prisma \
  --to-schema-datamodel prisma/schema.prisma \
  --script 2>&1) || EXIT_CODE=$?

# Check if output indicates no changes
if [ -z "$(echo "$SQL_OUTPUT" | tr -d '[:space:]')" ]; then
  echo "No schema changes detected. Nothing to migrate."
  exit 0
fi

if echo "$SQL_OUTPUT" | grep -q "This is an empty migration"; then
  echo "No schema changes detected. Nothing to migrate."
  exit 0
fi

# Exit code 2 means there are differences (expected behavior)
# Exit code 0 with content also means differences
# Any other non-zero exit code is an error
if [ -n "$EXIT_CODE" ] && [ "$EXIT_CODE" != "0" ] && [ "$EXIT_CODE" != "2" ]; then
  echo "Error generating migration:"
  echo "$SQL_OUTPUT"
  exit 1
fi

# Create migration directory
mkdir -p "$MIGRATION_DIR"

# Write the migration SQL file
echo "$SQL_OUTPUT" > "$MIGRATION_DIR/migration.sql"

echo "========================================="
echo "Migration created successfully!"
echo "========================================="
echo ""
echo "Migration: $MIGRATION_DIR"
echo ""
echo "Generated SQL:"
echo "-----------------------------------------"
cat "$MIGRATION_DIR/migration.sql"
echo "-----------------------------------------"
echo ""
echo "Next steps:"
echo "  1. Review the SQL above for correctness"
echo "  2. Verify: npm run migrate:verify"
echo "  3. Apply to dev DB: npm run migrate:apply"
echo "  4. Test the application"
echo "  5. Commit: git add prisma/ && git commit -m 'Add migration: $MIGRATION_NAME'"

migrate-verify.sh

#!/bin/bash
# Verifies migrations are valid and ready for deployment
# Checks: schema syntax, migration status, drift detection, file integrity

set -e

echo "========================================="
echo "Migration Verification"
echo "========================================="
echo ""

# Step 1: Validate schema syntax
echo "Step 1: Validating Prisma schema..."
if npx prisma validate > /dev/null 2>&1; then
  echo "  [PASS] Schema is valid"
else
  echo "  [FAIL] Schema validation failed:"
  npx prisma validate
  exit 1
fi
echo ""

# Step 2: Check migration files exist and have SQL
echo "Step 2: Checking migration files..."
MIGRATION_COUNT=0
MISSING_SQL=0

for dir in prisma/migrations/*/; do
  if [ -d "$dir" ]; then
    MIGRATION_COUNT=$((MIGRATION_COUNT + 1))
    if [ ! -f "${dir}migration.sql" ]; then
      echo "  [FAIL] Missing migration.sql in $dir"
      MISSING_SQL=$((MISSING_SQL + 1))
    fi
  fi
done

if [ "$MISSING_SQL" -gt 0 ]; then
  echo "  [FAIL] $MISSING_SQL migration(s) missing SQL files"
  exit 1
fi

echo "  [PASS] Found $MIGRATION_COUNT migrations, all have SQL files"
echo ""

# Step 3: Check database connection
echo "Step 3: Checking database connection..."
if docker exec strmate-db pg_isready -U "${DB_USER:-postgres}" > /dev/null 2>&1; then
  echo "  [PASS] Database is running"

  # Step 4: Check migration status
  echo ""
  echo "Step 4: Checking migration status..."
  MIGRATE_STATUS=$(npx prisma migrate status 2>&1) || true

  if echo "$MIGRATE_STATUS" | grep -q "Database schema is up to date"; then
    echo "  [PASS] All migrations are applied"
  elif echo "$MIGRATE_STATUS" | grep -q "Following migration"; then
    PENDING=$(echo "$MIGRATE_STATUS" | grep -c "Following migration" || echo "1+")
    echo "  [INFO] Pending migrations found:"
    echo "$MIGRATE_STATUS" | grep -A1 "Following migration" | head -10
  else
    echo "  [INFO] Migration status:"
    echo "$MIGRATE_STATUS" | head -10
  fi

  # Step 5: Check for schema drift (DB state vs schema.prisma)
  echo ""
  echo "Step 5: Checking for schema drift..."
  DIFF_OUTPUT=$(npx prisma migrate diff \
    --from-schema-datasource prisma/schema.prisma \
    --to-schema-datamodel prisma/schema.prisma \
    --exit-code 2>&1) && DIFF_EXIT=0 || DIFF_EXIT=$?

  if [ "$DIFF_EXIT" == "0" ] || [ -z "$(echo "$DIFF_OUTPUT" | tr -d '[:space:]')" ]; then
    echo "  [PASS] No drift - database matches schema"
  elif [ "$DIFF_EXIT" == "2" ]; then
    echo "  [WARN] Schema has changes not yet applied to database"
    echo "  This is expected if you have pending migrations"
    echo "  Run: npm run migrate:apply"
  else
    echo "  [WARN] Could not check drift"
  fi
else
  echo "  [SKIP] Database not running - skipping status and drift checks"
fi

echo ""
echo "========================================="
echo "Verification PASSED"
echo "========================================="
echo ""
echo "Safe to commit and deploy!"

migrate-apply.sh

#!/bin/bash
# Applies pending migrations to the development database
# Uses prisma migrate deploy (non-interactive)

set -e

# Load environment variables if .env exists
if [ -f ".env" ]; then
  set -a
  source .env
  set +a
fi

echo "========================================="
echo "Applying Migrations"
echo "========================================="
echo ""

# Check if database is running
echo "Checking database connection..."
if ! docker exec strmate-db pg_isready -U "${DB_USER:-postgres}" > /dev/null 2>&1; then
  echo "Error: Database is not running."
  echo "Start it with: docker compose up -d db"
  exit 1
fi
echo "Database is running."
echo ""

# Apply migrations
echo "Applying pending migrations..."
npx prisma migrate deploy

echo ""
echo "Regenerating Prisma Client..."
npx prisma generate

echo ""
echo "========================================="
echo "Migrations applied successfully!"
echo "========================================="
echo ""
echo "Next steps:"
echo "  - Test the application: npm run dev"
echo "  - Commit migrations: git add prisma/ && git commit"