best-sqlite3

best-sqlite3

What is SQLite3?

SQLite, version 3, is an RDBMS - relational database management system written in C.

It differs from most other systems (MySQL, Postgre etc) in that there is not a separate server running the database, instead the SQLite code gets embedded in the application you are writing.

SQLite stores a whole database in one single file.

If you want a graphical editor for SQLite we recommend SQLiteStudio - cross platform, with a nice GUI.

Note: The SQL dialect spoken by SQLite is similar to the syntax in PostgreSQL and rather similar to MySQL/MariaDB. An important difference compared to MySQL/MariaDB is that you can not use && instead of AND or || instead of OR. Just get used to writing AND and OR!

What is best-sqlite3?

• best-sqlite3 is a driver that lets you run SQLite3 with Node.js.
• It differs from other Node.js drivers in that it does not need node-gyp or any binaries during installation, because it runs SQLite3 recompiled to JavaScript/webassembly (thanks to the SQL.js project)
• best-sqlite3 provides a simple API and guarantees you that you won't run into problems with compiling bindings to other languages - the npm installation will be trouble free regardless of your operating system, Node.js version etc.
• best-sqlite3 is quite fast. See the tests below comparing it to the drivers sqlite3 and better-sqlite3.

Also:

• best-sqlite3 includes middleware for creating an Express Session store, with which you can store Express-sessions in your SQLite3 database, making them survive server restarts.

Installation

npm i best-sqlite3

Basic usage

Require best-sqlite3 and connect to a database, then run queries.

(async () => { // start of async wrapper

  // Require bestSqlite
  const bestSqlite = require('best-sqlite3');

  // Connect to a database
  // (if the file does not exist 
  //  a new db will be created)
  const db = await bestSqlite
    .connect('path-to-db-file.sqlite3');

  // Run a query
  let allUsers = db.run(`
    SELECT * FROM users
  `);

  // Run a query with parameters
  // (a prepared statement)
  let allJanes = db.run(
    `
      SELECT * FROM users
      WHERE firstName = $firstName
    `, 
    {
      firstName: 'Jane'
    }
  );


})().catch(e => console.error(e)); 
// end of async wrapper

What does the run-method return?

• For SELECT-queries run will return an array of objects. Each object corresponds to a row in the database.
• For other statements (CREATE, INSERT, UPDATE, DELETE) run returns an object with the property rowsModified (number of rows modified)
• For INSERT statements the property lastInsertRowId (the id of the latest row inserted) is also provided.

User defined functions with the regFunc-method

You can define your own functions written in JavaScript that you can then use in your SQL-queries.

// Register a function
db.regFunc('concatWithSpace', (x, y) => x + ' ' + y);

// Use the function in your query
db.run(`
  SELECT concatWithSpace(firstName, lastName) AS fullName
  FROM users
`);

Lists tables and views in a database

You can easily get a list (array of strings) with the names of the tables in a database. The same goes for all the views in a database...

// db.tables - a list of all tables in the database
console.log(db.tables);

// db.views - a list of all views in the database
console.log(db.views);

Storing express-session sessions in the database

The npm module express-session is used to get user sessions based on cookies to work with express (the popular web server for Node.js).

By default express-session stores session in internal memory, but its documentation recommend against doing so in production.

best-sqlite3 provides middleware that can be used together with express-session to automatically store sessions in the database instead.

(async () => { // start of async wrapper

  const express = require('express');
  const session = require('express-session');
  const bestSqlite = require('best-sqlite3');

  const app = express();
  const db = await bestSqlite.connect('path-to-db-file.sqlite3');

  // Setting up the express session middleware
  // with bestSqlite as a store
  app.use(session({
    secret: 'your own secret',
    resave: false,
    saveUninitialized: true,
    cookie: { secure: 'auto' },
    // tell express session to use our bestSqlite connection
    // (this will store the sessions in the database)
    store: db.sessionStore(
      // Optional settings
      {
        // What table to store sessions in
        tableName: 'sessions', 
        // Minutes a session lives after inactivity
        deleteAfterInactivityMinutes: 120
      }
    )
  }));

  // Check that storing sessions in the db works 
  // (restart your app/server to see that 
  //  the urls are still remembered)
  app.get('*', (req, res) => {
    let { visited } = req.session;
    if (!visited) { visited = req.session.visited = []; }
    req.url !== '/favicon.ico' && visited.push(req.url);
    res.send('Visited urls:<br>' + visited.join('<br>'));
  });

  app.listen(3000, () => console.log('Listening on port 3000!'));

})().catch(e => console.error(e)); 
// end of async wrapper

Scaling to multiple processes - what about concurrency?

To save time best-sqlite3 only writes changes to file when there are no queries in queue.

This approach works well as long as you only have one Node.js process communicating with a database. So if you are starting out - learning Node.js and SQLite, writing smaller apps - you will be just fine!

But: If your application has grown to a stage where you need to load-balance it by running several copies of your app in a cluster, you might get into trouble if the order in which you run destructive operations (insert, update, delete) is crucial.

We are currently working on a stand alone server version that will fix this. This server will run as a separate process on a separate port and handle all direct communication with the file system. Individual queries will run slower but will take no CPU-time from your application. The api-syntax will be identical, apart from having to write await before method calls, for example await run instead of run.