@prairielearn/postgres

@prairielearn/postgres

Tools for loading and executing Postgres queries.

Usage

Before making any queries, you must initialize the library with your connection details and an error handler:

import sqldb from '@prairielearn/postgres';

function idleErrorHandler(err: any) {
  console.error(err);
  process.exit(1);
}

await sqldb.initAsync(
  {
    user: '...',
    database: '...',
    host: '...',
    password: '...',
    max: 2,
    idleTimeoutMillis: 30000,
    errorOnUnusedParameters: false, // defaults to false
  },
  idleErrorHandler,
);

The options argument accepts any values that the pg.Pool constructor does.

Loading queries from files

The recommended way to write queries is to store them in a .sql file adjacent to the file from which they'll be used. For instance, if we want to make some queries in an index.js file, we can put the following in index.sql:

-- BLOCK select_user
SELECT
  *
FROM
  users
WHERE
  id = $user_id;

-- BLOCK select_course
SELECT
  *
FROM
  courses
WHERE
  id = $course_id;

You can then load these queries in your JavaScript file:

import sqldb from '@prairielearn/postgres';
const sql = sqldb.loadSqlEquiv(import.meta.url);

console.log(sql.select_user);
console.log(sql.select_course);

Making queries

Once you've loaded your SQL, you can use them to query the database:

import sqldb from '@prairielearn/postgres';
const sql = sqldb.loadSqlEquiv(import.meta.url);

const result = await sqldb.queryAsync(sql.select_user, { user_id: '1' });
console.log(result.rows);

The queryAsync function returns a pg.Result object; see linked documentation for a list of additional properties that are available on that object.

There are also utility methods that can make assertions about the results:

• queryOneRowAsync: Throws an error if the result doesn't have exactly one row.
• queryZeroOrOneRowAsync: Throws an error if the result has more than one row.

Stored procedures (sprocs)

There are also functions that make it easy to call a stored procedure with a given set of arguments. Consider a database that has the following sproc defined:

CREATE PROCEDURE insert_data (a integer, b integer) LANGUAGE SQL
BEGIN ATOMIC
INSERT INTO
  tbl
VALUES
  (a);

INSERT INTO
  tbl
VALUES
  (b);

END;

You can call this sproc in your JavaScript code:

await sqldb.callAsync('insert_data', [1, 2]);

Zod validation

For increased safety and confidence, you can describe the shape of data you expect from the database with a Zod schema. You can then provide this schema when making a query, and the data returned from the database will be parsed with that schema.

import { z } from 'zod';
import { loadSqlEquiv, queryRows, queryRow, queryOptionalRow } from '@prairielearn/postgres';

const sql = loadSqlEquiv(import.meta.url);

const User = z.object({
  name: z.string(),
  email: z.string(),
  age: z.number(),
});

// Get all users. Returns an array of objects.
const users = await queryRows(sql.select_users, User);

// Get single user. Returns a single object.
const user = await queryRow(sql.select_user, { user_id: '1' }, User);

// Get a user that may not exist. Returns `null` if the user cannot be found.
const maybeUser = await queryOptionalRow(sql.select_user, { user_id: '1' }, User);

// Call a stored procedure. Return value is equivalent to the functions above.
const users = await callRows('select_users', User);
const user = await callRow('select_user', ['1'], User);
const maybeUser = await callOptionalRow('select_user', ['1'], User);

Passing an object or array with parameters is optional.

The schema passed to these functions must be a z.object(...) schema. Each row from the query result will be validated against the schema.

Scalar queries

For queries that return a single column, you can use the scalar variants. These accept any Zod schema (not just z.object(...)) and validate the individual column value.

import { z } from 'zod';
import {
  queryScalar,
  queryScalars,
  queryOptionalScalar,
  callScalar,
  callScalars,
  callOptionalScalar,
} from '@prairielearn/postgres';

// Get all user IDs. Returns an array of strings.
const userIds = await queryScalars(sql.select_user_ids, z.string());

// Get one user's name. Returns a string.
const name = await queryScalar(sql.select_user_name, { user_id: '1' }, z.string());

// Get a count that might be null. Returns a number or null.
const count = await queryOptionalScalar(sql.select_count, z.coerce.number());

// Equivalent sproc variants.
const ids = await callScalars('get_all_ids', z.string());
const id = await callScalar('get_id', [1], z.string());
const maybeId = await callOptionalScalar('get_id', [1], z.string());

These functions will throw an error at runtime if the query returns more than one column.

Transactions

To use transactions, wrap your queries with the runInTransactionAsync function:

const { user, course } = await sqldb.runInTransactionAsync(async () => {
  const user = await sqldb.queryAsync(sql.insert_user, { name: 'Kevin Young' });
  const course = await sqldb.queryAsync(sql.insert_course, { rubric: 'CS 101' });
  return { user, course };
});

runInTransaction will start a transaction and then execute the provided function. Any nested query will use the same client and thus run inside the transaction. If the function throws an error, the transaction is rolled back; otherwise, it is committed.

Cursors

For very large queries that don't need to fit in memory all at once, it's possible to use a cursor to read a limited number of rows at a time.

import { z } from 'zod';
import { queryCursor } from '@prairielearn/postgres';

const UserSchema = z.object({
  id: z.string(),
  name: z.string(),
});

const cursor = await queryCursor(sql.select_all_users, {}, UserSchema);
for await (const users of cursor.iterate(100)) {
  for (const user of users) {
    console.log(user);
  }
}

You can also use cursor.stream(...) to get an object stream, which can be useful for piping it somewhere else:

const cursor = await queryCursor(sql.select_all_users, UserSchema);
cursor.stream(100).pipe(makeStreamSomehow());

If you don't need to parse and validate each row with Zod, you can use z.unknown() as the schema:

const cursor = await queryCursor(sql.select_all_users, z.unknown());

Callback-style functions

For most functions that return promises, there are corresponding versions that work with Node-style callbacks:

sqldb.query(sql.select_user, (err, result) => {
  if (err) {
    console.error('Error running query', err);
  } else {
    console.log(result.rows);
  }
});

However, these should be avoided in new code:

• They make it more difficult to correctly handle errors
• Callback-style code tends to be more verbose and suffer from "callback hell"