RFC: NativeScript SQLite Implementation

[herefishyfish]

Overview

This repository is designed to consolidate various API layers for SQLite into a unified interface that can seamlessly transition between embedded and system APIs. One of the biggest challenges is the absence of a standard approach towards this goal. Numerous frameworks including Web, React Native, Capacitor, Cordova, and various existing NativeScript implementations have developed their own solutions. Previously, I was aligning our efforts with the React Native Quick SQLite API design. However, I've reconsidered this path as it may not be the best approach.

API Proposal

This is the type for the options argument in the class constructor. It contains properties that determine how the SQLite database is opened.

export interface SqliteOpenOptions {
  name: string;           // Name of the database.
  path: string;           // Path to the database file.
  version: number;        // Version of the database.
  flags: any              // Database flags
  multithread: boolean;   // Whether to allow multiple threads to access the database.
  readOnly: boolean;      // Whether to open the database in read-only mode.
}

SQLite Class

This class encapsulates all interactions with the SQLite database, with the instance being instantiated by the constructor

const sqlite = new NSCSQLite(options);

export declare class NSCSQLite {
  constructor(options: SqliteOpenOptions);

  // Returns true if the database connection is open.
  isOpen(): boolean;

  // Executes a SQL query with optional parameters and returns the results.
  // When a single query is provided, it returns an array of results.
  // When an array of queries is provided it's treated as a transaction and returns an array of arrays of results.
  execute<T = any>(query: string, params?: any[]): QueryResult<T>;
  execute<T = any>(query: string[], params?: any[][]): QueryResult<T>[];

  // Executes a transaction. The action argument is a function that should return a promise.
  // If the action function calls the optional cancel function, the transaction is rolled back.
  // Returns a promise that resolves to the return value of the action function.
  transaction<T = any>(action: (cancel?: () => void) => Promise<T>): Promise<T>

  // Returns the database version number.
  getVersion( ): number;

  // Attaches another database file to the current database connection.
  attach(alias: string, path: string): void;

  // Detaches an attached database from the current database connection.
  detach(alias: string): void;

  // Closes the database connection.
  close(): void;

  // Deletes the database file.
  delete(): void;

  // Imports JSON file
  importJson(json: string): void;

  // Exports JSON file
  exportJson(): string;
}

export type QueryResult<T = any> = {
  insertId?: number; // The ID of the last inserted row.
  rowsAffected: number; // The number of rows affected by the last query.
  rows?: T[]; // Array of results as T.
};

Considerations

• The 'readonly' option, as currently defined, may become obsolete due to the potential precedence of database flags. Such flags offer a more granular control over database attributes and may consequently provide a more powerful and flexible alternative to the binary 'readonly' parameter.
• The multithreaded option in the open options determines whether all operations should be multithreaded or not. As a comparison, React Native employs executeAsync() to specify if a statement should be executed in a separate thread.
• This implementation is designed with extensibility in mind. For instance, integration with SQLiteCipher should be as simple as extending the base classes to pass a 'key' within the options and to incorporate additional methods for managing encryption.

Alternatives

1. Use V8 to expose SQLite methods directly to JavaScript. With this approach, we would create bindings that allow JavaScript code to call SQLite functions directly. This could potentially offer more flexibility and control over the database operations. However, this would also mean that developers would need to write more code and deal directly with the SQLite API, which may not be as user-friendly as the batteries included approach that is typical in these libraries.

The proposed SQLite class and API provides a middle ground. It provides a user-friendly and idiomatic JavaScript API while also exposing the power and flexibility of SQLite. It handles the details of the V8 and SQLite integration, freeing developers from the need to write low-level code or deal directly with the SQLite C API. This makes it a convenient and effective solution for most NativeScript developers.

References:

• NativeScript

  • NativeScript Community
  • NativeScript-SQLite (unmaintained)

• React Native

  • React Native Quick SQLite
  • Expo SQLite

• Capacitor

  • Capacitor Community

• Cordova

  • Cordova SQLite Storage

[shirakaba]

Just a quick comment. I used the SQLite C APIs for a Swift project yesterday and it wasn't too harrowing.

I think offering APIs that are 1:1 with the C APIs might be sensible just because they're ready-documented and there are plenty of existing examples for them out there. If providing direct V8 bindings seems hard, could manually map them out instead. Though would have to be careful about needless copies of data. NativeScript JSI may come in handy (and could actually make use of the existing JSI code in react-native-quick-sqlite).

But its true that the C API feels particularly unjavascripty and so may be intimidating. The hard part is deciding the right abstraction for it. react-native-quick-sqlite does seem loved by the community and successfully used, so it's a perfectly good starting point.

[peterlazar1993]

Any plans to support sqlite3_update_hook? This would make it compatible with libraries like tinybase

Also please consider aligning the API with already existing JS based sqlite implementations so that we can use it with tools like drizzle

[herefishyfish]

Any plans to support sqlite3_update_hook? This would make it compatible with libraries like tinybase

Yep, for the current Android/iOS implementations without "metal" we should already be able to directly access those calls if they're provided by the platform. I think moving forward with the "metal" build I'll expose as many of the direct calls to the C implementation as I can. Having the abstractions in C++ would probably yield slightly better results, but we're probably talking in nanoseconds.