Schema Synchronization

A model that you changed offline, or a database that someone changed with a migration script, no longer matches the other side. DbSchema keeps its own copy of the schema, the design model, in a .dbs file. Schema synchronization compares that model with the database, lists every difference, and lets you choose for each one whether the model or the database wins. DbSchema then writes the SQL for the database side and shows it before it runs.

Synchronization needs DbSchema Pro.

Work offline

While you're connected, every change that you confirm with OK runs in the database at once. Offline, your changes stay in the model and the database doesn't change, so you can review them before they run, or work while the database is out of reach.

1. Click the connection button in the toolbar. It shows the name of the connection, here PostgreSQL.
2. Choose Offline (connect later).

Database → Offline (connect later) does the same. The connection button now reads Offline:

Edit tables, columns and keys as usual. Your changes live only in the model, so save it with File → Save Model to File, which needs DbSchema Pro.

Connect again and choose which side leads

1. Click the connection button and choose your connection, here PostgreSQL.
2. DbSchema connects and asks how to bring the model and the database together.

Choice	What DbSchema does	What changes
Compare, then decide difference by difference	Reads the database and lists every difference	Only what you choose, row by row
Write the model into the database	Generates the CREATE and ALTER statements and shows them before they run	The database
Skip	Connects and does nothing else	Nothing

Compare opens the Synchronize with the Database dialog. DbSchema ticks the tables that are selected in the diagram, or the whole schema when nothing is selected. Tick or untick schemas and tables, then press Compare.

Refresh the model from the database

When someone changed the database outside DbSchema, a refresh shows you what changed.

1. Press Refresh in the toolbar, or choose Database → Refresh Model from Database.
2. DbSchema compares the whole model with the database. If nothing differs, it says "Schemas are identical".
3. Otherwise it counts the differences. Choose Review Differences to decide each one, or Refresh Model to write every difference into the model.

Refresh Model changes only the model, and changes that exist only in the model are lost. With Add new tables to diagram ticked, the tables that are new in the database land on the open diagram.

To compare only some schemas or tables, choose Database → Selective Refresh Model Schemas from Database (F5), which opens the Synchronize with the Database dialog first.

Review the differences

The Synchronize Model and Database dialog has one row per difference. The DbSchema Model column shows the model's side, the Database column the database's side. In the picture, the model has a new column birth_date and a column renamed from carrier to carrier_name, and the database has a new table suppliers:

1. In the Action column of a row, click the side that wins. The left button writes the difference into the model, Ignore leaves it as it is, and the right button writes it into the database.
2. Select a row to see, in the lower pane, the SQL that each side would run. The label "this one runs" marks the side you chose.
3. To decide every row at once, use Set every row at the top right.

The buttons name what they do to the row:

Difference	Left button, into the model	Right button, into the database
birth_date, only in the model	Drop	Create
carrier_name, renamed in the model	Rename	Rename
suppliers, only in the database	Create	Drop

For birth_date set to the database, DbSchema shows:

ALTER TABLE shop.customers ADD birth_date date;

The line above the list counts the differences that go to the model, go to the database, are ignored and are still undecided. Type in Find a table, column, index... to find a row in a long list.

When a table exists only in the database, tick Merge as Virtual before you write it into the model. The table then comes in as a virtual table, which lives only in the model file.

For a view, the lower pane compares the script body line by line instead. Previous Change and Next Change jump between the changed lines:

Apply the changes

The buttons at the bottom of the dialog apply your choices, and each counts the rows it covers, such as Commit 2 in the database:

Button	What changes
Apply to the model	The model, with the rows set to the model
Commit in the database	The database, with the rows set to the database
Generate SQL script	Nothing: DbSchema writes the statements without running them

Generate SQL script asks where the script goes, an SQL Editor or a File:

For the rows in the picture above, DbSchema generated this script, which holds only the rows set to the database:

ALTER TABLE shop.customers ADD birth_date date    ;

ALTER TABLE shop.shipments RENAME COLUMN carrier TO carrier_name;

Commit in the database opens the same Execute Script In Database Dialog as Write the model into the database, one statement at a time. Once the statements ran, DbSchema compares again and lists what is left, such as the suppliers row that waits for Apply to the model. Save the model afterwards, so the file matches the database.

Write the model into the database

Create or Upgrade Model in the Database creates a schema that you designed offline in an empty database, or brings a database up to the model.

1. Choose Database → Create or Upgrade Model in the Database. If you're offline, DbSchema first asks for the connection in Database Connections: select it and press Connect.
2. In Commit Changes into the Database, tick the schemas and tables to create or upgrade, and press OK.
3. The Execute Script In Database Dialog shows one statement at a time, and Execution Message shows what the database answered.

Button	What it does
Execute	Runs the statement on screen
Execute All	Runs every remaining statement
Skip	Goes to the next statement without running this one
Close	Stops, and leaves the remaining statements unrun

Writing the model into the database changes only the database. The model stays as it is.

Rename a table or a column

When you rename a table or a column in DbSchema, the model remembers its previous name. Synchronization then renames the object in the database with RENAME, instead of a drop and a create, which would lose the data in the column:

To check the renames, press Renamed Items in the synchronization dialog. The Renamed Elements Dialog lists each renamed table, column or index with its Name and its Previous Name.

To give an object a previous name that DbSchema doesn't know, select its cell and press Edit Selected Cell. To treat a renamed object as a new one, press Clear Cell.

Compare the model with another model file

A second .dbs file can hold an older version of the schema, or the schema of another environment.

1. Choose Model → Compare Model with Other Model From File.
2. Choose the .dbs file.
3. The same dialog opens, with Project From File on the right.

Here the buttons are Apply to the model and Generate SQL script. Nothing runs in a database.

Export the differences as a report

A change report lists what was added, removed and changed, to keep with a release or to send to people who don't use DbSchema. Press Export Report in the synchronization dialog:

Field	What it sets
Title	The heading of the report
Changes	The direction, such as From DbSchema Model to Database
Include	Only the rows that the filter shows, the ignored differences, the SQL script as an appendix
Format	HTML, which opens in any browser and prints to PDF, or Markdown, for a Git repository or a wiki
File	Where the report goes

Press Export to write the file.

Change the synchronization settings

Choose Database → Synchronization Settings, or press Sync Settings in the synchronization dialog:

Tab	What it sets
DDL Script Priorities	The order of the statements in a generated script, lowest Priority # first
Synchronization Filter	Java code that leaves out some differences or tables. Sample Script shows an example
Initialization Script	Java code that runs before each synchronization, with the variable project
SQL DDL Scripts	A Pre Script and a Post Script, run before and after the script that creates the schema

Press Apply & Close to keep the settings.

Common problems

A renamed column shows as a drop and a create

DbSchema has no previous name for the column, for example because the column was renamed outside DbSchema. Open Renamed Items and set the Previous Name, as Rename a table or a column shows.

The columns are in another order after a refresh

DbSchema sorts the columns alphabetically when Edit → Settings → General → Reverse Engineering → Order Columns Alphabetically is ticked. Untick it to keep the order that the database lists. The order of the columns in DbSchema never changes their order in the database.

Dropping a view fails because other views depend on it

When the SQL of a view differs, synchronization drops the view and creates it again. Here the view paid_totals reads from paid_orders:

CREATE VIEW shop.paid_orders AS
SELECT order_id, customer_id, total FROM shop.orders WHERE status = 'PAID';

CREATE VIEW shop.paid_totals AS
SELECT customer_id, sum(total) AS paid FROM shop.paid_orders GROUP BY customer_id;

When paid_orders differs, the script that DbSchema generates for PostgreSQL starts with a plain DROP VIEW:

DROP VIEW shop.paid_orders;

PostgreSQL 17 refuses it:

ERROR:  cannot drop view shop.paid_orders because other objects depend on it
DETAIL:  view shop.paid_totals depends on view shop.paid_orders
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Press Generate SQL script, send it to an SQL Editor and edit it before you run it: drop the dependent views first, and create them again after.

Next steps

• Generate the SQL script of the whole model
• Keep the model in Git
• Automate synchronization with Groovy scripts
• Run synchronization from the command line