In this tutorial we will show you step-by-step how to prepare a working MySQL 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 MySQL through phpMyAdmin or Linux terminal.

Prerequisites

Please make sure you have a MySQL server as well as MySQL CLI installed on your machine.

Create new database model

We 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 MySQL 5.5). 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 'Generate SQL Script' 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: Setup the database in phpMyAdmin

Create the database and a user on MySQL server

Open phpMyAdmin in your browser and login using root credentials. The main page appears.

Now go to the 'Databases' tab and enter vertabelo_mysql_example in text field under 'Create database'.

Click the 'Create' button. A success message should appear as well as the vertabelo_mysql_example in the databases list.

Now navigate to the privilege management page by clicking on the 'Check privileges' link next to the database name.

Click the 'Add user' link at the bottom.

Enter the login and password for a new user (please note you can also generate a password using the button at the end of the group).

Choose 'Local' from dropdown labeled 'Host'. This means that the user will be able to log in only from the local machine. We don't want anyone to connect remotely, right?

Please take a look at the 'Database for user' group. The last option should be checked since we want the user to have full control over the database. Alternatively, you could choose only some of the privileges from the lists in the last group.

Now click 'Go' to create the new user. A confirmation message appears.

Notice john user in the list. You can log out now.

Import structure and insert data

Please log in as the new user. Note that only vertabelo_mysql_example database is visible. Also there are no tables in that database – nothing appears under the database name when you click on the plus sign.

Now we will import tables structure. Click on vertabelo_mysql_example and select the 'Import' tab to continue.

Choose the file downloaded from Vertabelo and click 'Go' to start the import. A confirmation message appears.

Now there are table names listed under vertabelo_mysql_example. It's time to add some content.

Click on the client table in the list. A browse page appears listing all rows. So far we haven't inserted any so there's only an info message. Click on the 'Insert' tab.

Set the id field equal to 1. Now enter a name and email. Click on the 'Go' button to send the request. A confirmation message appears.

Now go back to the 'Browse' tab. The inserted record will appear in the list.

Option 2: Set up the database in Linux console

Create the database and a user on MySQL server

Now we can switch to terminal. Log in as a root user and create a database:

vertabelouser@machine:~$ mysql -u root -p

Please enter the password to access MySQL console.

mysql> CREATE DATABASE vertabelo_mysql_example;
Query OK, 1 row affected (0,00 sec)

The database was successfully created and is now visible among others in the list:

mysql> SHOW DATABASES;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| mysql                   |
| performance_schema      |
| vertabelo_mysql_example |
+-------------------------+
4 rows in set (0,00 sec)

Now we will create a new user and grant him all privileges on the newly created database so that he can create new tables.

mysql> CREATE USER 'john'@'localhost' IDENTIFIED BY 'johnspassword';
Query OK, 0 rows affected (0,00 sec)

mysql> GRANT ALL PRIVILEGES ON vertabelo_mysql_example.* TO 'john'@'localhost';
Query OK, 0 rows affected (0,00 sec)

You will have to execute the following command to make sure that settings are up-to-date:

mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0,00 sec)

Now we can fill our database with tables and records. Quit the console now:

mysql> quit Bye

Import structure and insert data

To create the database structure browse to the folder containing downloaded SQL code. There is additional info in the heading:

vertabelouser@machine:~$ head Shop_create.sql -n 4
-- Created by Vertabelo (http://vertabelo.com)
-- Script type: create
-- Scope: [tables, references, sequences, views, procedures]
-- Generated at Wed Mar 18 13:26:33 UTC 2015

Now you can create a database structure as the user we created in the previous step.

vertabelouser@machine:~$ mysql -u john -p vertabelo_mysql_example < Shop_create.sql

Please provide the password given during user creation (johnspassword in this tutorial). No other output should appear – it means that all the SQL commands were executed correctly. Now you can log in as john and check if any tables are present:

vertabelouser@machine:~$ mysql -u example_admin -p vertabelo_mysql_example

mysql> SHOW TABLES;
+-----------------------------------+
| Tables_in_vertabelo_mysql_example |
+-----------------------------------+
| client                            |
| product                           |
| product_category                  |
| purchase                          |
| purchase_item                     |
+-----------------------------------+
5 rows in set (0,00 sec)

It's time to fill the tables with data. For this purpose we will create a new customer using an INSERT statement.

mysql> INSERT INTO client (id, full_name, email) VALUES (1, 'Vertabelo User', 'vertabelo@example.com');
Query OK, 1 row affected, 1 warning (0,13 sec)

mysql> SELECT * FROM client;
+----+----------------+-----------------------+
| id | full_name      | email                 |
+----+----------------+-----------------------+
| 1  | Vertabelo User | vertabelo@example.com |
+----+----------------+-----------------------+
1 row in set (0,00 sec)

You can quit the console now.

That's all!

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