Contents

Introduction

SQlite3 is a super handy database format for prototyping and certainly for deployment on embedded device and smartphones/tablets. There a no quirks with respect to automatic setup and long dependencies as it would be the case when trying to run PostgreSQL or MariaDB on e.g an Android device. Furthermore, SQLite3 even provides some very simple simple concurrency features (see SQLite3 Concurrency example and benchmark). However, using the SQLite C API in a C++ project requires programming a significant amount of boilerplate and requires a lot of “C thinking” which can cause some slow-downs, especially in the field of data science and machine learning as most people working in this field might be familiar with easy SQLite usage as with Python.

C++ Wrappers for SQLite3

There are a couple of modern C++ wrappers for SQLite3 available. The most prominent are:

I made a Debian/Ubuntu build script for SQLiteCpp available on github. If ArchLinux or Manjaro is used, there exists a build script in the AUR.

Examples

Let’s have a look at QtSql.

QtSql

NB!: According to Qt’s documentation a QCoreApplication object needs to be initialized before using QtSql as it requires Qt’s event loop to be available. However, it seems to work without initializing a QCoreApplication as well (not tested properbly but in the example blow it works).

In general, there are two ways to use the QtSql wrapper:

  • write a class and use class functions to trigger sql actions
  • use it within any Qt loop/object or main function

It is important to point out that there exists something that is called qt_sql_default_connection. Let’s assume we have two databases we want to use. The correct way to use them is specify each database with a different connection name. Furthermore, we have to specifcy the connection type - in our case "QSQLITE":

QSqlDatabase db_0 = QSqlDatabase::addDatabase("QSQLITE", "DB_0");
QSqlDatabase db_1 = QSqlDatabase::addDatabase("QSQLITE", "DB_1");

if we don’t specify a connection name, then Qt would set a default connection name which results in the following error(s):

QSqlDatabase db_0 = QSqlDatabase::addDatabase("QSQLITE");
db_0.setDatabaseName(db_0_filepath);
if (!db_0.open()) {
    qDebug() << "Opening DB 0 failed - terminating";
} else {
    qDebug() << "Opening DB 0 opened";
}

QSqlDatabase db_1 = QSqlDatabase::addDatabase("QSQLITE");
db_1.setDatabaseName(db_1_filepath);
if (!db_1.open()) {
    qDebug() << "Opening DB 1 failed - terminating";
} else {
    qDebug() << "Opening DB 1 opened";
}

Opening DB 0 opened
QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.
QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.
Opening DB 1 opened

Therefore, do not use any default connection. It makes debugging more challenging - especially if someone reading the source is not really familiar with all of Qt’s internals.

If we would perform any query, even if the query is initialized as QSqlQuery query_0(db_0);, it will execute the command in db_1 and not db_0 because there seems to be some fallback mechanism to the “default connection name”.

Qt provides a class to handle queries and more importantly to bind values to queries. It is called QSqlQuery. SQlQuery can be used to execute any SQL command. Calling .exec() returns whether or not command was executed successful. If we run a SELECT command, then we’ll receive the the data and have to iterate over the query using next(). Then the values with known positions are converted to the correct data format/type.

QString select_data(
    "SELECT ID,NAME,SOME_BIN,SOME_REAL "
    "FROM test_table");
query_0.clear();
query_0.prepare(select_data);
if (!query_0.exec()) {
    qDebug() << query_0.lastError();
    db_0.rollback();
} else {
    while (query_0.next()) {
        qDebug() << "ID: " << query_0.value(0).toInt();
        qDebug() << "NAME: " << query_0.value(1).toString();
        qDebug() << "SOME_BIN: " << query_0.value(2).toByteArray();
        qDebug() << "SOME_REAL: " << query_0.value(3).toFloat();
    }
}

Here is the full source code of the Qt example:

#include <cstdlib>
#include <filesystem>
#include <iostream>
#include <string>

#include <QtGlobal>
#include <QDebug>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>
#include <QSql>
#include <QString>


int main()
{
    const char* home = getenv("HOME");
    if (home == nullptr) {
        qDebug() << "home direction not found - terminating";
        return 1;
    }
    QString home_dir(home);
    QString db_0_filepath = home_dir + "/db_0.sqlite3";
    QString db_1_filepath = home_dir + "/db_1.sqlite3";

    if (std::filesystem::exists(db_0_filepath.toStdString())) {
        std::filesystem::remove(db_0_filepath.toStdString());
    }
    if (std::filesystem::exists(db_1_filepath.toStdString())) {
        std::filesystem::remove(db_1_filepath.toStdString());
    }

    QSqlDatabase db_0 = QSqlDatabase::addDatabase("QSQLITE", "DB_0");
    db_0.setDatabaseName(db_0_filepath);
    if (!db_0.open()) {
        qDebug() << "Opening DB 0 failed - terminating";
    } else {
        qDebug() << "Opening DB 0 opened";
    }

    QSqlDatabase db_1 = QSqlDatabase::addDatabase("QSQLITE", "DB_1");
    db_1.setDatabaseName(db_1_filepath);
    if (!db_1.open()) {
        qDebug() << "Opening DB 1 failed - terminating";
    } else {
        qDebug() << "Opening DB 1 opened";
    }

    // create table in databases
    QString table_gen(
        "CREATE TABLE test_table("
            "ID        INTEGER    PRIMARY KEY AUTOINCREMENT,"
            "NAME      TEXT       NOT NULL,"
            "SOME_BIN  BINARY     NOT NULL,"
            "SOME_REAL REAL       NOT NULL,"
            "TS        TIMESTAMP  DEFAULT CURRENT_TIMESTAMP);");

    QSqlQuery query_0(db_0);
    query_0.prepare(table_gen);
    if (!query_0.exec()) {
        qDebug() << query_0.lastError();
        db_0.rollback();
    } else {
        qDebug() << "Query exec successful on DB 0";
        db_0.commit();
    }

    QSqlQuery query_1(db_1);
    query_1.prepare(table_gen);
    if (!query_1.exec()) {
        qDebug() << query_1.lastError();
        db_1.rollback();
    } else {
        qDebug() << "Query exec successful on DB 1";
        db_1.commit();
    }

    const QByteArray binary_array_0 = QByteArray::fromHex("FEFF00FFA8");
    const double some_number_0 = 3.14;
    const QString some_name_0("Some name");
    const QByteArray binary_array_1 = QByteArray::fromHex("AE0000FFA8");
    const double some_number_1 = 42.01;
    const QString some_name_1("Some other name");

    QString insert_data(
        "INSERT INTO test_table (NAME, SOME_BIN, SOME_REAL) "
        "VALUES (:name, :somebin, :somereal);");

    query_0.clear();
    query_0.prepare(insert_data);
    query_0.bindValue(":name", some_name_0);
    query_0.bindValue(":somebin", binary_array_0);
    query_0.bindValue(":somereal", some_number_0);
    if (!query_0.exec()) {
        qDebug() << query_0.lastError();
        db_0.rollback();
    } else {
        qDebug() << "Query exec successful on DB 0";
        db_0.commit();
    }

    query_1.clear();
    query_1.prepare(insert_data);
    query_1.bindValue(":name", some_name_1);
    query_1.bindValue(":somebin", binary_array_1);
    query_1.bindValue(":somereal", some_number_1);
    if (!query_1.exec()) {
        qDebug() << query_1.lastError();
        db_1.rollback();
    } else {
        qDebug() << "Query exec successful on DB 1";
        db_1.commit();
    }

    QString select_data(
        "SELECT ID,NAME,SOME_BIN,SOME_REAL "
        "FROM test_table");
    query_0.clear();
    query_0.prepare(select_data);
    if (!query_0.exec()) {
        qDebug() << query_0.lastError();
        db_0.rollback();
    } else {
        while (query_0.next()) {
            qDebug() << "ID: " << query_0.value(0).toInt();
            qDebug() << "NAME: " << query_0.value(1).toString();
            qDebug() << "SOME_BIN: " << query_0.value(2).toByteArray();
            qDebug() << "SOME_REAL: " << query_0.value(3).toFloat();
        }
    }

    db_0.close();
    db_1.close();

    return 0;
}

NB!: If float is used instead of double, then there are some rounding errors which change the number inserted into the SQLite3 database.