react-native-quick-sqlite

React Native Quick SQLite

    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 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).
• 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
  insertId?: number;
  rowsAffected: number;
  message?: string;
  rows?: {
    /** Raw array with all dataset */
    _array: any[];
    /** The lengh of the dataset */
    length: number;
  };
  /**
   * Query metadata, avaliable only for select query results
   */
  metadata?: ColumnMetadata[];
}

/**
 * Column metadata
 * Describes some information about columns fetched by the query
 */
declare type ColumnMetadata = {
  /** The name used for this column for this resultset */
  columnName: string;
  /** The 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. */
  columnDeclaredType: string;
  /**
   * The index for this column for this resultset*/
  columnIndex: 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`);
}

In some scenarios, dynamic applications may need to get some metadata information about the returned resultset. This can be done testing the returned data directly, but in some cases may not be enough, like when data is stored outside storage datatypes, like booleans or datetimes. When fetching data directly from tables or views linked to table columns, SQLite is able to identify the table declared types:

let result = sqlite.executeSql('myDatabase', 'SELECT int_column_1, bol_column_2 FROM sometable');
if (!result.status) {
  // result.status undefined or 0 === sucess
  for (let i = 0; i < result.metadata.length; i++) {
    const column = result.metadata[i];
    console.log(`${column.columnName} - ${column.columnDeclaredType}`);
    // Output:
    // int_column_1 - INTEGER
    // bol_column_2 - BOOLEAN
  }
}

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.