msqlite

It’s a C++20 SQLite wrapper which has as its main goal an expressive code that runs fast.

sql::open("dev.db")
| sql::prepare("select name, salary from person")
| sql::for_each([](string_view name, float salary)
  { cout << name << "," << salary << endl; })
| sql::onerror([](auto e){ cout << e << endl; });

The code above uses result<T> instead of C++ exceptions and the pipe operator(|) is used to chain operations. The usage of pipes is optional, the programmer can use the essential API using free functions and handling the result of each operation. The usage of result<T> to handling errors is also optional, C++ exceptions can be used through the API that throws exceptions. Example without pipes that uses C++ exceptions:

namespace sql = msqlite::throws;
try {
    auto db = sql::open("dev.db");
    auto stmt = sql::prepare(db, "select name, salary from person");
    sql::for_each(stmt, [](string_view name, float salary)
    { cout << name << "," << salary << endl; });
} catch(const system_error& e)
{ cout << e.code() << endl; }

Cache of prepared statements

Easily using the database connection

The prepared statements can be easily persisted using a database(cache::db) that caches each statement which is compiled. The only thing that should be done is call the function open(filename, cache_stmts) to obtain a cache::db instance which must be stored in a manner that the database instance lives at least until the last evaluation of a statement that was cached. Take a look at demo/cache_stmts/using_db.cpp to see an example.

By hand

The database connection and each prepared statement can be stored and reused through lvalue expressions. This approach is more flexible and can be more efficient too, but it is more work to be done by the developer. It’s flexible because the programmer can choose how to store the prepared statements(stmt) and it can be faster because the statements can be stored without dynamic allocation and runtime searching to obtain the prepared statement from a dynamic cache. Take a look at demo/cache_stmts/by_hand.cpp to see an example.

The essential

The free functions that executes the basic operations using a database connection and prepared statements. Error handling can be done by using result<T> or C++ exceptions to report errors.

db and cache::db

They represent a database connection using RAII to close the connection when the object is destroyed. The model cache::db caches each prepared statement that is compiled through the connection.

stmt

It represents a prepared statement using RAII to finalize the statement when the object is destroyed.

open()

Returns a database connection.

result<db> open(string_view filename) noexcept
result<db> open(string_view filename, cache_stmts) noexcept

Example: auto db = open("dev.db")

Using exceptions

db throws::open(string_view filename)
db throws::open(string_view filename, cache_stmts)

exec()

Executes a statement witout returning any results and optionally binds a sequence of values.

template<typename... Values>
result<void> exec(const db&, string_view stmt, Values&&...) noexcept

template<typename... Values>
result<void> exec(const cache::db&, string_view stmt, Values&&...) noexcept

template<typename... Values>
result<void> exec(stmt&, Values&&...) noexcept

Example: exec(db, "insert into person values(?,?)", "john", 10000.00)

Using exceptions

template<typename... Values>
void exec(const db&, string_view stmt, Values&&...)

template<typename... Values>
void exec(const cache::db&, string_view stmt, Values&&...)

template<typename... Values>
void exec(stmt&, Values&&...)

prepare()

Prepares a statement and optionally binds a sequence of values.

template<typename... Binds>
result<stmt> prepare(const db&, string_view stmt, Binds&&...) noexcept

template<typename... Binds>
result<stmt*> prepare(cache::db&, string_view stmt, Binds&&...) noexcept

Example: prepare(db, "select name, salary from person where name = ?", "john")

Using exceptions

template<typename... Binds>
result<stmt> prepare(const db&, string_view stmt, Binds&&...)

template<typename... Binds>
result<stmt*> prepare(cache::db&, string_view stmt, Binds&&...)

bind()

Binds a sequence of values to evaluates a prepared statement.

template<typename... Values>
void bind(stmt&, Values&&...) noexcept

Example: bind(insert_person, "john")

Using exceptions

ToDo

step()

Evaluates a statement and calls a function to handle the results.

template<typename F, typename T = detail::result_of_F<F> >
result<T> step(stmt&, F&&)

Example: step(select_person, [](string_view name, float salary){ /*do something*/ })

Using exceptions

template<typename F, typename T = detail::result_of_F<F> >
T step(stmt&, F&&)

reset()

Resets a prepared statement.

result<void> reset(stmt&) noexcept

Example: reset(insert_person)

Using exceptions

ToDo

for_each()

Evaluates a select statement and calls a fuction to handle each row that is returned.

template<typename F>
result<void> for_each(stmt&, F&&)

Example: for_each(select_persons, [](string_view name, float salary){ /*do something*/ })

Using exceptions

template<typename F>
void for_each(stmt&, F&&)

Chaining operations with pipes

The operations that were presented at the previous section can be chained through the usage of the pipe operator(|) to avoid boilerplates, this approach avoids intermediary objets to collet results that should be forward to a next operation as an argument, let’s consider the following code using C++ exceptions:

namespace sql = msqlite::throws;
try {
    auto conn = sql::open("dev.db");
    auto stmt = sql::prepare(conn, "select name, salary from person");
    sql::for_each(stmt, [](string_view name, float salary)
    { cout << name << "," << salary << endl; });
} catch(const system_error& e)
{ cout << e.code() << endl; }

Note: There isn’t yet an implementation to use pipes with the API that throws exceptions.

The objects conn and stmt exist only to chain the operations open(), prepare() and for_each() in the left to right order. The usage of pipes eliminates the intermediary objects with a natural code that can be read from the top to the bottom and from the left to the right:

namespace sql = msqlite::throws;
try {
    sql::open("dev.db")
    | sql::prepare("select name, salary from person")
    | sql::for_each([](string_view name, float salary)
      { cout << name << "," << salary << endl; });
} catch(const system_error& e)
{ cout << e.code() << endl; }

However, when using the API that uses result<T> to propagate errors, the usage of pipes can also remove the boilerplate that is necessary to handle a result when it is not desirable to do local error handling. Let’s consider the following:

if(auto db = sql::open("dev.db")) {
    if(auto stmt = sql::prepare(*db, "select name, salary from person")) {
        auto r = sql::for_each(*stmt, [](string_view name, float salary)
            { cout << name << "," << salary << endl; });
        if(!r) cout << r.error() << endl;
    } else cout << stmt.error() << endl;
} else cout << db.error() << endl;

The usage of pipes can remove the conditionals to check and handle errors replacing them with monadic operations like map and mbind in the implementation:

sql::open("dev.db")
| sql::prepare("select name, salary from person")
| sql::for_each([](string_view name, float salary)
  { cout << name << "," << salary << endl; })
| onerror([](auto e){ cout << e << endl; });

How fast is it?

There are some benchmarks at benchmark/ that compares msqlite with the sqlite library. I think that is not always a fair comparison in a sense of look what we have versus what we pay for it.

bench	sqlite	iters	stddev	msqlite	iters	stddev	diff
open()	30431ns	23161	31ns	30677ns	22797	34ns	+0.0081
exec()	36500ns	19330	49ns	36748ns	19311	143ns	+0.0068
for_each()	5036ns	141638	19ns	5130ns	138310	12ns	+0.0185

diff is equal to (msqlite - sqlite) / |sqlite| and iters is the number of iterations to each repetition

The benchmarks were executed at the environment below using the median of 9 repetitions.

GCC 10.1.0 with O3
AMD Ryzen 7 1700X Eight-Core Processor
Run on (16 X 3900 MHz CPU s)
CPU Caches:
  L1 Data 32 KiB (x8)
  L1 Instruction 64 KiB (x8)
  L2 Unified 512 KiB (x8)
  L3 Unified 8192 KiB (x2)

Supported compilers

• GCC 10.1

Dependencies

• tl::expected
• libsqlite3

ToDo

1. Take a look at Boost.Outcome to replace my result<T> and tl::expected<T, E>.
2. Support to use pipe operators with C++ exceptions.