In this tutorial we will cover the process of creating a working SQLite DB based on a Vertabelo project.

Prerequisites

Please make sure you have either the SQLite command line interface or SQLite Database Browser installed on your platform.

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 SQLite 3.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

If 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

Notice: SQLite for Linux and Mac works as described below. SQLite for Windows is shipped as a single application – you can follow this tutorial using sqlite3.exe instead of sqlite3.

Import database structure

SQLite automatically creates a database in the given path if none exists. Just redirect the downloaded SQL file to sqlite3.

vertabelouser@machine:~$ sqlite3 example.db <
~/Downloads/Shop_create.sql

Insert new data

To manage the database you have to enter the SQLite interactive console. In SQLite there is no privilege management other than file permissions, so we don't have to create a new user.

vertabelouser@machine:~$ sqlite3 example.db
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Now we can list the tables to make sure everything got imported.

sqlite> .tables
client            order_item                product_category
order            product

Let's insert a new customer using the INSERT query:

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

No feedback message means the query was executed correctly. The final step is to list existing customers:

sqlite> SELECT * FROM client;
1|Vertabelo User|vertabelo@example.com

Option 2: Use SQLite Database Browser

SQLite Database Browser is a simple tool for three main operating systems. You can download it from the official page.

Import database structure

Open SQLite Database Browser. A new window will appear.

Click the 'New Database' button. In the new window you can save your newly created database file (usually with .db extension). After saving, the window will display categories od elements. The lists in this example are now empty because we haven't inserted anything yet.

To import an existing structure choose File > Import > Database from SQL file... from the main menu. Then choose the downloaded SQL file. The application will ask whether to create a new database for the imported data. Click 'No' to execute statements in the current database. After confirmation, tables will be listed in the main window:

Insert new data

To manage rows go to the 'Browse Data' tab. There's a dropdown with list of tables and a 'New Record' button. Click on that now. A new row will be appended to the list. Fields are null now, but you can enter values if you double-click on any field.

Set full name and email of a new customer.

When finished, click the 'Write Changes' button to save the database file.

That's all!

Now you have a working SQLite database based on a Vertabelo project. Pretty easy, right?