DbSchema | How to Document PostgreSQL Databases

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.

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:

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:

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:

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.

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.

Conclusion

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.