react-native-quick-sqlite

React Native Quick SQLite

Fast SQLite for react-native.

    Copy typeORM patch-package from example dir
    npm i react-native-quick-sqlite typeorm
    npx pod-install
    Enable decorators and configure babel
  

This library uses JSI to directly call C++ code from JS. It provides a low-level API to execute SQL queries, therefore I recommend you use it with TypeORM.

Inspired/compatible with react-native-sqlite-storage and react-native-sqlite2.

Gotchas

• Javascript cannot represent intergers 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).
• Your app will now include C++, you will need to install the NDK on your machine for android.
• This library supports SQLite BLOBs which are mapped to JS ArrayBuffers, check out the sample project on how to use it
• From version 2.0.0 onwards errors are no longer thrown on invalid SQL statements. The response contains a status number, 0 signals correct execution, 1 signals an error.
• From version 3.0.0 onwards no JS errors are thown, every operation returns an object with a status field.
• If you want to run the example project on android, you will have to change the paths on the android/CMakeLists.txt file, they are already there, just uncomment them.

Use TypeORM

This package offers a low-level API to raw execute SQL queries. I strongly recommend to use TypeORM (with patch-package). TypeORM already has a sqlite-storage driver. In the example project on the patch folder you can a find a patch for TypeORM.

Follow the instructions to make TypeORM work with React Native (enable decorators, configure babel, etc), then apply the patch via patch-package and you should be good to go.

API

It is also possible to directly execute SQL against the db:

interface QueryResult {
  status: 0 | 1; // 0 for correct execution
  message: string; // if status === 1, here you will find error description
  rows: any[];
  insertId?: number;
}

interface BatchQueryResult {
  status?: 0 | 1;
  rowsAffected?: number;
  message?: string;
}

interface ISQLite {
  open: (dbName: string, location?: string) => any;
  close: (dbName: string) => any;
  executeSql: (
    dbName: string,
    query: string,
    params: any[] | undefined
  ) => QueryResult;
  executeSqlBatch: (
    dbName: string,
    commands: SQLBatchParams[]
  ) => BatchQueryResult;
}

In your code

import 'react-native-quick-sqlite';

// `sqlite` is a globally registered object, so you can directly call it from anywhere in your javascript
// the import on the top of the file only registers typescript types but it is not mandatory
const dbOpenResult = sqlite.open('myDatabase', 'databases');

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

Example queries

let result = sqlite.executeSql('myDatabase', 'SELECT somevalue FROM sometable');
if (!result.status) {
  // result.status undefined or 0 === sucess
  for (let i = 0; i < result.rows.length; i++) {
    const row = result.rows.item(i);
    console.log(row.somevalue);
  }
}

result = sqlite.executeSql(
  'myDatabase',
  'UPDATE sometable set somecolumn = ? where somekey = ?',
  [0, 1]
);
if (!result.status) {
  // result.status undefined or 0 === sucess
  console.log(`Update affected ${result.rowsAffected} rows`);
}

Batch execution allows transactional execution of a set of commands

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

Learn React Native JSI

If you want to learn how to make your own JSI module buy my JSI/C++ Cheatsheet, I'm also available for freelance work!

License

react-native-quick-sqlite is licensed under MIT.