Making a storage

How to make a storage properly

Making storage is a very first line of code from sqlite_orm you have to write. Making of a storage is performed using make_storage function call. In this call you have to tell sqlite_orm what tables and columns you need to access in runtime and filename where you want to store database or where it is already located if it is.

Hint

If you want to store sqlite database in memory just use ":memory:" or empty string instead of filename.

During making a storage you actually need to specify a database schema. You can ask sqlite3 shell to print out your database schema with .schema of .fullschema command. For example let's see what output we get for readme with users and user types:

.schema

Output:

CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , first_name TEXT NOT NULL , last_name TEXT NOT NULL , birth_date INTEGER NOT NULL , image_url TEXT , type_id INTEGER NOT NULL );
CREATE TABLE user_types ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , name TEXT NOT NULL , comment TEXT DEFAULT 'user' NOT NULL );

.schema sqlite command just shows exact command you wrote during creating existing tables. So making storage is the same but in C++.

For example to make storage for schema written above we have to write code like this:

struct User{
    int id;
    std::string firstName;
    std::string lastName;
    int birthDate;
    std::shared_ptr<std::string> imageUrl;
    int typeId;
};

struct UserType {
    int id;
    std::string name;
    std::string comment;
};

using namespace sqlite_orm;
auto storage = make_storage("db.sqlite",
                            make_table("users",
                                       make_column("id", &User::id, autoincrement(), primary_key()),
                                       make_column("first_name", &User::firstName),
                                       make_column("last_name", &User::lastName),
                                       make_column("birth_date", &User::birthDate),
                                       make_column("image_url", &User::imageUrl),
                                       make_column("type_id", &User::typeId)),
                            make_table("user_types",
                                       make_column("id", &UserType::id, autoincrement(), primary_key()),
                                       make_column("name", &UserType::name),
                                       make_column("comment", &UserType::comment, default_value("user"))));

First argument "db.sqlite" means that storage expects to find and store database file at this location. If there is no file at this location with a given schema you have to either create it with SQLite client or call sync_schema storage member function before requesting data from it. If you pass ":memory:" or "" as first parameter there won't be any file - just in memory database which will be cleared once a process of your app is over.

You can create two different storages with different schemas and they will work independently. Different in memory storages also will work independently.

After filename you have to specify tables. It can be any number of tables. Every table is created with make_table function. make_table takes table name as first argument. Table name in make_table must be equal to actual table name in database file. If table with given name doesn't exist std::runtime_error will be thrown during access to the table. You have to create table explicitly or call sync_schema before accessing it. Right after table name you have to specify columns your table has. Columns set must be the same that in database file. Order doesn't matter. Every column must be created with make_column function.

make_column expects at least two arguments: a column's name and it's mapped member pointer. For example:

make_column("name", &UserType::name);

You don't have to specify type explicitly: it is deduced from member's type you specify as the second parameter. So if you need to create column first_name TEXT NOT NULL just pass make_column("first_name", &User::firstName); where std::string firstName; is a member of User class with std::string type. If you need to create column type_id INTEGER NOT NULL pass make_column("type_id", &User::typeId); where int typeId; is member of User class.

If you need to add some constraints to column like PRIMARY KEY, AUTOINCREMENT or DEFAULTyou can use primary_key, autoincrement or default_value functions respectively. Examples:

id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL - make_column("id", &UserType::id, autoincrement(), primary_key()) comment TEXT DEFAULT 'user' NOT NULL - make_column("comment", &UserType::comment, default_value("user"))

NULL and NOT NULL is deduced from member type. std::string, int and any other type except std::shared_ptr and std::unique_ptr are mapped as NOT NULL and std::shared_ptr and std::unique_ptr are mapped as NULL. Of course you can set your own type as nullable if you wish. For example boost::optional. To do so you have to create a specialization for type_is_nullable class. Example code of using type_is_nullable can be found in Examples/nullable_enum_binding.cpp.

Hint

`std::optional` is supported if you enable compilation flag `SQLITE_ORM_OPTIONAL_SUPPORTED` and enable C++17 or higher.

Once storage created it is the best time to add/extract some data to/from it. But don't forget to keep schemas synchronized. If you don't want to use any SQLite client at all just sqlite_orm you can just call sync_schema right after storage is created and it will create missing tables and fix any mismatches.

External links

• Shell commands in SQLite
• In-memory database