@fmtk/pgfn v0.1.1
@fmtk/pgfn
This package contains utility functions for building composable queries for use with node-postgres.
Quick start
import * as pg from 'pg';
import { makePgPoolClient, sql } from '@fmtk/pgfn';
const pool = new pg.Pool({
/* connection params */
});
const db = makePgPoolClient(pool);
await db(sql`SELECT current_database()`);
Principals
SQL Queries
This library does not create yet another DSL for querying databases, because SQL works fine.
Queries are constructed using a tagged template:
const query = sql`SELECT * FROM products WHERE id=${id}`;
The query is automatically converted to a parameterised query for the driver. This has the same effect as the following:
// equivalent to:
pool.query(`SELECT * FROM products WHERE id=$1`, id);
Literal values and other queries can also be included:
const select = sql`SELECT * FROM ${verbatim(table)} WHERE id=${id}`;
const query = sql`WITH cte AS (${select}) SELECT * FROM cte`;
Composability
The database connection is simplified to a single function which can be passed a simple tokenized query or a more complex command function:
interface DbClient {
(query: TokenizedQuery): PromiseLike<QueryResult>;
<T>(command: DbCommand<T>): PromiseLike<T>;
}
interface DbCommand<T> {
(db: DbClient): PromiseLike<T>;
}
This makes composability really easy. Consider the transaction function:
function transaction<T>(
cmd: DbCommand<T>,
isolation = TransactionIsolation.Serializable,
): DbCommand<T> {
return async (db): Promise<T> => {
if (txdb[TransactingSymbol]) {
throw new DbError(`nested transactions are not supported`);
}
const txdb = cloneClient(db);
txdb[TransactingSymbol] = true;
try {
await db(sql`BEGIN TRANSACTION ISOLATION LEVEL ${verbatim(isolation)}`);
const result = await cmd(db);
await db(sql`COMMIT`);
return result;
} catch (e) {
await db(sql`ROLLBACK`);
throw e;
}
};
}
This function wraps any given command in another command that automatically runs BEGIN TRANSACTION
, COMMIT
and ROLLBACK
as necessary. Because the connection object is also just a function, it can be easily wrapped too.
Consider also the implementation of makePgPoolClient
:
function makePgPoolClient(pool: PgPool): DbClient {
return async (query) => {
const client = await pool.connect();
try {
const connectedClient = makePgClient(client);
return connectedClient(query);
} finally {
client.release();
}
},
);
}
This function automatically acquires a connection from the pool on first use and returns it when the query (or batch of queries) is complete. Composition of the db
function makes this trivial.