sqlite

What is sqlite?

The sqlite npm package is a wrapper for SQLite, a C library that provides a lightweight, disk-based database. It doesn't require a separate server process and allows access to the database using a nonstandard variant of the SQL query language. The sqlite npm package allows you to interact with SQLite databases in a Node.js environment.

What are sqlite's main functionalities?

Create a Database

This code demonstrates how to create an in-memory SQLite database, create a table, insert data, and query the data.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');
db.serialize(() => {
  db.run('CREATE TABLE lorem (info TEXT)');
  const stmt = db.prepare('INSERT INTO lorem VALUES (?)');
  for (let i = 0; i < 10; i++) {
    stmt.run('Ipsum ' + i);
  }
  stmt.finalize();
  db.each('SELECT rowid AS id, info FROM lorem', (err, row) => {
    console.log(row.id + ': ' + row.info);
  });
});
db.close();

Open an Existing Database

This code demonstrates how to open an existing SQLite database file, query data from a table, and print the results.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('example.db');
db.serialize(() => {
  db.each('SELECT rowid AS id, info FROM lorem', (err, row) => {
    console.log(row.id + ': ' + row.info);
  });
});
db.close();

Parameterized Queries

This code demonstrates how to use parameterized queries to prevent SQL injection attacks. It shows how to insert and query data using placeholders.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');
db.serialize(() => {
  db.run('CREATE TABLE lorem (info TEXT)');
  const stmt = db.prepare('INSERT INTO lorem VALUES (?)');
  stmt.run('Ipsum 1');
  stmt.finalize();
  db.get('SELECT info FROM lorem WHERE info = ?', ['Ipsum 1'], (err, row) => {
    console.log(row.info);
  });
});
db.close();

Other packages similar to sqlite

better-sqlite3

better-sqlite3 is a wrapper for SQLite3 that is faster and simpler to use than the sqlite package. It provides a more synchronous API, which can be easier to work with in many cases. Unlike sqlite, better-sqlite3 does not require a callback for each query, making the code more readable and easier to maintain.

sequelize

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication, and more. While it supports SQLite, it is more complex and feature-rich compared to the sqlite package, making it suitable for larger applications with more complex database interactions.

knex

Knex.js is a SQL query builder for PostgreSQL, MySQL, MariaDB, SQLite3, and Oracle. It is designed to be flexible and powerful, allowing you to build complex queries with ease. Knex.js can be used as a query builder in conjunction with an ORM or as a standalone query builder. It provides a more abstracted way to interact with databases compared to the sqlite package.

README

SQLite Client for Node.js Apps

A wrapper library written in Typescript with ZERO dependencies that adds ES6 promises and SQL-based migrations API to sqlite3 (docs).

note v4 of sqlite has breaking changes compared to v3! Please see CHANGELOG.md for more details.

• Installation
  • Install sqlite3
  • Install sqlite
• Usage
  • Opening the database
    • Without caching
    • With caching
    • Enable verbose / debug mode
    • Tracing SQL errors
    • With a custom driver
    • Opening multiple databases
    • open config params
  • Examples
    • Creating a table and inserting data
    • Getting a single row
    • Getting many rows
    • Inserting rows
    • Updating rows
    • Prepared statement
    • each()
    • Get the driver instance
    • Closing the database
  • ES6 tagged template strings
  • Migrations
• Typescript tricks
  • Import interfaces from sqlite
  • Specify typings for a specific database driver
  • Use generics to get better typings on your rows
    • Get example
    • All example
• API Documentation
• Management Tools
• Alternative SQLite libraries
• References
• Support
• License

Installation

Install sqlite3

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 file
export async function openDb () {
  return open({
    filename: '/tmp/database.db',
    driver: sqlite3.Database
  })
}

With caching

If you want to enable the database object cache

import sqlite3 from 'sqlite3'
import { open } from 'sqlite'

(async () => {
    const db = await open({
      filename: '/tmp/database.db',
      driver: sqlite3.cached.Database
    })
})()

Enable verbose / debug mode

import sqlite3 from 'sqlite3'

sqlite3.verbose()

Tracing SQL errors

For more info, see this doc.

db.on('trace', (data) => {
  
})

With a custom driver

You can use an alternative library to sqlite3 as long as it conforms to the sqlite3 API.

For example, using sqlite3-offline:

import sqlite3Offline from 'sqlite3-offline'
import { open } from 'sqlite'

(async () => {
    const db = await open({
      filename: '/tmp/database.db',
      driver: sqlite3Offline.Database
    })
})()

Opening multiple databases

import sqlite3 from 'sqlite3'
import { open } from 'sqlite'

(async () => {
  const [db1, db2] = await Promise.all([
    open({
      filename: '/tmp/database.db',
      driver: sqlite3.Database
    }),
    open({
      filename: '/tmp/database2.db',
      driver: sqlite3.Database
    }),
  ])

  await db1.migrate({
    migrationsPath: '...'
  })

  await db2.migrate({
    migrationsPath: '...'
  })
})()

open config params

// 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
• See the docs/ directory for full documentation.
• Also visit the sqlite3 library API docs

Creating a table and inserting data

await db.exec('CREATE TABLE tbl (col TEXT)')
await db.exec('INSERT INTO tbl VALUES ("test")')

Getting a single row

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.

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.

const rowsCount = await db.each(
  'SELECT col FROM tbl WHERE ROWID = ?',
  [2],
  (err, row) => {
    if (err) {
      throw err
    }

    // row = { col: 'other thing' }
  }
)

// rowsCount = 1

Get the driver instance

Useful if you need to call methods that are not supported yet.

const rawDb = db.getDatabaseInstance()
const rawStatement = stmt.getStatementInstance()

Closing the database

await db.close()

ES6 tagged template strings

This module is compatible with sql-template-strings.

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}`);

Migrations

This module comes with a lightweight migrations API that works with SQL-based migration files

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 installed
import sqlite3 from 'sqlite3'

// sqlite3.Database, sqlite3.Statement is the default if no explicit generic is specified
await 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 data
const result = await db.all<Row[]>('SELECT col FROM tbl')

result.each((row) => {
  // row should have type information now!
})

API Documentation

See the docs directory for full documentation.

Management Tools

• Beekeeper Studio: Open Source SQL Editor and Database Manager
• DB Browser for SQLite: Desktop-based browser.
• datasette: Datasette is a tool for exploring and publishing data. Starts up a server that provides a web interface to your SQLite data.
• SQLite Studio: A free, open source, multi-platform SQLite database manager written in C++, with use of Qt framework.
• HeidiSQL: Full-featured database editor.
• 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.
• sql.js: SQLite compiled to Webassembly.
• sqlite3-offline: Offers pre-compiled sqlite3 binaries if your machine cannot compile it. Should be mostly compatible with this library.

If you know of any others, feel free to open a PR to add them to the list.

References

• Using SQLite with Node.js for Rapid Prototyping on Medium.com
• SQLite Documentation, e.g. SQL Syntax, Data Types etc. on SQLite.org
• ES6 tagged sql-template-strings.

Support

• Join #node-sqlite chat room on Gitter to stay up to date regarding the project
• Join #sqlite IRC chat room on Freenode about general discussion about SQLite

License

The MIT License © 2020-present Kriasoft / Theo Gravity. All rights reserved.

---

Made with ♥ by Konstantin Tarkus (@koistya), Theo Gravity and contributors

Changelog

4.0.24 - Sun Mar 06 2022 20:12:24

Contributor: fresheneesz

• Updating exec to support passed in parameters like the other functions do. (#155)