Most people who use this library will use sqlite3
as the database driver.
Any library that conforms to the sqlite3 (API)
should also work.
$ npm install sqlite3 --save
Install sqlite
# v4 of sqlite is targeted for nodejs 10 and on.$ npm install sqlite --save# If you need a legacy version for an older version of nodejs# install v3 instead, and look at the v3 branch readme for usage details$ npm install sqlite@3 --save
Usage
This module has the same API as the original sqlite3 library (docs),
except that all its API methods return ES6 Promises and do not accept callback arguments (with the exception of each()).
Opening the database
Without caching
import sqlite3 from 'sqlite3'import { open } from 'sqlite'// this is a top-level await (async () => { // open the database const db = await open({ filename: '/tmp/database.db', driver: sqlite3.Database })})()
or
import sqlite3 from 'sqlite3'import { open } from 'sqlite'open({ filename: '/tmp/database.db', driver: sqlite3.Database}).then((db) => { // do your thing})
or
import sqlite3 from 'sqlite3'import { open } from 'sqlite'// you would have to import / invoke this in another fileexport async function openDb () { return open({ filename: '/tmp/database.db', driver: sqlite3.Database })}
// db is an instance of `sqlite#Database`// which is a wrapper around `sqlite3#Database`const db = await open({ /** * Valid values are filenames, ":memory:" for an anonymous in-memory * database and an empty string for an anonymous disk-based database. * Anonymous databases are not persisted and when closing the database * handle, their contents are lost. */ filename: string /** * One or more of sqlite3.OPEN_READONLY, sqlite3.OPEN_READWRITE and * sqlite3.OPEN_CREATE. The default value is OPEN_READWRITE | OPEN_CREATE. */ mode?: number /** * The database driver. Most will install `sqlite3` and use the `Database` class from it. * As long as the library you are using conforms to the `sqlite3` API, you can use it as * the driver. * * @example * * ``` * import sqlite from 'sqlite3' * * const driver = sqlite.Database * ``` */ driver: any})
Examples
See the src/**/__tests__ directory for more example usages
const result = await db.get('SELECT col FROM tbl WHERE col = ?', 'test')// { col: 'test' }
const result = await db.get('SELECT col FROM tbl WHERE col = ?', ['test'])// { col: 'test' }
const result = await db.get('SELECT col FROM tbl WHERE col = :test', { ':test': 'test'})// { col: 'test' }
Getting many rows
const result = await db.all('SELECT col FROM tbl')// [{ col: 'test' }]
Inserting rows
const result = await db.run( 'INSERT INTO tbl (col) VALUES (?)', 'foo')/*{ // row ID of the inserted row lastID: 1, // instance of `sqlite#Statement` // which is a wrapper around `sqlite3#Statement` stmt: <Statement>}*/
const result = await db.run('INSERT INTO tbl(col) VALUES (:col)', { ':col': 'something'})
Updating rows
const result = await db.run( 'UPDATE tbl SET col = ? WHERE col = ?', 'foo', 'test')/*{ // number of rows changed changes: 1, // instance of `sqlite#Statement` // which is a wrapper around `sqlite3#Statement` stmt: <Statement>}*/
Prepared statement
// stmt is an instance of `sqlite#Statement`// which is a wrapper around `sqlite3#Statement`const stmt = await db.prepare('SELECT col FROM tbl WHERE 1 = ? AND 5 = ?5')await stmt.bind({ 1: 1, 5: 5 })let result = await stmt.get()// { col: 'some text' }
const stmt = await db.prepare( 'SELECT col FROM tbl WHERE 13 = @thirteen ORDER BY col DESC')const result = await stmt.all({ '@thirteen': 13 })
each()
each() is a bit different compared to the other operations due to its underlying implementation.
The function signature looks like this:
async each (sql, [...params], callback)
callback(err, row) is triggered when the database has a row to return
The promise resolves when all rows have returned with the number of rows returned.
try { // You need to wrap this in a try / catch for SQL parse / connection errors const rowsCount = await db.each( 'SELECT col FROM tbl WHERE ROWID = ?', [2], (err, row) => { if (err) { // This would be if there is an error specific to the row result throw err } // row = { col: 'other thing' } } )} catch (e) { throw e}// rowsCount = 1
Get the driver instance
Useful if you need to call methods that are not supported yet.
import SQL from 'sql-template-strings'const book = 'harry potter';const author = 'J. K. Rowling';const data = await db.all(SQL`SELECT author FROM books WHERE name = ${book} AND author = ${author}`);
With default configuration, you can create a migrations/ directory in your project with SQL files,
and call the migrate() method to run the SQL in the directory against the database.
See this project's migrations/ folder for examples.
await db.migrate({ /** * If true, will force the migration API to rollback and re-apply the latest migration over * again each time when Node.js app launches. */ force?: boolean /** * Migrations table name. Default is 'migrations' */ table?: string /** * Path to the migrations folder. Default is `path.join(process.cwd(), 'migrations')` */ migrationsPath?: string})
Typescript tricks
Import interfaces from sqlite
import { ISqlite, IMigrate } from 'sqlite'
See the definitions for more details.
Specify typings for a specific database driver
// Assuming you have @types/sqlite3 installedimport sqlite3 from 'sqlite3'// sqlite3.Database, sqlite3.Statement is the default if no explicit generic is specifiedawait open<sqlite3.Database, sqlite3.Statement>({ filename: ':memory'})
Use generics to get better typings on your rows
Most methods allow for the use of generics
to specify the data type of your returned data. This allows your IDE to perform better autocomplete
and the typescript compiler to perform better static type analysis.
Get example
interface Row { col: string}// result will be of type Row, allowing Typescript supported IDEs to autocomplete on the properties!const result = await db.get<Row>('SELECT col FROM tbl WHERE col = ?', 'test')
All example
interface Row { col: string}// Result is an array of rows, you can now have array-autocompletion dataconst result = await db.all<Row[]>('SELECT col FROM tbl')result.each((row) => { // row should have type information now!})
DBeaver: Full-featured multi-platform database tool and designer.
Alternative SQLite libraries
This library and the library it primarily supports, sqlite3, may not be the best library that
fits your use-case. You might want to try these other SQLite libraries:
better-sqlite3: Totes itself as the fastest and
simplest library for SQLite3 in Node.js.
Bun sqlite3: bun:sqlite is a high-performance builtin SQLite3 module for bun.js.