slonik
Advanced tools
Readme
A PostgreSQL client with strict types, detail logging and assertions.
node-postgres).Slonik exports two factory functions:
createPoolcreateConnectionThe API of the query method is equivalent to that of pg.
Refer to query methods for documentation of Slonik-specific query methods.
Both functions accept the same parameters:
connectionConfigurationclientConfigurationtype DatabaseConnectionUriType = string;
type DatabaseConfigurationType =
DatabaseConnectionUriType |
{|
+database?: string,
+host?: string,
+idleTimeoutMillis?: number,
+max?: number,
+password?: string,
+port?: number,
+user?: string
|};
/**
* @property interceptors An array of [Slonik interceptors](https://github.com/gajus/slonik#slonik-interceptors).
* @property onConnect A new connection handler. Executed after a connection is established, but before allowing the connection to be used by any clients.
*/
type ClientConfigurationType = {|
+interceptors?: $ReadOnlyArray<InterceptorType>,
+onConnect?: (connection: DatabaseConnectionType) => MaybePromiseType<void>
|};
Example:
import {
createPool
} from 'slonik';
const pool = createPool('postgres://localhost');
await pool.query(sql`SELECT 1`);
Slonik only allows to check out a connection for a duration of promise routine supplied to the connect() method.
import {
createPool
} from 'slonik';
const pool = createPool('postgres://localhost');
const result = await pool.connect(async (connection) => {
await connection.query(sql`SELECT 1`);
await connection.query(sql`SELECT 2`);
return 'foo';
});
result;
// 'foo'
Connection is released back to the pool after the promise produced by the function supplied to connect() method is either resolved or rejected.
The primary reason for implementing only this connection pooling method is because the alternative is inherently unsafe, e.g.
// Note: This example is using unsupported API.
const main = async () => {
const connection = await pool.connect();
await connection.query(sql`SELECT produce_error()`);
await connection.release();
};
In this example, the error causes early rejection of the promise and a hanging connection. A fix to the above is to ensure that connection#release() is always called, i.e.
// Note: This example is using unsupported API.
const main = async () => {
const connection = await pool.connect();
let lastExecutionResult;
try {
lastExecutionResult = await connection.query(sql`SELECT produce_error()`);
} finally {
await connection.release();
}
return lastExecutionResult;
};
Slonik abstracts the latter pattern into pool#connect() method.
Functionality can be added to Slonik client by adding interceptors.
Each interceptor can implement several functions which can be used to change the behaviour of the database client.
type InterceptorType = {|
+beforeQuery?: (query: QueryType) => Promise<QueryResultType<QueryResultRowType>> | Promise<void> | QueryResultType<QueryResultRowType> | void,
+afterQuery?: (query: QueryType, result: QueryResultType<QueryResultRowType>) => Promise<void> | void
|};
Interceptors are configured using client configuration, e.g.
import {
createPool
} from 'slonik';
const interceptors = [];
const connection = createPool('postgres://', {
interceptors
});
There are 2 functions that an interceptor can implement:
Interceptors are executed in the order they are added.
beforeQuerybeforeQuery is the first interceptor function executed.
This function can optionally return a direct result of the query which will cause the actual query never to be executed.
afterQueryafterQuery is the last interceptor function executed.
auto_explainexecutionTime log property describes how long it took for the client to execute the query, i.e. it includes the overhead of waiting for a connection and the network latency, among other things. However, it is possible to get the real query execution time by using auto_explain module.
There are several pre-requisites:
-- Load the extension.
LOAD 'auto_explain';
-- or (if you are using AWS RDS):
LOAD '$libdir/plugins/auto_explain';
-- Enable logging of all queries.
SET auto_explain.log_analyze=true;
SET auto_explain.log_format=json;
SET auto_explain.log_min_duration=0;
SET auto_explain.log_timing=true;
-- Enables Slonik to capture auto_explain logs.
SET client_min_messages=log;
This can be configured using onConnect connection handler.
const pool = await createPool('postgres://localhost', {
onConnect: async (connection) => {
await connection.query(sql`LOAD 'auto_explain'`);
await connection.query(sql`SET auto_explain.log_analyze=true`);
await connection.query(sql`SET auto_explain.log_format=json`);
await connection.query(sql`SET auto_explain.log_min_duration=0`);
await connection.query(sql`SET auto_explain.log_timing=true`);
await connection.query(sql`SET client_min_messages=log`);
}
});
Slonik recognises and parses the auto_explain JSON message; Roarr logger will produce a pretty-print of the explain output, e.g.
[2018-12-31T21:15:21.010Z] INFO (30) (@slonik): notice message
notice:
level: notice
message:
Query Text: SELECT count(*) FROM actor
Plan:
Node Type: Aggregate
Strategy: Plain
Partial Mode: Simple
Parallel Aware: false
Startup Cost: 4051.33
Total Cost: 4051.34
Plan Rows: 1
Plan Width: 8
Actual Startup Time: 26.791
Actual Total Time: 26.791
Actual Rows: 1
Actual Loops: 1
Plans:
-
Node Type: Seq Scan
Parent Relationship: Outer
Parallel Aware: false
Relation Name: actor
Alias: actor
Startup Cost: 0
Total Cost: 3561.86
Plan Rows: 195786
Plan Width: 0
Actual Startup Time: 0.132
Actual Total Time: 15.29
Actual Rows: 195786
Actual Loops: 1
node-postgrestimestamp and timestamp with time zone returns UNIX timestamp in milliseconds.connect() method requires that connection is restricted to a single promise routine (see Checking out a client from the connection pool).Slonik will strip all comments and line-breaks from a query before processing it.
This makes logging of the queries easier.
The implication is that your query cannot contain values that include a newline character, e.g.
// Do not do this
connection.query(sql`INSERT INTO foo (bar) VALUES ('\n')`);
If you want to communicate a value that includes a multiline character, use value placeholder interpolation, e.g.
connection.query(sql`INSERT INTO foo (bar) VALUES (${'\n'})`);
Slonik enables use of question mark (?) value placeholders, e.g.
await connection.query('SELECT ?', [
1
]);
Question mark value placeholders are converted to positional value placeholders before they are passed to the pg driver, i.e. the above query becomes:
SELECT $1
Note: Mixing anonymous and position placeholders in a single query will result in an error.
A question mark is interpolated into a value set when the associated value is an array, e.g.
await connection.query('SELECT ?', [
[
1,
2,
3
]
]);
Produces:
SELECT ($1, $2, $3)
A question mark is interpolated into a list of value sets when the associated value is an array of arrays, e.g.
await connection.query('SELECT ?', [
[
[
1,
2,
3
],
[
1,
2,
3
]
]
]);
Produces:
SELECT ($1, $2, $3), ($4, $5, $6)
A :[a-zA-Z] regex is used to match named placeholders.
await connection.query('SELECT :foo', {
foo: 'FOO'
});
Produces:
SELECT $1
Query methods can be executed using sql tagged template literal, e.g.
import {
sql
} from 'slonik'
connection.query(sql`
INSERT INTO reservation_ticket (reservation_id, ticket_id)
VALUES ${values}
`);
Arguments of a tagged template literal invocation are replaced with an anonymous value placeholder, i.e. the latter query is equivalent to:
connection.query(sql`
INSERT INTO reservation_ticket (reservation_id, ticket_id)
VALUES ?
`, [
values
]);
Delimited identifiers are created by enclosing an arbitrary sequence of characters in double-quotes ("). To create create a delimited identifier, create an sql tag function placeholder value using sql.identifier, e.g.
sql`
SELECT ${'foo'}
FROM ${sql.identifier(['bar', 'baz'])}
`;
// {
// sql: 'SELECT ? FROM "bar"."baz"',
// values: [
// 'foo'
// ]
// }
Raw SQL can be inlined using sql.raw, e.g.
sql`
SELECT ${'foo'}
FROM ${sql.raw('"bar"')}
`;
// {
// sql: 'SELECT ? FROM "bar"',
// values: [
// 'foo'
// ]
// }
When using tagged template literals, it is easy to forget to add the sql tag, i.e.
Instead of:
connection.query(sql`
INSERT INTO reservation_ticket (reservation_id, ticket_id)
VALUES ${values}
`);
Writing
connection.query(`
INSERT INTO reservation_ticket (reservation_id, ticket_id)
VALUES ${values}
`);
This would expose your application to SQL injection.
Therefore, I recommend using eslint-plugin-sql no-unsafe-query rule. no-unsafe-query warns about use of SQL inside of template literals without the sql tag.
anyReturns result rows.
Example:
const rows = await connection.any(sql`SELECT foo`);
#any is similar to #query except that it returns rows without fields information.
anyFirstReturns value of the first column of every row in the result set.
DataIntegrityError if query returns multiple rows.Example:
const fooValues = await connection.anyFirst(sql`SELECT foo`);
insertUsed when inserting 1 row.
Example:
const {
insertId
} = await connection.insert(sql`INSERT INTO foo SET bar='baz'`);
The reason for using this method over #query is to leverage the strict types. #insert method result type is InsertResultType.
manyReturns result rows.
NotFoundError if query returns no rows.Example:
const rows = await connection.many(sql`SELECT foo`);
manyFirstReturns value of the first column of every row in the result set.
NotFoundError if query returns no rows.DataIntegrityError if query returns multiple rows.Example:
const fooValues = await connection.many(sql`SELECT foo`);
maybeOneSelects the first row from the result.
null if row is not found.DataIntegrityError if query returns multiple rows.Example:
const row = await connection.maybeOne(sql`SELECT foo`);
// row.foo is the result of the `foo` column value of the first row.
maybeOneFirstReturns value of the first column from the first row.
null if row is not found.DataIntegrityError if query returns multiple rows.DataIntegrityError if query returns multiple columns.Example:
const foo = await connection.maybeOneFirst(sql`SELECT foo`);
// foo is the result of the `foo` column value of the first row.
oneSelects the first row from the result.
NotFoundError if query returns no rows.DataIntegrityError if query returns multiple rows.Example:
const row = await connection.one(sql`SELECT foo`);
// row.foo is the result of the `foo` column value of the first row.
Note:
I've been asked "What makes this different from knex.js
knex('foo').limit(1)?".knex('foo').limit(1)simply generates "SELECT * FROM foo LIMIT 1" query.knexis a query builder; it does not assert the value of the result. Slonik#oneadds assertions about the result of the query.
oneFirstReturns value of the first column from the first row.
NotFoundError if query returns no rows.DataIntegrityError if query returns multiple rows.DataIntegrityError if query returns multiple columns.Example:
const foo = await connection.oneFirst(sql`SELECT foo`);
// foo is the result of the `foo` column value of the first row.
queryAPI and the result shape are equivalent to pg#query.
transactiontransaction method is used wrap execution of queries in START TRANSACTION and COMMIT or ROLLBACK. COMMIT is called if the transaction handler returns a promise that resolves; ROLLBACK is called otherwise.
transaction method can be used together with createPool method. When used to create a transaction from an instance of a pool, a new connection is allocated for the duration of the transaction.
const result = await connection.transaction(async (transactionConnection) => {
await transactionConnection.query(sql`INSERT INTO foo (bar) VALUES ('baz')`);
await transactionConnection.query(sql`INSERT INTO qux (quux) VALUES ('quuz')`);
return 'FOO';
});
result === 'FOO';
All Slonik errors extend from SlonikError, i.e. You can catch Slonik specific errors using the following logic.
import {
SlonikError
} from 'slonik';
try {
await query();
} catch (error) {
if (error instanceof SlonikError) {
// This error is thrown by Slonik.
}
}
NotFoundErrorTo handle the case where query returns less than one row, catch NotFoundError error.
import {
NotFoundError
} from 'slonik';
let row;
try {
row = await connection.one(sql`SELECT foo`);
} catch (error) {
if (!(error instanceof NotFoundError)) {
throw error;
}
}
if (row) {
// row.foo is the result of the `foo` column value of the first row.
}
DataIntegrityErrorTo handle the case where the data result does not match the expectations, catch DataIntegrityError error.
import {
NotFoundError
} from 'slonik';
let row;
try {
row = await connection.one(sql`SELECT foo`);
} catch (error) {
if (error instanceof DataIntegrityError) {
console.error('There is more than one row matching the select criteria.');
} else {
throw error;
}
}
NotNullIntegrityConstraintViolationErrorNotNullIntegrityConstraintViolationError is thrown when Postgres responds with unique_violation (23502) error.
ForeignKeyIntegrityConstraintViolationErrorForeignKeyIntegrityConstraintViolationError is thrown when Postgres responds with unique_violation (23503) error.
UniqueIntegrityConstraintViolationErrorUniqueIntegrityConstraintViolationError is thrown when Postgres responds with unique_violation (23505) error.
CheckIntegrityConstraintViolationErrorCheckIntegrityConstraintViolationError is thrown when Postgres responds with unique_violation (23514) error.
This package is using Flow types.
Refer to ./src/types.js.
The public interface exports the following types:
DatabaseConnectionTypeDatabasePoolConnectionTypeDatabaseSingleConnectionTypeUse these types to annotate connection instance in your code base, e.g.
// @flow
import type {
DatabaseConnectionType
} from 'slonik';
export default async (
connection: DatabaseConnectionType,
code: string
): Promise<number> => {
const countryId = await connection.oneFirst(sql`
SELECT id
FROM country
WHERE code = ${code}
`);
return countryId;
};
Slonik uses roarr to log queries.
To enable logging, define ROARR_LOG=true environment variable.
By default, Slonik logs the input query, query execution time and affected row count.
You can enable additional logging details by configuring the following environment variables.
# Logs query parameter values
export SLONIK_LOG_VALUES=true
# Logs normalised query and input values
export SLONIK_LOG_NORMALISED=true
SLONIK_LOG_STACK_TRACE=1 will create a stack trace before invoking the query and include the stack trace in the logs, e.g.
{"context":{"package":"slonik","namespace":"slonik","logLevel":20,"executionTime":"357 ms","queryId":"01CV2V5S4H57KCYFFBS0BJ8K7E","rowCount":1,"sql":"SELECT schedule_cinema_data_task();","stackTrace":["/Users/gajus/Documents/dev/applaudience/data-management-program/node_modules/slonik/dist:162:28","/Users/gajus/Documents/dev/applaudience/data-management-program/node_modules/slonik/dist:314:12","/Users/gajus/Documents/dev/applaudience/data-management-program/node_modules/slonik/dist:361:20","/Users/gajus/Documents/dev/applaudience/data-management-program/node_modules/slonik/dist/utilities:17:13","/Users/gajus/Documents/dev/applaudience/data-management-program/src/bin/commands/do-cinema-data-tasks.js:59:21","/Users/gajus/Documents/dev/applaudience/data-management-program/src/bin/commands/do-cinema-data-tasks.js:590:45","internal/process/next_tick.js:68:7"],"values":[]},"message":"query","sequence":4,"time":1540915127833,"version":"1.0.0"}
{"context":{"package":"slonik","namespace":"slonik","logLevel":20,"executionTime":"66 ms","queryId":"01CV2V5SGS0WHJX4GJN09Z3MTB","rowCount":1,"sql":"SELECT cinema_id \"cinemaId\", target_data \"targetData\" FROM cinema_data_task WHERE id = ?","stackTrace":["/Users/gajus/Documents/dev/applaudience/data-management-program/node_modules/slonik/dist:162:28","/Users/gajus/Documents/dev/applaudience/data-management-program/node_modules/slonik/dist:285:12","/Users/gajus/Documents/dev/applaudience/data-management-program/node_modules/slonik/dist/utilities:17:13","/Users/gajus/Documents/dev/applaudience/data-management-program/src/bin/commands/do-cinema-data-tasks.js:603:26","internal/process/next_tick.js:68:7"],"values":[17953947]},"message":"query","sequence":5,"time":1540915127902,"version":"1.0.0"}
Use @roarr/cli to pretty-print the output.
Using Atom IDE you can leverage the language-babel package in combination with the language-sql to enable highlighting of the SQL strings in the codebase.
To enable highlighting, you need to:
language-babel and language-sql packages.language-babel "JavaScript Tagged Template Literal Grammar Extensions" setting to use language-sql to highlight template literals with sql tag (configuration value: sql:source.sql).sql helper to construct the queries.For more information, refer to the JavaScript Tagged Template Literal Grammar Extensions documentation of language-babel package.
FAQs
A Node.js PostgreSQL client with strict types, detailed logging and assertions.
The npm package slonik receives a total of 50,230 weekly downloads. As such, slonik popularity was classified as popular.
We found that slonik demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Git dependencies in open source packages can introduce significant risks, including lack of version control, stability issues, dependency drift, and difficulty in auditing, making them potential targets for supply chain attacks.
Security News
Node.js has added experimental support for TypeScript, a move that highlights the growing importance of TypeScript in modern development.
Product
Check out what's new at Socket with our Product Changelog. It tracks all public-facing updates, improvements, and fixes so you can take full advantage of our features.