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 provides a low-level API to execute SQL queries, fast bindings via JSI.

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.

API

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) => { status: 0 | 1 };
  close: (dbName: string) => { status: 0 | 1 };
  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;
}

Usage

// Import as early as possible, auto-installs bindings
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`);
}

Async versions are also available if you have too much SQL to execute

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

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 example patch via patch-package.

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.