dbts v0.1.1

DBTS

An ergonomic Postgres query builder for TypeScript.

A Work in Progress

This project is still very much under construction. It was inspired by (and some of the API was shamelessly stolen from) the mammoth library.

This is mostly meant to be a fun project for me to work on in my spare time. It is missing many features and should not be incorporated into any other project at this point.

Goals

Embrace SQL

I love SQL and this project does too. Importantly, DBTS is not an ORM and its usage should make sense to someone who has never seen the library but who knows SQL.

Painless Client Typing

You shouldn't need to declare interfaces that are not well-typed in order to marshall your data into and out of SQL. The appropriate db.select call in DBTS should return the correct interface with all of the known types.

Examples

Declaring Tables

Simply write your tables as a class that extends Table using the various Column classes. Pass an object with all of your tables into the Database call.

import { Client } from "pg";
import { Database, IntColumn, SerialColumn, Table, TextColumn } from "dbts";

class UserTable extends Table {
  id = new SerialColumn();
  name = new TextColumn();
  age = new IntColumn();
  address = new TextColumn().nullable();
}

const pg = new Client();
await pg.connect();
const db = Database(pg, {
  users: new UserTable(),
});
await db.createTables();

There's currently no support for migrations or anything similar. db.createTables simply issues a CREATE TABLE statement for every table that you've defined.

Querying Data

// Has type Array<{ id: number; name: string; address: string | null }>
const users = db.select(db.users, "id", "name", "address");

You can also add WHERE clauses. The and and or functions provide a syntactically nice way to do this.

const teenagers = db
  .select(db.users, "id", "name", "address")
  .where(and(db.users.age.gte(13), db.users.age.lte(19)));

If you try do do bad things™, you'll get type errors.

// TS2345: Argument of type '"foo"' is not assignable to parameter of type '"id" | "name" | "address"
db.select(db.users, "foo");

db.select(db.users, "address").then((users) => {
  users.forEach(({address}) => {
    // TS2531: Object is possibly 'null'.
    console.log(address.toUpperCase());
  })
);

Inserting Data

Single-row insertion is super simply (and statically typed!). Columns with defaults (including nullable columns) need not be specified.

await db
  .insertInto(db.users)
  .values({,
    name: "Sylvia The Cat",
  });

If you try do do bad things™, you'll get type errors.

// TS2322: Type 'string' is not assignable to type 'number'.
await db.insertInto(db.users).values({
  name: "Sylvia The Cat",
});

Updating Data

await db
  .update(db.users)
  .set({
    name: "Sylvia, Queen of Cats",
  })
  .where(db.users.name.eq("Sylvia The Cat"));

SQL Safety

All parameters passed into queries are forwarded to Postgres via parameter values ($1, $2, ...). You can inspect the SQL generated as well.

console.log(
  db
    .insertInto(db.users)
    .values({
      id: 123,
      name: "Sylvia The Cat",
    })
    .$toSQL(),
);
// INSERT INTO users (id, name) VALUES ($1, $2);

Warts

• There is some inconsistency about what things should be constructed with new and which things are constructed just with function calls. The reason for this is because classes can't implement interfaces with generics.