[Proposal] Include simplified api

[alpha0010]

For common procedures (e.g. basic selects/inserts), the complexity of the current api can feel excessive. I propose we include an alternate api for simple use cases.

To use, get a Database instance:

import sqlite from 'react-native-sqlite-storage/simple';
const db = await sqlite.open('bar.sqlite');

Then make calls on it:

class Database {
  /**
   * Get all results from the query.
   */
  async all<T> (sql: string, params?: DataType[]): Promise<T[]>;

  /**
   * Close the database.
   */
  async close (): Promise<void>;

  /**
   * Execute a statement.
   */
  async exec (sql: string, params?: DataType[]): Promise<void>;

  /**
   * Get the first result from the query.
   */
  async get<T> (sql: string, params?: DataType[]): Promise<?T>;
}

---

Thoughts? Suggestions?

---

See #336 for a potential implementation.

[silverAndroid]

Would it be possible to get the same implementation inside a transaction as well?

[mitevdev]

Is there any progress on this?

[alpha0010]

For transactions, one potential is:

class Transaction {
  async exec (sql: string, params?: DataType[]): Promise<void>;
}

class Database {
  // [...]
  async transaction (querier: (txn: Transaction) => Promise<void>): Promise<void>;
}

Usage:

db->transaction(async txn => {
  // If the query fails, the transaction will be rolled back. However,
  // processing in this function will still continue.
  txn->exec('INSERT [...]');
  txn->exec('INSERT [...]');

  // To abort immediately if a query fails:
  await txn->exec('INSERT [...]');

  // To select data (discouraged since transactions are for modification),
  // use the database object:
  const foo = await db->get('SELECT [...]');
});

If any query fails, or if an exception is thrown, the transaction will be rolled back.

---

This is no longer a priority at my work place, so I cannot devote much time to it if there is not more interest from the community/library author.

[mitevdev]

I am migrating an iOS App into React Native and I want to reuse the existing SQLite database, that's why I came across this project. What I really would like to have is TS support for the recordsets, currently there is no any and I do not find any fork where this is already done. Are there any attempts on this before I go deeper?

[alpha0010]

We did switch to TypeScript internally; use the native code from this library, and add the following file to your app:

import { NativeModules } from "react-native";
const { SQLite } = NativeModules;

export type DataType = number | string;

export interface QueryFailResult {
  qid: number;
  result: { message: string };
  type: "error";
}

export interface QuerySuccessResult<T> {
  qid: number;
  result: { insertId?: number; rows?: T[]; rowsAffected?: number };
  type: "success";
}

export type QueryResult<T> = QueryFailResult | QuerySuccessResult<T>;

export class Database {
  private lastQueryID: number;
  private name: string;

  public constructor(name: string) {
    this.lastQueryID = 0;
    this.name = name;
  }

  /**
   * Get all results from the query.
   */
  public async all<T>(sql: string, params?: DataType[]): Promise<T[]> {
    const results = await this.executeBatch<T>([{ sql, params }]);
    const result = results[0];
    if (result.type === "success") {
      if (result.result.rows == null) {
        // Statement was not a SELECT.
        return [];
      } else {
        return result.result.rows;
      }
    } else {
      throw new Error(result.result.message);
    }
  }

  /**
   * Close the database.
   */
  public async close(): Promise<void> {
    return new Promise((resolve, reject) => {
      SQLite.close({ path: this.name }, () => resolve(), reject);
    });
  }

  /**
   * Execute a statement.
   */
  public async exec(sql: string, params?: DataType[]): Promise<void> {
    await this.all(sql, params);
  }

  /**
   * Execute a batch of queries, returning the status and output of each.
   */
  public async executeBatch<T>(
    queries: { sql: string; params?: DataType[] }[]
  ): Promise<QueryResult<T>[]> {
    if (queries.length === 0) {
      return [];
    }

    const executes: {
      qid: number;
      sql: string;
      params: DataType[] | null;
    }[] = [];
    for (const query of queries) {
      this.lastQueryID += 1;
      executes.push({
        qid: this.lastQueryID,
        sql: query.sql,
        params: query.params == null ? [] : query.params
      });
    }

    return new Promise((resolve, reject) => {
      SQLite.executeSqlBatch(
        {
          dbargs: { dbname: this.name },
          executes
        },
        resolve,
        reject
      );
    });
  }

  /**
   * Get the first result from the query.
   */
  public async get<T>(sql: string, params?: DataType[]): Promise<T | null> {
    const rows = await this.all<T>(sql, params);
    return rows.length > 0 ? rows[0] : null;
  }
}

/**
 * Open the database.
 */
async function open(filename: string): Promise<Database> {
  return new Promise((resolve, reject) => {
    SQLite.open(
      { name: filename, dblocation: "nosync" },
      () => resolve(new Database(filename)),
      reject
    );
  });
}

export const sqlite = { open };

[rodperottoni]

There are already types for this library available at https://www.npmjs.com/package/@types/react-native-sqlite-storage.

Simply add it to your project: npm install --save-dev @types/react-native-sqlite-storage.

[alpha0010]

Those types are for the current api of this library. The above ts wraps this library's native code in a completely different api; fewer features in this api, but when said features are not needed, this api can be simpler to use.