v1.3

⭐ 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)),
                                                           group_by(&Customer::grade),
                                                           having(greater_than(count(), 2))))));

⭐ EXCEPT and INTERSECT

All compound operators now are available:

SELECT dept_id
FROM dept_master
EXCEPT
SELECT dept_id
FROM emp_master

is just

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

and

SELECT dept_id
FROM dept_master
INTERSECT
SELECT dept_id
FROM emp_master

is just

auto rows = storage.select(intersect(select(&DeptMaster::deptId),
                                             select(&EmpMaster::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

v1.2 Union

• 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

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 = 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)

The very first

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 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)),
               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