While working with expo-sqlite, I ran into few challenges: database migrations, integration testing, and strange resource locks and connection exceptions.
Database migrations are needed because in the app lifetime there will be table additions, modifications and deletions. You could go without migrations in the same way you would go without git versioning of your code.
Apps that use SQLite have queries and mutations. How do you know that those are correct?
You don't - you test it. SQLite is an embedded database, it should work on any system. Ideally, I should be able to run SQLite related tests using Node.js, on my/any machine.
How to setup SQLite in Expo project
You could follow the official recommended setup, but that's not what I was using.
I'm using a slightly different setup than recommended in the official guide.
I had an issue with their SQLiteProvider, so I made my own. You might be coming to this article after searching for:
- Call to function
NativeDatabase.initAsynchas been rejected. Access to closed resource.
This is a hard one to reproduce, but you will eventually stumble on it as you reload expo app on your emulator or device.
I think that the source of the issue is at this line https://github.com/expo/expo/blob/6626b9458d85e31086c28a54fa1a493c9ee58995/packages/expo-sqlite/src/hooks.tsx#L174
Since prepare is an async function it might get triggered again, but the old one isn't yet finished. I believe it should be triggered only once per databaseName change.
My solution:
const isMounting = React.useRef(true);
useEffect(() => {
const setup = async () => {
if (!isMounting.current) {
return;
}
isMounting.current = false;
try {
const _db = await openDatabaseAsync(databaseName, undefined);
await onInit(_db);
logger.log(`Mounted sqlite provider`);
setDb(_db);
setLoading(false);
} catch (e: any) {
setError(e);
}
};
setup();
}, [databaseName, db]);This should help ensure that the setup function is only called once during the component's lifecycle, and prevents potential re-triggering or overlapping executions.
How to do migrations in Expo-SQLite
Example on expo-sqlite (migrateDbIfNeeded) shows you a basic kind of database setup and versioning. SQLite has an extension that enables to version the database.
Whenever you do a migration, bump PRAGMA user_version.
Each migration should be applied in a transaction, meaning all is committed or none.
Migration libraries usually keep track of a migration log table. This log is invaluable when debugging issues, as it provides a clear record of which migrations succeeded. Something like this:
CREATE TABLE IF NOT EXISTS migrations (
id INTEGER PRIMARY KEY,
migration_name TEXT NOT NULL,
applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
You would call migrations insert after each successful run of your migrations.
You could also have a different table, with status pending, done and error with error message. Then you've got a couple of inserts. Before start, after done or after error block.
Some strategies involve having a commit and rollback type of migration (up and down). If a migration errors, then down is called and rollbacks to a previous state.
I'm not sure if having a down is necessary when migration is applied in a transaction.
Anyways, my demo database migration process relies only on user_version PRAGMA:
private async runMigration(
userVersion: number,
migrations: DatabaseMigration[]
): Promise<number> {
return migrations
.slice(userVersion)
.reduce(async (previousPromise, currentMigration, index) => {
const previousVersion = await previousPromise;
if (previousVersion < userVersion + index) {
return previousVersion; // Stop if a previous migration failed
}
await this.db.withTransactionAsync(async () => {
try {
logger.log(`Executing ${currentMigration.name}`);
await currentMigration.up(this.db);
} catch (error) {
throw new Error(`Could not execute migration`, { cause: error });
}
});
return userVersion + index + 1;
}, Promise.resolve(userVersion));
}
public async apply(migrations: DatabaseMigration[]): Promise<UserVersion> {
const userVersion = await this.version();
const nextVersion = await this.runMigration(
userVersion.user_version, migrations);
await this.saveVersion(nextVersion);
return this.version();
}One example of such migration is:
import { SQLiteDatabase } from 'expo-sqlite';
import { DatabaseMigration } from '@/types';
const migration: DatabaseMigration = {
name: 'create initial tables',
async up(db: SQLiteDatabase): Promise<void> {
await db.execAsync(`
CREATE TABLE task (
id INTEGER PRIMARY KEY,
task TEXT NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);
`);
},
};
export default migration;
How to test expo SQLite?
I haven't seen much documentation and posts on testing the expo sqlite, and those that I see are mostly basic unit test examples. But just to get you started, setup jest for your project:
npm i --save-dev jest-expo jest @types/jest ts-nodeEdit package.json and add
"test": "jestto the scripts.
Create a jest.config.ts and add:
import type { Config } from 'jest';
const config: Config = {
verbose: true,
preset: 'jest-expo',
transformIgnorePatterns: [
'node_modules/(?!((jest-)?react-native|@react-native(-community)?)|expo(nent)?|@expo(nent)?/.*|@expo-google-fonts/.*|react-navigation|@react-navigation/.*|@sentry/react-native|native-base|react-native-svg)',
],
};
export default config;
When you'll first run npm test , it should report no tests.
Back to my problem: I want to run integration tests on my machine, on a service that's directly using SQLite.
As I mentioned already, SQL may be syntactically and semantically incorrect.
You could have syntax errors in the query or you're querying it wrong.
When I tried to run expo-sqlite tests on my machine, I got:
TypeError: _ExpoSQLiteNext.default.NativeDatabase is not a constructor
Ofc, it's made to run only on iOS and android.
Solution for this problem is to introduce an adapter. Adapter pattern is used when you need incompatible interfaces to communicate.
In my case, that means replacing the NativeDatabase implementation with something that can run on my machine, but it has to adhere the same interface.
sqlite3 is what I use instead of ExpoSqliteNext . To be exact, in memory sqlite. That's perfect for running tests. I don't need to worry about truncating the db. It gets destroyed once my tests are run.
Good thing about jest is that you can mock everything. For mocking a node module, you can directly mock it inside your test case or provide a manual mocks with __mocks__ adjacent to the node_modules directory.
Since I want to mock expo-sqlite, I need to mock functions that I'm calling from it.
Create a __mocks__/expo-sqlite/index.ts
import { SQLiteRunResult, SQLiteVariadicBindParams } from 'expo-sqlite/src/SQLiteStatement';
import { RunResult } from 'sqlite3';
const sqlite3 = require('sqlite3').verbose();
export const openDatabaseAsync = (_name: string) => prepareDb();
const prepareDb = () => {
const db = new sqlite3.Database(':memory:');
return {
withTransactionAsync: async (task: () => Promise<void>) => {
try {
await db.exec('BEGIN');
await task();
await db.exec('COMMIT');
} catch (e) {
await db.exec('ROLLBACK');
throw e;
}
},
execAsync: async (source: string): Promise<void> => {
return new Promise((resolve, reject) => {
db.exec(source, (result: RunResult, err: Error | null) => {
if (err) {
reject(err);
} else {
resolve(undefined);
}
});
});
},
runAsync: async (source: string, ...params: SQLiteVariadicBindParams): Promise<SQLiteRunResult> => {
return new Promise((resolve, reject) => {
db.run(source, params, (result: RunResult, err: Error | null) => {
if (err) {
reject(err);
} else {
resolve(
result
? ({
lastInsertRowId: result.lastID,
changes: result.changes,
} as SQLiteRunResult)
: null
);
}
});
});
},
getAllAsync: async <T>(source: string, ...params: SQLiteVariadicBindParams): Promise<any[]> => {
return new Promise<any[]>((resolve, reject) => {
// @ts-ignore
db.all<T>(source, params, (err: Error | null, rows: any[]) => {
if (err) {
reject(err);
} else {
resolve(rows);
}
});
});
},
getFirstAsync: async <T>(stmt: string, params: any): Promise<T> => {
return new Promise((resolve, reject) => {
// @ts-ignore
db.get<T>(stmt, params, (err: Error | null, row: T) => {
if (err) {
reject(err);
} else {
resolve(row);
}
});
});
},
closeAsync: async () => {
db.close();
},
};
};
__mocks__/expo-sqlite/index.ts
Function signature is similar, you can use unnamed parameters and named parameters in the same way. As for return type, node sqlite3 package does not return a promise, but works in callbacks. That's why I have to wrap the function in a Promise and resolve/reject inside a callback.
One example of my testcase:
import { openDatabaseAsync, SQLiteDatabase } from 'expo-sqlite';
import DbMigrationRunner from '@/DbMigrationRunner';
import migrations from '../migrations';
describe('DbMigrationRunner', () => {
let sqlite: SQLiteDatabase;
beforeEach(async () => {
sqlite = await openDatabaseAsync('test.db');
});
it('should migrate', async () => {
const runner = new DbMigrationRunner(sqlite);
const currVersion = (await runner.version()).user_version;
expect(currVersion).toBe(0);
const lastVersion = (await runner.apply(migrations)).user_version;
expect(lastVersion).toBeGreaterThan(0);
});
afterEach(async () => {
await sqlite.closeAsync();
});
});
By using an in-memory database, you don't need to worry about truncating or clearing the data. Once database is closed, everything disappears. In this scenario, new SQLite is recreated before each test.
Conclusion
All the example code I've demonstrated (and more) is available in my GitHub repository. While I didn’t aim for full code coverage, the purpose of this article was to provide you with a solid MVP foundation for your test cases, and I hope I've accomplished that.
That said, I have a confession to make: I ultimately decided not to use SQLite for my app.
Here’s why.
I needed a real-time database synchronization, and things got complicated when users began sharing data. Imagine this scenario: user goes offline, enters some data, and then reconnects. They log in on another device, using the same account, and all of their data needs to sync across both devices. This is just one part of the many complex scenarios you'd need to consider when implementing real-time data syncing with relational database.
Frankly, it was too much work. So, I switched to Firebase Realtime Database.
I ended up building a shopping list app—yet another "millionth" to-do app. I missed Google’s Shopping List feature, which was discontinued in favor of Google Keep, so I decided to create my own. Plus, a new project always presents an opportunity to learn something new, and Firebase allowed me to do just that!
Thank you for following along, and I hope this guide has been helpful in providing you with some useful insights and inspiration.