boost_sqlite 1
A sqlite C++ library
Loading...
Searching...
No Matches
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"};
main object for a connection to a database.

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;
Representation of a row in a database.
Definition row.hpp:21

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

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());
cstring_ref get_text() const
Returns the value as text, i.e. a string_view. Note that this value may be invalidated`.
field at(std::size_t idx) const
Returns the field at idx,.

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.

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");
});
}
#define BOOST_SQLITE_EXTENSION(Name, Conn)
Declare a sqlite module.
Definition extension.hpp:47

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

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 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.

This library takes a different approach, by making everything an iostream interface. iostream interfaces have somewhat fallen out of favor.

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 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.