Modern C++

Using SQLite with Modern C++

Incorporate the free database library with these easy, step-by-step directions.

• By Kenny Kerr
• 02/10/2014

More on This Topic:

• SQLite Performance and Prepared Statements
• SQLite Queries and Primary Keys

Microsoft has long offered a compact alternative to the mainstream editions of SQL Server, but the SQL Server Compact Edition appears to be losing steam. It's not particularly compact and has lost traction in some scenarios such as Windows Store apps, where it isn't even permitted. Windows does include the Extensible Storage Engine, a very fast and efficient database engine. Unfortunately, it doesn't provide a SQL interpreter and, thus, is quite difficult to use for simple tasks.

An amazing little database engine called SQLite is, however, rapidly gaining ground. SQLite is a library in the best sense of the word. Unlike SQL Server Compact Edition, which is distributed as a set of hefty DLLs, SQLite ships in a single C source file. As the sqlite.org Web site advertises, SQLite is a software library that implements a self-contained, server-less, zero-configuration, transactional SQL database engine.

That's a wonderful thing; and to top it off, Microsoft has blessed its use in Windows Store and Windows Phone apps. Of course, SQLite is written entirely in C and the API is, well, a little awkward. At the end of the day, however, it is just C, and modern C++ can make working with SQLite a breeze. It just takes a little up-front attention to detail.

There's a sqlite.c file that contains the implementation and a sqlite3.h file that provides the API. Once you've downloaded the SQLite source files, you need to compile sqlite.c as part of your project. Unfortunately, it won't compile cleanly at warning level 4, my preferred compiler warning level, but you can just tell Visual C++ to compile just this one source file at warning level 3. It also makes use of the GetVersionEx function, which has been deprecated as of Windows 8.1, so you'll also need to tell Visual C++ to ignore warning 4996 to allow the SQLite implementation to compile. You can safely ignore this warning because the database engine doesn't actually rely on any of the version information.

Having compiled the source files, it's time to write some code. I don't much enjoy using C-style APIs directly, so a little help from modern C++ is in order. First, I suggest you download the handle.h header file from dx.codeplex.com. I've described the unique_handle class template in my previous columns and Pluralsight courses. It makes working with C-style APIs a whole lot easier, providing correctness and safety for C-style handle types.

SQLite implements two main objects: connections and statements. I'll start by defining some safe handle wrappers for these. First up is the traits class for a connection handle:

struct connection_handle_traits
{
  using pointer = sqlite3 *;

  static auto invalid() noexcept
  {
    return nullptr;
  }

  static auto close(pointer value) noexcept
  {
    VERIFY_(SQLITE_OK, sqlite3_close(value));
  }
};

I'm using the latest preview build of the Visual C++ compiler, the November 2013 CTP in this case, which is a joy to work with. I'm able to replace the deprecated throw exception specification with the noexcept specifier, providing more opportunities for the compiler to generate optimal code in some cases. I'm also able to rely on the compiler to deduce the return type for the auto functions.

As you can see in the connection handle's traits class, SQLite connections are closed or destroyed with the sqlite3_close function. You'll also notice the SQLITE_OK error code that indicates success. So that's the connection handle's traits class; I'll just include a type alias for convenience:

using connection_handle = unique_handle<connection_handle_traits>;

Next is the traits class for statement handles:

struct statement_handle_traits
{
  using pointer = sqlite3_stmt *;

  static auto invalid() noexcept
  {
    return nullptr;
  }

  static auto close(pointer value) noexcept
  {
    VERIFY_(SQLITE_OK, sqlite3_finalize(value));
  }
};

Here's where you can begin to appreciate how modern C++ can make your life easier. While connection objects are closed with the sqlite3_close function, statement objects are closed with the sqlite3_finalize function. This isn't very discoverable, but with the help of the unique_handle class template, you needn't worry about this at all; just add a type alias again:

using statement_handle = unique_handle<statement_handle_traits>;

The next thing I need to deal with is error handling. As with any database engine -- and, really, any good library or API -- reliably retrieving error information is an essential ingredient to success. For now I'll just define a simple exception class:

struct sql_exception
{
  int code;
  string message;

  sql_exception(int const result, char const * text) :
    code { result },
    message { text }
  {}
};

The exception class will carry the error code as well as an error message inside a standard character string. I've specifically chosen a string class here because the exception needs to make a copy of whatever error message SQLite returns. SQLite provides the sqlite3_errmsg function, which returns an error message describing the most recent error experienced on a particular connection. The storage of the returned string is owned by the connection object, so we need to be careful to make a copy of it immediately, which is exactly what my exception class does.

While the connection and statement handle wrappers are convenient, they don't go far enough, so I'll provide an extra layer of abstraction as a convenience. First up is a connection class that will take care of a single connection handle:

struct connection
{
  connection_handle handle;

  // ...
};

The first thing it needs is a way to open a connection. SQLite provides the sqlite3_open function to create or open a database file, returning a connection handle on success. The trick is that even if it fails to establish a valid connection, a connection object will still be created so that this connection object may be used to convey diagnostic information. This means that there's going to be some potentially awkward handle-juggling going on. I need to keep the connection alive to retrieve the error message, but I also don't want it to outlive the connection class's open method. Fortunately, the unique_handle class template supports move semantics and makes this a breeze:

auto open(char const * filename)
{
  auto local = connection_handle {};

  auto const result = sqlite3_open(filename,
                                   local.get_address_of());

  if (SQLITE_OK != result)
  {
    throw sql_exception { result, sqlite3_errmsg(local.get()) };
  }

  handle = move(local);
}

The open method first attempts to open the connection and capture the resulting handle to a local variable. If the attempt to open the connection fails, it uses this valid connection object to retrieve the error message and throws an exception. If, on the other hand, the sqlite3_open function succeeds, it transfers ownership of the newly-created connection handle, and the connection object is ready for business.

So I've got a connection class that's able to open and create a connection to a new or existing database file. Now I need a way to execute SQL statements. As I've mentioned, SQLite provides a statement object, but it also provides a simple abstraction that avoids having to create a statement object directly. It still relies on a statement behind the scenes, just not one I need to create myself. This might be useful for the occasional ad-hoc SQL query.

The sqlite3_exec function isn't particularly obvious, and provides a C-style callback mechanism that's a little tedious to work with; fortunately, you can ignore all that and simply use it to execute simple SQL statements:

auto execute(char const * text)
{
  ASSERT(handle);

  auto const result = sqlite3_exec(handle.get(),
                                   text,
                                   nullptr,
                                   nullptr,
                                   nullptr);

  if (SQLITE_OK != result)
  {
    throw sql_exception { result, sqlite3_errmsg(handle.get()) };
  }
}

In general, I don't use the callback facilities as it tends to be simpler just to create a statement object, assuming you've wrapped it up in some modern C++. Given the connection class, I can now create a database and execute some SQL statements quite simply:

auto main()
{
  try
  {
    connection c;
    c.open("C:\\Sample\\Chickens.db");

    c.execute("create table Hens ( Id int primary key, Name nvarchar(100) not null )");

    c.execute("insert into Hens (Id, Name) values (1, 'Rowena'), 
      (2, 'Henrietta'), (3, 'Constance')");
  }
  catch (sql_exception const & e)
  {
    TRACE(L"%d %S\n", e.code, e.message.c_str());
  }
}

SQLite is an amazingly impressive library that's entirely free: First, it's open source. Second (and what's most important), it's in the public domain. Thus, SQLite is unencumbered by troublesome license agreements.

Check out my Pluralsight course, "10 Practical Techniques to Power Your Visual C++ Apps," for more information about SQLite. I provide step-by-step instructions for how to set up and use SQLite, as well as how to create a useful statement class. You'll learn how to create prepared statements, how to step through rows of data, how to retrieve individual column values and much more.