jawj/mostly-ormless

Mostly ORMless: ergonomic Postgres from TypeScript

I have now turned this approach into a library: please see https://jawj.github.io/zapatos/ for details

I gave a very brief outline of this on HN and a few people sounded interested, so here goes. It's extracted from my work as co-founder/CTO at PSYT.

TypeScript and VS Code are wonderful. ORMs are problematic.

Using an ORM within TypeScript can be particularly aggravating, since you can find yourself specifying lots of type information twice over (once to define the database schema, and once to define the associated TypeScript types).

That's why, in the course of converting a project to TypeScript recently, I decided to switch it from Sequelize to TypeORM. The first day was wonderful: shiny TypeScript toys! The rest of the week was less so: perhaps even more than other ORMs, TypeORM just doesn't seem to get SQL.

So, let's back up a bit. What am I really after?

Well, I like and understand Postgres. I like and understand SQL. When I'm working with data, I'd like a good mental model of what SQL is going to be issued. I don't mind writing the SQL myself, but in that case let me write the SQL, rather than deal with the endlessly chained method calls of a 'query builder'.

Also, I'm greedy. I want all that, but I want to keep all the type-checking goodness of TypeScript — speeding development, preventing bugs, simplifying refactoring — too. And I'd strongly prefer not to spend hours laboriously adding and later maintaining lots of type information to facilitate it.

In short, I want cake. So I set out to see if I could rustle some up. As it turns out, this was about a week-long project in three acts:

Act 1: In which we translate a Postgres schema into TypeScript types. Spoiler: we'll use a project called schemats here.
Act 2: In which we significantly expand that type information and use it to improve the ergonomics of writing raw SQL. Spoiler: ES2015 tagged templates will play a starring role.
Act 3: In which we create a set of simple shortcut functions on top, which looks a little bit like an ORM but really isn't. Spoiler: Typescript function signature overloads on concrete string values will be these functions' secret sauce.

(There is a also a brief encore in which I discuss transactions).

Act 1: In which we translate a Postgres schema into TypeScript types

If you want type information that matches a database schema, you have two ways to maintain a single source of truth.

On the one hand, you can define a schema in TypeScript (or JavaScript), then automagically issue database commands to sync up the tables and columns. That's the approach taken by both Sequelize and TypeScript. This approach helps manage schema updates across multiple databases (e.g. test, staging, production). But I've always been a bit leery of it: I'm not sure I want any code, let alone a third party npm library, issuing CREATE/ALTER/DROP TABLE statements at will where production data may be involved.

So, on the other hand, you can take the database schema as your source of truth, then interrogate it to discover the tables and column types. This is what Active Record does (or at least, what it did when I last used it, about 10 years ago), and what I'm going to do here. Managing schema updates across multiple databases is therefore something I'm not going to address (but check out tools such as dbmate).

Active Record interrogates the database at runtime, but I'm going to do it prior to runtime: discovering types only at runtime would be largely pointless, since what we want is type information to ease development.

As it happens, there's already a library that can do most of the heavy lifting we need, called schemats.

How does it work? Well, take this simple database schema:

CREATE TABLE authors 
( "id" SERIAL PRIMARY KEY
, "name" TEXT NOT NULL
, "isLiving" BOOLEAN
);

CREATE TABLE books 
( "id" SERIAL PRIMARY KEY
, "authorId" INTEGER NOT NULL REFERENCES "authors"("id")
, "title" TEXT
, "createdAt" TIMESTAMPTZ NOT NULL DEFAULT now()
, "updatedAt" TIMESTAMPTZ NOT NULL DEFAULT now()
);

If we run vanilla schemats against this, we get the following TypeScript definitions for the books table:

export namespace booksFields {
    export type id = number;
    export type authorId = number;
    export type title = string | null;
    export type createdAt = Date;
    export type updatedAt = Date;
}

export interface books {
    id: booksFields.id;
    authorId: booksFields.authorId;
    title: booksFields.title;
    createdAt: booksFields.createdAt;
    updatedAt: booksFields.updatedAt;
}

This is a helpful description of what you might get back from a SELECT query, and we can use it as follows

First, some housekeeping — we set up a connection pool:

import * as pg from "pg";
const pool = new pg.Pool({ /* connection options */ });

Then:

const
  authorId = 123,  // in production this could be untrusted input
  result = await pool.query({
    text: 'SELECT * FROM "books" WHERE "authorId" = $1',
    values: [authorId],
  }),
  existingBooks: books[] = result.rows;

Our results now pop out fully typed. But there are still some real annoyances here:

There's nothing to stop me absent-mindedly or fat-fingeredly trying to query from the non-existent tables "novels" or "boks", or according to the non-existent columns "writerId" or "authorid", or indeed trying to equate my "authorId" with a string or date. There's no auto-complete to help me out, and any errors (whether introduced now or by a future schema update) will only become apparent later on, at runtime.
I have to keep track of the order of interpolated parameters, and provide them separately from the query text. That's only authorId in this instance, but there could be dozens.
Things are even worse for INSERTs and UPDATEs, where I need to generate separate, matching-ordered lists of column names and their corresponding values.

So, here's the plan. I'm going to come up with some ES2015 tagged templates to improve these SQL-writing ergonomics in TypeScript. But first, to support that, I'm going to need some more complete type information about my tables.

Act 2: In which we expand that type information and use it to improve the ergonomics of writing raw SQL

I said I wanted more complete type information about my tables. Specifically, I want the types I generate to specify not just what I'll get back from a SELECT query, but also what I'm allowed to use in a WHERE condition, and what I can INSERT and UPDATE too.

So these are the four main interfaces I'm going to define:

Selectable: what I'll get back from a SELECT query. This is what schemats was already giving us.

Whereable: what I can use in a WHERE condition. This is approximately the same as Selectable, but all columns are optional. It is (subject to some later tweaks) a Partial<Selectable>.

Insertable: what I can INSERT into a table. This is also similar to Selectable, but any fields that are NULLable and/or have DEFAULT values are allowed to be missing, NULL or DEFAULT.

Updatable: what I can UPDATE a row with. This is similar to what I can INSERT, but all columns are optional: it is a simple Partial<Insertable>.

I forked schemats to generate these interfaces. While I was at it I also got rid of the verbose, original, two-stage approach, where a type alias is created for every column.

Ignoring a few additional bells and whistles, my schemats fork therefore now generates something like the following when run against the books table:

export namespace books { export interface Selectable { id: number; authorId: number; title: string | null; createdAt: Date; updatedAt: Date; } export type Whereable = Partial<Selectable>; export interface Insertable { id?: number | DefaultType; authorId: number; title?: string | null | DefaultType; createdAt?: Date | DefaultType; updatedAt?: Date | DefaultType; } export type Updatable = Partial<Insertable>; } export const Default = Symbol('DEFAULT'); export type DefaultType = typeof Default;

If I'm querying that table, I now type the returned rows as books.Selectable[] instead of just books[]:

const existingBooks: books.Selectable[] = /* database query goes here */;

And if I have an object with data that I plan to insert into the books table, or that I plan to use to look up a particular book, I can now also have that type-checked and auto-completed in VS Code, like so:

const newBook: books.Insertable = { authorId: 123, title: "One Hundred Years of Solitude", }; const bookConditions: books.Whereable = { authorId: 456, };

But how am I going to use these extra types? Enter the tagged templates I mentioned earlier.

Tagged templates are pleasingly flexible. Critically, neither a template's interpolated values nor its return value have to be strings. And TypeScript supports tagged templates fully, applying the tag function's signature to the interpolated values.

This is useful because:

I'm going to define a typed tagged template function, sql, which we'll use to write queries.

It's going to be generic, so I can tell it which table(s) it targets: e.g. sql<books.SQL> `-- database query goes here`

It's not going to return a string (yet), but an instance of a class I'm going to call SQLFragment.

Different kinds of interpolated values will be processed in different ways, and they'll be type-checked and auto-completed appropriately.

An important kind of interpolated values is other SQLFragments.

When it comes to making the query, the root SQLFragment will be compiled recursively to produce the appropriate { text: '', values: [] } object that can be passed on to pg.

It's still going to be possible to write invalid SQL, of course, but a bunch of simple errors (including most of the errors that could be introduced by future refactorings) will be caught at compile time or earlier.

SELECT

How does this look in practice? Well, the simple query above ends up looking like this:

const authorId = 123, query = sql<books.SQL>`SELECT * FROM ${"books"} WHERE ${{ authorId }}`, existingBooks: books.Selectable[] = await query.run(pool);

What's happening here?

Well, first, the table name is now being interpolated, which means it will be type checked and auto-completed. That means: I can pick it from a list; if I get it wrong, I'll be told so immediately; and if I later change its name in the database but forget to change it here, I'll be told so immediately too.

So the first kind of interpolated value my sql template tag function supports is a plain string. This is type-checked/auto-completed to correspond to a valid table or column name. It will be double-quoted in the generated raw SQL, to protect any idiomatic JavaScript/TypeScript camelCased column names.

And, second, the object I've passed as my WHERE clause is being type-checked/auto-completed as the appropriate books.Whereable type. It will be compiled to ("authorId" = $1), and its value will be inserted at the appropriate index of the pg query values array.

So the second kind of interpolated value I can use in a sql template is a plain object. This is type-checked/auto-completed as an appropriate Whereable, and compiled to a set of WHERE conditions connected with ANDs.

These Whereable objects in fact have more flexibility than I let on before, since they also accept SQLFragment values. For example, what if I want to see only books for a particular author that were added during the last N days?

Then I can write the following:

const authorId = 123, days = 7, query = sql<books.SQL>` SELECT * FROM ${"books"} WHERE ${{ authorId, createdAt: sql<books.SQL>` ${self} > now() - ${param(period)} * INTERVAL '1 DAY'`, }}`;

And to confirm how this is compiled:

> console.log(query.compile()); { text: 'SELECT * FROM "books" WHERE ("authorId" = $1 AND ("createdAt" > now() - $2 * INTERVAL \'1 DAY\'))', values: [ 123, 7 ] }

You can see the SQLFragment nested inside the Whereable. You can also see a symbol I've called self, which is compiled to its parent column name. Finally, I'm using my param wrapper function, which lets me manually insert parameters to be represented as $1, $2 (etc.) and added to the pg query's values array.

INSERT and UPDATE

There are some additional supported interpolation types that can help with INSERT and UPDATE queries. For example, what if I wanted to save the Insertable object I showed you before?

I can write:

const newBook: books.Insertable = { authorId: 123, title: "One Hundred Years of Solitude", }, query = sql<books.SQL>` INSERT INTO ${"books"} (${cols(newBook)}) VALUES (${vals(newBook)})`;

And this gives the expected:

> console.log(query.compile()); { text: 'INSERT INTO "books" ("authorId", "title") VALUES ($1, $2)', values: [ 123, 'One Hundred Years of Solitude' ] }

The cols and vals wrapper functions ultimately produce identically-ordered sequences of the object's keys (quoted) and values (safely passed via pg's values array) respectively.

And, of course, this is all being type-checked and auto-completed as I type it:

And if, for example, someone renames the title column to name in future, TypeScript will complain before I even try to run the program.

For further flexibility, there are three further kinds of interpolated value that are supported in a sql template. They are:

The Default symbol I mentioned earlier, compiling to the DEFAULT keyword.

raw() — as in raw(name), where name is "Robert'); -- DROP TABLE Students; --" (XKCD) — for times when you really know what you're doing.

Arrays containing any of the other supported interpolation types, whose compiled results are simply concatenated together.

Some of this relies on a few further types that I tweaked schemats to generate too:

export namespace books { /* ... Selectable, Whereable, Insertable, Updatable, mostly as before, but all except Selectable now also allow SQLFragment values ... */ export type Table = "books"; export type Column = keyof Selectable; export type SQLExpression = GenericSQLExpression | Table | Whereable | Column | ColumnNames<Updatable> | ColumnValues<Updatable>; // all building up to the books.SQL type we saw above: export type SQL = SQLExpression | SQLExpression[]; } // these are mostly the types of symbols or wrapper classes (not shown) export type GenericSQLExpression = SQLFragment | Parameter | DefaultType | DangerousRawString | SelfType;

JOINs

We can of course use all of these types to help us in more complicated queries too. Take this JOIN, for example, retrieving each book with its (single) author. We can use Postgres's excellent JSON support to produce a sensibly-structured return value and avoid column name clashes:

type bookAuthorSQL = books.SQL | authors.SQL | "author"; type bookAuthorSelectable = books.Selectable & { author: authors.Selectable }; const query = sql<bookAuthorSQL>` SELECT ${"books"}.*, to_jsonb(${"authors"}.*) as ${"author"} FROM ${"books"} JOIN ${"authors"} ON ${"books"}.${"authorId"} = ${"authors"}.${"id"}`; const bookAuthors: bookAuthorSelectable[] = await query.run(pool);

Producing:

> console.log(bookAuthors[0]); { "id": 14, "authorId": 123, "title": "One Hundred Years of Solitude", "createdAt": "2019-06-11T14:52:30.291Z", "updatedAt": "2019-06-11T14:52:30.291Z", "author": { "id": 123, "name": "Gabriel Garcia Marquez", "isLiving": false } }

In which everything is appropriately typed:

Of course, we might also want the converse query, retrieving each author with their (many) books. This is also easy enough to arrange:

type authorBooksSQL = authors.SQL | books.SQL; type authorBooksSelectable = authors.Selectable & { books: books.Selectable[] }; const query = sql<authorBooksSQL>` SELECT ${"authors"}.*, jsonb_agg(${"books"}.*) AS ${"books"} FROM ${"authors"} JOIN ${"books"} ON ${"authors"}.${"id"} = ${"books"}.${"authorId"} GROUP BY ${"authors"}.${"id"}`, authorBooks: authorBooksSelectable[] = await query.run(pool);

Which gives:

> console.dir(authorBooks[0]); [ { id: 123, name: 'Gabriel Garcia Marquez', isLiving: false, books: [ { id: 139, title: 'Love in the Time of Cholera', authorId: 123, createdAt: '2019-09-22T19:49:32.373132+01:00', updatedAt: '2019-09-22T19:49:32.373132+01:00' }, { id: 140, title: 'One Hundred Years of Solitude', authorId: 123, createdAt: '2019-09-22T19:49:32.380854+01:00', updatedAt: '2019-09-22T19:49:32.380854+01:00' } ] } ]

(It also demonstrates a useful SQL fact: selecting all fields in a GROUP BY query is permitted as long as you're grouping by primary key).

Note that if you want to include authors with no books, you need a LEFT JOIN in this query, and then you'll want to fix the annoying [null] array results jsonb_agg will return for those authors.

One way to do that is to define a simple function to replace [null] with the empty array []:

CREATE OR REPLACE FUNCTION empty_nulls(jsonb) RETURNS jsonb AS $$ SELECT CASE $1 WHEN '[null]'::jsonb THEN '[]'::jsonb ELSE $1 END $$ LANGUAGE SQL IMMUTABLE;

And wrap it around the jsonb_agg call:

const query = sql<authorBooksSQL>` SELECT ${"authors"}.*, empty_nulls(jsonb_agg(${"books"}.*)) AS ${"books"} FROM ${"authors"} LEFT JOIN ${"books"} ON ${"authors"}.${"id"} = ${"books"}.${"authorId"} GROUP BY ${"authors"}.${"id"}`;

Alternatively, we can achieve the same result using a LATERAL join instead:

const query = db.sql<authorBooksSQL>` SELECT ${"authors"}.*, bq.* FROM ${"authors"} CROSS JOIN LATERAL ( SELECT coalesce(json_agg(${"books"}.*), '[]') AS ${"books"} FROM ${"books"} WHERE ${"books"}.${"authorId"} = ${"authors"}.${"id"} ) bq`;

This approach is straightforward to extend to more complex, nested cases too. Say, for example, that we let each book have multiple tags, with the following addition to the schema:

CREATE TABLE "tags" ( "tag" TEXT NOT NULL , "bookId" INTEGER NOT NULL REFERENCES "books"("id") ); CREATE UNIQUE INDEX "tagsUniqueIdx" ON "tags"("bookId", "tag"); CREATE INDEX "tagsBookIdIdx" ON "tags"("tag");

Then we could retrieve authors, each with their books, each with its tag values, using the following screenful:

type authorBookTagsSQL = authors.SQL | books.SQL | tags.SQL; type authorBookTagsSelectable = authors.Selectable & { books: (books.Selectable & { tags: tags.Selectable['tag'] })[] }; const query = sql<authorBookTagsSQL>` SELECT ${"authors"}.*, bq.* FROM ${"authors"} CROSS JOIN LATERAL ( SELECT coalesce(jsonb_agg(to_jsonb(${"books"}.*) || to_jsonb(tq.*)), '[]') AS ${"books"} FROM ${"books"} CROSS JOIN LATERAL ( SELECT coalesce(jsonb_agg(${"tags"}.${"tag"}), '[]') AS ${"tags"} FROM ${"tags"} WHERE ${"tags"}.${"bookId"} = ${"books"}.${"id"} ) tq WHERE ${"books"}.${"authorId"} = ${"authors"}.${"id"} ) bq`; const authorBookTags: authorBookTagsSelectable[] = await query.run(pool); console.dir(authorBookTags, { depth: null });

Which gives, correctly typed:

[ { id: 1, name: 'Jane Austen', isLiving: false, books: [ { id: 284, tags: [], title: 'Pride and Prejudice', authorId: 1, createdAt: '2019-11-04T17:06:11.209512+00:00', updatedAt: '2019-11-04T17:06:11.209512+00:00' } ] }, { id: 123, name: 'Gabriel Garcia Marquez', isLiving: false, books: [ { id: 285, tags: [ 'Spanish', '1980s' ], title: 'Love in the Time of Cholera', authorId: 123, createdAt: '2019-11-04T17:06:11.209512+00:00', updatedAt: '2019-11-04T17:06:11.209512+00:00' }, { id: 286, tags: [], title: 'One Hundred Years of Solitude', authorId: 123, createdAt: '2019-11-04T17:06:11.228866+00:00', updatedAt: '2019-11-04T17:06:11.228866+00:00' } ] }, { id: 456, name: 'Douglas Adams', isLiving: false, books: [] } ]

Field subsets

Unless you have very wide tables and/or very large values, it could be a premature optimization to query only for a subset of fields.

But if you need it, two conveniences are provided: (1) the cols function can take an array of column names, and simply spit them out quoted and comma-separated; and (2) there's a table-specific type helper OnlyCols that will narrow Selectable down to the columns included in such an array.

For example:

const bookCols = <const>['id', 'title']; // <const> prevents generalization to string[] type BookDatum = books.OnlyCols<typeof bookCols>; const query = sql<books.SQL>`SELECT ${cols(bookCols)} FROM ${"books"}`, bookData: BookDatum[] = await query.run(pool);

Giving:

> console.log(bookData); [ { id: 153, title: 'Pride and Prejudice' }, { id: 154, title: 'Love in the Time of Cholera' }, { id: 155, title: 'One Hundred Years of Solitude' } ]

And that marks the end of Act 2. SQL queries are now being auto-completed and type-checked for me, which is excellent. But a lot of the simple stuff still feels a bit boiler-platey and verbose.

Act 3: In which we create a set of simple shortcut functions on top, which looks a little bit like an ORM but really isn't

In the final Act, I aim to make the simple one-table CRUD stuff fall-off-a-log easy with some straightforward helper functions. For these helper functions I generate a set of overloaded signatures per table, which means the return type and all other argument types can be inferred automatically from the table name argument.

I haven't yet done anything to help with JOINs, but that may be worth considering for the future.

SELECT
First, SELECTs. I define a select function, and my schemats fork generates appropriate signatures for it for every table in the database. Using that function turns this query: const query = sql<books.SQL>`SELECT * FROM ${"books"} WHERE ${{ authorId }}`, existingBooks: books.Selectable[] = await query.run(pool); Into this: const existingBooks = await select(pool, "books", { authorId }); What's really nice here is that, thanks to the schemats-generated function signatures, once I've typed in "books" as the second argument to the function, TypeScript and VS Code know how to type-check and auto-complete both the third argument (which, if present, must be a books.Whereable) and the return value (which must be a Promise<books.Selectable[]>). The generated function signatures that make this happen look approximately like so: interface SelectSignatures { (client: Queryable, table: authors.Table, where?: authors.Whereable, options?: authors.SelectOptions): Promise<authors.Selectable[]>; (client: Queryable, table: books.Table, where?: books.Whereable, options?: books.SelectOptions): Promise<books.Selectable[]>; } The options keys include columns, which lets me limit the columns to be returned, such as: const allBookTitles = await db.select(db.pool, "books", undefined, { columns: ['title'] }); The return type is then appropriately narrowed to those columns: The options keys also include order, limit and offset, so I can do this kind of thing: const [lastButOneBook] = await select(pool, "books", { authorId }, { order: [{ by: "createdAt", direction: "DESC" }], limit: 1, offset: 1 }); The order by option is being type-checked and auto-completed, of course. I used destructuring assignment here ([lastButOneBook] = /* ... */) to account for the fact that I know this query is only going to return one response. Unfortunately, destructuring is just syntactic sugar for indexing, and indexing in TypeScript doesn't reflect that the result may be undefined. That means that lastButOneBook is now typed as a books.Selectable, but it could actually be undefined, and that could lead to errors down the line. To work around this, I also define a selectOne helper function. This turns the query above into the following: const lastButOneBook = await selectOne(pool, "books", { authorId }, { order: [{ by: "createdAt", direction: 'DESC' }], offset: 1 }); The limit option is applied automatically in this case, and the return type following await is now, correctly, books.Selectable | undefined. "Oh, great", I hear you say, unenthusiastically. "We've come all this way, and you've finished up reinventing the ORMs and query builders you promised we were going to escape". Well, I don't think so. This is not an ORM. There's no weird magic going on behind the scenes — not even the merest hint that any semantics are being obscured. There's just a mechanical transformation of some function parameters into a single SQL query, which makes my life easier. One call to select means one unsurprising SELECT query is issued, and happily both the inputs and outputs of that query are now fully type-checked. It's also not a query builder. There's no interminable method chaining. There's just type-checking and some additional conveniences applied to writing raw SQL queries, and some shortcut functions that can create basic SQL queries on your behalf. With that out of the way, onwards and upwards: INSERTs, UPDATEs, and (because, hey, now we can do anything Postgres can do) UPSERTs! INSERT The insert helper has the same sort of table-specific signatures as select, and it turns the INSERT query we saw earlier into this: const savedBook = await insert(pool, "books", { authorId: 123, title: "One Hundred Years of Solitude", }); This produces the same query we wrote previously, but now with the addition of a RETURNING * clause at the end, meaning that we get back a books.Selectable that includes values for all the columns with defaults, such as the id serial. In addition, we can insert multiple rows in one query: the function is written (and has appropriate type signatures) such that, if I instead give it an array-valued books.Insertable[], it gives me an array-valued books.Selectable[] back: const savedBooks = await insert(pool, "books", [{ authorId: 123, title: "One Hundred Years of Solitude", }, { authorId: 456, title: "Cheerio, and Thanks for All the Fish", }]); UPDATE Updates are likewise straightforward, fully typed, and also return the affected rows as a Selectable[]: const fishBookId = savedBooks[1].id, properTitle = "So Long, and Thanks for All the Fish", [updatedBook] = await update(pool, "books", { title: properTitle }, { id: fishBookId } ); We can again use a SQLFragment in an update for further flexibility — for example, if we needed to atomically increment a count. In a different context — for a schema that's not shown — I do something like the following: await update(pool, "emailAuthentication", { consecutiveFailedLogins: sql`${self} + 1`, lastFailedLogin: sql`now()`, }, { email } ); 'UPSERT' This being the thinnest possible wrapper around Postgres, we can of course do native 'UPSERT's too: I'll illustrate this one with a simplified example culled from our actual codebase, which deals with In-App Purchase updates for our iOS apps. CREATE TABLE "appleTransactions" ( "environment" "appleEnvironment" NOT NULL -- ENUM of 'PROD' or 'Sandbox' , "originalTransactionId" TEXT NOT NULL , "accountId" INTEGER REFERENCES "accounts"("id") NOT NULL , "latestReceiptData" TEXT -- ... lots more fields ... ); ALTER TABLE "appleTransactions" ADD CONSTRAINT "appleTransPKey" PRIMARY KEY ("environment", "originalTransactionId"); When we receive a purchase receipt, either via our iOS app or direct from Apple's servers, we need to either store a new record or update an existing record for each 'original transaction ID' it contains. We therefore map the transaction data in the receipt into an appleTransactions.Insertable[], and do what's needed with a single upsert call. Hard-coding the Insertable[] for ease of exposition: const newTransactions: s.appleTransactions.Insertable[] = [{ environment: 'PROD', originalTransactionId: '123456', accountId: 123, latestReceiptData: "TWFuIGlzIGRpc3Rp", }, { environment: 'PROD', originalTransactionId: '234567', accountId: 234, latestReceiptData: "bmd1aXNoZWQsIG5v", }], result = await upsert(pool, "appleTransactions", newTransactions, ["environment", "originalTransactionId"]); The last argument to upsert is the key or array of keys on which there could be a conflict. In this case, the following query is issued: { text: `INSERT INTO "appleTransactions" ("accountId", "environment", "latestReceiptData", "originalTransactionId") VALUES ($1, $2, $3, $4), ($5, $6, $7, $8) ON CONFLICT ("environment", "originalTransactionId") DO UPDATE SET ("accountId", "latestReceiptData") = ROW(EXCLUDED."accountId", EXCLUDED."latestReceiptData") RETURNING *, CASE xmax WHEN 0 THEN 'INSERT' ELSE 'UPDATE' END AS "$action"`, values: [ 123, 'PROD', 'TWFuIGlzIGRpc3Rp', '123456', 234, 'PROD', 'bmd1aXNoZWQsIG5v', '234567' ] } The (awaited) return value is an appleTransactions.UpsertReturnable[]. An UpsertReturnable is just like a Selectable but with one extra property, $action: "INSERT" | "UPDATE", valued according to what in fact happened. This is modelled on a similar approach in MS SQL Server. The result of the above query might therefore be: > console.log(result); [{ "environment": "PROD", "originalTransactionId": "123456", "accountId": 123, "latestReceiptData": "TWFuIGlzIGRpc3Rp", "$action": "UPDATE" }, { "environment": "PROD", "originalTransactionId": "234567", "accountId": 234, "latestReceiptData": "bmd1aXNoZWQsIG5v", "$action": "INSERT" }] Encore: Transactions The 'transactions' we just saw were data about people giving us money, but — for added confusion — let's talk for a moment about database transactions (as in: BEGIN TRANSACTION and COMMIT TRANSACTION). Transactions a

`Mostly ORMless: ergonomic Postgres from TypeScript`

I have now turned this approach into a library: please see https://jawj.github.io/zapatos/ for details I gave a very brief outline of this on HN and a few people sounded interested, so here goes. It's extracted from my work as co-founder/CTO at PSYT. TypeScript and VS Code are wonderful. ORMs are problematic. Using an ORM within TypeScript can be particularly aggravating, since you can find yourself specifying lots of type information twice over (once to define the database schema, and once to define the associated TypeScript types). That's why, in the course of converting a project to TypeScript recently, I decided to switch it from Sequelize to TypeORM. The first day was wonderful: shiny TypeScript toys! The rest of the week was less so: perhaps even more than other ORMs, TypeORM just doesn't seem to get SQL. So, let's back up a bit. What am I really after? Well, I like and understand Postgres. I like and understand SQL. When I'm working with data, I'd like a good mental model of what SQL is going to be issued. I don't mind writing the SQL myself, but in that case let me write the SQL, rather than deal with the endlessly chained method calls of a 'query builder'. Also, I'm greedy. I want all that, but I want to keep all the type-checking goodness of TypeScript — speeding development, preventing bugs, simplifying refactoring — too. And I'd strongly prefer not to spend hours laboriously adding and later maintaining lots of type information to facilitate it. In short, I want cake. So I set out to see if I could rustle some up. As it turns out, this was about a week-long project in three acts: Act 1: In which we translate a Postgres schema into TypeScript types. Spoiler: we'll use a project called schemats here. Act 2: In which we significantly expand that type information and use it to improve the ergonomics of writing raw SQL. Spoiler: ES2015 tagged templates will play a starring role. Act 3: In which we create a set of simple shortcut functions on top, which looks a little bit like an ORM but really isn't. Spoiler: Typescript function signature overloads on concrete string values will be these functions' secret sauce. (There is a also a brief encore in which I discuss transactions). Act 1: In which we translate a Postgres schema into TypeScript types If you want type information that matches a database schema, you have two ways to maintain a single source of truth. On the one hand, you can define a schema in TypeScript (or JavaScript), then automagically issue database commands to sync up the tables and columns. That's the approach taken by both Sequelize and TypeScript. This approach helps manage schema updates across multiple databases (e.g. test, staging, production). But I've always been a bit leery of it: I'm not sure I want any code, let alone a third party npm library, issuing CREATE/ALTER/DROP TABLE statements at will where production data may be involved. So, on the other hand, you can take the database schema as your source of truth, then interrogate it to discover the tables and column types. This is what Active Record does (or at least, what it did when I last used it, about 10 years ago), and what I'm going to do here. Managing schema updates across multiple databases is therefore something I'm not going to address (but check out tools such as dbmate). Active Record interrogates the database at runtime, but I'm going to do it prior to runtime: discovering types only at runtime would be largely pointless, since what we want is type information to ease development. As it happens, there's already a library that can do most of the heavy lifting we need, called schemats. How does it work? Well, take this simple database schema: CREATE TABLE authors ( "id" SERIAL PRIMARY KEY , "name" TEXT NOT NULL , "isLiving" BOOLEAN ); CREATE TABLE books ( "id" SERIAL PRIMARY KEY , "authorId" INTEGER NOT NULL REFERENCES "authors"("id") , "title" TEXT , "createdAt" TIMESTAMPTZ NOT NULL DEFAULT now() , "updatedAt" TIMESTAMPTZ NOT NULL DEFAULT now() ); If we run vanilla schemats against this, we get the following TypeScript definitions for the books table: export namespace booksFields { export type id = number; export type authorId = number; export type title = string | null; export type createdAt = Date; export type updatedAt = Date; } export interface books { id: booksFields.id; authorId: booksFields.authorId; title: booksFields.title; createdAt: booksFields.createdAt; updatedAt: booksFields.updatedAt; } This is a helpful description of what you might get back from a SELECT query, and we can use it as follows First, some housekeeping — we set up a connection pool: import * as pg from "pg"; const pool = new pg.Pool({ /* connection options */ }); Then: const authorId = 123, // in production this could be untrusted input result = await pool.query({ text: 'SELECT * FROM "books" WHERE "authorId" = $1', values: [authorId], }), existingBooks: books[] = result.rows; Our results now pop out fully typed. But there are still some real annoyances here: There's nothing to stop me absent-mindedly or fat-fingeredly trying to query from the non-existent tables "novels" or "boks", or according to the non-existent columns "writerId" or "authorid", or indeed trying to equate my "authorId" with a string or date. There's no auto-complete to help me out, and any errors (whether introduced now or by a future schema update) will only become apparent later on, at runtime. I have to keep track of the order of interpolated parameters, and provide them separately from the query text. That's only authorId in this instance, but there could be dozens. Things are even worse for INSERTs and UPDATEs, where I need to generate separate, matching-ordered lists of column names and their corresponding values. So, here's the plan. I'm going to come up with some ES2015 tagged templates to improve these SQL-writing ergonomics in TypeScript. But first, to support that, I'm going to need some more complete type information about my tables. Act 2: In which we expand that type information and use it to improve the ergonomics of writing raw SQL I said I wanted more complete type information about my tables. Specifically, I want the types I generate to specify not just what I'll get back from a SELECT query, but also what I'm allowed to use in a WHERE condition, and what I can INSERT and UPDATE too. So these are the four main interfaces I'm going to define: Selectable: what I'll get back from a SELECT query. This is what schemats was already giving us. Whereable: what I can use in a WHERE condition. This is approximately the same as Selectable, but all columns are optional. It is (subject to some later tweaks) a Partial<Selectable>. Insertable: what I can INSERT into a table. This is also similar to Selectable, but any fields that are NULLable and/or have DEFAULT values are allowed to be missing, NULL or DEFAULT. Updatable: what I can UPDATE a row with. This is similar to what I can INSERT, but all columns are optional: it is a simple Partial<Insertable>. I forked schemats to generate these interfaces. While I was at it I also got rid of the verbose, original, two-stage approach, where a type alias is created for every column. Ignoring a few additional bells and whistles, my schemats fork therefore now generates something like the following when run against the books table: export namespace books { export interface Selectable { id: number; authorId: number; title: string | null; createdAt: Date; updatedAt: Date; } export type Whereable = Partial<Selectable>; export interface Insertable { id?: number | DefaultType; authorId: number; title?: string | null | DefaultType; createdAt?: Date | DefaultType; updatedAt?: Date | DefaultType; } export type Updatable = Partial<Insertable>; } export const Default = Symbol('DEFAULT'); export type DefaultType = typeof Default; If I'm querying that table, I now type the returned rows as books.Selectable[] instead of just books[]: const existingBooks: books.Selectable[] = /* database query goes here */; And if I have an object with data that I plan to insert into the books table, or that I plan to use to look up a particular book, I can now also have that type-checked and auto-completed in VS Code, like so: const newBook: books.Insertable = { authorId: 123, title: "One Hundred Years of Solitude", }; const bookConditions: books.Whereable = { authorId: 456, }; But how am I going to use these extra types? Enter the tagged templates I mentioned earlier. Tagged templates are pleasingly flexible. Critically, neither a template's interpolated values nor its return value have to be strings. And TypeScript supports tagged templates fully, applying the tag function's signature to the interpolated values. This is useful because: I'm going to define a typed tagged template function, sql, which we'll use to write queries. It's going to be generic, so I can tell it which table(s) it targets: e.g. sql<books.SQL> `-- database query goes here` It's not going to return a string (yet), but an instance of a class I'm going to call SQLFragment. Different kinds of interpolated values will be processed in different ways, and they'll be type-checked and auto-completed appropriately. An important kind of interpolated values is other SQLFragments. When it comes to making the query, the root SQLFragment will be compiled recursively to produce the appropriate { text: '', values: [] } object that can be passed on to pg. It's still going to be possible to write invalid SQL, of course, but a bunch of simple errors (including most of the errors that could be introduced by future refactorings) will be caught at compile time or earlier. SELECT How does this look in practice? Well, the simple query above ends up looking like this: const authorId = 123, query = sql<books.SQL>`SELECT * FROM ${"books"} WHERE ${{ authorId }}`, existingBooks: books.Selectable[] = await query.run(pool); What's happening here? Well, first, the table name is now being interpolated, which means it will be type checked and auto-completed. That means: I can pick it from a list; if I get it wrong, I'll be told so immediately; and if I later change its name in the database but forget to change it here, I'll be told so immediately too. So the first kind of interpolated value my sql template tag function supports is a plain string. This is type-checked/auto-completed to correspond to a valid table or column name. It will be double-quoted in the generated raw SQL, to protect any idiomatic JavaScript/TypeScript camelCased column names. And, second, the object I've passed as my WHERE clause is being type-checked/auto-completed as the appropriate books.Whereable type. It will be compiled to ("authorId" = $1), and its value will be inserted at the appropriate index of the pg query values array. So the second kind of interpolated value I can use in a sql template is a plain object. This is type-checked/auto-completed as an appropriate Whereable, and compiled to a set of WHERE conditions connected with ANDs. These Whereable objects in fact have more flexibility than I let on before, since they also accept SQLFragment values. For example, what if I want to see only books for a particular author that were added during the last N days? Then I can write the following: const authorId = 123, days = 7, query = sql<books.SQL>` SELECT * FROM ${"books"} WHERE ${{ authorId, createdAt: sql<books.SQL>` ${self} > now() - ${param(period)} * INTERVAL '1 DAY'`, }}`; And to confirm how this is compiled: > console.log(query.compile()); { text: 'SELECT * FROM "books" WHERE ("authorId" = $1 AND ("createdAt" > now() - $2 * INTERVAL \'1 DAY\'))', values: [ 123, 7 ] } You can see the SQLFragment nested inside the Whereable. You can also see a symbol I've called self, which is compiled to its parent column name. Finally, I'm using my param wrapper function, which lets me manually insert parameters to be represented as $1, $2 (etc.) and added to the pg query's values array. INSERT and UPDATE There are some additional supported interpolation types that can help with INSERT and UPDATE queries. For example, what if I wanted to save the Insertable object I showed you before? I can write: const newBook: books.Insertable = { authorId: 123, title: "One Hundred Years of Solitude", }, query = sql<books.SQL>` INSERT INTO ${"books"} (${cols(newBook)}) VALUES (${vals(newBook)})`; And this gives the expected: > console.log(query.compile()); { text: 'INSERT INTO "books" ("authorId", "title") VALUES ($1, $2)', values: [ 123, 'One Hundred Years of Solitude' ] } The cols and vals wrapper functions ultimately produce identically-ordered sequences of the object's keys (quoted) and values (safely passed via pg's values array) respectively. And, of course, this is all being type-checked and auto-completed as I type it: And if, for example, someone renames the title column to name in future, TypeScript will complain before I even try to run the program. For further flexibility, there are three further kinds of interpolated value that are supported in a sql template. They are: The Default symbol I mentioned earlier, compiling to the DEFAULT keyword. raw() — as in raw(name), where name is "Robert'); -- DROP TABLE Students; --" (XKCD) — for times when you really know what you're doing. Arrays containing any of the other supported interpolation types, whose compiled results are simply concatenated together. Some of this relies on a few further types that I tweaked schemats to generate too: export namespace books { /* ... Selectable, Whereable, Insertable, Updatable, mostly as before, but all except Selectable now also allow SQLFragment values ... */ export type Table = "books"; export type Column = keyof Selectable; export type SQLExpression = GenericSQLExpression | Table | Whereable | Column | ColumnNames<Updatable> | ColumnValues<Updatable>; // all building up to the books.SQL type we saw above: export type SQL = SQLExpression | SQLExpression[]; } // these are mostly the types of symbols or wrapper classes (not shown) export type GenericSQLExpression = SQLFragment | Parameter | DefaultType | DangerousRawString | SelfType; JOINs We can of course use all of these types to help us in more complicated queries too. Take this JOIN, for example, retrieving each book with its (single) author. We can use Postgres's excellent JSON support to produce a sensibly-structured return value and avoid column name clashes: type bookAuthorSQL = books.SQL | authors.SQL | "author"; type bookAuthorSelectable = books.Selectable & { author: authors.Selectable }; const query = sql<bookAuthorSQL>` SELECT ${"books"}.*, to_jsonb(${"authors"}.*) as ${"author"} FROM ${"books"} JOIN ${"authors"} ON ${"books"}.${"authorId"} = ${"authors"}.${"id"}`; const bookAuthors: bookAuthorSelectable[] = await query.run(pool); Producing: > console.log(bookAuthors[0]); { "id": 14, "authorId": 123, "title": "One Hundred Years of Solitude", "createdAt": "2019-06-11T14:52:30.291Z", "updatedAt": "2019-06-11T14:52:30.291Z", "author": { "id": 123, "name": "Gabriel Garcia Marquez", "isLiving": false } } In which everything is appropriately typed: Of course, we might also want the converse query, retrieving each author with their (many) books. This is also easy enough to arrange: type authorBooksSQL = authors.SQL | books.SQL; type authorBooksSelectable = authors.Selectable & { books: books.Selectable[] }; const query = sql<authorBooksSQL>` SELECT ${"authors"}.*, jsonb_agg(${"books"}.*) AS ${"books"} FROM ${"authors"} JOIN ${"books"} ON ${"authors"}.${"id"} = ${"books"}.${"authorId"} GROUP BY ${"authors"}.${"id"}`, authorBooks: authorBooksSelectable[] = await query.run(pool); Which gives: > console.dir(authorBooks[0]); [ { id: 123, name: 'Gabriel Garcia Marquez', isLiving: false, books: [ { id: 139, title: 'Love in the Time of Cholera', authorId: 123, createdAt: '2019-09-22T19:49:32.373132+01:00', updatedAt: '2019-09-22T19:49:32.373132+01:00' }, { id: 140, title: 'One Hundred Years of Solitude', authorId: 123, createdAt: '2019-09-22T19:49:32.380854+01:00', updatedAt: '2019-09-22T19:49:32.380854+01:00' } ] } ] (It also demonstrates a useful SQL fact: selecting all fields in a GROUP BY query is permitted as long as you're grouping by primary key). Note that if you want to include authors with no books, you need a LEFT JOIN in this query, and then you'll want to fix the annoying [null] array results jsonb_agg will return for those authors. One way to do that is to define a simple function to replace [null] with the empty array []: CREATE OR REPLACE FUNCTION empty_nulls(jsonb) RETURNS jsonb AS $$ SELECT CASE $1 WHEN '[null]'::jsonb THEN '[]'::jsonb ELSE $1 END $$ LANGUAGE SQL IMMUTABLE; And wrap it around the jsonb_agg call: const query = sql<authorBooksSQL>` SELECT ${"authors"}.*, empty_nulls(jsonb_agg(${"books"}.*)) AS ${"books"} FROM ${"authors"} LEFT JOIN ${"books"} ON ${"authors"}.${"id"} = ${"books"}.${"authorId"} GROUP BY ${"authors"}.${"id"}`; Alternatively, we can achieve the same result using a LATERAL join instead: const query = db.sql<authorBooksSQL>` SELECT ${"authors"}.*, bq.* FROM ${"authors"} CROSS JOIN LATERAL ( SELECT coalesce(json_agg(${"books"}.*), '[]') AS ${"books"} FROM ${"books"} WHERE ${"books"}.${"authorId"} = ${"authors"}.${"id"} ) bq`; This approach is straightforward to extend to more complex, nested cases too. Say, for example, that we let each book have multiple tags, with the following addition to the schema: CREATE TABLE "tags" ( "tag" TEXT NOT NULL , "bookId" INTEGER NOT NULL REFERENCES "books"("id") ); CREATE UNIQUE INDEX "tagsUniqueIdx" ON "tags"("bookId", "tag"); CREATE INDEX "tagsBookIdIdx" ON "tags"("tag"); Then we could retrieve authors, each with their books, each with its tag values, using the following screenful: type authorBookTagsSQL = authors.SQL | books.SQL | tags.SQL; type authorBookTagsSelectable = authors.Selectable & { books: (books.Selectable & { tags: tags.Selectable['tag'] })[] }; const query = sql<authorBookTagsSQL>` SELECT ${"authors"}.*, bq.* FROM ${"authors"} CROSS JOIN LATERAL ( SELECT coalesce(jsonb_agg(to_jsonb(${"books"}.*) || to_jsonb(tq.*)), '[]') AS ${"books"} FROM ${"books"} CROSS JOIN LATERAL ( SELECT coalesce(jsonb_agg(${"tags"}.${"tag"}), '[]') AS ${"tags"} FROM ${"tags"} WHERE ${"tags"}.${"bookId"} = ${"books"}.${"id"} ) tq WHERE ${"books"}.${"authorId"} = ${"authors"}.${"id"} ) bq`; const authorBookTags: authorBookTagsSelectable[] = await query.run(pool); console.dir(authorBookTags, { depth: null }); Which gives, correctly typed: [ { id: 1, name: 'Jane Austen', isLiving: false, books: [ { id: 284, tags: [], title: 'Pride and Prejudice', authorId: 1, createdAt: '2019-11-04T17:06:11.209512+00:00', updatedAt: '2019-11-04T17:06:11.209512+00:00' } ] }, { id: 123, name: 'Gabriel Garcia Marquez', isLiving: false, books: [ { id: 285, tags: [ 'Spanish', '1980s' ], title: 'Love in the Time of Cholera', authorId: 123, createdAt: '2019-11-04T17:06:11.209512+00:00', updatedAt: '2019-11-04T17:06:11.209512+00:00' }, { id: 286, tags: [], title: 'One Hundred Years of Solitude', authorId: 123, createdAt: '2019-11-04T17:06:11.228866+00:00', updatedAt: '2019-11-04T17:06:11.228866+00:00' } ] }, { id: 456, name: 'Douglas Adams', isLiving: false, books: [] } ] Field subsets Unless you have very wide tables and/or very large values, it could be a premature optimization to query only for a subset of fields. But if you need it, two conveniences are provided: (1) the cols function can take an array of column names, and simply spit them out quoted and comma-separated; and (2) there's a table-specific type helper OnlyCols that will narrow Selectable down to the columns included in such an array. For example: const bookCols = <const>['id', 'title']; // <const> prevents generalization to string[] type BookDatum = books.OnlyCols<typeof bookCols>; const query = sql<books.SQL>`SELECT ${cols(bookCols)} FROM ${"books"}`, bookData: BookDatum[] = await query.run(pool); Giving: > console.log(bookData); [ { id: 153, title: 'Pride and Prejudice' }, { id: 154, title: 'Love in the Time of Cholera' }, { id: 155, title: 'One Hundred Years of Solitude' } ] And that marks the end of Act 2. SQL queries are now being auto-completed and type-checked for me, which is excellent. But a lot of the simple stuff still feels a bit boiler-platey and verbose. Act 3: In which we create a set of simple shortcut functions on top, which looks a little bit like an ORM but really isn't In the final Act, I aim to make the simple one-table CRUD stuff fall-off-a-log easy with some straightforward helper functions. For these helper functions I generate a set of overloaded signatures per table, which means the return type and all other argument types can be inferred automatically from the table name argument. I haven't yet done anything to help with JOINs, but that may be worth considering for the future. SELECT First, SELECTs. I define a select function, and my schemats fork generates appropriate signatures for it for every table in the database. Using that function turns this query: const query = sql<books.SQL>`SELECT * FROM ${"books"} WHERE ${{ authorId }}`, existingBooks: books.Selectable[] = await query.run(pool); Into this: const existingBooks = await select(pool, "books", { authorId }); What's really nice here is that, thanks to the schemats-generated function signatures, once I've typed in "books" as the second argument to the function, TypeScript and VS Code know how to type-check and auto-complete both the third argument (which, if present, must be a books.Whereable) and the return value (which must be a Promise<books.Selectable[]>). The generated function signatures that make this happen look approximately like so: interface SelectSignatures { (client: Queryable, table: authors.Table, where?: authors.Whereable, options?: authors.SelectOptions): Promise<authors.Selectable[]>; (client: Queryable, table: books.Table, where?: books.Whereable, options?: books.SelectOptions): Promise<books.Selectable[]>; } The options keys include columns, which lets me limit the columns to be returned, such as: const allBookTitles = await db.select(db.pool, "books", undefined, { columns: ['title'] }); The return type is then appropriately narrowed to those columns: The options keys also include order, limit and offset, so I can do this kind of thing: const [lastButOneBook] = await select(pool, "books", { authorId }, { order: [{ by: "createdAt", direction: "DESC" }], limit: 1, offset: 1 }); The order by option is being type-checked and auto-completed, of course. I used destructuring assignment here ([lastButOneBook] = /* ... */) to account for the fact that I know this query is only going to return one response. Unfortunately, destructuring is just syntactic sugar for indexing, and indexing in TypeScript doesn't reflect that the result may be undefined. That means that lastButOneBook is now typed as a books.Selectable, but it could actually be undefined, and that could lead to errors down the line. To work around this, I also define a selectOne helper function. This turns the query above into the following: const lastButOneBook = await selectOne(pool, "books", { authorId }, { order: [{ by: "createdAt", direction: 'DESC' }], offset: 1 }); The limit option is applied automatically in this case, and the return type following await is now, correctly, books.Selectable | undefined. "Oh, great", I hear you say, unenthusiastically. "We've come all this way, and you've finished up reinventing the ORMs and query builders you promised we were going to escape". Well, I don't think so. This is not an ORM. There's no weird magic going on behind the scenes — not even the merest hint that any semantics are being obscured. There's just a mechanical transformation of some function parameters into a single SQL query, which makes my life easier. One call to select means one unsurprising SELECT query is issued, and happily both the inputs and outputs of that query are now fully type-checked. It's also not a query builder. There's no interminable method chaining. There's just type-checking and some additional conveniences applied to writing raw SQL queries, and some shortcut functions that can create basic SQL queries on your behalf. With that out of the way, onwards and upwards: INSERTs, UPDATEs, and (because, hey, now we can do anything Postgres can do) UPSERTs! INSERT The insert helper has the same sort of table-specific signatures as select, and it turns the INSERT query we saw earlier into this: const savedBook = await insert(pool, "books", { authorId: 123, title: "One Hundred Years of Solitude", }); This produces the same query we wrote previously, but now with the addition of a RETURNING * clause at the end, meaning that we get back a books.Selectable that includes values for all the columns with defaults, such as the id serial. In addition, we can insert multiple rows in one query: the function is written (and has appropriate type signatures) such that, if I instead give it an array-valued books.Insertable[], it gives me an array-valued books.Selectable[] back: const savedBooks = await insert(pool, "books", [{ authorId: 123, title: "One Hundred Years of Solitude", }, { authorId: 456, title: "Cheerio, and Thanks for All the Fish", }]); UPDATE Updates are likewise straightforward, fully typed, and also return the affected rows as a Selectable[]: const fishBookId = savedBooks[1].id, properTitle = "So Long, and Thanks for All the Fish", [updatedBook] = await update(pool, "books", { title: properTitle }, { id: fishBookId } ); We can again use a SQLFragment in an update for further flexibility — for example, if we needed to atomically increment a count. In a different context — for a schema that's not shown — I do something like the following: await update(pool, "emailAuthentication", { consecutiveFailedLogins: sql`${self} + 1`, lastFailedLogin: sql`now()`, }, { email } ); 'UPSERT' This being the thinnest possible wrapper around Postgres, we can of course do native 'UPSERT's too: I'll illustrate this one with a simplified example culled from our actual codebase, which deals with In-App Purchase updates for our iOS apps. CREATE TABLE "appleTransactions" ( "environment" "appleEnvironment" NOT NULL -- ENUM of 'PROD' or 'Sandbox' , "originalTransactionId" TEXT NOT NULL , "accountId" INTEGER REFERENCES "accounts"("id") NOT NULL , "latestReceiptData" TEXT -- ... lots more fields ... ); ALTER TABLE "appleTransactions" ADD CONSTRAINT "appleTransPKey" PRIMARY KEY ("environment", "originalTransactionId"); When we receive a purchase receipt, either via our iOS app or direct from Apple's servers, we need to either store a new record or update an existing record for each 'original transaction ID' it contains. We therefore map the transaction data in the receipt into an appleTransactions.Insertable[], and do what's needed with a single upsert call. Hard-coding the Insertable[] for ease of exposition: const newTransactions: s.appleTransactions.Insertable[] = [{ environment: 'PROD', originalTransactionId: '123456', accountId: 123, latestReceiptData: "TWFuIGlzIGRpc3Rp", }, { environment: 'PROD', originalTransactionId: '234567', accountId: 234, latestReceiptData: "bmd1aXNoZWQsIG5v", }], result = await upsert(pool, "appleTransactions", newTransactions, ["environment", "originalTransactionId"]); The last argument to upsert is the key or array of keys on which there could be a conflict. In this case, the following query is issued: { text: `INSERT INTO "appleTransactions" ("accountId", "environment", "latestReceiptData", "originalTransactionId") VALUES ($1, $2, $3, $4), ($5, $6, $7, $8) ON CONFLICT ("environment", "originalTransactionId") DO UPDATE SET ("accountId", "latestReceiptData") = ROW(EXCLUDED."accountId", EXCLUDED."latestReceiptData") RETURNING *, CASE xmax WHEN 0 THEN 'INSERT' ELSE 'UPDATE' END AS "$action"`, values: [ 123, 'PROD', 'TWFuIGlzIGRpc3Rp', '123456', 234, 'PROD', 'bmd1aXNoZWQsIG5v', '234567' ] } The (awaited) return value is an appleTransactions.UpsertReturnable[]. An UpsertReturnable is just like a Selectable but with one extra property, $action: "INSERT" | "UPDATE", valued according to what in fact happened. This is modelled on a similar approach in MS SQL Server. The result of the above query might therefore be: > console.log(result); [{ "environment": "PROD", "originalTransactionId": "123456", "accountId": 123, "latestReceiptData": "TWFuIGlzIGRpc3Rp", "$action": "UPDATE" }, { "environment": "PROD", "originalTransactionId": "234567", "accountId": 234, "latestReceiptData": "bmd1aXNoZWQsIG5v", "$action": "INSERT" }] Encore: Transactions The 'transactions' we just saw were data about people giving us money, but — for added confusion — let's talk for a moment about database transactions (as in: BEGIN TRANSACTION and COMMIT TRANSACTION). Transactions a

gem-hunter