In android native world it’s common to test SQLite code in isolation. I wanted to have something similar in react-native project I am currently working on. Since none of sqlite packages proves usable abstractions I came up with the following approach.

We can use node-sqlite3 to test sqlite code in the jest environment. Applies to react-native-sqlite2, react-native-sqlite-storage, cordova-sqlite-storage and similar javascript packages.

requires

  • npm install --saveDev sqlite3@4.1.1 @types/websql@0.0.27 @types/sqlite@3.1.6

Queries

We can abstract from environment by having an “query executor”

export type SqlExecutor = {
  read: (query: string, args: Array<string>) => Promise<string[]>,
  write: (query: string, args: Array<string>) => Promise<string[]>,
}

Now we can define queries using read and write of the executor


// createUsersQueries.ts
const CREATE_TABLE_IF_NOT_EXIST = `
  CREATE TABLE IF NOT EXISTS USERS (
    NAME TEXT
  );
`
const GET_COUNT = `
  SELECT count(*) AS result FROM USERS
`

export const createUsersQueries = (executor: SqlExecutor) => ({
    createTable: () => executor.write(CREATE_TABLE_IF_NOT_EXIST),
    getCount: () => executor.read(GET_COUNT).then(result => Number.parseInt(result[0]))
})

Writing tests

Before writing tests we need to write an implementation of the executor for node.js environment.

const { promisify } = require('util');
import { Database } from 'sqlite3';
const createNodeSqlite2Executor = (db: Database): SqlExecutor => {
  const allPromise = promisify(db.all).bind(db);
  return {
    read: ((query, args) => allPromise(query, args)),
    write: ((query, args) => allPromise(query, args)),
  };
};

Now we can test usersQueries in jest

const sqlite3 = require('sqlite3').verbose();
import { createUsersQueries } from "./createUsersQueries"

const inMemoryDb = new sqlite3.Database(':memory:');
const nodeExecutor = createNodeSqlite2Executor(inMemoryDb);
const userQueries = createUsersQueries(nodeExecutor);

describe('usersQueries', () => {
    beforeEach(async () => {
        await userQueries.createTable();
    })
    it('getCount should return zero for empty table', async () => {
        const count = await userQueries.getCount();
        expect(count).toEqual(0);
    });
});

React-native environment

After writing all the tests for our queries we can implement SqlExecutor in react-native. react-native-sqlite2 or a similar package can be used.

const parseSql(rs: SQLResultSet): string[] => {
  const result: Array<string> = new Array(rs.rows.length);
  for (let i = 0; i < rs.rows.length; i += 1) {
    result[i] = rs.rows.item(i);
  }
  return result;
}

const executeSql = (
  tr: SQLTransaction,
  query: string,
  ...args: string[]
):Promise<string[]> => new Promise((resolve, reject) => {
  tr.executeSql(
    query,
    args,
    (_, result) => resolve(parseSql(result)),
    (_, error) => {
      reject(error);
      return true;
    },
  );
});

export const createRnSqlite2Executor = (db: Database) : SqlExecutor => ({
  read: (query, ...args: string[]) => new Promise(
    (resolve) => db.readTransaction((tr) => resolve(executeSql(tr, query, ...args))),
  ),
  write: (query, ...args: string[]) => new Promise(
    (resolve) => db.transaction((tr) => resolve(executeSql(tr, query, ...args))),
  ),
});

Now we can use createRnSqlite2Executor in react-native application. For example using React Context API

Notes

  1. Make sure you are using the same sqlite version in jest and app environment
  2. It’s still not as good as testing on device because of the different environment, but good enough for the unit tests
  3. Ideally you would need to have integration tests for createRnSqlite2Executor too
  4. This approach potentially can be used with detox framework
  5. I don’t need to support multiple queries in one sql transaction, that’s why the SqlExecutor api doesn’t support it