Install using system libsqlite3-dev rather than compiling from source

[reasonableperson]

Thanks for this library, it's very easy to use. I don't know much about node-gyp so please forgive me if I've got some kind of configuration problem, but from what I can tell, installing this package with npm install means compiling a fresh copy of SQLite from the source provided in better-sqlite3/deps/sqlite3.tar.gz. Is there some way to install better-sqlite3 using a system installation of libsqlite3-dev? This would accelerate build times and make it easier to audit the code in better-sqlite3. Currently I just have to take the maintainer's word that the bundled tarball is an authentic copy of SQLite ...

[JoshuaWise]

Currently I just have to take the maintainer's word that the bundled tarball is an authentic copy of SQLite

You can verify the tarball by running:

git clone git@github.com:JoshuaWise/better-sqlite3.git
cd better-sqlite3
git checkout v5.4.0
tar -xzf deps/sqlite3.tar.gz
SUM1="$(md5sum sqlite3.c)"
sh deps/download.sh
tar -xzf deps/sqlite3.tar.gz
SUM2="$(md5sum sqlite3.c)"
printf "$SUM1\n$SUM2\n"

If the two printed md5 checksums match, the copy of SQLite3 being used is authentic. Of course, you'll also need to verify that deps/download.sh isn't doing anything malicious, but it's a simple bash script so it's pretty easy to verify.

better-sqlite3 cannot use libsqlite3-dev because libsqlite3-dev doesn't have the required compile-time options. However, you can use your own, personally provided copy of the SQLite3 source code, by following the instructions here.

If the main concern is build times, unfortunately there's no official work-around right now. I suppose technically you could cache node_modules/better-sqlite3 and copy it into node_modules as part of a preinstall script.

[reasonableperson]

Thank you very much for the verification instructions and pointer to the --sqlite3 flag. It looks like I might be able to use this to build nodegit against the SQLite source supplied by the system, by running sudo apt build-deps sqlite3 && apt source sqlite3 and building the amalgamation myself.

Thanks also for the explanation, I can see how bundling a source tarball is the easiest way to do things from better-sqlite3's perspective. I'll look further into the node build system and the required compile-time flags, and see if I can come up with a good way of caching the build.

It would be helpful if docs/compilation.md contained an explanation of the need for the various compilation flags. For now, I'll just consult the SQLite docs and see if I can figure it out.

[JoshuaWise]

@splintax Each of the compilation flags used by better-sqlite3 can be omitted in a custom build. There are two required flags, but those will automatically be added during the build process whether you like it or not (see: reference). As for what each flags does, yes you'll have to consult the SQLite3 documentation, which is also linked to from the better-sqlite3 docs.

[JoshuaWise]

I'll close this issue with a brief summary of why each compile-time flag is included in the default build of better-sqlite3:

SQLITE_THREADSAFE=0
- This flag disables multithreading. Node.js is single-threaded, so we don't need multithreading.
- Disabling multithreading improves SQLite3's performance

SQLITE_OMIT_PROGRESS_CALLBACK
SQLITE_DEFAULT_MEMSTATUS=0
- These features are not used by better-sqlite3, so we disable them with these flags
- This improves SQLite3's performance

SQLITE_OMIT_DEPRECATED
- The SQLite3 docs state that removing deprecated parts of SQLite3 is "the right thing to do"

SQLITE_OMIT_TCL_VARIABLE
SQLITE_OMIT_GET_TABLE
- These features are not used by better-sqlite3, so we omit them to achieve a smaller binary

SQLITE_TRACE_SIZE_LIMIT=32
- This changes the behavior of better-sqlite3's "verbose" option 
- With this flag, it will only log the first 32 characters of strings bound to prepared statements

SQLITE_DEFAULT_FOREIGN_KEYS=1
- SQLite3 normally disables foreigns key constraints by default, for backward compatibility
- With this flag, foreign keys are enabled. We assume most users want this.

SQLITE_DEFAULT_CACHE_SIZE=-16000
- This increases the default cache size used by SQLite3 to 16MB.
- Node.js is typically not used on embedded systems, so being more liberal here is okay.

SQLITE_DEFAULT_WAL_SYNCHRONOUS=1
- This significantly increases the performance of WAL mode
- Most Node.js applications should be using WAL mode
- Most Node.js applications don't care about the tiny amount of durability lost by using this flag
- See: https://github.com/JoshuaWise/better-sqlite3/blob/master/docs/performance.md

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_INTROSPECTION_PRAGMAS
SQLITE_SOUNDEX
- These each enable various optional features of SQLite3 that some users may need.
- For information about each feature, see here: https://www.sqlite.org/compile.html

SQLITE_USE_URI=1
SQLITE_ENABLE_COLUMN_METADATA
- These are the only two flags actually required by better-sqlite3
- The first is needed to open *named* in-memory databases, via the "memory: true" option
- The second is needed for the statement.columns() method

[reasonableperson]

Thank you very much for that summary – I'm still pretty new to libsqlite and it's helpful even aside from its relevance to better-sqlite. The fact that foreign key constraints are disabled by default explains some weirdness I've seen in the past.