In this tutorial we will show you how to prepare a working PostgreSQL DB based on a Vertabelo project. This article is divided in two sections – the first one shows how to generate SQL code and the second covers interaction with PostgreSQL through pgAdmin or Linux terminal.

Prerequisites

Please make sure you have PostgreSQL installed on your machine. You can also use pgAdmin which is shipped with most PostgreSQL distributions.

Here are the links if you miss anything:

• PostgreSQL
• pgAdmin

Create a new database model

We'll start from the dashboard. The first step is to click the 'Create new model' button and then pick 'Physical data model'. The model creation form will appear.

Enter the model name and set the database engine (in this article, we use PostgreSQL 9.X). You can create an empty model, import SQL code, Vertabelo XML or use an example project. For this tutorial, let's use the 'Example'.

Now proceed to the editor by clicking the 'Start modeling' button.

Plan the structure

In this step you can edit the project using options from the left panel and the toolbox visible in the main area. (A detailed description is beyond the scope of this article). You can continue with the current project as well.

Generate SQL code

Since your project is finished, you can now export the database structure. Please take a look at the right-hand 'Model properties' panel: in the 'SQL generation settings' tab you can turn on comments generation. 'Additional SQL scripts' allows you to add extra SQL code to be executed at the beginning and the end of the file. If you don't see it you may need to scroll down a bit.

Now press Ctrl+G (or Cmd+G) or click on the 'SQL' icon in the top bar to show the options dialog:

Click on the 'Generate' button to prepare SQL output. Now you can click on the 'Download' button to grab a copy or 'Save' to save this file within your account.

Option 1: Use command line interface

Create the database and a user on PostgreSQL server

First, switch the account to your postgresql superuser account. Usually a postgres account is created during postgresql package installation. You can check if one exists by using the following:

vertabelouser@machine:~$ awk -F':' '{ print $1}' /etc/passwd | grep postgres

Now switch to the superuser account:

vertabelouser@machine:~$ sudo -i -u postgres
[sudo] password for vertabelouser: 
postgres@machine:~$

The second step is to create a new role. A role is a concept working like a user or a group in the database

postgres@machine:~$ createuser --interactive
Enter name of role to add: john
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

Since there's a new john role, we can create a database for him:

postgres@machine:~$ createdb -O john vertabelo_postgresql_example

Let's enter the PSQL console and set a password for john. Notice that \du command lists existing roles:

postgres@machine:~$ psql
psql (9.4.2, server 9.3.7)
Type "help" for help.

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 john      | Create DB                                      | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

postgres=# \password john
Enter new password: 
Enter it again: 
postgres=# \q

Import structure

It's time to fill the database with data exported from Vertabelo. The following command will execute the downloaded file (-a option is used to print input out):

postgres@machine:~$ psql -U john -h 127.0.0.1 -d vertabelo_postgresql_example -W -a -f /home/vertabelouser/Downloads/Shop_create.sql

There should be no other output than SQL from the downloaded file.

Enter the console as john to make sure tables are created:

postgres@machine:~$ psql -U john -h 127.0.0.1 -d vertabelo_postgresql_example -W
Password for user john: 
psql (9.4.2, server 9.3.7)
SSL connection (protocol: TLSv1.2, cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256,
compression: off)
Type "help" for help.

vertabelo_postgresql_example=> \l
                                           List of databases
             Name             |  Owner   |   Access privileges   
------------------------------+----------+-----------------------
 postgres                     | postgres | 
 template0                    | postgres | =c/postgres          +
                              |          | postgres=CTc/postgres
 template1                    | postgres | =c/postgres          +
                              |          | postgres=CTc/postgres
 vertabelo_postgresql_example | john     | 
(4 rows)

As you can see, our example DB is in the list. Now list tables:

vertabelo_postgresql_example=> \dt
             List of relations
 Schema |       Name       | Type  | Owner 
--------+------------------+-------+-------
 public | client           | table | john
 public | product          | table | john
 public | product_category | table | john
 public | purchase         | table | john
 public | purchase_item    | table | john
(5 rows)

Insert data

Finally, use a simple INSERT query to insert new row:

vertabelo_postgresqql_example=> INSERT INTO client (id, full_name, email) 
VALUES (1, 'Vertabelo User', 'vertabelo@example.com');
INSERT 0 1

1 at the end of the printed line indicates that one row was inserted. Let's check that:

vertabelo_postgresql_example=> SELECT * FROM client;
 id |   full_name    |         email         
----+----------------+-----------------------
  1 | Vertabelo User | vertabelo@example.com
(1 row)

You can quit the console now.

Option 2: Use pgAdmin III

pgAdmin is an open-source GUI tool for PostgreSQL management.

Create the database and a user on PostgreSQL server

Connect to PostgreSQL server by clicking right mouse button on PostgreSQL server name and choosing 'Connect'. The application will ask for the superuser password:

The next thing is to create a new role. A role is a concept working like a user or a group in a database. Choose 'New login role...' from the context menu of 'Login roles' (right mouse button click). Fill in Properties / Role name (e.g. john), Definition / Password and select Role privileges / Can create databases.

Click OK to confirm. Now, choose 'New database' from the context menu of 'Databases' in the list – we will create a database for john.

Insert database name (vertabelo_postgresql_example in this tutorial) and set the owner to john. Click 'OK' and disconnect from the current server.

Import structure

Add new connection to PostgreSQL server through the plug icon in the main window.

Use connection details as you did in the previous entry (right-click on PostgreSQL 9.4 and choose 'Properties' to see them), set username to john and enter the password. Click OK to confirm.

Now connect to the database using the new entry.

Frames in the right will list various properties. It's time to create tables. To do so, click the 'SQL Query Tool' icon. A new window appears.

Choose File / Open… to import a file downloaded from Vertabelo. Now click the 'Run' icon (green arrow) to execute SQL.

A confirmation message appears. Close the window.

Expand Databases / Schemas / public / Tables to see the list of existing tables. Everything got imported correctly.

Insert data

To insert a new client, right-click the table name and choose INSERT query. The query tool appears. Insert values in the query.

Run the query and close the window. Now choose SELECT query to list all of the rows from the table.

That's all!

Now you have a working PostgreSQL database reflecting a Vertabelo project. Pretty easy, right?