logo CoolAJ86

Building SQLCipher for node.js on Raspberry Pi 2

Published 2015-6-29

I want SQLite3 with FTS (Full-Text Search) and AES crypto support. It doesn't come handed to me on a silver platter, but I can make it happen nonetheless... and I will.

Note that both "SQLite Encryption Extension" and "SQLCipher" exist. SQLCipher seems to be the popular choice (and it dominates SEO, for sure).

Building SQLCipher Standalone

OS X

Since it's already been added to brew, it's super simple:

brew options sqlcipher

brew install sqlcipher --with-fts

Linux / Raspberry Pi

You'll need to grab build tools (gcc et al) as well as openssl.

sudo apt-get install -y build-essential

# I found the correct package with 'apt-cache search openssl2'
sudo apt-get install -y openssl libssl-dev

Then you can clone the sqlcipher and get to work

git clone https://github.com/sqlcipher/sqlcipher.git
pushd sqlcipher

./configure --help

# CFLAGS will turn on encryption, default temp_store to memory,
# (file usage optional), enable full-text search, link to libcrypto
./configure CFLAGS="-DSQLITE_HAS_CODEC -DSQLITE_TEMP_STORE=2 -DSQLITE_ENABLE_FTS3 -lcrypto"

Before you actually compile, you'll want to make sure that you're taking full advantage of the CPUs.

This is a very time consuming process, so if you have additional CPU cores (four on the RPi 2, eight on a macbook) you definitely want to be running all of them at full capacity.

On linux nproc command will show you the number of CPUs you have.

On OS X use sysctl -n hw.physicalcpu (or sysctl -n hw.ncpu to account for hyperthreading).

make clean

NUMCPUS=$(nproc 2>/dev/null || sysctl -n hw.ncpu)
time make -j $NUMCPUS

I was very impressed with the speed on the RPi 2:

real  2m36.831s
user  2m35.100s
sys 0m3.290s

The B+ wasn't too bad either:

real  5m45.961s
user  5m34.060s
sys 0m6.150s

Due to the limited RAM on the B, it took well over 10 minutes, maybe over 20.

Finish the installation

sudo chown -R $(whoami):$(whoami) /usr/local

make install
sudo ldconfig

IMPORTANT: Don't forget sudo ldconfig or otherwise the system will have trouble finding libsqlcipher.so

Testing SQLCipher FTS Standalone

Create a test file test-sqlcipher-fts.sql with the following contents:

-- sqlcipher test.sqlcipher < test-sqlcipher-fts.sql

-- this will generate a 256-bit key from a passphrase via PBKDF2
PRAGMA KEY = 'secret';

-- to supply a 128- or 256-bit key (in hex) on your own, do like so
-- PRAGMA key = "x'2DD29CA851E7B56E4697B0E1F0850729'";
-- PRAGMA key = "x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'";


PRAGMA CIPHER = 'aes-128-cbc';

CREATE TABLE messages(id INTEGER, user VARCHAR, msg TEXT);
CREATE VIRTUAL TABLE messages_fts USING FTS4(id INTEGER, msg TEXT);

INSERT INTO messages(id, user, msg)
  VALUES (1, 'coolaj86', 'this is test message number one');
INSERT INTO messages(id, user, msg)
  VALUES (2, 'ajthedj', 'this is test message number two');
INSERT INTO messages(id, user, msg)
  VALUES (3, 'coolaj86', 'this is test message number three');

INSERT INTO messages_fts SELECT id, msg FROM messages;

SELECT 'SEARCH one';
SELECT messages.user, messages.msg
  FROM messages INNER JOIN messages_fts ON messages.id = messages_fts.id
  WHERE messages_fts.msg MATCH 'one';
SELECT 'SEARCH two';
SELECT messages.user, messages.msg
  FROM messages INNER JOIN messages_fts ON messages.id = messages_fts.id
  WHERE messages_fts.msg MATCH 'two';
SELECT 'SEARCH message';
SELECT messages.user, messages.msg
  FROM messages INNER JOIN messages_fts ON messages.id = messages_fts.id
  WHERE messages_fts.msg MATCH 'message';

And then run it like so:

sqlcipher test.sqlcipher < test-sqlcipher-fts.sql

If you don't get any errors, you're good to go.

Also, you may want to double check that you can't access the database with the regular sqlite3:

sqlite3 test.sqlcipher

You should see a message that the database is file is encrypted or is not a database.

Compiling for node / io.js

Since node.js is old and out-of-sync with v8, I'm just going to demo with io.js as that is more likely to work in the future when io.js is merged back into node.js.

Note: I would not expect this to work in node < 0.12.x.

export LDFLAGS="-L/usr/local/lib"
export CPPFLAGS="-I/usr/local/include -I/usr/local/include/sqlcipher"
export CXXFLAGS="-I/usr/local/include -I/usr/local/include/sqlcipher"
npm install sqlite3 --build-from-source --sqlite_libname=sqlcipher --sqlite=/usr/local --verbose

For reference, I'm on io.js@2.3.1 and sqlite3@3.0.8.

Test that the node.js module works

Here's the JavaScript version of the standalone test above:

test-sqlcipher-fts.js:

// node test-sqlcipher-fts.js
'use strict';

var sqlite3 = require('sqlite3');
var db = new sqlite3.Database('./test.sqlcipher');

db.serialize(function() {
  var stmt
    , messages
    ;

  db.run("PRAGMA KEY = 'secret'");
  // db.run("PRAGMA key = \"x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'\"");
  db.run("PRAGMA CIPHER = 'aes-128-cbc'");
  db.run("CREATE TABLE messages(id INTEGER, user VARCHAR, msg TEXT)");
  db.run("CREATE VIRTUAL TABLE messages_fts USING FTS4(user VARCHAR, msg TEXT)");

  stmt = db.prepare("INSERT INTO messages(id, user, msg) VALUES (?, ?, ?)");
  messages = [
    [1, 'coolaj86', 'this is test message number one']
  , [2, 'ajthedj', 'this is test message number two']
  , [3, 'coolaj86', 'this is test message number three']
  ];
  messages.forEach(function (msg) {
    stmt.run(msg);
  });
  stmt.finalize();

  db.run("INSERT INTO messages_fts SELECT user, msg FROM messages");
  db.get("SELECT * FROM messages INNER JOIN messages_fts ON messages.user = messages_fts.user WHERE messages_fts.msg MATCH 'one'", function (err, data) {
    if (err) {
      console.error(err);
      return;
    }

    console.log(data);
  });
  db.all("SELECT * FROM messages INNER JOIN messages_fts ON messages.user = messages_fts.user WHERE messages_fts.msg MATCH 'two'", function (err, data) {
    if (err) {
      console.error(err);
      return;
    }

    console.log(data);
  });
  db.each("SELECT * FROM messages INNER JOIN messages_fts ON messages.user = messages_fts.user WHERE messages_fts.msg MATCH 'message'", function (err, data) {
    if (err) {
      console.error(err);
      return;
    }

    console.log(data);
  });
});

And here's how you run it:

node test-sqlcipher-fts.js

Errors

sqlite3.h: No such file or directory

Solution: Explicitly include -I/usr/local/include/sqlcipher, not just -I/usr/local/include

export CPPFLAGS="-I/usr/local/include -I/usr/local/include/sqlcipher"
export CXXFLAGS="-I/usr/local/include -I/usr/local/include/sqlcipher"

> sqlite3@3.0.8 install /home/pi/node_modules/sqlite3
> node-pre-gyp install --fallback-to-build

make: Entering directory '/home/pi/node_modules/sqlite3/build'
  CXX(target) Release/obj.target/node_sqlite3/src/database.o
In file included from ../src/database.cc:5:0:
../src/database.h:10:21: fatal error: sqlite3.h: No such file or directory
compilation terminated.
node_sqlite3.target.mk:93: recipe for target 'Release/obj.target/node_sqlite3/src/database.o' failed
make: *** [Release/obj.target/node_sqlite3/src/database.o] Error 1
make: Leaving directory '/home/pi/node_modules/sqlite3/build'

libsqlcipher.so.0: No such file or directory

Solution: sudo ldconfig

module.js:353
  Module._extensions[extension](this, filename);
                               ^
Error: libsqlcipher.so.0: cannot open shared object file: No such file or directory
    at Error (native)
    at Module.load (module.js:353:32)
    at Function.Module._load (module.js:308:12)
    at Module.require (module.js:363:17)
    at require (module.js:382:17)
    at Object.<anonymous> (/home/pi/node_modules/sqlite3/lib/sqlite3.js:4:15)
    at Module._compile (module.js:428:26)
    at Object.Module._extensions..js (module.js:446:10)
    at Module.load (module.js:353:32)
    at Function.Module._load (module.js:308:12)

By AJ ONeal

If you loved this and want more like it, sign up!


Did I make your day?
Buy me a coffeeBuy me a coffee  

(you can learn about the bigger picture I'm working towards on my patreon page )