SQLite build flags and configuration

TL;DR: We include 8 additional SQLite features beyond Node.js (JSON, FTS4, Unicode normalization, etc.) plus security-hardened defaults (foreign keys enabled, stricter SQL parsing, larger cache). The API remains fully compatible.

This document describes the SQLite build flags used in @photostructure/sqlite, compares them with Node.js's configuration, and explains the rationale behind our choices.

Overview

Our SQLite build enables a broad set of features while maintaining security and performance. This configuration differs from Node.js's more conservative approach by including FTS4, NORMALIZE, and stricter security settings.

Build configuration files

Our build configuration: binding.gyp (lines 22-54)
Node.js SQLite configuration: sqlite.gyp (lines 15-28)

Complete build flags comparison

Core features present in both

Flag	Purpose	@photostructure/sqlite	Node.js	Notes
`SQLITE_ENABLE_COLUMN_METADATA`	Column metadata APIs	✅	✅	Required for schema introspection
`SQLITE_ENABLE_DBSTAT_VTAB`	Database statistics virtual table	✅	✅	Performance monitoring
`SQLITE_ENABLE_FTS3`	Full-text search version 3	✅	✅	Basic FTS support
`SQLITE_ENABLE_FTS3_PARENTHESIS`	Enhanced FTS3 query syntax	✅	✅	Improved query capabilities
`SQLITE_ENABLE_FTS5`	Full-text search version 5	✅	✅	Latest FTS with best performance
`SQLITE_ENABLE_GEOPOLY`	GeoJSON and polygon functions	✅	✅	Spatial data support
`SQLITE_ENABLE_MATH_FUNCTIONS`	Math functions (sin, cos, sqrt, etc.)	✅	✅	Mathematical operations
`SQLITE_ENABLE_PREUPDATE_HOOK`	Pre-update hooks for sessions	✅	✅	Change tracking support
`SQLITE_ENABLE_RBU`	Resumable Bulk Update support	✅	✅	Incremental database updates
`SQLITE_ENABLE_RTREE`	R*Tree spatial indexing	✅	✅	Spatial indexing capabilities
`SQLITE_ENABLE_SESSION`	Session and changeset support	✅	✅	Database replication features
`SQLITE_DEFAULT_MEMSTATUS=0`	Disabled memory usage tracking	✅	✅	`sqlite3_malloc()` routines run much faster
`SQLITE_ENABLE_JSON1`	JSON functions and operators	✅	✅	Modern web development requires JSON support, defaults to enabled since v3.38.0+

Additional features we include

Flag	Purpose	@photostructure/sqlite	Node.js	Rationale
`SQLITE_ENABLE_FTS4`	Full-text search version 4	✅	❌	Bridge between FTS3 and FTS5, broader compatibility
`SQLITE_ENABLE_NORMALIZE`	Unicode normalization	✅	❌	Proper Unicode handling for international apps
`SQLITE_ENABLE_SNAPSHOT`	Database snapshots	✅	❌	Advanced backup and point-in-time recovery
`SQLITE_ENABLE_STAT4`	Advanced query planner statistics	✅	❌	Better query optimization
`SQLITE_ENABLE_UPDATE_DELETE_LIMIT`	LIMIT clause on UPDATE/DELETE	✅	❌	SQL standard compliance
`SQLITE_SOUNDEX`	Soundex algorithm	✅	❌	Fuzzy string matching capabilities
`SQLITE_USE_URI=1`	URI filename support	✅	❌	Advanced database configuration via URIs

Security and safety configurations

These include the majority of SQLite's recommended compile options

Flag	Purpose	@photostructure/sqlite	Node.js	Notes
`SQLITE_DEFAULT_FOREIGN_KEYS=1`	Foreign keys enabled by default	✅	❌	Data integrity by default
`SQLITE_DQS=0`	Double-quoted strings disabled	✅	❌	Prevents SQL ambiguity
`SQLITE_DEFAULT_WAL_SYNCHRONOUS=1`	Safe WAL mode defaults	✅	❌	Durability vs performance balance
`SQLITE_OMIT_DEPRECATED`	Remove deprecated features	✅	❌	Smaller, more secure API surface
`SQLITE_OMIT_SHARED_CACHE`	Disable shared cache mode	✅	❌	Shared cache is deprecated
`SQLITE_LIKE_DOESNT_MATCH_BLOBS`	LIKE doesn't match BLOB data	✅	❌	LIKE and GLOB operators always return FALSE if either operand is a BLOB
`SQLITE_ENABLE_API_ARMOR`	Validate C-API arguments	✅	❌	Misused API calls return `SQLITE_MISUSE` instead of risking undefined behavior; defense-in-depth for hosted extensions (e.g. sqlite-vec)

SQLite threading modes and process concurrency

This build keeps SQLite's default SQLITE_THREADSAFE=1 configuration. SQLite therefore includes its mutex code and, unless an open flag overrides it, opens connections in serialized mode.

Threading mode answers a narrow question: may two threads call the same sqlite3* connection at the same time? It does not control whether separate connections or separate processes may access the same database file. SQLite's file locks, journal mode, and transaction state govern that concurrency.

Setting	Effect	Tradeoff
`SQLITE_THREADSAFE=1`	Includes mutexes and defaults connections to serialized mode	Safest global default; a connection mutex guards accidental same-handle concurrency
`SQLITE_THREADSAFE=2`	Includes mutexes but defaults connections to multi-thread mode	Avoids the connection mutex unless `SQLITE_OPEN_FULLMUTEX` overrides it; every caller must serialize each handle
`SQLITE_THREADSAFE=0`	Omits mutex code	Smallest overhead, but serialized mode cannot be restored at runtime or per connection
`SQLITE_OPEN_FULLMUTEX`	Selects serialized mode for one connection	Multiple threads may safely enter that handle; the mutex adds work at SQLite API boundaries
`SQLITE_OPEN_NOMUTEX`	Selects multi-thread mode for one connection	Different handles remain concurrent, but the application must prevent simultaneous use of this handle and its statements

The stable Database API does not pass either mutex open flag, so it inherits the serialized default. Keep that behavior: asynchronous backup uses a Database source handle on a worker thread while the JavaScript object is still alive. Changing the global default to SQLITE_THREADSAFE=2 would remove SQLite's same-handle protection from this and every other stable connection.

The experimental pool currently passes SQLITE_OPEN_FULLMUTEX explicitly. Its scheduler also ensures that only one native worker owns a pooled handle at a time, so SQLITE_OPEN_NOMUTEX may eventually be a valid targeted optimization. We retain FULLMUTEX as defense in depth until application benchmarks show that its cost matters and concurrency/lifetime tests validate the weaker setting. Setting SQLITE_THREADSAFE=2 alone would not make the pool faster because its FULLMUTEX open flag overrides the compile-time default.

PhotoStructure's two-process workload

PhotoStructure's web and sync processes open independent SQLite connections; they cannot share a sqlite3* pointer. FULLMUTEX therefore does not serialize the two processes, nor does it serialize two different handles in one pool.

For the read-heavy web process, a two-connection pool can execute two independent reads concurrently. For the sync process, extra connections do not create a second writer: SQLite still permits only one writer at a time. In WAL mode, web readers can normally overlap the sync writer. Configure busy_timeout on every connection so lock contention waits on a libuv worker instead of immediately failing. Whether a second connection helps incremental sync reads is a workload question; measure it against the extra cache, libuv-thread, and lock contention.

Platform-specific build settings

Standard build flags (all platforms)

Flag	Purpose	Notes
`NAPI_CPP_EXCEPTIONS`	N-API C++ exception support	Required for proper error handling
`NAPI_VERSION=8`	Pins the Node-API surface	8 is the header default and our ABI floor; explicit so a node-addon-api bump cannot silently widen it
`NODE_API_SWALLOW_UNTHROWABLE_EXCEPTIONS`	Drops errors that can no longer be thrown	After `process.exit()` or `terminate()` stops a worker's JavaScript, node-addon-api otherwise aborts the whole process when a failed call tries to become an `Error`, for example a getter that exits while an option is read
`HAVE_STDINT_H=1`	Standard integer types available	Cross-platform compatibility
`HAVE_USLEEP=1`	usleep() function available	Sleep functionality

Compiler and linker hardening (POSIX)

We follow the OpenSSF Compiler Options Hardening Guide. A .node addon is a shared library, so we use -fPIC and never -fPIE/-pie.

Toolchain floor matters. The shipped glibc prebuild is built in node:20-bullseye (Debian 11, GCC 10.2) so the binary loads on Ubuntu 20.04+. Every flag below was verified against that compiler, not the developer's newer one.

Flag	Scope	Purpose
`-fstack-protector-strong`	C + C++	Stack-smashing canary
`-U_FORTIFY_SOURCE -D_FORTIFY_SOURCE=2`	C + C++	Compile/run-time buffer + libc misuse checks. `=3` needs GCC 12+, so on our GCC 10.2 floor it would silently degrade to `=2` — we ask for `=2` honestly. Requires `-O1`+ (Release uses `-O3`).
`-Wformat -Wformat=2 -Werror=format-security`	C + C++	Format-string hardening. Keep all three together: GCC 10 errors on `-Werror=format-security` without `-Wformat`.
`-fstack-clash-protection`	C + C++, Linux	Probe each stack page so a large frame cannot leap the guard page. Not supported by Apple clang.
`-fcf-protection=full`	C + C++, Linux x86/x64 only	Intel CET (IBT/shadow stack). Hard-errors on arm64 — must stay arch-gated.
`-mbranch-protection=standard`	C + C++, Linux arm64 only	AArch64 PAC return-signing + BTI. Unknown option on x86 — must stay arch-gated.
`-D_GLIBCXX_ASSERTIONS`	C++, Linux	libstdc++ bounds/precondition assertions. (The libc++ analogue, `_LIBCPP_HARDENING_MODE`, is not yet available on our macOS toolchain.)
`-Wl,-z,relro -Wl,-z,now`	Linker, Linux	Full RELRO (GOT mapped read-only after load)
`-Wl,-z,noexecstack`	Linker, Linux	Non-executable stack (W^X)
`-fvisibility=hidden`	C + C++	Hide internal symbols; this addon shares a process with V8, libuv, and possibly other SQLite addons
`-fvisibility-inlines-hidden`	C++	Same, for inline functions
`-Wno-implicit-fallthrough`	C only	SQLite uses intentional switch fallthroughs. Scoped to `cflags_c` so it cannot mask an unannotated fallthrough in our C++.

The -Wl,-z,* family is GNU-ld/ELF only — macOS ld64 rejects it, so those flags are confined to the Linux branch. macOS gets PIE/ASLR and W^X from the platform.

_FORTIFY_SOURCE is disabled in sanitizer builds (scripts/sanitizers-test.sh): its libc interceptors collide with AddressSanitizer's and produce false results.

You can confirm the protections actually landed in the built artifact:

readelf -d  build/Release/phstr_sqlite.node | grep BIND_NOW   # full RELRO
readelf -lW build/Release/phstr_sqlite.node | grep GNU_STACK  # must be RW, not RWE
readelf -nW build/Release/phstr_sqlite.node | grep -i shstk   # Intel CET (x64)
readelf -sW build/Release/phstr_sqlite.node | grep _chk       # FORTIFY'd libc calls

Linux-specific performance settings

Flag	Scope	Purpose
`-fno-plt`	C++ sources only	Calls Node-API symbols through the ELF GOT instead of the procedure linkage table, removing one indirection from each row-materialization call without changing the stable Node-API ABI.

Pinned, alternating A/B measurements on the 1,000-row SELECT shape showed a 2.3% improvement for all() and 2.8% for iterate(). The flag is deliberately Linux-only; macOS and Windows builds are unchanged.

Windows-specific security settings

For Windows builds, we include extensive security features:

Both architectures get /Qspectre, /guard:cf, /ZH:SHA_256, /sdl and /DYNAMICBASE. They differ only in backward-edge control-flow integrity, which is a genuine hardware difference:

Protection	x64	ARM64
Spectre v1 mitigation	`/Qspectre`	`/Qspectre`
Forward-edge CFI	`/guard:cf`	`/guard:cf`
Backward-edge CFI (ROP)	`/CETCOMPAT` (Intel CET)	`/guard:signret` (hardware PAC)
ASLR	`/DYNAMICBASE`	`/DYNAMICBASE`
Source-file hash	`/ZH:SHA_256`	`/ZH:SHA_256`
SDL checks	`/sdl`	`/sdl`
Stack cookie, DEP, 64-bit ASLR	`/GS`, `/NXCOMPAT`, `/HIGHENTROPYVA` — on by default, not passed explicitly

Two corrections to beliefs this document previously encoded:

/Qspectre is not x64-only. MSVC has supported it on ARM/ARM64 since VS 2017 15.7 and ships Spectre-mitigated ARM64 libraries (docs). Omitting it left the ARM64 build under-hardened.
/guard:cf is forward-edge only. ARM64 does not get backward-edge protection "for free" from PAC — it must be requested with /guard:signret (verified accepted by MSVC 19.44 / VS 2022 targeting ARM64).

/CETCOMPAT genuinely is x64-only (CET shadow-stack is an Intel/AMD feature), so its absence on ARM64 is correct.

Rationale for extra features

1. JSON support (`SQLITE_ENABLE_JSON1`)

Modern web applications use JSON extensively
Enables efficient JSON storage and querying
Faster than external JSON parsing
Many other SQLite distributions include this

2. FTS4 (`SQLITE_ENABLE_FTS4`)

Bridge between FTS3 and FTS5 for broader compatibility
Supports applications migrating from older FTS versions
Minimal overhead when not used

3. Unicode normalization (`SQLITE_ENABLE_NORMALIZE`)

Proper Unicode handling for international applications
Prevents Unicode-related data corruption
Required for applications with non-ASCII text

4. Security defaults

Foreign Keys: Enabled by default to prevent data integrity issues
DQS=0: Prevents ambiguous SQL parsing
WAL Synchronous: Balanced durability and performance

5. Performance features

STAT4: Better query optimization with column statistics
16MB Cache: Larger default cache for better performance
Serialized Threading Default: Protects a connection used across threads; independent handles and processes remain concurrent

Features intentionally omitted

These SQLite features are available but not enabled in our build:

Feature	Reason for omission
`SQLITE_ENABLE_ICU`	Adds large ICU dependency, platform-specific
`SQLITE_ENABLE_MEMSYS3/5`	Alternative allocators not needed
`SQLITE_ENABLE_UNLOCK_NOTIFY`	Primarily for embedded systems
`SQLITE_ENABLE_ATOMIC_WRITE`	Platform-specific, limited benefit

Security implications

Enabled security features

Foreign Key Enforcement: Prevents referential integrity violations
DQS=0: Eliminates double-quoted string ambiguity
Deprecated Feature Removal: Reduces attack surface
Safe WAL Defaults: Balances performance with data durability

Potential security considerations

JSON Functions: Could potentially be used for data exfiltration (mitigated by proper application design)
Extension Loading: Disabled by default in our implementation
User-Defined Functions: Properly sandboxed in our implementation

Performance implications

Performance benefits

Larger Cache: 16MB default vs 2MB improves read performance
STAT4: Better query optimization with advanced statistics
Independent Connection Concurrency: Separate pool handles and processes can run concurrently; WAL and file locks, not connection mutex mode, govern database-level overlap
JSON Functions: Faster than external JSON parsing

Performance considerations

Binary Size: Additional features increase library size by ~200KB
Memory Usage: More features mean higher base memory consumption
Compilation Time: More features extend build time
API Armor: SQLITE_ENABLE_API_ARMOR adds argument-validation checks at the C-API boundary. Benchmarking it on vs. off across SELECT/INSERT/BLOB workloads (30 trials each) showed all differences within run-to-run noise (~1–2%, with no consistent direction) — i.e. no measurable runtime cost, since the checks are never-taken branches on well-formed calls.

Customizing build flags

To modify build flags for your specific use case:

1. Fork and modify

# Clone the repository
git clone https://github.com/photostructure/node-sqlite.git
cd node-sqlite

# Edit binding.gyp - modify the "defines" array
# Lines 22-54 contain the SQLite build flags

# Rebuild
npm run clean
npm run build:native

2. Common customizations

Minimal build (remove features):

# Remove optional features for smaller binary
# Comment out or remove these lines:
"SQLITE_ENABLE_FTS4",
"SQLITE_ENABLE_JSON1",
"SQLITE_SOUNDEX",
"SQLITE_ENABLE_NORMALIZE",

Maximum features (add more features):

# Add these to the defines array:
"SQLITE_ENABLE_ICU",           # Requires ICU library
"SQLITE_ENABLE_ATOMIC_WRITE",  # Platform-specific

Performance tuning:

# Larger cache for memory-rich environments
"SQLITE_DEFAULT_CACHE_SIZE=-32000",  # 32MB cache

# Or smaller cache for memory-constrained environments
"SQLITE_DEFAULT_CACHE_SIZE=-4000",   # 4MB cache

Maintenance and sync

Keeping documentation in sync

When modifying build flags in binding.gyp:

Update this document with new flags and rationale
Update features.md with user-facing feature descriptions
Test thoroughly to ensure new flags work correctly
Update comparison tables with any Node.js changes

Monitoring upstream changes

We regularly check Node.js's sqlite.gyp for changes:

Automated sync: scripts/sync-from-node.ts monitors Node.js changes
Manual review: Quarterly review of Node.js SQLite configuration
Issue tracking: GitHub issues track when Node.js adds new flags

Links for reference

SQLite Compile-Time Options: https://sqlite.org/compile.html
SQLite Build Configuration: https://sqlite.org/howtocompile.html
Node.js SQLite Source: https://github.com/nodejs/node/tree/main/deps/sqlite
Our Build Configuration: https://github.com/photostructure/node-sqlite/blob/main/binding.gyp

Navigation Menu

Repository navigation

SQLite build flags and configuration

Overview

Build configuration files

Complete build flags comparison

Core features present in both

Additional features we include

Security and safety configurations

SQLite threading modes and process concurrency

PhotoStructure's two-process workload

Platform-specific build settings

Standard build flags (all platforms)

Compiler and linker hardening (POSIX)

Linux-specific performance settings

Windows-specific security settings

Rationale for extra features

1. JSON support (`SQLITE_ENABLE_JSON1`)

2. FTS4 (`SQLITE_ENABLE_FTS4`)

3. Unicode normalization (`SQLITE_ENABLE_NORMALIZE`)

4. Security defaults

5. Performance features

Features intentionally omitted

Security implications

Enabled security features

Potential security considerations

Performance implications

Performance benefits

Performance considerations

Customizing build flags

1. Fork and modify

2. Common customizations

Maintenance and sync

Keeping documentation in sync

Monitoring upstream changes

Links for reference

FilesExpand file tree

build-flags.md

Latest commit

History

build-flags.md

File metadata and controls

SQLite build flags and configuration

Overview

Build configuration files

Complete build flags comparison

Core features present in both

Additional features we include

Security and safety configurations

SQLite threading modes and process concurrency

PhotoStructure's two-process workload

Platform-specific build settings

Standard build flags (all platforms)

Compiler and linker hardening (POSIX)

Linux-specific performance settings

Windows-specific security settings

Rationale for extra features

1. JSON support (SQLITE_ENABLE_JSON1)

2. FTS4 (SQLITE_ENABLE_FTS4)

3. Unicode normalization (SQLITE_ENABLE_NORMALIZE)

4. Security defaults

5. Performance features

Features intentionally omitted

Security implications

Enabled security features

Potential security considerations

Performance implications

Performance benefits

Performance considerations

Customizing build flags

1. Fork and modify

2. Common customizations

Maintenance and sync

Keeping documentation in sync

Monitoring upstream changes

Links for reference

1. JSON support (`SQLITE_ENABLE_JSON1`)

2. FTS4 (`SQLITE_ENABLE_FTS4`)

3. Unicode normalization (`SQLITE_ENABLE_NORMALIZE`)