ParaQL

ParaQL is a mad-science experiment combining libSQL (an open-contribution SQLite fork with native vector support) with Autobase (or to be more precise, it's next-gen iteration Autobee) for massively-parallel multi-writer access.

It logs every write in a per-instance append-only log (oplog) and applies them in deterministic order to a shared view (the database) ensuring no corruption can occur. Performance is impressive, it blows vanilla SQLite out of the water, in part due to flushing to disk less often. That's not an issue though, because by the time database write occurs the operation is already in the oplog, so in case crashes or corruption it can simply be reapplied. Peers who haven't written to the database simply fast-forward (download) the latest database version which on a good connection is even faster.

The oplog based design means that disk space used is roughly double (after compaction) that of vanilla SQLite. Operating requirements are considerably higher, because Autobee uses RocksDB as the storage backend and RocksDB doesn't free deleted data immediately as a performance optimization. Periodic compaction, both automatic, and manual, makes this an easily solvable issue.

For a better idea of how ParaQL performs and compares to other solutions see the benchmark.

ParaQL is developed on Bare but it's also tested on Node. Because Bare is multi-platform and supports mobile operating systems as first class citizens, ParaQL runs on recent versions of Android, iOS, macOS, Linux, and Windows.

ParaQL supports encrypting the database with a 256-bit key, both on disk and in transport (meaning remote peers need to know the key to read or write into the database). The local-only temporary files are currently encrypted with unique but not random nonces. This is something we're still working on.

ParaQL has native support for vector data types and vector search functions with optional indexing. This is courtesy of libSQL and one of the primary reasons ParaQL was made: to support vector similarity search in P2P context.

Install

npm i @bullet./paraql

Usage

const Corestore = require("corestore")
const ParaQL = require("@bullet./paraql")

const store = new Corestore("./paraql")
const db = new ParaQL(store)

await db.exec(`
  CREATE TABLE people (id INTEGER PRIMARY KEY, name TEXT);
  INSERT INTO people (name) VALUES ('Alice'), ('Bob');
`)

const select = await db.prepare("SELECT id, name FROM people ORDER BY id")

for await (const row of select.iterate()) {
  console.log(row)
}

await select.finalize()
await db.close()

Prints:

{ id: 1, name: "Alice" }
{ id: 2, name: "Bob" }

API

const db = new ParaQL(store[, key][, options])

Creates a new database or an instance of an existing database if key is provided. store is an instance of Corestore used for storage. key is either null or 32-byte key of the database.

options include:

options = {
  name: "paraql.db",
  authorize: null,
  keyPair: null,
  encrypted: false,
  encryptionKey: null,
}

These options should only be used at database creation and set for every instance of the database. Option mismatch may lead to database corruption and/or sync issues.

name is the name of the database file, also used as a prefix for temporary files.

authorize is either null or an (asynchronous) authorizer callback with the following signature:

;(
  key: Buffer,
  action: ParaQL.AUTHORIZE_ACTION,
  param1: string | null,
  param2: string | null,
  param3: string | null,
  param4: string | null,
) => Promise<boolean>

where key is the local key of the writer attempting the operation, action is numeric action code defined in ParaQL.AUTHORIZE_ACTION enum, and params 1 through 4 are action specific strings (e.g. table name) or null.

Do note that operations initiated by initial instance (root node) and all read-only operations are always allowed and authorizer callback is NOT invoked for them.

keyPair, if provided, is the signing key pair for the local writer in the form { publicKey: <32-byte Buffer>, secretKey: <32-byte Buffer> } .

If encrypted is true and encryptionKey is provided as 32-byte buffer, it is used to encrypt the database.

db.name

The name of the database file.

db.key

The database key used for replication. null before db.ready() is called.

db.local

The key of the local writer. Pass this to db.addWriter() to grant write access. null before db.ready() is called.

db.discoveryKey

The discovery key of the database, can be used e.g. as the topic for replicating over Hyperswarm. null before db.ready() is called.

encryptionKey

The key used to encrypt the database or null. null before db.ready() is called.

writable

Whether this instance has write access to the database. false if db.addWriter() hasn't been called with this instances local key or if db.ready() hasn't been called.

encrypted

Whether this database is encrypted.

await db.ready()

Initialized the database. All methods call this implicitly, so unless you need to access some instance property early, there's no need to call this yourself.

await db.close()

Closes the database and cleans up all used resources. Does not close the Corestore instance.

await db.addWriter(key)

Grant another instance of the database write access. key should be the local key of the remote instance. db needs to be writable.

await db.removeWriter(key)

Revoke write access from another instance of the database. key should be the local key of the remote instance. db needs to be writable.

db.replicate(isInitiatorOrStream)

Creates a replication stream that can be piped over any streamable transport. isInitiatorOrStream can be a boolean indicating whether this instance initiated Noise handshake, or another replication stream.

await db.compact()

Compacts database and removes stale data. This operation is local only and can reduce disk space usage by up to 30x or more depending on the data stored and settings of the database. Compaction happens automatically but you might want to run this periodically, when idle.

const info = await db.info()

Get information about disk space usage. Returned object has all properties in bytes and looks like this:

{
    database: number,
    temporary: number,
    total: number,
}

await db.exec(sql)

Execute given SQL statement(s) without checking return values.

This is a convenience method.

const stmt = await db.prepare(sql)

Prepare SQL statement stmt from the first statement in sql. If sql contains more than one statement tailing statements are discarded.

stmt.sourceSQL

The SQL string used to initialize this prepared statement.

await stmt.finalize()

Finalize a statement freeing up resources used.

const rows = await stmt.all(...params)

Execute a statement with given positional params. First param may optionally be an object containing named params.

Returns an array of row objects keyed by column name.

const row = await stmt.get(...params)

Same as stmt.all() except it only returns the first row.

const result = await stmt.run(...params)

Execute a statement with given params and return an object in the form { changes: number, lastInsertRowid: number }.

for await (const row of stmt.iterate(...params))

Execute a statement with given params and return rows one by one.

const buffer = await db.serialize()

Return an unencrypted, uncompressed, serialized version of the database that can be (re-)used with any SQLite3 compatible application or library.

const db = await ParaQL.deserialize(buffer)

Initiate a new ParaQL instance with any SQLite3 compatible database contained in the buffer.

License

Apache-2.0