Skip to main content

SQLite RxStorage

This RxStorage is based on SQLite and is made to work with Node.js, Electron, React Native and Capacitor or SQLite via wasm in the browser. It can be used with different so called sqliteBasics adapters to account for the differences in the various SQLite bundles and libraries that exist.

Performance comparison with other storages

The SQLite storage is a bit slower compared to other Node.js based storages like the Filesystem Storage because wrapping SQLite has a bit of overhead and sending data from the JavaScript process to SQLite and backwards increases the latency. However for most hybrid apps the SQLite storage is the best option because it can leverage the SQLite version that comes already installed on the smartphones OS (iOS and android). Also for desktop electron apps it can be a viable solution because it is easy to ship SQLite together inside of the electron bundle.

SQLite performance - Node.js

Using the SQLite RxStorage

To use the SQLite storage you have to import getRxStorageSQLite from the RxDB Premium 👑 package and then add the correct sqliteBasics adapter depending on which sqlite module you want to use. This can then be used as storage when creating the RxDatabase. In the following you can see some examples for some of the most common SQLite packages.

Usage with Node.js SQLite

import {
createRxDatabase
} from 'rxdb';
import {
getRxStorageSQLite,
getSQLiteBasicsNode
} from 'rxdb-premium/plugins/storage-sqlite';

/**
* In Node.js, we get use the SQLite database
* from the 'sqlite' npm module.
* @link https://www.npmjs.com/package/sqlite3
*/
import sqlite3 from 'sqlite3';

const myRxDatabase = await createRxDatabase({
name: 'exampledb',
storage: getRxStorageSQLite({
/**
* Different runtimes have different interfaces to SQLite.
* For example in node.js we have a callback API,
* while in capacitor sqlite we have Promises.
* So we need a helper object that is capable of doing the basic
* sqlite operations.
*/
sqliteBasics: getSQLiteBasicsNode(sqlite3)
})
});

Usage with React Native

  1. Install the react-native-quick-sqlite npm module
  2. Import getSQLiteBasicsQuickSQLite from the SQLite plugin and use it to create a RxDatabase:
import {
createRxDatabase
} from 'rxdb';
import {
getRxStorageSQLite,
getSQLiteBasicsQuickSQLite
} from 'rxdb-premium/plugins/storage-sqlite';
import { open } from 'react-native-quick-sqlite';

// create database
const myRxDatabase = await createRxDatabase({
name: 'exampledb',
multiInstance: false, // <- Set multiInstance to false when using RxDB in React Native
storage: getRxStorageSQLite({
sqliteBasics: getSQLiteBasicsQuickSQLite(open)
})
});

If react-native-quick-sqlite does not work for you, as alternative you can use the react-native-sqlite-2 library instead:

import {
getRxStorageSQLite,
getSQLiteBasicsWebSQL
} from 'rxdb-premium/plugins/storage-sqlite';
import SQLite from 'react-native-sqlite-2';
const storage = getRxStorageSQLite({
sqliteBasics: getSQLiteBasicsWebSQL(SQLite.openDatabase)
});

Usage with SQLite Capacitor

  1. Install the sqlite capacitor npm module
  2. Add the iOS database location to your capacitor config
{
"plugins": {
"CapacitorSQLite": {
"iosDatabaseLocation": "Library/CapacitorDatabase"
}
}
}
  1. Use the function getSQLiteBasicsCapacitor to get the capacitor sqlite wrapper.
import {
createRxDatabase
} from 'rxdb';
import {
getRxStorageSQLite,
getSQLiteBasicsCapacitor
} from 'rxdb-premium/plugins/storage-sqlite';

/**
* Import SQLite from the capacitor plugin.
*/
import {
CapacitorSQLite,
SQLiteConnection
} from '@capacitor-community/sqlite';
import { Capacitor } from '@capacitor/core';

const sqlite = new SQLiteConnection(CapacitorSQLite);

const myRxDatabase = await createRxDatabase({
name: 'exampledb',
storage: getRxStorageSQLite({
/**
* Different runtimes have different interfaces to SQLite.
* For example in node.js we have a callback API,
* while in capacitor sqlite we have Promises.
* So we need a helper object that is capable of doing the basic
* sqlite operations.
*/
sqliteBasics: getSQLiteBasicsCapacitor(sqlite, Capacitor)
})
});

Database Connection

If you need to access the database connection for any reason you can use getDatabaseConnection to do so:

import { getDatabaseConnection } from 'rxdb-premium/plugins/storage-sqlite'

It has the following signature:

getDatabaseConnection(
sqliteBasics: SQLiteBasics<any>,
databaseName: string
): Promise<SQLiteDatabaseClass>;

Known Problems of SQLite in JavaScript apps

  • Some JavaScript runtimes do not contain a Buffer API which is used by SQLite to store binary attachments data as BLOB. You can set storeAttachmentsAsBase64String: true if you want to store the attachments data as base64 string instead. This increases the database size but makes it work even without having a Buffer.

  • The SQlite RxStorage works on SQLite libraries that use SQLite in version 3.38.0 (2022-02-22) or newer, because it uses the SQLite JSON methods like JSON_EXTRACT. If you get an error like [Error: no such function: JSON_EXTRACT (code 1 SQLITE_ERROR[1]), you might have a too old version of SQLite.

  • expo-sqlite cannot be used on android (but it works on iOS) because it uses an outdated SQLite version. This is fixed if you use Expo SDK version 50 or never.

  • To debug all SQL operations, you can pass a log function to getRxStorageSQLite() like this:

const storage = getRxStorageSQLite({
sqliteBasics: getSQLiteBasicsCapacitor(sqlite, Capacitor),
// pass log function
log: console.log.bind(console)
});