Improve Your PostgreSQL Database Design and Management with DbSchema

A PostgreSQL database you did not design yourself hands you a list of table names. In psql, \dt prints those names and \d prints one table at a time, so the foreign keys between them stay in your head. DbSchema reads the whole schema over the same connection and draws it: every table, every column, and a line for every foreign key, on a canvas you can rearrange, query, browse and export.

What DbSchema does with a PostgreSQL database

The features below are the ones a PostgreSQL user reaches for first, with the edition each one belongs to. Before the list, one distinction that decides what any of them can do to your database. DbSchema keeps its own copy of the schema in a design model file, a .dbs file in XML on your computer, and that file holds the diagrams and their layouts, the descriptions you type, the virtual foreign keys and the generator patterns. None of that exists in PostgreSQL, and editing it touches nothing on the server. The features that do reach the database announce themselves: the two editors hold their changes until you press Commit, the Data Generator inserts rows when you press Generate, and the synchronization dialog runs its statements when you press Execute. Each section below says which side of that line it works on.

Connect DbSchema to PostgreSQL

DbSchema talks to PostgreSQL over JDBC, and it downloads the driver for you when you create the connection, so there is nothing to install beside DbSchema itself. Choose PostgreSQL from the database list and DbSchema opens the Connection Dialog already configured for it. The Connection tab takes the Server Host and Port, the Database User and Password, and the database to open; Test Connection checks that the server answers before you commit to it, and Connect opens the session, which DbSchema saves for next time.

Two settings are worth a look on a database that matters. The Advanced tab holds the JDBC URL parameters, where the PostgreSQL SSL Mode decides how strictly the server is checked: Prefer tries SSL and falls back to a plain connection, Require always encrypts without checking the certificate, and Verify CA encrypts and checks the certificate against a trusted CA. The Settings tab holds Read Only Connection, which makes the database refuse every change made through DbSchema, and Highlight, which colors a connection as Production so you can tell it from Development at a glance.

Connecting reads the structure, not the contents: DbSchema pulls the table names, columns and keys, and reads table data only when you open a table or run a query. What it builds from that structure is a design model, saved as a .dbs file on your own computer.

Read the schema as a diagram

Reverse engineering ends with diagrams already drawn: DbSchema places the tables and connects them with a line for every foreign key, with crow's foot symbols at the ends to show the cardinality. Barker and plain-line notations are in the Diagram menu if your team reads one of those instead, and Show Column Types in the same menu adds the data type next to each column name.

A schema of any size arrives as a tangle, and two commands untangle it. Diagram, then Auto Arrange lets DbSchema lay the selected tables out with a graph algorithm. Right-click the canvas, choose New Group, and DbSchema clusters the tables you drag into it as a named, colored box that moves as a unit, which is how a schema with several modules becomes readable. One project can hold many diagrams, each with its own layout and its own set of visible tables, so the orders diagram does not have to show the reporting tables.

Everything on the canvas, including the table positions, the groups and the notes you add from the Insert menu, is stored in the .dbs model file rather than in PostgreSQL. Rearranging a diagram changes nothing in the database.

Build and run queries

The DbSchema SQL Editor opens from the Editors menu and runs whatever you type against the connected database. Execute Query runs the statement at the cursor and shows the result as a table; Run Script runs the whole editor content and prints the output as text, with Ignore Errors and Auto-Commit on its dropdown. Ctrl+Space opens auto-complete over the tables and columns of the connected schema, and the SQL History pane keeps every statement of the session so you can click one back into the editor. Because PostgreSQL needs an explicit commit for DML, the Commit and Rollback buttons in the toolbar decide what happens to an INSERT, UPDATE or DELETE you have run.

The Query Builder writes the SELECT for you. Click a table header in the diagram and DbSchema opens the builder with that table loaded; click the small arrow next to a column to follow a foreign key, and DbSchema adds the related table and the join. Click the join type label on the connecting line to switch between INNER JOIN, LEFT JOIN and EXISTS, right-click a column for a WHERE condition, and turn on Group By mode to aggregate with MIN, MAX, SUM, AVG or COUNT. The generated SQL updates at the bottom of the builder as you click. The SQL Editor is in the free Community Edition, and the Query Builder is in Pro.

Browse and edit rows across related tables

A row in one table rarely answers a question on its own. The Relational Data Editor opens several tables at once and keeps them in step: pick a row in the parent pane and every child pane reloads to show only the records related to it through the foreign key, as many levels deep as the schema goes. Right-click a table header in the diagram, choose Open in Relational Data Editor, and DbSchema opens it in the Tools panel at the bottom of the screen. To start an empty one, open the Editors menu and choose New Relational Data Editor. Holding Shift and Ctrl while clicking a table header opens the editor inline on the diagram for a quick look.

Editing works in the same panes. Insert adds a row through an edit form, Edit or a double-click changes a cell, Delete removes the selected row, and a click on a column header opens a filter for that column. As in the SQL Editor, changes wait for Commit and disappear on Rollback, so a wrong value costs you nothing until you decide. When the schema has no foreign key between two tables that clearly belong together, drag one column onto the other in the diagram: DbSchema stores that virtual foreign key in the model file, creates no constraint in PostgreSQL, and the Relational Data Editor cascades through it exactly as through a real one. Browsing related data this way is a Pro feature.

Fill empty tables with test data

An empty schema tells you nothing about how it will behave, and typing forty rows by hand tells you nothing either. The DbSchema Data Generator opens from Data Tools, then Generate Random Data, or from a right-click on any table header. Set how many rows each table gets, and DbSchema assigns every column a pattern that decides what its values look like: a numeric range, a sequence, a date range, a reverse regular expression, or an entry from the pattern repository for first names, cities, email addresses and phone numbers.

Two settings make the result usable rather than merely present. The generator dialog lets you reorder the tables so a parent is filled before the table whose foreign key points at it, which is what keeps the inserts from failing. Per column, the Nulls percentage decides how often the value is missing, and a Seed makes the sequence reproducible, so two columns that share a seed generate the same values and a rerun generates the same data as last time. Patterns are saved in the .dbs model file and reused in the next session. Generating writes rows into PostgreSQL, so point it at a development database. The Data Generator is a Pro feature.

Keep the model and the PostgreSQL database in step

The model and the database drift apart the moment someone deploys a migration you did not make, and DbSchema compares them instead of leaving you to diff two dumps. Schema, then Refresh Schema from Database pulls the current database state into the model. Schema, then Compare Model with Database lists what differs, object by object, and for each difference you choose to update the model, push the change to the database, or skip it. Schema, then Synchronize Model with Database generates the migration SQL, shows it for you to read and edit, and executes it when you click Execute.

Working the other way is just as normal. Disconnect and DbSchema writes your changes only to the .dbs file, so you can design a change on a train and review the whole diff when you reconnect. A database that has none of your tables yet is the third case, and Schema, then Create or Upgrade Schema in Database generates the DDL for the entire model. When the same steps have to run without anyone watching, schema synchronization can be scripted with Groovy automation scripts or the DbSchemaCLI and run headless in a pipeline. Synchronization is a Pro feature.

Document the schema and version it with the team

Colleagues who need to read your schema will not install DbSchema to do it. Diagram, then Export HTML5 or PDF Documentation turns the DbSchema model into documentation in HTML5, PDF or Markdown. The HTML5 output opens in any browser with no server behind it, carries the diagram as a vector image and a searchable table list, and shows the Description you typed on a table or a column as a mouse-over tooltip. Markdown is the one to commit next to the code; PDF is the one to send to a review.

The model file itself belongs in version control, and DbSchema speaks Git directly. Open the Model menu and choose Git — Collaborative Design to clone a repository, then stage, commit and push the .dbs file the same way you would a source file, and pull what your colleagues pushed. After a pull, Compare with Current opens the synchronization dialog on the file that just arrived, so you can see what changed in the schema before any of it reaches your database. The Git dialog also creates branches and stashes work in progress, which means a schema change can travel through a branch and a review like everything else in the repository. Saving the model to a file, and therefore versioning it, is a Pro feature.

A PostgreSQL database that started as a list of table names ends up as a diagram you can query, a set of related rows you can edit, documentation your colleagues can open, and a file your team can review in a pull request. To try that on your own database, download DbSchema at https://dbschema.com/download.html, choose PostgreSQL, and enter the host, the database and your credentials. Connecting, reverse engineering, the diagrams and the SQL Editor are in the free Community Edition; the Query Builder, the Relational Data Editor, the Data Generator, schema synchronization and the exported documentation are in Pro.