Skip to content

Latest commit

 

History

History
206 lines (161 loc) · 5.33 KB

readme.md

File metadata and controls

206 lines (161 loc) · 5.33 KB

⚠️ This library is currently in alpha. Contributors wanted!


tusken

Postgres client from a galaxy far, far away.

  • your database is the source-of-truth for TypeScript generated types
  • type safety for all queries (even subqueries)
    • all built-in Postgres functions are available and type-safe
    • implicit type casts are accounted for
  • minimal, intuitive SQL building
    • shortcuts for common tasks (eg: get, put, and more)
    • identifiers are case-sensitive
  • lightweight, largely tree-shakeable
  • works with @tusken/cli to easily import CSV files, wipe data, generate a type-safe client, dump the schema for migrations, and more
  • you control the pg version as a peer dependency
  • query streaming with the .stream method (just install pg-query-stream and run tusken generate)

Migrations?

Use graphile-migrate.

Install

pnpm i tusken@alpha pg postgres-range postgres-interval
pnpm i @tusken/cli@alpha -D

Usage

First, you need a tusken.config.ts file in your project root, unless you plan on using the default config. By default, the Postgres database is assumed to exist at ./postgres relative to the working directory (customize with dataDir in your config) and the generated types are emitted into the ./src/generated folder (customize with schemaDir in your config).

import { defineConfig } from 'tusken/config'

export default defineConfig({
  dataDir: './postgres',
  schemaDir: './src/generated',
  connection: {
    host: 'localhost',
    port: 5432,
    user: 'postgres',
    password: ' ',
  },
  pool: {
    /* node-postgres pooling options */
  },
})

After running pnpm tusken generate -d <database> in your project root, you can import the database client from ./src/db/<database> as the default export.

import db, { t, pg } from './db/<database>'

The t export contains your user-defined Postgres tables and many native types. The pg export contains your user-defined Postgres functions and many built-in functions.

Creating, updating, deleting one row

Say we have a basic user table like this…

create table "user" (
  "id" serial primary key,
  "name" text,
  "password" text
)

To create a user, use the put method…

// Create a user
await db.put(t.user, { name: 'anakin', password: 'padme4eva' })

// Update a user (merge, not replace)
await db.put(t.user, 1, { name: 'vader', password: 'darkside4eva' })

// Delete a user
await db.put(t.user, 1, null)

Getting a row by primary key

Here we can use the get method…

await db.get(t.user, 1)

Selections are supported…

await db.get(
  t.user(u => [u.name]),
  1
)

Selections can have aliases…

await db.get(
  t.user(u => [{ n: u.name }]),
  1
)

// You can omit the array if you don't mind giving
// everything an alias.
await db.get(
  t.user(u => ({ n: u.name })),
  1
)

Selections can contain function calls…

await db.get(
  t.user(u => ({
    name: pg.upper(u.name),
  })),
  1
)

To select all but a few columns…

await db.get(t.user.omit('id', 'password'), 1)

Inner joins

// Find all books with >= 100 likes and also get the author of each.
await db.select(t.author).innerJoin(
  t.book.where(b => b.likes.gte(100)),
  t => t.author.id.eq(t.book.authorId)
)

 

What's planned?

This is a vague roadmap. Nothing here is guaranteed to be implemented soon, but they will be at some point (contributors welcome).

  • math operators
  • enum types
  • domain types
  • composite types
  • more geometry types
  • array-based primary key
  • ANY and SOME operators
  • transactions
  • explicit locking
  • views & materialized views
  • table inheritance
  • window functions
  • plugin packages
    • these plugins can do any of:
      • alter your schema
      • seed your database
      • extend the runtime API
    • auto-loading of packages with tusken-plugin-abc or @xyz/tusken-plugin-abc naming scheme
    • add some new commands
      • tusken install (merge plugin schemas into your database)
      • tusken seed (use plugins to seed your database)
  • NOTIFY/LISTEN support (just copy pg-pubsub?)
  • define Postgres functions with TypeScript
  • more shortcuts for common tasks

What could be improved?

This is a list of existing features that aren't perfect yet. If you find a good candidate for this list, please add it and open a PR.

Contributions are extra welcome in these places:

  • comprehensive "playground" example
  • subquery support is incomplete
    • bug: selectors cannot treat single-column set queries like an array of scalars
  • type safety of comparison operators
    • all operators are allowed, regardless of data type
    • see .where methods and is function
  • the jsonb type should be generic
    • with option to infer its subtype at build-time from current row data
  • missing SQL commands
    • WITH
    • GROUP BY
    • UPDATE
    • MERGE
    • USING
    • HAVING
    • DISTINCT ON
    • INTERSECT
    • CASE
    • etc