postgres.querygen - Query generator for postgres, built on top of PostgresJS

Lightning fast query generator to accompany your use of the PostgresJS library. What does lightning fast mean? Frankly I don't know, but see the performance section below for benchmarks.

This library was created to reduce the boilerplate for very common queries. Having the flexibility of the PostgresJS library's inherent query building mechanism is great, but it usually ends up with teams writing large amounts of the same code. For simple CRUD entities this is usually overkill and not the reason you are using a lower level library like PostgresJS. We help you fill the gap by standardizing the way you interact with your tables, without boxing you into the use of an ORM or a different driver like PG.

Installation

NPM

npm install --save postgres postgres.querygen

PNPM

pnpm install --save postgres postgres.querygen

Yarn

yarn add postgres postgres.querygen

Getting Started

Setup your row type

Given an example table for an inventory item list:

CREATE TABLE inventory_items (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    quantity INT NOT NULL,
    is_published BOOLEAN NOT NULL
)

We can then construct our type definition for the row as follows:

type InventoryItemRow {
    id: number;
    name: string;
    quantity: number;
    isPublished: boolean;
}

From here all you need to do is initialize the querygen instance with the type and you are good to go!

import { createInstance } from "postgres.querygen";
import { connection } from "./connection";

const inventoryQuerygen = createInstance<InventoryItemRow>({
    tableName: "inventory_items" // Mandatory for construction of the 
    connection, // Mandatory connection of type "sql", can also be a transaction sql object!
})

// Using the select one by column convenience method, yields a single row filtered on the column provided
await inventoryQuerygen.selectOneByColumn("id", 1);

// Using the select many convenience method, yields multiple rows filtered by the arguments given
await inventoryQuerygen.selectMany({
    quantity__gte: 1
});

// Using the select many convenience method, yields multiple rows filtered by the arguments given, but adheres to keyset pagination (more paginators available in the paginators module)
await inventoryQuerygen.selectMany<KeysetPaginator>({
    quantity__gte: 1
}, {
    after: 2,
    pageSize: 1
});

// Using the generated filters directly with the PostgresJS templates
await connection`
SELECT id
FROM inventory_items
WHERE true
${inventoryQuerygen.buildFilters({
    id__in: [1, 2]
})}
`