An SQLite binding for node.js with built-in encryption, focused on simplicity and (async) performance

Description

An SQLite (more accurately SQLite3MultipleCiphers) binding for node.js focused on simplicity and (async) performance.

When dealing with encrypted sqlite databases, this binding only supports the ChaCha20-Poly1305 cipher to keep things simple, secure, and working well across multiple platforms.

Available/Relevant special PRAGMAs:

Table of Contents

Implementation/Design Notes

The goal of this addon/binding is to provide a simple and consistent interface for interacting with SQLite databases. What that means on a technical level is:

  • Only synchronous opening and closing of databases

    • Why? To simplify things. Opening and closing should be fast enough and are typically not done that often anyway.
  • Only async queries, which are processed in a queue

    • Why? Async because queries could easily have the potential to disrupt the node.js event loop. A per-connection queue is used because of the threading model used with SQLite, which not only avoids a lot of extra mutexes but also avoids various race conditions that can still occur even with SQLite in a serialized/"thread-safe" threading model.
  • Only strings, null, and Buffers for column values

    • Why? To provide a consistent set of data types without any "gotchas." In particular there is no awkward number value handling that plagues a lot of node.js database bindings in general due to JavaScript's use of a double type for its numbers (although there is built-in bigint now, it is a separate type and can't be used with regular JavaScript numbers very easily).

      Some bindings deal with this problem by allowing you to configure number-handling behavior, however in general that ultimately means you will probably end up adding some kind of type checking and whatnot when processing query results to support different configurations.

  • Only SQLite's UTF-8 APIs are used/supported

    • Why? To be clear, this doesn't mean databases utilizing UTF-16 can't be used with this addon, it just means that SQLite will be forced to do some transformations that would ordinarily be unnecessary with a database that used UTF-8 for string values from the get-go. This incurs additional overhead when executing queries. Also, SQLite has some APIs that only accept UTF-8 strings anyway so it makes even more sense from a consistency perspective.

Performance

When discussing performance (particularly node.js sqlite driver performance), it's important to reiterate that your mileage may vary and that it mostly boils down to how the sqlite database is accessed. Specifically I'm referring to synchronous vs. asynchronous. Both have their advantages and disadvantages and have different scaling properties.

Because esqlite only provides an async API and the fact that sqlite directly accesses the disk, it means queries run in the thread pool to ensure the main thread is not blocked. With other types of databases where you make a network connection to the database, this is unnecessary and can be done without the thread pool (and without writing/using C/C++ code) because you're simply waiting for I/O, which node.js can easily and more efficiently do.

With that in mind, what this means is that for some workloads, synchronous queries will perform better than asynchronous queries because of the overhead of queueing work to the thread pool and the additional copying of results because you cannot access V8 APIs from threads in a node addon.

For benchmarking, I generated a single, unencrypted database with 100k records. The schema looked like:

CREATE TABLE data (
  ID INT,
  EmailAddress VARCHAR(500),
  FirstName VARCHAR(500),
  LastName VARCHAR(500),
  IPAddress VARCHAR(500),
  Age INT
)

The node.js version benchmarked with here was v16.10.0. I did however notice that older node.js branches (e.g. v10.x) performed better, for example: esqlite was ~20ms faster in the 100k row fetching benchmark with node v10.22.1 compared to node v16.10.0.

The sqlite packages being benchmarked:

Package Version
better-sqlite3 7.4.3
esqlite 0.0.1
sqlite3 5.0.2
sqlite3 (PR) 5.0.2

Here is the code and the results for a couple of different queries that I ran on my Linux desktop:

  • SELECT * FROM data (retrieves all 100k rows)

    • Code

      • better-sqlite3
        const openDB = require('better-sqlite3');
        const db = openDB('/tmp/test.db', { readonly: true });
        
        console.time('select');
        db.prepare('SELECT * FROM data').all();
        console.timeEnd('select');
        db.close();
      • esqlite
        const { Database, OPEN_FLAGS } = require('esqlite');
        const db = new Database('/tmp/test.db');
        db.open(OPEN_FLAGS.READONLY);
        
        console.time('select');
        db.query('SELECT * FROM data', () => {
          console.timeEnd('select');
          db.close();
        });
      • sqlite3 / sqlite3 (PR)
        const sqlite3 = require('sqlite3');
        const db = new sqlite3.Database('/tmp/test.db', sqlite3.OPEN_READONLY);
        
        console.time('select');
        db.all('SELECT * FROM data', () => {
          console.timeEnd('select');
          db.close();
        });
    • Results

      Package Average time (ms) Average max RSS (MB)
      better-sqlite3 350 89
      esqlite 200 117
      sqlite3 760 160
      sqlite3 (PR) 390 132
  • SELECT * FROM data LIMIT 1000

    • Code same as before, but with the SQL string changed appropriately

    • Results

      Package Average time (ms) Average max RSS (MB)
      better-sqlite3 3 38.0
      esqlite 3 35.0
      sqlite3 10 42.0
      sqlite3 (PR) 5 41.5
  • SELECT * FROM data LIMIT 10

    • Code same as before, but with the SQL string changed appropriately

    • Results

      Package Average time (ms) Average max RSS (MB)
      better-sqlite3 0.300 38
      esqlite 1.000 34
      sqlite3 0.800 41
      sqlite3 (PR) 0.800 41

Requirements

Installation

npm install esqlite

Examples

  • Create/Open an encrypted database
const { Database } = require('esqlite');

const db = new Database('/path/to/database');
db.open();
db.query(`PRAGMA key = 'my passphrase'`, (err) => {
  if (err) throw err;

  // Perform queries as normal ...

  // ... and eventually close the database
  db.close();
});
  • Binding values
const { Database } = require('esqlite');

const db = new Database('/path/to/database');
db.open();

// Using nameless/ordered parameters
db.query('SELECT * FROM posts WHERE id = ?', [1234], (err, rows) => {
  if (err) throw err;

  db.close();
});

// Using named parameters
const values = { id: 1234 };
db.query('SELECT * FROM posts WHERE id = :id', { values }, (err, rows) => {
  if (err) throw err;

  db.close();
});

API

Exports

  • Database - A class that represents a connection to an SQLite database.

  • OPEN_FLAGS - object - Contains various flags that can be passed to database.open():

    • CREATE - The database is created if it does not exist.
    • MEMORY - The database will be opened as an in-memory database. The database is named by the filename argument passed to the Database constructor for the purposes of cache-sharing if shared cache mode is enabled, otherwise the filename is ignored.
    • NOFOLLOW - When opening the database, the database path is not allowed to be a symbolic link.
    • PRIVATECACHE - The database is opened with shared cache disabled.
    • READONLY - The database is opened in read-only mode. If the database does not already exist, an error is thrown.
    • READWRITE - The database is opened for reading and writing if possible, or reading only if the file is write protected by the operating system. In either case the database must already exist, otherwise an error is thrown.
    • SHAREDCACHE - The database is opened with shared cache enabled.
  • PREPARE_FLAGS - object - Contains various flags related to query preparation that can be passed to query():

    • NO_VTAB - Causes the query to fail if the statement uses any virtual tables.
  • version - string - Contains the SQLite and SQLite3MultipleCiphers versions.


Database methods

  • Database(< string >path) - Creates a new Database object for operating on the database located at path.

  • autoCommitEnabled() - boolean - Returns whether the opened database currently has auto-commit enabled.

  • close() - (void) - Closes the database.

  • interrupt(< function >callback) - (void) - Interrupts the currently running query. callback has no arguments and is called after any query has been interrupted.

  • open([ < integer >flags ]) - (void) - Opens the database with optional flags whose values come from OPEN_FLAGS. Default flags: CREATE | READWRITE

  • query(< string >sql[, < object >options][, < array >values][, < function >callback) - Executes the statement(s) in sql. options may contain:

    • prepareFlags - integer - Flags to be used during preparation of the statement(s) whose values come from PREPARE_FLAGS. Default: (no flags)

    • single - boolean - Whether only a single statement should be executed from sql. This can be useful to help avoid some SQL injection attacks. Default: true

    • values - mixed - Either an object containing named bind parameters and their associated values or an array containing values for nameless/ordered bind parameters. Default: (none)

    If using nameless/ordered values, then an array values may be passed directly in query().

    If an error occurs while preparing/parsing a statement, further processing of sql stops immediately (only relevant when options.single === false).

    callback is called when zero or more of the statement(s) finish and has the signature (err, rows). In the case of a single statement, err is a possible Error instance and rows is a possible array of rows returned from the statement. In the case of multiple statements, if any one of the statements ended in an error, then err will be an array. If there was no error, rows will contain a 2D array of rows, one set of rows per statement. It is possible that the length of err and/or rows will not equal the number of statements if there was a fatal error that halted execution of any further statements.

Similar Resources

An updated fork of sqlite_protobuf, a SQLite extension for extracting values from serialized Protobuf messages.

This fork of sqlite_protobuf fixes some issues (e.g., #15) and removes the test suite that we do not use. It also comes with proto_table, a C library

Oct 19, 2022

Verneuil is a VFS extension for SQLite that asynchronously replicates databases to S3-compatible blob stores.

Verneuil is a VFS extension for SQLite that asynchronously replicates databases to S3-compatible blob stores.

Verneuil: streaming replication for sqlite Verneuil1 [vɛʁnœj] is a VFS (OS abstraction layer) for sqlite that accesses local database files like the d

Nov 27, 2022

C++ ORM for SQLite

Hiberlite ORM C++ object-relational mapping with API inspired by the awesome Boost.Serialization - that means almost no API to learn. Usage Just compi

Dec 1, 2022

The C++14 wrapper around sqlite library

sqlite modern cpp wrapper This library is a lightweight modern wrapper around sqlite C api . #includeiostream #include sqlite_modern_cpp.h using n

Nov 12, 2022

Unofficial git mirror of SQLite sources (see link for build instructions)

SQLite Source Repository This repository contains the complete source code for the SQLite database engine. Some test scripts are also included. Howeve

Dec 3, 2022

Lightweight C++ wrapper for SQLite

NLDatabase Lightweight C++ wrapper for SQLite. Requirements C++11 compiler SQLite 3 Usage Let's open a database file and read some rows: #include "NLD

Sep 20, 2019

Writing a sqlite clone from scratch in C++

如何用C++实现一个简易数据库 基于cstack/db_tutorial C语言版本 KCNyu 2022/2/2 作为笔者写的第一个系列型教程,还是选择基于前人的教程经验以及添加一些自己个人的探索。也许有很多纰漏之处,希望大家指正。 1. 数据库是什么? 数据库是“按照数据结构来组织、存储和管理数

Dec 1, 2022

The database built for IoT streaming data storage and real-time stream processing.

The database built for IoT streaming data storage and real-time stream processing.

The database built for IoT streaming data storage and real-time stream processing.

Dec 1, 2022

Money Manager Ex is an easy to use, money management application built with wxWidgets

Money Manager Ex is an easy to use, money management application built with wxWidgets

Money Manager Ex Money Manager Ex is a free, open-source, cross-platform, easy-to-use personal finance software. It primarily helps organize one's fin

Dec 1, 2022
Related tags
React-native-quick-sqlite - ⚡️ The fastest SQLite implementation for react-native.
React-native-quick-sqlite - ⚡️ The fastest SQLite implementation for react-native.

React Native Quick SQLite The **fastest** SQLite implementation for react-native. Copy typeORM patch-package from example dir npm i react-nati

Nov 29, 2022
A PostgreSQL extension providing an async networking interface accessible via SQL using a background worker and curl.

pg_net is a PostgreSQL extension exposing a SQL interface for async networking with a focus on scalability and UX.

Nov 25, 2022
C++11 Lightweight Redis client: async, thread-safe, no dependency, pipelining, multi-platform
C++11 Lightweight Redis client: async, thread-safe, no dependency, pipelining, multi-platform

C++11 Lightweight Redis client: async, thread-safe, no dependency, pipelining, multi-platform

Dec 3, 2022
A friendly and lightweight C++ database library for MySQL, PostgreSQL, SQLite and ODBC.

QTL QTL is a C ++ library for accessing SQL databases and currently supports MySQL, SQLite, PostgreSQL and ODBC. QTL is a lightweight library that con

Dec 2, 2022
Fork of sqlite4java with updated SQLite and very basic compiler hardening enabled.

Download latest version: sqlite4java-392 with SQLite 3.8.7, Windows/Linux/Mac OS X/Android binaries OSGi bundle 1.0.392 with sqlite4java-392 Files for

Oct 26, 2022
Serverless SQLite database read from and write to Object Storage Service, run on FaaS platform.

serverless-sqlite Serverless SQLite database read from and write to Object Storage Service, run on FaaS platform. NOTES: This repository is still in t

May 12, 2022
A hook for Project Zomboid that intercepts files access for savegames and puts them in an SQLite DB instead.

ZomboidDB This project consists of a library and patcher that results in file calls for your savegame(s) being transparently intercepted and redirecte

Aug 27, 2022
❤️ SQLite ORM light header only library for modern C++
❤️ SQLite ORM light header only library for modern C++

SQLite ORM SQLite ORM light header only library for modern C++ Status Branch Travis Appveyor master dev Advantages No raw string queries Intuitive syn

Nov 29, 2022
SQLean: all the missing SQLite functions

SQLite has very few functions compared to other DBMS. SQLite authors see this as a feature rather than a bug, because SQLite has extension mechanism in place.

Nov 26, 2022
Yet another SQLite wrapper for Nim

Yet another SQLite wrapper for Nim Features: Design for ARC/ORC, you don’t need to close the connection manually Use importdb macro to create helper f

Jul 4, 2022