Package Exports

sqlite

This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (sqlite) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

SQLite Client for Node.js Apps

built with typescript

A wrapper library 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
Usage
References
Support
License

Installation

# v4 of sqlite is targted for nodejs 10 and on.
$ npm install sqlite@4.0.0-beta.2 --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 { open } from 'sqlite'

// this is a top-level await
(async () => {
    // open the database
    const db = await open({
      filename: '/tmp/database.db'
    })
})()

import { open } from 'sqlite'

open({
  filename: '/tmp/database.db'
}).then((db) => {
  // do your thing
})

With caching

If you want to enable the database object cache

import { open } from 'sqlite'

(async () => {
    const db = await open({
      filename: '/tmp/database.db',
      cache: true
    })
})()

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 { open } from 'sqlite'

(async () => {
    const db = await open({
      filename: '/tmp/database.db',
      cache: true
    })
})()

`open` config params

// db is an instance of Sqlite3Database
// 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?: OpenDatabaseEnum

  /**
   * Use an alternative library instead of sqlite3. The interface of the library must
   * conform to `sqlite3`.
   *
   * The default is to use `sqlite3` as the driver.
   *
   * @see https://github.com/mapbox/node-sqlite3/wiki/API
   */
  driver?: any

  /**
   * If true, uses the `sqlite3` built-in database object cache to avoid opening the same
   * database multiple times.
   *
   * Does not apply if `driver` is defined.
   *
   * @see https://github.com/mapbox/node-sqlite3/wiki/Caching
   */
  cached?: boolean

  /**
   * Enables verbose mode.
   *
   * This only applies to the `sqlite3` driver.
   */
  verbose?: boolean
})

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 (part 2)

const result = await db.run(
  'INSERT INTO tbl (col) VALUES (?)',
  'foo'
)

/*
{
  // row ID of the inserted row
  lastId: 1,
  // instance of Sqlite3Statement
  // which is a wrapper around `sqlite3#Statement`
  stmt: <Sqlite3Statement>
}
*/

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 Sqlite3Statement
  // which is a wrapper around `sqlite3#Statement`
  stmt: <Sqlite3Statement>
}
*/

Prepared statement

// stmt is an instance of Sqlite3Statement
// 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 })

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

API Documentation

See the docs directory for full documentation.

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

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

sqlite