react-native-quick-sqlite

    yarn add react-native-quick-sqlite
    npx pod-install

Quick SQLite embeds the latest version of SQLite and provides a low-level JSI-backed API to execute SQL queries. By using an embedded SQLite you get access the latest security patches and latest features.

Performance metrics are intentionally not posted, anecdotic testimonies suggest anywhere between 2x and 5x speed improvement.

Gotchas

• Javascript cannot represent integers larger than 53 bits, be careful when loading data if it came from other systems. Read more.
• It's not possible to use a browser to debug a JSI app, use Flipper (for android Flipper also has SQLite Database explorer).

API

/**
 * All SQLite command results will have at least this status definition:
 * Specific statements or actions can bring more data, relative to its context
 * status: 0 or undefined for correct execution, 1 for error
 *  message: if status === 1, here you will find error description
 */
export interface StatementResult {
  status?: 0 | 1;
  message?: string;
}

interface QueryResult extends StatementResult {
  insertId?: number;
  rowsAffected: number;
  rows?: {
    /** Raw array with all dataset */
    _array: any[];
    /** The length of the dataset */
    length: number;
  };
  /**
   * Query metadata, available only for select query results
   */
  metadata?: ColumnMetadata[];
}

/**
 * Column metadata
 * Describes some information about columns fetched by the query
 * columnDeclaredType - declared column type for this column, when fetched directly from a table or a View resulting from a table column. "UNKNOWN" for dynamic values, like function returned ones.
 */
interface ColumnMetadata = {
  columnName: string;
  columnDeclaredType: string;
  columnIndex: number;
};

/**
 * status: 0 or undefined for correct execution, 1 for error
 * message: if status === 1, here you will find error description
 * rowsAffected: Number of affected rows if status == 0
 */
export interface BatchQueryResult extends StatementResult {
  rowsAffected?: number;
}

/**
 * Result of loading a file and executing every line as a SQL command
 * Similar to BatchQueryResult
 */
export interface FileLoadResult extends BatchQueryResult {
  commands?: number;
}

interface ISQLite {
  open: (dbName: string, location?: string) => StatementResult;
  close: (dbName: string) => StatementResult;
  delete: (dbName: string, location?: string) => StatementResult;
  attach: (
    mainDbName: string,
    dbNameToAttach: string,
    alias: string,
    location?: string
  ) => StatementResult;
  detach: (mainDbName: string, alias: string) => StatementResult;
  executeSql: (
    dbName: string,
    query: string,
    params: any[] | undefined
  ) => QueryResult;
  asyncExecuteSql: (
    dbName: string,
    query: string,
    params: any[] | undefined,
    cb: (res: QueryResult) => void
  ) => void;
  executeSqlBatch: (
    dbName: string,
    commands: SQLBatchParams[]
  ) => BatchQueryResult;
  asyncExecuteSqlBatch: (
    dbName: string,
    commands: SQLBatchParams[],
    cb: (res: BatchQueryResult) => void
  ) => void;
  loadSqlFile: (dbName: string, location: string) => FileLoadResult;
  asyncLoadSqlFile: (
    dbName: string,
    location: string,
    cb: (res: FileLoadResult) => void
  ) => void;
}

WebSQL wrapper

You can get a WebSQL wrapper (meant to be used with TypeORM or other drivers) with a different global call. It's a simple wrapper around the low level API.

openDatabase(
  options: IConnectionOptions,
  ok: (db: IDBConnection) => void,
  fail: (msg: string) => void
): IDBConnection

Usage

Just import the package and fire away

// Thanks to @mrousavy for this installation method, see one example: https://github.com/mrousavy/react-native-mmkv/blob/75b425db530e26cf10c7054308583d03ff01851f/src/createMMKV.ts#L56
import { QuickSQLite } from 'react-native-quick-sqlite';

const dbOpenResult = QuickSQLite.open('myDatabase', 'databases');

// status === 1, operation failed
if (dbOpenResult.status) {
  console.error('Database could not be opened');
}

Simple queries

The basic query is synchronous, it will block rendering on large operations, below there are async versions.

let { status, rows } = QuickSQLite.executeSql(
  'myDatabase',
  'SELECT somevalue FROM sometable'
);
if (!status) {
  rows.forEach((row) => {
    console.log(row);
  });
}

let { status, rowsAffected } = QuickSQLite.executeSql(
  'myDatabase',
  'UPDATE sometable SET somecolumn = ? where somekey = ?',
  [0, 1]
);
if (!status) {
  console.log(`Update affected ${rowsAffected} rows`);
}

Transactions

Transactions are supported. However, due to the library being opinionated and mostly not throwing errors you need to return a boolean (true for correct execution, false for incorrect execution) to either commit or rollback the transaction.

JSI bindings are fast but there is still some overhead calling executeSql for single queries, if you want to execute a large set of commands as fast as possible you should use the executeSqlBatch method below, it still uses transactions, but only transmits data between JS and native once.

QuickSQLite.transaction('myDatabase', (tx) => {
  const { status } = tx.executeSql(
    'UPDATE sometable SET somecolumn = ? where somekey = ?',
    [0, 1]
  );

  if (status) {
    return false;
  }

  return true;
});

Async transactions are also possible, but the API is based on promises and/or a boolean response:

QuickSQLite.asyncTransaction('myDatabase', async (tx) => {
  // If the function throws (rejects) the transaction will be rolled back
  const res = tx.promiseExecuteSql(
    'UPDATE sometable SET somecolumn = ? where somekey = ?',
    [0, 1]
  );
  // You must also return true to signal a correct transaction
  return true;
});

Batch operation

Batch execution allows transactional execution of a set of commands

const commands = [
  ['CREATE TABLE TEST (id integer)'],
  ['INSERT INTO TEST (id) VALUES (?)', [1]][
    ('INSERT INTO TEST (id) VALUES (?)', [2])
  ][('INSERT INTO TEST (id) VALUES (?)', [[3], [4], [5], [6]])],
];
const result = QuickSQLite.executeSqlBatch('myDatabase', commands);
if (!result.status) {
  // result.status undefined or 0 === success
  console.log(`Batch affected ${result.rowsAffected} rows`);
}

Dynamic Column Metadata

In some scenarios, dynamic applications may need to get some metadata information about the returned result set.

This can be done testing the returned data directly, but in some cases may not be enough, for example when data is stored outside sqlite datatypes. When fetching data directly from tables or views linked to table columns, SQLite is able to identify the table declared types:

let { status, metadata } = QuickSQLite.executeSql(
  'myDatabase',
  'SELECT int_column_1, bol_column_2 FROM sometable'
);
if (!status) {
  metadata.forEach((column) => {
    // Output:
    // int_column_1 - INTEGER
    // bol_column_2 - BOOLEAN
    console.log(`${column.columnName} - ${column.columnDeclaredType}`);
  });
}

Async operations

You might have too much SQL to process and it will cause your application to freeze. There are async versions for some of the operations. This will offload the SQLite processing to a different thread.

QuickSQLite.asyncExecuteSql(
  'myDatabase',
  'SELECT * FROM "User";',
  [],
  ({ status, rows }) => {
    if (status === 0) {
      console.log('users', rows);
    }
  }
);

Attach or Detach another databases

SQLite supports to attach or detach another database files into your main database connection through an alias. You can do any operation you like on this attached databases like JOIN results across tables in different schemas, or update data or objects. This databases can have different configurations, like journal modes, cache settings.

You can, at any moment detach a database that you don't need anymore. Note: You don't need to detach an attached database before closing your connection. Closing the main connection will dettach any dettached databases. SQLite have a limit for attached databases: A default of 10, and a global max of 125

References: Attach - Detach

const result = QuickSQLite.attach(
  'mainDatabase',
  'statistics',
  'stats',
  '../databases'
);

// Database is attached successfully
if (!result.status) {
  const data = QuickSQLite.executeSql(
    'mainDatabase',
    'SELECT * FROM some_table_from_mainschema a INNER JOIN stats.some_table b on a.id_column = b.id_column'
  );
  // Consume the results
  if (!data.status) {
  }
}

// You can detach databases at any moment
const detachResult = QuickSQLite.detach('mainDatabase', 'stats');
if (!detachResult.status) {
  // Database de-attached
}

Use built-in SQLite

On iOS you can use the embedded SQLite, when running pod-install add an environment flag:

QUICK_SQLITE_USE_PHONE_VERSION=1 npx pod-install

On Android it is not possible to link (using C++) the embedded SQLite. It is also a bad idea due to vendor changes, old android bugs, etc. Unfortunately this means this library will add some mbs to your app size.

Use TypeORM

You can use this library as a driver for TypeORM, when initializing the connection use:

datasource = new DataSource({
  type: 'react-native',
  database: 'typeormdb',
  location: '.',
  driver: require('react-native-quick-sqlite'),
  entities: [Book, User],
  synchronize: true,
});

If you are using Node 14+, TypeORM is currently broken with React Native. You can patch your node-modules installation and apply the fix in this issue.

Loading existing DBs

The library creates/opens databases by appending the passed name plus, the documents directory on iOS and the files directory on Android, this differs from other SQL libraries (some place it in a www folder, some in androids databases folder, etc.).

If you have an existing database file you want to load you can navigate from these directories using dot notation. e.g. ../www/myDb.sqlite. Note that on iOS the file system is sand-boxed, so you cannot access files/directories not in your app bundle directories.

Alternatively you can place/move your database file using the one of the many react-native fs libraries.

Enable compile-time options

By specifying pre-processor flags, you can enable optional features like FTS5, Geopoly, etc.

iOS

Add a post_install block to your <PROJECT_ROOT>/ios/Podfile like so:

post_install do |installer|
  installer.pods_project.targets.each do |target|
    if target.name == "react-native-quick-sqlite" then
      target.build_configurations.each do |config|
        config.build_settings['GCC_PREPROCESSOR_DEFINITIONS'] ||= ['$(inherited)', '<SQLITE_FLAGS>']
      end
    end
  end
end

Replace the <SQLITE_FLAGS> part with flags you want to add. For example, you could add SQLITE_ENABLE_FTS5=1 to GCC_PREPROCESSOR_DEFINITIONS to enable FTS5 in the iOS project.

Android

You can specify flags via <PROJECT_ROOT>/android/gradle.properties like so:

quickSqliteFlags="<SQLITE_FLAGS>"

More

If you want to learn how to make your own JSI module buy my JSI/C++ cheat sheet.

License

MIT License.