Simple "everyday CRUD" Postgres queries with perfect TypeScript types

Overview

Crudely Typed

codecov

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

Quickstart

Install pg-to-ts and this library, and generate a schema file:

npm install -D pg-to-ts
npm install crudely-typed
pg-to-ts generate -c $POSTGRES_URL --output src/dbschema.ts

Then generate your queries using a TypedSQL instance:

// src/demo.ts
import {TypedSQL} from 'crudely-typed';
import {tables} from './dbschema';

const typedSql = new TypedSQL(tables);

const getDocById = typedSql.table('docs').selectByPrimaryKey();
//    ^? const getDocById: (db: Queryable, where: { id: string }) => Promise<Doc | null>

Crudely Typed supports the basic create / read / update / delete queries. See API for details. Crudely Typed is not a full-fledged query builder, nor does it aspire to be. See FAQ for more on this.

API

TypedSQL

Everything starts with a TypedSQL instance, which you construct from the tables export of a pg-to-ts DB schema. There are many schema generators derived from the old SchemaTS project, but crudely-typed specifically requires pg-to-ts schemas because they have just the type references it needs.

import {TypedSQL} from 'crudely-typed';
import {tables} from './dbschema';  // <-- output of pg-to-ts

const typedSql = new TypedSQL(tables);

table

From a TypedSQL instance, you can produce a TableBuilder object for any of your tables:

const usersTable = typedSql.table('users');

The remaining functions in crudely-typed are all defined on this table object. Each of the functions comes in regular and ByPrimaryKey variants, e.g. table.select() and table.selectByPrimaryKey().

table.select

table.select(): (db: Queryable) => Promise<Row[]>

with no parameters, this is select all in the order returned by the database.

table.select(options: {
    where?: (Column | SQLAny<Column>)[],
    columns?: Column[],
    orderBy?: [col: Column, order: 'ASC' | 'DESC'][];
    limitOne?: boolean;
    join?: {
        [resultingColumnName: string]: Column
    };
}): (db: Queryable, where: ...) => Promise<...>

Looking at each option individually:

  • where adds a WHERE clause to the query:
const docsTable = typedSql.table('docs');
const getDocsByAuthor = docsTable.select({where: ['author']});
//    ^? const getDocsByAuthor: (db: Queryable, where: {author: string}) => Promise<Doc[]>

If you specify multiple where clauses, they'll be combined with AND. You may also specify an ANY clause to match one of many values. See Where clasues, below.

  • columns restricts the set of columns that are retrieved (by default all columns are retrieved, i.e. SELECT *). You can use this to avoid fetching large, unneeded columns.
const docsTable = typedSql.table('docs');
const getTitles = docsTable.select({columns: ['title']});
//    ^? const getTitles: (db: Queryable) => Promise<{title: string}[]>
  • orderBy sorts the output, i.e. it adds an ORDER BY clause to the query. Adding an orderBy clause does not affect the type of the select.
const docsTable = typedSql.table('docs');
const getDocs = docsTable.select({orderBy: [['author', 'ASC']]});
//    ^? const getTitles: (db: Queryable) => Promise<Doc[]>
  • limitOne adds a LIMIT 1 clause to the query, so that it always returns either zero or one row. This changes the return type from T[] to T | null.
const docsTable = typedSql.table('docs');
const getTitle = docsTable.select({where: ['title'], limitOne: true});
//    ^? const getTitle: (
//         db: Queryable,
//         where: {title: string}
//       ) => Promise<Doc | null>
  • join adds 1-1 joins to the query for columns that are foreign keys into other tables. The row from the joined table comes back as an object under the property name that you specify. You may specify multiple joins, though they cannot be nested and they must all be 1-1.
const docsTable = typedSql.table('docs');
const getDocs = docsTable.select({
    join: {
        author: 'author_id',
        publisher: 'publisher_id',
    }
});
// ^? const getDocs: (
//      db: Queryable
//    ) => Promise<(Doc & {author: Author; publisher: Publisher })[]>

You don't need to specify the joined table or its type; crudely-typed has all the information it needs from the dbschema. If you specify a set of columns to select with columns, the foreign key need not be one of those columns.

table.selectByPrimaryKey

There's a helper for the common case of selecting by primary key:

const docsTable = typedSql.table('docs');
const getDocById = docsTable.selectByPrimaryKey();
//    ^? const getDocById: (
//         db: Queryable,
//         where: { id: string }
//       ) => Promise<Doc | null>

This is exactly equivalent to docsTable.select({where: ['id'], limitOne: true}) but saves you some typing.

You may use the columns and join and with selectByPrimaryKey:

const getDocById = docsTable.selectByPrimaryKey({
    columns: ['title'],
    join: { author: 'author_id' }
});
const doc = await getDocById(db, {id: 'doc-id'});
//    ^? const doc: {title: string; author: Author} | null

table.insert

table.insert(): (db: Queryable, row: RowInput) => Promise<Row>

This generates a dynamic INSERT query based on the properties of row. The RowInput type models the required and optional columns in the table. If an optional property is omitted from row, then it will be set to its default value and observable in the returned Row. If a required property is omitted, you'll get a type error.

const insertDoc = docsTable.insert();
const doc = await insertDoc({author: 'Mark Twain', title: 'Huckleberry Finn'});
//    ^? const doc: Doc

It's sometimes desirable to prevent certain columns from being set, e.g. the primary key. This can be enforced with the disallowColumns option:

const insertDoc = docsTable.insert({ disallowColumns: ['id'] });
//    ^? const insertDoc: (db: Queryable, row: Omit<DocInput, 'id'>) => Promise<Doc>
insertDoc({id: 'some id'});
//         ~~ type error!
const doc = await insertDoc({author: 'Mark Twain', title: 'Huckleberry Finn'});
//    ^? const doc: Doc

table.insertMultiple

This is indentical to insert but allows multiple rows to be inserted with a single query.

const docsTable = typedSql.table('docs');
const insertDocs = docsTable.insertMultiple();
//    ^? const insertDocs: (
//         db: Queryable,
//         rows: readonly DocInput[]
//       ) => Promise<Row[]>
const docs = await insertDocs([
    {title: 'Huckleberry Finn', author: 'Mark Twain'},
    {title: 'Poor Richard', author: 'Ben Franklin'}
]);

insertMultiple also supports disallowColumns, just like insert.

table.update

table.update({
    where?: (Column | SQLAny<Column>)[],
    set?: Column[],
    limitOne?: boolean,
}): (db: Queryable, where: ..., set: ...) => Promise<...>

With a where and a set clause, this updates specific columns on specific rows. All affected rows are returned:

const docsTable = typedSql.table('docs');
const setYear = docsTable.update({where: ['title'], set: ['year']});
//    ^? const setYear: (
//         db: Queryable,
//         where: {title: string},
//         set: {year: number}
//       ) => Promise<Doc[]>
const newDocs = setYear(db, {title: 'Huck Finn'}, {year: 1872});
//    ^? const newDocs: Promise<Doc[]>

Without a set clause, this performs a dynamic update based on the param:

const update = docsTable.update({where: ['title']});
//    ^? const update: (
//         db: Queryable,
//         where: {title: string},
//         set: Partial<Doc>
//       ) => Promise<Doc[]>
const newDocs = setYear(db, {title: 'Huck Finn'}, {year: 1872});
//    ^? const newDocs: Promise<Doc[]>

The where clause can include multiple columns, in which case it operates as an AND, and can support ANY clauses. See Where clasues, below.

Without a where clause, this updates all rows in the table.

If you pass limitOne: true, at most one row will be updated and the function will return T | null instead of T[]:

const update = docsTable.update({where: ['title'], limitOne: true});
//    ^? const update: (
//         db: Queryable,
//         where: {title: string},
//         set: Partial<Doc>
//       ) => Promise<Doc | null>
const newDoc = setYear(db, {title: 'Huck Finn'}, {year: 1872});
//    ^? const newDocs: Promise<Doc>

table.updateByPrimaryKey

This is a shortcut for updating a row by its table's primary key:

const update = docsTable.updateByPrimaryKey(set: ['year']);
//    ^? const update: (
//         db: Queryable,
//         where: {id: string},
//         set: {year: number}
//       ) => Promise<Doc | null>
const newDoc = setYear(db, {id: 'isbn-123'}, {year: 1872});
//    ^? const newDoc: Promise<Doc>

If you pass a set option, then this updates a fixed set of columns. If you don't, it's dynamic based on its parameter, just like update.

table.delete

table.delete(options: {
    where?: (Column | SQLAny<Column>)[];
    limitOne?: boolean;
}): (db: Queryable, where: ...) => Promise<...>

The where clause for delete works exactly as it does for select. It may be set to an array of columns or ANY clauses. See Where clauses, below.

const docsTable = typedDb.table('docs');
const deleteByTitle = docsTable.delete({ where: ['title'] });
//    ^? const deleteByTitle: (db: Queryable, where: {title: string}) => Promise<Doc[]>

The delete function returns the rows that it deletes (if any). As with select, if you pass limitOne: true then it will return T | null instead of T[]:

const docsTable = typedDb.table('docs');
const deleteByTitle = docsTable.delete({ where: ['title'], limitOne: true });
//    ^? const deleteByTitle: (db: Queryable, where: {title: string}) => Promise<Doc | null>

If you don't specify a where clause or limitOne, this acts as "delete all".

table.deleteByPrimaryKey

This is a helper for the common case where you want to delete rows by their primary key:

const docsTable = typedDb.table('docs');
const deleteDoc = docsTable.deleteByPrimaryKey();
//    ^? const deleteDoc: (db: Queryable, where: {id: string}) => Promise<Doc | null>

This is exactly equivalent to docsTable.delete({ where: ['id'], limitOne: true }).

Queryable

All generated functions take a Queryable object as their first parameter:

interface Queryable {
  query(sql: string, ...args: any[]): Promise<Result>;
}

The Client and Pool classes from pg conform to this and can be used with crudely-typed.

Where clauses

TODO:

  • Multiple columns are ANDed
  • You can generate ANY matchers
  • You can generate a mix

Joins

TODO

FAQ

  • Isn't this just a query builder?

  • Why does crudely-typed generate functions instead of running them?

  • Can you add support for X?

Probably not! The goal of this library is to handle the simplest queries for you with perfect types and a minimum of fuss. Supporting every SQL query is absolutely not a goal. At some point you should just write SQL (see below).

  • What should I do for complex queries?

  • Why not use PgTyped for all my queries?

  • Why not use an ORM?

  • What's with the name?

CRUD is short for Create, Read, Update, Delete. I wanted something that had "crud" in the name but didn't sound like "crud". "crudely" fit the bill. It's also a play on DefinitelyTyped and is a bit tongue in cheek since the types in this library are anything but crude.

How this works

The tables object that pg-to-ts outputs includes all the TypeScript types and runtime values needed to generate well-typed queries. From there it's just a bunch of TypeScript generics that should be entirely invisible to you, the user. See index.ts for all the details. The following blog posts may be helpful for understanding some of the techniques being used:

  • intersect what you have
  • unionize/objectify
  • display of type
  • currying and classes
Comments
  • Check for null in where clauses

    Check for null in where clauses

    If the where clause of a query contains a null value, or is a set containing a null value, then we replace:

    • WHERE column = $1WHERE (column IS NULL OR column = $1)
    • WHERE column = ANY($1)WHERE (column IS NULL OR column = ANY($1))

    Especially in the former case this is a little roundabout since the column = $1 clause can never match. But this does have the advantage that we don't need to renumber all the remaining fields.

    Fixes #22

    opened by danvk 0
  • Issue with queries for null

    Issue with queries for null

    If you generate a select query with a nullable column in the where clause, then querying for null won't work:

    https://stackoverflow.com/questions/3777230/is-there-any-difference-between-is-null-and-null

    This is surprising. Perhaps crudely-typed should switch the condition to IS NULL in that case. But what about if you're matching against a set? This is a little messy.

    opened by danvk 0
  • Un-vendor eslint-plugin-expect-type

    Un-vendor eslint-plugin-expect-type

    Once https://github.com/JoshuaKGoldberg/eslint-plugin-expect-type/pull/47 makes it in and is released, I can depend on the npm version of eslint-plugin-expect-type again.

    opened by danvk 0
  • Type tests

    Type tests

    Closes #5

    Using https://github.com/JoshuaKGoldberg/eslint-plugin-expect-type. I have a PR out over there (https://github.com/JoshuaKGoldberg/eslint-plugin-expect-type/pull/47) but in the meantime I can depend on a bundled version here.

    This works pretty nicely! And now I can confidently refactor the generic types.

    opened by danvk 0
  • Run type tests

    Run type tests

    There's select.typetest.ts but the types there aren't checked in any way, so they'll almost certainly drift.

    See https://github.com/JoshuaKGoldberg/eslint-plugin-expect-type/issues/43 for my preferred way to do this and this discussion on Twitter: https://twitter.com/danvdk/status/1505209336414547968

    Also need to write type tests for:

    • [ ] update
    • [ ] insert
    • [ ] delete
    opened by danvk 0
  • Allow control of

    Allow control of "returning" clause

    The insert / update / delete queries all have RETURNING * hard-coded. This gets you all the data you could possibly need, but sometimes it's more than you want. It would be nice to allow some control of this, e.g. to only return particular columns, or perhaps just a count of affected rows.

    opened by danvk 0
  • Support compound primary keys

    Support compound primary keys

    It's possible for a postgres primary key to include multiple columns. I believe pg-to-ts already supports this. crudely-typed should support it with the "ByPrimaryKey" methods, e.g. selectByPrimaryKey.

    opened by danvk 0
  • Set up npm auto-publish

    Set up npm auto-publish

    ~See https://docs.github.com/en/packages/working-with-a-github-packages-registry/working-with-the-npm-registry~ <-- That's not what I thought it was.

    But auto-publishing still seems like a good idea!

    opened by danvk 0
  • Write docs

    Write docs

    Write docs on:

    • [ ] Goals of the project
    • [ ] When you should use it vs. PgTyped or something else
    • [x] How to set it up in a project w/ pg-to-ts
    • [ ] API:
      • [x] Select
      • [x] Insert
      • [ ] Update
      • [x] Delete
      • [ ] any clauses
    • [ ] Development workflow

    Also:

    • [ ] Run TypeDoc to generate API documentation
    opened by danvk 0
  • Support pg-promise

    Support pg-promise

    The Queryable interface assumes you're using node-postgres, i.e. the pg package. But it shouldn't be too hard to support pg-promise as well, or at least to include an example of a wrapper.

    From my testing, there were a few oddities with pg-promise, for example selects with ANY clauses and UUID types didn't work.

    For reference:

    • pg has ~3M downloads/week
    • pg-promise has ~300k downloads/week
    opened by danvk 0
  • Prevent omitting non-optional columns in insert / insertMultiple

    Prevent omitting non-optional columns in insert / insertMultiple

    pg-to-ts emits a requiredForInsert list and there's a TableInsert. crudely-typed shouldn't allow users to disallow a column that's required for insert. See also https://github.com/danvk/pg-to-ts/issues/18

    opened by danvk 0
Releases(v0.1.2)
Owner
Dan Vanderkam
Software Developer @sidewalklabs, Author of _Effective TypeScript_.
Dan Vanderkam
100% type-safe query builder for node-postgres :: Generated types, call any function, tree-shakable, implicit type casts, and more

⚠️ This library is currently in alpha. Contributors wanted! tusken Postgres client from a galaxy far, far away. your database is the source-of-truth f

alloc 54 Dec 29, 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
Variant types in Roblox TypeScript - Ported from Vanilla TypeScript

Variant (for Roblox) This is a roblox typescript variant (heh, pun) of Variant. See the Variant documentation on how to use Variant. A variant type is

Australis 2 Jun 3, 2022
Simple jQuery plugin that will allow users to zoom in your images, perfect for product images and galleries.

Image Zoom (jQuery) Plugin Simple jQuery plugin that will allow users to zoom in your images, perfect for product images and galleries that is less th

Mario Duarte 8 Aug 3, 2022
RepoProvas API, a system for sharing tests between students, built with Typescript, Node.js, Express, Prisma and Postgres.

Repoprovas Built With ?? Description RepoProvas API, a system for sharing tests between students, built with Typescript, Node.js, Express, Prisma and

Lucas Lima 3 Dec 13, 2022
🧩 TypeScript utility type in order to ensure to return only properties (not methods) containing values in primitive types such as number or boolean (not Value Objects)

?? TypeScript Primitives type TypeScript utility type in order to ensure to return only properties (not methods) containing values in primitive types

CodelyTV 82 Dec 7, 2022
🔒 The new way to do types in typescript.

Nominal The right way to do types in typescript. Installation npm install nominal-types yarn install nominal-types pnpm install nominal-types Usage

Modfy 251 Dec 24, 2022
A little utility type package that allows you to access the current TypeScript version from your types.

@phryneas/ts-version A little utility type package that allows you to access the current TypeScript version from your types. import { TSVersion } from

Lenz Weber-Tronic 11 Sep 4, 2022
🎊 A Collection of TypeScript types for the Kirby CMS

kirby-fest A collection of TypeScript types to work with Kirby, mainly in the context of the Kirby Query Language. Setup # pnpm pnpm add -D kirby-fest

Johann Schopplich 5 Nov 22, 2022
Opinionated collection of TypeScript definitions and utilities for Deno and Deno Deploy. With complete types for Deno/NPM/TS config files, constructed from official JSON schemas.

Schemas Note: You can also import any type from the default module, ./mod.ts deno.json import { type DenoJson } from "https://deno.land/x/[email protected]

deno911 2 Oct 12, 2022
TSServer plugin & Utilities for "expanding" TypeScript types

ts-expand-type This repo provides a TS Server plugin which amends quick info to include the fully "expanded" type. "Expanded" means that object inters

Max Stoumen 6 Nov 20, 2022
🗂 The perfect Front-End Checklist for modern websites and meticulous developers

Front-End Checklist The Front-End Checklist is an exhaustive list of all elements you need to have / to test before launching your website / HTML page

David Dias 63.6k Jan 7, 2023
Perfect interpolation for multiplayer cursors.

perfect-cursors Perfect interpolation for animated multiplayer cursors. Used in tldraw. ?? Love this library? Consider becoming a sponsor. Installatio

Steve Ruiz 452 Dec 15, 2022
Perfect SvelteKit dark mode in 2 lines of code. Support System preference and any other theme with no flashing

This library is a port of next-theme for SvelteKit. All credit goes to pacocoursey and all next-themes contributors While usable, this library is stil

null 42 Sep 30, 2022
cpace - nodemon for C/C++ files. Monitor for any changes in your [.c] and [.cpp] application and automatically restart it - perfect for development

cpace cpace is a tool that helps develop [.c] and [.cpp] based applications by automatically restarting them when file changes are detected. The packa

null 17 Dec 3, 2022
A remote nodejs Cache Server, for you to have your perfect MAP Cache Saved and useable remotely. Easy Server and Client Creations, fast, stores the Cache before stopping and restores it again!

remote-map-cache A remote nodejs Cache Server, for you to have your perfect MAP Cache Saved and useable remotely. Easy Server and Client Creations, fa

Tomato6966 8 Oct 31, 2022
A robust and light-weight inventory management application designed to help businesses maintain perfect control over every unit of stock.

Inventory Buddy Access inventory anytime on web, tablet or mobile. Inventory Buddy is a robust and light-weight inventory management application desig

Brynn Smith 7 Nov 5, 2022
iX is a design system for designers and developers, to consistently create the perfect digital experience for industrial software products.

Siemens Industrial Experience (iX) monorepo iX is an open source design system for designers and developers, to consistently create the perfect digita

Siemens 54 Dec 26, 2022
Boost is a Microsoft Excel Add-in to help developers import large excel workbooks into their database using SQL queries

Microsoft Excel Add-in for Developers About Us Boost is a Microsoft Excel Add-in to help developers import large excel workbooks into their database u

OSLabs Beta 30 Sep 30, 2022