pgtyped-model

This library provides convenient no-magic interface for entity mapping and customization of queries generated by pgTyped.

Rationale

Not 👏 an 👏 ORM 👏 !

The key feature of pgTyped is being able to write queries in SQL without any ORM magic, and have them translated into typescript perfectly.

This library builds on top of pgTyped approach and enables you to map queries to your application entities with minimal boilerplate, while still retaining the transparency of writing queries in pure SQL.

Features

✅  Built-in flag to convert all column names to 🐪 camelCase - Example ⬇️

✅  Map all queries in the model to a unified data format - Example ⬇️

✅  Extend mapping for specific queries - Example ⬇️

✅  Override mapping for specific queries - Example ⬇️

✅  Create multiple variations of the same query with different return types - Example ⬇️

✅  Convenient helper functions for common transformations - Example ⬇️

✅  Invoke a custom hook on each database query (useful for logging or debugging) - Example ⬇️

Installation

Using npm

npm install pgtyped-model

Using yarn

yarn add pgtyped-model

Usage

File structure

Assuming you are building a simple music app, where Album is one of the entities in your system:

models
└── Album
    ├── Album.sql        # Hand-written SQL queries
    ├── Album.queries.ts # File generated by PgTyped
    └── index.ts         # Define & export your model here

Create a basic model

Let's start by defining a simple model that does nothing except converting all column names to camelCase.

import {createModel} from "pgtyped-model"
// Import client or pool instance from pg library
import {pool} from "./connection"
// Import generated queries
import * as queries from "./Album.queries"

// AlbumModel exposes all queries, but with column names mapped to camelCase.
export const AlbumModel = createModel({
  queries,
  connection: pool,
  camelCaseColumnNames: true,
})

Map all queries to an entity

Now, let's map the results of all queries to Album class.

import {createModel, mapWithEntity} from "pgtyped-model"
import {pool} from "./connection"
import * as queries from "./Album.queries"

// Return type of all queries will change to `Album[]`.
export const AlbumModel = createModel({
  queries,
  connection: pool,
  camelCaseColumnNames: true,
  collectDefault: mapWithEntity(Album),
})

Extend mapping for specific queries

In some cases, it is useful to extend the default mapping for particular queries. A typical example of that would be queries that can only return one or zero rows.

import {createModel, takeOne, mapWithEntity} from "pgtyped-model"
import {pool} from "./connection"
import * as queries from "./Album.queries"

// Return type of all queries except `getAlbum` remains `Album[]`
export const AlbumModel = createModel({
  queries,
  connection: pool,
  camelCaseColumnNames: true,
  collectDefault: mapWithEntity(Album),
}).extend({
  // `getAlbum` will return `Album | undefined`
  getAlbum: takeOne(),
})

Override mapping for specific queries

In other cases, one or several queries in the model might return a different set of columns from the rest of the queries, and hence need to be mapped differently. This typically applies to aggregate and join queries.

import {createModel, mapWithEntity} from "pgtyped-model"
import {pool} from "./connection"
import * as queries from "./Album.queries"

// Return type of all queries except `listAlbumsWithTracks` remains `Album[]`
export const AlbumModel = createModel({
  queries,
  connection: pool,
  camelCaseColumnNames: true,
  collectDefault: mapWithEntity(Album),
  collect: {
    // `listAlbumsWithTracks` will return `AlbumWithTracks[]`
    listAlbumsWithTracks: groupWith("albumId", (rows) => {
      const tracks = rows.map((row) => new Track(row))
      return new AlbumWithTracks({...rows[0], tracks})
    }),
  },
})

Create multiple variations of a query

import {createModel, mapWithEntity, indexWith} from "pgtyped-model"
import {pool} from "./connection"
import * as queries from "./Album.queries"

// Return type of all queries except `getAlbum` remains `Album[]`
export const AlbumModel = createModel({
  queries: {
    ...queries,
    // Exposing `listAlbums` under a new name
    listTitlesByArtistId: queries.listAlbums,
  },
  connection: pool,
  camelCaseColumnNames: true,
  collectDefault: mapWithEntity(Album),
}).extend({
  // `listAlbums` will return `Album[]`
  // `listTitlesByArtistId` will return `{ [artistId: string]: string[] }`
  listTitlesByArtistId: indexWith(
    "artistId",
    mapWith((album) => album.albumName),
  ),
})

Collect functions

• mapWith
• mapWithEntity
• takeOne
• indexWith
• groupWith

mapWith(mapResults)

Maps an array of results with the provided function

Arguments:

• mapResults: (result: R) => T

Returns: (results: R[]) => T[]

---

mapWithEntity(Entity)

Maps an array of results with the provided class constructor

Arguments:

• Entity: { new: (data: R) => T }

Returns: (results: R[]) => T[]

---

takeOne(index = 0)

Returns a single item from the array of results

Arguments:

• index?: number

Returns: (results: R[]) => R | undefined

---

indexWith(field, collectFunction)

Converts an array of results into an object

Arguments:

• field: keyof R
• collectFunction?: (row: R) => T

Returns: (results: R[]) => Record<R[K], R[] | T>

---

groupWith(field, collectFunction)

Collapses multiple rows into one, grouped by the value of the provided field

Arguments:

• field: keyof R
• collectFunction: (results: R[]) => T

Returns: (results: R[]) => T[]

Attach a custom hook

Sometimes it's useful to invoke a custom hook to each successful query. For example, for debugging or logging purposes.

import {createModel} from "pgtyped-model"
import {pool} from "./connection"
import * as queries from "./Album.queries"

export const AlbumModel = createModel({
  queries,
  connection: pool,
  camelCaseColumnNames: true,

  onQuery: ({queryName, params, rows, result}) => {
    console.table([
      {
        queryName,
        params: JSON.stringify(params),
        rowCount: rows.length,
      },
    ])
  },
})