❤️ SQLite ORM light header only library for modern C++


Sublime's custom image

License Donate using PayPal Twitter URL


SQLite ORM light header only library for modern C++


Branch Travis Appveyor
master Build Status Build status
dev Build Status Build status


  • No raw string queries
  • Intuitive syntax
  • Comfortable interface - one code line per single query
  • Built with modern C++14 features (no macros and external scripts)
  • CRUD support
  • Pure select query support
  • Prepared statements support
  • STL compatible
  • Custom types binding support
  • BLOB support - maps to std::vector<char> or one can bind your custom type
  • FOREIGN KEY support
  • Composite key support
  • JOIN support
  • Transactions support
  • Migrations functionality
  • Powerful conditions
  • ORDER BY and LIMIT, OFFSET support
  • GROUP BY / DISTINCT support
  • INDEX support
  • Follows single responsibility principle - no need write code inside your data model classes
  • Easy integration - single header only lib.
  • The only dependency - libsqlite3
  • C++ standard code style
  • In memory database support - provide :memory: or empty filename
  • COLLATE support
  • Limits setting/getting support

sqlite_orm library allows to create easy data model mappings to your database schema. It is built to manage (CRUD) objects with a primary key and without it. It also allows you to specify table names and column names explicitly no matter how your classes actually named. Take a look at example:

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

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

So we have database with predefined schema like

CREATE TABLE users (id integer primary key autoincrement, 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, name text not null DEFAULT 'name_placeholder')

Now we tell sqlite_orm library about our schema and provide database filename. We create storage service object that has CRUD interface. Also we create every table and every column. All code is intuitive and minimalistic.

using namespace sqlite_orm;
auto storage = make_storage("db.sqlite",
                                       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_column("id", &UserType::id, autoincrement(), primary_key()),
                                       make_column("name", &UserType::name, default_value("name_placeholder"))));

Too easy isn't it? You do not have to specify mapped type explicitly - it is deduced from your member pointers you pass during making a column (for example: &User::id). To create a column you have to pass two arguments at least: its name in the table and your mapped class member pointer. You can also add extra arguments to tell your storage about column's constraints like primary_key, autoincrement, default_value or unique(order isn't important; not_null is deduced from type automatically).

More details about making storage can be found in tutorial.

If your datamodel classes have private or protected members to map to sqlite then you can make a storage with setter and getter functions. More info in the example.


Let's create and insert new User into our database. First we need to create a User object with any id and call insert function. It will return id of just created user or throw exception if something goes wrong.

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

User secondUser{-1, "Alice", "Inwonder", 831168000, {} , 2};
insertedId = storage.insert(secondUser);
secondUser.id = insertedId;

Note: if we need to insert a new user with specified id call storage.replace(user); instead of insert.

Next let's get our user by id.

    auto user = storage.get<User>(insertedId);
    cout << "user = " << user.firstName << " " << user.lastName << endl;
}catch(std::system_error e) {
    cout << e.what() << endl;
    cout << "unknown exeption" << endl;

Probably you may not like throwing exceptions. Me too. Exception std::system_error is thrown because return type in get function is not nullable. You can use alternative version get_pointer which returns std::unique_ptr and doesn't throw not_found_exception if nothing found - just returns nullptr.

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

std::unique_ptr is used as optional in sqlite_orm. Of course there is class optional in C++14 located at std::experimental::optional. But we don't want to use it until it is experimental.

We can also update our user. It updates row by id provided in user object and sets all other non primary_key fields to values stored in the passed user object. So you can just assign members to user object you want and call update

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

Also there is a non-CRUD update version update_all:

storage.update_all(set(c(&User::lastName) = "Hardey",
                       c(&User::typeId) = 2),
                   where(c(&User::firstName) == "Tom"));

And delete. To delete you have to pass id only, not whole object. Also we need to explicitly tell which class of object we want to delete. Function name is remove not delete cause delete is a reserved word in C++.


Also we can extract all objects into std::vector.

auto allUsers = storage.get_all<User>();
cout << "allUsers (" << allUsers.size() << "):" << endl;
for(auto &user : allUsers) {
    cout << storage.dump(user) << endl; //  dump returns std::string with json-like style object info. For example: { id : '1', first_name : 'Jonh', last_name : 'Doe', birth_date : '664416000', image_url : 'https://cdn1.iconfinder.com/data/icons/man-icon-set/100/man_icon-21-512.png', type_id : '3' }

And one can specify return container type explicitly: let's get all users in std::list, not std::vector:

auto allUsersList = storage.get_all<User, std::list<User>>();

Container must be STL compatible (must have push_back(T&&) function in this case).

get_all can be too heavy for memory so you can iterate row by row (i.e. object by object):

for(auto &user : storage.iterate<User>()) {
    cout << storage.dump(user) << endl;

iterate member function returns adapter object that has begin and end member functions returning iterators that fetch object on dereference operator call.

CRUD functions get, get_pointer, remove, update (not insert) work only if your type has a primary key column. If you try to get an object that is mapped to your storage but has no primary key column a std::system_error will be thrown cause sqlite_orm cannot detect an id. If you want to know how to perform a storage without primary key take a look at date_time.cpp example in examples folder.

Prepared statements

Prepared statements are strongly typed.

//  SELECT doctor_id
//  FROM visits
//  WHERE LENGTH(patient_name) > 8
auto selectStatement = storage.prepare(select(&Visit::doctor_id, where(length(&Visit::patient_name) > 8)));
cout << "selectStatement = " << selectStatement.sql() << endl;  //  prints "SELECT doctor_id FROM ..."
auto rows = storage.execute(selectStatement); //  rows is std::vector<decltype(Visit::doctor_id)>

//  SELECT doctor_id
//  FROM visits
//  WHERE LENGTH(patient_name) > 11
get<0>(selectStatement) = 11;
auto rows2 = storage.execute(selectStatement);

get<N>(statement) function call allows you to access fields to bind them to your statement.

Aggregate Functions

//  SELECT AVG(id) FROM users
auto averageId = storage.avg(&User::id);    
cout << "averageId = " << averageId << endl;        //  averageId = 4.5
//  SELECT AVG(birth_date) FROM users
auto averageBirthDate = storage.avg(&User::birthDate);  
cout << "averageBirthDate = " << averageBirthDate << endl;      //  averageBirthDate = 6.64416e+08
//  SELECT COUNT(*) FROM users
auto usersCount = storage.count<User>();    
cout << "users count = " << usersCount << endl;     //  users count = 8

//  SELECT COUNT(id) FROM users
auto countId = storage.count(&User::id);    
cout << "countId = " << countId << endl;        //  countId = 8

//  SELECT COUNT(image_url) FROM users
auto countImageUrl = storage.count(&User::imageUrl);   
cout << "countImageUrl = " << countImageUrl << endl;      //  countImageUrl = 5

auto concatedUserId = storage.group_concat(&User::id);      
cout << "concatedUserId = " << concatedUserId << endl;      //  concatedUserId = 1,2,3,4,5,6,7,8

//  SELECT GROUP_CONCAT(id, "---") FROM users
auto concatedUserIdWithDashes = storage.group_concat(&User::id, "---");     
cout << "concatedUserIdWithDashes = " << concatedUserIdWithDashes << endl;      //  concatedUserIdWithDashes = 1---2---3---4---5---6---7---8

//  SELECT MAX(id) FROM users
if(auto maxId = storage.max(&User::id)){    
    cout << "maxId = " << *maxId <<endl;    //  maxId = 12  (maxId is std::unique_ptr<int>)
    cout << "maxId is null" << endl;
//  SELECT MAX(first_name) FROM users
if(auto maxFirstName = storage.max(&User::firstName)){ 
    cout << "maxFirstName = " << *maxFirstName << endl; //  maxFirstName = Jonh (maxFirstName is std::unique_ptr<std::string>)
    cout << "maxFirstName is null" << endl;

//  SELECT MIN(id) FROM users
if(auto minId = storage.min(&User::id)){    
    cout << "minId = " << *minId << endl;   //  minId = 1 (minId is std::unique_ptr<int>)
    cout << "minId is null" << endl;

//  SELECT MIN(last_name) FROM users
if(auto minLastName = storage.min(&User::lastName)){
    cout << "minLastName = " << *minLastName << endl;   //  minLastName = Doe
    cout << "minLastName is null" << endl;

//  SELECT SUM(id) FROM users
if(auto sumId = storage.sum(&User::id)){    //  sumId is std::unique_ptr<int>
    cout << "sumId = " << *sumId << endl;
    cout << "sumId is null" << endl;

//  SELECT TOTAL(id) FROM users
auto totalId = storage.total(&User::id);
cout << "totalId = " << totalId << endl;    //  totalId is double (always)

Where conditions

You also can select objects with custom where conditions with =, !=, >, >=, <, <=, IN, BETWEEN and LIKE.

For example: let's select users with id lesser than 10:

//  SELECT * FROM users WHERE id < 10
auto idLesserThan10 = storage.get_all<User>(where(c(&User::id) < 10));
cout << "idLesserThan10 count = " << idLesserThan10.size() << endl;
for(auto &user : idLesserThan10) {
    cout << storage.dump(user) << endl;

Or select all users who's first name is not equal "John":

//  SELECT * FROM users WHERE first_name != 'John'
auto notJohn = storage.get_all<User>(where(c(&User::firstName) != "John"));
cout << "notJohn count = " << notJohn.size() << endl;
for(auto &user : notJohn) {
    cout << storage.dump(user) << endl;

By the way one can implement not equal in a different way using C++ negation operator:

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

You can use ! and not in this case cause they are equal. Also you can chain several conditions with and and or operators. Let's try to get users with query with conditions like where id >= 5 and id <= 7 and not id = 6:

auto id5and7 = storage.get_all<User>(where(c(&User::id) <= 7 and c(&User::id) >= 5 and not (c(&User::id) == 6)));
cout << "id5and7 count = " << id5and7.size() << endl;
for(auto &user : id5and7) {
    cout << storage.dump(user) << endl;

Or let's just export two users with id 10 or id 16 (of course if these users exist):

auto id10or16 = storage.get_all<User>(where(c(&User::id) == 10 or c(&User::id) == 16));
cout << "id10or16 count = " << id10or16.size() << endl;
for(auto &user : id10or16) {
    cout << storage.dump(user) << endl;

In fact you can chain together any number of different conditions with any operator from and, or and not. All conditions are templated so there is no runtime overhead. And this makes sqlite_orm the most powerful sqlite C++ ORM library!

Moreover you can use parentheses to set the priority of query conditions:

auto cuteConditions = storage.get_all<User>(where((c(&User::firstName) == "John" or c(&User::firstName) == "Alex") and c(&User::id) == 4));  //  where (first_name = 'John' or first_name = 'Alex') and id = 4
cout << "cuteConditions count = " << cuteConditions.size() << endl; //  cuteConditions count = 1
cuteConditions = storage.get_all<User>(where(c(&User::firstName) == "John" or (c(&User::firstName) == "Alex" and c(&User::id) == 4)));   //  where first_name = 'John' or (first_name = 'Alex' and id = 4)
cout << "cuteConditions count = " << cuteConditions.size() << endl; //  cuteConditions count = 2

Also we can implement get by id with get_all and where like this:

//  SELECT * FROM users WHERE ( 2 = id )
auto idEquals2 = storage.get_all<User>(where(2 == c(&User::id)));
cout << "idEquals2 count = " << idEquals2.size() << endl;
    cout << storage.dump(idEquals2.front()) << endl;
    cout << "user with id 2 doesn't exist" << endl;

Lets try the IN operator:

//  SELECT * FROM users WHERE id IN (2, 4, 6, 8, 10)
auto evenLesserTen10 = storage.get_all<User>(where(in(&User::id, {2, 4, 6, 8, 10})));
cout << "evenLesserTen10 count = " << evenLesserTen10.size() << endl;
for(auto &user : evenLesserTen10) {
    cout << storage.dump(user) << endl;

//  SELECT * FROM users WHERE last_name IN ("Doe", "White")
auto doesAndWhites = storage.get_all<User>(where(in(&User::lastName, {"Doe", "White"})));
cout << "doesAndWhites count = " << doesAndWhites.size() << endl;
for(auto &user : doesAndWhites) {
    cout << storage.dump(user) << endl;


//  SELECT * FROM users WHERE id BETWEEN 66 AND 68
auto betweenId = storage.get_all<User>(where(between(&User::id, 66, 68)));
cout << "betweenId = " << betweenId.size() << endl;
for(auto &user : betweenId) {
    cout << storage.dump(user) << endl;

And even LIKE:

//  SELECT * FROM users WHERE last_name LIKE 'D%'
auto whereNameLike = storage.get_all<User>(where(like(&User::lastName, "D%")));
cout << "whereNameLike = " << whereNameLike.size() << endl;
for(auto &user : whereNameLike) {
    cout << storage.dump(user) << endl;

Looks like magic but it works very simple. Cute function c (column) takes a class member pointer and returns a special expression middle object that can be used with operators overloaded in ::sqlite_orm namespace. Operator overloads act just like functions

  • is_equal
  • is_not_equal
  • greater_than
  • greater_or_equal
  • lesser_than
  • lesser_or_equal
  • is_null
  • is_not_null

that simulate binary comparison operator so they take 2 arguments: left hand side and right hand side. Arguments may be either member pointer of mapped class or any other expression (core/aggregate function, literal or subexpression). Binary comparison functions map arguments to text to be passed to sqlite engine to process query. Member pointers are being mapped to column names and literals/variables/constants to '?' and then are bound automatically. Next where function places brackets around condition and adds "WHERE" keyword before condition text. Next resulted string appends to a query string and is being processed further.

If you omit where function in get_all it will return all objects from a table:

auto allUsers = storage.get_all<User>();

Also you can use remove_all function to perform DELETE FROM ... WHERE query with the same type of conditions.

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

Raw select

If you need to extract only a single column (SELECT %column_name% FROM %table_name% WHERE %conditions%) you can use a non-CRUD select function:

//  SELECT id FROM users
auto allIds = storage.select(&User::id);    
cout << "allIds count = " << allIds.size() << endl; //  allIds is std::vector<int>
for(auto &id : allIds) {
    cout << id << " ";
cout << endl;

//  SELECT id FROM users WHERE last_name = 'Doe'
auto doeIds = storage.select(&User::id, where(c(&User::lastName) == "Doe"));
cout << "doeIds count = " << doeIds.size() << endl; //  doeIds is std::vector<int>
for(auto &doeId : doeIds) {
    cout << doeId << " ";
cout << endl;

//  SELECT last_name FROM users WHERE id < 300
auto allLastNames = storage.select(&User::lastName, where(c(&User::id) < 300));    
cout << "allLastNames count = " << allLastNames.size() << endl; //  allLastNames is std::vector<std::string>
for(auto &lastName : allLastNames) {
    cout << lastName << " ";
cout << endl;

//  SELECT id FROM users WHERE image_url IS NULL
auto idsWithoutUrls = storage.select(&User::id, where(is_null(&User::imageUrl)));
for(auto id : idsWithoutUrls) {
    cout << "id without image url " << id << endl;

//  SELECT id FROM users WHERE image_url IS NOT NULL
auto idsWithUrl = storage.select(&User::id, where(is_not_null(&User::imageUrl)));
for(auto id : idsWithUrl) {
    cout << "id with image url " << id << endl;
auto idsWithUrl2 = storage.select(&User::id, where(not is_null(&User::imageUrl)));

Also you're able to select several column in a vector of tuples. Example:

//  `SELECT first_name, last_name FROM users WHERE id > 250 ORDER BY id`
auto partialSelect = storage.select(columns(&User::firstName, &User::lastName),
                                    where(c(&User::id) > 250),
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;

ORDER BY support

ORDER BY query option can be applied to get_all and select functions just like where but with order_by function. It can be mixed with WHERE in a single query. Examples:

//  `SELECT * FROM users ORDER BY id`
auto orderedUsers = storage.get_all<User>(order_by(&User::id));
cout << "orderedUsers count = " << orderedUsers.size() << endl;
for(auto &user : orderedUsers) {
    cout << storage.dump(user) << endl;

//  `SELECT * FROM users WHERE id < 250 ORDER BY first_name`
auto orderedUsers2 = storage.get_all<User>(where(c(&User::id) < 250), order_by(&User::firstName));
cout << "orderedUsers2 count = " << orderedUsers2.size() << endl;
for(auto &user : orderedUsers2) {
    cout << storage.dump(user) << endl;

//  `SELECT * FROM users WHERE id > 100 ORDER BY first_name ASC`
auto orderedUsers3 = storage.get_all<User>(where(c(&User::id) > 100), order_by(&User::firstName).asc());
cout << "orderedUsers3 count = " << orderedUsers3.size() << endl;
for(auto &user : orderedUsers3) {
    cout << storage.dump(user) << endl;

//  `SELECT * FROM users ORDER BY id DESC`
auto orderedUsers4 = storage.get_all<User>(order_by(&User::id).desc());
cout << "orderedUsers4 count = " << orderedUsers4.size() << endl;
for(auto &user : orderedUsers4) {
    cout << storage.dump(user) << endl;

//  `SELECT first_name FROM users ORDER BY ID DESC`
auto orderedFirstNames = storage.select(&User::firstName, order_by(&User::id).desc());
cout << "orderedFirstNames count = " << orderedFirstNames.size() << endl;
for(auto &firstName : orderedFirstNames) {
    cout << "firstName = " << firstName << endl;


There are three available versions of LIMIT/OFFSET options:

  • LIMIT %limit%
  • LIMIT %limit% OFFSET %offset%
  • LIMIT %offset%, %limit%

All these versions available with the same interface:

//  `SELECT * FROM users WHERE id > 250 ORDER BY id LIMIT 5`
auto limited5 = storage.get_all<User>(where(c(&User::id) > 250),
cout << "limited5 count = " << limited5.size() << endl;
for(auto &user : limited5) {
    cout << storage.dump(user) << endl;

//  `SELECT * FROM users WHERE id > 250 ORDER BY id LIMIT 5, 10`
auto limited5comma10 = storage.get_all<User>(where(c(&User::id) > 250),
                                             limit(5, 10));
cout << "limited5comma10 count = " << limited5comma10.size() << endl;
for(auto &user : limited5comma10) {
    cout << storage.dump(user) << endl;

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

Please beware that queries LIMIT 5, 10 and LIMIT 5 OFFSET 10 mean different. LIMIT 5, 10 means LIMIT 10 OFFSET 5.

JOIN support

You can perform simple JOIN, CROSS JOIN, INNER JOIN, LEFT JOIN or LEFT OUTER JOIN in your query. Instead of joined table specify mapped type. Example for doctors and visits:

//  SELECT a.doctor_id, a.doctor_name,
//      c.patient_name, c.vdate
//  FROM doctors a
//  LEFT JOIN visits c
//  ON a.doctor_id=c.doctor_id;
auto rows = storage2.select(columns(&Doctor::id, &Doctor::name, &Visit::patientName, &Visit::vdate),
                            left_join<Visit>(on(c(&Doctor::id) == &Visit::doctorId)));  //  one `c` call is enough cause operator overloads are templated
for(auto &row : rows) {
    cout << std::get<0>(row) << '\t' << std::get<1>(row) << '\t' << std::get<2>(row) << '\t' << std::get<3>(row) << endl;
cout << endl;

Simple JOIN:

//  SELECT a.doctor_id,a.doctor_name,
//      c.patient_name,c.vdate
//  FROM doctors a
//  JOIN visits c
//  ON a.doctor_id=c.doctor_id;
rows = storage2.select(columns(&Doctor::id, &Doctor::name, &Visit::patientName, &Visit::vdate),
                       join<Visit>(on(c(&Doctor::id) == &Visit::doctorId)));
for(auto &row : rows) {
    cout << std::get<0>(row) << '\t' << std::get<1>(row) << '\t' << std::get<2>(row) << '\t' << std::get<3>(row) << endl;
cout << endl;

Two INNER JOINs in one query:

//      trackid,
//      tracks.name AS Track,
//      albums.title AS Album,
//      artists.name AS Artist
//  FROM
//      tracks
//  INNER JOIN albums ON albums.albumid = tracks.albumid
//  INNER JOIN artists ON artists.artistid = albums.artistid;
auto innerJoinRows2 = storage.select(columns(&Track::trackId, &Track::name, &Album::title, &Artist::name),
                                     inner_join<Album>(on(c(&Album::albumId) == &Track::albumId)),
                                     inner_join<Artist>(on(c(&Artist::artistId) == &Album::artistId)));
//  innerJoinRows2 is std::vector<std::tuple<decltype(Track::trackId), decltype(Track::name), decltype(Album::title), decltype(Artist::name)>>

More join examples can be found in examples folder.

Migrations functionality

There are no explicit up and down functions that are used to be used in migrations. Instead sqlite_orm offers sync_schema function that takes responsibility of comparing actual db file schema with one you specified in make_storage call and if something is not equal it alters or drops/creates schema.

//  or

Please beware that sync_schema doesn't guarantee that data will be saved. It tries to save it only. Below you can see rules list that sync_schema follows during call:

  • if there are excess tables exist in db they are ignored (not dropped)
  • every table from storage is compared with it's db analog and
    • if table doesn't exist it is created
    • if table exists its colums are being compared with table_info from db and
      • if there are columns in db that do not exist in storage (excess) table will be dropped and recreated if preserve is false, and table will be copied into temporary table without excess columns, source table will be dropped, copied table will be renamed to source table (sqlite remove column technique) if preserve is true. preserve is the first argument in sync_schema function. It's default value is false. Beware that setting it to true may take time for copying table rows.
      • if there are columns in storage that do not exist in db they will be added using 'ALTER TABLE ... ADD COLUMN ...' command and table data will not be dropped but if any of added columns is null but has not default value table will be dropped and recreated
      • if there is any column existing in both db and storage but differs by any of properties (type, pk, notnull) table will be dropped and recreated (dflt_value isn't checked cause there can be ambiguity in default values, please beware).

The best practice is to call this function right after storage creation.


There are three ways to begin and commit/rollback transactions:

  • explicitly call begin_transaction();, rollback(); or commit(); functions
  • use transaction function which begins transaction implicitly and takes a lambda argument which returns true for commit and false for rollback. All storage calls performed in lambda can be commited or rollbacked by returning true or false.
  • use transaction_guard function which returns a guard object which works just like lock_guard for std::mutex.

Example for explicit call:

auto secondUser = storage.get<User>(2);

secondUser.typeId = 3;
storage.rollback(); //  or storage.commit();

secondUser = storage.get<decltype(secondUser)>(secondUser.id);
assert(secondUser.typeId != 3);

Example for implicit call:

storage.transaction([&] () mutable {    //  mutable keyword allows make non-const function calls
    auto secondUser = storage.get<User>(2);
    secondUser.typeId = 1;
    auto gottaRollback = bool(rand() % 2);
    if(gottaRollback){  //  dummy condition for test
        return false;   //  exits lambda and calls ROLLBACK
    return true;        //  exits lambda and calls COMMIT

The second way guarantees that commit or rollback will be called. You can use either way.

Trancations are useful with changes sqlite function that returns number of rows modified.

storage.transaction([&] () mutable {
    storage.remove_all<User>(where(c(&User::id) < 100));
    auto usersRemoved = storage.changes();
    cout << "usersRemoved = " << usersRemoved << endl;
    return true;

It will print a number of deleted users (rows). But if you call changes without a transaction and your database is located in file not in RAM the result will be 0 always cause sqlite_orm opens and closes connection every time you call a function without a transaction.

Also a transaction function returns true if transaction is commited and false if it is rollbacked. It can be useful if your next actions depend on transaction result:

auto commited = storage.transaction([&] () mutable {    
    auto secondUser = storage.get<User>(2);
    secondUser.typeId = 1;
    auto gottaRollback = bool(rand() % 2);
    if(gottaRollback){  //  dummy condition for test
        return false;   //  exits lambda and calls ROLLBACK
    return true;        //  exits lambda and calls COMMIT
    cout << "Commited successfully, go on." << endl;
    cerr << "Commit failed, process an error" << endl;

Example for transaction_guard function:

  auto guard = storage.transaction_guard(); //  calls BEGIN TRANSACTION and returns guard object
  user.name = "Paul";
  auto notExisting = storage.get<User>(-1); //  exception is thrown here, guard calls ROLLBACK in its destructor
  cerr << "exception" << endl;

In memory database

To manage in memory database just provide :memory: or "" instead as filename to make_storage.

Comparison with other C++ libs

sqlite_orm SQLiteCpp hiberlite ODB
Schema sync yes no yes no
Single responsibility principle yes yes no no
STL compatible yes no no no
No raw string queries yes no yes yes
Transactions yes yes no yes
Custom types binding yes no yes yes
Doesn't use macros and/or external codegen scripts yes yes no no
Aggregate functions yes yes no yes
Prepared statements yes yes no no


To work well your data model class must be default constructable and must not have const fields mapped to database cause they are assigned during queries. Otherwise code won't compile on line with member assignment operator.

For more details please check the project wiki.


Note: Installation is not necessary if you plan to use the fetchContent method, see below in Usage.

Use a popular package manager like vcpkg and just install it with the vcpkg install sqlite-orm command.

Or you build it from source:

git clone https://github.com/fnc12/sqlite_orm.git sqlite_orm
cd sqlite_orm
cmake -B build
cmake --build build --target install

You might need admin rights for the last command.



If you use cmake, there are two supported ways how to use it with cmake (if another works as well or should be supported, open an issue).

Either way you choose, the include path as well as the dependency sqlite3 will be set automatically on your target. So usage is straight forward, but you need to have installed sqlite3 on your system (see Requirements below)

Find Package

If you have installed the lib system wide and it's in your PATH, you can use find_package to include it in cmake. It will make a target sqlite_orm::sqlite_orm available which you can link against. Have a look at examples/find_package for a full example.

find_package(SqliteOrm REQUIRED)

target_link_libraries(main PRIVATE sqlite_orm::sqlite_orm)

Fetch Content (Recommended)

Alternatively, cmake can download the project directly from github during configure stage and therefore you don't need to install the lib before. Againt a target sqlite_orm::sqlite_orm will be available which you can link against. Have a look at examples/fetch_content for a full example.

No CMake

If you want to use the lib directly with Make or something else, just set the inlcude path correctly (should be correct on Linux already), so sqlite_orm/sqlite_orm.h is found. As this is a header only lib, there is nothing more you have to do.


  • C++14 compatible compiler (not C++11 cause of templated lambdas in the lib).
  • Sqlite3 installed on your system and in the path, so cmake can find it (or linked to you project if you don't use cmake)

Video from conference

Video from conference


In case you need a native SQLite client for macOS or Windows 10 you can use SqliteMan https://sqliteman.dev. It is not a commercial. It is a free native client being developed by the maintainer of this repo.

  • 268: Add remove overload for whole object. (fixes #268)

    268: Add remove overload for whole object. (fixes #268)


    I add remove overload for accept whole object, I think it is very good for general repository pattern, that does not know which member variable is key for the entity.

    this PR resolved #268

    opened by soroshsabz 59
  • How to check dependent tables before attempting to delete a parent record?

    How to check dependent tables before attempting to delete a parent record?

    I have a table Bank and a table City. Bank has a foreign key into City. I want to check if a certain City has dependent Banks before I attempt to remove it from the DB. I can do this at a low level like this:

    auto count = storage.count<Bank>(where(is_equal(&Bank::fkey_city, city_primaryKey)));

    There is so much type information in sqlite_orm that I am sure that this can be done at a higher level. I would like to create a class Dependent for each table that is dependent on a Parent table and define the SQL in a generic way, something like:

    template<typename DependentTable, typename ForeignKeyColumn>
    class Dependent
         bool link_exists( int primaryKey)
         { //...

    Is this possible?

    I look for an elegant solution!!

    Regards, Juan

    opened by juandent 53
  • Allow deletion with a set of composite keys

    Allow deletion with a set of composite keys

    I'm currently having an issue where I have something like:

    typedef struct {
        std::string serial_number;
        std::string device_id;
    } Device;

    Would there be a way for me to create a query similar to DELETE FROM devices WHERE (serial_number, device_id) IN (VALUES ('abc', '123'), ('def', '456'))?

    opened by TwelveNights 48
  • How to write a select within a select in sqlite_orm?

    How to write a select within a select in sqlite_orm?

    I have this SQL statement:

    select c.id_claim, (
        SELECT count(*) > 0 
        from Invoices ii 
        WHERE ii.fkey_claim = c.id_claim and ii.fkey_INSResponse = 1
    ), i.fkey_INSResponse = 1, i.number 
    from Invoices i 
    INNER JOIN Claims c on i.fkey_claim = c.id_claim

    and want to write it in sqlite_orm like so:

    auto lines = storage.select(columns(alias_column<als_c>(&Claim::id), select(columns(alias_column<als_s>(&Invoice::id), count()), where(c(alias_column<als_s>(&Invoice::fkey_claim)) == alias_column<als_m>(&Claim::id))),
    		inner_join<als_c>(on(c(alias_column<als_i>(&Invoice::fkey_claim)) == alias_column<als_c>(&Claim::id)))));

    But throws error on compilation:

    error C2027: use of undefined type 'sqlite_orm::internal::column_result_t<St,sqlite_orm::internal::inner_join_t<als_c,sqlite_orm::internal::on_t<sqlite_orm::internal::is_equal_t<sqlite_orm::internal::alias_column_t<als_i,int Invoice::* >,sqlite_orm::internal::alias_column_t<als_c,int Claim::* >>>>,void>'

    what am I doing wrong?

    question feature verify 
    opened by juandent 47
  • How can I produce NULL in a column?

    How can I produce NULL in a column?

    I have this:

    auto statement = storage.prepare(select(union_all(
    			select(columns(quote("--------------------"), 0)),
    			select(columns(as<NamesAlias>(&Employee::m_ename), &Employee::m_depno)))));

    I want to produce a NULL value where I currently have a cero (0)... I tried nullptr and std::nullopt to no avail.

    there must be a way to produce a NULL value!

    This is part of a larger union_all where I want the dashes to separate 2 selects but I don't want a 0 in the second column - I need a numeric value or a NULL so it matches the second select...

    See my point?

    bug in progress 
    opened by juandent 40
  • Exception in the destructor crashes the application

    Exception in the destructor crashes the application

    terminate called after throwing an instance of 'std::system_error'
      what():  unable to close due to unfinalized statements or unfinished backups: database is locked
    (gdb) bt full
    #0  0x00007fd5920e17ff in raise () from /lib64/libc.so.6
    No symbol table info available.
    #1  0x00007fd5920cbc35 in abort () from /lib64/libc.so.6
    No symbol table info available.
    #2  0x00007fd53b90c09b in __gnu_cxx::__verbose_terminate_handler() [clone .cold.1] () from /lib64/libstdc++.so.6
    No symbol table info available.
    #3  0x00007fd53b91253c in __cxxabiv1::__terminate(void (*)()) () from /lib64/libstdc++.so.6
    No symbol table info available.
    #4  0x00007fd53b911559 in __cxa_call_terminate () from /lib64/libstdc++.so.6
    No symbol table info available.
    #5  0x00007fd53b911ed8 in __gxx_personality_v0 () from /lib64/libstdc++.so.6
    No symbol table info available.
    #6  0x00007fd53b674b13 in _Unwind_RaiseException_Phase2 () from /lib64/libgcc_s.so.1
    No symbol table info available.
    #7  0x00007fd53b675081 in _Unwind_RaiseException () from /lib64/libgcc_s.so.1
    No symbol table info available.
    #8  0x00007fd53b9127eb in __cxa_throw () from /lib64/libstdc++.so.6
    No symbol table info available.
    #9  0x00007fd53ccf8875 in sqlite_orm::internal::connection_holder::release (this=0x104fb90) at /usr/include/c++/8/system_error:151
            rc = 5
    #10 0x00007fd53cda5841 in sqlite_orm::internal::connection_ref::~connection_ref (this=0x7fd4c47f6648, __in_chrg=<optimized out>)
        at .../sqlite_orm/sqlite_orm.h:6842
    No locals.
    #11 sqlite_orm::internal::prepared_statement_base::~prepared_statement_base ([email protected]=0x7fd4c47f6640, __in_chrg=<optimized out>)
        at .../sqlite_orm/sqlite_orm.h:6865
    No locals.
    #12 0x00007fd53ce3fa6b in sqlite_orm::internal::prepared_statement_t<sqlite_orm::internal::get_all_t<zroute::ElementModel> >::~prepared_statement_t (this=0x7fd4c47f6640, __in_chrg=<optimized out>)
        at .../sqlite_orm/sqlite_orm.h:6915
    No locals.
    #13 sqlite_orm::internal::storage_t<sqlite_orm::internal::table_t<...> (this=0x1040a70) at .../sqlite_orm/sqlite_orm.h:10486
            statement = {<sqlite_orm::internal::prepared_statement_base> = {stmt = 0x0, con = {holder = @0x104fb90}}, t = {conditions = empty std::tuple}}
            statement = <optimized out>
    #14 ... more custom app logic to follow ...

    I'm using sqlite_orm v1.5 in a multi-threaded environment. When doing some load testing application crashes due to exception being thrown inside a destructor as can be seen from the backtrace above.

    Is there anything that can be done to prevent the application from crashing in this case?

    opened by IvanRibakov 34
  • More complex query example

    More complex query example

    Hey, sorry to keep posting here.. Is there an example somewhere of how you'd do a complex query such as:

    SELECT b.*
    FROM tagmap bt, bookmark b, tag t
    WHERE bt.tag_id = t.tag_id
    AND (t.name IN ('bookmark', 'webservice', 'semweb'))
    AND b.id = bt.bookmark_id
    GROUP BY b.id
    HAVING COUNT( b.id )=3

    I'm attempting to implement a toxi structure similar to the one posted here:


    question good example 
    opened by jakemumu 34
  • User defined function possible in generated_always_as column?

    User defined function possible in generated_always_as column?

    I don't think so, but just in case here it is:

    the function:

    using namespace std::chrono;
    struct Age {
        int operator()(sys_days birth) const {
            auto diff = Today() - birth;
            return  duration_cast<years>(diff).count();
        static const char* name() {
            return "AGE";

    and the make_storage() that won't compile:

        static auto storage = make_storage(dbFilePath,
            make_unique_index("name_unique.idx", &User::name ),
                make_column("id", &User::id, primary_key()),
                make_column("name", &User::name),
                make_column("born", &User::born),
                make_column("job", &User::job),
                make_column("blob", &User::blob),
                make_column("age", &User::age, generated_always_as(func<Age>(&User::born))),
                check(length(&User::name) > 2),
                check(c(&User::born) != 0),
                make_column("id", &Job::id, primary_key(), autoincrement()),
                make_column("name", &Job::name, unique()),
                make_column("base_salary", &Job::base_salary)),
                make_column("name", &UserView::name)));

    I get error:

    1>C:\Components\klaus\include\sqlite_orm\sqlite_orm.h(14071,1): error C2664: 'std::chrono::sys_days sqlite_orm::row_extractor<std::chrono::sys_days,void>::extract(const char *)': cannot convert argument 1 from 'sqlite3_value *' to 'const char *'

    opened by juandent 33
  • provide

    provide "user define function"

    Hi! Could you provide "user define function" function? Refer to the link: http://sqlite.org/c3ref/create_function.html and http://sqlite.org/c3ref/create_collation.html This is usually very helpful for complex comparisons, sorting, etc. /:)

    opened by paineZhang 33
  • Building a large storage consumes lots of memory

    Building a large storage consumes lots of memory

    I'm running into an issue where the build consumes almost all the memory on my device (16GB RAM). I have two storages in the same cpp file. Each storage has ~5 tables, and each table has ~6 columns. The issue arises when I compile with -Ofast.

    Have you had this issue before?

    opened by TwelveNights 32
  • Send a raw sqlite query

    Send a raw sqlite query

    We are transitioning a large chunk of code from using sqlite3 directly to using this ORM. While most of the transition has been easy-ish, we keep hitting a wall with dynamic multi-column sorts.

    In the past, we dynamically generate the sqlite3 query string by evaluating N pairs of column name and sort order. multi_order_by does not work for us in this case, as we have dozens of combinations possible per table.

    Is there a way to gain access to the underlying sqlite3 connection so we can invoke sqlite3_exec directly?

    opened by unclevlad 29
  • Compile error: catch2 too old

    Compile error: catch2 too old

    ERROR: In file included from /home/autumn/Downloads/sqlite_orm-1.8/tests/tests.cpp:5: /home/autumn/Downloads/sqlite_orm-1.8/build/_deps/catch2-src/single_include/catch2/catch.hpp:10877:45: ошибка: размер массива «altStackMem» не является целочисленным константным выражением 10877 | char FatalConditionHandler::altStackMem[sigStackSize] = {}; | ^~~~~~~~~~~~ gmake[2]: *** [tests/CMakeFiles/unit_tests.dir/build.make:494: tests/CMakeFiles/unit_tests.dir/tests.cpp.o] Ошибка 1 gmake[2]: выход из каталога «/home/autumn/Downloads/sqlite_orm-1.8/build» gmake[1]: *** [CMakeFiles/Makefile2:941: tests/CMakeFiles/unit_tests.dir/all] Ошибка 2 gmake[1]: выход из каталога «/home/autumn/Downloads/sqlite_orm-1.8/build» gmake: *** [Makefile:149: all] Ошибка 2



    `catch2 GIT_REPOSITORY https://github.com/catchorg/Catch2.git

    •    GIT_TAG v2.13.2
    •   GIT_TAG v2.13.5

    Please fix this problem

    opened by KoNekoD 1
  • Compile errors: sqlite_orm_VERSION

    Compile errors: sqlite_orm_VERSION "1.8.0", use not_single_header_include

    When I choose use not_single_header_include, I got many errors, please help me: /usr/local/lihuili/pl2117/src/libdatasync/../../sysroot/../prebuilts/gcc/linux-x86/arm/gcc-arm-8.3-2019.03-x86_64-arm-linux-gnueabihf/bin/arm-linux-gnueabihf-g++ --sysroot=/usr/local/lihuili/pl2117/src/libdatasync/../../sysroot -DQT_CORE_LIB -DQT_DBUS_LIB -DQT_GUI_LIB -DQT_NO_DEBUG -DQT_WIDGETS_LIB -D_TARGET_DEVICE -I/usr/local/lihuili/pl2117/build/libdatasync -I/usr/local/lihuili/pl2117/src/libdatasync -I/usr/local/lihuili/pl2117/build/libdatasync/datasync_autogen/include -I/usr/local/lihuili/pl2117/src/libdatasync/../include -I/usr/local/lihuili/pl2117/src/libdatasync/sqlite_orm -isystem /usr/local/lihuili/pl2117/sysroot/usr/include/qt5 -isystem /usr/local/lihuili/pl2117/sysroot/usr/include/qt5/QtWidgets -isystem /usr/local/lihuili/pl2117/sysroot/usr/include/qt5/QtGui -isystem /usr/local/lihuili/pl2117/sysroot/usr/include/qt5/QtCore -isystem /usr/local/lihuili/pl2117/sysroot/usr/../qt/mkspecs/devices/linux-buildroot-g++ -isystem /usr/local/lihuili/pl2117/sysroot/usr/include/qt5/QtDBus -O3 -DNDEBUG -Wno-deprecated-declarations -Wno-unused-result -O3 -Wno-psabi -fPIC -std=gnu++17 -O0 -MD -MT CMakeFiles/datasync.dir/DataBaseUtil_O0.cpp.o -MF CMakeFiles/datasync.dir/DataBaseUtil_O0.cpp.o.d -o CMakeFiles/datasync.dir/DataBaseUtil_O0.cpp.o -c /usr/local/lihuili/pl2117/src/libdatasync/DataBaseUtil_O0.cpp In file included from /usr/local/lihuili/pl2117/src/libdatasync/sqlite_orm/./ast_iterator.h:12, from /usr/local/lihuili/pl2117/src/libdatasync/sqlite_orm/./view.h:11, from /usr/local/lihuili/pl2117/src/libdatasync/sqlite_orm/./storage.h:43, from /usr/local/lihuili/pl2117/src/libdatasync/sqlite_orm/sqlite_orm.h:33, from /usr/local/lihuili/pl2117/src/libdatasync/DataBaseUtil.h:8, from /usr/local/lihuili/pl2117/src/libdatasync/DataBaseUtil.cpp:15: /usr/local/lihuili/pl2117/src/libdatasync/sqlite_orm/./prepared_statement.h:25:13: error: ‘connection_ref’ does not name a type connection_ref con; ^~~~~~~~~~~~~~ /usr/local/lihuili/pl2117/src/libdatasync/sqlite_orm/./prepared_statement.h:79:70: error: ‘connection_ref’ has not been declared prepared_statement_t(T expression_, sqlite3_stmt* stmt_, connection_ref con_) : ^~~~~~~~~~~~~~ /usr/local/lihuili/pl2117/src/libdatasync/sqlite_orm/./prepared_statement.h:135:29: error: ‘set_t’ was not declared in this scope struct update_all_t<set_t<Args...>, Wargs...> { ^~~~~

    opened by kexinmm 9
  • [Question/Suggestion] Throw Error If Data Will Be Deleted

    [Question/Suggestion] Throw Error If Data Will Be Deleted

    Hey -- revisiting this library after a long time and continuing to research it for a project.

    I noticed as I'm working on a local db, if I change a field, the database can have its data ruthlessly dropped which is quite scary.

    Is there a way to have sync_schema tell me if I've coded things in such a way it will drop data when updating the local DB? Perhaps like:

    sync_schema(preserve, throw_error_on_deletion)

    I think if I say preserve is true, sqlite_orm should never delete information, opting to throw an error instead of deleting data. This gives the developer to fix their instructions, perhaps a default value is needed etc, before creating an irreversible action.

    Also -- my feedback would be to pick a sensible default for the user if none is provided, instead of dropping an entire table

    opened by jakemumu 1
  • "Fetch Content" doesn't work because of catch2-src

    I tried to open "fetch_content" (opened CMakeLists.txt from "fetch_content") and cmake configuration failed because it cannot fetch "catch2-src":

    fatal: No url found for submodule path 'tests/_deps/catch2-src' in .gitmodules
    CMake Error at sqliteorm-subbuild/sqliteorm-populate-prefix/tmp/sqliteorm-populate-gitclone.cmake:62 (message):
      Failed to update submodules in:

    cmake 3.25.1 g++/gcc 11.3.0

    opened by zafirovicmilan2 1
  • Is it possible to write 2 or more asterisks in a select?

    Is it possible to write 2 or more asterisks in a select?

    // SELECT artists.*, albums.* FROM artists JOIN albums ON albums.artist_id = artist.id
    auto expression = select(columns(asterisk<Artist>(), asterisk<Album>()), join<Album>(on(c(&Album::m_artist_id) == &Artist::m_id)));
    auto sql = storage.dump(expression);

    This does not compile!

    opened by juandent 1
  • How to insert elements into table with foreign key

    How to insert elements into table with foreign key

    I have a class like this:

    class LikedGenre
    	std::unique_ptr<int> m_userID;
    	std::string m_genre;
    	LikedGenre() = default;
    	LikedGenre(std::unique_ptr<int> userId, std::string genre);
    	~LikedGenre() = default;
    	const std::unique_ptr<int>& GetUserID() const;
    	std::string GetGenre() const;
    	void SetUserID(std::unique_ptr<int> userID);
    	void SetGenre(std::string genre);

    and the corresponding table:

    			make_column("userId", &LikedGenre::GetUserID, &LikedGenre::SetUserID, primary_key()),
    			make_column("genres", &LikedGenre::GetGenre, &LikedGenre::SetGenre),

    My question is, how do I do something like this:

    LikedGenre lg(std::move(userIdPtr), genreName);

    How do I link the UserId to the foreign key from LikedGenre table and insert into said table? Can I do this with templates?

    opened by alexmicato 1
  • v1.8(Dec 9, 2022)

    ⭐ Triggers! No more words

    Triggers have to be specified inside make_storage call just like tables and indexes:

    struct Lead {
        int id = 0;
        std::string firstName;
        std::string lastName;
        std::string email;
        std::string phone;
    struct LeadLog {
        int id = 0;
        int oldId = 0;
        int newId = 0;
        std::string oldPhone;
        std::string newPhone;
        std::string oldEmail;
        std::string newEmail;
        std::string userAction;
        std::string createdAt;
        auto storage = make_storage("",
                                    //  CREATE TRIGGER validate_email_before_insert_leads
                                    //     BEFORE INSERT ON leads
                                    //  BEGIN
                                    //     SELECT
                                    //        CASE
                                    //      WHEN NEW.email NOT LIKE '%[email protected]__%.__%' THEN
                                    //           RAISE (ABORT,'Invalid email address')
                                    //         END;
                                    //  END;
                                                                       .when(not like(new_(&Lead::email), "%[email protected]__%.__%"),
                                                                             then(raise_abort("Invalid email address")))
                                    //  CREATE TRIGGER log_contact_after_update
                                    //     AFTER UPDATE ON leads
                                    //     WHEN old.phone <> new.phone
                                    //          OR old.email <> new.email
                                    //  BEGIN
                                    //      INSERT INTO lead_logs (
                                    //          old_id,
                                    //          new_id,
                                    //          old_phone,
                                    //          new_phone,
                                    //          old_email,
                                    //          new_email,
                                    //          user_action,
                                    //          created_at
                                    //      )
                                    //  VALUES
                                    //      (
                                    //          old.id,
                                    //          new.id,
                                    //          old.phone,
                                    //          new.phone,
                                    //          old.email,
                                    //          new.email,
                                    //          'UPDATE',
                                    //          DATETIME('NOW')
                                    //      ) ;
                                    //  END;
                                                     .when(is_not_equal(old(&Lead::phone), new_(&Lead::phone)) and
                                                           is_not_equal(old(&Lead::email), new_(&Lead::email)))
                                    //  CREATE TABLE leads (
                                    //      id integer PRIMARY KEY,
                                    //      first_name text NOT NULL,
                                    //      last_name text NOT NULL,
                                    //      email text NOT NULL,
                                    //      phone text NOT NULL
                                    //  );
                                               make_column("id", &Lead::id, primary_key()),
                                               make_column("first_name", &Lead::firstName),
                                               make_column("last_name", &Lead::lastName),
                                               make_column("email", &Lead::email),
                                               make_column("phone", &Lead::phone)),
                                    //  CREATE TABLE lead_logs (
                                    //      id INTEGER PRIMARY KEY,
                                    //      old_id int,
                                    //      new_id int,
                                    //      old_phone text,
                                    //      new_phone text,
                                    //      old_email text,
                                    //      new_email text,
                                    //      user_action text,
                                    //      created_at text
                                    //  );
                                               make_column("id", &LeadLog::id, primary_key()),
                                               make_column("old_id", &LeadLog::oldId),
                                               make_column("new_id", &LeadLog::newId),
                                               make_column("old_phone", &LeadLog::oldPhone),
                                               make_column("new_phone", &LeadLog::newPhone),
                                               make_column("old_email", &LeadLog::oldEmail),
                                               make_column("new_email", &LeadLog::newEmail),
                                               make_column("user_action", &LeadLog::userAction),
                                               make_column("created_at", &LeadLog::createdAt)));

    Triggers are being created during sync_schema call if they do not exist.

    For more information please check out an example file at examples/triggers.cpp.

    More info about triggers at sqlite.org.

    ⭐ generated columns support
    struct Product {
         int id = 0;
         std::string name;
         int quantity = 0;
         float price = 0;
         float totalValue = 0;
     auto storage = make_storage({},
                                            make_column("id", &Product::id, primary_key()),
                                            make_column("name", &Product::name),
                                            make_column("quantity", &Product::quantity),
                                            make_column("price", &Product::price),
                                                        //  GENERATED ALWAYS AS (price * quantity)
                                                        generated_always_as(&Product::price * c(&Product::quantity)))));

    More info about generated columns on sqlite.org.

    ⭐ added pointer passing interface API

    Leverages the convenient way of communicating non-SQL values between subcomponents or between an extension and the application.

    Pointer Passing is superior to transforming a C-language pointer into a BLOB or a 64-bit integer, and allows for writing ad-hoc domain-specific extensions from within an application.

    For more information please check the SQLite documentation, as well as the example at examples/pointer_passing_interface.cpp.

    ⭐ added `bool` optional argument to `asterisk` and `object` functions which identifies column order


    auto rows = storage.select(object<User>());
    // decltype(rows) is std::vector<User>, where the User objects are constructed from columns in implicitly stored order
    auto rows = storage.select(object<User>(true));
    // decltype(rows) is std::vector<User>, where the User objects are constructed from columns in declared make_table order
    ⭐ built-in aggregate functions now support `FILTER` clause

    avg(&User::id) -> AVG(users.id) avg(&User::id).filter(where(length(&User::name) > 5)) -> AVG(users.id) FILTER (WHERE LENGTH(users.name) > 5)

    ⭐ indexes support `WHERE` clause
    make_index("idx_contacts_name", &Contract::firstName, &Contract::lastName, where(length(&Contract::firstName) > 2))

    will produce

    CREATE INDEX idx_contacts_name ON contacts (first_name, last_name) WHERE LENGTH(first_name) > 2
    ⭐ added `column_name` member function to `prepared_statement`


    auto statement = storage.prepare(select(columns(&User::id, &User::name)));
    auto idColumnName = statement.column_name(0);
    auto nameColumnName = statement.column_name(1);
    //  decltype(idColumnName) and decltype(nameColumnName) is `std::string_view`

    This API is available only with C++17 and higher

    ⭐ added `dbstat` virtual table support

    SELECT * FROM dbstat; can be called with

    auto storage =
                      make_table("users", make_column("id", &User::id, primary_key()), make_column("name", &User::name)),
    auto dbstatRows = storage.get_all<dbstat>();

    dbstat struct looks as follows:

    struct dbstat {
        std::string name;
        std::string path;
        int pageno = 0;
        std::string pagetype;
        int ncell = 0;
        int payload = 0;
        int unused = 0;
        int mx_payload = 0;
        int pgoffset = 0;
        int pgsize = 0;

    It is available if SQLITE_ENABLE_DBSTAT_VTAB macro is defined.

    More info on sqlite.org

    ⭐ `order_by` supports column aliases (thanks to @trueqbit)


    //  SELECT name, instr(abilities, 'o') i
    //  FROM marvel
    //  WHERE i > 0
    //  ORDER BY i
    auto rows = storage.select(columns(&MarvelHero::name, as<colalias_i>(instr(&MarvelHero::abilities, "o"))),
                               where(greater_than(get<colalias_i>(), 0)),

    For more info please check new example in examples/column_aliases.cpp.

    ⭐ order by kth column (#950, thanks to @trueqbit)


    //  SELECT name, instr(abilities, 'o')
    //  FROM marvel
    //  ORDER BY 2
    auto rows = storage.select(columns(&MarvelHero::name, as<colalias_i>(instr(&MarvelHero::abilities, "o"))),

    For more info please check new example in examples/column_aliases.cpp.

    ⭐ improved `primary_key` API
    • primary_key().asc().autoincrement() makes PRIMARY KEY ASC AUTOINCREMENT
    • primary_key().on_conflict_rollback() makes PRIMARY KEY ON CONFLICT ROLLBACK
    • primary_key().desc().on_conflict_abort() makes PRIMARY KEY DESC ON CONFLICT ABORT

    It means that all available SQLite PRIMARY KEY options now are available in sqlite_orm!

    ⭐ enhanced API for `BEGIN TRANSACTION`


    ⭐ `asterisk` now supports table aliases as well (thanks to @trueqbit)
    auto expression1 = storage.select(asterisk<User>()); // SELECT * FROM users
    // will return the same as
    using als_u = alias_u<User>;
    auto expression2 = storage.select(asterisk<als_u>()); // SELECT * FROM users u
    ⭐ added explicit columns support in `using_` function

    using_(&User::id) will produce the same as using_(column<User>(&User::id)). Why people may need this? To use mapped structs with inheritance.

    ⭐ added `PRAGMA.application_id`
    storage.pragma.application_id(3);  // PRAGMA application_id = 3, function accepts int
    auto value = storage.pragma.application_id();  // PRAGMA application_id, decltype(value) is int
    • asterisk now supports table aliases as well
    • storage.dump now can accept prepared expressions to serialize them (thanks to @trueqbit)
    • ⭐ added new example file chrono_binding.cpp
    • ⭐ added scalar versions of MAX and MIN functions (yes we missed them; thanks to @trueqbit)
    • ⭐ added NULLIF and IFNULL core functions support (thanks to @trueqbit)
    • ⭐ now you can select std::optional<T>() to return NULL as T in raw select
    • ⭐ also you can select nullptr to return NULL as std::nullptr_t in raw select same way
    • update_all now allows updating more than one table at once. If no tables specified orm_error_code::no_tables_specified is thrown
    • transaction_guard_t finally has move constructor. Move it carefully
    • ⭐ prepared statement can be moved as well. Let's make a moving party!
    • std::string_view can be used in queries as read only data not result type (available in C++17 and higher)
    • ⚙️ sync_schema function call supports DROP COLUMN instead of dropping and recreating the whole table in SQLite 3.35 and higher
    • ⚙️ Huge inner template code refactoring. Certainly reduces the amount of resources needed by the compiler (thanks to @trueqbit)
    • ⚙️ having function marked as deprecated. Please use group_by(...).having(...) instead. Simple having function will be removed in v1.9 release
    • ⚙️ Improved unit tests run speed (thanks to @trueqbit)
    • ⚙️ Refactored unit tests to make them more brilliant (thanks to @trueqbit)
    • ⚙️ Properly quote identifiers (thanks to @trueqbit)
    • ⚙️ Refactored complex inner serializing functions and identifiers quoting (thanks to @trueqbit)
    • ⚙️ Optimized passing arguments to user defined functions: removed excess arguments copying
    • ⚙️ Reduced amount of warnings (thanks to @trueqbit)
    • ⚙️ Added new example with blob binding in case you need it examples/blob_binding.cpp
    • ⚙️ Improved multi-threaded use of persistent connections (https://github.com/fnc12/sqlite_orm/pull/1054)
    • ⚙️ Identified and encapsulated workarounds for legacy compilers. Fully works with Visual C++ 2015 Update 3.
    🐞 Bug fixes
    • Exceptions that occur during transaction rollback or commit are now properly propagated. https://github.com/fnc12/sqlite_orm/issues/1027 (thanks to @trueqbit)
    • https://github.com/fnc12/sqlite_orm/issues/969
    • https://github.com/fnc12/sqlite_orm/issues/976
    • https://github.com/fnc12/sqlite_orm/issues/953
    • https://github.com/fnc12/sqlite_orm/issues/922
    • Allow explicit columns in USING-clause (as described above). https://github.com/fnc12/sqlite_orm/issues/955 (thanks to @trueqbit)
    • asterisk now supports table aliases as well https://github.com/fnc12/sqlite_orm/issues/945 (thanks to @trueqbit)
    • https://github.com/fnc12/sqlite_orm/issues/933 (thanks to @trueqbit)
    • https://github.com/fnc12/sqlite_orm/issues/893
    • fixed CHECK serialization. https://github.com/fnc12/sqlite_orm/issues/890
    • added column pointer support for unique constraint. https://github.com/fnc12/sqlite_orm/issues/854, https://github.com/fnc12/sqlite_orm/issues/925, https://github.com/fnc12/sqlite_orm/issues/1065
    • fixed a crash that occurred when using the backup functionality. https://github.com/fnc12/sqlite_orm/issues/855
    • considered bindable expressions in order-by clauses. https://github.com/fnc12/sqlite_orm/issues/847 (thanks to @trueqbit)
    • https://github.com/fnc12/sqlite_orm/issues/841
    • fixed updating nullable columns. https://github.com/fnc12/sqlite_orm/issues/822
    Source code(tar.gz)
    Source code(zip)
  • v1.7.1(Dec 26, 2021)

  • v1.7(Oct 28, 2021)

    ⚖️ license changed from BSD3 to GNU AGPL + paid MIT

    sqlite_orm is being developed more than 5 years and I am (@fnc12) very happy that people love it. But the project now becomes huger and more difficult to maintain. Dropping support is not an option so the best way of going on with active updates is switching to part-time/full-time job mode for me with this library. What does it mean for you? If you use this lib within another open source project then nothing changed cause GNU AGPL license allows using it anywhere if the source of 'anywhere' is open and public available. If you use this lib within a closed source project and you want to update sqlite_orm version used in your project to v1.7 or higher then you need to pay 50$ to obtain a MIT license of sqlite_orm for your project. Payments can be accepted using PayPal. Add your email and project name to payment comment. If you have PRs merged into this lib before then you can have a discount. Please contact lib owner ([email protected]) using e-mail for details. If you'd like to develop sqlite_orm and earn money as a developer please contact owner ([email protected]) using e-mail for details.

    Note: 50$ is not a huge amount. Actually it is two visits for a dinner at cafe. Consider it as a meeting with me at cafe where you pay for a dinner.

    ⭐ added custom scalar and aggregate functions support
    Long story short:
    struct SignFunction {
        double operator()(double arg) const {
            if(arg > 0) {
                return 1;
            } else if(arg < 0) {
                return -1;
            } else {
                return 0;
        static const char *name() {
            return "SIGN";
    //  SELECT SIGN(5)
    auto rows = storage.select(func<SignFunction>(5));

    More info can be found at wiki page.

    ⭐ added raw `INSERT`/`REPLACE` feature

    Sometimes existing storage.insert<T> and storage.replace<T> functions are not enough so now you also have a function to achieve every case during INSERT/REPLACE call. E.g. how to call INSERT INTO ... SELECT?

    //  INSERT INTO artists_backup 
    //  SELECT ArtistId, Name
    //  FROM artists;
                   select(columns(&Artist::id, &Artist::name)));

    or call INSERT OR ABORT:

    //  INTO users(id, name)
    //  VALUES(10, 'Mabel')
                   columns(&User::id, &User::name),
                   values(std::tuple(10, "Mabel")))

    More info can be found at wiki page

    ⭐ added all built in math functions

    SQLite 3.35 added a lot of built in math functions. Now all these functions are also available within sqlite_orm. E.g. sin, cos, log. To use it make sure that your SQLite version is 3.35 or higher and have SQLITE_ENABLE_MATH_FUNCTIONS compilation flag. More info about all built in functions can be found at wiki

    ⭐ added `as_optional` function which allows you obtaining result type as `std::optional`. Available with C++17 or higher

    Why you may need this? In cases when you may get null as a result and want to obtain it as std::nullopt instead.

    auto rows = storage.select(as_optional(&User::id));  // decltype(rows) is std::vector<std::optional<decltype(User::id)>>
    ⭐ added JSON1 extension support

    More extensions - more power! JSON1 is a very useful extension which adds JSON API right into SQLite. Example:

    auto rows = storage.select(json_object("a", 2, "c", 4));  // decltype(rows) is std::vector<std::string> and equal '{"a":2,"c":4}'

    All JSON1 extensions functions are available except json_each and json_tree functions. Information about all JSON1 extension functions are available here.

    ⭐ added strong type collations

    This is an alternative way of using collations. Once user defined functions feature appeared the idea of the same API for collations was born. And here we go:

    struct OtotoCollation {
        int operator()(int leftLength, const void* lhs, int rightLength, const void* rhs) const {
            if(leftLength == rightLength) {
                return ::strncmp((const char*)lhs, (const char*)rhs, leftLength);
            } else {
                return 1;
        static const char* name() {
            return "ototo";
    //  SELECT name
    //  FROM items
    //  WHERE name == 'Mercury' COLLATE 'ototo'
    auto rows = storage.select(&Item::name, where(is_equal(&Item::name, "Mercury").collate<OtotoCollation>()));

    Strong typed collations is a way of writing more clear code cause you need to write a name of your collations only once.

    ⭐ added explicit FROM feature

    sqlite_orm defines tables set for FROM query section for you automatically. But sometimes you may need to specify FROM tables set explicitly. It can happen when you make a subselect:

    int n = storage->count(&ItemData::id,
                                        where(is_equal(&ScanResultData::itemId, &ItemData::id))))));

    will call

    SELECT COUNT(item.id) 
    FROM scan_result, item
                  FROM scan_result, item  
                  WHERE scan_result.item = item.id)

    and it may be not what you expect to be called (pay attention to the second line FROM scan_result, item). Why are there two tables in FROM table set instead of one? Because storage tries to define what tables are mentioned inside query arguments and it does well except some corner cases like this one. So if you want to call a query like this but with only one table inside high level FROM sections then you need to write it like this:

    int n = storage->count(&ItemData::id,
                                        where(is_equal(&ScanResultData::itemId, &ItemData::id))))));

    Function call from<ItemData>() will be serialized to FROM items. If you don't specify any from<T>() call then FROM section table list is deduced automatically as before.

    ⭐ added transformer support for `insert_range` and `replace_range` statements

    Sometimes you may want to use insert_range and replace_range API with containers with not strict objects but something else: pointers, optionals, whatever. In that cases you need to use the third argument of insert_range and replace_range - a transformer caller object:

    // strict objects
    std::vector<User> users;
    // fulfill users vector
    storage.insert_range(users.begin(), users.end());
    // not strict objects
    std::vector<std::unique_ptr<User>> userPointers;
    // fulfill userPointers vector
    storage.insert_range(users.begin(), users.end(), [](const std::unique_ptr<User> &pointer) {
                                                         return *pointer;
    ⭐ added `PRAGMA integrity_check` (thanks to @mishal23)
    auto rows = storage.pragma.integrity_check();
    // or
    auto rows = storage.pragma.integrity_check(5);
    // or
    auto rows = storage.pragma.integrity_check("users");

    decltype(rows) is std::vector<std::string>. More info here.

    ⭐ new core functions support
    • TYPEOF
    • IFNULL
    • ⭐ added static IN feature (fixed bugs https://github.com/fnc12/sqlite_orm/issues/675 and https://github.com/fnc12/sqlite_orm/issues/512)
    • ⭐ added storage.column_name API
    • ⚙️ added noexcept getters and setter modifiers. Available with C++17 and higher
    • ⚙️ added std::nullopt support. It works just like nullptr works and available with C++17 or higher
    • ⚙️ binary operators can be used as row results
    • ⚙️ added some thread safety improvements https://github.com/fnc12/sqlite_orm/pull/736
    • ⚙️ added static assert in case if you try to call storage.insert with a non-insertable table (https://github.com/fnc12/sqlite_orm/pull/644 thanks to @denzor200)
    • ⚙️ improved serialization for some AST nodes: std::string was replaced with std::string_view for C++17. It reduces amount of heap allocations during query serialization
    • ⚙️ file tests/CMakeLists.txt now has a pretty look (thanks to @undisputed-seraphim)
    • ⚙️ fixed GCC warnings (thanks to @denzor200)
    • ⚙️ improved code formatting
    • ⚙️ iterator_t now is compatible with std::input_iterator concept (https://github.com/fnc12/sqlite_orm/pull/685 thanks to @andrei-datcu)
    • ⚙️ field_printer now has an additional template argument for SFINAE tricks (thanks to @Overlordff)
    • ⚙️ improved bool transaction(const std::function<bool()>& f) call - now it uses guard inside to make calls safer (thanks to @denzor200)
    🐞 Bug fixes
    • https://github.com/fnc12/sqlite_orm/issues/625
    • https://github.com/fnc12/sqlite_orm/issues/638
    • https://github.com/fnc12/sqlite_orm/issues/654 (thanks to @denzor200)
    • https://github.com/fnc12/sqlite_orm/issues/663 (thanks to @denzor200)
    • https://github.com/fnc12/sqlite_orm/issues/687
    • https://github.com/fnc12/sqlite_orm/issues/699
    • https://github.com/fnc12/sqlite_orm/issues/716 (thanks to @Ashoat)
    • https://github.com/fnc12/sqlite_orm/issues/723
    • https://github.com/fnc12/sqlite_orm/issues/730
    • https://github.com/fnc12/sqlite_orm/issues/632
    • https://github.com/fnc12/sqlite_orm/issues/765
    • https://github.com/fnc12/sqlite_orm/issues/784

    Special thanks to: @denzor200 @mishal23 @undisputed-seraphim @Ashoat @andrei-datcu

    Source code(tar.gz)
    Source code(zip)
  • 1.6(Oct 8, 2020)

    ⭐ Added `CHECK` constraint
    auto storage = make_storage("database.sqlite",
                                           make_column("contact_id", &Contact::id, primary_key()),
                                           make_column("phone", &Contact::phone),
                                           check(length(&Contact::phone) >= 10)));


    CREATE TABLE contacts (
        contact_id INTEGER NOT NULL PRIMARY KEY,
        phone TEXT NOT NULL,
        CHECK(LENGTH(phone >= 10))


    auto storage = make_storage("database.sqlite",
                                           make_column("Book_id", &Book::id, primary_key()),
                                           make_column("Book_name", &Book::name),
                                           make_column("Pub_name", &Book::pubName),
                                           make_column("PRICE", &Book::price, check(c(&Book::price) > 0))));


        Book_name TEXT NOT NULL,
        Pub_name TEXT NOT NULL,
    ⭐ Added bitwise operators support
    storage.select(bitwise_or(60, 13));  // SELECT 60 | 13
    storage.select(bitwise_and(60, 13));  // SELECT 60 & 13
    storage.select(bitwise_shift_left(60, 2));  // SELECT 60 << 2
    storage.select(bitwise_shift_right(60, 2));  // SELECT 60 >> 2
    storage.select(bitwise_not(60));  // SELECT ~60
    ⭐ Added `indexed_column` function to specify order (`ASC`, `DESC`) and collation for indexed columns
    auto storage = make_storage({}, 
                                make_index("name_index", indexed_column(&User::name).collate("binary").asc()), 
                                           make_column("id", &User::id), 
                                           make_column("name", &User::name));

    will translate to

    CREATE TABLE users (
        id INTEGER NOT NULL,
        name TEXT NOT NULL);
    CREATE INDEX name_index ON users (name COLLATE binary ASC);
    ⭐ New core functions
    • HEX
    • QUOTE
    • INSTR
    • ROUND
    ⭐ New date & time functions - all date & time functions are supported from now!
    • TIME
    ⭐ Added `storage.dump` function for prepared statements
    auto statement = storage.prepare(select(&User::id, where(length(&User::name) > 5 and like(&User::name, "T%"))));
    auto str = storage.dump(statement);  // str is something like 'SELECT \"users\".\"name\", \"users\".\"id\" FROM 'users'  WHERE ( ((\"id\" % 2) = 0)) ORDER BY \"users\".\"name\" '

    The difference between statement.sql is that dump function prints real values instead of question marks. Also it does not call any sqlite3 functions - it calls sqlite_orm serializer instead.

    ⭐ Added custom container support for `get_all` prepared statement


    auto statement = storage.prepare(get_all<User, std::list<User>>());
    ⭐ `UNIQUE` constraint supports more than one column
               make_column("shape_id", &Shape::id, primary_key()),
               make_column("background_color", &Shape::backgroundColor),
               make_column("foreground_color", &Shape::foregroundColor),
               sqlite_orm::unique(&Shape::backgroundColor, &Shape::foregroundColor))
    ⭐ New table operating API


    * storage.rename_table<User>("new_table_name") -> change name in table information not database
    * storage.rename_table("old_name", "new_name");  -> rename table using SQL query
    * storage.tablename<User>(); -> get table name as `std::string` from table info not database
    ⭐ Added `VALUES` API


    //    DELETE FROM devices
    //    WHERE (serial_number, device_id) IN (VALUES ('abc', '123'), ('def', '456'))
    storage.remove_all<Device>(where(in(std::make_tuple(&Device::serialNumber, &Device::deviceId),
                                             values(std::make_tuple("abc", "123"), std::make_tuple("def", "456")))));
    //  or
             where(in(std::make_tuple(&Device::serialNumber, &Device::deviceId),
                      values(std::vector<std::tuple<std::string, std::string>>{std::make_tuple("abc", "123"),
                                                                               std::make_tuple("def", "456")}))));

    These queries are the same. The difference between them is that the first is static and the second is dynamic (std::vector based). It may be useful if you change bound values using get API.

    • ⚙️ sync_schema behavior changes: now types are ignored cause SQLite ignores them too. It allows using custom types.
    • ⚙️ Fixed all clang and GCC warnings.
    • 🐞 Fixed bug: unable to use reserved keywords as foreign key columns
    • 🐞 Fixed bug: compilation error during using any core function within CASE operator
    • 🐞 Fixed bug in sync_schema: https://github.com/fnc12/sqlite_orm/issues/521
    • 🐞 Fixed backup bug: https://github.com/fnc12/sqlite_orm/issues/540

    Special thanks to: @undisputed-seraphim @Leon0402 @air-h-128k-il

    Source code(tar.gz)
    Source code(zip)
    sqlite_orm.h(468.96 KB)
  • 1.5(Jul 24, 2020)

    ⭐ Prepared statements
    //  SELECT doctor_id
    //  FROM visits
    //  WHERE LENGTH(patient_name) > 8
    auto selectStatement = storage.prepare(select(&Visit::doctor_id, 
                                           where(length(&Visit::patient_name) > 8)));
    cout << "selectStatement = " << selectStatement.sql() << endl;
    auto rows = storage.execute(selectStatement);
    get<0>(selectStatement) = 10;    // change LENGTH(patient_name) > 8 to LENGTH(patient_name) > 10
    auto rows2 = storage.execute(selectStatement);

    More info can be found in the example

    ⭐ GLOB operator
    //  SELECT id, first_name, last_name
    //  FROM users
    //  WHERE first_name GLOB 'C*'
    auto users = storage.get_all<User>(where(glob(&User::firstName, "C*")));


    //  SELECT id
    //  FROM users
    //  WHERE last_name GLOB '*a*' OR first_name LIKE 'J%'
    auto rows = storage.select(&User::id, where(glob(lower(&User::lastName), "*a*") 
                               or like(&User::firstName, "J%"));

    More info about GLOB

    ⭐ std::optional support (C++17 and higher)
    auto userMaybe = storage.get_optional<User>(14);  // decltype(userMaybe) is std::optional<User>
        cout << "user = " << storage.dump(userMaybe.value()) << endl;
        cout << "user with id 14 doesn't exist" << endl;

    std::optional better suites for returning nullable data than std::unique_ptr so it is highly recommended to use storage_t::get_optional instead of storage_t::get_pointer to avoid extra heap allocations. Hint: available only with C++17 or higher. One can set C++ standard version with -std=c++17 compiler option with clang and gcc or in target properties in Visual Studio and Xcode. For different build systems please check out related documentation.

    More info about std::optional on cppreference

    ⭐ get_all_pointer query

    storage_t:: get_all_pointer can be useful if you want to obtain your objects allocated as unique pointers.

    auto users = storage.get_all_pointer<User>();  // decltype(users) is std::vector<std::unique_ptr<User>>


    auto statement = storage.prepare(get_all_pointer<User>(where(c(&User::id) < 100));
    auto users = storage.execute(statement);  // decltype(users) is std::vector<std::unique_ptr<User>>
    ⭐ Improved DEFAULT constraint

    DEFAULT constraint can accept not only literals but functions like DATETIME. sqlite_orm now also has support for it.

    auto storage = make_storage("myDatabase.sqlite",
                       make_column("timestamp", &Induction::time, default_value(datetime("now", "localtime")))));


    CREATE TABLE induction (
        timestamp INTEGER NOT NULL DEFAULT DATETIME('now', 'localtime')
    ⚙️ Query static validations

    Once you try to create a query with more than one WHERE options you get a static assert telling you "a single query cannot contain > 1 WHERE blocks". Same check works for:

    • WHERE
    • GROUP BY
    • ORDER BY
    • LIMIT

    Before you'd know that you constructed incorrect query only in runtime. Now this check happens in compile time!

    ⚙️ storage_t::filename()

    Use storage_t::filename() function to retrieve filename passed in storage during construction. Example:

    const auto &filename = storage.filename();  // decltype(filename) is std::string
    ⚙️ Added code format with clang-format

    All library code is formatted with clang-format using config located in project root. From now when you create a pull request please don't forget to format it using clang-format tool. If code is not formatted then your pull request will be declined cause one of CI check will be failed. More information about clang-format can be found here.

    🐞 Fixed bug with incorrect PRIMARY KEY and AUTOINCREMENT order

    Now one can write these two constraints in either order: the correct one and the legacy one.

    • 🐞 fixed compilation errors with older versions of sqlite3

    • 🐞 #384

    • 🐞 #369

    • 🐞 #400

    Source code(tar.gz)
    Source code(zip)
    sqlite_orm.h(445.84 KB)
  • v1.4(Aug 13, 2019)

    ⭐ CASE support
    //      CASE
    //      WHEN MARKS >=80 THEN 'A+'
    //      WHEN MARKS >=70 THEN 'A'
    //      WHEN MARKS >=60 THEN 'B'
    //      WHEN MARKS >=50 THEN 'C'
    //      ELSE 'Sorry!! Failed'
    //      END
    //      FROM STUDENT;
    auto rows = storage.select(columns(&Student::id,
                                       .when(greater_or_equal(&Student::marks, 80), then("A+"))
                                       .when(greater_or_equal(&Student::marks, 70), then("A"))
                                       .when(greater_or_equal(&Student::marks, 60), then("B"))
                                       .when(greater_or_equal(&Student::marks, 50), then("C"))
                                       .else_("Sorry!! Failed")
    //    decltype(rows) is std::vector<std::tuple<decltype(Student::id), decltype(Student::name), decltype(Student::marks), std::string>>


    //    SELECT CASE country WHEN 'USA' THEN 'Dosmetic' ELSE 'Foreign' END
    //    FROM users
    auto rows = storage.select(columns(case_<std::string>(&User::country)
                                               .when("USA", then("Dosmetic"))
                                       multi_order_by(order_by(&User::lastName), order_by(&User::firstName)));
    //    decltype(rows) is std::vector<std::string>
    ⭐ Added core functions: COALESCE, ZEROBLOB, SUBSTR
    //  SELECT coalesce(10,20);
    cout << "coalesce(10,20) = " << storage.select(coalesce<int>(10, 20)).front() << endl;
    //  SELECT substr('SQLite substr', 8);
    cout << "substr('SQLite substr', 8) = " << storage.select(substr("SQLite substr", 8)).front() << endl;
    //  SELECT substr('SQLite substr', 1, 6);
    cout << "substr('SQLite substr', 1, 6) = " << storage.select(substr("SQLite substr", 1, 6)).front() << endl;
    //  SELECT zeroblob(5);
    cout << "zeroblob(5) = " << storage.select(zeroblob(5)).front().size() << endl;
    ⭐ Dynamic ORDER BY

    order_by and multi_order_by are strong typed so you cannot specify ORDER BY column type at runtime. dynamic_order_by solves this problem. dynamic_order_by is a multi_order_by that accepts order_by conditions at runtime. Example:

    auto orderBy = dynamic_order_by(storage);
    auto rows = storage.get_all<User>(where(...), orderBy);
    ⭐ Added LIKE as a query result

    Now LIKE can also be used as a core function to retrieve a result:

    auto rows = storage.select(like(&User::name, "J%"));
    //    decltype(rows) is std::vector<bool>
    ⭐ Added LIKE ESCAPE option support

    LIKE has a third argument and now it is available in sqlite_orm:

    //    SELECT name LIKE 'J%' ESCAPE '_'
    //    FROM users
    auto rows = storage.select(like(&User::name, "J%").escape("_"));


    //    SELECT LIKE(name, 'J%', '_')
    //    FROM users
    auto rows = storage.select(like(&User::name, "J%", "_"));
    • ⚙️ Added Catch2 unit tests framework into unit tests project
    • ⚙️ Added unit tests configurations for even more platforms and compilers (thanks to @Farwaykorse)
    • ⚙️ Added contributing doc
    • 🚀 Added nullptr binding to WHERE conditions
    • 🚀 Reduced binary size
    • 🚀 Added composite key support for storage_t::remove function
    • 🚀 Reduces memory consumption
    🚀 Better error reporting

    Before once you get an exception thrown asking e.what() gave you a poor text like NOT NULL constraint failed. Now it is more detailed (thanks to sqlite3_errmsg function) like: NOT NULL constraint failed: users.age: constraint failed

    🐞 Bug fixes
    • Fixed GCC6 compilation bug
    • Fixed runtime error on ARM architecture
    • Fixed getter by value support for storage_t::replace and storage_t::update functions
    • Fixed bug with iterating over blob values
    • Fixed on_copy coping on storage_t copy
    • Fixed silencing binding failure - now exception is thrown
    • Fixed using std::unique_ptr in storage_t::update_all set arguments
    • Fixed incorrect (reverse) arguments order in GROUP BY
    Source code(tar.gz)
    Source code(zip)
    sqlite_orm.h(364.48 KB)
  • v1.3(Aug 11, 2019)

    ⭐ Complex subqueries
    SELECT cust_code, cust_name, cust_city, grade
    FROM customer
    WHERE grade=2 AND EXISTS
        (SELECT COUNT(*)
        FROM customer
        WHERE grade=2
        GROUP BY grade
        HAVING COUNT(*)>2);

    now can be called with this way:

    auto rows = storage.select(columns(&Customer::code, &Customer::name, &Customer::city, &Customer::grade),
                                       where(is_equal(&Customer::grade, 2)
                                             and exists(select(count<Customer>(),
                                                               where(is_equal(&Customer::grade, 2)),
                                                               having(greater_than(count(), 2))))));

    All compound operators now are available:

    SELECT dept_id
    FROM dept_master
    SELECT dept_id
    FROM emp_master

    is just

    auto rows = storage.select(except(select(&DeptMaster::deptId),


    SELECT dept_id
    FROM dept_master
    SELECT dept_id
    FROM emp_master

    is just

    auto rows = storage.select(intersect(select(&DeptMaster::deptId),
    • ⭐ Column aliases

    • SELECT * FROM table with syntax storage.select(asterisk<T>()) returns std::tuple of mapped members' types

    • CAST(expression AS type) expression with cast<T>(expression) syntax

    • ⭐ added julianday function

    • 🚀 FOREIGN KEY now works with composite PRIMARY KEY

    🚀 added simple arithmetic types biding to WHERE conditions
    bool myFilterIsOn = getMyFilterValue();
    auto values = storage.get_all<User>(where(!myFilterIsOn and like(&User::name, "Adele%")));
    • 🚀 improved performance - replaced std::shared_ptr with std::unique_ptr inside storage, view iterator and aggregate functions
    • ⚙️ added Windows CI with Appveyor (thanks to @soroshsabz)
    • 🐞 Bug fixes - fixed runtime error which can be faced during storage::iterate() call
    • ⚠️ Minor warning fixes
    Source code(tar.gz)
    Source code(zip)
    sqlite_orm.h(358.09 KB)
  • v1.2(Jul 4, 2018)

    • UNION Use union_/union_all functions with two subselects to query data with UNION/UNION ALL operators
    • Custom collations Now you can bind your function as a collating function used to compare data during selection
    • Setters/getters Added different getters/setters support. E.g. now one can easily map Protobuf structure as is
    • Easier development Library code is split into different header files for easily development. Also final code is still available as a single source for include
    • Explicit types Explicit columns/tables types. Now one can map subclasses with inherited fields and select them from database properly
    • Limits Added all SQLite limits API
    • Explicit INSERT Now you can specify columns to insert from provided object
    • Static unit tests Static unit tests are checked at compile time and have a bulk of static_assert functions.
    • Speed Decreased dump() and several other functions work time
    Source code(tar.gz)
    Source code(zip)
    sqlite_orm.h(316.49 KB)
  • v1.1(Mar 17, 2018)


    • VACUUM Use storage.vacuum() to call VACUUM query explicitly or use storage.pragma.auto_vacuum(...); to set PRAGMA auto_vacuum.
    • Arithmetic operators +, -, *, / and % are now available for using within expressions. Example: auto doubledAge = storage.select(c(&User::age) * 2); or auto doubledAge = storage.select(mul(c(&User::age), 2));. As you can see every operator has a function in case you like functions more than operators: c(&User::age) + 5 is add(&User:age, 5). Also sub, mul, div and mod functions are now available in sqlite_orm namespace.
    • Bug fixes Fixed compilation error in case sqlite_orm.h file is included in more than one place. Fixed incorrect query generation in remove call in case PRIMARY KEY is defined as a separate column.
    • Warning fixes Fixed three Visual Studio 2017 warnings (thanks to @ntkernelcom)
    Source code(tar.gz)
    Source code(zip)
    sqlite_orm.h(270.70 KB)
  • v1.0(Mar 9, 2018)

    Finally sqlite_orm v1.0 is released. This is a first stable version. All future versions with the same major version number will have back-compatibility with this version. If you meet broken compatibility within the same major version please report a bug in the issues section.


    • No raw string queries: forget about db << "SELECT " + idColumnName + " FROM " + myTableName + " WHERE " + idColumnName + " < 10 ORDER BY " + nameColumnName;. Just write storage.select(&Object::id, where(c(&Object::id) < 10), order_by(&Object::name)); instead
    • Intuitive syntax most of SQLite3 keywords are provided as functions in sqlite_orm: foreign_key, unique, autoincrement, default_value, collate, using_, on, cross_join, natural_join, left_join, join, left_outer_join, inner_join, offset, limit, is_null, is_not_null, in, where, order_by, group_by, between, like, date, datetime, char_, trim, ltrim, rtrim, changes, length, abs, lower, upper, avg, count, sum, max, min, total, group_concat, distinct, all, rowid, oid, _rowid_. Just imagine SQL syntax is provided in your IDE. (Hint: don't forget to add using namespace sqlite_orm)
    • Comfortable interface - one code line per single query yes, there are no service objects required to be declared to make a single query. You can write the most complicated queries within a single semicolon:
    storage.select(columns(&Visit::mark, &Visit::visited_at, &Location::place),
                   inner_join<Location>(on(is_equal(&Visit::location, &Location::id))),
                   where(is_equal(&Visit::user, id) and
                         greater_than(&Visit::visited_at, fromDate) and
                         lesser_than(&Visit::visited_at, toDate) and
                         lesser_than(&Location::distance, toDistance)),
    • Built with modern C++14 features (no macros and external scripts) yes, some ORM libs require scripts/macros to make columns to members mapping work. But sqlite_orm just works as is
    • CRUD support declare a variable and insert it without any other unnecessary stuff. Next get it by a primary key, update it, remove it or replace it.
    • Pure select query support also you can just get a std::vector (or any other STL-compatible container) of any column with or without any desired where conditions. Or you can even select several column in a vector of tuples.
    • STL compatible it means two things: 1) select your objects or columns in any STL compatible container (std::list, QList or even nlohmann::json); 2) iterate your objects in C++11 for loop:
    for(auto &user : storage.iterate<User>()) {
        cout << storage.dump(user) << endl;
    • Custom types binding support sqlite_orm understands implicitly what column type must be by member pointer type you provide. E.g. std::string member pointer maps to TEXT, int, long map to INTEGER, float, double map to REAL. But you can also use your custom types if you have it. You can even bind your enum to be mapped as string or int or whatever. Or even bind boost::optional<T> as nullable generic type.
    • BLOB support BLOB SQLite type maps to std::vector<char> or you can add binding to any other type.
    • FOREIGN KEY support you can use FOREIGN KEY with intuitive syntax: foreign_key(&Visit::location).references(&Location::id). And you don't need to call PRAGMA foreign_keys = 1 every time - storage class calls it for you on every database open if there is at least one foreign key exists.
    • Composite key support PRIMARY KEY with several columns also supported. Just write primary_key(&User::id, &User::firstName) and your composite key is ready to go.
    • JOIN support all kinds of JOIN supported by SQLite are also supported by the lib.
    • Transactions support transaction is one the most important performance improvement tool. There are three different ways to use transactions in the lib to make your code more flexible and stable.
    • Migrations functionality sometimes when you use some ORM libs you need to create your database with tables first. Someone performs it at runtime, someone creates all tables with a SQLite client and adds this file in the project assets. Forget about it. Just call sync_schema and storage will check all tables and columns and if there is something missing it will recreate/alter it. sync_schema guarantees that schema will be the same as you specified during make_storage call.
    • Powerful conditions don't be shy - use any combinations of conditions during selection/deleting.
    • INDEX support use indexes as is - just specify member pointer in make_index function.
    • Follows single responsibility principle this is a very important thing which many developers omit - your data model classes must know nothing about storage and other services. It is very useful if your software has a lot of modules and sometimes you change some of them.
    • Easy integration single header, no .cpp files. Use conan, cmake or just include it as is.
    • The only dependency 5 seconds required to connect the lib to your project.
    • C++ standard code style no 'initcapped' C# like function names, no camel case in public function/classes names. Include it and use it just like it is one of the standard headers.
    • No undefined behaviour as you know some code in standard library can produce undefined behaviour (e.g. std::vector<int>()[5]). sqlite_orm creators do not like undefined behavior at all. So if something goes wrong be ready to catch std::system_error. By this std::system_error you can know whether error happened in SQLIte or in the lib by inspecting the error_category.

    sqlite_orm.h SHA1 6e0b40c2b7122c02cb6d9efbade487689d933827

    Source code(tar.gz)
    Source code(zip)
    sqlite_orm.h(261.05 KB)
Yevgeniy Zakharov
Mobile developer. Have experience in: C++, Objective-C, Swift, C#, Java. Frameworks: cocos2d-x, cocoa touch, cocoa, .NET, Android SDK/NDK, gtkmm
Yevgeniy Zakharov
DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite.

DB Browser for SQLite What it is DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files c

null 17.5k Jan 2, 2023
React-native-quick-sqlite - ⚡️ The fastest SQLite implementation for react-native.

React Native Quick SQLite The **fastest** SQLite implementation for react-native. Copy typeORM patch-package from example dir npm i react-nati

Oscar Franco 423 Dec 30, 2022
A project to create a simple ORM.

cpp-ORM Current build status : An ORM project. You can simply create persistent objects using databases. The object representation: Each object have t

Maxime Barbier 5 Dec 14, 2020
A friendly and lightweight C++ database library for MySQL, PostgreSQL, SQLite and ODBC.

QTL QTL is a C ++ library for accessing SQL databases and currently supports MySQL, SQLite, PostgreSQL and ODBC. QTL is a lightweight library that con

null 173 Dec 12, 2022
The C++14 wrapper around sqlite library

sqlite modern cpp wrapper This library is a lightweight modern wrapper around sqlite C api . #include<iostream> #include <sqlite_modern_cpp.h> using n

null 720 Dec 29, 2022
SQLean: all the missing SQLite functions

SQLite has very few functions compared to other DBMS. SQLite authors see this as a feature rather than a bug, because SQLite has extension mechanism in place.

Anton Zhiyanov 2.1k Jan 8, 2023
An SQLite binding for node.js with built-in encryption, focused on simplicity and (async) performance

Description An SQLite (more accurately SQLite3MultipleCiphers) binding for node.js focused on simplicity and (async) performance. When dealing with en

mscdex 14 May 15, 2022
Yet another SQLite wrapper for Nim

Yet another SQLite wrapper for Nim Features: Design for ARC/ORC, you don’t need to close the connection manually Use importdb macro to create helper f

Code Hz 9 Jan 4, 2023
Fork of sqlite4java with updated SQLite and very basic compiler hardening enabled.

Download latest version: sqlite4java-392 with SQLite 3.8.7, Windows/Linux/Mac OS X/Android binaries OSGi bundle 1.0.392 with sqlite4java-392 Files for

GrapheneOS 6 Oct 26, 2022
An updated fork of sqlite_protobuf, a SQLite extension for extracting values from serialized Protobuf messages.

This fork of sqlite_protobuf fixes some issues (e.g., #15) and removes the test suite that we do not use. It also comes with proto_table, a C library

Backtrace Labs 18 Oct 19, 2022
Serverless SQLite database read from and write to Object Storage Service, run on FaaS platform.

serverless-sqlite Serverless SQLite database read from and write to Object Storage Service, run on FaaS platform. NOTES: This repository is still in t

老雷 7 May 12, 2022
Verneuil is a VFS extension for SQLite that asynchronously replicates databases to S3-compatible blob stores.

Verneuil: streaming replication for sqlite Verneuil1 [vɛʁnœj] is a VFS (OS abstraction layer) for sqlite that accesses local database files like the d

Backtrace Labs 307 Dec 21, 2022
Unofficial git mirror of SQLite sources (see link for build instructions)

SQLite Source Repository This repository contains the complete source code for the SQLite database engine. Some test scripts are also included. Howeve

null 2k Dec 25, 2022
A hook for Project Zomboid that intercepts files access for savegames and puts them in an SQLite DB instead.

ZomboidDB This project consists of a library and patcher that results in file calls for your savegame(s) being transparently intercepted and redirecte

Oliver 7 Aug 27, 2022
Lightweight C++ wrapper for SQLite

NLDatabase Lightweight C++ wrapper for SQLite. Requirements C++11 compiler SQLite 3 Usage Let's open a database file and read some rows: #include "NLD

Raven 0 Sep 20, 2019
Writing a sqlite clone from scratch in C++

如何用C++实现一个简易数据库 基于cstack/db_tutorial C语言版本 KCNyu 2022/2/2 作为笔者写的第一个系列型教程,还是选择基于前人的教程经验以及添加一些自己个人的探索。也许有很多纰漏之处,希望大家指正。 1. 数据库是什么? 数据库是“按照数据结构来组织、存储和管理数

shengyu.li 69 Dec 27, 2022
A lightweight header-only C++11 library for quick and easy SQL querying with QtSql classes.

EasyQtSql EasyQtSql is a lightweight header-only C++11 library for quick and easy SQL querying with QtSql classes. Features: Header only C++11 library

null 53 Dec 30, 2022
redis-cpp is a header-only library in C++17 for Redis (and C++11 backport)

redis-cpp - lightweight C++ client library for Redis redis-cpp is a C++17 library for executing Redis commands with support for pipelines and the publ

null 77 Dec 11, 2022
An Ultra Light DataBase Project

An Ultra Light DataBase Project

Jianxff 3 Nov 28, 2022