boost_sqlite

This library provides a simple C++ sqlite library.

It includes:

• typed queries
• prepared statements
• json support
• custom functions (scalar, aggregrate, windows)
• event hooks
• virtual tables

sqlite provides an excellent C-API, so this library does not attempt to hide, but to augment it.

Building the library

You can either build the library and link against boost_sqlite for embedding it, or boost_sqlite_ext for extensions.

If you want to use it for extensions you'll need to define BOOST_SQLITE_COMPILE_EXTENSION or include boost/sqlite/extensions.hpp first.

Quickstart

First we open a database. Note that this can be ":memory:" for an in-memory database.

boost::sqlite::connection conn{"./my-database.db"};

Next we're creating tables using boost::sqlite::connection::execute, because it can execute multiple statements in one command:

  conn.execute(R"(
create table if not exists author (
    id         integer primary key autoincrement,
    first_name text,
    last_name  text
);

create table if not exists library(
    id      integer primary key autoincrement,
    name    text unique,
    author  integer references author(id)
);
)"
);

Next, we'll use a prepared statement to insert multiple values by index:

conn.prepare("insert into author (first_name, last_name) values (?1, ?2), (?3, ?4), (?5, ?6), (?7, ?8)")
    .execute({"vinnie", "falco", "richard", "hodges", "ruben", "perez", "peter", "dimov"});

Prepared statements can also be used multiple time and used with named parameters instead of indexed.

{
  conn.query("begin transaction;");

  auto st = conn.prepare("insert into library (\"name\", author) values ($library, "
                         "  (select id from author where first_name = $fname and last_name = $lname))");

  st.execute({{"library", "beast"},    {"fname", "vinnie"}, {"lname", "falco"}});
  st.execute({{"library", "mysql"},    {"fname", "ruben"},  {"lname", "perez"}});
  st.execute({{"library", "mp11"},     {"fname", "peter"},  {"lname", "dimov"}});
  st.execute({{"library", "variant2"}, {"fname", "peter"},  {"lname", "dimov"}});

  conn.query("commit;");
}

Now that we have the values in the table, let's add a custom aggregate function to create a comma separated list:

struct collect_libs
{
  void step(std::string & name, span<sqlite::value, 1> args)
  {
    if (name.empty())
      name = args[0].get_text();
    else
      (name += ", ") += args[0].get_text();
  }
  std::string final(std::string & name) { return name; }
};
sqlite::create_aggregate_function(conn, "collect_libs", collect_libs{}); 

Print out the query with aggregates libraries:

for (boost::sqlite::row r : conn.query(
    "select first_name, collect_libs(name) "
       " from author inner join library l on author.id = l.author group by last_name"))
  std::cout << r.at(0u).get_text() << " authored " << r.at(1u).get_text() << std::endl;

Alternatively a query result can also be read manually instead of using a loop:

boost::sqlite::row r;
boost::sqlite::query q = conn.query(
    "select first_name, collect_libs(name) " 
       " from author inner join library l on author.id = l.author group by last_name")
do 
{
  auto r = q.current();''
  std::cout << r.at(0u).get_text() << " authored " << r.at(1u).get_text() << std::endl;
}
while (q.read_next());

Fields, values & parameters

sqlite3 has a weak typesystem, where everything is one of following [value_types](@ref boost::sqlite::value_type):

• integer
• floating
• text
• blob
• null

The result of a query is a [field](@ref boost::sqlite::field) type, while a [value](@ref boost::sqlite::value) is used in functions.

Fields & values can have subtypes, while parameter to prepared statements do not have thos associated.

Because of this the values that can be bound to an [execute](@ref boost::sqlite::statement::execute) need to be convertible to a fixed set of types (see [param_ref](@ref boost::sqlite::param_ref) for details).

When a [value](@ref boost::sqlite::value) is returned from a custom function, such as done through [create_scalar_function](@ref boost::sqlite::create_scalar_function), additional types can be added with the following tag_invoke function:

void tag_invoke(const struct set_result_tag &, sqlite3_context * ctx, const my_type & value);

An implementation can look like this:

void tag_invoke(const struct set_result_tag &, sqlite3_context * ctx, const my_type & value)
{
  auto data = value.to_string();
  sqlite3_result_text(ctx, data.c_str(), data.size(), sqlite3_free);
  sqlite3_result_subtype(ctx, my_subtype);
}

Typed queries

Queries can be typed through tuples, describe or, if you're on C++20, by plain structs. The type to hold them is static_resultset<T> which will check if the columns match the result types before usage. Tuples are matched by position, structs by name.

for (auto q : conn.query<std::tuple<std::string, std::string>>(
    "select first_name, collect_libs(name) "
       " from author inner join library l on author.id = l.author group by last_name"))
  std::cout << std::get<0>(q) << " authored " << std::get<0>(q) << std::endl;

struct query_result { std::string first_name, lib_name;};
BOOST_DESCRIBE_STRUCT(query_result, (), (first_name, lib_name)); // this can be omitted with C++20. 

for (auto q : conn.query<query_result>(
    "select first_name, collect_libs(name) as lib_name"
       " from author inner join library l on author.id = l.author group by last_name"))
  std::cout << q.first_name << " authored " << q.lib_name << std::endl;

The following types are allowed in a static query result:

• sqlite::value
• int
• sqlite_int64
• double
• std::string
• sqlite::string_view
• sqlite::blob
• sqlite::blob_view

You'll need to include boost/sqlite/static_resultset.hpp for this to work.

Custom functions

Since sqlite is running in the same process you can add custom functions that can be used from within sqlite.

• [collation](@ref boost::sqlite::create_collation)
• [scalar function](@ref boost::sqlite::create_scalar_function)
• [aggregate function](@ref boost::sqlite::create_aggregate_function)
• [window function](@ref boost::sqlite::create_window_function)

Vtables

This library also simplifies adding virtual tables significantly; virtual tables are table that are backed by code instead of data.

See [create_module](@ref boost::sqlite::create_module) and [prototype](@ref boost::sqlite::vtab_module_prototype) for more details.

Modules

This library can also be used to build a sql plugin:

BOOST_SQLITE_EXTENSION(testlibrary, conn)
{
  // create a function that can be used in the plugin
  create_scalar_function(
    conn, "assert",
    [](boost::sqlite::context<>, boost::span<boost::sqlite::value, 1u> sp)
    {
        if (sp.front().get_int() == 0)
          throw std::logic_error("assertion failed");
    });
}

The plugin can then be loaded & used like this:

SELECT load_extension('./test_library');

select assert((3 * 4) = 12);

To build a plugin you need to define BOOST_SQLITE_COMPILE_EXTENSION (e.g. by including boost/sqlite/extension.hpp or linking against boost_sqlite_ext).

This will include the matching sqlite header (sqlite3ext.h) and will move all the symbols into an inline namespace ext inside boost::sqlite.

Reference

• Reference: Covers the topics discussed in this document.

Library Comparisons

While there are many sqlite wrappers out there, most haven't been updated in the last five years - while sqlite has.

Here are some actively maintained ones:

• SQLiteCpp

SQLiteCpp is the closest to this library, a C++11 wrapper only depending on sqlite & the STL. It's great and served as an inspiration for this library. boost.sqlite does provide more functionality when it comes to hooks, custom functions & virtual tables. Furthermore, boost.sqlite has a non-throwing interface and supports variants & json, as those are available through boost.

• sqlite_modern_cpp

This library takes a different approach, by making everything an iostream interface.

• sqlite_orm

As the name says, it's an ORM. So it does more than give easy access to a database.