# 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:

```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"]
    }
  }
}
```

Add with:

```bash
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`:

```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](https://harishgarg.com/mcp-workflows-cookbook-10-end-to-end-recipes-for-claude-code-cli-and-rovo-dev-cli) for all 10 recipes.

---