Relational databases are widely used for storing and processing structured information, while their visualization is a key phase in the architectural design of information systems. One of the most popular tools for creating ERD is PlantUML, which implements the “diagram as code” approach for generating diagrams. This research analyzes PlantUML's capabilities for building ERD and explores tools that improve the diagrams' quality and automate their generation process. The shortcomings of PlantUML's basic functionality for creating ERD are described. Additional PlantUML features are described, such as improving the appearance and readability of diagrams by highlighting primary and foreign keys, removing unnecessary elements, and creating legends for user convenience. A plugin module has been developed to enhance the readability of PlantUML code (hereafter referred to as 'PUML code') by structuring it into functions and procedures, making diagrams easier to create and maintain. The benefits of the modular approach include standardized styles and a minimized amount of PUML code. The article also introduces the Sqlant tool, which allows PUML code to be automatically generated from a PostgreSQL relational database. The PlantUML, combined with automation tools like Sqlant and a modular approach, is an efective tool for producing high-quality ERD. It can be particularly beneficial in environments where database structures undergo frequent changes. Despite the limitations of the oficial documentation, PlantUML has significant potential to extend its functionality.
Relational databases have become a standard in software development due to their ability to help solve a wide range of tasks, especially given the popularity of web technologies. As the use of relational databases has grown, so has the number of professionals requiring database skills. Today, SQL knowledge is essential for software engineers, as well as technical support specialists, data analysts, business analysts, marketers, and other IT professionals. Teams of specialists often interact through internal wiki systems such as Confluence, Notion, etc., where information about projects and products, including database schemas, is shared.
ERDs are used to design databases [
Tools such as Lucidchart [
PlantUML is one such tool. PlantUML is an open-source tool for generating diagrams based on
text descriptions (PUML code) that allows simplifying the process of visualizing the physical model
of relational databases [
Despite its wide functionality, PlantUML is insuficiently documented at the oficial level, which
creates dificulties in its use. Therefore, the issues of improving the quality of diagrams and optimizing
the process of their generation require additional research [
The research aims to explore the capabilities of PlantUML, develop improved approaches to creating ERDs to provide more convenient and accurate modeling of relational databases and develop an automated system for generating PUML code directly from an existing database.
PUML code was tested on the oficial PlantUML website (https://www.plantuml.com/plantuml/uml). GitHub (developer platform) was used to host the PlantUML module. SQL queries were checked using RDBMS PostgreSQL version 15.0.
Docker (version 27.3.1) was used to spin up the local PlantUML server (version 1.2024.8) in the section 7.
The study is based on PlantUML diagram types such as the “Entity Relationship Diagram” which, as
stated in the documentation, is an extension of the “Class Diagram” with support for relationship
descriptions like “Information Engineering Relations”. Figure 1 shows an example of an ERD, taken
from the documentation [
Let’s take a PostgreSQL database and create the “customer” and “customer_order” tables, which are connected using a foreign key. Additionally, define and use the enumerated type “shipping_method” (figure 2).
According to the PlantUML documentation [
The PlantUML documentation suggests using the “*” symbol as a visibility modifier to indicate
mandatory attributes [
Since “Entity Relationship Diagram” (in PlantUML) is intended not only for displaying schemes of relational databases, such a view (Figure 3) has a number of disadvantages: • A circle with the letter “E” to the left of the table name has no information value in our case. • Foreign keys are weakly highlighted, they are usually distinguished by colors or additional symbols. • “*” symbol, which indicates that a field is mandatory (NOT NULL constraint) and depicted as a black circle to the left of the variable name, may be confusing to a diagram user unfamiliar with PlantUML.
• The same font style for the variable name and its type makes them dificult to distinguish.
According to the authors’ studies [
Using the additional features of PlantUML, the following result can be achieved (figure 4). In figure 4(b), the following rendering flaws have been fixed: • Deleted letter (E) near the name of the table (using the command “hide circle”). • The primary key is represented by the “golden key” symbol.
• The foreign key is represented by the “gray key” symbol.
Among the disadvantages, it should be noted that the PUML code in figure 4(a) has become more ‘disorganized’ and less readable. 3.2. Enum Most SQL databases (MySQL, PostgreSQL, Oracle, etc.), as well as most programming languages, support the enum data type. Using the “entity” keyword creates an unwanted artifact during rendering the diagram (figure 5, marked with a red arrow).
To remove this artifact, the ‘object’ keyword should be used. Additionally, highlighting the name of the list by adding the identifier ‘(E)’ can help make it easier to distinguish from other objects (figure 6).
Since some symbols (as mentioned above in disadvantages) may not be clear to the end user, a “legend” can be used to explain their meaning. As the diagram becomes more complex, the legend can be expanded. For example, in figure 7 shows four objects in the legend: Foreign Key, Primary Key, Not NULL, and Enum.
(a) (b)
PlantUML uses the relationship description based on the Information Engineering notation. Figure 8 shows the types of relationships that are supported.
Also, it is worth mentioning that it is possible to add a comment text to each relationship (figure 9).
The readability of the PUML code is an important characteristic because it is responsible for rendering
the diagrams and can be read and edited by diferent team members. To improve the readability and
convenience of writing PUML code, it’s worth reducing its volume. This can be achieved by using
built-in functions (!function) and procedures (!procedure) or by creating your own, which are organized
into plugin modules. It is important to note that there are built-in functions beginning with the symbol
’%’. You can find their brief descriptions on the oficial PlantUML website in the ‘Builtin functions’
section [
Figure 11 shows the enum procedure and its usage. The result of this code is shown in figure 6(b).
As can be seen from figures 10 and 11, the amount of code has increased rather than decreased because the example only shows one entity that uses custom functions. However, when there are more tables, this approach will be more eficient.
Procedures and functions that are used for the design of ERDs should be placed in a separate file with the .puml extension and included in the main file using the “!include” command. This approach provides modularity and improves code organization. The module file db_ent.puml was created with the next content: • Functions: table($name), column($name, $type, $pk=false, $fk=false, $nn=false); • Procedures: enum($name, $variants), add_legend().
To use the module, it must be placed on any web resource so that it has a permanent HTTP(s) address and is available to users. In this study, a public GitHub repository was used to host the PlantUML module. It can be accessed at the following address: https://raw.githubusercontent.com/kurotych/ sqlant/b2e5db9ed8659f281208a687a344b34f38129cd/puml-lib/db_ent.puml After the PlantUML module is hosted on web resource there is a possibility to include it in PUML code (figure 12).
This approach gives us the following advantages: • PUML code file contains only the logic of the diagram without unnecessary code-specific details, which improves the code readability. • Generic code can be reused in other files without the need for repetition. • Changes in the PlantUML module are automatically reflected in all related diagrams, which is useful for fixing bugs.
• The standardization of styles and procedures for all project participants simplifies teamwork.
This approach makes it easy to scale projects and keep code clear and organized. Designing functions (and procedures) in a module will provide an advantage even in the case with a single entity in a diagram.
A software tool called Sqlant (https://github.com/kurotych/sqlant, [
The internal Sqlant workflow operates as follows: 1. Retrieve information about the database schema, including table names, columns, foreign keys, and other related metadata. 2. Generate PUML code based on the retrieved information. 3. Output the PUML code to standard output, which can be redirected to a file using standard OS utilities.
After that, the user executes the received code on the PlantUML server side and receives the result in the form of a diagram. The generated diagram can be downloaded in several formats: PNG, SVG, ASCII Art. This tool supports several options for customizing the result. After installation, users can familiarize themselves with these options by using the --help argument (figure 14).
It is worth noting that in the above examples, the plantuml.com site was used to convert PUML code into PNG/SVG format. This site may (potentially) save these diagrams, as PlantUML generation occurs on the server side. This could be unacceptable for many companies, as it may expose the internal architecture of the database to third parties. Since PlantUML is an open-source product, its source code can be reviewed, and it can be installed locally. One option for installation is using the Docker containerization system. The following command will launch the PlantUML server (https://github.com/plantuml/plantuml-server) with a web interface accessible locally at the address: http://localhost:8080 (figure 15).
docker run -d -p 8080:8080 plantuml/plantuml-server:jetty
When replacing the use of plantuml.com with a locally installed plantuml.jar file (https://plantuml.
com/download) or a locally installed Docker container running the PlantUML server, this approach
becomes easily automatable. As a result, some engineers integrate this process into their bash scripts,
which can then be used in the CI (Continuous Integration) workflow (figure 16) [
During the study of PlantUML capabilities, the main functions of this tool for creating ERDs were
analyzed. An approach is proposed, which includes the development of a plugin module to simplify the
process of modeling the physical structure of databases. The module allows you to standardize code,
improve readability, reduce duplication, and facilitate teamwork. In addition, automation of the PUML
code generation process from the PostgreSQL relational database was implemented. The Sqlant tool
was developed for automation purposes [