SQL + PlantUML: Generate Automatic Database Diagrams

5 min readDec 18, 2020

When it comes to showing up somewhere in your documentation a diagram describing your SQL database, you often end up with a recurring problem : after a few days / weeks / months, the diagram you made became obsolete.

Yeah, since you made that beautiful diagram, a foreign key has changed, a field that was previously a boolean is now a number, a table has been split in two, a new table has been created, a few fields have been renamed, others removed…

The whole purpose of this article is to keep your diagram up-to-date automatically.

Prerequisite: you are using TypeORM in your project (NestJS supports it by default).

Introducing diagram-as-code

PlantUML is a language specifically designed to generate diagrams.
Here is a sample of code to generate a diagram with two tables:

@startuml
!define primary_key(x) <b><color:#b8861b><&key></color> x</b>
!define foreign_key(x) <color:#aaaaaa><&key></color> x
!define column(x) <color:#efefef><&media-record></color> x
!define table(x) entity x << (T, white) >>table( user ) {
  primary_key( id ): UUID 
  column( isActive ): BOOLEAN 
  foreign_key( cityId ): INTEGER <<FK>>
}table( city ) {
  primary_key( id ): UUID 
  column( name ): CHARACTER VARYING 
  column( country ): CHARACTER VARYING
  column( postCode ): INTEGER
}user }|--|| city@enduml

When sending this code to a PlantUML server (you can use the official playground here), the server generates and outputs the following image:

Adding a few more lines at the top of the code, you can even add a bit of modernity with custom colors, shapes, directions:

@startumlleft to right direction
skinparam roundcorner 5
skinparam linetype ortho
skinparam shadowing false
skinparam handwritten falseskinparam class {
    BackgroundColor white
    ArrowColor #2688d4
    BorderColor #2688d4
}// !define primary_key ...
// table( ...
// table( ...@enduml

This will output the following image:

Generating the diagram code with TypeORM

You know how to generate a database diagram based on some PlantUML code. Don’t you dare think you are going to manually write this code!

Let’s generate the code that generates the diagram! 😎

Chances are that if you are developing a product with a database, you are using an ORM to interact with the database. If you are using TypeORM, subject of this article, you are in luck as there exists a package called typeorm-uml that enables you to generate a diagram directly from your entities!

The library transforms the entities available in your project to PlantUML code. It then calls the official PlantUML server with the PlantUML code then finally outputs a diagram image.

Through the power of Node and npm, install the library as a dev dependency in your project:

npm install --save-dev typeorm-uml

Given a standard TypeORM config file — usually something called ormconfig.json, you can write a simple script to generate your database diagram. Create a file generate-database-diagram.js , both JavaScript and TypeScript are supported:

import { TypeormUml } from 'typeorm-uml';new TypeormUml().build(TYPEORM_CONFIG_FILE_PATH, {
  'plantuml-url': 'http://www.plantuml.com/plantuml',
  'download': `${FOLDER_PATH_TO_OUTPUT_IMAGE}/diagram.png`,
});

Now every time you execute your script by running node generate-database-diagram.js, you should see in the output folder an image of your database diagram!

Generating the diagram may take a few seconds or up to a few minutes if your database schema is huge.

For handy purposes you could add the script in your package.json then be able to run the command npm run db:diagram:generate :

"scripts": {
  "db:diagram:generate": "node ./generate-database-diagram.ts"
}

NB: if you are not using TypeORM, you could Google your way to see if people already made a PlantUML code generator based on what you use, or even write your own PlantUML code generator?

The need to set up a private PlantUML server

In order to generate a diagram, your whole database schema needs to be sent to a PlantUML server.

Using the official PlantUML server may be okay for test purposes.
The typeorm-uml library uses the official server by default.

When it comes to dealing with a real database whose schema is in production, you could probably want to use your own server.

From a security point-of-view, it would be great not to expose your whole schema over the internet, especially to an external server you do not own, even if it is the official server of the PlantUML language.

The typeorm-uml library I introduced has an option to let you specify the url of your own PlantUML server. So let’s create one!

Docker to create your own PlantUML server

First, you need to install Docker Compose.

Then anywhere on your system, create a folder with the following two files:

a file Dockerfile-plantuml-server with the following content:

FROM plantuml/plantuml-server:tomcat
EXPOSE 8080

a file docker-compose.yml with the following:

version: '3'services:
  plantuml-server:
    build:
      context: .
      dockerfile: Dockerfile-plantuml-server
    ports:
      - 8089:8080

In this folder, run the following command:

docker-compose up -d && docker-compose logs -f

And, that’s it, you have your own PlantUML running locally on port 8089!

Go to http://localhost:8089 to access it.

When using the typeorm-uml library, you can now specify the url of your own PlantUML server. It will work the same as with the official server, except that your database schema will always stay on your machine.

Note: using your own server may generate diagrams slower or faster depending on your machine and resources allocated to Docker.

Automatically running the script

It is handy to manually generate the diagram from times to times using the previously created command: npm run db:diagram:generate. Though, getting the diagram to update itself on its own automatically without a developer interaction would ensure that it the diagram is never obsolete. There are several ways of doing this.

You could use a pre-commit git hook or even better simply configure your CI/CD pipeline(s) to run the npm script whenever something gets merged into the main branch 🙂

As for the private PlantUML server, you could either have a dedicated instance running all the time or you could mount/unmount the server on-demand with Docker every time you want to generate the diagram (by running docker-compose up -d, then npm run db:diagram:generate, then docker-compose down)

By the way, if this article helped you, don’t forget to clap & subscribe! 🙂
This helps me know that taking the time to write the article was worth it!