How to use SQLite in C++

Tue, Jul 9, 2019

Read in 2 minutes

Sqlite is a crazy fast embeddable sql database engine. It is small; the demo application below is about 20kb compiled on my machine and has a complete embedded database engine included.

Putting structured data into a database file that is universably readable is pretty awesome. And all small tools I write that need structured data use sqlite. I only go to postgres if I really need it. Usually when combining multiple writers and transactions are needed. For desktop/mobile apps it is usually the best tool. At least for me.

The sqlite api is in c - which makes it easy - albeit a bit non-standard at times - to use in C++. I guess wrappers exist to make this experience much more like modern c++ - but I have never found the need. Yet.

#include <iostream>
#include <sqlite3.h>

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
   int i;
   for(i = 0; i<argc; i++) {
     std::cerr << azColName[i] << ": " <<  (argv[i] ? argv[i] : "NULL") << "\n";
   }
   std::cerr << "\n";
   return 0;
}

int main(int argc, char *argv[])
{
  sqlite3 *db;
  char *zErrMsg = 0;
  int rc;

  // OPEN DATABASE
  rc = sqlite3_open("test.db", &db);

  if(rc != 0) {
    std::cerr << "Failed to open database\n";
    std::cerr << zErrMsg << "\n\n";
    return 1;
  }


  // CREATE TABLE
  auto createSql = "CREATE TABLE users (" \
              "id INTEGER PRIMARY KEY AUTOINCREMENT," \
              "name TEXT NOT NULL," \
              "email TEXT NOT NULL);";

  rc = sqlite3_exec(db, createSql, callback, nullptr, &zErrMsg);
  if(rc != 0) {
    std::cerr << "Failed to create table\n";
    std::cerr << zErrMsg << "\n\n";
  }


  auto insertSql = "INSERT INTO users (name, email) VALUES (\"Claus Witt\", \"claus@fliva.com\")";

  rc = sqlite3_exec(db, insertSql, callback, nullptr, &zErrMsg);
  if(rc != 0) {
    std::cerr << "Failed to insert name\n";
    std::cerr << zErrMsg << "\n\n";
  }



  auto selectSql = "SELECT * FROM users;";

  rc = sqlite3_exec(db, selectSql, callback, nullptr, &zErrMsg);
  if(rc != 0) {
    std::cerr << "Failed to select users\n";
    std::cerr << zErrMsg << "\n\n";
    return 4;
  }



  return 0;
}

The demo requires a sqlite development package installed (on my mac that was done using brew install sqlite.

Compile the demo with g++ -std=gnu++11 -stdlib=libc++ demo.cpp -l sqlite3 -o test and run it with ./test

You should see my name and email as output (multiple times if you run it again). You can inspect the database by opening the test.db file in a sqlite database editor. I use base for this.

See Also

How to Read a File in C++

Tue, Oct 4, 2016

Read more →

How to parse JSON in C++

Fri, Sep 16, 2016

Read more →

How to Read from stdin in Cpp

Tue, Dec 1, 2015

Read more →