Building an offline app isn’t just about working without an internet connection. It should also let users back up their data and import it on another device or platform.
For example, I built a shopping list app for Play Store, App Store and the web. No login needed, just use the offline features. You can backup your data on the web and import it on iOS or Android. It just works.
I'm not open sourcing the project, but I want to share what I've learned. That's why I made an expo sqlite series.
Prerequisites
Install expo dependencies, they're mostly needed on native part of the implementation:
$ expo install expo-file-system expo-document-picker expo-sharing
Accessing database files
When testing cross-platform data imports, the adb tool can be useful:
# pushes file into android Downloads directory
adb push testbackup.sqlite "storage/emulated/0/Download/test.db"
# pushes file into app's SQLite directory, but doesn't work if not rooted
adb push testbackup.sqlite "data/data/<app_package>/files/SQLite/test.db"Windows users
If working from Windows, don't start the path with a slash, you'll get something like
adb: error: failed to copy 'db.sqlite' to 'C:/Program Files/Git/...
A simpler alternative: Use Android Studio's Device Explorer to upload/download files.
Exporting Data from Expo SQLite
When exporting SQLite I'd usually go with shell command like .backup , which ensures that my database is consistent and there's no loss of data.
Here, I just copy the current db file and pass it to the Sharing dialog, which opens either Drive, email, quick share...
If my db was in write ahead log mode, executing a force checkpoint could flush the changes if there's some extra shm or wal file created (PRAGMA wal_checkpoint(FULL)).
import * as FileSystem from 'expo-file-system';
import * as Sharing from 'expo-sharing';
import * as DocumentPicker from 'expo-document-picker';
export const backupDatabase = async (db: SQLiteDatabase, backupName: string) => {
try {
await db.execAsync('PRAGMA wal_checkpoint(FULL)');
const backupPath = `${FileSystem.documentDirectory}SQLite/${backupName}`;
const databasePath = `${FileSystem.documentDirectory}SQLite/${db.databaseName}`;
await FileSystem.copyAsync({
from: databasePath,
to: backupPath,
});
await Sharing.shareAsync(backupPath, { mimeType: 'application/x-sqlite3' });
await FileSystem.deleteAsync(backupPath, { idempotent: true });
} catch (error) {
logger.error('Failed to backup the database:', error);
}
};Exporting Data from SQL.js
Exporting from SQL.js in-memory db is a lot simpler. I haven't checked if and how WAL is supported, but I'd just call export to have it export a binary db. I use export already as a workaround for storing state of SQLite on the web.
export const backupDatabase = async (db: SQLiteDatabase, backupName: string) => {
try {
await db.execAsync('PRAGMA wal_checkpoint(FULL)');
const fileContent = await db.export();
const blob = new Blob([fileContent], { type: 'application/x-sqlite3' });
const url = URL.createObjectURL(blob);
const link = document.createElement('a');
link.href = url;
link.download = backupName;
link.click();
URL.revokeObjectURL(url);
} catch (error) {
logger.error('Failed to backup the database:', error);
}
};Importing Data to Expo SQLite
To import SQLite into expo native I had to first close the current database connection, do the import and then open a new connection.
Deleting the wal files is to ensure data doesn't get corrupted. It's just uncommited data from the previous database.
export const restoreDatabase = async (db: SQLiteDatabase): Promise<void> => {
try {
const result = await DocumentPicker.getDocumentAsync({
type: '*/*',
copyToCacheDirectory: true,
multiple: false,
});
if (result.canceled) {
return;
}
const backupPath = result.assets[0].uri;
const backupExists = await FileSystem.getInfoAsync(backupPath);
if (!backupExists.exists) {
return;
}
await db.closeAsync();
const databasePath = `${FileSystem.documentDirectory}SQLite/${db.databaseName}`;
const walFilePath = `${FileSystem.documentDirectory}SQLite/${db.databaseName}-wal`;
const shmFilePath = `${FileSystem.documentDirectory}SQLite/${db.databaseName}-shm`;
await FileSystem.deleteAsync(walFilePath, { idempotent: true });
await FileSystem.deleteAsync(shmFilePath, { idempotent: true });
logger.log('Restoring database from:', backupPath);
await FileSystem.copyAsync({
from: backupPath,
to: databasePath,
});
} catch (error) {
logger.error(error, 'Could not restore');
throw error;
}
};Importing data to SQL.js
export const restoreDatabase = async (db: SQLiteDatabase): Promise<void> => {
try {
const input = document.createElement('input');
input.type = 'file';
input.accept = '*/*';
return new Promise<void>((resolve, reject) => {
input.onchange = async (event: any) => {
const file = event.target.files[0];
if (!file) {
resolve(undefined);
return;
}
const reader = new FileReader();
reader.onload = async (e) => {
try {
const fileContent = e.target?.result as ArrayBuffer | null;
if (!fileContent) {
reject('Corrupted file!');
return;
}
const uint8Array = new Uint8Array(fileContent);
// @ts-ignore
await db.import(uint8Array);
resolve(undefined);
} catch (error) {
reject(error?.message || 'Unknown error!');
}
};
reader.onerror = () => {
reject('Error reading the file!');
};
reader.readAsArrayBuffer(file);
};
input.click();
});
} catch (error) {
logger.error(error, 'Could not restore');
throw error;
}
};I had to wrap the file reader events into a Promise because next action depends on this action being finished.
You might notice I don't specify mime type when reading the data. I tried application/x-sqlite3 and application/octet-stream. However, when selecting the data, file selector doesn't show the targeted files. I must be making a mistake somewhere.
All of the code is available at https://github.com/amarjanica/react-native-sqlite-expo-demo
Youtube: https://youtu.be/56VQxU_kgwE
