By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement . We’ll occasionally send you account related emails.

Users can now compile better-sqlite3 against their own customized version of SQLite3:
Read the instructions here.
This also enables support for sqleet, SQLCipher, and other drop-in replacements for SQLite3.

how to build with SQLCipher ??

Steps 1-4 are documented on the SQLCipher website . Step 6 is documented in our docs .

Steps 1-4 are documented on the SQLCipher website . Step 6 is documented in our docs .

hi, JoshuaWise

I followed the step from 1 to 3, but there's no sqlite3.c in source of sqlcipher, and the make failed

@ChachiQ are you still having trouble with this?

@JoshuaWise after I finish step 1-4, and npm install better-sqlite3, i got these error

npm install better-sqlite3 --sqlite3=/mypath/sqlcipher

TOUCH deps_sqlite3_gyp_locate_sqlite3_target_symlink_sqlite3.intermediate
ACTION deps_sqlite3_gyp_locate_sqlite3_target_symlink_sqlite3 deps_sqlite3_gyp_locate_sqlite3_target_symlink_sqlite3.intermediate
TOUCH Release/obj.target/deps/locate_sqlite3.stamp
CC(target) Release/obj.target/sqlite3/gen/sqlite3/sqlite3.o
Release/obj/gen/sqlite3/sqlite3.c:34726:42: error: use of undeclared identifier 'close'
{ "close", (sqlite3_syscall_ptr)close, 0 },
Release/obj/gen/sqlite3/sqlite3.c:34729:42: error: use of undeclared identifier 'access'
{ "access", (sqlite3_syscall_ptr)access, 0 },
Release/obj/gen/sqlite3/sqlite3.c:34732:42: error: use of undeclared identifier 'getcwd'
{ "getcwd", (sqlite3_syscall_ptr)getcwd, 0 },
Release/obj/gen/sqlite3/sqlite3.c:34752:42: error: use of undeclared identifier 'ftruncate'
{ "ftruncate", (sqlite3_syscall_ptr)ftruncate, 0 },
Release/obj/gen/sqlite3/sqlite3.c:34758:42: error: use of undeclared identifier 'read'
{ "read", (sqlite3_syscall_ptr)read, 0 },
Release/obj/gen/sqlite3/sqlite3.c:34762:42: error: use of undeclared identifier 'pread'
{ "pread", (sqlite3_syscall_ptr)pread, 0 },
Release/obj/gen/sqlite3/sqlite3.c:34775:42: error: use of undeclared identifier 'write'
{ "write", (sqlite3_syscall_ptr)write, 0 },
Release/obj/gen/sqlite3/sqlite3.c:34779:42: error: use of undeclared identifier 'pwrite'
{ "pwrite", (sqlite3_syscall_ptr)pwrite, 0 },
Release/obj/gen/sqlite3/sqlite3.c:34804:42: error: use of undeclared identifier 'unlink'; did you mean 'inline'?
{ "unlink", (sqlite3_syscall_ptr)unlink, 0 },
^~~~~~
inline
Release/obj/gen/sqlite3/sqlite3.c:34804:42: error: expected expression
Release/obj/gen/sqlite3/sqlite3.c:34813:42: error: use of undeclared identifier 'rmdir'
{ "rmdir", (sqlite3_syscall_ptr)rmdir, 0 },
Release/obj/gen/sqlite3/sqlite3.c:34817:42: error: use of undeclared identifier 'fchown'
{ "fchown", (sqlite3_syscall_ptr)fchown, 0 },
Release/obj/gen/sqlite3/sqlite3.c:34823:42: error: use of undeclared identifier 'geteuid'
{ "geteuid", (sqlite3_syscall_ptr)geteuid, 0 },
Release/obj/gen/sqlite3/sqlite3.c:34827:42: error: use of undeclared identifier 'mmap'
{ "mmap", (sqlite3_syscall_ptr)mmap, 0 },
Release/obj/gen/sqlite3/sqlite3.c:34834:42: error: use of undeclared identifier 'munmap'
{ "munmap", (sqlite3_syscall_ptr)munmap, 0 },
Release/obj/gen/sqlite3/sqlite3.c:34855:42: error: use of undeclared identifier 'readlink'
{ "readlink", (sqlite3_syscall_ptr)readlink, 0 },
Release/obj/gen/sqlite3/sqlite3.c:34904:22: error: invalid application of 'sizeof' to an incomplete type 'struct unix_syscall []'
for(i=0; i<sizeof(aSyscall)/sizeof(aSyscall[0]); i++){
^~~~~~~~~~
Release/obj/gen/sqlite3/sqlite3.c:34913:22: error: invalid application of 'sizeof' to an incomplete type 'struct unix_syscall []'
for(i=0; i<sizeof(aSyscall)/sizeof(aSyscall[0]); i++){
^~~~~~~~~~
Release/obj/gen/sqlite3/sqlite3.c:34940:20: error: invalid application of 'sizeof' to an incomplete type 'struct unix_syscall []'
for(i=0; i<sizeof(aSyscall)/sizeof(aSyscall[0]); i++){
^~~~~~~~~~
fatal error: too many errors emitted, stopping now [-ferror-limit=]
20 errors generated.
make: *** [Release/obj.target/sqlite3/gen/sqlite3/sqlite3.o] Error 1
rm deps_sqlite3_gyp_locate_sqlite3_target_symlink_sqlite3.intermediate
gyp ERR! build error
gyp ERR! stack Error: make failed with exit code: 2
gyp ERR! stack at ChildProcess.onExit (/usr/local/lib/node_modules/npm/node_modules/node-gyp/lib/build.js:262:23)
gyp ERR! stack at ChildProcess.emit (events.js:182:13)
gyp ERR! stack at Process.ChildProcess._handle.onexit (internal/child_process.js:238:12)
gyp ERR! System Darwin 18.2.0
gyp ERR! command "/usr/local/Cellar/node/7.10.0/bin/node" "/usr/local/lib/node_modules/npm/node_modules/node-gyp/bin/node-gyp.js" "rebuild"
gyp ERR! cwd /Users/chachi/blockchain/eosio/projct/eosbatch/node_modules/better-sqlite3
gyp ERR! node -v v10.8.0
gyp ERR! node-gyp -v v3.7.0
gyp ERR! not ok
npm WARN [email protected] No repository field.

npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! [email protected] install: node-gyp rebuild
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the [email protected] install script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

@ChachiQ ,

I got exactly the same error messages when trying to run npm install with a sqlite3.c file created according to the instructions on SQLCipher's website.

However, the command line interface and the relevant libraries generated from the same file [by running simply make , not make sqlite3.c ] worked correctly.

The reason for error messages is as follows, I believe(*): The sqlite3.c file that includes source files for SQLCipher changes #include statements for a number of standard files (e.g. errno.h , unistd.h ) so that they are contained within the #ifdef SQLITE_HAS_CODEC block.

But as there is no way to pass the required SQLITE_HAS_CODEC option to node-gyp and subsequently to make when running npm install better-sqlite3 --sqlite3=/path/to/my/sqlite3/amalgamation , the block does not get executed and hence the errors.

This is not an error with SQLCipher: If the #include statements were in their normal place, npm install would run without errors (I guess), but the SQLCipher extension would not get included. Only the options defined in deps/sqlite.gyp file would be included.

I managed to solve this as follows (Linux/Debian Stretch):

First, I installed better-sqlite3 normally (without the --sqlite3 switch).

Then, I edited the first line in the deps/common.gyp file to have the path to the directory where the sqlite3.c and sqlite3.h files generated according to the instructions on SQLCipher's website were located:

'variables': { 'sqlite3%': '/home/myusername/sqlcipher' },

Then, I copied the deps/defines.gypi file to defines_with_sqlcipher.gypi (the name could be anything) and placed it in the same directory as above (could be anywhere) and edited it (see attached file; extension changed to txt, as gypi was not supported for attachments; I also commented out some options I did not want to include).
defines_with_sqlcipher.txt

Then, I edited the deps/sqlite.gyp file as follows:

Before:

      'conditions': [['sqlite3 == ""', {
        'includes': ['defines.gypi'],
          'defines': [
            # These are currently required by better-sqlite3.
            'SQLITE_USE_URI=1',
            'SQLITE_ENABLE_COLUMN_METADATA'
After:
      'conditions': [['sqlite3 == ""', {
        'includes': ['defines.gypi'],
        'includes': ['/home/myusername/sqlcipher/defines_with_sqlcipher.gypi'],
Then, I ran node-gyp rebuild (from the myproject/node_modules/better-sqlite3 directory):
../node-gyp/bin/node-gyp.js rebuild
Note that I had installed node-gyp locally in my project directory, not with the -g global switch. Therefore I could not simply run node-gyp rebuild.
One can check that the compile options get correctly included by inspecting the node_modules/better-sqlite3/build/deps/sqlite3.target.mk file generated by node-gyp (section DEFS_Release).
It is a pity that gyp does not allow to use variables in 'includes'. If variables were allowed, one could replace the hardcoded path with the sqlite3 variable. Which would allow just to run npm install better-sqlite3 --sqlite3=/path/to/my/sqlite3/amalgamation/and/defines/gypi/file, hence allowing for an easy way of defining compilation options.
As to encrypting the database, I found it easier to first encrypt the database using the command line interface as per instructions on SQLCipher's website (Example 1). Then, I could use it with better-sqlite3 by sending db.pragma("key = 'mykeyhere'") as the first statement after opening the database.
With best regards,
user20190106
(*) Please note that I'm a complete novice in this area, having done my first ever build (for DB Browser for SQLite) the day before yesterday. So the above may contain errors or wrong terminology.
          
The process used by @user20190106 can be greatly simplified by simply prepending the necessary configuration to sqlite3.c and sqlite3.h.
# First build the SQLCipher files
git clone https://github.com/sqlcipher/sqlcipher.git
cd sqlcipher
./configure --enable-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC"
make sqlite3.c
# Then prepend your custom configuration
echo "#define SQLITE_HAS_CODEC" | tee -a sqlite3.c_temp sqlite3.h_temp
echo "#define SQLITE_TEMP_STORE 2" | tee -a sqlite3.c_temp sqlite3.h_temp
cat sqlite3.c >> sqlite3.c_temp
cat sqlite3.h >> sqlite3.h_temp
mv -f sqlite3.c_temp sqlite3.c
mv -f sqlite3.h_temp sqlite3.h
# Then install better-sqlite3 with your custom amalgamation
cd /path/to/my/nodejs/project
npm install --sqlite3=/absolute/path/to/sqlcipher
          
@JoshuaWise,

a late thanks!
As I'm not that proficient in Linux commands I would have never figured out how to do it command line. And as I mainly use JavaScript,  touching upon a huge C source file was a scary thought. Editing a couple of small text files with familiar-looking syntax felt easier and safer, in particular for adding more SQLite3 options which I carefully copy-pasted from SQLite3's website.
Anyway, could you be so kind and add the instructions also to the Sqlite3 compilation page so that other users would find them more easily? Or at least add a link.
          
@JoshuaWise I had to modify the first define to:
#define SQLITE_HAS_CODEC 1 

otherwise I get this error:
Release/obj/gen/sqlite3/sqlite3.c:369:21: error: #if with no expression #if SQLITE_HAS_CODEC (repeated 4 additional times in other places)
However, compilation still fails:
CXX(target) Release/obj.target/better_sqlite3/src/better_sqlite3.o
./src/objects/database.lzz: In static member function ‘static void Database::JS_new(const v8::FunctionCallbackInfo<v8::Value>&)’:
./src/objects/database.lzz:163:55: error: ‘SQLITE_DBCONFIG_DEFENSIVE’ was not declared in this scope
./src/objects/database.lzz:163:55: note: suggested alternative: ‘SQLITE_DBCONFIG_LOOKASIDE’
./src/objects/database.lzz: In static member function ‘static void Database::JS_aggregate(const v8::FunctionCallbackInfo<v8::Value>&)’:
./src/objects/database.lzz:301:21: error: ‘sqlite3_create_window_function’ was not declared in this scope
./src/objects/database.lzz:301:21: note: suggested alternative: ‘sqlite3_create_function’
better_sqlite3.target.mk:113: recipe for target 'Release/obj.target/better_sqlite3/src/better_sqlite3.o' failed
I got rid of these by pulling the latest sqlcipher - my previous version wasn't that old, but it apparently made a difference.
          
@JoshuaWise are the following configuration options (from the docs) automatically added when using the --sqlite flag?
SQLITE_DQS=0
SQLITE_LIKE_DOESNT_MATCH_BLOBS
SQLITE_THREADSAFE=2
SQLITE_USE_URI=0
SQLITE_DEFAULT_MEMSTATUS=0
SQLITE_OMIT_DEPRECATED
SQLITE_OMIT_GET_TABLE
SQLITE_OMIT_TCL_VARIABLE
SQLITE_OMIT_PROGRESS_CALLBACK
SQLITE_OMIT_SHARED_CACHE
SQLITE_TRACE_SIZE_LIMIT=32
SQLITE_DEFAULT_CACHE_SIZE=-16000
SQLITE_DEFAULT_FOREIGN_KEYS=1
SQLITE_DEFAULT_WAL_SYNCHRONOUS=1
SQLITE_ENABLE_COLUMN_METADATA
SQLITE_ENABLE_UPDATE_DELETE_LIMIT
SQLITE_ENABLE_STAT4
SQLITE_ENABLE_FTS3_PARENTHESIS
SQLITE_ENABLE_FTS3
SQLITE_ENABLE_FTS4
SQLITE_ENABLE_FTS5
SQLITE_ENABLE_JSON1
SQLITE_ENABLE_RTREE
SQLITE_ENABLE_GEOPOLY
SQLITE_INTROSPECTION_PRAGMAS
SQLITE_SOUNDEX
EDIT: They have to be manually added.
      
TODO we have to build sqlcipher properly when running make. We need to ship our own copy of the sqlcipher amalgamation
better-sqlite3 has been temporarily removed from package.json as a workaround for getting it to build with sqlcipher
WiseLibs/better-sqlite3#188
https://github.com/JoshuaWise/better-sqlite3/blob/master/docs/compilation.md
      
TODO we have to build sqlcipher properly when running make. We need to ship our own copy of the sqlcipher amalgamation
better-sqlite3 has been temporarily removed from package.json as a workaround for getting it to build with sqlcipher
WiseLibs/better-sqlite3#188
https://github.com/JoshuaWise/better-sqlite3/blob/master/docs/compilation.md
      
TODO we have to build sqlcipher properly when running make. We need to ship our own copy of the sqlcipher amalgamation
better-sqlite3 has been temporarily removed from package.json as a workaround for getting it to build with sqlcipher
WiseLibs/better-sqlite3#188
https://github.com/JoshuaWise/better-sqlite3/blob/master/docs/compilation.md
使用的过程@user20190106可以通过简单地将必要的配置添加到sqlite3.c和来大大简化sqlite3.h。
# First build the SQLCipher files
git clone https://github.com/sqlcipher/sqlcipher.git
cd sqlcipher
./configure --enable-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC"
make sqlite3.c
# Then prepend your custom configuration
echo "#define SQLITE_HAS_CODEC" | tee -a sqlite3.c_temp sqlite3.h_temp
echo "#define SQLITE_TEMP_STORE 2" | tee -a sqlite3.c_temp sqlite3.h_temp
cat sqlite3.c >> sqlite3.c_temp
cat sqlite3.h >> sqlite3.h_temp
mv -f sqlite3.c_temp sqlite3.c
mv -f sqlite3.h_temp sqlite3.h
# Then install better-sqlite3 with your custom amalgamation
cd /path/to/my/nodejs/project
npm install --sqlite3=/absolute/path/to/sqlcipher
I follow these steps everything goes well, but i don't know what happened:
const better = new SQL(path.resolve(userDataPath, 'db/better.db'));
better.pragma('cipher_compatibility = 3');
better.pragma('key = "123"');
It still doesn't work, i can open the database directly.

package.json:
"scripts": {
    "preinstall": "npm install better-sqlite3 --sqlite3=/Users/zhangyifan/Documents/projects/sqlcipher",
    "electron-rebuild": "node -r ts-node/register ../../.erb/scripts/electron-rebuild.js",
    "postinstall": "npm run electron-rebuild && npm run link-modules",
    "link-modules": "node -r ts-node/register ../../.erb/scripts/link-modules.ts"
  "dependencies": {
    "better-sqlite3": "^7.6.2"
Or run npm install better-sqlite3 --sqlite3=/Users/***/Documents/projects/sqlcipher