A small companion library for node-postgres
npm install possu
!License

A small companion library for node-postgres.
- A Promise-based API, which aims to reduce boilerplate code
- Write raw SQL queries with tagged template strings
- Prevent most types of accidental SQL injection vulnerabilities
- Transaction and savepoint handling, including retrying in case of
serialization failures and deadlocks.
- First-class TypeScript support
- Not a framework. Most Possu functions take either a
pg.Pool or a
pg.PoolClient as an argument, so you
can integrate Possu easily to an existing application.
- Installation
- Getting started
- API
- Building queries
- sql
- sql.identifier
- sql.json
- Executing queries
- query
- queryOne
- queryMaybeOne
- execute
- executeOne
- executeMaybeOne
- Transaction handling
- withTransaction
- withSavepoint
Run either
``shell`
$ npm install possu
or
`shell`
$ yarn add possu
depending on your favourite package manager.
If you've ever written an application using
node-postgres, a lot of your database code
might look a bit like this:
`typescript`
async function getUser(tx, userId) {
const result = await tx.query('SELECT * FROM users WHERE user_id = $1', [
userId,
])
return result.rows[0]
}
In addition to the SQL query, there is some boilerplate code that selects the
correct amount of rows from the query result. In a large application, this
can get quite repetetive. Things can even more complicated if you're only
interested in a single column from the result set.
`typescript`
async function getUserNames(tx) {
const result = await tx.query('SELECT name FROM users')
return result.rows.map((row) => row.name)
}
The goal of Possu is to eliminate this kind of boilerplate code from your
application.
`typescript
import { query, queryMaybeOne, sql } from 'possu'
function getUser(tx, userId) {
return queryMaybeOne(tx, sqlSELECT * FROM users WHERE user_id = ${userId})
}
function getUserNames(tx) {
return query(tx, sqlSELECT name FROM users)`
}
Here we use Possu's sql tagged template literal for constructing thequery
queries, while and queryMaybeOne functions contain the necessary
code for selecting the correct amount of rows from the result set.
In the getUserNames function, possu automatically unwraps the name column
from each row, since in most cases, an extra object wrapper in the results of
a single-column query is just extra noise.
That's it! This was not an exhaustive tour of Possu, but it should be enough
to get an idea of its main features.
#### sql
`typescript`
(parts: TemplateStringsArray, ...values: unknown[]) => SqlQuery
Create an SQL query.
This is the only way to create queries in Possu. To prevent accidental SQL injections, other
Possu functions check at runtime that the query has been created with sql.
Example:
`typescriptSELECT * FROM users WHERE user_id = ${1}
const query = sql`
// => SqlQuery { text: 'SELECT * FROM users WHERE user_id = $1', values: [1] }
Queries may be nested within other queries, which can be a powerful mechanism for code reuse.
`typescriptSELECT * FROM users WHERE user_id = ${1}
const usersQuery = sqlSELECT exists(${usersQuery})
const existsQuery = sql`
// => SqlQuery { text: 'SELECT exists(SELECT * FROM users WHERE user_id = $1)', values: [1] }
Nested queries can also be used to customize parts of a query without having to worry about SQL injections.
`typescriptSELECT * FROM users ORDER BY name ${
const order = 'asc'
const query = sql
order === 'asc' ? sqlASC : sqlDESC
}`
// => SqlQuery { text: 'SELECT * FROM users ORDER BY name ASC', values: [] }
Calling the .prepare() method on a query causes it be executed as a prepared statement.
This can sometimes have measurable performance benefits, especially if the query is very complex to parse and plan.
See the PostgreSQL manual
for more information.
`typescriptSELECT * FROM users
sql.prepare('fetch-users')`
// => SqlQuery { text: 'SELECT * FROM users', values: [], name: 'fetch-users' }
---
#### sql.identifier
`typescript`
(name: string) => Identifier
Escape an SQL
identifier
to be used in a query. It can be used to create queries which are
parametrized by table or column names.
Example:
`typescriptSELECT * FROM ${sql.identifier('users')}
sql`
// => SqlQuery { text: 'SELECT * FROM "users"', values: [] }
`typescriptSELECT * FROM users ORDER BY ${sql.identifier('name')} DESC
sql`
// => SqlQuery { text: 'SELECT * FROM users ORDER BY "name" DESC', values: [] }
---
#### sql.json
`typescript`
(value: unknown) => string
Serialize a value as JSON to be used in a query.
Example:
`typescriptSELECT * FROM jsonb_array_elements(${sql.json([1, 2, 3])})
sql`
// => SqlQuery { text : 'SELECT * FROM jsonb_array_elements($1)', values: ['[1,2,3]'] }
---
Each of the query functions take a connection pool or a client checked out of
the pool as the first argument.
For queries that return result rows, you may also supply an optional row
parser, which can validate and transform the value of each row. This
can be useful when combined with a library like
io-ts or
runtypes.
When using TypeScript, the type of each result row is unknown by default,
so you must either cast the result to the correct type or to use a row
parser that helps the TypeScript compiler infer the correct result type.
`typescript
import { Record, Number, String } from 'runtypes'
const result = await query)
// Type inferred to string[]
const User = Record({
id: Number,
name: String,
})
const users = await query(db, sqlSELECT * FROM users, User.check)`
// Type inferred to [{ id: number, name: string }]
As an additional TypeScript helper, possu exports a Connection type, which can be used in your own query functions aspg.Pool | pg.PoolClient
a generic connection parameter. It is a type alias for .
`typescript
import { Connection, query, sql } from 'possu'
export function getUsers(conn: Connection) {
return query(conn, sqlSELECT * FROM users)`
}
For actions that must be performed within a transaction, Possu also provides a Transaction type, which is just apg.PoolClient
regular with a type-level brand. Using it is completely optional, but it may improve the readability and
type-safety of your code.
`typescript
import { Transaction, query, sql } from 'possu'
export async function insertTwoUsers(tx: Transaction) {
await execute(tx, sqlINSERT INTO users (name) VALUES ('Alice'))INSERT INTO users (name) VALUES ('Bob')
await execute(tx, sql)`
}
#### query
`typescript`
Execute a SELECT or other query that returns zero or more rows. Returns all rows.
Example:
`typescriptSELECT * FROM users
const users = await query(db, sql)`
// => [{ id: 1, name: 'Alice' }, { id: 2, name: 'Bob' }]
If selecting a single column, each result row is unwrapped automatically.
`typescriptSELECT name FROM users
const names = await query(db, sql)`
// => ['Alice', 'Bob']
---
#### queryOne
`typescript`
Execute a SELECT or other query that returns exactly one row. Returns the first row.
- Throws a ResultError if query doesn't return exactly one row.
Example:
`typescriptSELECT * FROM users WHERE id = 1
const user = await queryOne(db, sql)`
// => { id: 1, name: 'Alice' }
If selecting a single column, it is unwrapped automatically.
`typescriptSELECT name FROM users WHERE id = 1
const name = await queryOne(db, sql)`
// => 'Alice'
You can transform the result with a custom row parser. Here we transform the
count from a string to a number by using the built-in Number constructor.
`typescriptSELECT count(*) FROM users
const count = await queryOne(db, sql, Number)`
// => 3
---
#### queryMaybeOne
`typescript`
Execute a SELECT or other query that returns zero or one rows. Returns the first row or undefined.
- Throws a ResultError if query returns more than 1 row.
Example:
`typescriptSELECT * FROM users WHERE id = 1
const user = await queryMaybeOne(db, sql)
// => { id: 1, name: 'Alice' }
const nil = await queryMaybeOne(db, sqlSELECT * FROM users WHERE false)`
// => undefined
If selecting a single column, it is unwrapped automatically.
`typescriptSELECT name FROM users WHERE id = 1
const name = await queryMaybeOne(db, sql)`
// => 'Alice'
---
#### execute
`typescript`
(connection: Connection, query: SqlQuery) => Promise
Execute an INSERT, UPDATE, DELETE or other query that is not expected to return any rows. Returns the number of
rows affected.
Example:
`typescriptINSERT INTO users (name) VALUES ('Eve')
const rowCount = await execute(db, sql)`
// => 1
---
#### executeOne
`typescript`
(tx: Transaction, query: SqlQuery) => Promise
Execute an INSERT, UPDATE, DELETE or other query that affects exactly one row. Returns the number of rows
affected (1).
- Throws a ResultError if the query doesn't affect exactly one row. execute
- Unlike , it must be called within an explicit transaction, so the changes can be rolled back.
Example:
`typescriptUPDATE users SET name = 'Bob' WHERE id = 1
await withTransaction(db, (tx) => {
return executeOne(tx, sql)`
})
// => 1
---
#### executeMaybeOne
`typescript`
(tx: Transaction, query: SqlQuery) => Promise
Execute an INSERT, UPDATE, DELETE or other query that affects zero or one rows. Returns the number of
rows affected.
- Throws a ResultError if the query affects more than one row. execute
- Unlike , it must be called within an explicit transaction, so the changes can be rolled back.
Example:
`typescriptUPDATE users SET name = 'Bob' WHERE id = 1
await withTransaction(db, (tx) => {
return executeMaybeOne(tx, sql)`
})
// => 1
#### withTransaction
`typescript`
Execute a set of queries within a transaction.
Start a transaction and execute a set of queries within it. If the function
does not throw an error, the transaction is committed.
If the function throws a non-retryable error, the transaction is rolled back
and the error is rethrown.
If the function throws a retryable error, the transaction is rolled back and
retried up to 2 or maxRetries times. By default, PostgreSQL errors codes40001 (serialization failure) and 40P01 (deadlock detected) areshouldRetry
considered to be retryable, but you may customize the behavior by supplying a
custom predicate.
You may also configure the access
mode and
isolation
level of the
transaction by supplying the accessMode and isolationLevel options,
respectively.
Example:
`typescriptINSERT INTO users (name) VALUES ('${'Alice'}')
const userCount = await withTransaction(db, async (tx) => {
await execute(tx, sql)INSERT INTO users (name) VALUES ('${'Bob'}')
await execute(tx, sql)INSERT INTO users (name) VALUES ('${'Charlie'}')
await execute(tx, sql)SELECT count(*) FROM users
return queryOne(tx, sql, Number)`
})
---
#### withSavepoint
`typescript`
Execute a set of queries within a savepoint.
Start a savepoint and execute a set of queries within it. If the function
does not throw an error, the savepoint is released.
If the function throws any kind of error, the savepoint is rolled back and
the error is rethrown.
May only be used within a transaction.
Example:
`typescriptINSERT INTO users (name) VALUES ('Alice')
await withTransaction(db, async (tx) => {
await execute(tx, sql)INSERT INTO users (name) VALUES ('Bob')
return withSavepoint(tx, async (tx) => {
await execute(tx, sql)INSERT INTO users (name) VALUES ('Charlie')
await execute(tx, sql)``
}).catch((err) => {
// Let the first insert to through if the second or third one fail.
})
})