sqlite-tag-spawned

sqlite-tag-spawned

^{Social Media Photo by Tomas Kirvėla on Unsplash}

The same sqlite-tag ease but without the native sqlite3 dependency, aiming to replace dblite.

import SQLiteTagSpawned from 'sqlite-tag-spawned';
// const SQLiteTagSpawned = require('sqlite-tag-spawned');

const {query, get, all, raw, transaction} = SQLiteTagSpawned('./db.sql');

// single query as any info
console.log(await query`.databases`);

// single query as SQL
await query`CREATE TABLE IF NOT EXISTS names (
  id INTEGER PRIMARY KEY,
  name TEXT
)`;

// transaction (requires .commit() to execute)
const populate = transaction();
for (let i = 0; i < 2; i++)
  populate`INSERT INTO names (name) VALUES (${'Name' + i})`;
await populate.commit();

// get single row (works with LIMIT 1 too, of course)
await get`SELECT name FROM names`;
// { name: 'Name0' }

// get all results, if any, or an empty array
await all`SELECT * FROM names`;
// [ { id: 1, name: 'Name0' }, { id: 2, name: 'Name1' } ]

// use the IN clause through arrays
const list = ['Name 0', 'Name 1'];
await all`SELECT * FROM names WHERE name IN (${list})`;

Differently from dblite

• requires SQLite 3.33 or higher (it uses the -json output mode)
• each query is a spawn call except for transactions, grouped as single spawned query
• performance still similar to sqlite3 native module
• :memory: database is based on an always same, yet runtime-once created temporary file, and it requires NodeJS 16+

API: SQLiteTagSpawned(fileName[, options])

While the fileName is just a string pointing at the db file or the string :memory:, optional options can contain the following fields:

• readonly to run queries in read only mode
• bin to specify a different sqlite3 executable
• timeout to drop the spawned process after N milliseconds
• persistent to open a DB in persistent mode (kept alive spawned command)
• exec to specify a different way to execute spawned process and results, mostly used for internal purpose