Wrapper for expo-sqlite with async/await, migrations and transactions

README.md

Expo-sqlite - the right way

Why

With expo-sqlite it's not possible to execute few depending statements inside single transaction - db.transaction does not work with async/promise and tx.executeSql just enqueues sql statement but does not execute it.

Documentation

Database class has two methods - execute (to execute single statement without transaction) and transaction(cb) to execute few statements inside a transaction

execute method takes an SQL string as first parameter and array of values to replace ? symbols as second parameter

transaction method takes an async function as parameter. Callback function receives an instance of Connection class which has execute method with signature as above

Constructor of Database class takes database name as first parameter and optional object as second. Available options:

• prepareConnFn Async function to execute after connecting to database. Function receives a Connection instance, execute and transaction methods will wait for resolve of returned promise. This can be used to enable foreign keys, for example

• migrateFn Similar to prepareConnFn but for migration purposes (to prepare and update tables). It will receive separate Connection instance

See an example in db.js for example of migration function and prepare function. You can omit them if not needed.

Usage

import {Database} from "./database";
const db = new Database("main");

...

await db.transaction(async connection => {
  const res1 = await connection.execute('some sql query');
  await connection.execute('some another query depending on res1');
  await connection.execute('and another');
});

...

await db.execute('some sql containing ?', ['values to replace ?']);

database.js

import * as SQLite from "expo-sqlite";

class Connection {
    constructor(databaseName) {
        this._db = SQLite.openDatabase(databaseName);
        this.transacting = false;
    }

    execute(sqlStatement, args = []) {
        return new Promise((resolve, reject) => {
            this._db.exec([{sql: sqlStatement, args}], false, (err, res) => {
                if (err) {
                    return reject(err);
                }
                
                if (res[0].error) {
                    return reject(res[0].error);
                }

                resolve(res[0]);
            });
        });
    }

    close() {
        this._db._db.close();
    }

    async beginTransaction() {
        await this.execute("begin transaction");
        this.transacting = true;
    }

    async commitTransaction() {
        await this.execute("commit");
        this.transacting = false;
    }

    async rollbackTransaction() {
        await this.execute("rollback");
        this.transacting = false;
    }
}

export class Database {
    constructor(name = "main", {prepareConnFn, migrateFn} = {}) {
        this._dbName = name;
        this._connection = new Connection(this._dbName);
        this._params = {prepareConnFn, migrateFn};

        this._prepareConnectionPromise =
            typeof this._params.prepareConnFn === "function"
                ? this._params.prepareConnFn(this._connection)
                : Promise.resolve();

        const performMigration = async () => {
            const connection = new Connection(this._dbName);
            await this._params.migrateFn(connection);
            connection.close();
        };

        this._migrationPromise =
            typeof this._params.migrateFn === "function"
                ? performMigration()
                : Promise.resolve();
    }

    async execute(sqlQuery, args = []) {
        await this._prepareConnectionPromise;
        await this._migrationPromise;

        return await this._connection.execute(sqlQuery, args);
    }

    async transaction(cb) {
        await this._prepareConnectionPromise;
        await this._migrationPromise;
        const connection = new Connection(this._dbName);
        if (typeof this._params.prepareConnFn === "function") {
            await this._params.prepareConnFn(connection);
        }
        try {
            await connection.beginTransaction();
            try {
                await cb(connection);
                await connection.commitTransaction();
            } catch (e) {
                await connection.rollbackTransaction();
                throw e;
            }
        } catch (e) {
            connection.close();
            throw e;
        }
        await connection.close();
    }

    close() {
        this._connection._db.close();
    }
}

db.js

import {Database} from "./database";
import m_0001 from "./m_0001";

//When adding new migrations in already deployed app, append them to the end of array, do not re-arrange
//Do not modify migration after app version containing it is published
const migrations = [null, m_0001];
const migrationsTable = "_migrations";

const db = new Database("main", {
  prepareConnFn: async connection => {
    try {
      await connection.execute("PRAGMA foreign_keys = ON;");
    } catch (e) {
      console.log(e);
    }
  },
  migrateFn: async connection => {
    //Inside migration function you can use `connection.beginTransaction`, `connection.commitTransaction` and 
    //`connection.rollbackTransaction` methods to control transactions, as needed. In this example I simply 
    //run all migrations inside single transaction. Your needs might be different
    //Outside of migration use `transaction` method of `Database` class for transactions
    await connection.beginTransaction();
    try {
      await connection.execute(
        `create table if not exists ${migrationsTable} (version integer primary key, updatedAt text not null)`
      );
      const versions = (
          await connection.execute(`select * from ${migrationsTable}`)
      ).rows.map(({ version }) => version);
      const currentVersion = Math.max(0, ...versions);
      for (let i = currentVersion + 1; i < migrations.length; i++) {
        await migrations[i](connection);
        await connection.execute(`insert into ${migrationsTable} values (?, ?)`, [
          i,
          new Date().toISOString()
        ]);
        console.log(`Applied migration ${i}`)
      }
      await connection.commitTransaction();
    } catch (e) {
      await connection.rollbackTransaction();
      console.log(e);
    }    
  }
});

export default db;

m_0001.js

export default async function(connection) {
  await connection.execute(
    `create table users (
      id integer primary key autoincrement, 
      name text not null
    )`
  );
  await connection.execute(
    `create unique index uk_users_name on users (name)`
  );
}

Using this setup I'm doing multiple reads to an api and each time attempting to write that data on sqlite, but I'm constantly getting a database is locked (code 5 SQLITE_BUSY error. I'm probably using this setup wrong. Any pointers?

I'm doing something like:

const cats = catalogs.map(async item => {
  const response = await api.get(item.ruta);
  const data = response.data;
  await db.transaction(async connection => {
    await connection.execute('DELETE FROM ' + item.tabla);
    const strSqlite = arrToInsertStr(item.tabla, [data[d]], del);
    await connection.execute(strSqlite.str, strSqlite.arrToInsert);
  });
});

Promise.all(cats);

@mauricionuup quick googling tells me this:

The SQLITE_BUSY result code indicates that the database file could not be written (or in some cases read) because of concurrent activity by some other database connection, usually a database connection in a separate process.

For example, if process A is in the middle of a large write transaction and at the same time process B attempts to start a new write transaction, process B will get back an SQLITE_BUSY result because SQLite only supports one writer at a time. Process B will need to wait for process A to finish its transaction before starting a new transaction. The sqlite3_busy_timeout() and sqlite3_busy_handler() interfaces and the busy_timeout pragma are available to process B to help it deal with SQLITE_BUSY errors.

An SQLITE_BUSY error can occur at any point in a transaction: when the transaction is first started, during any write or update operations, or when the transaction commits. To avoid encountering SQLITE_BUSY errors in the middle of a transaction, the application can use BEGIN IMMEDIATE instead of just BEGIN to start a transaction. The BEGIN IMMEDIATE command might itself return SQLITE_BUSY, but if it succeeds, then SQLite guarantees that no subsequent operations on the same database through the next COMMIT will return SQLITE_BUSY.

See also: SQLITE_BUSY_RECOVERY and SQLITE_BUSY_SNAPSHOT.

The SQLITE_BUSY result code differs from SQLITE_LOCKED in that SQLITE_BUSY indicates a conflict with a separate database connection, probably in a separate process, whereas SQLITE_LOCKED indicates a conflict within the same database connection (or sometimes a database connection with a shared cache).

My understanding from the above explanation is that sqlite does not support parallel write, which you are trying to do with Promise.all

This wrapper is exactly what I needed, I was struggling all day to get the callback based interface of expo-sqlite to work but with your wrapper everything was working in less than 10min. Thanks for sharing, expo-sqlite docs should mention this wrapper or actually just provide it because using the current websql is difficult to use with react. (Not to mention that websql is so poorly designed in the first place, e.g., not keeping the order of success and error callbacks the same between functions :(

I would like to ask, in the Database class, what is the point of awaiting on two instance variables, for example in execute():

async execute(sqlQuery, args = []) {
    await this._prepareConnectionPromise; // <-- ??
    await this._migrationPromise; // <-- ??
    return await this._connection.execute(sqlQuery, args);
}

what does this achieve?

I would like to ask, in the Database class, what is the point of awaiting on two instance variables, for example in execute():

async execute(sqlQuery, args = []) {
    await this._prepareConnectionPromise; // <-- ??
    await this._migrationPromise; // <-- ??
    return await this._connection.execute(sqlQuery, args);
}

what does this achieve?

It is a promise that have some operation that must be done before executing rhe queries.

@tasso85
As @AlenToma correctly said, we are waiting for async operations that must be finished before executing any queries

Oh yeah, that's it, I didn't consider those instance variables where promises, sorry for the obvious question!

This is awesome - thanks for this lib! ❤️ As a complete zero in JS I was breaking my head trying to work out the sequential transactions until I found this. Works like a charm - I had to iron out a few db locks (SQLITE_BUSY), but only because I moved to another screen in the app within one transaction, which attempted to open another.