How to create an SQLite database in node.js

This was originally posted on atdatabases.org

SQLite is a great database for embedded use cases. e.g. if you are using node.js in IOT, or an Electron app.

To get started, install @databases/sqlite using either yarn or npm:

yarn install @databases/sqlite

or

npm install @databases/sqlite

Then you can import it (if you are using TypeScript/Babel/some other environment that supports ESModules) or require it (if you are using plain JavaScript), and call connect to create the database file if it does not exist, and open it if it already exists.

Here is an example of using SQLite as a basic key value store of strings (here VARCHAR is the SQLite data type that is equivalent to string in JavaScript).

const connect = require('@databases/sqlite');
const {sql} = require('@databases/sqlite');
const db = connect('temp.db');
async function prepare() {
  await db.query(sql`
    CREATE TABLE IF NOT EXISTS app_data (
      id VARCHAR NOT NULL PRIMARY KEY,
      value VARCHAR NOT NULL
    );
  `);
}
const prepared = prepare();
async function set(id, value) {
  await prepared;
  await db.query(sql`
    INSERT INTO app_data (id, value)
      VALUES (${id}, ${value})
    ON CONFLICT (id) DO UPDATE
      SET value=excluded.value;
  `);
}
async function get(id) {
  await prepared…