Mightyql
Deprecated in favour https://www.npmjs.com/package/slonik.
A PostgreSQL client with strict types and assertions.
- Usage
- Conventions
- Value placeholders
- Query methods
- Overriding Error Constructor
- Error handling
- Utilities
- Types
- Debugging
- Syntax highlighting
Usage
; const connection = ; await connection;
Conventions
No multiline values
Mightyql 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 thisconnection.query(`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(`INSERT INTO foo (bar) VALUES (?)`, [ '\n']);
Value placeholders
Mightyql enables use of question mark (?
) value placeholders, e.g.
await connection;
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
Do not mix question mark and positional value placeholders in a single query.
A value set
A question mark is interpolated into a value set when the associated value is an array, e.g.
await connection;
Produces:
SELECT ($1, $2, $3)
Multiple value sets
A question mark is interpolated into a list of value sets when the associated value is an array of arrays, e.g.
await connection;
Produces:
SELECT ($1, $2, $3), ($4, $5, $6)
Named placeholders
A :[a-zA-Z]
regex is used to match named placeholders.
await connection;
Produces:
SELECT $1
Tagged template literals
Query methods can be executed using sql
tagged template literal, e.g.
connection;
Arguments of a tagged template literal invocation are replaced with an anonymous value placeholder, i.e. the latter query is equivalent to:
connection;
Guarding against accidental unescaped input
When using tagged template literals, it is easy to forget to add the sql
tag, i.e.
Instead of:
connection;
Writing
connection;
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.
Query methods
any
Returns result rows.
Similar to
#query
except that it returns rows without fields information.
Example:
const rows = await connection;
anyFirst
Returns value of the first column of every row in the result set.
- Throws
DataIntegrityError
if query returns multiple rows.
Example:
const fooValues = await connection;
insert
Designed to use when inserting 1 row.
The reason for using this method over
#query
is to leverage the strict types.#insert
method result type isInsertResultType
.
Example:
const insertId } = await connection;
many
Returns result rows.
- Throws
NotFoundError
if query returns no rows.
Example:
const rows = await connection;
manyFirst
Returns value of the first column of every row in the result set.
- Throws
NotFoundError
if query returns no rows. - Throws
DataIntegrityError
if query returns multiple rows.
Example:
const fooValues = await connection;
maybeOne
Selects the first row from the result.
- Returns
null
if row is not found. - Throws
DataIntegrityError
if query returns multiple rows.
Example:
const row = await connection; // row.foo is the result of the `foo` column value of the first row.
maybeOneFirst
Returns value of the first column from the first row.
- Returns
null
if row is not found. - Throws
DataIntegrityError
if query returns multiple rows. - Throws
DataIntegrityError
if query returns multiple columns.
Example:
const foo = await connection; // foo is the result of the `foo` column value of the first row.
one
Selects the first row from the result.
- Throws
NotFoundError
if query returns no rows. - Throws
DataIntegrityError
if query returns multiple rows.
Example:
const row = await connection; // row.foo is the result of the `foo` column value of the first row.
Note:
I've got asked "How is this different from knex.js
knex('foo').limit(1)
".knex('foo').limit(1)
simply generates "SELECT * FROM foo LIMIT 1" query.knex
is a query builder; it does not assert the value of the result. Mightyqlone
adds assertions about the result of the query.
oneFirst
Returns value of the first column from the first row.
- Throws
NotFoundError
if query returns no rows. - Throws
DataIntegrityError
if query returns multiple rows. - Throws
DataIntegrityError
if query returns multiple columns.
Example:
const foo = await connection; // foo is the result of the `foo` column value of the first row.
query
API and the result shape are equivalent to pg#query
.
Overriding Error Constructor
Overriding the error constructor used by Mightyql allows you to map database layer errors to your application errors.
; {}; ;
The following error types can be overridden:
NotFoundError
transaction
transaction
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; result === 'FOO';
Error handling
NotFoundError
Handling To handle the case where query returns less than one row, catch NotFoundError
error.
; let row; try row = await connection; catch error if !error instanceof NotFoundError throw error; if row // row.foo is the result of the `foo` column value of the first row.
DataIntegrityError
Handling To handle the case where the data result does not match the expectations, catch DataIntegrityError
error.
; let row; try row = await connection; catch error if error instanceof DataIntegrityError console; else throw error;
UniqueViolationError
Handling UniqueViolationError
is thrown when Postgres responds with unique_violation
(23505
) error.
Types
This package is using Flow types.
Refer to ./src/types.js
.
The public interface exports the following types:
DatabaseConnectionType
DatabasePoolConnectionType
DatabaseSingleConnectionType
Use these types to annotate connection
instance in your code base, e.g.
// @flow ; connection: DatabaseConnectionType code: string: Promise<number> const row = await connection ; return Numberrowid;;
Debugging
Define DEBUG=mightyql*
environment variable to enable logging.
Logging includes information about:
- the query thats about to be executed
- placeholder values
- the execution time
- the number of result rows
Here is the output example:
mightyql query execution time 196 ms +199ms
mightyql query returned 4 row(s) +0ms
mightyql query SELECT * FROM `movie` WHERE id IN (1000223) +3ms
mightyql values [ 'movie', [ 1000223 ] ] +0ms
mightyql query execution time 28 ms +29ms
mightyql query returned 1 row(s) +0ms
mightyql query SELECT * FROM `movie` WHERE id IN (1000292) +3ms
mightyql values [ 'movie', [ 1000292 ] ] +0ms
mightyql query execution time 24 ms +25ms
mightyql query returned 1 row(s) +0ms
mightyql query SELECT * FROM `movie` WHERE id IN (1000220) +1ms
mightyql values [ 'movie', [ 1000220 ] ] +0ms
mightyql query execution time 26 ms +27ms
mightyql query returned 1 row(s) +0ms
Syntax highlighting
Atom
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:
- Install
language-babel
andlanguage-sql
packages. - Configure
language-babel
"JavaScript Tagged Template Literal Grammar Extensions" setting to uselanguage-sql
to highlight template literals withsql
tag (configuration value:sql:source.sql
). - Use
sql
helper to construct the queries.
For more information, refer to the JavaScript Tagged Template Literal Grammar Extensions documentation of language-babel
package.