Import and Export the Model

Sometimes you get the CREATE TABLE script of a database, or the Flyway migrations of a project, but no server to connect to. DbSchema reads those files into a model without running them. In the other direction, DbSchema writes the model out as an SQL script, as the next Flyway migration, or as text in a layout you define.

Importing starts at File → Import Model from SQL or Data Files, which opens the Load Model dialog. Exporting starts at File → Export As...

Import a model from SQL files

Use this when you have the DDL script of a database and want its diagram. Importing a model needs DbSchema Pro. Here the file is suppliers.sql:

CREATE TABLE suppliers (
    supplier_id integer PRIMARY KEY,
    name        varchar(80) NOT NULL
);

CREATE TABLE purchase_orders (
    purchase_order_id integer PRIMARY KEY,
    supplier_id       integer NOT NULL REFERENCES suppliers,
    ordered_at        timestamp NOT NULL
);

1. Choose File → Import Model from SQL or Data Files.
2. Under Read from, choose Files.
3. Press Choose and pick one or more files.
4. Leave Read as on Detect automatically, unless the tables come out with wrong characters in their names or comments.
5. Under Put the result in, choose A new model and set DBMS to the dialect the files are written in, here PostgreSQL. To add the tables to the model you have open instead, choose The design open now.
6. Press Load Model.

The dialog at the top of this page shows these fields. A new model opens in a window of its own, offline, with the tables suppliers and purchase_orders and the foreign key between them. DbSchema also opens each file in an SQL Editor below the diagram.

The import changes the model only, and nothing runs against a database. Where the parser can't apply a statement, the Import Report lists the files that hold such statements, so you can see what the model is missing.

File → New Model leads to the same dialog: choose Open SQL or CSV File, pick the database, press Browse... to pick the file, and press Import.

Import a folder of Flyway migrations

A Flyway project keeps its schema as numbered scripts. DbSchema replays them in the order a database would apply them, so a script that alters a table changes the table that an earlier script created. The folder here holds four scripts:

V1__create_suppliers.sql
V2__create_purchase_orders.sql
V3__add_supplier_email.sql
R__open_purchase_orders.sql

V3__add_supplier_email.sql changes a table of V1, and the repeatable script defines a view:

ALTER TABLE suppliers ADD COLUMN email varchar(120);

CREATE OR REPLACE VIEW open_purchase_orders AS
SELECT purchase_order_id, supplier_id, ordered_at
FROM purchase_orders
WHERE status = 'OPEN';

1. Choose File → Import Model from SQL or Data Files.
2. Under Read from, choose Migration folder.
3. Press Choose and pick the folder, such as db/migration.
4. Check the order under Scripts to replay. Applied as says how DbSchema reads each script.
5. Choose where to put the result, as for SQL files, and press Load 4 Scripts. The number counts the scripts in the list.

The options above the list choose which scripts take part:

Option	Scripts it adds to the replay
Sub-folders	The scripts in the folders below the chosen one
Repeatable (R__)	The scripts that Flyway reapplies on every migration, replayed last
Undo (U__)	The scripts that reverse a version

Repeatable scripts usually hold views and functions. While Undo (U__) is off, the list marks each undo script as skipped. Leave it off: replayed after its version, an undo script takes apart what the version built.

The result holds the last state of every table. suppliers has the email column of V3, and the view of the repeatable script sits beside the tables:

DbSchema parses the scripts and never runs them, so the import needs no connection and changes the model only.

Export the schema as an SQL script

An SQL script of the model creates the schema in another database, or keeps a copy of it next to your code.

1. Choose File → Export As... → Export Schema & Data, or press F2.
2. Keep Schema Script ticked. It needs no connection, because DbSchema writes the script from the model.
3. Under Destination, choose SQL Editor ( only for small projects ) or File.
4. Press Export, then tick the schemas and tables to include and press OK.

The toolbar's Refresh menu holds Export Schema & Data too. DbSchema writes the script and doesn't run it. The script of the shop model starts with the schema and its sequences, then creates each table:

CREATE SCHEMA IF NOT EXISTS shop;

-- …
CREATE SEQUENCE shop.categories_category_id_seq AS integer START WITH 1 INCREMENT BY 1;
-- …

CREATE  TABLE shop.categories (
	category_id          integer DEFAULT nextval('shop.categories_category_id_seq'::regclass) NOT NULL  ,
	parent_id            integer    ,
	name                 varchar(80)  NOT NULL  ,
	CONSTRAINT categories_pkey PRIMARY KEY ( category_id )
 );

The foreign keys come at the end of the script:

ALTER TABLE shop.categories ADD CONSTRAINT categories_parent_id_fkey FOREIGN KEY ( parent_id ) REFERENCES shop.categories( category_id );

Option	What it changes in the script
Content	Manual Selection asks for the tables, Current Diagram takes the diagram's
Data from Database	Adds the rows of the tables, which needs a connection
Exclude AutoIncrement Columns	Leaves autoincrement and generated columns out of the data
Prefix table name with schema name	Writes shop.categories instead of categories
Foreign keys inside CREATE TABLE	Writes each foreign key in its CREATE TABLE and orders the tables to match

With Foreign keys inside CREATE TABLE ticked, even a foreign key of a table to itself goes inside its table:

CREATE  TABLE categories (
	category_id          integer DEFAULT nextval('shop.categories_category_id_seq'::regclass) NOT NULL  ,
	parent_id            integer    ,
	name                 varchar(80)  NOT NULL  ,
	CONSTRAINT categories_pkey PRIMARY KEY ( category_id ),
	CONSTRAINT categories_parent_id_fkey FOREIGN KEY ( parent_id ) REFERENCES categories( category_id )
 );

That excerpt also has Prefix table name with schema name cleared. The sequence names in the column defaults keep their schema, while the sequences themselves are created without it. In PostgreSQL 17, every table that takes a sequence default then fails:

ERROR:  relation "shop.categories_category_id_seq" does not exist

Keep the prefix ticked, or run SET search_path TO shop; after CREATE SCHEMA so that the sequences and tables land in shop.

Write the next Flyway migration

The Flyway Manager compares the model with what you already have and writes the difference as the next migration script. You keep designing in DbSchema, and each change reaches the database through a script that Flyway applies.

1. Choose Model → Flyway Migrations..., then press Choose and pick the migration folder.
2. Under Compare with, choose what the model is compared with.
3. Press Compare to show the script of the new migration on the right.
4. Set the Version and the Description. The file name follows them, such as V1__create_shop_schema.sql.
5. Press Write Migration File, which needs DbSchema Pro.

Compare with	What the model is compared with
Flyway files	The schema that the scripts of the folder build when they are replayed in order
Database	The connected database
Git revision	The model as a branch, a tag or a commit of the repository holds it
Design model file	Another DbSchema model file, which Choose Model picks

Compared with an empty folder, the shop model gives one difference, the whole schema, and the first migration creates it. The line under the script counts the differences and how many of them drop an object, so a migration that would drop a table shows before you write it. The Flyway Manager writes the file only. The database changes when Flyway applies the script.

To decide difference by difference, press Review in Synchronization. The synchronization dialog opens with the same comparison.

Export the model in your own format

A custom export writes the model as text in a layout you define, such as one line per column. It needs DbSchema Pro.

1. Choose File → Export As... → Custom Schema Export.
2. Press Export Definition, type a name, such as Columns CSV, and press OK. The definition starts with Iterate Schemas.
3. Select Iterate Schemas, press Iteration, choose Iterate Tables and press Select. Add Iterate Columns under Iterate Tables the same way.
4. Select Iterate Columns, press Field, choose Name and press Select. Add Data Type the same way.
5. Set Format to Comma Separated.
6. Press Export to see the text.

The Format is a pattern for each field. DbSchema replaces ${value} with the value of the field, so ${value}, writes each value followed by a comma, including the last one of a line. The Export Result window shows the text, and Save to File keeps it as a .txt, .csv or .xml file:

address_id,integer,
customer_id,integer,
street,varchar(120),

File → Export As... also holds the HTML5/PDF Documentation and Diagram Image, which saves the diagram as a PNG, GIF, JPG or SVG image.

Common problems

Where is Export Schema & Data?

Choose File → Export As... → Export Schema & Data, or open the menu of the Refresh button on the toolbar, which holds it too. Its shortcut is F2.

Next steps

• Compare the model with the database
• Generate HTML5 documentation
• Keep the model in Git
• Load data from CSV and Excel files