@theredhead/data

Purpose

This package provides a minimalistic yet fully featured programming interface to work with databases. It supports sqlite and mysql and is supported by a full suite of tests with a high level of coverage. Initially intended for mysqlf to have something nicer to work with than the different native APIs offered by sqlite and mysql, but built in a way that makes it possible to add support for more databases in the future.

Features

• Easy to use
• Full CRUD and straight SQL executing.
• Fully built with typescript
• Optional peer dependencies on mysql2 and/or sqlite3. (only grab the engine you use)
• Source code freely available

Some quick examples using FetchRequest fluently through FetchRequestBuilder

const db = new SqliteDatabase('starwars.sqlite3');
const carrie = await db.from('actor')
  .where( 'surname = ? AND name = ?', 'Carrie','Fischer')
  .orderBy('surname', 'DESC')
  .fetch();

const mark = await db.from('actor')
  .whereAnd(
    ['surname = ?', 'Hamill'],
    ['name = ?', 'Mark']
  )
  .fetch();

const otiginalTrilogyMainCast = await db.from('actor')
  .whereOr(
    ['name = ?', 'Carrie'],
    ['name = ?', 'Mark']
    ['name = ?', 'Harrison'],
    ['name = ?', 'Peter'],
    ['name = ?', 'Kenny'],
    ['name = ?', 'Anthony'],
  )
  .fetch();

The interfaces making this possible:

interface IDbConnection {

  // straight sql execution
  executeScalar<T>(text: string, params: DbParams): Promise<T>;
  executeSingle<T extends PartialRecord>(text: string, params: DbParams): Promise<T>;
  executeArray<T extends Record>(text: string, params: DbParams): Promise<T[]>;
  executeNonQuery(text: string, params: DbParams): Promise<number>;

  // schema inspection
  tableExists(table: string): Promise<boolean>;
  columnExists(table: string, column: string): Promise<boolean>;

  // CRUD
  insert<T extends PartialRecord>(table: string, obj: T): Promise<T>;
  update<T extends Record>(table: string, obj: T): Promise<T>;
  delete<T extends Record>(table: string, id: number): Promise<T>;

  // FetchRequest support
  fetch<T extends Record>(request: FetchRequest): Promise<T[]>;
  from(table: string): FetchRequestBuilder;
}

interface FetchRequestBuilder {
  where(text: string, ...params: any[]): FetchRequestBuilder;
  whereAnd(...predicates: PredicateShorthand[]): FetchRequestBuilder;
  whereOr(...predicates: PredicateShorthand[]): FetchRequestBuilder;
  orderBy(column: string, direction: "ASC" | "DESC"): FetchRequestBuilder;
  page(pageIndex: number, pageSize: number): FetchRequestBuilder;
  fetch(): Promise<Record[]>;
}

type PredicateShorthand = [text: string, ...params: any[]];

interface FetchRequest {
  table: string;
  predicates: FetchPredicte;
  sort: Sort;
  pageIndex?: number;
  pageSize?: number;
}

interface FetchSimplePredicteClause {
  text: string;
  params: any[];
}

interface FetchCompoundPredicteClause {
  type: "AND" | "OR";
  predicates: FetchPredicates;
}

type FetchPredicteClause =
  | FetchSimplePredicteClause
  | FetchCompoundPredicteClause;

type FetchPredicates = FetchPredicteClause[];

type Sort = SortClause[];

interface SortClause {
  column: string;
  direction: "ASC" | "DESC";
}

Connecting to mysql

const connection = new MySqlConnection({
  host: "127.0.0.1",
  user: "user",
  password: "password",
  database: "database",
})

Connecting to sqlite

const connection = new SqliteConnection('/path/to/file');

Conventions that avoid problems

For the insert, update and delete methods

We expect every table to have a unique, numeric identity column. This means that for mysql tables, you must have a rowid column probably declared as rowid BIGINT NOT NULL UNIQUE AUTO_INCREMENT. Note that this is for the database, not for the data, so it does not need to be a primary key, but it must be unique. If you don't want it to be named rowid, you can set the rowIdColumn property on your MySqlConnection to something else.

sqlite does not need special consideration. but you will get the rowid column along with your records from FetchRequests and the insert/update methods.