SQLite unit testing in React-Native/Cordova applications
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
- Make sure you are using the same
sqlite
version injest
and app environment - It’s still not as good as testing on device because of the different environment, but good enough for the unit tests
- Ideally you would need to have integration tests for
createRnSqlite2Executor
too - This approach potentially can be used with detox framework
- I don’t need to support multiple queries in one sql transaction, that’s why the
SqlExecutor
api doesn’t support it