Sqlite With C++

  • Sun, Mar 12, 2017
  • 5-minute read

This blog post outlines how simple it is to interface with the sqlite3 database platform in a type and memory-safe way using features in modern C++. In order to complie this code, you will need a compiler that supports at least some new features of C++17. Namely: the ability for the compiler to deduce template arguments from constructors. If you want to play with C++17, I suggest grabbing a pre-release snapshot of your favourite compiler. I’m currently using the weekly snapshots of gcc 7.

This code is mostly a reimagination of the Sqlite 5 minute tutorial.

First, some pre-amble.

#include <iostream>
#include <memory>
#include <sqlite3.h>
using namespace std;

Next we introduce a simple class template whose sole job is to execute some code when the object goes out of scope. This little utility is useful for when dealing with C-style API’s, since the onus is typically on the programmer to remember to release resources, free memory, etc. This scope_exit object is designed to call a lambda at scope exit, so put any cleanup code you want to execute inside of it, and you will be exception-safe!

template <typename Func> struct scope_exit {
    explicit scope_exit(Func f) : f_{f} {}
    ~scope_exit() { f_(); }
private:
    Func f_;
};

To help us to remember to release our database handle when we’re done with it, we will use C++’s std::unique_ptr with a custom deleter. std::unique_ptr by default calls delete on objects that it owns when it goes out of scope, but it allows you to replace delete with a different action if you require it. Here we’ll make a simple function object (an object with an overloaded function-call operator) that simply calls sqlite3_close on the passed-in pointer.

struct sqlite3_deleter {
    void operator () (sqlite3* db) const { sqlite3_close(db); }
};

This technique of wrapping a call to a C-function in a function-object to be used by std::unique_ptr is a useful one. In the land of C you can never be sure if a function call is actually a macro, which means you can never be sure if and when you can compose C function calls with more modern C++ features.

Next we introduce a type alias to make the rest of our code easier to follow.

using sqlite3_handle = unique_ptr<sqlite3, sqlite3_deleter>;

Creating an object of type sqlite3_handle requires a bit of care, but not much. Since the sqlite3 API functions return status flags, and use out-parameters for returning values, we first construct a pointer to an sqlite3 database via a call to sqlite3_open. Before doing anything else however, we stash that pointer into an sqlite3_handle object. This ensures that if any code in the remainder of the function throws an exception, the database handle will be cleaned up properly.

auto make_sqlite3_handle(char const* db_name)
{
    sqlite3* p;
    int rc = sqlite3_open(db_name, &p);
    sqlite3_handle h{p};
    if (rc) h.reset();
    return h;
}

Notice that if the status flag is non-zero, we reset the ponter held by our object. This assigns the pointer to nullptr, which signifies that there is no database connection.

Next we have a callback function. This is taken mostly verbatim from the sqlite3 5-minute tutorial, but cleaned up a bit to use iostreams instead of printf. A pointer to this function will be passed into our query execution in main.

int callback(void*, int argc, char** argv, char** col_names)
{
    for (int i = 0; i < argc; ++i)
        cout << col_names[i] << " = " << (argv[i] ? argv[i] : "NULL") << '\n';
    cout << endl;
    return 0;
}

And finally we have our main function. First, here’s the code.

int main(int argc, char* argv[])
{
    if (argc != 3) {
        cerr << "Usage: " << argv[0] << " DATABASE SQL-STATEMENT\n";
        return 1;
    }

    auto db = make_sqlite3_handle(argv[1]);
    if (!db) {
        cerr << "Can't open database: " << sqlite3_errmsg(db.get()) << '\n';
        return 2;
    }

    char* error_msg{};
    scope_exit cleaner_upper{[&] { sqlite3_free(error_msg); }};
    int rc = sqlite3_exec(db.get(), argv[2], callback, 0, &error_msg);
    if( rc != SQLITE_OK ) {
        cerr << "Can't execute query: " << error_msg << '\n';
        return 3;
    }
}

… and now the explanation. We first check to make sure our arguments are available, and then create a database handle to the database file given as the first parameter to our program. If the database open fails (std::unique_ptr overloads the NOT operator that allows us to check if it stores a nullptr or a real value) we give a diagnostic message then exit.

Next we try to execute the query. The sqlite3 API returns a string message in case of failure. It does this by taking in an out-parameter of type pointer to char, and does some allocation behind the scenes. Which leaves us in charge of freeing the message! Hence the scope_exit object we defined much eariler. We create an object called cleaner_upper that will, when it’s destructor gets called, free the string that may be allocated by the sqlite3 API. Here we are also using a new C++17 feature: we do not actually specify the template type parameter of our scope_exit object. It gets figured out by the type of the lambda we pass into it! Nice!

So we execute the query and call the callback function defined above.

Compiling this code

$ g++ -std=c++17 main.cxx -o main -lsqlite3

we can then test it on some queries.

$ ./main test.db "CREATE TABLE users (first_name STRING, last_name STRING)"

$ ./main test.db \
   "INSERT INTO users (first_name, last_name) VALUES ('Bryan', 'St Amour')"

$ ./main test.db \
   "INSERT INTO users (first_name, last_name) VALUES ('Fred', 'Brooks')"

$ ./main test.db "SELECT * FROM users"
first_name = Bryan
last_name = St. Amour

first_name = Fred
last_name = Brooks

And so, as you can see, with some very minor adjustments, it’s very easy to query an sqlite3 database from C++. For simple toy examples such as this, using unique_ptr and all that may be a bit overkill. But as your project grows and becomes more complex, having an easy-to-use handle type to clean up your garbage for you is really convenient.

programming C++sqlite