super-sqlite3

super-sqlite3

Fast SQLite library with optional full db encryption, simple query builder, and a host of utility features, all in one neat package.

Table of Contents

• Credits
• Features
• Install
• Usage
• API
  • Querying
    • Binding parameters
  • Bulk statements
  • Bulk insert mode
  • Pragma
  • Count
  • Schema
  • Schema alternation
  • Encryption
  • Backup
  • Closing
• Query Builder
  • Select
    • Runners
  • Where
  • Order
  • Limit & Offset
  • Joins
    • Inner Join
    • Left Outer Join
  • Inserts
    • Insert
    • Upsert
  • Update
  • Delete
• License

Credits

super-sqlite3 is a thin wrapper around better-sqlite3-multiple-ciphers, which extends better-sqlite3 (the fastest SQLite library for node.js) with full database encryption using SQLite3MultipleCiphers. super-sqlite3 then adds its own query builder and other convenience features.

Features

• fast synchronous API (yes, it is)
• optional full database encryption
• full transaction support
• simple query builder inspired by knex.js
• db utility and convenience functions
• nice API

Install

npm install super-sqlite3

Usage

const Database = require('super-sqlite3')
const db = new Database('/path/to/file', options?)

Options:

• options.readonly open the database connection in readonly mode (default: false).
• options.fileMustExist if the database does not exist, an Error will be thrown instead of creating a new file. This option is ignored for in-memory, temporary, or readonly database connections (default: false).
• options.timeout the number of milliseconds to wait when executing queries on a locked database, before throwing a SQLITE_BUSY error (default: 5000).
• options.verbose provide a function that gets called with every SQL string executed by the database connection (default: null).
• options.nativeBinding if you're using a complicated build system that moves, transforms, or concatenates your JS files, better-sqlite3-multiple-ciphers might have trouble locating its native C++ addon (better_sqlite3.node). If you get an error that looks like this, you can solve it by using this option to provide the file path of better_sqlite3.node (relative to the current working directory).

API

Note: not all functionality of better-sqlite3 is directly exposed yet, but you can access the wrapped object instance at db.$.

Querying

db.query (aliased db.prepare) returns Statement object. Check better-sqlite3 docs for more details.

db.query(sql).run(bindParams?) // exec query
db.query(sql).get(bindParams?) // get single record
db.query(sql).all(bindParams?) // get array of records
db.query(sql).raw(bindParams?) // return data as value arrays, instead of objects

You can also use shorthands, but multiple args for binding parameters are not supported, use array or object:

db.run(sql, bindParams?) // exec query
db.get(sql, bindParams?) // get single record
db.all(sql, bindParams?) // get array of records
db.raw(sql, bindParams?) // get array of value arrays

Binding parameters

Handlers accept optional binding parameters, that are used to prevent SQL injection.

Anonymous parameters use ? sign for a placeholder:

db.query('INSERT INTO table VALUES (?, ?, ?)').run(1, 2, 3)

// all these are equivalent
.run(1, 2, 3)     // multiple args
.run([1, 2, 3])   // array
.run([1], [2, 3]) // mixed

Named parameters use @, $ or : followed by a name, and require an object:

db.query('INSERT INTO table VALUES (:foo, @bar, $baz)').run({foo:1, bar:2, baz:3})

Bulk statements

// exec multiple statements
db.exec(queries)

// exec multiple statements from file
db.execFile(pathToFile)

Bulk insert mode

Note: bulk insert mode is experimental, be cautious

In bulk insert mode, super-sqlite3 temporarily sets custom PRAGMA values, which trade safety for speed. You can view, change, remove, or add your own values, by directly manipulating the object:

console.log(db._bulkInsertPragma)

.bulkInsert takes one parameter, a function that performs the insertion. A transaction is automatically started before execution and committed immediately afterwards.

db.bulkInsert( db => {...insert...} )

Pragma

.pragma allows you to query and set PRAGMA values.

db.pragma('auto_vacuum')
// [ { auto_vacuum: 0 } ]

To get bare value, use .pragmaValue:

db.pragmaValue('busy_timeout')
// 5000

To set the value:

db.pragma('busy_timeout=3000')

Full PRAGMA list can be found here.

Count

Returns the number of rows in the specified table, or all tables if omitted. Returns object.

db.count('table') // single
// { table: 42 }
db.count(['table1', 'table2']) // multiple
// { table1: 42, table2: 420 }
db.count() // all

Schema

super-sqlite3 provides convenient functions for querying schema.

// get names of all tables
db.tables() // array

// check if table exists
db.hasTable(tableName) // bool

// get column names of a table
db.columns(tableName) // array

// get extended column information
db.columnsExt(tableName) // object

// get table schema sql
// tables - string or array of table names, returns all if omitted
db.schema(tables) // string

// get table indexes
db.indexes(tableName) // object

Schema alternation

db.altergen generates a set of queries that help to add/move/update columns while maintaining the intended column order.

db.altergen(tableName) // string

Encryption

super-sqlite3 uses ChaCha20-Poly1305 cipher algorithms, the default for SQLite3MultipleCiphers. See docs for more details.

// encrypt currently opened database
db.encrypt(key)

// PERMANENTLY decrypt the database (once unlocked)
db.decrypt()

// unlock database for a session
db.unlock(key)

// check if db is unlocked
db.isUnlocked()

Backup

Create backup of the database. Returns promise resolving to the path of the backup file created.

await db.backup(options?)

Options:

• options.file absolute path to backup file
• options.dir path to backup dir (defaults to the same dir as the current database). The filename will be derived from the current name, with an added datestamp.
• options.time add time to datestamp, defaults to true.
• options.progress see better-sqlite3 documentation.

If options are omitted, the backup file will be stored alongside the current database, using the same name with an added timestamp, so: database.sqlite => database--2027-09-14-09-50-36.sqlite.

Closing

Gracefully shut down the database connection.

db.close()

Query Builder

To use query builder, invoke db object with table name:

db(table).select(fields).where(conditions).get()

Note: query builder is only intended to help with basic queries, it is not meant to replace SQL.

Select

Select is the default mode, and can be omitted. Accepts arguments in several formats:

select('field1', 'field2')            // multiple args
select(['field1', 'field2'])          // array
select({field1:'alias', field2:true}) // object

// to alias field:
select('*', {field:'alias'})

Runners

Select type query must be executed by one of the following:

.get()    // single record
.all()    // array of records
.count()  // record count
.exists() // boolean

Where

// where
where('field', 3)
where('field', '>', 3)
where([ ['field', 3], ['field2', '>', 15] ]) // note single top arg
where({field:3, field2:'value'})

// where not
whereNot('field', 3)
whereNot({field: 3})

// where in
whereIn('field', [5, 8])

// where not in 
whereNotIn('field', [5, 8])

// where null
whereNull('field', 'field2')
whereNull(['field', 'field2'])
whereNull({field:1, field2:true, field3:false}) // field3 not included

// where not null
whereNotNull('field', 'field2')

If the table uses id as its primary key, a shorthand can be used:

// .id(n) is a shorthand to .where({id:n})
db(table).id(10).get()
// SELECT * FROM table WHERE id=10;

// when id is the only selector in select type query:
db(table).getId(10)

Order

db.order('field') // asc
db.order('field', 'asc')
db.order('field', 'desc')

Limit & Offset

db.limit(100)

db.offset(1000)

Joins

Inner Join

db(table).join(table2, field)
db(table).innerJoin(table2, field) // alias
// SELECT * FROM table INNER JOIN table2 USING (field);

db(table).join(table2, [field, field2])
// SELECT * FROM table INNER JOIN table2 USING (field, field2);

db(table).join(table2, {'table.id':'table2.tab_id'})
// SELECT * FROM table INNER JOIN table2 ON table.id=table2.tab_id;

Left Outer Join

db(table).leftJoin(table2, field)
db(table).leftOuterJoin(table2, field) // alias
// SELECT * FROM table LEFT OUTER JOIN table2 USING (field);

db(table).leftJoin(table2, [field, field2])
// SELECT * FROM table LEFT OUTER JOIN table2 USING (field, field2);

db(table).leftJoin(table2, {'table.id':'table2.tab_id'})
// SELECT * FROM table LEFT OUTER JOIN table2 ON table.id=table2.tab_id;

Inserts

Insert

.insert returns rowId on success.

db(table).insert({foo:'bar'})

Upsert

.upsert tries to update an existing record, and falls back to insert if it is not found.

db(table).upsert(data, conflict?)

// upsert
db(table).upsert({foo:'bar'})
// INSERT INTO table (foo) VALUES (?)
// ON CONFLICT DO UPDATE SET foo=excluded.foo;

db(table).upsert({foo:'bar', baz:3}, 'foo')
// INSERT INTO table (foo, baz) VALUES (?,?)
// ON CONFLICT (foo)
// DO UPDATE SET baz=excluded.baz;

db(table).upsert({one:'1st', two:2, three:'tre'}, ['one', 'two'])
// INSERT INTO table (one, two, three) VALUES (?,?,?)
// ON CONFLICT (one, two)
// DO UPDATE SET three=excluded.three;

.upsert returns result object:

{changes:1, lastInsertRowid:0}  // update
{changes:1, lastInsertRowid:42} // insert

Update

.update returns the number of affected rows.

db(table).where(cond).update(obj) // int

Delete

.delete returns the number of affected rows.

db(table).where(cond).delete() // int

License

MIT