100% type-safe query builder for node-postgres :: Generated types, call any function, tree-shakable, implicit type casts, and more

Related tags

Learning resource postgres typescript query-builder
Overview

⚠️ 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
  • multiple primary keys in one row
  • 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
    • ORDER BY
    • GROUP BY
    • UPDATE
    • MERGE
    • USING
    • HAVING
    • DISTINCT ON
    • UNION / INTERSECT
    • CASE
    • etc
Comments
  • Add `cast` helper

    Add `cast` helper

    This creates an Expression object that renders the CAST ... AS operator.

    cast(20, t.text)

    This will require an export const statement for each type in the t namespace, as there needs to be a way to infer the identifier that trails the AS operator when building the query at runtime.

    enhancement good first issue 
    opened by aleclarson 3
  • Postgres type functions

    Postgres type functions 

    // A. Cast expression to built-in type
t.text(expr)

// B. Cast primary key to inlined columns
t.post(post => [
  post.id,
  post.text,
  // Map the author ID to a user row. Select the name only.
  t.user(post.author, user => [user.id, user.name]),
])

    Exhibit A would replace #3.

    Exhibit B would return the following columns:

    "id": int4
"text": text
"author.id": int4
"author.name": text

    …and Tusken would convert author.id and author.name into author: { id, name } for you.

    enhancement priority: high 
    opened by aleclarson 2
  • Add helper for converting json array into a set

    Add helper for converting json array into a set

    Whose SQL would be similar to:

    FROM json_to_recordset(yourJsonValue -> 'rows') AS rows(columns JSON),
     json_to_record(columns) AS cols(
       "Nombre" TEXT,
       "rutaEsquema" TEXT,
       "TipoDeComponente" TEXT,
       "detalleDelComponente" TEXT)
    duplicate enhancement 
    opened by aleclarson 1
  • Add `pg.default` placeholder

    Add `pg.default` placeholder

    This tokenizes into DEFAULT, which is a keyword used for inserting/updating a column with the default value for that column. Adding pg.default is useful for expressions that may or may not resolve to the default column value.

    enhancement 
    opened by aleclarson 0
  • Type brands for primary/foreign keys

    Type brands for primary/foreign keys

    Goal: Extra type safety. Prevent primary keys for one table from being used in a query of another table.

    // The generated type of a primary/foreign key
id: t.int4 & t.key<'user.id'>

    To convert an external value into a branded key:

    // Note: Falsy values are passed through.
const userId = t.user.id(req.searchParams.get('userId'))

    Casting like what's shown above doesn't include runtime validation. This is only for type safety.

    enhancement 
    opened by aleclarson 0
  • Tree-shaking doesn't work with Esbuild

    Tree-shaking doesn't work with Esbuild

    https://github.com/evanw/esbuild/issues/1420

    We recommend using Rollup when bundling a package that uses Tusken to interact with your Postgres database.

    upstream bug 
    opened by aleclarson 0
  • Multiple databases with one client

    Multiple databases with one client

    If only one database is configured by tusken.config.ts, the current behavior is kept.

    db.select(t.user)

    If multiple databases are configured like…

    export default defineConfig({
  database: ['foo', 'bar'],
})

    …then it works like this:

    db.foo.select(t.foo.user)
db.bar.select(t.bar.user)

    The t.foo.user could be shortened to t.user if and only if db.bar has no user table.

    enhancement priority: low 
    opened by aleclarson 0
Releases([email protected])
Owner
alloc
alloc
GitHub
A simple query builder, it will helps to develop DSL query for Elasticsearch

Elasticsearch Dynamic Query Builder A simple query builder, it will helps to develop DSL query for elasticsearch Installation You can start it from np

Hashemi Rafsan 4 Nov 20, 2022
Framework agnostic CLI tool for routes parsing and generation of a type-safe helper for safe route usage. 🗺️ Remix driver included. 🤟

About routes-gen is a framework agnostic CLI tool for routes parsing and generation of a type-safe helper for safe route usage. Think of it as Prisma,

Stratulat Alexandru 192 Jan 2, 2023
Simple "everyday CRUD" Postgres queries with perfect TypeScript types

Crudely Typed Simple "everyday CRUD" Postgres queries with perfect TypeScript types. Zero dependencies. Designed to work with pg-to-ts and node-postgr

Dan Vanderkam 26 Dec 26, 2022
Contains html file showcasing Earthquake related data generated in the form of VR model, ArcGIS API with real-time earthquake feed and video of simulation of earthquake generated in blender

Module-EADGI-Project-All about Earthquakes Introduction Contains html file showcasing Earthquake related data generated in the form of VR model, ArcGI

Abhishek Rawat 2 Jun 9, 2022
⚡🚀 Call multiple view functions, from multiple Smart Contracts, in a single RPC query!

ethers-multicall ⚡ ?? Call multiple view functions, from multiple Smart Contracts, in a single RPC query! Querying an RPC endpoint can be very costly

Morpho Labs 20 Dec 30, 2022
Compile-time tests for types. Useful to make sure types don't regress into being overly-permissive as changes go in over time.

expect-type Compile-time tests for types. Useful to make sure types don't regress into being overly-permissive as changes go in over time. Similar to

Misha Kaletsky 82 Jan 8, 2023
100 Days of Code is a self improvement tool, where you make a commitment to solve 1 coding problem everyday for the next 100 days.

100 Days of Code is a self-improvement tool, where you make a commitment to solve 1 coding problem everyday for the next 100 days. This repository includes a range of tasks, lessons, resources, and challenges on various programming languages to help improve programming skills.

Tarleton Computer Society 7 Dec 14, 2022
Grupprojekt för kurserna 'Javascript med Ramverk' och 'Agil Utveckling'

JavaScript-med-Ramverk-Laboration-3 Grupprojektet för kurserna Javascript med Ramverk och Agil Utveckling. Utvecklingsguide För information om hur utv

Svante Jonsson IT-Högskolan 3 May 18, 2022
Hemsida för personer i Sverige som kan och vill erbjuda boende till människor på flykt

Getting Started with Create React App This project was bootstrapped with Create React App. Available Scripts In the project directory, you can run: np

null 4 May 3, 2022
Kurs-repo för kursen Webbserver och Databaser

Webbserver och databaser This repository is meant for CME students to access exercises and codealongs that happen throughout the course. I hope you wi

null 14 Jan 3, 2023
Wrap a function with bun-livereload to automatically reload any imports inside the function the next time it is called

bun-livereload Wrap a function with bun-livereload to automatically reload any imports inside the function the next time it is called. import liveRelo

Jarred Sumner 19 Dec 19, 2022
Fancytree - JavaScript tree view / tree grid plugin with support for keyboard, inline editing, filtering, checkboxes, drag'n'drop, and lazy loading

Fancytree Fancytree (sequel of DynaTree 1.x) is a JavaScript tree view / tree grid plugin with support for keyboard, inline editing, filtering, checkb

Martin Wendt 2.6k Jan 9, 2023
A movie schema sandbox for playing with EdgeDB and the EdgeQL query builder, pre-loaded with MCU data

The EdgeDB MCU sandbox ?? This is a sandbox for playing with EdgeDB and the EdgeQL query builder. It includes a simple movie database schema (dbschema

EdgeDB 13 Nov 9, 2022
Inside-out promise; lets you call resolve and reject from outside the Promise constructor function.

Inside-out promise; lets you call resolve and reject from outside the Promise constructor function.

Lily Scott 3 Feb 28, 2022
Interactive 3D plotting with a simple function call using Three.js

About Generate interactive 3d plots with a simple function call. Function returns a Three.js scene which can be customized as needed. Basic function c

Nathan Epstein 89 Oct 20, 2022
🧬 A type builder for pagination with prisma and type-graphql.

?? Prisma TypeGraphql Pagination Prisma TypeGraphql Pagination builds prisma pagination types for type-graphql. import { ... } from 'type-graphql'

Arthur Fiorette 2 Apr 21, 2022
A simple C++ function parser/tagger based on tree-sitter

What the func?! - A simple C++ function parser/tagger This project implements a simple C++ function parser, what-the-func, based on the tree-sitter C+

Software and Systems Engineering Chair 4 - Faculty of Informatics 3 May 21, 2022
fcall, fetch and call any remote hot functions, anywhere, anytime, without installations or configurations.

fcall, fetch and call any remote hot functions, anywhere, anytime, without installations or configurations.

立党 Lidang 4 Sep 20, 2022
A quickstart AWS Lambda function code generator. Downloads a template function code file, test harness file, sample SAM deffiniation and appropriate file structure.

Welcome to function-stencil ?? A quickstart AWS Lambda function code generator. Downloads a template function code file, test harness file, sample SAM

Ben Smith 21 Jun 20, 2022