A PostgreSQL client with strict types and assertions.
npm install mightyql
Deprecated in favour https://www.npmjs.com/package/slonik.
A PostgreSQL client with strict types and assertions.
* Usage
* Conventions
* No multiline values
* Value placeholders
* A value set
* Multiple value sets
* Named placeholders
* Tagged template literals
* Guarding against accidental unescaped input
* Query methods
* any
* anyFirst
* insert
* many
* manyFirst
* maybeOne
* maybeOneFirst
* one
* oneFirst
* query
* transaction
* Overriding Error Constructor
* Error handling
* Handling NotFoundError
* Handling DataIntegrityError
* Handling UniqueViolationError
* Utilities
* Types
* Debugging
* Syntax highlighting
* Atom
``js
import {
createPool
} from 'mightyql';
const connection = createPool({
host: '127.0.0.1'
});
await connection.query('SELECT 1');
`
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.
`sqlINSERT INTO foo (bar) VALUES ('\n')
// Do not do this
connection.query();
`
If you want to communicate a value that includes a multiline character, use value placeholder interpolation, e.g.
`sqlINSERT INTO foo (bar) VALUES (?)
connection.query(, [
'\n'
]);
`
Mightyql enables use of question mark (?) value placeholders, e.g.
`js
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:
`sql`
SELECT $1
> Do not mix question mark and positional value placeholders in a single query.
A question mark is interpolated into a value set when the associated value is an array, e.g.
`js
await connection.query('SELECT ?', [
[
1,
2,
3
]
]);
`
Produces:
`sql
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.
`js
await connection.query('SELECT ?', [
[
[
1,
2,
3
],
[
1,
2,
3
]
]
]);
`
Produces:
`sql
SELECT ($1, $2, $3), ($4, $5, $6)
`
A :[a-zA-Z] regex is used to match named placeholders.
`js
await connection.query('SELECT :foo', {
foo: 'FOO'
});
`
Produces:
`sql
SELECT $1
`
Query methods can be executed using sql tagged template literal, e.g.
`js
import {
sql
} from 'mightyql'
connection.query(sqlINSERT 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:
`js
connection.query('INSERT INTO reservation_ticket (reservation_id, ticket_id) VALUES ?', [
values
]);
`
#### Guarding against accidental unescaped input
When using tagged template literals, it is easy to forget to add the sql tag, i.e.
Instead of:
`jsINSERT INTO reservation_ticket (reservation_id, ticket_id) VALUES ${values}
connection.query(sql);
`
Writing
`jsINSERT INTO reservation_ticket (reservation_id, ticket_id) VALUES ${values}
connection.query();
`
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.
Returns result rows.
> Similar to #query except that it returns rows without fields information.
Example:
`js
const rows = await connection.any('SELECT foo');
`
Returns value of the first column of every row in the result set.
* Throws DataIntegrityError if query returns multiple rows.
Example:
`js
const fooValues = await connection.any('SELECT foo');
`
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 is InsertResultType.
Example:
`js
const {
insertId
} = await connection.insert('INSERT INTO foo SET bar="baz"');
`
Returns result rows.
* Throws NotFoundError if query returns no rows.
Example:
`js
const rows = await connection.many('SELECT foo');
`
Returns value of the first column of every row in the result set.
* Throws NotFoundError if query returns no rows.DataIntegrityError
* Throws if query returns multiple rows.
Example:
`js
const fooValues = await connection.many('SELECT foo');
`
Selects the first row from the result.
* Returns null if row is not found.DataIntegrityError
* Throws if query returns multiple rows.
Example:
`js
const row = await connection.maybeOne('SELECT foo');
// row.foo is the result of the foo column value of the first row.
`
Returns value of the first column from the first row.
* Returns null if row is not found.DataIntegrityError
* Throws if query returns multiple rows.DataIntegrityError
* Throws if query returns multiple columns.
Example:
`js
const foo = await connection.maybeOneFirst('SELECT foo');
// foo is the result of the foo column value of the first row.
`
Selects the first row from the result.
* Throws NotFoundError if query returns no rows.DataIntegrityError
* Throws if query returns multiple rows.
Example:
`js
const row = await connection.one('SELECT foo');
// 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.one
> Mightyql adds assertions about the result of the query.
Returns value of the first column from the first row.
* Throws NotFoundError if query returns no rows.DataIntegrityError
* Throws if query returns multiple rows.DataIntegrityError
* Throws if query returns multiple columns.
Example:
`js
const foo = await connection.oneFirst('SELECT foo');
// foo is the result of the foo column value of the first row.
`
API and the result shape are equivalent to pg#query.
Overriding the error constructor used by Mightyql allows you to map database layer errors to your application errors.
`js
import {
createPool
} from 'mightyql';
class NotFoundError extends Error {};
createPool('postgres://', {
errors: {
NotFoundError
}
});
`
The following error types can be overridden:
* NotFoundError
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.
`jsINSERT INTO foo (bar) VALUES ('baz')
const result = await connection.transaction(async (transactionConnection) => {
transactionConnection.query();INSERT INTO qux (quux) VALUES ('quuz')
transactionConnection.query();
return 'FOO';
});
result === 'FOO';
`
To handle the case where query returns less than one row, catch NotFoundError error.
`js
import {
NotFoundError
} from 'mightyql';
let row;
try {
row = await connection.one('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.
}
`
To handle the case where the data result does not match the expectations, catch DataIntegrityError error.
`js
import {
NotFoundError
} from 'mightyql';
let row;
try {
row = await connection.one('SELECT foo');
} catch (error) {
if (error instanceof DataIntegrityError) {
console.error('There is more than one row matching the select criteria.');
} else {
throw error;
}
}
`
UniqueViolationError is thrown when Postgres responds with unique_violation (23505) error.
This package is using Flow types.
Refer to ./src/types.js.
The public interface exports the following types:
* DatabaseConnectionType
*
*
Use these types to annotate connection instance in your code base, e.g.
`js
// @flow
import type {
DatabaseConnectionType
} from 'mightyql';
export default async (
connection: DatabaseConnectionType,
code: string
): Promise
const row = await connection
.one('SELECT id FROM country WHERE code = ? LIMIT 2', [
code
]);
return Number(row.id);
};
`
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 WHERE id IN (1000223) +3msmovie
mightyql values [ 'movie', [ 1000223 ] ] +0ms
mightyql query execution time 28 ms +29ms
mightyql query returned 1 row(s) +0ms
mightyql query SELECT * FROM WHERE id IN (1000292) +3msmovie
mightyql values [ 'movie', [ 1000292 ] ] +0ms
mightyql query execution time 24 ms +25ms
mightyql query returned 1 row(s) +0ms
mightyql query SELECT * FROM WHERE id IN (1000220) +1ms`
mightyql values [ 'movie', [ 1000220 ] ] +0ms
mightyql query execution time 26 ms +27ms
mightyql query returned 1 row(s) +0ms
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:
1. Install language-babel and language-sql packages.language-babel
1. Configure "JavaScript Tagged Template Literal Grammar Extensions" setting to use language-sql to highlight template literals with sql tag (configuration value: sql:source.sql).sql
1. Use helper to construct the queries.
For more information, refer to the JavaScript Tagged Template Literal Grammar Extensions documentation of language-babel` package.