All Versions
Latest Version
Avg Release Cycle
157 days
Latest Release
1349 days ago

Changelog History

  • v1.6 Changes

    October 08, 2020

    ๐Ÿฑ โญ Added CHECK constraint

    auto storage = make\_storage("database.sqlite", make\_table("contacts", 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\_table("BOOK", 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))));



    ๐Ÿฑ โญ Added bitwise operators support\_or(60, 13)); // SELECT 60 |\_and(60, 13)); // SELECT 60 &\_shift\_left(60, 2)); // SELECT 60 \<\<\_shift\_right(60, 2)); // SELECT 60 \>\>\_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\_table("users", 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
    • SOUNDEX ๐Ÿฑ โญ New date & time functions - all date & time functions are supported from now!

    • TIME

    • STRFTIME ๐Ÿฑ โญ 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\_table("shapes", 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")))));// orstorage.remove\_all\<Device\>( 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: #521
    • ๐Ÿฑ ๐Ÿž Fixed backup bug: #540

    Special thanks to:

  • v1.5

    December 22, 2019
  • v1.4 Changes

    August 13, 2019

    ๐Ÿ‘ โญ CASE support

    // SELECT ID, NAME, MARKS,// 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 =, &Student::name, &Student::marks, case\_\<std::string\>() .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") .end()));// 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 usersauto rows =\_\<std::string\>(&User::country) .when("USA", then("Dosmetic")) .else\_("Foreign") .end()), 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) = " \<\<\<int\>(10, 20)).front() \<\< endl;// SELECT substr('SQLite substr', 8);cout \<\< "substr('SQLite substr', 8) = " \<\<"SQLite substr", 8)).front() \<\< endl;// SELECT substr('SQLite substr', 1, 6);cout \<\< "substr('SQLite substr', 1, 6) = " \<\<"SQLite substr", 1, 6)).front() \<\< endl;// SELECT zeroblob(5);cout \<\< "zeroblob(5) = " \<\< \<\< 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);if(shouldOrderByNameAndId){ orderBy.push\_back(order\_by(&User::name)); orderBy.push\_back(order\_by(&User::id)); }else{ orderBy.push\_back(order\_by(&User::id)); }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 =, "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 usersauto rows =, "J%").escape("\_"));


    // SELECT LIKE(name, 'J%', '\_')// FROM usersauto rows =, "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
  • v1.3 Changes

    August 11, 2019

    โญ Complex subqueries

    SELECT cust\_code, cust\_name, cust\_city, gradeFROM customerWHERE grade=2 AND EXISTS (SELECT COUNT(\*) FROM customer WHERE grade=2GROUP BY grade HAVING COUNT(\*)\>2);

    now can be called with this way:

    auto rows =, &Customer::name, &Customer::city, &Customer::grade), where(is\_equal(&Customer::grade, 2) and exists(select(count\<Customer\>(), where(is\_equal(&Customer::grade, 2)), group\_by(&Customer::grade), having(greater\_than(count(), 2))))));


    All compound operators now are available:

    SELECT dept\_idFROM dept\_master EXCEPTSELECT dept\_idFROM emp\_master

    is just

    auto rows =, select(&EmpMaster::deptId)));


    SELECT dept\_idFROM dept\_master INTERSECTSELECT dept\_idFROM emp\_master

    is just

    auto rows =, select(&EmpMaster::deptId)));

    โญ Column aliases

    โญ SELECT * FROM table with syntax<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
  • v1.2 Changes

    July 04, 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
  • v1.1 Changes

    March 17, 2018

    ๐Ÿ”‹ Features

    • 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 = * 2); or auto doubledAge =, 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)
  • v1.0 Changes

    March 09, 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.

    ๐Ÿ”‹ Features

    • No raw string queries :
      forget about db << "SELECT " + idColumnName + " FROM " + myTableName + " WHERE " + idColumnName + " < 10 ORDER BY " + nameColumnName;. Just write, 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:, &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)), order_by(&Visit::visited_at));

    • 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