@withcardinal/sql v3.0.3

cardinal-sql

SQL is our database interaction library at Cardinal. It is mostly a light wrapper around the pg module.

Configuration

Cardinal's SQL library relies on the DATABASE_URL environment variable for defining the database to connect to. We have not yet run into a need for other configuration options.

Additionally, the library allows a DATABASE_THROW_ON_QUERY environment variable that will, as the name implies, throw immediately if a query is executed. This is helpful in cases where you're mocking or faking all queries in test.

You can also specify DATABASE_TRACE with a value of true to output a log line each time a query is run.

API

SQLquery

Defines a new sql query. This is a tagged template function, so you can include javascript interpolated statements and the library will handle them appropriately and securely. Behind the scenes SQL generates parameterized queries.

SQL also supports injecting the result of other SQL statements into a SQL statement.

Example

const id = 1;
const q = SQL`SELECT * FROM people WHERE id = ${id}`;

const tableName = SQL`people`;
const q2 = SQL`SELECT * FROM ${tableName} WHERE id = ${id}`;

query(sql, tx = null)

query returns all rows from a query. If tx is provided, query is run against that transaction.

Example

const teamName = "Super Team";
const users = await query(SQL`SELECT * FROM users WHERE team_name = ${teamName}`);

queryOne(sql, tx = null)

queryOne returns a single row from a query. If tx is provided, query is run against that transaction.

Example

const id = 12;
const user = await queryOne(SQL`SELECT * FROM users WHERE id = ${id}`);

transaction(txFn)

transaction contains a transaction. It takes a function to execute against the transaction. If any uncaught exceptions arise during the transaction, the transaction is rolled back. Otherwise the transaction is applied.

txFn is provided the transaction object from the database driver and a rollback function. You can pass the transaction object to queryOne and query, or any defined queries, and it will be used instead of a default connection. Once txFn completes, the transaction will be committed.

Example

transaction(async (tx, rollback) => {
  const id = 12;
  await query(SQL`UPDATE users SET name = 'Jane Doe' WHERE id = ${id}`, tx);

  rollback();
});

startTestTx()

startTestTx starts a global transaction within a test. It's meant to be run non-concurrently with other tests, but will allow your test to isolate its results into a transaction.

Example

beforeEach(startTestTx);

endTestTx

endTestTx ends a global transaction within a test which was started with startTestTx.

Example

beforeEach(startTextTx);
afterEach(endTestTx);

Command Line Tools

This package also includes two command line tools, cardinal-migrate and cardinal-sql.

cardinal-migrate

cardinal-migrate is a very simple migration tool. It expects a directory with files ordered with names of the following format 00to01-[migration-name].sql. Running cardinal-migrate [dirname] will read all migration files from that directory and run them in order. It creates a migrations table in the database. Like the SQL library itself, cardinal-migrate expects a DATABASE_URL environment variable to be set.

Examples

cardinal-migrate migrations/

cardinal-migrate --dry-run migrations/

cardinal-sql

cardinal-sql runs sql files as arguments or from stdin.

Examples

cardinal-sql script1.sql

echo "SELECT * FROM users" | cardinal-sql

Changelog

We use GitHub releases to track each release, and include notes about the releases there.

Releases

License

Licensed under the MIT license. See LICENSE for more information.