A wrapper library that eases the work with better-sqlite3 with some new functions and a migration-system
npm install better-sqlite3-helperA nodejs wrapper library for the work with better-sqlite3 ("The fastest and simplest library for SQLite3 in Node.js"). It's intended for simple server-apps for nodejs and offer some new functions and a migration-system.
![]()
New in Version 3.0
better-sqlite3 Version 7 is now used. This means that the option "memory" is removed (use path :memory: instead - worked in version 2 too) and support for Node.js versions < 10 is dropped. For older node versions you can continue using version 2 of this library.
Install it for example with
``bash`
npm i better-sqlite3-helper
In every file you want access to a sqlite3 database simply require the library and use it right away.
##### anyServerFile.js
`js
const DB = require('better-sqlite3-helper');
let row = DB().queryFirstRow('SELECT * FROM users WHERE id=?', userId);
console.log(row.firstName, row.lastName, row.email);
`
To setup your database, create a sql-file named 001-init.sql in a migrations-directory in the root-directory of your program.`
##### ~/migrations/001-init.sqlsqlusers
-- Up
CREATE TABLE (
id INTEGER PRIMARY KEY,
firstName TEXT NOT NULL,
lastName TEXT NOT NULL,
email TEXT NOT NULL
);
-- Down
DROP TABLE IF EXISTS users;`
And that's it!
The database loads lazy. Only when it's used for the first time, the database is read from the file, the migration is started and the journal-mode WAL is set. The default directory of the database is './data/sqlite3.db'.
If you want to change the default-values, you can do this by calling the library once in the beginning of your server-code and thus setting it up:
##### index.js
`js
const DB = require('better-sqlite3-helper');
// The first call creates the global instance with your settings
DB({
path: './data/sqlite3.db', // this is the default
readonly: false, // read only
fileMustExist: false, // throw error if database not exists
WAL: true, // automatically enable 'PRAGMA journal_mode = WAL'
migrate: { // disable completely by setting migrate: false`
force: false, // set to 'last' to automatically reapply the last migration-file
table: 'migration', // name of the database table that is used to keep track
migrationsPath: './migrations' // path of the migration-files
}
})
After that you can use the library without parameter:
##### anotherAPIFile.js
`js
const DB = require('better-sqlite3-helper');
// a second call directly returns the global instance
let row = DB().queryFirstRow('SELECT * FROM users WHERE id=?', userId);
console.log(row.firstName, row.lastName, row.email);
`
`js
// shorthand for db.prepare('SELECT * FROM users').all();
let allUsers = DB().query('SELECT * FROM users');
// result: [{id: 1, firstName: 'a', lastName: 'b', email: 'foo@b.ar'},{},...]
// result for no result: []
// shorthand for db.prepare('SELECT * FROM users WHERE id=?').get(userId);
let row = DB().queryFirstRow('SELECT * FROM users WHERE id=?', userId);
// result: {id: 1, firstName: 'a', lastName: 'b', email: 'foo@b.ar'}
// result for no result: undefined
// shorthand for db.prepare('SELECT * FROM users WHERE id=?').get(999) || {};
let {id, firstname} = DB().queryFirstRowObject('SELECT * FROM users WHERE id=?', userId);
// result: id = 1; firstName = 'a'
// result for no result: id = undefined; firstName = undefined
// shorthand for db.prepare('SELECT * FROM users WHERE id=?').pluck(true).get(userId);
let email = DB().queryFirstCell('SELECT email FROM users WHERE id=?', userId);
// result: 'foo@b.ar'
// result for no result: undefined
// shorthand for db.prepare('SELECT * FROM users').all().map(e => e.email);
let emails = DB().queryColumn('email', 'SELECT email FROM users');
// result: ['foo@b.ar', 'foo2@b.ar', ...]
// result for no result: []
// shorthand for db.prepare('SELECT * FROM users').all().reduce((o, e) => {o[e.lastName] = e.email; return o;}, {});
let emailsByLastName = DB().queryKeyAndColumn('lastName', 'email', 'SELECT lastName, name FROM users');
// result: {b: 'foo@b.ar', c: 'foo2@b.ar', ...}
// result for no result: {}
`
There are shorthands for update, insert, replace and delete. They are intended to make programming of CRUD-Rest-API-functions easier. With a blacklist or a whitelist it's even possible to send a request's query (or body) directly into the database.
js
// const numberOfChangedRows = DB().update(table, data, where, whitelist = undefined)// simple use with a object as where and no whitelist
DB().update('users', {
lastName: 'Mustermann',
firstName: 'Max'
}, {
email: 'unknown@emailprovider.com'
})
// data from a request and a array as a where and only editing of lastName and firstName is allowed
DB().update('users', req.body, ['email = ?', req.body.email], ['lastName', 'firstName'])
// update with blacklist (id and email is not allowed; only valid columns of the table are allowed) and where is a shorthand for ['id = ?', req.body.id]
DB().updateWithBlackList('users', req.body, req.body.id, ['id', 'email'])
`$3
`js
// const lastInsertID = DB().insert(table, datas, whitelist = undefined)
// const lastInsertID = DB().replace(table, datas, whitelist = undefined)// simple use with an object and no whitelist
DB().insert('users', {
lastName: 'Mustermann',
firstName: 'Max',
email: 'unknown@emailprovider.com'
})
// inserting two users
DB().insert('users', [{
lastName: 'Mustermann',
firstName: 'Max',
email: 'unknown@emailprovider.com'
}, {
lastName: 'Mustermann2',
firstName: 'Max2',
email: 'unknown2@emailprovider.com'
}])
// data from a request and only lastName and firstName are set
DB().replace('users', req.body, ['lastName', 'firstName'])
// replace with blacklist (id and email is not allowed; only valid columns of the table are allowed)
DB().replaceWithBlackList('users', req.body, ['id', 'email']) // or insertWithBlackList
`$3
`js
//delete the user with an id of 4
DB().delete('users', {id: 4})
`$3
If you want to put invalid values into the database, the functions will throw an error. So don't forget to surround the functions with a
try-catch. Here is an example for an express-server:
`js
const { Router } = require('express')
const bodyParser = require('body-parser')
const DB = require('better-sqlite3-helper')router.patch('/user/:id', bodyParser.json(), function (req, res, next) {
try {
if (!req.params.id) {
res.status(400).json({error: 'missing id'})
return
}
DB().updateWithBlackList(
'users',
req.body,
req.params.id,
['id']
)
res.statusCode(200)
} catch (e) {
console.error(e)
res.status(503).json({error: e.message})
}
})
`Migrations
The migration in this library mimics the migration system of the excellent sqlite by Kriasoft.
To use this feature you have to create a
migrations-directory in your root. Inside you create sql-files that are separated in a up- and a down-part:#####
migrations/001-initial-schema.sql`sql
-- Up
CREATE TABLE Category (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE Post (id INTEGER PRIMARY KEY, categoryId INTEGER, title TEXT,
CONSTRAINT Post_fk_categoryId FOREIGN KEY (categoryId)
REFERENCES Category (id) ON UPDATE CASCADE ON DELETE CASCADE);
INSERT INTO Category (id, name) VALUES (1, 'Business');
INSERT INTO Category (id, name) VALUES (2, 'Technology');-- Down
DROP TABLE Category
DROP TABLE Post;
`#####
migrations/002-missing-index.sql`sql
-- Up
CREATE INDEX Post_ix_categoryId ON Post (categoryId);-- Down
DROP INDEX Post_ix_categoryId;
`The files need to be numbered. They are automatically executed before the first use of the database.
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. See "Global Instance".You can also give an array of changes.
`js
const DB = require('better-sqlite3-helper')const db = new DB({
migrate: {
migrations: [
-- Up -- Down
DROP INDEX IF EXISTS Setting_index_key;
DROP TABLE IF EXISTS Setting;
,-- Up
INSERT INTO Setting (key, value, type) VALUES ('test', 'now', 0);
INSERT INTO Setting (key, value, type) VALUES ('testtest', 'nownow', 6);
-- Down
DELETE FROM Setting WHERE key = 'test';
DELETE FROM Setting WHERE key = 'testtest';
``
]
}
})