SQLite for your Raspberry Pi server

Adding a persistent database to your pi.

In my previous posts I talked about how to create a RESTful server app in C++ using the Raspberry pi. In this installment, I will talk about how you can leverage the SQLite library to give your code the amazing ability to manage persistent data.

SQLite is the most deployed database engine in the world and is used on a plethora of applications ranging from mobile phones to web browsers to even embedded systems. The fact that is compact library written in the C language rather than a server makes it ideal for small applications like you would find on the Raspberry Pi.

Last I checked the libsqlite3.so file on Raspbian GNU/Linux 11 (bullseye) was something like 116Kb.

The library is is self-contained with no external dependencies, and the database file itself, used by your app is cross-platform. SQLite is also open-source, which makes it easy to use without any limitation.

Getting started

Installing SQLite on the pi is fairly simple. As always we make sure that our pi is up to date.

sudo apt update 
sudo apt full-upgrade

The use the advanced package tool (apt) system to install the library.

sudo apt install sqlite3 

This will give yo the core library and the command line interface, If you plan to link the library to you application you will also need to install the development libraries and header files.

sudo apt-get install libsqlite3-dev

A quick example

Let’s start our exploration by creating a simple database to store keys and values and populate it with a few items.

#create a directory for our test
mkdir sqltest
cd sqltest

#create a database file and open the command line app
sqlite3 foo.db
 
#Enter some commands at the sqlite>  prompt

#create a table for our demo
CREATE TABLE IF NOT EXISTS DEMO_DATA(NAME PRIMARY KEY,VALUE);

#insert some values
INSERT INTO DEMO_DATA(NAME,VALUE) VALUES ('prop1','some text');
INSERT INTO DEMO_DATA(NAME,VALUE) VALUES ('prop2','48');
INSERT INTO DEMO_DATA(NAME,VALUE) VALUES ('prop3','more data');

#display our results 
select * from DEMO_DATA;
prop1|some text
prop2|48
prop3|more data

#exit sqlite
.exit

Using SQLite from code.

Calling the library from code isn’t really much different. The following is an example piece of C code that does pretty much the same thing. In this case we will also add in a SELECT statement to get the values from the table and print them.

Note that I have also changed the INSERT with REPLACE. This is so I can run the code multiple times and get the same results.

/*
clang -Wall -o testsql testsql.c -lsqlite3
*/
#include <assert.h>
#include <stdio.h>
#include <stdlib.h>
#include <stdbool.h>
#include <sqlite3.h>

// create a typedef for readability 
typedef const unsigned char* cP;

int main(int argc, const char * argv[]) {

  sqlite3 *_sdb;

  //  Open database
  if(sqlite3_open("foo.db", &_sdb) != SQLITE_OK){
    printf("sqlite3_open FAILED: %s", sqlite3_errmsg(_sdb ) );
    abort();
  }

  // make sure primary tables are there.
    const char* sql = "CREATE TABLE IF NOT EXISTS DEMO_DATA "\
                      "(NAME PRIMARY KEY, VALUE )";

  char *zErrMsg = 0;
  if(sqlite3_exec(_sdb, sql ,NULL, 0, &zErrMsg) != SQLITE_OK){
      printf("sqlite3_exec FAILED: %s",sqlite3_errmsg(_sdb));
      sqlite3_free(zErrMsg);
      abort();
  }

  const char* sql1 = 
    "REPLACE INTO DEMO_DATA (NAME, VALUE) VALUES " \
    "('prop1','some text')," \
    "('prop2',48)," \
    "('prop3','more data');" ;

  if(sqlite3_exec(_sdb, sql1 ,NULL, 0, &zErrMsg) != SQLITE_OK){
      printf("sqlite3_exec FAILED: %s",sqlite3_errmsg(_sdb));
      sqlite3_free(zErrMsg);
      abort();
  }

// enumerate and print out the table
 
  const char* sql2 = "SELECT NAME,VALUE FROM DEMO_DATA;";
  sqlite3_stmt* stmt = NULL;
  sqlite3_prepare_v2(_sdb, sql2, -1,  &stmt, NULL);

  while ( (sqlite3_step(stmt)) == SQLITE_ROW) { 
    cP  key = sqlite3_column_text(stmt, 0);
    cP  value =  sqlite3_column_text(stmt, 1);    
    printf("%10s : %s\n", key,value);
    }
  sqlite3_finalize(stmt);

  // close the database
  if(_sdb) {
    sqlite3_close(_sdb);
  }
  
  return 0;
}

To test this yourself can create a file called testsql.c and assuming you have previously setup your Pi with Clang you should be able to do the following:

#compile and link
clang -Wall -o testsql testsql.c -lsqlite3

./testsql 
     prop1 : some text
     prop2 : 48
     prop3 : more data

SQLDemoServer

To put all this to work, we can take the REST server I described in my Giving your Raspberry Pi a REST part 2 article and add some database features. I have a piece of sample code called SQLDemoserver on GitHub you can try for yourself.

The SQLDemoserver will create a “test” endpoint at port 9000 and will handle GET PATCH and DELETE http methods to manipulate a simple key/value store.

Again assuming you have previously setup your Pi with git-core, cmake, and clang or are running on a similar Linux system. You can build the demo code and follows:

git clone https://github.com/vinthewrench/sqldemoserver.git
cd  sqldemoserver.git
git submodule update --init --recursive
cmake .
make
# this will create the target in bin 

#To get it running in the background, simply
cd bin
nohup demoserver &

We can use the cURL and json_pp utilities from the command line to test getting and setting these objects.

#multi line command to set three properties
curl -s -X PATCH localhost:9000/props \
 -H 'Content-Type: application/json' \
 -d '{"prop1":"some text","prop2":42,"prop3":"more data"}' \
 json_pp -json_opt pretty,canonical

{
   "success" : true
}

#get the properties from the database

curl -s localhost:9000/props |  json_pp -json_opt pretty,canonical
{
   "properties" : {
      "prop_bool" : "1",
      "prop_int" : "2222",
      "prop_str" : "EeNGPi4CMxnXYlua"
   },
   "success" : true
}

#delete the prop3 property
curl -s -X DELETE localhost:9000/props/prop3

and of course kill the server process when you are done hacking

sudo killall -9 demoserver

Incidentally feel free to run the sqlite3 command to check on the database

sqlite3 demo.db
SELECT * from DEMO_DATA;
prop1|some text
prop2|42

#exit
.exit

Walking though the code

As with the previous articles examples the code also includes a demoserver.xcodeproj file. If you clone the code from GitHub on an macOS system, you can build and debug it using Xcode.

git clone https://github.com/vinthewrench/sqldemoserver.git
cd  sqldemoserver
git submodule update --init --recursive
open demoserver.xcodeproj

This will allow you to place breakpoints and watch the code respond to the same curl example I posted above.

Note that this code can also be built with authentication turned on as described Giving your Raspberry Pi a REST part 3 and debugged as described in part 4.

That’s all folks

This concludes my series on on building REST servers on Raspberry Pi. Digging through my previous articles, I have talked about the basics of REST and authenticating requests, and this adds the layer of persistence to the fray. If you can think of any more interesting topics please comment and I will try to address those in a future article.