A wrapper library that eases the work with better-sqlite3 with some new functions and a migration-system
Installations
npm install @beenotung/better-sqlite3-helper
Developer Guide
Typescript
No
Module System
CommonJS
Node Version
20.12.2
NPM Version
10.8.1
Score
68.8
Supply Chain
97.6
Quality
79.4
Maintenance
100
Vulnerability
99.3
License
Releases
Unable to fetch releases
Contributors
Unable to fetch Contributors
Languages
JavaScript (100%)
validate.email 🚀
Verify real, reachable, and deliverable emails with instant MX records, SMTP checks, and disposable email detection.
Developer
Download Statistics
Total Downloads
6,118
Last Day
3
Last Week
26
Last Month
319
Last Year
4,679
GitHub Statistics
MIT License
132 Commits
1 Watchers
11 Branches
1 Contributors
Updated on Feb 19, 2025
Bundle Size
23.69 kB
Minified
7.16 kB
Minified + Gzipped
Package Meta Information
Latest Version
4.1.7
Package Id
@beenotung/better-sqlite3-helper@4.1.7
Unpacked Size
50.43 kB
Size
12.05 kB
File Count
6
NPM Version
10.8.1
Node Version
20.12.2
Published on
Jun 17, 2024
Total Downloads
Cumulative downloads
Total Downloads
6,118
Last Day
-25%
3
Compared to previous day
Last Week
-67.9%
26
Compared to previous week
Last Month
6.3%
319
Compared to previous month
Last Year
253.7%
4,679
Compared to previous year
Daily Downloads
Weekly Downloads
Monthly Downloads
Yearly Downloads
Dependencies
1
Peer Dependencies
1
Dev Dependencies
9
Optional Dependencies
1
@beenotung/better-sqlite3-helper
A 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.
This package is forked from better-sqlite3-helper by Kauto. Most changes were back-ported but some pull requests are still pending.
New in Version 4.x
- breaking: forked into
@beenotung/better-sqlite3-helper
for npm release - breaking: better-sqlite3 Version 9 is now used.
- feat:
@types/better-sqlite3
is added as optional and peer dependencies - feat: auto setup synchronous pragma when establish connection to database
- patch: fix type signature for creating new instance of DB
- chore: no longer depends on
mkdirp
, using the built-in version fromfs
instead
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.
New in Version 2.0
All commands of better-sqlite3 Version 5 (like function and backup) can now be used too. Commands for Version 4 are removed. In addition there is now a TypeScript Declaration File for this library.
How to install
Install it for example with
1npm i @beenotung/better-sqlite3-helper
How to use
In every file you want access to a sqlite3 database simply require the library and use it right away.
anyServerFile.js
1const DB = require('@beenotung/better-sqlite3-helper') 2 3let row = DB().queryFirstRow('SELECT * FROM users WHERE id=?', userId); 4console.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.sql
1-- Up 2CREATE TABLE `users` ( 3 id INTEGER PRIMARY KEY, 4 firstName TEXT NOT NULL, 5 lastName TEXT NOT NULL, 6 email TEXT NOT NULL 7); 8 9-- Down 10DROP TABLE IF EXISTS `users`;
And that's it!
One global instance
A normal, simple application is mostly working with only one database. To make the class management more easy, this library does the access-control for you - mainly as a singleton. (But you can create a new instance to access other databases.)
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
1const DB = require('@beenotung/better-sqlite3-helper')
2
3// The first call creates the global instance with your settings
4DB({
5 path: './data/sqlite3.db', // this is the default
6 readonly: false, // read only
7 fileMustExist: false, // throw error if database not exists
8 WAL: true, // automatically enable 'PRAGMA journal_mode = WAL'
9 migrate: { // disable completely by setting `migrate: false`
10 force: false, // set to 'last' to automatically reapply the last migration-file
11 table: 'migration', // name of the database table that is used to keep track
12 migrationsPath: './migrations' // path of the migration-files
13 }
14})
After that you can use the library without parameter:
anotherAPIFile.js
1const DB = require('@beenotung/better-sqlite3-helper') 2 3// a second call directly returns the global instance 4let row = DB().queryFirstRow('SELECT * FROM users WHERE id=?', userId); 5console.log(row.firstName, row.lastName, row.email);
New Functions
This class implements shorthand methods for better-sqlite3.
1// shorthand for db.prepare('SELECT * FROM users').all(); 2let allUsers = DB().query('SELECT * FROM users'); 3// result: [{id: 1, firstName: 'a', lastName: 'b', email: 'foo@b.ar'},{},...] 4// result for no result: [] 5 6// shorthand for db.prepare('SELECT * FROM users WHERE id=?').get(userId); 7let row = DB().queryFirstRow('SELECT * FROM users WHERE id=?', userId); 8// result: {id: 1, firstName: 'a', lastName: 'b', email: 'foo@b.ar'} 9// result for no result: undefined 10 11// shorthand for db.prepare('SELECT * FROM users WHERE id=?').get(999) || {}; 12let {id, firstname} = DB().queryFirstRowObject('SELECT * FROM users WHERE id=?', userId); 13// result: id = 1; firstName = 'a' 14// result for no result: id = undefined; firstName = undefined 15 16// shorthand for db.prepare('SELECT * FROM users WHERE id=?').pluck(true).get(userId); 17let email = DB().queryFirstCell('SELECT email FROM users WHERE id=?', userId); 18// result: 'foo@b.ar' 19// result for no result: undefined 20 21// shorthand for db.prepare('SELECT * FROM users').all().map(e => e.email); 22let emails = DB().queryColumn('email', 'SELECT email FROM users'); 23// result: ['foo@b.ar', 'foo2@b.ar', ...] 24// result for no result: [] 25 26// shorthand for db.prepare('SELECT * FROM users').all().reduce((o, e) => {o[e.lastName] = e.email; return o;}, {}); 27let emailsByLastName = DB().queryKeyAndColumn('lastName', 'email', 'SELECT lastName, name FROM users'); 28// result: {b: 'foo@b.ar', c: 'foo2@b.ar', ...} 29// result for no result: {}
Insert, Update and Replace
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.
Update
1// const numberOfChangedRows = DB().update(table, data, where, whitelist = undefined) 2 3// simple use with a object as where and no whitelist 4DB().update('users', { 5 lastName: 'Mustermann', 6 firstName: 'Max' 7}, { 8 email: 'unknown@emailprovider.com' 9}) 10 11// data from a request and a array as a where and only editing of lastName and firstName is allowed 12DB().update('users', req.body, ['email = ?', req.body.email], ['lastName', 'firstName']) 13 14 15// 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] 16DB().updateWithBlackList('users', req.body, req.body.id, ['id', 'email'])
Insert and replace
1// const lastInsertID = DB().insert(table, datas, whitelist = undefined) 2// const lastInsertID = DB().replace(table, datas, whitelist = undefined) 3 4// simple use with an object and no whitelist 5DB().insert('users', { 6 lastName: 'Mustermann', 7 firstName: 'Max', 8 email: 'unknown@emailprovider.com' 9}) 10 11// inserting two users 12DB().insert('users', [{ 13 lastName: 'Mustermann', 14 firstName: 'Max', 15 email: 'unknown@emailprovider.com' 16}, { 17 lastName: 'Mustermann2', 18 firstName: 'Max2', 19 email: 'unknown2@emailprovider.com' 20}]) 21 22// data from a request and only lastName and firstName are set 23DB().replace('users', req.body, ['lastName', 'firstName']) 24 25 26// replace with blacklist (id and email is not allowed; only valid columns of the table are allowed) 27DB().replaceWithBlackList('users', req.body, ['id', 'email']) // or insertWithBlackList
Delete
1//delete the user with an id of 4 2DB().delete('users', {id: 4})
Try and catch
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:
1const { Router } = require('express') 2const bodyParser = require('body-parser') 3const DB = require('@beenotung/better-sqlite3-helper') 4 5router.patch('/user/:id', bodyParser.json(), function (req, res, next) { 6 try { 7 if (!req.params.id) { 8 res.status(400).json({error: 'missing id'}) 9 return 10 } 11 DB().updateWithBlackList( 12 'users', 13 req.body, 14 req.params.id, 15 ['id'] 16 ) 17 18 res.statusCode(200) 19 } catch (e) { 20 console.error(e) 21 res.status(503).json({error: e.message}) 22 } 23})
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
1-- Up 2CREATE TABLE Category (id INTEGER PRIMARY KEY, name TEXT); 3CREATE TABLE Post (id INTEGER PRIMARY KEY, categoryId INTEGER, title TEXT, 4 CONSTRAINT Post_fk_categoryId FOREIGN KEY (categoryId) 5 REFERENCES Category (id) ON UPDATE CASCADE ON DELETE CASCADE); 6INSERT INTO Category (id, name) VALUES (1, 'Business'); 7INSERT INTO Category (id, name) VALUES (2, 'Technology'); 8 9-- Down 10DROP TABLE Category 11DROP TABLE Post;
migrations/002-missing-index.sql
1-- Up 2CREATE INDEX Post_ix_categoryId ON Post (categoryId); 3 4-- Down 5DROP 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.
1const DB = require('@beenotung/better-sqlite3-helper') 2 3const db = new DB({ 4 migrate: { 5 migrations: [ 6 `-- Up 7 CREATE TABLE Setting ( 8 key TEXT NOT NULL UNIQUE, 9 value BLOB, 10 type INT NOT NULL DEFAULT 0, 11 PRIMARY KEY(key) 12 ); 13 CREATE INDEX IF NOT EXISTS Setting_index_key ON Setting (key); 14 15 -- Down 16 DROP INDEX IF EXISTS Setting_index_key; 17 DROP TABLE IF EXISTS Setting; 18 `, 19 `-- Up 20 INSERT INTO Setting (key, value, type) VALUES ('test', 'now', 0); 21 INSERT INTO Setting (key, value, type) VALUES ('testtest', 'nownow', 6); 22 23 -- Down 24 DELETE FROM Setting WHERE key = 'test'; 25 DELETE FROM Setting WHERE key = 'testtest'; 26 ` 27 ] 28 } 29})
More Documentation of better-sqlite3
License

No vulnerabilities found.

No security vulnerabilities found.