Hello. I write on Habré for the first time, do not judge strictly. I want to share my experience in finding a universal SQLite ORM library in C ++ and my new development of my own library for working with SQLite in C ++ sqlite_orm .

When I was looking for ORM, I was repelled by several key points:

• library must have both CRUD and non CRUD
• there must be flexible WHERE conditions, not stupid WHERE id = ?
• there should be a migration functionality (schema synchronization) in case of application updates
• Features like ORDER BY and LIMIT should also be
• serialization of classes should not be written in their classes. This is a very important point for me since I got acquainted with Java and with Android-development in particular. Android developers are trying to adhere to the principle of the sole responsible (single responsibility principle), which is very important if the application is assembled from different heaps of libraries and modules that can change over time. And therefore the most popular on github SQLite ORM'ka in C ++ hiberlite did not suit me in the way of serialization - the class of the model should have a static serialize function with a direct serialization code. I was looking for a module on which the code of my data model would not depend. After all, I can have several serializers (JSON, XML, SQLite), and, in a good way, everyone should be attached to the data model, but not to change it, otherwise it will turn out to be a mess in the model code.
  
• code in the style of the standard library - recently this trend is gaining popularity (in general I was impressed by this library )
• support for both the database on the file system and in memory
• leave the ability to name tables and columns to the developer regardless of the class names and their fields in case you still need to get into the database through the SQLite client so that everything is obvious
• transactions

In addition to hiberlite there are also a bunch of different libraries, but for some reason they have a small functionality. In other words, working with them it turns out that the developer will still have to write direct connection code to the database via libsqlite3 , then why is this ORM necessary?

It seems that I delayed the introduction, I will go directly to the possibilities that the library sqlite_orm gives.

1) CRUD

Example:

 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; }; 

Two classes means two tables.

The interaction takes place through the storage object which is a service object with an interface to the database. storage is created by the make_storage function. At creation the scheme is underlined.

 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, default_value("name_placeholder")))); 

Note that the data model is "not up to date" about the repository. Also the name of the column and the name of the class field are independent of each other This allows you to write Camel-case code, for example, and the database schema through underscores as I do.

In make_storage first parameter is the file name, then the tables go. To create a table, we specify the name of the table (it has nothing to do with the class, because if you make an automatic naming, the implementation will not be very: you must either use typeid(T).name() , which returns not always a clear name, but rather a system name, either cheat with macros, which I generally disapprove), then indicate the columns. To create a single column, you need at least two parameters: the name of the column and a link to the class field. This link will determine the type of column and address for assignment in the future. You can also AUTOINCREMENT and / or PRIMARY KEY with DEFAULT .

Now you can send queries to the database through calls to the storage object functions. For example, let's create a user and make an INSERT .

 User user{-1, "Jonh", "Doe", 664416000, std::make_shared<std::string>("url_to_heaven"), 3 }; auto insertedId = storage.insert(user); cout << "insertedId = " << insertedId << endl; user.id = insertedId; 

Now we have sent INSERT INTO users(first_name, last_name, birth_date, image_url, type_id) VALUES('Jonh', 'Doe', 664416000, 'url_to_heaven', 3) .

The first argument -1 we specified when creating the user object is id. It is ignored during creation, as the id is the PRIMARY KEY column. sqlite_orm ignores the PRIMARY KEY column at INSERT and returns the id of the newly created object. Therefore, after INSERT, we do user.id = insertedId; - after that, the user is full and can be used further in the code.

To get the same user, use the get function:

 try{ auto user = storage.get<User>(insertedId); cout << "user = " << user.firstName << " " << user.lastName << endl; }catch(sqlite_orm::not_found_exception) { cout << "user not found with id " << insertedId << endl; }catch(...){ cout << "unknown exeption" << endl; } 

get returns an object of class User (which we passed as a template parameter). If the user with such id is not thrown, the exception sqlite_orm::not_found_exception . Such an interface with an exception may be inconvenient. The reason for this is that in C ++ just an object cannot be set to zero as it can be done in Java, C # or Objective-C. You can use std::shared_ptr<T> as the type to be std::shared_ptr<T> . For such a case, there is a second version of the get - get_no_throw :

 if(auto user = storage.get_no_throw<User>(insertedId)){ cout << "user = " << user->firstName << " " << user->lastName << endl; }else{ cout << "no user with id " << insertedId << endl; } 

Here, user is std::shared_ptr<User> and may be equal to nullptr , or it may store the user.

Next we may want to make the UPDATE user. To do this, we will change the fields we want to change and call the update function:

 user.firstName = "Nicholas"; user.imageUrl = "https://cdn1.iconfinder.com/data/icons/man-icon-set/100/man_icon-21-512.png" storage.update(user); 

It works like this: UPDATE users SET ... primary key... WHERE id = % , , primary key% is called UPDATE users SET ... primary key... WHERE id = % , , primary key% .

It's simple. Note that there are no proxy objects for interacting with the repository — the repository accepts and returns "clean" model objects. This simplifies the work and lowers the threshold of entry.

Deleting an object by id is implemented like this:

 storage.remove<User>(insertedId); 

Here you need to explicitly specify the type as a template parameter, since there is no way to guess the compiler.

This CRUD ends. But the functionality is not limited to this. CRUD functions in sqlite_orm are functions that work only with objects that have one column with PRIMARY KEY . There are also non-CRUD functions.

For example, let's do SELECT * FROM users .

 auto allUsers = storage.get_all<User>(); cout << "allUsers (" << allUsers.size() << "):" << endl; for(auto &user : allUsers) { cout << storage.dump(user) << endl; } 

The variable allUsers is of type std::vector<User> . Pay attention to the dump function - it takes a class object that is associated with the storage, and returns information about it in the json-style in the form of std::string . For example, "{id: '1', first_name: 'Jonh', last_name: 'Doe', birth_date: '664416000', image_url: '0x10090c3d8', type_id: '3'}".

But this is not enough. The ORM library cannot be considered complete without WHERE conditions. Therefore, they also exist in sqlite_orm they are very powerful.

The above-mentioned get_all function can take as its argument the result of a where function with conditions. For example, let's select users whose id is less than 10. The query should look like this: SELECT * FROM users WHERE id < 10 . In code, it looks like this:

 auto idLesserThan10 = storage.get_all<User>(where(lesser_than(&User::id, 10))); 

Or select users whose firstName field is not equal to "John". Query - SELECT * FROM users WHERE first_name != 'John'

 auto notJohn = storage.get_all<User>(where(is_not_equal(&User::firstName, "John"))); 

Moreover, you can "mix" the operators && , || and ! (for greater clarity, it is better to use the literal versions of these operators and , or and not ).

 auto notJohn2 = storage.get_all<User>(where(not is_equal(&User::firstName, "John"))); 

notJohn2 equivalent to notJohn .

And another example with linked conditions:

 auto id5and7 = storage.get_all<User>(where(lesser_or_equal(&User::id, 7) and greater_or_equal(&User::id, 5) and not is_equal(&User::id, 6))); 

We implemented this query SELECT * FROM users WHERE where id >= 5 and id <= 7 and not id = 6 .

Or SELECT * FROM users WHERE id = 10 or id = 16 :

 auto id10or16 = storage.get_all<User>(where(is_equal(&User::id, 10) or is_equal(&User::id, 16))); 

So you can "glue" any combination of conditions. Moreover, you can specify the priority of conditions using parentheses as in raw queries in SQLite. For example, these two queries differ in the returned results:

 auto cuteConditions = storage.get_all<User>(where((is_equal(&User::firstName, "John") or is_equal(&User::firstName, "Alex")) and is_equal(&User::id, 4))); cuteConditions = storage.get_all<User>(where(is_equal(&User::firstName, "John") or (is_equal(&User::firstName, "Alex") and is_equal(&User::id, 4)))); 

In the first condition WHERE (first_name = 'John' or first_name = 'Alex') and id = 4 , in the second - WHERE first_name = 'John' or (first_name = 'Alex' and id = 4) .

This magic works by virtue of the fact that in C ++ brackets have the same function of explicitly defining the priority of operations. Plus, the sqlite_orm itself is only a convenient sqlite_orm for working with SQLite in C ++, it (the library) does not itself execute queries, but only transforms them into text and sends them to the sqlite3 engine.

There is also an IN operator:

 auto evenLesserTen10 = storage.get_all<User>(where(in(&User::id, {2, 4, 6, 8, 10}))); 

It turned out SELECT * FROM users WHERE id IN (2, 4, 6, 8, 10) . Or for the lines:

 auto doesAndWhites = storage.get_all<User>(where(in(&User::lastName, {"Doe", "White"}))); 

Here we sent a SELECT * FROM users WHERE last_name IN ("Doe", "White") query to the database.

The in function takes two arguments: a pointer to the class field and a vector / initialization list. The type of the contents of the vector / initialization list is the same as the pointer field to which we passed as the first parameter.

The condition functions is_equal , is_not_equal , greater_than , greater_or_equal , lesser_than , lesser_or_equal take two arguments each. Arguments can be both pointers to class fields, and constants / variables. Pointers to the fields are parsed into the query in the column names, and the literals are as they are, only the strings are still quoted around the edges.

You may have a question: what if I pass in a condition a pointer to a class field that is not listed in any column? In this case, an exception std::runtime_error with an explanatory text will be thrown. The same will happen if you specify a type that is not bound to the repository.

By the way, WHERE conditions can be used in DELETE queries. For this there is a function remove_all . For example, let's delete all users whose id is less than 100:

 storage.remove_all<User>(where(lesser_than(&User::id, 100))); 

All examples above operate with full-fledged objects. And what if we want to call a single column SELECT ? This, too, is:

 auto allIds = storage.select(&User::id); 

We called this SELECT id FROM users . allIds is of type std::vector<decltype(User::id)> or std::vector<int> .

You can add conditions:

 auto doeIds = storage.select(&User::id, where(is_equal(&User::lastName, "Doe"))); 

You guessed it SELECT id FROM users WHERE last_name = 'Doe' .

There may be many options. For example, you can request all surnames where id is less than 300:

 auto allLastNames = storage.select(&User::lastName, where(lesser_than(&User::id, 300))); 

ORDER BY

ORM or ORM without ordering. ORDER BY used in many projects, and sqlite_orm has an interface for it.

The simplest example is let's choose users sorted by id:

 auto orderedUsers = storage.get_all<User>(order_by(&User::id)); 

This turns into a SELECT * FROM users ORDER BY id . Or let's mix where and order_by : SELECT * FROM users WHERE id < 250 ORDER BY first_name

 auto orderedUsers2 = storage.get_all<User>(where(lesser_than(&User::id, 250)), order_by(&User::firstName)); 

You can also explicitly specify ASC and DESC . For example: SELECT * FROM users WHERE id > 100 ORDER BY first_name ASC :

 auto orderedUsers3 = storage.get_all<User>(where(greater_than(&User::id, 100)), order_by(asc(&User::firstName))); 

Or here:

 auto orderedUsers4 = storage.get_all<User>(order_by(desc(&User::id))); 

It turned out SELECT * FROM users ORDER BY id DESC .

And of course, just select also works with order_by :

 auto orderedFirstNames = storage.select(&User::firstName, order_by(desc(&User::id))); 

It turned out SELECT first_name FROM users ORDER BY ID DESC .

Migrations

There are no migrations per se in the library, but there is a function sync_schema . Calling this function asks the DB for the current scheme, compares it with the one specified when creating the repository, and if something does not match, rules it. At the same time, this call does not guarantee the safety of already existing data. It only guarantees that the scheme will become identical (or std::runtime_error will be thrown. For more information about the rules for synchronizing the scheme, see the repository page on github .

Transactions

The library has two options for the implementation of transactions: explicit and implicit. Explicit implies a direct call to the begin_transaction and commit or rollback functions. Example:

 auto secondUser = storage.get<User>(2); storage.begin_transaction(); secondUser.typeId = 3; storage.update(secondUser); storage.rollback(); //  storage.commit(); secondUser = storage.get<decltype(secondUser)>(secondUser.id); assert(secondUser.typeId != 3); 

The second method is a bit more cunning. First the code:

 storage.transaction([&] () mutable { auto secondUser = storage.get<User>(2); secondUser.typeId = 1; storage.update(secondUser); auto gottaRollback = bool(rand() % 2); if(gottaRollback){ //     return false; //      ROLLBACK } return true; //      COMMIT }); 

The transaction function calls BEGIN TRANSACTION immediately and takes a lambda expression as an argument, which is returned by bool . If you return true , then COMMIT will be executed, if false - ROLLBACK . This method ensures that you do not forget to call the end of transaction function (like std::lock_guard in a mutex in the standard library).

There are also aggregate functions AVG , MAX , MIN , COUNT , GROUP_CONCAT :

 auto averageId = storage.avg(&User::id); // 'SELECT AVG(id) FROM users' auto averageBirthDate = storage.avg(&User::birthDate); // 'SELECT AVG(birth_date) FROM users' auto usersCount = storage.count<User>(); // 'SELECT COUNT(*) FROM users' auto countId = storage.count(&User::id); // 'SELECT COUNT(id) FROM users' auto countImageUrl = storage.count(&User::imageUrl); // 'SELECT COUNT(image_url) FROM users' auto concatedUserId = storage.group_concat(&User::id); // 'SELECT GROUP_CONCAT(id) FROM users' auto concatedUserIdWithDashes = storage.group_concat(&User::id, "---"); // 'SELECT GROUP_CONCAT(id, "---") FROM users' auto maxId = storage.max(&User::id); // 'SELECT MAX(id) FROM users' auto maxFirstName = storage.max(&User::firstName); // 'SELECT MAX(first_name) FROM users' auto minId = storage.min(&User::id); // 'SELECT MIN(id) FROM users' auto minLastName = storage.min(&User::lastName); // 'SELECT MIN(last_name) FROM users' 

More details can be found here . Contributing is welcome as much as criticism.

EDIT 1

In the last commit, the "raw" select of several columns is added to the vector of tuples (tuples). For example:

 // `SELECT first_name, last_name FROM users WHERE id > 250 ORDER BY id` auto partialSelect = storage.select(columns(&User::firstName, &User::lastName), where(greater_than(&User::id, 250)), order_by(&User::id)); cout << "partialSelect count = " << partialSelect.size() << endl; for(auto &t : partialSelect) { auto &firstName = std::get<0>(t); auto &lastName = std::get<1>(t); cout << firstName << " " << lastName << endl; } 

EDIT 2

The last commit adds support for LIMIT and OFFSET . There are three options for using LIMIT and OFFSET :

1. LIMIT% limit%
2. LIMIT% limit% OFFSET% offset%
3. LIMIT% offset%,% limit%

Examples:

 // `SELECT first_name, last_name FROM users WHERE id > 250 ORDER BY id LIMIT 5` auto limited5 = storage.get_all<User>(where(greater_than(&User::id, 250)), order_by(&User::id), limit(5)); cout << "limited5 count = " << limited5.size() << endl; for(auto &user : limited5) { cout << storage.dump(user) << endl; } // `SELECT first_name, last_name FROM users WHERE id > 250 ORDER BY id LIMIT 5, 10` auto limited5comma10 = storage.get_all<User>(where(greater_than(&User::id, 250)), order_by(&User::id), limit(5, 10)); cout << "limited5comma10 count = " << limited5comma10.size() << endl; for(auto &user : limited5comma10) { cout << storage.dump(user) << endl; } // `SELECT first_name, last_name FROM users WHERE id > 250 ORDER BY id LIMIT 5 OFFSET 10` auto limit5offset10 = storage.get_all<User>(where(greater_than(&User::id, 250)), order_by(&User::id), limit(5, offset(10))); cout << "limit5offset10 count = " << limit5offset10.size() << endl; for(auto &user : limit5offset10) { cout << storage.dump(user) << endl; } 

Please do not forget that LIMIT 5, 10 and LIMIT 5 OFFSET 10 have different meanings. If exactly, then LIMIT 5, 10 is LIMIT 10 OFFSET 5 .