Pooled SQLite Client for Node.js Apps
A wrapper library that adds ES6 promises, an SQL-based migrations API, connection pooling, and managed transactions to sqlite3 (docs). Originally based on the sqlite library.
How to Install
$ npm install sqlite-pool --save
How to Use
This module has a similar API as the original sqlite3
library (docs), except that its equivalent API methods return ES6 Promises and do not accept callback arguments. Full API listings can be found in API.md.
Below is an example of how to use it with Node.js and Express, using Bluebird instead of native Promises:
const express = ;const Promise = ;const Sqlite = ; const app = ;const port = processenvPORT || 3000;const db = './database.sqlite' Promise ; app; // Launch Node.js appapp;
NOTE: For Node.js v5 and below use var Sqlite = require('sqlite-pool/legacy');
.
Multiple Connections
Due to the asynchronous interface which the sqlite3 Node.js library provides, isolation and query ordering is not guaranteed within any given connection. This module uses the generic-pool library to create multiple connections to an SQLite database if desired, and to queue requests made using methods of the Sqlite
class.
The minimum/maximum number of open connections can be set with the min
and max
options when calling new Sqlite()
(see the API reference for the full list of options).
Below is an example with the Sqlite.use()
method of how one connection may perform a transaction isolated from other calls to the same Sqlite
instance:
const express = ;const Promise = ;const Sqlite = ; const app = ;const port = processenvPORT || 3000;const db = './database.sqlite' Promise min: 2 max: 4 ; app; // Launch Node.js appapp;
Transactions
This module also provides managed transactions, with automatic commit or rollback. Below is a modified version of the above example, using Sqlite.transaction()
:
const express = ;const Promise = ;const Sqlite = ; const app = ;const port = processenvPORT || 3000;const db = './database.sqlite' Promise min: 2 max: 4 ; app; // Launch Node.js appapp;
Migrations
This module comes with a lightweight migrations API that works with SQL-based migration files as the following example demonstrates:
migrations/001-initial-schema.sql
-- Up ( id INTEGER PRIMARY KEY, name TEXT NOT NULL); ( id INTEGER PRIMARY KEY, categoryId INTEGER NOT NULL, title TEXT NOT NULL, views NUMERIC NOT NULL DEFAULT 0, isPublished NUMERIC NOT NULL DEFAULT 0, CONSTRAINT Post_fk_categoryId FOREIGN KEY (categoryId) REFERENCES Category (id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT Post_ck_isPublished CHECK (isPublished IN (0, 1))); INSERT INTO Category (id, name) VALUES (1, 'Business');INSERT INTO Category (id, name) VALUES (2, 'Technology'); -- Down Post; Category;
migrations/002-missing-index.sql
-- Up ON Post (categoryId); -- Down Post_ix_categoryId;
app.js
(Node.js/Express)
const express = ;const Promise = ;const Sqlite = ; const app = ;const port = processenvPORT || 3000;const db = './database.sqlite' Promise ; app; Promise // First, try to update the database schema to the latest version // Finally, launch Node.js app ;
NOTE: For the development environment, while working on the database schema, you may want to set force: 'last'
(default false
) that will force the migration API to rollback and re-apply the latest migration over again each time when Node.js app launches.
References
- SQLite Documentation, e.g. SQL Syntax, Data Types etc. on SQLite.org
License
The MIT License © 2017 Raymond Neilson. All rights reserved. Original work © 2015-2016 Kriasoft, LLC. All rights reserved.
Original library by Konstantin Tarkus (@koistya) and contributors