MCP Workflow: Schema Diff, Migration, Reversible SQL, and Tests (Claude Code CLI and Rovo Dev CLI)

Goal of this MCP recipe is to compare the current database schema to a target specification, generate a safe and reversible migration plan, update application models, and add integration tests to verify both the migration and rollback paths.

---

Prerequisites

• A running Postgres or MySQL database with schema access

• Target schema documented in a file, for example docs/target_schema.md

• Access to Claude Code CLI or Rovo Dev CLI

• Installed MCP servers for:

  • Database introspection

  • SQL execution (non-production DB)

  • ORM or code update operations

  • Git operations

• Environment variables:

  • DB_URL for current database

  • CI_DB_URL for a staging or CI database

---

MCP Setup

Claude Code CLI

Place this in a project-scoped .mcp.json at your repo root:

{
  "mcpServers": {
    "postgres": {
      "command": "python",
      "args": ["-m", "mcp_server_postgres"],
      "env": {
        "DB_URL": "postgresql://user:pass@localhost:5432/demo"
      }
    },
    "sql_exec": {
      "command": "python",
      "args": ["-m", "mcp_server_sql"]
    },
    "git": {
      "command": "python",
      "args": ["-m", "mcp_server_git"]
    }
  }
}

Add with:

claude mcp add --scope project postgres python -m mcp_server_postgres

Claude will prompt for approval when the project opens.

---

Rovo Dev CLI

Place this in ~/.rovodev/mcp.json:

{
  "mcpServers": {
    "postgres": {
      "command": "python",
      "args": ["-m", "mcp_server_postgres"],
      "env": {
        "DB_URL": "postgresql://user:pass@localhost:5432/demo"
      }
    },
    "sql_exec": {
      "command": "python",
      "args": ["-m", "mcp_server_sql"]
    },
    "git": {
      "command": "python",
      "args": ["-m", "mcp_server_git"]
    }
  }
}

Rovo loads these servers automatically on CLI start; restart after edits.

---

Prompts to Run in Either CLI

Diff

Introspect the current schema from DB_URL and compare it to docs/target_schema.md. Produce a structured diff with compatibility notes including new columns, renames, index changes, and potential lock impacts.

Plan

Propose an incremental, reversible migration plan with steps tagged online or offline, expected lock durations, and any required data backfills.

Generate

Create migration files for <your migration tool/ORM> including down scripts for rollback. Use safe defaults and avoid long exclusive locks.

Update Code

Modify src/models.py or equivalent to reflect the new schema. Keep API surface changes minimal and document any breaking changes.

Tests

Add tests/test_migrations.py that:

1. Applies migrations on a temp database.

2. Verifies expected columns, indexes, and constraints exist.

3. Runs the down migration to confirm full rollback.

---

Validation Steps

1. Apply migrations against CI_DB_URL in a staging environment.

2. Run migration tests and confirm all pass.

3. Verify rollback restores prior schema state and indexes.

4. Record lock timings if your tables are large or traffic is high.

---

Variations

• Swap Postgres MCP server for MySQL MCP server with matching connection string

• Add a “blue/green” or “shadow table” migration strategy for high-traffic tables

• Separate data backfill jobs into their own PR to keep schema changes atomic

---

Troubleshooting

• If Claude does not load servers, confirm .mcp.json is in the repo root and was added with claude mcp add --scope project

• If Rovo does not pick up changes, validate ~/.rovodev/mcp.json JSON syntax and restart

• For large migrations, run a dry run first in a throwaway database to catch lock issues early

---

Key Differences Noticed

• Claude’s project-scoped setup makes it easy to share the same .mcp.json with the team via version control

• Rovo’s global ~/.rovodev/mcp.json is quicker to set up but less tied to a specific repo, which may require extra caution in multi-project environments

---

Back to the Cookbook

See the full Awesome MCP Workflows Cookbook for all 10 recipes.

---