Db

yarn add @ff00ff/db

A (mostly) type-safe Postgres query builder for TypeScript. This is not for the front-end, but for the back-end.

Quick start

const rows = await db.list
  .select('id', 'name', 'createdAt', 'value')
  .from(db.list)
  .where(db.list.createdAt.gt(db.now().minus(`2 days`)).or(db.list.value.eq(0)))
  .limit(10);

A select should not require declaring an additional interface explicitly.

The type of rows is automatically derived from the table. You do not have to specify this. For example, column value with type INTEGER is not declared NOT NULL and thus is null or number.

const rows: {
  id: string;
  name: string;
  createdAt: Date;
  value: number | null;
}[];

Db is under active development. Some things work, but some things are a bit weird and will improve in the coming months.

Create

To create a table. Using db migrations generate you can generate migrations of your tables and db migrations apply applies the migrations in your database. When you applied the migrations, change your tables and generate your migrations again, a new migration file with only the changes is created. See Migrations section for more info.

class List {
  id = new UuidColumn().primary().notNull().default(new UuidGenerateV4());
  createdAt = new TimestampWithTimeZoneColumn().notNull().default(new Now());
  name = new TextColumn().notNull();
  value = new IntegerColumn();
}

class ListItem {
  id = new UuidColumn().primary().notNull().default(new UuidGenerateV4());
  createdAt = new TimestampWithTimeZoneColumn().notNull().default(new Now());
  listId = new UuidColumn().notNull().references<Database>(db => db.list.id);
  name = new TextColumn().notNull();
}

Update

To update rows.

const numberOfUpdates = await db.list
  .update({
    name: `New Name`
  })
  .where(db.list.id.eq(`acb82ff3-3311-430e-9d1d-8ff600abee31`));

Insert

To insert a row.

const numberOfRows = await db.list
  .insert({
    id: undefined,
    createdAt: undefined,
    name: `My List`,
  });

You do need to explicitly set all values. The return type is automatically handled.

const rows = await db.list
  .insert({
    id: undefined,
    createdAt: undefined,
    name: `My List`,
  })
  .returning(`id`, `createdAt`, `name`);

When using returning() the return value is automatically changed from an integer (number of affected rows) to an array of objects with keys matching the columns specified.

Transactions

You can call db.transaction(callback) which begins a transaction and depending on the promise you return in the transaction will commit or rollback the transaction.

Best practice is to shadow your database variable, generally db, so you do not mistakenly execute queries outside the transaction.

db.transaction(db => {
  const listId = await db.list
    .insert({
      id: undefined,
      createdAt: undefined,
      name: `My List`,
    })
    .returning(`id`)
    .first();

  await db.listItem
    .insert({
      id: undefined,
      createdAt: undefined,
      listId,
      name: `My Item`,
    });
});

Even though transactions are designed, it's implementation is still a work-in-progress.

Migrations

The idea is to automatically generate migrations based on the changes in your tables. A rough first version of the CLI is working, but it's picky about you project structure:

• It expects your db instance to be at src/db.ts.
• It writes your migrations to migrations/.

For example, in src/db.ts:

import { createDatabase, UuidColumn, TextColumn, IntegerColumn } from '@ff00ff/db';

class Test {
  id = new UuidColumn().primaryKey().notNull().default(new UuidGenerateV4());
  name = new TextColumn().notNull();
  value = new IntegerColumn();
}

export const db = createDatabase({
  test: new Test(),
});

export type Database = typeof db;

It's a best practice to place your tables in src/tables instead of directly in src/db.ts.

db migrations generate should read your tables, read your migrations and generate a new migration based on the changes between them.

Up next

• Get feedback on the public API.
• Refactor internal API as some bits are a bit in a proof on concept state.
• Extend SQL keywords e.g. UNION, WITH, INSERT INTO-SELECT, etc.
• Improve upsert, delete.
• Support indices and enums.

Stay in touch!

Please star or watch this repo. Because Db is still in development you can sign up to receive the announcement once we hit 1.0 http://eepurl.com/dgySSz (this is a Mailchimp sign up form). You can also start a discussion on e.g. GitHub to give feedback on the public API.

Versioning

A final note on the versioning: we're at version 0.X until we consider Db not production-ready. Once we consider the project production-ready we bump to 1.0 and stricly abide to semver.