Synchronizing Database Schemas with DataGrip’s Schema Diff Tool

Desired Outcome

Keep a target database (e.g., production) exactly in sync with a source of truth – either another live database or a set of DDL files – while preserving a history of changes in version control.

Prerequisites

• DataGrip 2026.1 or newer installed on a machine with network access to both databases.
• Valid JDBC connections for the source and target databases in DataGrip.
• DDL privileges on the target database (CREATE, ALTER, DROP, etc.).
• Optional but recommended: a Git repository configured in DataGrip’s VCS settings.

Step‑by‑Step Procedure

1. Connect to Both Databases

   Open DataGrip, go to View | Tool Windows | Database. Right‑click Data Sources and add connections for your source (e.g., dev_db) and target (e.g., prod_db). Verify each connection is live.

2. Launch Schema Diff

   Right‑click the source database node and select Compare with | Database…. In the dialog choose the target database. Click OK. The Schema Diff tool opens in a new tab.

3. Configure Diff Options

   In the diff panel click the gear icon. Ensure Include dropped objects is checked if you want to delete objects that exist only in the source. For PostgreSQL, you may also enable Show column defaults for a more thorough comparison.

4. Review the Diff

   The left pane shows the source schema, the right pane the target. Differences are highlighted. Each change is represented as a SQL statement in the SQL tab below.

5. Export Generated SQL (Optional)

   Click Export to File in the SQL tab to save a script for manual review or CI/CD deployment.

6. Apply Changes

   Click the Apply button. DataGrip will present a confirmation dialog listing the SQL statements to run. Review, then confirm. The statements execute against the target database.

7. Commit to VCS

   After applying, DataGrip prompts to commit the generated SQL file and any modified DDL files. Use Commit to record the changes in Git.

Concrete Example

Suppose dev_db contains a table users with an extra column last_login TIMESTAMP that is missing in prod_db. After running Schema Diff, the SQL tab will contain:

ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

Reviewing and applying this statement will add the column to production. You can then query:

SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'users' AND table_schema = 'public';

to confirm the column exists.

Expected Validation Checks

• After applying, run a query against information_schema.columns or pg_catalog.pg_table_def to verify the schema matches the source.
• If using Git, inspect the commit to ensure the SQL script is present.
• For critical changes, run the generated script against a staging clone of the target database first.

Recovery Options

• Rollback via Backup: If the change caused issues, restore the target database from the most recent backup before the sync.
• Undo with VCS: If the SQL script was committed, revert the commit and reapply the previous schema state using the backup or by re‑executing the inverse SQL.
• Manual Reversal: Use DataGrip’s Show Diff again to generate the reverse SQL (e.g., ALTER TABLE users DROP COLUMN last_login;) and apply it.

Limitations & Caveats

• Some engines (e.g., MySQL 5.7) provide incomplete diff support; verify the diff output manually.
• DDL changes may break application logic; coordinate with dev teams or use feature flags.
• Always test generated SQL in a non‑production environment before applying.

Practical Check

After applying a sync, run:

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';

and compare the count to the source database. A mismatch indicates a sync issue.

Conclusion

DataGrip’s Schema Diff tool streamlines keeping databases in sync with source‑code DDL. By following the steps above, validating changes, and planning recovery, you can confidently manage schema evolution across environments.