Import and Export the Model

DbSchema can build a model from files instead of from a database: SQL scripts, data files or a folder of migration scripts. It can also write the model out again, as an SQL script, as a Flyway migration or in a text format that you define.

Both menus are in File: File → Import Model from SQL or Data Files and File → Export As....

Import a model from SQL or data files

Use this when you have the CREATE TABLE script of a database, but no connection to it. Importing a model needs DbSchema Pro.

1. Choose File → Import Model from SQL or Data Files.
2. Under Read from, choose Files: one or more SQL, CSV, XML, Excel or JSON files.
3. Press Choose and pick the files.
4. Leave Read as on Detect automatically, unless the file shows wrong characters.
5. Under Put the result in, choose one:
   • A new model, and set DBMS to the SQL dialect the files are written in, such as PostgreSQL.
   • The design open now, which adds the tables to the model you have open.
6. Press Load Model.

The picture at the top of this page shows the dialog. DbSchema only reads the files. Nothing runs against a database. The tables then show on a diagram.

You can also start from File → New Model: choose Import from File, pick the file and the database, and press Import.

Import a folder of Flyway migrations

A Flyway project keeps its schema as numbered scripts, such as V1__create_suppliers.sql. DbSchema reads them in the order a database would apply them and builds one model from the result. A script that alters a table changes the table that an earlier script created.

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 in the list. Applied as says how DbSchema reads each script.
5. Choose where to put the result and press Load 4 Scripts. The number counts the scripts in the list.

The options above the list choose which scripts take part:

• Sub-folders: also read the scripts in the folders below.
• Repeatable (R__): include the scripts that Flyway runs again on every migration, usually views and functions. DbSchema applies them last.
• Undo (U__): include the scripts that reverse a version. Leave it off, because an undo script takes apart what the version before it built.

DbSchema parses the scripts only. It never runs them and needs no connection. The new model starts offline.

Export the schema as an SQL script

An SQL script of the whole model creates the schema in another database, or keeps a copy of it in version control.

1. Choose File → Export As... → Export Schema & Data, or press F2.
2. Keep Schema Script ticked. It needs no connection. Tick Data from Database to add the rows of the tables, which needs a connection.
3. Under Destination, choose SQL Editor or File.
4. Press Export.
5. Tick the schemas and tables to include and press OK.

The script opens in an SQL Editor or goes to the file. DbSchema does not run it.

The dialog has more options:

• Content: Manual Selection asks which tables to include. Current Diagram takes the tables of the open diagram.
• Exclude AutoIncrement Columns: leaves out of the data the columns that the database fills by itself.
• Prefix table name with schema name: writes shop.customers instead of customers.
• Foreign keys inside CREATE TABLE: writes each foreign key in its CREATE TABLE statement where it can. Without it, the foreign keys come at the end of the script as ALTER TABLE ... ADD CONSTRAINT statements.

The SQL Editor suits small schemas. For a large one, write to a file.

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.

1. Choose Model → Flyway Migrations....
2. Press Choose and pick the migration folder.
3. Under Compare with, choose what the model is compared with:
   • Flyway files: the schema that the scripts in the folder build.
   • Database: the connected database.
   • Git revision: the model as a branch, a tag or a commit holds it.
   • Design model file: another .dbs file.
4. Press Compare. The script of the new migration shows on the right.
5. Set the Version and the Description. The file name follows, such as V1__create_shop_schema.sql.
6. Press Write Migration File.

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 and type a name, such as Columns CSV.
3. Select Iterate Schemas and press Iteration to add Iterate Tables. Add Iterate Columns under it the same way.
4. Select Iterate Columns and press Field for each value to write, such as Name and Data Type.
5. Choose the Format, such as Comma Separated.
6. Press Export.

The Format is a pattern. DbSchema replaces ${value} with the value of each field, so ${value}, writes the values separated by commas. Filter leaves out the items that do not match a condition.

The Export Result window shows the text. Press Save to File to keep it.

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

Common problems

Where are Import and Export As?

Both are in the File menu: File → Import Model from SQL or Data Files and File → Export As.... Model → Flyway Migrations... writes migration scripts.

Next steps

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