herefishyfish / nativescript-sqlite Goto Github PK

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