mightyql

4.6.2 • Public • Published

Mightyql

Travis build status Coveralls NPM version Canonical Code Style Twitter Follow

Deprecated in favour https://www.npmjs.com/package/slonik.

A PostgreSQL client with strict types and assertions.

Usage

import {
  createPool
} from 'mightyql';
 
const connection = createPool({
  host: '127.0.0.1'
});
 
await connection.query('SELECT 1');
 

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 this
connection.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.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

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.query('SELECT ?', [
  [
    1,
    2,
    3
  ]
]);
 

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.query('SELECT ?', [
  [
    [
      1,
      2,
      3
    ],
    [
      1,
      2,
      3
    ]
  ]
]);
 

Produces:

SELECT ($1, $2, $3), ($4, $5, $6)
 

Named placeholders

A :[a-zA-Z] regex is used to match named placeholders.

await connection.query('SELECT :foo', {
  foo: 'FOO'
});
 

Produces:

SELECT $1
 

Tagged template literals

Query methods can be executed using sql tagged template literal, e.g.

import {
  sql
} from 'mightyql'
 
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('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:

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.

Query methods

any

Returns result rows.

Similar to #query except that it returns rows without fields information.

Example:

const rows = await connection.any('SELECT foo');
 

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.any('SELECT foo');
 

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 is InsertResultType.

Example:

const {
  insertId 
= await connection.insert('INSERT INTO foo SET bar="baz"');
 

many

Returns result rows.

  • Throws NotFoundError if query returns no rows.

Example:

const rows = await connection.many('SELECT foo');
 

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.many('SELECT foo');
 

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.maybeOne('SELECT foo');
 
// 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.maybeOneFirst('SELECT foo');
 
// 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.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. Mightyql one 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.oneFirst('SELECT foo');
 
// 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.

import {
  createPool
} from 'mightyql';
 
class NotFoundError extends Error {};
 
createPool('postgres://', {
  errors: {
    NotFoundError
  }
});

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.transaction(async (transactionConnection) => {
  transactionConnection.query(`INSERT INTO foo (bar) VALUES ('baz')`);
  transactionConnection.query(`INSERT INTO qux (quux) VALUES ('quuz')`);
 
  return 'FOO';
});
 
result === 'FOO';
 

Error handling

Handling NotFoundError

To handle the case where query returns less than one row, catch NotFoundError error.

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.
}
 

Handling DataIntegrityError

To handle the case where the data result does not match the expectations, catch DataIntegrityError error.

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;
  }
}
 

Handling UniqueViolationError

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
 
import type {
  DatabaseConnectionType
} from 'mightyql';
 
export default async (
  connection: DatabaseConnectionType,
  code: string
): Promise<number> => {
  const row = await connection
    .one('SELECT id FROM country WHERE code = ? LIMIT 2', [
      code
    ]);
 
  return Number(row.id);
};
 

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:

  1. Install language-babel and language-sql packages.
  2. Configure 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).
  3. Use sql helper to construct the queries.

For more information, refer to the JavaScript Tagged Template Literal Grammar Extensions documentation of language-babel package.

Package Sidebar

Install

npm i mightyql

Weekly Downloads

2

Version

4.6.2

License

BSD-3-Clause

Last publish

Collaborators

  • gajus