There is a keyword WITH in SQLite which allows making unreal queries like Mandelbrot Set (Apfelmaennchen in German):

WITH RECURSIVE
  xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
  yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
  m(iter, cx, cy, x, y) AS (
    SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
    UNION ALL
    SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m 
     WHERE (x*x + y*y) < 4.0 AND iter<28
  ),
  m2(iter, cx, cy) AS (
    SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
  ),
  a(t) AS (
    SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') 
    FROM m2 GROUP BY cy
  )
SELECT group_concat(rtrim(t),x'0a') FROM a;

which produces

                                    ....#
                                   ..#*..
                                 ..+####+.
                            .......+####....   +
                           ..##+*##########+.++++
                          .+.##################+.
              .............+###################+.+
              ..++..#.....*#####################+.
             ...+#######++#######################.
          ....+*################################.
 #############################################...
          ....+*################################.
             ...+#######++#######################.
              ..++..#.....*#####################+.
              .............+###################+.+
                          .+.##################+.
                           ..##+*##########+.++++
                            .......+####....   +
                                 ..+####+.
                                   ..#*..
                                    ....#
                                    +.

or Sudoku solver. Actually those exampled can be inspected here. Long story short from this release you can create all those common table expressions right with sqlite_orm. E.g. Mandelbrot Set's code in C++ looks like this:

constexpr orm_cte_moniker auto xaxis = "xaxis"_cte;
constexpr orm_cte_moniker auto yaxis = "yaxis"_cte;
constexpr orm_cte_moniker auto m = "m"_cte;
constexpr orm_cte_moniker auto m2 = "m2"_cte;
constexpr orm_cte_moniker auto a = "string"_cte;
constexpr orm_column_alias auto x = "x"_col;
constexpr orm_column_alias auto y = "y"_col;
constexpr orm_column_alias auto iter = "iter"_col;
constexpr orm_column_alias auto cx = "cx"_col;
constexpr orm_column_alias auto cy = "cy"_col;
constexpr orm_column_alias auto t = "t"_col;
auto ast = with_recursive(
    make_tuple(
        xaxis(x).as(union_all(select(-2.0), select(xaxis->*x + 0.05, where(xaxis->*x < 1.2)))),
        yaxis(y).as(union_all(select(-1.0), select(yaxis->*y + 0.10, where(yaxis->*y < 1.0)))),
        m(iter, cx, cy, x, y)
            .as(union_all(select(columns(0, xaxis->*x, yaxis->*y, 0.0, 0.0)),
                          select(columns(m->*iter + 1,
                                         m->*cx,
                                         m->*cy,
                                         m->*x * m->*x - m->*y * m->*y + m->*cx,
                                         2.0 * m->*x * m->*y + m->*cy),
                                 where((m->*x * m->*x + m->*y * m->*y) < 4.0 && m->*iter < 28)))),
        m2(iter, cx, cy).as(select(columns(max<>(m->*iter), m->*cx, m->*cy), group_by(m->*cx, m->*cy))),
        a(t).as(select(group_concat(substr(" .+*#", 1 + min<>(m2->*iter / 7.0, 4.0), 1), ""), group_by(m2->*cy)))),
    select(group_concat(rtrim(a->*t), "\n")));

for C++20 or

using cte_xaxis = decltype(1_ctealias);
using cte_yaxis = decltype(2_ctealias);
using cte_m = decltype(3_ctealias);
using cte_m2 = decltype(4_ctealias);
using cte_a = decltype(5_ctealias);
constexpr auto x = colalias_a{};
constexpr auto y = colalias_b{};
constexpr auto iter = colalias_c{};
constexpr auto cx = colalias_d{};
constexpr auto cy = colalias_e{};
constexpr auto t = colalias_f{};
auto ast = with_recursive(
    make_tuple(
        cte<cte_xaxis>("x").as(
            union_all(select(-2.0 >>= x), select(column<cte_xaxis>(x) + 0.05, where(column<cte_xaxis>(x) < 1.2)))),
        cte<cte_yaxis>("y").as(
            union_all(select(-1.0 >>= y), select(column<cte_yaxis>(y) + 0.10, where(column<cte_yaxis>(y) < 1.0)))),
        cte<cte_m>("iter", "cx", "cy", "x", "y")
            .as(union_all(
                select(columns(0 >>= iter,
                               column<cte_xaxis>(x) >>= cx,
                               column<cte_yaxis>(y) >>= cy,
                               0.0 >>= x,
                               0.0 >>= y)),
                select(columns(column<cte_m>(iter) + 1,
                               column<cte_m>(cx),
                               column<cte_m>(cy),
                               column<cte_m>(x) * column<cte_m>(x) - column<cte_m>(y) * column<cte_m>(y) +
                                   column<cte_m>(cx),
                               2.0 * column<cte_m>(x) * column<cte_m>(y) + column<cte_m>(cy)),
                       where((column<cte_m>(x) * column<cte_m>(x) + column<cte_m>(y) * column<cte_m>(y)) < 4.0 &&
                             column<cte_m>(iter) < 28)))),
        cte<cte_m2>("iter", "cx", "cy")
            .as(select(columns(max<>(column<cte_m>(iter)) >>= iter, column<cte_m>(cx), column<cte_m>(cy)),
                       group_by(column<cte_m>(cx), column<cte_m>(cy)))),
        cte<cte_a>("t").as(
            select(group_concat(substr(" .+*#", 1 + min<>(column<cte_m2>(iter) / 7.0, 4.0), 1), "") >>= t,
                   group_by(column<cte_m2>(cy))))),
    select(group_concat(rtrim(column<cte_a>(t)), "\n")));

for the older C++ versions. And then just run

auto stmt = storage.prepare(ast);
auto results = storage.execute(stmt);
cout << "Apfelmaennchen (Mandelbrot set):\n";
for(const string& rowString: results) {
    cout << rowString << '\n';
}
cout << endl;

In case this example is too complex let's use the very simple example:

WITH RECURSIVE
    cnt(x) AS(VALUES(1) UNION ALL SELECT x + 1 FROM cnt WHERE x < 1000000)
    SELECT x FROM cnt;

which can be represented in C++ as

constexpr orm_cte_moniker auto cnt = "cnt"_cte;
auto ast = with_recursive(
    cnt().as(union_all(select(from), select(cnt->*1_colalias + 1, where(cnt->*1_colalias < end)))),
    select(cnt->*1_colalias));

in C++20 and as

using cnt = decltype(1_ctealias);
auto ast = with_recursive(
    cte<cnt>().as(
        union_all(select(from), select(column<cnt>(1_colalias) + 1, where(column<cnt>(1_colalias) < end)))),
    select(column<cnt>(1_colalias)));

in the older C++ versions.

There are a lot of exampleы of CTE (common table expressions) available in our examples folder here.

Thanks to @trueqbit for such a brilliant feature!

Example:

struct Post {
    std::string title;
    std::string body;
};
auto storage = make_storage(
    "",
    make_virtual_table("posts", 
        using_fts5(make_column("title", &Post::title), make_column("body", &Post::body))));

which is equivalent of

CREATE VIRTUAL TABLE posts
USING FTS5(title, body);

Post class is mapped to virtual table posts . You can operate with it like a regular table just like you do with FTS5 virtual table. New AST nodes:

• match<T>(expression) function which represents MATCH SQLite operator. Can be used like any other function within FTS5 queries
• rank() function which represents RANK keyword. One can write order_by(rank()) to get ORDER BY rank or plain rank() without order_by to get RANK keyword in your query
• is_equal overload for table comparison: is_equal<User>("Tom Gregory") serializes to "users" = 'Tom Gregory'
• highlight<T>(a, b, c) function which represents HIGHLIGHT(table, a, b, c) SQL function
• unindexed() for UNINDEXED column constraint
• prefix(auto) for prefix=x column constraint
• tokenize(auto) for tokenize = x. E.g. tokenize("porter ascii") produces tokenize = 'porter ascii'
• content(auto) for content=x. E.g. content("") produces content=''
• content<T>() for content="table_name_for_T". E.g. content<T>() produces content="users"

More info about FTS5

module_list is a get-only pragma which returns std::vector<std::string>:

auto storage = make_storage(...);
const auto moduleList = storage.pragma.module_list();  // decltype(moduleList) is std::vector<std::string>

More info about module_list

For a long time sqlite_orm did not have null() and not_null() explicit column constraints functions cause nullability has being computed from mapped member pointer field type. E.g. std::optional, std::unique_ptr and std::shared_ptr are treated as nullables by default. And if you create a class with a field of one of these types and call sync_schema for empty database the columns will be created as nullables and you may store SQLite's NULL there using nullptr value in C++. Also you can create your own nullable types using sqlite_orm::type_is_nullable trait specialization. All other types are not nullable by default. E.g. if your column is mapped to a field of std::string then sync_schema will create a column with NOT NULL and you will not be able to insert NULL there even though you want it bad. In some cases it was not very comfortable for development and code maintaining. And now from v1.9 one can specify null() and not_null() column constraints no matter the field type. If you don't specify then old algorithm is used. But if you specify either you'll get respective column constrained specified during sync_schema call.

How it works if I extract NULL inside non-nullable type like int or std::string? Default value will be applied. int will become 0, std::string will become a value built with default constructor and so on. Also you can insert NULL into column with explicit not_null() constraint but mapped to non-nullable field using raw insert (the one which is insert(into<...>) by passing nullptr as value to insert. But you'll get a runtime error just like in raw SQLite. So please be wise.

Sometimes when we use derived classes to map to sqlite_orm storage we have to use column<Derived>(&Base::id) syntax instead of regular &Derived::id to make C++ understand we want Derived type to be identified not Base. Now you can replace column<Derived>(&Base::field) huge syntax with more lightweight and clearer

constexpr auto derived = c<Derived>();
select(derived->*&Derived::id);

Available with C++20 and later.

New functions current_time(), current_date() and current_timestamp() are available which are serialized to CURRENT_TIME, CURRENT_DATE and CURRENT_TIMESTAMP respectively. You can use these functions inside queries and also as storage's member functions:

auto storage = make_storage(...);
const auto time = storage.current_time();
const auto date = storage.current_date();
const auto timestamp = storage.current_timestamp();  // <= this guy existed before tbh

And you can put these functions right into default column constraint function like this:

struct User {
    int id = 0;
    std::string current;
};
auto storage = make_storage({},
                            make_table("users",
                                       make_column("id", &User::id, primary_key()),
                                       make_column("current", &User::current, default_value(current_time()))));  // <= here we go
storage.sync_schema();

The default_value(current_time()) part will be serialized to DEFAULT (CURRENT_TIME).

Also you can query those values inside raw select:

auto rows = storage.select(current_time());

which equals to

SELECT CURRENT_TIME

No need to create a dedicated C++ class. This is how it looks:

// example for a freestanding function from a library
constexpr auto clamp_int_f = "clamp_int"_scalar.quote(std::clamp<int>);
// example for a stateless lambda
constexpr auto is_fatal_error_f = "IS_FATAL_ERROR"_scalar.quote([](unsigned long errcode) {
    return errcode != 0;
});
// example for a function object instance
constexpr auto equal_to_int_f = "equal_to"_scalar.quote(std::equal_to<int>{});
// example for a function object
constexpr auto equal_to_int_2_f = "equal_to"_scalar.quote<std::equal_to<int>>();

storage.create_scalar_function<clamp_int_f>();
storage.create_scalar_function<is_fatal_error_f>();
storage.create_scalar_function<equal_to_int_f>();
storage.create_scalar_function<equal_to_int_2_f>();

auto rows = storage.select(clamp_int_f(0, 1, 1));
auto rows = storage.select(is_fatal_error_f(1));
auto rows = storage.select(equal_to_int_f(1, 1));
auto rows = storage.select(equal_to_int_2_f(1, 1));

However the old API is also supported and is not planned to be deprecated.

There are various types of transactions and also there is a nice API in sqlite_orm made for transactions storage.transaction_guard(). Now this brilliant API became even more brilliant cause it supports various transaction types:

auto guard = storage.deferred_transaction_guard();  // for `BEGIN DEFERRED TRANSACTION` call
auto guard = storage.immediate_transaction_guard();  // for `BEGIN IMMEDIATE TRANSACTION` call
auto guard = storage.exclusive_transaction_guard();  // for `BEGIN EXCLUSIVE TRANSACTION` call

SQLite docs about TRANSACTION

const auto value = storage.pragma.quick_check();
// decltype(value) is std::vector<std::string>

Thanks to @jakemumu

SQLite docs about quick_check pragma

//  to get call
const auto value = storage.pragma.recursive_triggers();
// decltype(value) is bool

//  to set call
storage.pragma.recursive_triggers(true);

SQLite docs about recursive_triggers pragma

• before: column<User>(&User::id) > c(0) now: column<User>(&User::id) > 0
• before: "alias"_col > c(0) now: "alias"_col > 0

auto db = make_storage("");
std::ranges::view auto int_view = db.iterate(select(1));
for (int i : int_view) {
    // ...
}

Usually user-defined functions can be called like this

struct IdFunc {
    int operator()(int arg) { return arg; }
};
select(func<IdFunc>(42));

Now you also co do it like this:

constexpr auto id_func = func<IdFunc>;
select(id_func(42));

Compound operators (union_, union_all, intersect, except) now support variadic amount of arguments.

struct Object {
    int64 id;
    int64 difference;   // diff value in days
};

union_all(
    select(cast<int64>(min(&Object::difference))),
    select(count(), where(c(&Object::difference) < 0)),
    select(count(), where(c(&Object::difference) >= 0 and c(&Object::difference) < 7)),
    select(count(), where(c(&Object::difference) >= 7 and c(&Object::difference) < 15)),
    ...
);

Pointer type tags (as part of the pointer passing interface) can also be created in the same way as table references, table aliases, CTE monikers and column aliases.

In addition, these inline variable "tags" should be used instead of type aliases.

For example, the pointer type "carray" is now defined:

inline constexpr orm_pointer_type auto carray_pointer_tag = "carray"_pointer_type;

instead of:

inline constexpr const char carray_pointer_name[] = "carray";
using carray_pointer_type = std::integral_constant<const char*, carray_pointer_name>;

std::list<int> list{1, 2, 3};
in(&User::id, list);

• https://github.com/fnc12/sqlite_orm/issues/1168
• https://github.com/fnc12/sqlite_orm/issues/1317 (thanks to @lemourin)
• https://github.com/fnc12/sqlite_orm/issues/1187
• https://github.com/fnc12/sqlite_orm/issues/1048
• https://github.com/fnc12/sqlite_orm/issues/1162
• https://github.com/fnc12/sqlite_orm/issues/1270
• https://github.com/fnc12/sqlite_orm/issues/1280
• https://github.com/fnc12/sqlite_orm/issues/1280
• https://github.com/fnc12/sqlite_orm/issues/1301