Compare two schemas

Read this when production has drifted from your local database, or a design is ready for a second environment, and you want the exact ALTER script with its risks before anything runs. Schema compare is Pro, works on all four engines, and reads both sides fresh every time.

Start a compare

1. Right-click commerce in the explorer and choose Compare schema with…, or run Compare schemas from the command palette.
2. Under Change this database, pick the connection and schema the script will run on.
3. Under to match, pick Connection, Saved design or Snapshot, then which one.
4. Click Compare.

A design has its own entry: Compare with a database…, beside Review and apply in the design workbench, opens the dialog with that design as the source. The dialog only offers connections that are open and of the same kind, and MySQL and MariaDB compare with each other.

Read the changes

The tab's toolbar says what will happen: "Make orders-prod · commerce match orders-local". Each difference is one row, written as a sentence: Add column shipped_at timestamp with time zone, Change status from character varying(20) to varchar(32).

Seqora checks the target's data as soon as both sides are read. A change that would fail on the rows that are there, such as a NOT NULL over 12 empty values, is shaded red with the reason under it. A change that locks a large table is shaded amber. Everything else stays plain. By risk regroups the rows under "Fails on current data", "Check before applying" and "Ready".

Anything only the target has, a column or a table the source dropped, starts under Left out of the script with what adding it would delete. Tick it to add it. Structure on a table shows its columns on both sides.

Confirm renames

Two connections can't tell a rename from a drop and an add. When a dropped column and an added one share a type, Possible renames asks: Treat as rename keeps the values, Not a rename leaves the pair as a drop and an add. A saved design knows its renames, so it never asks.

Apply

Before you apply, under the script, lists each failure and warning, then what passed: "Deletes no data. 1 drop left out." The button says why it is waiting, for example Fix 1 failure to continue. Leave the failing row out, or fix its data in the SQL editor and compare again.

Review and apply opens Review changes with the same Checks, Dry run and apply steps a design uses, and the same typed database name on production. Before the first statement runs, Seqora saves a snapshot of the target. When it's done, the catalog refreshes and the compare reads both sides again.

Snapshots and undo

Schema snapshots… on a schema, or Snapshots in the compare's toolbar, lists the snapshots of that schema. Take snapshot saves one under a name you choose, such as Release 4.2. The ones Seqora takes before an apply are named "Before applying ", and the 20 newest of those are kept per schema.

A snapshot taken before an apply has Undo script. It compares the live schema with that snapshot, so the script puts the schema back the way it was. Data a drop deleted does not come back.

Caveats

• Phase one compares tables, columns, keys, indexes, foreign keys and comments. Views, check constraints, enums, sequences, triggers and functions are not compared yet, and the line at the bottom of the tab names them.
• Partial and expression indexes and generated columns in the source can't be created yet. They are listed under Left out of the script and never put in the script.
• Differences in constraint and index names and in comments are ignored unless you untick them in the dialog.
• If Seqora could only read part of either side, the compare is read-only: an unread table would look like a drop.
• On MySQL and MariaDB, if a statement fails during apply, the ones before it stay applied. Compare again to see what is left.
• Snapshots and their names stay on Free and can be listed and deleted there. Taking, renaming and comparing them is Pro.