pgr

This module aims to provide a structured and easy way to execute queries against a Postgres DB. It's a good fit if you want more support than using the pg module by itself but don't want to use an ORM. Its main features include a tagged template string based query helper and a small wrapper around pg's actual query methods.

Installation

yarn add pgr

Basic Example Usage

Once, in your application's entry point (before you want to run a query):

import{createPool}from'pgr'createPool('myPoolName',{// The options here are exactly what you can provide to pg, such ashost: 'localhost',user: 'Andre',password: '',database: 'mydb',})

If you only create one pool, you don't need to specify its name when running queries. For multiple pool support, check out the advanced usage section below.

Later on:

import{query,sql}from'pgr'constvalue=42constrows=awaitquery(sql` SELECT * FROM my_table WHERE some_col = ${value}`)

That's it! The sql tagged template string will run your statement through pgformat (always with %L) to properly escape any dangerous variables and invoke it with your previously created pool.

sql.if

I find that I often want to dynamically construct my statements based on the truthiness of a given variable. This allows for compact, powerful query methods similar to what you might find in an ORM. Enter sql.if:

Simple mode (your test variable and arg are the same)

Note: For purposes of sql.if, the number 0 is treated as truthy, and an empty array is treated as falsy.

import{query,sql}from'pgr'constfindUsers=async({ id, accountId, emails, roles })=>query(sql` SELECT * FROM users WHERE status = 'active'${sql.if('AND id = ?',id)}${sql.if('AND email = ?',email)}${sql.if('AND role IN (?)',roles)} `)

awaitfindUsers({id: 73})

SELECT*FROM users
WHERE status ='active'AND id ='73'

Your variable will get subbed in for the question mark in your expression. If there is no question mark, the variable will be used to test if the expression should be added as-is.

awaitfindUsers({accountId: 1,roles: ['admin','superadmin']})

SELECT*FROM users
WHERE status ='active'AND account_id ='1'AND role IN ('admin', 'superadmin')

awaitfindUsers({accountId: 1,roles: []})// An empty array is treated as falsy

SELECT*FROM users
WHERE status ='active'AND account_id ='1'

Complex mode (different test and arg variables, arg is optional)

import{query,sql}from'pgr'constSTATUSES=[1,2,3]constfindRelationships=async({ id, includeOngoing })=>{constcheckStatus= ... // External function returning true/falsereturnquery(sql` SELECT * FROM relationships WHERE from_id = ${id}${sql.if({test: includeOngoing,expr: 'AND end_date IS NULL'})}${sql.if({test: checkStatus,expr: 'AND status IN (?)',arg: STATUSES})} `)}

awaitfindRelationships({id: 1,includeOngoing: true})

(assuming checkStatus was true):
SELECT*FROM relationships
WHERE from_id =1AND end_date IS NULLAND status IN ('1','2','3')

sql.raw

You may have standard query fragments that you build up and inject into many queries. You might also have situations where pgformat's substitution doesn't achieve what you need. The escape hatch that you can use carefully is sql.raw.

constcurrentUser={purchasedItems: [10,20]}constfragment=sql`AND allowed_items IN (${currentUser.purchasedItems})`conststatement=sql`
SELECT *
FROM items
WHERE on_sale = true
${sql.raw(fragment)}

SELECT*FROM items
WHERE on_sale = true
AND allowed_items IN ('10','20')

Note that fragments must themselves be run through sql if you need escaping. Don't be like little Bobby Tables.

constname="Robert'); DROP TABLE Students; --"conststatement=sql`
SELECT *
FROM oh_no
WHERE name IN ('${sql.raw(fragment)}')

SELECT*FROM oh_no
WHERE name IN ('Robert'); DROPTABLEStudents; --')

query, query.one, query.transaction

We've seen the most simple form of query, but it can also take a second options argument:

constrows=awaitquery(sql`SELECT ...`,{debug: false,// Logs the statement to the console before running itdebugOnly: false,// Logs the statement to the console and does NOT run itpoolName: '',// Runs the query with a client of the specified pool namerowMapper: row=>{},// A (synchronous) function to run on every row in the result})constknownEmails=awaitquery(sql`SELECT email FROM users`,{rowMapper: row=>row.email,})

query.one

Invoked exactly like query, except that instead of returning an array of rows, it will return one object. If your query results in no rows, it will return a null. If your query returns more than one row, it will throw an Error. You can also use rowMapper here.

const{ email }=awaitquery.one(sql`SELECT email FROM users WHERE id = ${currentUserId}`)console.log(email)// 'apazzolini@test.test'

query.transaction

You can also run multiple queries inside of a transaction:

constresult=awaitquery.transaction(asynctquery=>{// Inside this function, you should take care to use tquery// instead of query or you may run into deadlocks.// tquery behaves exactly like query (and also has tquery.one)return'myResult'})console.log(result)// 'myResult'

Metrics

pgr stores average execution time for your queries along with the number of times the query has happened. This is done by taking the base query (pre variable insertion) and giving it an ID based on its hash. This allows aggregating metrics even if a query is executed multiple times with different arguments.

const{ metrics }=getPool('default')console.log(metrics.queries)// { [id]: { baseStatement: '...', count: 1, avgMs: 100 }}

License

MIT