DbSchema Database Designer

DbSchema | How to Document PostgreSQL Databases

Publish on DbSchema Blog >>>

Database documentation is a vital yet often overlooked part of every IT project. Keeping the documentation updated has to be a consistent effort from the team. Because most often this effort doesn’t give an immediate result it can be overlooked.

What is database documentation?

Good database documentation can be compared with the blueprint of a building. It contains every aspect of the database, the indexes, tables, columns, data-types, etc. Each of them with comments and callouts, making it easier to understand the role of every database object. Most of the time, the documentation is the common ground between the development or testing teams and the business teams.

The documentation can reduce risks during maintenance because the team can follow the implications of every action. Because it facilitates the transfer of information, good documentation will also reduce the time (and cost) of newcomers training.

The documentation has a bigger impact and a longer lifespan than one might expect. Of course, the case varies from project to project, but most often a database will be accessed by more than just the development or project management team. It can be an outside BI Consultant or an Auditor that has nothing to do with your project and having good documentation can save time and effort.

The classic way of documenting PostgreSQL databases

The way of documenting the database plays an important part in making it a constant task. To document a PostgreSQL database from the shell, you can use the COMMENT command. This will add descriptions to your columns, tables, indexes, etc. COMMENT is a custom Postgres command and can’t be used as a standard SQL query.

To view data and add comments to it, you first have to access the PostgreSQL shell. Once there, execute \d+ to view all your tables with descriptions. Executing a regular \d command will only show a few details about your tables leaving out the description.

view data

As we can see above, the actor table doesn’t have any description. To insert a comment, we can execute the next command:

COMMENT ON TABLE actor IS ‘Table containing actors’;

The result will be:


As we can see, the description of the table was updated. Let’s do another example, but this time for a column. To view the columns of a table, we will execute:

\d+ actor

It’s similar to the command from above, the only difference is that we specify the name of the table at the end. This should give us:

columns result

Now, I will add a comment to the actor_id column by executing:

COMMENT ON COLUMN actor.actor_id IS ‘id of the actor’;

And the result:

columns with comments

Similarly, you can add a comment on indexes. Instead of \d+ you will use \di+ and COMMENT ON INDEX index_name IS comment;

How do I export?

We solved the documenting part but exporting the documentation visually is another problem. To do this you will have to use another program or create it manually, but this will take a lot of time and will become outdated most probably.

Document Visually

You can do both things (documenting & exporting) from a single place and without too much trouble. With DbSchema you don’t need to write queries in the PostgreSQL shell, you can document the database from a friendly GUI.

DbSchema is a database design & management tool that allows you to interact with the diagram and edit the database objects right there. First, you have to connect it to your Postgres database. Don’t worry about JDBC drivers, DbSchema already integrates it.

After connecting, DbSchema will reverse engineer the diagram. Being interactive, the diagram makes it very easy to document your database. If you want to add comments to tables or columns, all you have to do is double-click on it and insert the comment:

documentation comment

Yes, it’s that simple. In DbSchema you can also insert callouts. These are general comments that will show in the diagram.


Exporting Documentation

DbScheama has an innovative approach to database design. The diagram is viewed in a layout. A layout is a tab in which you can add tables, edit them, add foreign keys, etc. Basically, it is a drawing board for your database design. There can be multiple layouts in a project, each focused on a specific part of the database. Also, a table can be present in multiple layouts.

This gives you the freedom to create a layout including what tables you want from the database and export an HTML5 or PDF documentation based on it. The export of documentation is just a few clicks away.

documentation dialog

Here you can select what to include in your documentation. After everything is set, select a place to export it and you’re done.


The HTML5 documentation is interactive. Meaning that it will show details when you hover database objects like in the image above. If you click on an object, it will automatically jump to the table containing info about it. The HTML5 documentation can be opened with any internet browser.


Keeping the documentation updated is very important for the project. It brings benefits to the technical aspect, making it easier to understand the database, and also to the team, enhancing the communication between departments.

This task can be made easier by using tools like DbSchema. It offers a friendly interface that makes documentation update & export seem like a child’s play.

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.