SQLite
The @databases/sqlite
library provides a safe and convenient API for querying SQLite databases in node.js.
N.B. you should only have one process connected to a given SQLite database at a time.
Usage
import connect, {sql} from '@databases/sqlite';
// or in CommonJS:
// const connect = require('@databases/sqlite');
// const {sql} = require('@databases/sqlite');
const db = connect();
db.query(sql`SELECT * FROM users;`).then(
(results) => console.log(results),
(err) => console.error(err),
);
For details on how to build queries, see Building SQL Queries
API
connect(fileName)
Create a database connection for a given database. You should only create one connection per database for your entire applicaiton. Normally this means having one module that creates and exports the connection pool.
In memory:
import connect from '@databases/sqlite';
const db = connect();
File system:
import connect from '@databases/sqlite';
const db = connect(FILE_NAME);
The Database
inherits from DatabaseTransaction
, so you call Database.query
directly instead of having to create a transaction for every query. Since SQLite has very limited support for actual transactions, we only support running one transaction at a time, but multiple queries can be run in parallel. You should therefore only use transactions when you actually need them.
Connection.query(SQLQuery): Promise<any[]>
Run an SQL Query and get a promise for an array of results.
Connection.queryStream(SQLQuery): AsyncIterable<any>
Run an SQL Query and get an async iterable of the results. e.g.
for await (const record of db.queryStream(sql`SELECT * FROM massive_table`)) {
console.log(result);
}
Connection.tx(fn): Promise<T>
Executes a callback function as a transaction, with automatically managed connection.
When invoked on the ConnectionPool object, the method allocates the Connection from the pool, executes the callback, and once finished - releases the connection back to the pool. However, when invoked inside another task or transaction, the method reuses the parent Connection.
A transaction wraps a regular task with additional queries:
it executes
BEGIN
just before invoking the callback functionit executes
COMMIT
, if the callback didn't throw any error or return a rejected promiseit executes
ROLLBACK
, if the callback did throw an error or return a rejected promise
const result = await db.tx(async (transaction) => {
const resultA = await transaction.query(sql`SELECT 1 + 1 AS a`);
const resultB = await transaction.query(sql`SELECT 1 + 1 AS b`);
return resultA[0].a + resultB[0].b;
});
// => 4
ConnectionPool.dispose(): Promise<void>
Dispose the connection pool. Once this is called, any subsequent queries will fail.