We all know that we should produce good documentation, but we’ve all skipped out on it every now and then. It’s often a lot of mundane effort when you could be doing new exciting things.
Then days, weeks, months or even later the questions start coming in from your team-mates or new hires: why is this column like this, what does this column really describe etc. You no longer remember and you have to spend time to understand the database you’ve made.
One of the great things about PostgreSQL is that documenting your database is a piece of cake and that it saves you more time than you spend on adding comments. It’s without exaggeration less time consuming per table than answering a single question like the ones above.
Thankfully the syntax isn’t horrible and adding comments won’t be a slow affair if you have a lot of existing data unlike some other DB engines I’ve worked with.
PostgreSQL has support for storing comments directly in your schema:
You can add a comment to a table, a schema, an operator and so on.
Adding them is simple:
|
COMMENT ON TABLE mytable IS 'This is my table.'; COMMENT ON COLUMN my_table.my_column IS 'Employee ID number'; |
Some tools have better support for showing them than others and I hope to see that being improved in the future, for instance: Datagrip, my editor of choice will not show comments in the database explorer unless you hover over the object it does include it in the in-editor information pop-up when hovering over a column, function or table.
My personal favorite for generating visually pleasing documentation with little effort is SchemaSpy it requires Java and the JDBC driver to run and it generates pretty documentation in about a minute for a complex schema:
|
java -jar "/home/me/database/tools/schemaspy-6.1.0.jar" -dp "/home/me/database/drivers/postgresql-42.2.16.jar" -t pgsql -db postgres -s SCHEMA_GOES_HERE -host localhost -port 5432 -u USERNAME_GOES_HERE -p PASSWORD_GOES_HERE -o "/home/me/database/documentation/" -hq -nologo -vizjs |
The output is in the form of a relatively pretty website like this : Sample
I would say that this is quite a few levels of quality above what most companies have for their internal databases and with a lot less effort. As you can see from the sample it even supports markdown.
If you have suggestions for even better tools for the job, please join the People, Postgres, Data: Discord server
This work is unrelated to my job at Bamboo Solutions AS