prequel/README.md

Prequel

Streamlined, type-safe SQLite without too much cruft.

(I figured I should add on to the massive pile of sql-related projects called "prequel")

Usage

Prequel is designed to handle some of the boilerplate for getting going with a SQLite db. It handles setting up tables and basic CRUD statements and otherwise wraps a SQLite DB to allow for using raw SQL alongside the pre-built queries.

import { Prequel } from "@foundry/prequel";

interface UserRow {
  user_id: number;
  name: string;
}

const db = new Prequel("db.sqlite");
const users = db.table("Users", {
  user_id: {
    type: "INTEGER",
    primary: true,
  },
  name: "TEXT",
});

users.insertPartial({
  name: "Flynn",
});

users.findAll(); // [{user_id: 1, name: "Flynn"}]

const findByName = db.prepare<UserRow>(`SELECT * FROM ${users} WHERE name = ?`);
findByName.get("Flynn"); // {user_id: 1, name: "Flynn"}

Defining columns

When defining the shape of a table, you can specify either a string containing a valid SQLite data type (limited to the five main types), or an object with further options.

{
  type: "TEXT", // or "INTEGER", "REAL", "BLOB", "NULL"
  nullable: true, // Default is non-nullable columns
  primary: true, // Default is non-primary column
  misc: "" // Specify misc sql to append to the column def
  references: othertable.reference() // Specifies a foreign key ref
  cascade: true, // Cascading deletes. Default is off
  default: "" // Optionally provide a default value
}

API

new Prequel(filename?: string)

Creates a new Prequel object which wraps a SQLite db.

prequel.table<RowShape>(name: string, columns: ColumnDefs)

Ensures the existance of the specified table in the DB

prequel.prepare<RowShape, Params>(query: string)

Alias for the underlying database.query call, preparing a SQL statement and caching the statement if possible.

prequel.enableWAL()

Executes the pragma statement to enable WAL mode

prequel.close()

Safely close the database

prequel.transaction(fn: () => void)

Execute a function within a transaction. If the function throws an error, the transaction is rolled back.

Returns true when the transaction was successful. Throws any errors that occur during the transaction.

new Table<RowShape>(db: Database | Prequel, name: string, columns: ColumnDefs)

Create a new table. Can be used without a Prequel object. If you pass in a Prequel object instead of a raw Database object, the table will be attached to the Prequel object as if it were created with prequel.table()

table.reference(colName?: string)

Returns a reference usable with the reference: field in a column definition. Optionally provide a column name to reference. If no column name is provided, the tables primary key column is used.

table.insert(data: RowShape)

Insert a fully formed row into the table

table.insertPartial(data: Partial<RowShape>)

Insert a partial row into the table. Useful for generating IDs and leverage defaults.

table.update(data: RowShape)

Given a complete row with a valid primary key value, update the row in the db.

This can only be used if a column has been set to primary: true.

table.findOnebyId(id: string | number)

Return a single row with the given ID

table.findAll()

Return all of the rows in this table

table.deleteById(id: string | number)

Delete a row with the given ID

table.size()

Count the rows in the table

table.toString()

The toString() behavior of a Table object returns the name of the table. This allows you to use the table object in queries for consistency:

SELECT * FROM ${users} WHERE user_id = ?

table.name

The name of the table

table.db

The Database object this Table is a part of

table.columns

A list of the column names on this table in the order they exist in the DB

table.schema

The raw SQL that was used to generate this table