PostgreSQL database design

Quick start guide for PostgreSQL database design in Moon Modeler.

1. New project

To create a new project, click New on the main toolbar and select the PostgreSQL project type.

New project for PostgreSQL

2. Add tables

Basic database objects can be added to your project/diagram by clicking the appropriate icon on the main toolbar.

New table, composite or JSON

Then click the diagram area. A new graphical object will appear on the diagram.

Edit properties in Right Side panel:

Edit table

Alternatively, you can double click the graphics or right-click the item and choose Edit.

Edit object from diagram

Table properties

Basic type settings can be specified in the section Details.


In section Columns, you can add new table columns. Specify new column name, datatype, enum, domain or JSON and click Add.

  • PK indicates primary key.
  • NN represents Not Null column.

Detailed column specifics can be defined in a collapsible/expandable area.

  • Sample data will appear in the diagram if the Sample data mode is activated.
Table columns - PostgreSQL

3. JSON and composite types

One of the key, unique features of Moon Modeler is the visualization of JSON structures. PostgreSQL supports JSON data type and you can either set the basic JSON datatype for a column or prepare a new JSON object in the diagram and then pick the newly defined JSON structure as a datatype for your table column.

Compare the following:

  • table customer_basic contains column contact and the datatype is defined as JSON.
  • table customer_nested also contains column contact, but instead of JSON datatype, the contact JSON object is used.

When you look at the generated script, the customer_nested table contains a basic definition of the datatype, however, in the diagram, the structure is fully visualized.

Nested PostgeSQL JSON

How to add JSON to diagram

To create a JSON object:

  • Click the JSON icon on the main toolbar and then click the diagram.
  • Define fields for the JSON object, in the example above the fields are phone, email, address.
  • And then assign the object to the table column datatype in the desired table, in our case customer_nested.
JSON as datatype

4. Views, enums, procedures, functions, domains, rules, policies and other objects

All other supported objects can be added to the diagram either by clicking the Other icon on the main toolbar, selecting the object type and then clicking the diagram area or, by right-clicking the diagram area and selecting the desired object type.

New other object

Enum used in the diagram:

Enum in diagram

5. Relationships

Relationships can be created between parent and child tables (for other visual references you can use Lines and connect two graphical objects).

To add a new relationship to the diagram, click the Relationship icon, then click the parent table and then the child table. If the primary key is defined in the parent table, a new column will be added to the child table and marked as a foreign key.

Example: relationship drawn from table customer to table cart. Foreign key column customer_id was added to the child table automatically.

PostgreSQL relationship

Read more about one-to-many relationships and many-to-many relationships.

6. Code generation

To generate an SQL script, click the SQL Script icon on the main toolbar.

Generated script

How to execute the script? Use another tool, for example, PG Admin:

PG admin dark theme

7. Other features and visual elements

Notes, lines and other information that can be displayed in a diagram will help you keep the structure well documented. You can also display descriptions instead of columns in the visualisations.


8. Reverse engineering and visualization of existing database structures

You can establish a connection to your PostgreSQL database and load the existing structure, incl. all supported PostgreSQL objects.

To reverse engineer a database, create a new connection:

Database connections

and then click Connect and reverse engineer the existing database.

Load existing PostgreSQL database



Quick links

Free Trial Buy Now!

Blog categories