Thin C++ wrapper for sqlite3 C library

Question

I originally wrote a rough C++ wrapper of the sqlite3 c library which did not use prepared statements. This code is my attempt at using prepared statements. I have attempted to make the wrapper as generic as possible so can be used in a wide range of applications.

How easy to use do you think this C++ class is? Any bugs? How could this be improved?

Here is the header, sqlite.hpp:

// sqlite - a thin c++ wrapper of sqlite c library   
#ifndef SQLITE_HPP_
#define SQLITE_HPP_

// uncomment below to print blob data as hex in ostream overload of column_values
// #define PRINT_BLOB_AS_HEX

#include "sqlite3.h"

#include <string>
#include <vector>
#include <variant>
#include <cstdint>
#include <iostream>
#include <map>

namespace sql {

    /*
    sqlite types can be: NULL, INTEGER, REAL, TEXT, BLOB
    NULL: we don't support this type
    INTEGER: int
    REAL: double
    TEXT: std::string
    BLOB: std::vector<uint8_t>
    */
    using sqlite_data_type = std::variant<int, double, std::string, std::vector<uint8_t> >;

    struct column_values {
        std::string column_name;
        sqlite_data_type column_value;
    };

    struct where_binding {
        std::string column_name;
        sqlite_data_type column_value;
    };

    std::ostream& operator<< (std::ostream& os, const column_values& v);
    std::ostream& operator<< (std::ostream& os, const sqlite_data_type& v);

    std::ostream& operator<< (std::ostream& os, const std::map<std::string, sqlite_data_type>& v);


    class sqlite {
    public:
        /* sqlite constructor */
        sqlite();

        /* cleanup */
        ~sqlite();

        /* database must be opened before calling an sql operation */
        int open(const std::string& filename);

        /* close database connection */
        int close();

        /* INSERT INTO (col1, col2) VALUES (:col1, :col2); table_name is table to insert into
        fields are a list of column name to column value key value pairs */
        int insert_into(const std::string& table_name, const std::vector<column_values>& fields);

        /* returns rowid of last successfully inserted row. If no rows
        inserted since this database connectioned opened, returns zero. */
        int last_insert_rowid();

        /* UPDATE contacts SET col1 = value1, col2 = value2, ... WHERE rowid = therowid;
        table_name is table to update, 
        fields are a list of column name to column value key value pairs to update
        where_clause is WHERE clause predicate expressed as : parameterised query
        bindings is the binding of values in the where clause */
        int update(
            const std::string& table_name, 
            const std::vector<column_values>& fields, 
            const std::string& where_clause, 
            const std::vector<where_binding>& bindings);

        /* UPDATE contacts SET col1 = value1, col2 = value2, ...;
        same as update(table_name, fields, where) except no WHERE clause so potential to change EVERY row. USE WITH CAUTION. */
        int update(const std::string& table_name, const std::vector<column_values>& fields);

        /* DELETE FROM table_name WHERE condition; */
        int delete_from(const std::string& table_name, const std::string& where_clause, const std::vector<where_binding>& bindings);

        /* DELETE FROM table_name;
        same as delete_from(table_name, where) except no WHERE clause so potential to delete EVERY row. USE WITH CAUTION. */
        int delete_from(const std::string& table_name);

        /* SELECT * FROM table_name WHERE col1 = x;
        table_name is table to select,
        where_clause is WHERE clause predicate expressed as : parameterised query
        bindings is the binding of values in the where clause
        results is a table of values */
        int select_star(const std::string& table_name,
            const std::string& where_clause,
            const std::vector<where_binding>& bindings,
            std::vector<std::map<std::string, sqlite_data_type>>& results);
        /* SELECT * FROM table_name;
        table_name is table to select,
        results is a table of values */
        int select_star(const std::string& table_name,
            std::vector<std::map<std::string, sqlite_data_type>>& results);

        /* SELECT col1, col2 FROM table_name WHERE col1 = x;
        table_name is table to select,
        fields are list of fields in table to select
        where is list of key value pairs as criterion for select
        results is a table of values */
        int select_columns(const std::string& table_name,
            const std::vector<std::string>& fields,
            const std::string& where_clause,
            const std::vector<where_binding>& bindings,
            std::vector<std::map<std::string, sqlite_data_type>>& results);

        /* get error text relating to last sqlite error. Call this function
        whenever an operation returns a sqlite error code */
        const std::string get_last_error_description();

    private:
        sqlite3* db_;
    };

} // itel

#endif // SQLITE_HPP_

The implementation, sqlite.cpp:

#include "sqlite.hpp"

#include <algorithm>
#include <iomanip>
#include <iostream>
#include <sstream>
#include <string>

#define EXIT_ON_ERROR(resultcode) \
if (resultcode != SQLITE_OK) \
{ \
    sqlite3_finalize(stmt); \
    return resultcode; \
}

namespace {
    std::string insert_into_helper(
        const std::string& table_name,
        const std::vector<sql::column_values>& fields) {

        std::string sqlfront{ "INSERT INTO " + table_name + " (" };
        std::string sqlend{ ") VALUES (" };

        std::string separator{ "" };
        for (const auto& field : fields) {
            sqlfront += separator + field.column_name;
            sqlend += separator + ':' + field.column_name;
            separator = ",";
        }

        sqlend += ");";
        return sqlfront + sqlend;
    }

    std::string space_if_required(const std::string& s) {
        return !s.empty() && s[0] != ' ' ? " " : "";
    }

    std::string update_helper(
        const std::string& table_name,
        const std::vector<sql::column_values>& fields,
        const std::string& where_clause) {

        std::string sql{ "UPDATE " + table_name + " SET " };

        std::string separator{ "" };
        for (const auto& field : fields) {
            sql += separator + field.column_name + "=:" + field.column_name;
            separator = ",";
        }

        if (!where_clause.empty()) {
            sql += space_if_required(where_clause);
            sql += where_clause;
        }

        sql += ";";

        return sql;
    }

    std::string delete_from_helper(
        const std::string& table_name,
        const std::string& where_clause) {
        std::string sql{ "DELETE FROM " + table_name };

        if (!where_clause.empty()) {
            sql += space_if_required(where_clause);
            sql += where_clause;
        }

        sql += ";";

        return sql;
    }

    const std::string select_helper(
        const std::string& table_name,
        const std::vector<std::string>& fields,
        const std::string& where_clause) {

        std::string sql{ "SELECT " };

        std::string separator{ "" };
        for (const auto& field : fields) {
            sql += separator + field;
            separator = ",";
        }

        if (fields.empty()) {
            sql += "*";
        }

        sql += " FROM " + table_name;

        if (!where_clause.empty()) {
            sql += space_if_required(where_clause);
            sql += where_clause;
        }

        sql += ";";

        return sql;
    }

    int bind_fields(sqlite3_stmt* stmt, const std::vector<sql::column_values>& fields) {

        int rc = SQLITE_OK;

        for (const auto& param : fields) {
            std::string next_param{ ':' + param.column_name };
            int idx = sqlite3_bind_parameter_index(stmt, next_param.c_str());

            switch (param.column_value.index()) {
            case 0: rc = sqlite3_bind_int(stmt, idx, std::get<0>(param.column_value)); break;
            case 1: rc = sqlite3_bind_double(stmt, idx, std::get<1>(param.column_value)); break;
            case 2: rc = sqlite3_bind_text(stmt, idx, std::get<2>(param.column_value).c_str(), -1, SQLITE_STATIC); break;
            case 3:
                rc = sqlite3_bind_blob(stmt, idx, std::get<3>(param.column_value).data(), std::get<3>(param.column_value).size(), SQLITE_STATIC);
                break;
            }
        }
        return rc;
    }

    int bind_where(sqlite3_stmt* stmt, const std::vector<sql::where_binding>& binding) {

        int rc = SQLITE_OK;

        for (const auto& param : binding) {
            std::string next_param{ ':' + param.column_name };

            int idx = sqlite3_bind_parameter_index(stmt, next_param.c_str());

            switch (param.column_value.index()) {
            case 0: rc = sqlite3_bind_int(stmt, idx, std::get<0>(param.column_value)); break;
            case 1: rc = sqlite3_bind_double(stmt, idx, std::get<1>(param.column_value)); break;
            case 2:  rc = sqlite3_bind_text(stmt, idx, std::get<2>(param.column_value).c_str(), -1, SQLITE_STATIC); break;
            case 3:
                rc = sqlite3_bind_blob(stmt, idx, std::get<3>(param.column_value).data(), std::get<3>(param.column_value).size(), SQLITE_STATIC);
                break;
            }
        }
        return rc;
    }

    int step_and_finalise(sqlite3_stmt* stmt) {
        if (stmt == nullptr) { return SQLITE_ERROR; }

        // whether error or not we must call finalize
        int rc = sqlite3_step(stmt);
        // SQLITE_ROW = another row ready - possible to configure to return a value - but we just ignore anything returned
        // SQLITE_DONE = finished executing

        // caller is more interested in the result of the step
        int finalise_rc = sqlite3_finalize(stmt);  // de-allocates stmt
        return rc == SQLITE_DONE ? finalise_rc : rc;
    }
}

namespace sql {

    std::ostream& operator<< (std::ostream& os, const column_values& v) {

        os << "name: " << v.column_name << ", value: ";

        switch (v.column_value.index()) {
        case 0: os << std::get<0>(v.column_value) << " of type int"; break;
        case 1: os << std::get<1>(v.column_value) << " of type double"; break;
        case 2: os << std::get<2>(v.column_value) << " of type string"; break;
        case 3:
        {
#ifdef PRINT_BLOB_AS_HEX
            auto previous_flags = os.flags();
            std::for_each(std::get<3>(v.column_value).begin(), std::get<3>(v.column_value).end(), [&os](const uint8_t& byte) {
                os << std::hex << std::setfill('0') << std::setw(2) << (byte & 0xFF) << ' ';
                });
            os << " of type vector<uint8_t>";
            os.setf(previous_flags);
#else
            os << "<blob> of type vector<uint8_t>";
#endif
            break;
        }
        }

        return os;
    }

#ifdef PRINT_BLOB_AS_HEX
    std::ostream& operator<<(std::ostream & os, const std::vector<uint8_t>&v)
    {
        auto previous_flags = os.flags();
        std::for_each(v.begin(), v.end(), [&os](const uint8_t& byte) {
            os << std::hex << std::setfill('0') << std::setw(2) << (byte & 0xFF) << ' ';
            });
        os.setf(previous_flags);
#else
    std::ostream& operator<<(std::ostream & os, const std::vector<uint8_t>& /* v */)
    {
        os << "<blob>";
#endif
        return os;
    }

    std::ostream& operator<<(std::ostream& os, const sqlite_data_type& v)
    {
        std::visit([&](const auto& element) {
                os << element;
            }, v);

        return os;
    }

    std::ostream& operator<< (std::ostream& os, const std::map<std::string, sqlite_data_type>& v) {

        for (const auto& element : v) {
            os << element.first << ": " << element.second << '|';
        }

        return os;
    }

    sqlite::sqlite() : db_(nullptr) {}

    sqlite::~sqlite() {
        close();
    }

    int sqlite::open(const std::string& filename) {
        return sqlite3_open(filename.c_str(), &db_);
    }

    int sqlite::close() {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        return sqlite3_close(db_);
    }

    int sqlite::insert_into(const std::string& table_name, const std::vector<column_values>& fields) {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        const std::string sql = insert_into_helper(table_name, fields);

        sqlite3_stmt* stmt = NULL;
        EXIT_ON_ERROR(sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, NULL));

        EXIT_ON_ERROR(bind_fields(stmt, fields));

        return step_and_finalise(stmt);
    }

    int sqlite::last_insert_rowid() {
        return static_cast<int>(sqlite3_last_insert_rowid(db_));
    }

    int sqlite::update(const std::string& table_name, const std::vector<column_values>& fields, 
        const std::string& where_clause, const std::vector<where_binding>& bindings) {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        const std::string sql = update_helper(table_name, fields, where_clause);

        sqlite3_stmt* stmt = NULL;
        EXIT_ON_ERROR(sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, NULL));

        EXIT_ON_ERROR(bind_fields(stmt, fields));

        EXIT_ON_ERROR(bind_where(stmt, bindings));

        return step_and_finalise(stmt);
    }

    int sqlite::update(const std::string& table_name, const std::vector<column_values>& fields) {
        return update(table_name, fields, "", {});
    }

    int sqlite::delete_from(const std::string& table_name, const std::string& where_clause, const std::vector<where_binding>& bindings) {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        const std::string sql = delete_from_helper(table_name, where_clause);

        sqlite3_stmt* stmt = NULL;
        EXIT_ON_ERROR(sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, NULL));

        EXIT_ON_ERROR(bind_where(stmt, bindings));

        return step_and_finalise(stmt);
    }

    int sqlite::delete_from(const std::string& table_name) {
        return delete_from(table_name, "", {});
    }

    int sqlite::select_columns(const std::string& table_name,
        const std::vector<std::string>& fields,
        const std::string& where_clause,
        const std::vector<where_binding>& bindings,
        std::vector<std::map<std::string, sqlite_data_type>>& results) {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        const std::string sql = select_helper(table_name, fields, where_clause);

        sqlite3_stmt* stmt = NULL;
        EXIT_ON_ERROR(sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, NULL));

        EXIT_ON_ERROR(bind_where(stmt, bindings));

        int num_cols = sqlite3_column_count(stmt);

        std::vector<std::string> column_names;
        for (int i = 0; i < num_cols; i++) {
            const char* colname = sqlite3_column_name(stmt, i);
            column_names.push_back(colname ? colname : "");
        }

        int rc = 0;
        while ((rc = sqlite3_step(stmt)) != SQLITE_DONE) {
        std::map<std::string, sqlite_data_type> row;
        for (int i = 0; i < num_cols; i++)
        {
            switch (sqlite3_column_type(stmt, i))
            {
            case SQLITE3_TEXT:
            {
                const unsigned char* value = sqlite3_column_text(stmt, i);
                int len = sqlite3_column_bytes(stmt, i);
                row[column_names[i]] = std::string(value, value+len);
            }
            break;
            case SQLITE_INTEGER:
            {
                row[column_names[i]] = sqlite3_column_int(stmt, i);
            }
            break;
            case SQLITE_FLOAT:
            {
                row[column_names[i]] = sqlite3_column_double(stmt, i);
            }
            break;
            case SQLITE_BLOB:
            {
                const uint8_t* value = reinterpret_cast<const uint8_t*>(sqlite3_column_blob(stmt, i));
                int len = sqlite3_column_bytes(stmt, i);
                row[column_names[i]] = std::vector<uint8_t>(value, value + len);
            }
            break;
            case SQLITE_NULL:
            {
                row[column_names[i]] = "null";
            }
            break;
            default:
                break;
            }
        }
        results.push_back(row);
        }

        return sqlite3_finalize(stmt);
    }

    int sqlite::select_star(const std::string& table_name,
        const std::string& where_clause,
        const std::vector<where_binding>& bindings,
        std::vector<std::map<std::string, sqlite_data_type>>& results) {
        return select_columns(table_name, {}, where_clause, bindings, results);
    }

    int sqlite::select_star(const std::string& table_name,
        std::vector<std::map<std::string, sqlite_data_type>>& results) {
        return select_star(table_name, "", {}, results);
    }

    const std::string sqlite::get_last_error_description() {
        if (db_ == nullptr) { return ""; }

        const char* error = sqlite3_errmsg(db_);
        std::string s(error ? error : "");
        return s;
    }
} // sql

main.cpp example:

/*
This example assumes you have created a database as follows:
sqlite3.exe mydb.db

CREATE TABLE test (name TEXT, age INTEGER, photo BLOB);
*/
#include "sqlite.hpp"

#include <iostream>
#include <string>
#include <vector>
#include <fstream>
#include <sstream>
#include <iomanip>

using namespace sql;

int main()
{
    sql::sqlite db;
    int rc = db.open("mydb.db");

    std::cout << "db.open returned: " << rc << std::endl;

    // picture from https://en.wikipedia.org/wiki/Mickey_Mouse
    std::ifstream f("Mickey_Mouse.png", std::ios::binary);

    if (!f.good()) {
        std::cout << "failed to open Mickey Mouse bitmap file\n";
        return 1;
    }

    std::vector<uint8_t> buffer(std::istreambuf_iterator<char>(f), {});

    std::vector<sql::column_values> params {
        {"name", "Mickey Mouse"},
        {"age", 12},
        {"photo", buffer}
    };

    for (const auto& param : params) {
        std::cout << "inserting param: " << param << std::endl;
    }

    rc = db.insert_into("test", params);
    std::cout << "db.insert_into(...) returned: " << rc << std::endl;

    int lastrowid = 0;

    if (rc == SQLITE_OK) {
        lastrowid = db.last_insert_rowid();
        std::cout << "inserted into rowid: " << lastrowid << std::endl;
    }

    // let us now update this record
    std::vector<sql::column_values> updated_params{
    {"name", "Donald Duck"},
    {"age", 23}
    };

    const std::vector<where_binding>& bindings{
        {"rowid", lastrowid}
    };

    rc = db.update("test", updated_params, "WHERE rowid=:rowid", bindings);
    std::cout << "db.update(...) returned: " << rc << std::endl;

    // try SELECT
    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    // simplest way
    //rc = db.select_star("test", results);

    // using select_column to specifically display sqlite table rowid
    //rc = db.select_columns("test", { "rowid", "name", "age", "photo" }, {}, results);

    // Or pass in rowid and * to display rowid and all other columns
    //rc = db.select_columns("test", { "rowid", "*" }, {}, results);

    const std::vector<where_binding>& select_bindings{
       {"name", "Don%"}
    };

    rc = db.select_columns("test", { "rowid", "*" }, "WHERE name LIKE :name", select_bindings, results);

    std::cout << "db.select_columns(...) returned: " << rc << std::endl;

    // print rows
    int i = 0;
    for (const auto& row : results) {
        std::cout << "row" << ++i << ": " << row << std::endl;
    }

    // finally delete row added
    const std::vector<where_binding>& delete_bindings {
        {"rowid", lastrowid}
    };

    rc = db.delete_from("test", "WHERE rowid=:rowid", delete_bindings);
    std::cout << "db.delete_from(...) returned: " << rc << std::endl;

    // code below inserts into data into a table that does not exist

    // test to insert into an invalid column
    std::vector<sql::column_values> bad_params{
        {"nave", "Tanner"},
        {"address8", "3 The Avenue"},
        {"postcoode", "GU17 0TR"}
    };

    rc = db.insert_into("contacts", bad_params);
    std::cout << "db.insert_into(...) returned: " << rc << std::endl;

    if (rc != SQLITE_OK) {
        std::cout << db.get_last_error_description() << std::endl;
    }
}

And some tests:

#include "sqlite.hpp"

#include "sqlite3.h" // required for db_initial_setup

#include <string>
#include <iostream>
#include <cstdio>
#include <sstream>
#include <cstdio>
#include <filesystem>
#include <algorithm>

#include "gtest/gtest.h"

using namespace sql;

namespace {

    void db_initial_setup() {

        if (remove("contacts.db") != 0) {
            perror("Error deleting contacts.db");
        }

        // we create using c library so not using any of the code to exercise
        sqlite3* db;
        char* err_msg = 0;

        int rc = sqlite3_open("contacts.db", &db);

        if (rc != SQLITE_OK) {

            fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
            sqlite3_close(db);

            FAIL() << "Cannot open database for testing\n";
            return;
        }

        const char* sql[] = {
            "DROP TABLE IF EXISTS contacts;"
            "CREATE TABLE contacts (name TEXT, company TEXT, mobile TEXT, ddi TEXT, switchboard TEXT, address1 TEXT, address2 TEXT, address3 TEXT, address4 TEXT, postcode TEXT, email TEXT, url TEXT, category TEXT, notes TEXT);"
            "CREATE INDEX idx_mobile ON contacts (mobile);"
            "CREATE INDEX idx_switchboard ON contacts (switchboard);"
            "CREATE INDEX idx_ddi ON contacts (ddi);",
            "CREATE TABLE calls(timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, callerid TEXT, contactid INTEGER);",
            "INSERT INTO contacts (name, mobile, switchboard, address1, address2, address3, postcode, email, url, category) VALUES(\"Test Person\", \"07788111222\", \"02088884444\", \"House of Commons\", \"Westminster\", \"London\", \"SW1A 0AA\", \"test@house.co.uk\", \"www.house.com\", \"Supplier\");",
            "INSERT INTO calls (callerid, contactid) VALUES(\"07788111222\", 1);"
        };

        size_t num_commands = sizeof(sql) / sizeof(char*);

        for (size_t i = 0; i < num_commands; ++i) {
            rc = sqlite3_exec(db, sql[i], 0, 0, &err_msg);

            if (rc != SQLITE_OK) {

                fprintf(stderr, "SQL error: %s\n", err_msg);

                sqlite3_free(err_msg);
                sqlite3_close(db);
            }
        }
        sqlite3_close(db);
    }

    const std::string filename("contacts.db");
    std::vector<std::string> tables{ "contacts", "calls" };
}

class sqlite_cpp_tester : public ::testing::Test {
public:
    void SetUp() {
        db_initial_setup();
    }
};


TEST_F(sqlite_cpp_tester, given_a_valid_db_file_open_close_return_success) {

    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);
    EXPECT_EQ(db.close(), SQLITE_OK);
}

TEST_F(sqlite_cpp_tester, given_a_valid_insert_select_returns_same_as_inserted) {

    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    std::vector<sql::column_values> fields {
    {"name", "Mickey Mouse"},
    {"company", "Disney"},
    {"mobile", "07755123456"},
    {"ddi", "01222333333"},
    {"switchboard", "01222444444"},
    {"address1", "1 The Avenue"},
    {"address2", "Greystoke"},
    {"address3", "Lower Wirmwood"},
    {"address4", "Baffleshire"},
    {"postcode", "PO21 4RR"},
    {"email", "mickey@disney.com"},
    {"url", "disney.com"},
    {"category", "cartoonist"},
    {"notes", "delightful mouse"}
    };

    EXPECT_EQ(db.insert_into("contacts", fields), SQLITE_OK);

    int lastrowid = db.last_insert_rowid();

    const std::vector<sql::where_binding>& bindings {
      {"rowid", lastrowid}
    };
    std::vector<std::map<std::string, sql::sqlite_data_type>> results;
    
    EXPECT_EQ(db.select_star("contacts", "WHERE rowid=:rowid", bindings, results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);

    EXPECT_EQ(results[0]["name"], fields[0].column_value);
    EXPECT_EQ(results[0]["company"], fields[1].column_value);
    EXPECT_EQ(results[0]["mobile"], fields[2].column_value);
    EXPECT_EQ(results[0]["ddi"], fields[3].column_value);
    EXPECT_EQ(results[0]["switchboard"], fields[4].column_value);
    EXPECT_EQ(results[0]["address1"], fields[5].column_value);
    EXPECT_EQ(results[0]["address2"], fields[6].column_value);
    EXPECT_EQ(results[0]["address3"], fields[7].column_value);
    EXPECT_EQ(results[0]["address4"], fields[8].column_value);
    EXPECT_EQ(results[0]["postcode"], fields[9].column_value);
    EXPECT_EQ(results[0]["email"], fields[10].column_value);
    EXPECT_EQ(results[0]["url"], fields[11].column_value);
    EXPECT_EQ(results[0]["category"], fields[12].column_value);
    EXPECT_EQ(results[0]["notes"], fields[13].column_value);
}


TEST_F(sqlite_cpp_tester, given_a_valid_insert_then_update_select_returns_same_as_updated) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    std::vector<sql::column_values> fields{
    {"name", "Mickey Mouse"},
    {"company", "Disney"},
    {"mobile", "07755123456"},
    {"ddi", "01222333333"},
    {"switchboard", "01222444444"},
    {"address1", "1 The Avenue"},
    {"address2", "Greystoke"},
    {"address3", "Lower Wirmwood"},
    {"address4", "Baffleshire"},
    {"postcode", "PO21 4RR"},
    {"email", "mickey@disney.com"},
    {"url", "disney.com"},
    {"category", "cartoonist"},
    {"notes", "delightful mouse"}
    };

    EXPECT_EQ(db.insert_into("contacts", fields), SQLITE_OK);

    int lastrowid = db.last_insert_rowid();

    // UPDATE
    std::vector<sql::column_values> updated_fields{
    {"name", "Donald Duck"},
    {"company", "Disney"},
    {"mobile", "07755654321"},
    {"ddi", "01222444444"},
    {"switchboard", "01222555555"},
    {"address1", "2 The Avenue"},
    {"address2", "Greystoke"},
    {"address3", "Lower Wirmwood"},
    {"address4", "Baffleshire"},
    {"postcode", "PO21 4RR"},
    {"email", "donald@disney.com"},
    {"url", "disney.com"},
    {"category", "cartoonist"},
    {"notes", "quackers"}
    };

    const std::vector<where_binding>& update_bindings{
      {"rowid", lastrowid}
    };

    const std::string where_clause{ "WHERE rowid=:rowid" };

    EXPECT_EQ(db.update("contacts", updated_fields, where_clause, update_bindings), SQLITE_OK);

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;


    EXPECT_EQ(db.select_columns("contacts", { "rowid", "*" }, "WHERE rowid=:rowid", update_bindings, results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);

    EXPECT_EQ(results[0]["name"], updated_fields[0].column_value);
    EXPECT_EQ(results[0]["company"], updated_fields[1].column_value);
    EXPECT_EQ(results[0]["mobile"], updated_fields[2].column_value);
    EXPECT_EQ(results[0]["ddi"], updated_fields[3].column_value);
    EXPECT_EQ(results[0]["switchboard"], updated_fields[4].column_value);
    EXPECT_EQ(results[0]["address1"], updated_fields[5].column_value);
    EXPECT_EQ(results[0]["address2"], updated_fields[6].column_value);
    EXPECT_EQ(results[0]["address3"], updated_fields[7].column_value);
    EXPECT_EQ(results[0]["address4"], updated_fields[8].column_value);
    EXPECT_EQ(results[0]["postcode"], updated_fields[9].column_value);
    EXPECT_EQ(results[0]["email"], updated_fields[10].column_value);
    EXPECT_EQ(results[0]["url"], updated_fields[11].column_value);
    EXPECT_EQ(results[0]["category"], updated_fields[12].column_value);
    EXPECT_EQ(results[0]["notes"], updated_fields[13].column_value);
}

TEST_F(sqlite_cpp_tester, given_a_single_quote_in_notes_field_select_returns_same_value_inserted) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    std::vector<sql::column_values> fields{
    {"name", "Sean O'Hennessey"},
    {"company", "Disney"},
    {"mobile", "07755123456"},
    {"ddi", "01222333333"},
    {"switchboard", "01222444444"},
    {"address1", "1 The Avenue"},
    {"address2", "Greystoke"},
    {"address3", "Lower Wirmwood"},
    {"address4", "Baffleshire"},
    {"postcode", "PO21 4RR"},
    {"email", "mickey@disney.com"},
    {"url", "disney.com"},
    {"category", "cartoonist"},
    {"notes", "single quote symbol is '"}
    };

    EXPECT_EQ(db.insert_into("contacts", fields), SQLITE_OK);

    int lastrowid = db.last_insert_rowid();
    const std::vector<where_binding>& update_bindings{
       {"rowid", lastrowid}
    };

    const std::string where_clause{ "WHERE rowid=:rowid" };

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    EXPECT_EQ(db.select_columns("contacts", { "rowid", "*" }, "WHERE rowid=:rowid", update_bindings, results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);

    EXPECT_EQ(results[0]["name"], fields[0].column_value);
    EXPECT_EQ(results[0]["company"], fields[1].column_value);
    EXPECT_EQ(results[0]["mobile"], fields[2].column_value);
    EXPECT_EQ(results[0]["ddi"], fields[3].column_value);
    EXPECT_EQ(results[0]["switchboard"], fields[4].column_value);
    EXPECT_EQ(results[0]["address1"], fields[5].column_value);
    EXPECT_EQ(results[0]["address2"], fields[6].column_value);
    EXPECT_EQ(results[0]["address3"], fields[7].column_value);
    EXPECT_EQ(results[0]["address4"], fields[8].column_value);
    EXPECT_EQ(results[0]["postcode"], fields[9].column_value);
    EXPECT_EQ(results[0]["email"], fields[10].column_value);
    EXPECT_EQ(results[0]["url"], fields[11].column_value);
    EXPECT_EQ(results[0]["category"], fields[12].column_value);
    EXPECT_EQ(results[0]["notes"], fields[13].column_value);
}


TEST_F(sqlite_cpp_tester, given_non_alphanumeric_characters_inserted_select_returns_same_value_inserted) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    std::vector<sql::column_values> fields{
    {"name", "<---------------------->'"},
    {"company", "D\nisne    y"},
    {"mobile", "!!!\"0775512345'''6"},
    {"ddi", "{}===================="},
    {"switchboard", "++++++++++++++++++++++++"},
    {"address1", "&&&&&&&&&&&&&&&&&&&&&&&&&"},
    {"address2", "``````````¬|"},
    {"address3", ";'#:@~"},
    {"address4", "'''''''''''''''''''"},
    {"postcode", "!\"£$%^&*()_+"},
    {"email", "***************************"},
    {"url", "disney.com"},
    {"category", "cartoonist"},
    {"notes", "1\n2\n3\n4\n5\n"}
    };

    EXPECT_EQ(db.insert_into("contacts", fields), SQLITE_OK);

    int lastrowid = db.last_insert_rowid();
    const std::vector<where_binding>& update_bindings{
       {"rowid", lastrowid}
    };

    const std::string where_clause{ "WHERE rowid=:rowid" };

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    EXPECT_EQ(db.select_columns("contacts", { "rowid", "*" }, "WHERE rowid=:rowid", update_bindings, results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);

    EXPECT_EQ(results[0]["name"], fields[0].column_value);
    EXPECT_EQ(results[0]["company"], fields[1].column_value);
    EXPECT_EQ(results[0]["mobile"], fields[2].column_value);
    EXPECT_EQ(results[0]["ddi"], fields[3].column_value);
    EXPECT_EQ(results[0]["switchboard"], fields[4].column_value);
    EXPECT_EQ(results[0]["address1"], fields[5].column_value);
    EXPECT_EQ(results[0]["address2"], fields[6].column_value);
    EXPECT_EQ(results[0]["address3"], fields[7].column_value);
    EXPECT_EQ(results[0]["address4"], fields[8].column_value);
    EXPECT_EQ(results[0]["postcode"], fields[9].column_value);
    EXPECT_EQ(results[0]["email"], fields[10].column_value);
    EXPECT_EQ(results[0]["url"], fields[11].column_value);
    EXPECT_EQ(results[0]["category"], fields[12].column_value);
    EXPECT_EQ(results[0]["notes"], fields[13].column_value);
}

TEST_F(sqlite_cpp_tester, add_integer_value_select_returns_same_value_inserted) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    std::vector<sql::column_values> fields{
    {"callerid", "0775512345"},
    {"contactid", 2}
    };

    EXPECT_EQ(db.insert_into("calls", fields), SQLITE_OK);

    const std::vector<where_binding>& bindings{
       {"contactid", 2}
    };

    const std::string where_clause{ "WHERE rowid=:rowid" };

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    EXPECT_EQ(db.select_columns("calls", { "timestamp", "callerid", "contactid" }, "WHERE contactid=:contactid", bindings, results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);

    EXPECT_EQ(results[0]["callerid"], fields[0].column_value);
    EXPECT_EQ(results[0]["contactid"], fields[1].column_value);
    // get 3 columns back
    EXPECT_EQ(results[0].size(), 3u);
}

// SELECT (using LIKE)
TEST_F(sqlite_cpp_tester, add_integer_value_select_like_returns_same_value_inserted) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    std::vector<sql::column_values> fields{
    {"callerid", "0775512345"},
    {"contactid", 2}
    };

    EXPECT_EQ(db.insert_into("calls", fields), SQLITE_OK);

    const std::vector<where_binding>& bindings{
       {"callerid", "077%"}
    };

    const std::string where_clause{ "WHERE callerid LIKE :callerid" };

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    EXPECT_EQ(db.select_columns("calls", { "timestamp", "callerid", "contactid" }, where_clause, bindings, results), SQLITE_OK);

    EXPECT_EQ(results.size(), 2u);

    EXPECT_EQ(std::get<std::string>(results[0]["callerid"]), "07788111222");
    EXPECT_EQ(std::get<int>(results[0]["contactid"]), 1);

    EXPECT_EQ(results[1]["callerid"], fields[0].column_value);
    EXPECT_EQ(results[1]["contactid"], fields[1].column_value);
    // get 3 columns back
    EXPECT_EQ(results[0].size(), 3u);
}

// GETCALLS
TEST_F(sqlite_cpp_tester, join_returning_data_from_two_tables_returns_correct_data) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    const std::vector<std::string>& fields { "calls.timestamp", "contacts.name", "calls.callerid", "contacts.url" };

    const std::string where_clause{ "LEFT JOIN contacts ON calls.contactid = contacts.rowid" };

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    EXPECT_EQ(db.select_columns("calls", fields, where_clause, {}, results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);
    EXPECT_EQ(std::get<2>(results[0]["callerid"]), "07788111222");
    EXPECT_EQ(std::get<2>(results[0]["name"]), "Test Person");
    EXPECT_EQ(std::get<2>(results[0]["url"]), "www.house.com");
    EXPECT_NE(std::get<2>(results[0]["timestamp"]), "");
}


int main(int argc, char **argv) {
    testing::InitGoogleTest(&argc, argv);
    return RUN_ALL_TESTS();
}

Answer 1

Just a few quick observations:

/* sqlite constructor */

Duh. Keep comments useful and meaningful. You don't need to explain that the ctor and dtor are those things.

(const std::string& table_name, 

Suggest you use std::string_view (by value) for parameters instead. This is more optimal if you pass a lexical string literal, as it doesn't have to copy the data into a std::string first! And it's just as fast when passed existing std::string instances.

All the functions return int — what does that mean? I suppose it is passing through the underlying sqlite function results. I suggest you define a "strong type" for that, e.g. enum class sqlite_result : int {};

Don't use endl, as explained many times on this board.

Why do you use fprintf in some places and ostreams in others? You know that cerr exists, right?

std::vector<sql::column_values> params {
        {"name", "Mickey Mouse"},
        {"age", 12},
        {"photo", buffer}
    };

Should be at least const, can be constexpr in this case. You might think that test code is not important, but people will use it as an exemplar and copy it to use as a starting point in real uses, so it should be fully robust and illustrative of best practices.

You're requiring a std::vector for the fields. You should instead make it a template that takes a pair of iterators, or if you are using C++20 or otherwise have ranges3 available, use a range view. Then you can pass it, for example, a plain C array in your examples and not need to construct a vector in dynamic memory, use vectors with non-default allocators, std::pmr::vector, Boost containers, etc.

const std::vector<where_binding>& bindings{
        {"rowid", lastrowid}
    };

What does the & do here?