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:

• integer
• floating
• text
• blob
• null

The result of a query is a field type, while a 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 need to be convertible to a fixed set of types (see param_ref for details).

When a value is returned from a custom function, such as done through 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
• scalar function
• aggregate function
• 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 and 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: Complete API documentation.

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. iostream interfaces have somewhat fallen out of favor.

• sqlite_orm

As the name says, it's an ORM. While there is nothing wrong with ORMs, they are one layer of abstraction above a client library like this.

• SOCI

SOCI is an abstraction layer for multiple databases in C++, including sqlite. It's interfaces encourages dynamic building of query string, which should not be considered safe.