Concepts

pgModeler is a database modeling tool for PostgreSQL. You draw the structure of a database (tables, their columns and how they relate to each other), and the tool writes the SQL that creates it. This page explains the vocabulary used throughout the manual.

Model and database are different things

A model is a file (.dbm) that describes a database. It lives on your computer and does not need a server to be created or edited. A database is what exists on a running PostgreSQL server. The model becomes a database when you export it, and a database becomes a model when you import it. When both already exist and drift apart, the diff shows what differs and generates the SQL that brings the database up to date with the model. The import and the diff are features of pgModeler Plus; exporting is available in both editions (see Community and Plus editions).

The model also keeps things a database does not: the position and colors of the objects on the canvas, layers, notes and so on.

Everything is an object

Each element of the model is an object: a table, a column, a schema, a function, a role... PostgreSQL has dozens of kinds of objects, and pgModeler has an editing form for each one (see Database objects). The forms look alike on purpose: they share the same first fields and tabs, so once you know one, the others are familiar.

The canvas is the area in the middle of the window where tables, views and relationships are drawn. Objects that are not drawn (a role, a function) are still part of the model and are listed in the model objects panel.

Objects live in a hierarchy

A model has one database; the database has schemas; schemas have tables, views, functions and more; tables have columns, constraints, indexes and triggers. A new model already comes with the schema public. Putting objects in schemas is how you keep a large database organized, exactly as in PostgreSQL.

Relationships do part of the work for you

Normally you do not write foreign keys by hand. You link two tables with a relationship (one-to-one, one-to-many, many-to-many and others) and pgModeler creates the columns and constraints that represent the link in the right table. Those generated columns appear in a different style (green and italic by default) and are managed by the relationship: change the relationship and they follow. This mechanism, called column propagation, is the main reason to model in pgModeler instead of writing SQL directly. It is described in Relationships.

It is also possible to go the other way: create the foreign keys yourself, as constraints of the tables, and pgModeler draws the relationships that represent them automatically (they are the foreign key generated relationships described in the same page). This approach gives you full control over the columns and constraints, but the relationship no longer maintains them for you, so it is recommended for advanced users. If you are starting out, prefer the relationships.

The model is validated before it is used

Since objects can be created in any order, a model can end up with an object that depends on another that does not exist yet, or two objects with the same name. The validation finds these problems, fixes most of them for you and sorts the objects in the order PostgreSQL needs. Validate the model before saving and before exporting. See Model validation.

Every object has its SQL

Select any object and you can see the SQL that pgModeler generates for it (Source code preview). The tool does not hide PostgreSQL from you: it is a faster and safer way of writing the same commands. Knowing PostgreSQL helps, and for the details of each command the manual links to the official PostgreSQL documentation.

What to read next

Your first model puts these concepts into practice in about fifteen minutes.