Generating Migration Scripts with DataGrip's Schema Comparison Tool

Keeping two databases in sync usually means hand-writing ALTER scripts and hoping you didn't miss a column. DataGrip's built-in schema comparison removes most of that guesswork: point it at two data sources (or a live database and a DDL dump), and it shows object-level differences and can generate the DDL that turns one side into the other. The catch is that the generated script is a starting point, not a reviewed migration — this guide shows how to use the tool, what the output actually looks like, and where you must intervene.

How the comparison works

Select two schemas, two data sources, or a schema and a DDL script file in the Database Explorer, right-click, and choose Tools → Compare Structure (the exact menu label varies slightly by version). DataGrip walks the metadata of both sides and presents a side-by-side tree of objects — tables, columns, indexes, constraints, views, and routines — marking each as identical, different, or present on only one side.

The critical feature is selectivity. Every difference has a checkbox, so you can promote only the changes you intend to ship. Environment-specific noise — local users, grants, scratch tables, job-queue tables that exist only in staging — can be excluded before you generate anything. This is what separates the tool from naive "sync everything" utilities.

A worked example: dev to staging

Assume a PostgreSQL setup where dev.public.orders has gained a column and an index that staging lacks:

-- Applied in dev, not yet in staging
ALTER TABLE orders ADD COLUMN priority smallint NOT NULL DEFAULT 0;
CREATE INDEX idx_orders_priority ON orders (priority) WHERE priority > 0;

The workflow:

1. Create data sources for both dev and staging in the Database Explorer. Use a read-only or least-privilege account for the staging side — comparison only reads metadata, and you want a hard guard against accidental writes.
2. Expand both to the public schema, select both schema nodes, right-click, and choose Tools → Compare Structure.
3. In the diff view, confirm the expected differences appear: the priority column and idx_orders_priority index. Deselect anything you don't want to migrate.
4. Click the button to generate the migration script (the toolbar offers generating DDL to make the left side match the right, or vice versa — check the direction before proceeding).
5. DataGrip opens the script in a query console. Review it, then run it against staging.

The generated script will look roughly like:

ALTER TABLE public.orders ADD COLUMN priority smallint NOT NULL DEFAULT 0;
CREATE INDEX idx_orders_priority ON public.orders USING btree (priority) WHERE priority > 0;

Quoting and schema qualification follow DataGrip's code-generation settings, so the output is usually runnable as-is for additive changes like this one.

Where the generated script can hurt you

The diff engine compares structure, not data semantics. It has no concept of preserving rows through a transformation:

• Renames become drop-and-add. If you renamed email to email_address in dev, the tool sees a missing column and a new column. The generated script drops one and creates the other — and the data in email is gone. Rewrite this as ALTER TABLE ... RENAME COLUMN ... by hand.
• Type narrowing is emitted blindly. Changing varchar(100) to varchar(20) generates a plain ALTER that will fail at runtime (or truncate, depending on the database) if longer values exist. Check data first.
• NOT NULL additions without defaults fail against populated tables on most databases. Add a default or backfill in steps.
• Coverage varies by vendor and version. Partitions, row-level security policies, extensions, and other exotic objects may be compared incompletely or not at all. Check the in-IDE help or release notes for your DataGrip version and database before trusting the diff for those object types.

Treat the generated file the way you'd treat a junior engineer's migration PR: a solid draft that needs review before it touches production.

Drift detection against version control

The same comparison works between a live database and a DDL script file or database model. If you keep a schema dump in your repository, you can periodically compare production against the dump to catch out-of-band changes — someone hotfixing a column directly in prod, for example. This is a lightweight drift check that requires no extra tooling.

Practical limits and verification

Comparing large schemas is memory-hungry and slow. Narrow the scope: select only the schemas you care about rather than comparing entire data sources, and close other heavy result sets first.

Before trusting the tool on real work, verify it end to end:

1. Create two small disposable schemas that differ by exactly one column.
2. Run Compare Structure and confirm the generated script contains the expected ALTER — and nothing else.
3. Execute the script against a copy of the target, then run the comparison a second time. Zero remaining differences means the round trip worked.

On databases with transactional DDL (PostgreSQL, for example), wrap execution in a transaction so a mid-script failure rolls back cleanly. On databases without it (MySQL's implicit commits), take a backup or snapshot first — there is no undo for a half-applied migration.