spn-sqlite

A main-thread-friendly, no-dependency SQLite library for Node.js using worker threads.

- Fully asynchronous — never blocks your event loop
- Zero native dependencies by default (uses built-in node:sqlite)
- Optional high-performance and stable driver available: better-sqlite3
- Excellent concurrency with WAL and smart worker management
- Tagged template queries with safe parameter binding
- Can Work in serverless, edge runtimes, and bundled environments

Note: node:sqlite is an experimental package, with some features added as late as Node.js v25.1+ (verified on v25.2.1).
The better-sqlite3 driver might work on earlier versions of node but I haven't tested that.
``bash
npm install spn-sqlite
`

Optional (if you want better-sqlite3 dependency):
`bash
npm install spn-sqlite better-sqlite3
`

Why spn-sqlite?

You want a SQL API that's fucking awesome to look at, and does caching and parameterized queries automatically for you.
`JavaScript
db.getSELECT * FROM users WHERE username = ${'Alice'};

// could also be written as:
db.get(["SELECT * FROM users WHERE username = ",""], "Alice");
`

JavaScript's template literals make this super easy- though the other way of writing it is kind of confusing. Essentially, anywhere you would put a ? in a parameterized query, in the template literal the string gets split there. That helpful side-effect makes it trivial to join the query back together with question marks ["SELECT FROM users WHERE username = ",""].join('?') which would give us SELECT FROM users WHERE username = ?. And then of course the values would match up as expected- so Alice and that query get sent to Sqlite and then SQLite replaces the ? with the value, blah blah boring- you've heard this before.

But- that's not all that's happening here. Everytime you write a query in your application using the node:sqlite driver, spn-sqlite automatically runs your queries in tagged statements- and that caches the parameterized queries in sqlite natively, which your users love because it makes everything load faster.

This library is for real-world usage, not numbers. It loads up workers and dispatches jobs to them automatically while keeping things First-in-First-Out, and in SQLite DBs- writes must always synchronous. Now, it is possible to load multiple databases- and spn-sqlite will happily spin up workers for each instance. So, if you have 2 DBs, then yeah you can do writes on both of them at the same time- just not multiple writes to the same SQLite db. And while all that's happening- it shouldn't ever block your main thread.


| Feature | spn-sqlite (default) | spn-sqlite + better-sqlite3 | better-sqlite3 alone |
|-------------------------------|-------------------------------|-------------------------------|----------------------|
| Async / non-blocking | Yes | Yes | No |
| Native dependencies | None | Yes (optional) | Yes |
| Serverless / edge compatible | Yes | No | No |
| Real-world concurrent perf | ~100–130 ms (forum test) | ~100–110 ms (forum test) | Slightly faster (sync) |
| Bulk insert speed (100k rows) | 70–80% of native | Nearly identical to native | Fastest |

- Use the default (node:sqlite) driver for maximum portability and async safety- it uses only node.js standard libraries..
- Switch to the better-sqlite3 driver when you want something more mainstream, less experimental, and very fast- but still in a worker thread to keep your servers accepting requests quickly.

Quick Start

`js
import DB from 'spn-sqlite';

const db = new DB;

/*

Try this:

const db = new DB({
filename: "./my_database.db",
driver: "better-sqlite"
});

*/

await db.execPRAGMA journal_mode = WAL;

await db.exec
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id TEXT PRIMARY KEY,
username TEXT UNIQUE NOT NULL
);
;

await db.runINSERT INTO users (id, username) VALUES (${'u1'}, ${'alice'});

const user = await db.getSELECT * FROM users WHERE username = ${'alice'};
console.log(user);

let all_users = await db.allSELECT * FROM users;
console.log(all_users);
await db.close(); // Gracefully shuts down
`

API

$3

`js
new DB({
filename = './my_database.db', // Database file path or ':memory:'
driver = 'node:sqlite', // 'node:sqlite', 'better-sqlite'
options = {}, // Passed directly to the underlying driver- these are specific to the respective driver; see their docs.
})
`

Driver aliases supported (case-sensitive):
- node:sqlite / node:sqlite3 → built-in driver (default)
- better-sqlite / better-sqlite3 → native driver (requires better-sqlite3 installed)

$3

All return Promises and support tagged template literals with parameter binding:

`js
await db.execCREATE TABLE ... // No parameters allowed
await db.runINSERT ... VALUES (${id}, ${name}) // Returns { changes, lastInsertRowid }
await db.getSELECT ... WHERE id = ${id} // First row or undefined
await db.allSELECT ... FROM users // Array of rows
`

$3

`js
await db.transaction(async (tx) => {
await tx.runINSERT INTO ...;
const {lastInsertRowid} = await tx.runUPDATE ...;
return await tx.getSELECT ... WHERE id = ${lastInsertRowid};
// Commits on success, rolls back on error
});
`

$3

`js
await db.close();
`
Tries to terminate all workers. Any busy workers defer closing in an attempt to prevent data loss/corruption. Anything in the queue is lost, as workers are terminated immediately after they finish the job they are working on.

Real-World Example

- node:sqlite (default): ~100–130 ms
- better-sqlite3` driver: ~100–110 ms

Both drivers handle heavy concurrency safely with zero errors and clean shutdown.

License

MIT