Back to articles list
Data modeling for PostgreSQL is no longer a challenge. Read on and learn about the best tool for PostgreSQL ER diagrams.
PostgreSQL, a.k.a. Postgres, is a free and open-source relational database management system (RDBMS). It has become popular for database development among major organizations such as Instagram, Skype, and Netflix.
Since the ERD (Entity Relationship Diagram) is an essential tool in data modeling, it’s worth the effort to find the perfect ERD tools that support Postgres. In this article, we compare the features of several excellent PostgreSQL ER diagram tools to help you determine the best one for you.
1. Vertabelo
Vertabelo is an online data modeling tool that has ERD capabilities for Postgres and many other database management systems such as MySQL, Microsoft SQL Server, Oracle, and SQLite. It has a clean, modern, and responsive UI and many attractive features such as model validation and automated tasks to move quickly through conceptual, logical, and physical diagrams. Let me explain more about Vertabelo’s key features below.
Clean, Modern, and Responsive UI
Vertabelo’s online Postgres design tool has a user-friendly, clean, modern, and responsive user interface (UI). Even new users can use the Vertabelo modeling tool easily. You can take an “application tour” in the Help menu if you need a guided tour of the Vertabelo user interface. The diagram area, the navigation tree, the properties panel, the main toolbar, and the toolbox are some of the essential regions of the Vertabelo online tool to make your tasks easier.
Validating PostgreSQL Data Models
Vertabelo understands the unique parameters of PostgreSQL. For instance, it knows the PostgreSQL naming conventions, data types, and constraints. As a design tool, it validates your physical model against the Postgres rules and alerts you for problems.
Logical and Physical Diagrams
Vertabelo has all the essential features to create your logical and physical diagrams. They let you add new entities, various types of relationships, associations, and other components of your models.
This data modeling tool supports Crow’s foot, UML, and IDEF1X notations.
Converting Logical Models Into Physical Models
In Vertabelo, you don’t have to create your Postgres physical model from scratch if you have already done your logical modeling. It has an automated feature to generate your physical model for PostgreSQL using your logical model. You can read the step-by-step guide for generating the physical data model from a logical data model here.
Support for Collaboration
You can share your models with anyone who has a Vertabelo account by simply providing the e-mail address. You can share models at three different access levels: Owner, Editor, and Viewer. You can change the access levels anytime. You can even share the model with people who do not have Vertabelo accounts, such as partners and customers, by creating a public link to your model and sending it to them via email.
Multiple users can open and work on the model at the same time. Learn more about collaboration in Vertabelo.
Generating PostgreSQL DDL Files
Automated DDL file generation is one of the key features in this Postgres ER diagram tool. You can generate a PostgreSQL script to create or remove some or all elements of your database from the physical data model.
Learn more about SQL generation in Vertabelo.
Importing Existing PostgreSQL Databases
Vertabelo has three ways to import an existing Postgres database:
- Through a live connection to the database using the Vertabelo Reverse Engineering Tool.
- By importing from the DDL SQL Script (or XML).
- By simply copying an existing data model you have already created in Vertabelo.
Learn more about importing an existing database into Vertabelo.
Vertabelo is an online tool for PostgreSQL with all the essential features a perfect ER diagram tool should have. Some essential features like support for remote and collaborative work, change management, and easy revisions, as well as ease of solving practical issues in database development enabled by these features, bring Vertabelo up to the highest rank among other competitors.
2. Navicat
Navicat is an ER diagram tool for visually designing your conceptual, logical, and physical data models for the Postgres DBMS. In addition to PostgreSQL, it supports many other DBMSs such as MySQL, SQL Server, MariaDB, and Oracle. Let’s have a look at the key features of Navicat.
Data Modeling
As a Postgres ER diagram tool, Navicat has the essential features for modeling your conceptual, logical, and physical data models. You can create complex ER Diagrams in Navicat using one of three standard notations: Crow’s Foot, UML, or IDEF1x. With a user-friendly, simple user interface, this tool enables the automated creation of physical data models from conceptual and logical data models.
Reverse Engineering
Reverse engineering is one of the key features of Navicat. It loads existing database structures of Postgres and many other supported DBMSs into the tool and creates a new ER diagram. Also, it visualizes database models without showing the actual data so that you can see how the elements such as attributes, indexes, and relationships relate to each other.
Comparison and Synchronization
When you revise your data model in Navicat, its database synchronization feature compares the target database against the revised model. Then, the tool generates a script to update the changes into your database. You can customize the comparison and synchronization with its flexible settings.
Generating SQL Codes
Not only does Navicat create ER diagrams and design your database, but it also lets you generate SQL scripts through its Export SQL feature for individual parts of the model. This saves hundreds of hours of work.
Navicat is equipped with the essential standard features of a perfect data modeling tool and supports many popular DBMSs. In addition, the comparison and synchronization option speeds up database revisions. Integration with Navicat cloud enables you to sync your model files and give you real-time access from anywhere anytime. These features give Navicat an advantage that improves productivity for database architects.
3. Visual Paradigm
Visual Paradigm is an online modeling tool that supports different sets of notations for various diagrams including data models. It supports PostgreSQL and many other DBMSs including MySQL, Oracle, SQL Server, Sybase, SQLite, HSQL, and MariaDB.
Data Modeling
As a Postgres ER diagram tool, Visual Paradigm helps you design your conceptual, logical, and physical data models. It has a user-friendly interface and accommodates the notations to draw your data models, including Crow’s Foot notations.
The Table Record Editor feature of this design tool enables you to enter sample records for the entities in the ERD. These sample records help users understand the formats of the data inserted into the database.
Syncing Between Models
Visual Paradigm’s Model Transitor helps you derive your logical model from the conceptual model and your physical model from the logical model so that you don’t need to create them from scratch. This saves you time, maintains traceability between the models, and allows you to navigate easily between them.
Generating Your Database From the ERD
Like other PostgreSQL ER diagram tools discussed in this article, Visual Paradigm lets you generate DDL files from selected entities or from the entire ERD to create your physical database. It also has a feature for patching revisions made in the data model into the physical database. After analyzing the database, the Patch Tool creates the SQL script needed to patch the changes in the database.
Reverse Engineering
Reverse engineering is supported in Visual Paradigm. It lets you import an existing Postgres database or any other database of supported DBMS so that you can edit them visually.
Visual Paradigm has many of the common essential features that save time and improve productivity. The Patch Tool, which manages revisions efficiently, and the Table Record Editor, which helps you understand the data formats entered into the database, should delight database architects.
4. SqlDBM
SqlDBM is an online PostgreSQL ER diagram tool that enables collaborative work with your teams on any platform. It also supports many other DBMSs such as MySQL, SQL Server, and Amazon Redshift.
Drawing ER Diagrams
SqlDBM has many features and accommodates notations like Crow’s Foot and IDEF1X to model your data from the conceptual to the physical. Below is a data model of an online shopping app modeled in SqlDBM.
Forward and Reverse Engineering
In SqlDBM, you can generate a DDL script from the physical model you create for PostgreSQL. Also, with its reverse engineering feature, you can copy and paste a DDL script or upload a DDL file generated from an existing PostgreSQL database. This creates a corresponding data model in SqlDBM to be edited visually.
Working Collaboratively
You can share your SqlDBM diagrams with your team members and work on them collaboratively. You have to provide their email addresses to share your model. You can control the access levels by checking or unchecking a box.
Version Control
You can maintain different versions of your SqlDBM data model, and you can compare the changes with its Compare Revisions feature.
SqlDBM has the essential features a perfect Postgres design tool should have. In particular, its Compare Revision feature helps database architects manage changes effectively. However, as a modeling tool, it needs improvements in model validation against PostgreSQL.
5. ERDPlus
ERDPlus is a web-based PostgreSQL ER diagram tool. It has the features for creating conceptual and logical ER diagrams, relational schemas (physical data models), and SQL DDL statements, among others. In addition to PostgreSQL, it supports many other DBMSs such as MySQL, SQL Server, Oracle, and IBM DB2. Let’s discuss some of the key features of ERDPlus.
Data Modeling
ERDPlus supports notations like Crow’s Foot and has the features for moving through your conceptual, logical, and physical data modeling phases from scratch. Its notation capabilities let you draw regular and weak entities, different types of attributes, and all possible cardinality constraints of relationships.
This is a logical ER diagram of a simple library system drawn in ERDPlus.
ERDPlus provides two options for creating your relational schema (physical data model):
- Designing the physical model (relational schema) with all the elements from scratch.
- Using the automated feature to convert your logical ER diagram (logical model) into a relational schema (physical model).
Generating SQL Scripts
In ERDPlus, you can generate an SQL script for your relational schema (physical data model) with a single click so that you can create your physical database.
As a Postgres design tool, ERDPlus is most suited for modeling your data from scratch. It supports through the entire data modeling process, generating your physical model from the logical model automatically, generating SQL script for the physical data model, and exporting your diagrams to PNG format, among other capabilities. However, there are limitations in this tool, as it does not offer features such as reverse engineering and model validation against databases.
6. Lucidchart
Lucidchart is an online multi-purpose diagramming tool that supports drawing many types of diagrams including ER diagrams. It supports PostgreSQL and many others like MySQL, Oracle, SQL Server, and Salesforce. So, we can consider Lucidchart a PostgreSQL ER diagram tool.
Creating ER Diagrams
You have two options for creating your ER diagram in Lucidchart. You can create it manually from scratch or have Lucidchart generate it automatically by importing data. For manual drawing, it offers you templates and a shape library. You must enable the shape library from the Shape Manager.
Exporting Models
Lucidchart creates SQL queries to export your ER diagram to PostgreSQL and other supported DBMSs.
Sharing Your Model
You can work collaboratively with your team from anywhere by inviting them to access or by sharing a public link with them at different access levels: edit and share, edit, comment, or view. You can also publish your model via a continuously updated link or embed it on your intranet or web page using the generated HTML code in this tool.
Considering the key features, Lucidchart is recommended for collaborating with your team online to model your data and draw other diagrams for your development project. As a data modeling tool, however, it needs improvements in model validation against PostgreSQL and other supported databases.
Which One Is the Best for PostgreSQL?
Many popular PostgreSQL ER diagram tools are available in the market today. I have selected six tools from them. Ranking these tools are very challenging. They all have most of the essential features a perfect ER diagram tool should have. They include good user interfaces and various notations for data modeling, forward and reverse engineering, automated features for generating data models, and model sharing options, among others.
However, considering the entire process of database design, which is more than an ERD, Vertabelo is my Number One. It has a clean, modern, and responsive UI. Features such as model validation, automated tasks for much of the data modeling activities including creating the physical data model, version management, change and revision management, and the range of supported DBMSs, make the life of database architects much easier. Visual Paradigm is my Number Two, with valuable features like the Patch Tool, syncing between data modeling levels, and the Table Record Editor, in addition to other key features.
