A C++ interface for SQLite

Table of Contents

• History
• Installation
  • Bazel support
  • Consuming sl3 in a CMake project
• Building sl3 from source
  • Bazel build
  • CMake build
  • Dependency management
  • Building the library (developer mode)
  • Testing
• Usage Overview
  • A first example
• sl3::Database
• Types in libsl3
• sl3::DbValue
  • Using sl3::DbValue
  • Example
• sl3::Command
  • Parameter types
• sl3::Dataset
  • Create a sl3::Dataset
  • Ensure types for a sl3::Dataset
• RowCallback and Callback functions
  • sl3::Columns
  • Example

libsl3 enables efficient communication with SQLite3 databases using a natural approach: SQL.
The library does not attempt to function as an ORM (Object-Relational Mapping) and does not introduce C++ syntax resembling SQL for interacting with a SQLite database.
Reading from and writing to a database is accomplished through SQL and prepared statements.
The library fully supports the SQLite duck typing concept, but it also offers a means to incorporate type checking when communicating with a database.

History

libsl3 started as a C++98 project a long time ago. Later, when C++11 was new, it became a modern C++11 interface to SQLite. Today, libsl3 is still around: a C++ interface for SQLite. Active development happens rarely, but the library is still maintained and updated when required.

Installation

Bazel support

Since version 1.2.50004 (tested with SQLite3 3.50.4), the Bazel build system is supported.

This should make it easy to integrate libsl3 into a Bazel project.

Consuming sl3 in a CMake project

For CMake usage, there are various ways to handle dependencies and the build.

With CMake, libsl3 can be built and consumed without any external dependencies, but you might want to use the sqlite3 installation from your system.

One way to consume sl3 is via CMake's FetchContent.

cmake_minimum_required(VERSION 3.29)
 
project(consume_sl3)
 
set(CMAKE_CXX_STANDARD_REQUIRED ON)
set(CMAKE_CXX_STANDARD 20)
 
include(FetchContent)
FetchContent_Declare(
  sl3
  GIT_REPOSITORY https://github.com/a4z/libsl3.git
  GIT_TAG        main
  OVERRIDE_FIND_PACKAGE
)
SET(BUILD_TESTING OFF)
SET(sl3_USE_INTERNAL_SQLITE3 ON)
SET(sl3_USE_COMMON_COMPILER_WARNINGS OFF)
 
find_package(sl3 CONFIG REQUIRED)
 
add_executable(consume_sl3 main.cpp)
target_link_libraries(consume_sl3 PRIVATE sl3)

If you want to use the system sqlite3 installation, you can omit the line SET(sl3_USE_INTERNAL_SQLITE3 ON)

Building sl3 from source

Bazel build

bazel build ...
bazel test ...

Bazel will care about dependencies and build and test the package.

CMake build

Get your copy of the source code from the libsl3 GitHub repository.

The project has the following dependencies:

• The minimum required C++ standard is C++17 (planned to move to C++20 soon)
• Required: CMake for building the library
• Optional: SQLite3; sqlite3 is included in the source code
• Optional: doxygen for building the documentation
• Optional: doctest for unit testing

Dependency management

There are 3 options for dependency management.

• Ignore them and turn them off (no testing and internal sqlite3)
• Use vcpkg, a package manager for C++ libraries
• Use CMake's FetchContent to fetch the dependencies

If doxygen is not found, it will not be possible to build the documentation.

See below for how to use either vcpkg or FetchContent.

Building the library (developer mode)

The library can be built with CMake. No surprises here.

If you use vcpkg, the easiest way to build the library is:

cmake --preset ninja -DTOOLCHAIN_INCLUDES=toolchain/use-vcpkg
cmake --build --preset ninja
ctest --preset ninja

There is also an Xcode preset for macOS, and an MSVC22 preset for Windows.

macOS:

cmake --preset xcode -DTOOLCHAIN_INCLUDES=toolchain/use-vcpkg
cmake --build --preset xcode
ctest --preset xcode

Windows:

cmake --preset msvc22 -DTOOLCHAIN_INCLUDES=toolchain/use-vcpkg
cmake --build --preset msvc22
ctest --preset msvc22

If you do not have vcpkg installed, the simplest way to get started is

cmake --preset ninja -DPROJECT_ADDONS=add-on/fetch-dependencies -Dsl3_USE_INTERNAL_SQLITE3=ON

If you have sqlite3 installed on your system, you can use it by omitting -Dsl3_USE_INTERNAL_SQLITE3=ON.

CMake options to control the build process:

• sl3_USE_INTERNAL_SQLITE3:
  If set to ON, the internal sqlite3 source code will be used.
  If set to OFF, the system sqlite3 will be used.
  Default is ON.

• sl3_BUILD_TESTING:
  If set to ON, test targets will be added.
  Default is ON.

For the internal sqlite3 distribution, there are several options to configure sqlite3.

Advantages of using the internal sqlite3

• No external dependencies.
• sqlite3 is used in the tested version.
• Consuming libsl3 becomes easier.

Programs consuming libsl3 do not need to link against sqlite3.

Disadvantages of using the internal sqlite3

• A bigger library.
• Slightly longer build time for building libsl3. (not for programs consuming libsl3)

Testing

All the tests can be found in the tests subdirectory.
Existing tests try to cover as much as possible, see the coverage report for details.

Usage Overview

A database is represented via sl3::Database.
A database can execute SQL strings to read or write data.

An sl3::Command is a compiled SQL statement which can contain parameters.
Parameters are sl3::DbValues, a sl3::Container of sl3::DbValue instances.

sl3::DbValues can also be used to receive data from a database.
Requesting data from a database will return a sl3::Dataset with rows of sl3::DbValues where each field is a sl3::DbValue.

Another and fully customizable way to receive data from a database is covered in the RowCallback and Callback functions section.

A first example

#include <cassert>
#include <iostream>
#include <sl3/database.hpp>
 
int
main ()
{
  using namespace sl3;
  // define a db
  Database db (":memory:");
  // run commands against the db
  db.execute ("CREATE TABLE tbl(f1 INTEGER, f2 TEXT, f3 REAL);");
  // create a command with parameters
  auto cmd = db.prepare ("INSERT INTO tbl (f1, f2, f3) VALUES (?,?,?);");
 
  // add some data
  cmd.execute (parameters (1, "one", 1.1));
  cmd.execute (parameters (2, "two", 2.1));
 
  // access the data
  Dataset ds = db.select ("SELECT * FROM tbl;");
 
  // Dataset is a container
  assert (ds.size () == 2);
 
  // Dataset row is a container
  auto row = ds[0];
  assert (row.size () == 3);
  assert (row[0].type () == Type::Int);
  assert (row[1].type () == Type::Text);
  assert (row[2].type () == Type::Real);
 
  // of course there is also iterator access
  for (const auto& r : ds)
    {
      for (const auto& field : r)
        {
          std::cout << field << " ";
        }
      std::cout << std::endl;
    }
}

Since the ostream operator for sl3::DbValue is overloaded, this will print

1 one 1.1
2 two 2.1

sl3::Database

sl3::Database encapsulates a SQLite database.
It can be used directly, but it also has a virtual destructor and can be used as a base class.

Types in libsl3

The types in libsl3 are those available in the datatypes SQLite provides.

• sl3::Type::Int stands for an int64_t.
• sl3::Type::Real stands for a double.
• sl3::Type::Text stands for a std::string.
• sl3::Type::Blob stands for a sl3::Blob, a char vector

Since a database value can also be NULL, there is a type for it.

• sl3::Type::Null, a null value.

There is one additional sl3::Type.
In SQLite, a column can hold different types, and libsl3 supports this concept.

• sl3::Type::Variant, any value type.

See sl3::DbValue for more information.

Note

Text is a UTF-8 string since this is a good default and keeps things simple.
If UTF-16 string types are needed, this must be implemented separately. The same is true for the special endianness functions SQLite provides.
There are two ways to do this: write your own sl3::RowCallback or sl3::Command::Callback. See RowCallback and Callback functions for more information.
If you think one of these features should be supported by the library, please file a feature request in the issue tracker.

sl3::DbValue

This class can be used to read from and write to a sl3::Database.
An sl3::DbValue can hold different types. You can define whether any type is allowed for an instance or only a specific one.

There are two sl3::Type properties. One defines the type rule, and the other defines the type value.

• sl3::DbValue::dbtype: The type rule
  This property is assigned at creation and cannot be changed.
  If the type is sl3::Type::Variant the instance can hold any type, otherwise only the specified type is allowed, one of:
  sl3::Type::Int, sl3::Type::Real, sl3::Type::Text, sl3::Type::Blob
  
  
• sl3::DbValue::getStorageType: The current value type
  This property depends on the actual value
  If the type is sl3::Type::Variant values of any type can be used, otherwise it has to be the type specified.
  If sl3::DbValue::isNull returns true, the storage type is also sl3::Type::Null.

Both properties are used to validate reads and writes at runtime.
If a value is set or read that is not allowed by the type property, an sl3::ErrTypeMisMatch exception is thrown.

Using sl3::DbValue

Setting a value can be done via

• sl3::DbValue::set or the assignment operator.
  If an invalid type is set, a sl3::ErrTypeMisMatch will be thrown.

sl3::DbValue::setNull can be used to set a value to Null.

There are getters for each type:

• sl3::DbValue::getInt
• sl3::DbValue::getReal
• sl3::DbValue::getText
• sl3::DbValue::getBlob

There are two versions of each of these functions. With and without a default value as argument.
The version without a default value will throw a sl3::ErrNullValueAccess exception if sl3::DbValue::isNull is true for the instance.
The version with a default value as argument will return the passed argument if the current sl3::DbValue::isNull is true.

If a type getter is used for the wrong storage type, a sl3::ErrTypeMisMatch exception is thrown.

• sl3::DbValue::get

Additionally, there is a sl3::DbValue::get version which will never throw.
This function always requires a default value which has to be one of the four value types.

If the type is incorrect or the value is Null, the given default value will be returned.

Example

#include <cassert>
#include <iostream>
#include <sl3/database.hpp>
int
main ()
{
  using namespace sl3;
  DbValue val (Type::Variant);
  assert (val.isNull ());
  assert (val.get ("foo") == "foo"); // get with default never throws
  val = 2;
  std::cout << val << std::endl; // access the integer property
  try
    { // not possible since this variant holds an integer value
      val.getText ();
    }
  catch (const Error& e)
    {
      std::cout << e << std::endl;
    }
  assert (val.get ("foo") == "foo"); // get with default never throws
 
  val = "example";
  // now it is possible to access the text property
  std::cout << val.getText () << std::endl;
}

This example will print

2
sl3::TypeMisMatch:Int!=Text
example

sl3::Command

An sl3::Command is a compiled SQL statement which can contain parameters.
sl3::Database::prepare creates a command.
If a command has parameters the types are specified at creation.

A command can return data or not, depending on the SQL that is used.
An insert or update statement does not return data, while a select statement does.
A command therefore has 2 ways to run it.

• sl3::Command::execute, returns no data
• sl3::Command::select returns data.

Parameter types

In the A first example overview example, all sl3::Command parameters were of type sl3::Type::Variant since nothing was specified.
But it can be ensured that certain types are used.

#include <cassert>
#include <iostream>
 
#include <sl3/database.hpp>
 
int
main ()
{
  using namespace sl3;
  // define a db
  Database db (":memory:");
  // run commands against the db
  db.execute ("CREATE TABLE tbl(f1 INTEGER, f2 TEXT, f3 REAL);");
  // create a command with parameters
  auto cmd = db.prepare ("INSERT INTO tbl (f1, f2, f3) VALUES (?,?,?);",
                         DbValues ({Type::Int, Type::Text, Type::Real}));
 
  // this will work,
  cmd.execute (parameters (1, "one", 1.1));
 
  // this will throw since "2" is a wrong type
  try
    {
      cmd.execute (parameters ("2", "two", 2.2));
    }
  catch (const Error& e)
    {
      std::cout << e << std::endl;
    }
}

The output of this program will be:

sl3::TypeMisMatch:Int=Text

It is not a problem to insert different types into a column; SQLite supports this, and so does libsl3.
But it might be unwanted and can therefore be turned off.

sl3::Dataset

A sl3::Dataset is a generic way to receive data from a sl3::Database.

It is returned by sl3::Command::select or sl3::Database::select.

Create a sl3::Dataset

If there are different types in a column, a sl3::Dataset will automatically provide the correct sl3::DbValue objects.

#include <cassert>
#include <iostream>
 
#include <sl3/database.hpp>
 
int
main ()
{
  using namespace sl3;
  // define a db
  Database db (":memory:");
  // run commands against the db
  db.execute ("CREATE TABLE tbl(f1 INTEGER, f2 TEXT, f3 REAL);");
  // create a command with parameters
  auto cmd = db.prepare ("INSERT INTO tbl (f1, f2, f3) VALUES (?,?,?);");
  // no types so we use variants
  // so this will work
  cmd.execute (parameters (1, "one", 1.1));
  // and this will also work
  cmd.execute (parameters ("some text", "two", 2.1));
 
  // access the data
  Dataset ds = db.select ("SELECT * FROM tbl;");
 
  assert (ds.size () == 2);    // 2 records
  assert (ds[0].size () == 3); // 3 fields
 
  // first row first field is a integer, as inserted
  assert (ds[0][0].dbtype () == Type::Variant);
  assert (ds[0][0].type () == Type::Int);
  // second row first field is text, as inserted
  assert (ds[1][0].dbtype () == Type::Variant);
  assert (ds[1][0].type () == Type::Text);
 
  // of course we can work wit the values
  for (const auto& row : ds)
    {
      for (const auto& field : row)
        {
          std::cout << typeName (field.dbtype ()) << "/"
                    << typeName (field.type ()) << ": " << field << ", ";
        }
      std::cout << std::endl;
    }
}

Running this program will print

Variant/Int: 1, Variant/Text: one, Variant/Real: 1.1,
Variant/Text: some text, Variant/Text: two, Variant/Real: 2.1,

Ensure types for a sl3::Dataset

It might be undesirable to get different types for one column into a sl3::Dataset.
This can be ensured by passing the allowed sl3::Types to the sl3::Database::select call.
If the requested types are not those in the table, an sl3::TypeMisMatch exception is thrown.

#include <iostream>
#include <sl3/database.hpp>
 
int
main ()
{
  using namespace sl3;
  // define a db
  Database db (":memory:");
  // run commands against the db
  db.execute ("CREATE TABLE tbl(f1 INTEGER, f2 TEXT, f3 REAL);");
  // create a command with parameters
  auto cmd = db.prepare ("INSERT INTO tbl (f1, f2, f3) VALUES (?,?,?);");
  // no types so we use variants
  // insert Int, Text, Real
  cmd.execute (parameters (1, "one", 1.1));
  // insert Text, Text, Real
  cmd.execute (parameters ("some text", "two", 2.1));
 
  // this will throw since types in column0  are different
  try
    {
      Dataset ds = db.select ("SELECT * FROM tbl;",
                              {Type::Int, Type::Text, Type::Real});
    }
  catch (const Error& e)
    {
      std::cout << e << std::endl;
    }
}

This code will throw an exception, as expected, and print

sl3::TypeMisMatch:Int not one of the required types

RowCallback and Callback functions

A custom way to handle query results is to use an sl3::RowCallback or the sl3::Command::Callback function.
They can be passed to sl3::Database::execute and sl3::Command::execute functions.
The callback will be called with an sl3::Columns representing the current row for each row in the query result.
The callback returns whether it should be called again.

sl3::Columns

In a callback, the sl3::Columns class gives access to the current row.
There are several methods to query the type, size, and values.

Example

#include <cassert>
#include <iostream>
#include <sl3/columns.hpp>
#include <sl3/database.hpp>
int
main ()
{
  using namespace sl3;
  // define a db
  Database db (":memory:");
  // run commands against the db
 
  db.execute ("CREATE TABLE tbl(f1 INTEGER, f2 TEXT, f3 REAL);"
              "INSERT INTO tbl (f1, f2, f3) VALUES (1, 'one', 1.1);"
              "INSERT INTO tbl (f1, f2, f3) VALUES (2, 'two', 2.2)");
 
  db.execute ("SELECT f1, f2  FROM tbl;", [] (Columns cols) {
    assert (cols.count () == 2);
    assert (cols.getType (0) == Type::Int);
    assert (cols.getType (1) == Type::Text);
    std::cout << std::to_string (cols.getInt (0)) << "_" << cols.getText (1)
              << std::endl;
    return true;
  });
}

Running this program, the output will be

1_one
2_two