A C++ interface for SQLite

Table of Contents

• History
• Installation
  • Consuming sl3 in a CMake project
• Building sl3 from source
  • Dependency management
  • Building the library (developer mode)
  • Testing
• Usage Overview
  • A first example
• sl3::Database
• Types in libsl3
• sl3::DbValue
  • Using sl3::DbValues
  • 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 the 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 commands, also known as stored procedures.
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 stared as a C++98 project long time ago. Later, when C++11 was new, it became a modern C++11 interface to sqlite. Today, libsl3 it's still around. A C++ interface for SQLite. Active development happens rarely but the library is still maintained and updated if required.

Installation

libsl3 can be build and consumed without any external dependencies. But you might want to use sqlite3 installations from your system.

Consuming sl3 in a CMake project

An easy way is consuming sl3 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

Get you 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 go to 20 soon)
• Required: CMake for building the library
• Optional: SQLite3, sqlite is included in the source code
• Optional: doxygen for buliding the documentation
• Optional: doctest for unit testing

Dependency management

There are 3 options to deal with dependencies.

• 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 build with CMake. No surprises here.

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

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

There is also a xcode preset for MacOS, and a msv22 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

In case you do not have vcpkg installed, the most simple 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 sqlite distribution there are several options to configure sqlite3 available.

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

A 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 directly used, but it has also a virtual destructor and can be used as a base class.

Types in libsl3

The types in libsl3 are those which are available in datatypes as sqlite does.

• sl3::Type::Int stands for a int64_t.
• sl3::Type::Real stands for a doube.
• 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 is wanted this needs to be implement self. The same is true for the special endianness functions sqlite provides.
There are 2 ways to do with writing an own sl3::RowCallback or a sl3::Command::Callback. See RowCallback and Callback functions from more information.
If you think on 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.
A sl3::DbValues can hold different types. It can be defined if any type is allowed for an instance or only a certain one.

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

• sl3::DbValue::dbtype: The type rule
  This property is assigned at creation and can not be change
  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 Type::Variant values of any type can be used, otherwise it has to be the type specified.
  If a sl3::DbValue::isNull 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 omitted by the type property an sl3::ErrTypeMisMatch exception is thrown.

Using sl3::DbValues

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 2 version for each of this function. With and without a default value as argument.
The version without a default value will throw a sl3::ErrNullValueAccess exception is case that sl3::DbValue::isNull is true for the instance.
The version with a default value as argument will return the passed argument in case that the current sl3::DbValue::isNull.

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

• sl3::DbValue::get

Additional, 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 4 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

A sl3::Command is a compiled SQL statement which can contain parameters.
sl3::Database::prepare does create 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.
A insert or update statement does not return data, while a select statement does.
A command has therefor 2 ways to run it.

• sl3::Command::execute, no return data
• sl3::Command::select , does return data.

Parameter types

In the A first example overview example all sl3::Command parameters have been of type sl3::Typ::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 is 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 return by a 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 correct sl3::DbValue object.

#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 unwanted 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 wanted 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 required types

RowCallback and Callback functions

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

sl3::Columns

In a callback the sl3::Columns class give access to the current row.
There are several methods to query the type, size and get the 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