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):
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:
const value = 42 const rows = await
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.
const findUsers = async
await
SELECT *FROM usersWHERE 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.
await
SELECT *FROM usersWHERE status = 'active' AND account_id = '1' AND role IN ('admin', 'superadmin')
await // An empty array is treated as falsy
SELECT *FROM usersWHERE status = 'active' AND account_id = '1'
Complex mode (different test and arg variables, arg is optional)
const STATUSES = 1 2 3 const findRelationships = async { const checkStatus = ... // External function returning true/false return }
await
(assuming checkStatus was true): SELECT *FROM relationshipsWHERE from_id =1 AND end_date IS NULL AND 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
.
const currentUser = purchasedItems: 10 20 const fragment = sql`AND allowed_items IN ()` const statement = sql` SELECT * FROM items WHERE on_sale = true
SELECT *FROM itemsWHERE 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.
const name = "Robert'); DROP TABLE Students; --" const statement = sql` SELECT * FROM oh_no WHERE name IN ('${sql.raw(fragment)}')
SELECT *FROM oh_noWHERE name IN ('Robert');; --')
query, query.one, query.transaction
We've seen the most simple form of query, but it can also take a second options
argument:
const rows = await const knownEmails = await
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 = await queryconsole // 'apazzolini@test.test'
query.transaction
You can also run multiple queries inside of a transaction:
const result = await query console // '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 = console // { [id]: { baseStatement: '...', count: 1, avgMs: 100 } }
License
MIT