Skip to main content
Version: 4.12.0

Testing with Jest

Testing the database

Making sure your database functions, triggers, permissions, etc work correctly is critical; there are many tools out there that you can use to do this, but if you're already developing in a JavaScript environment it may feel natural to add them to your Jest suite.

The pattern is effectively to spin up a transaction, set any relevant Postgres settings (e.g. role, jwt.claims.user_id, etc), run the SQL you want to test, check the results, and then rollback the transaction; i.e.:

-- start transaction
begin;

-- set relevant transaction settings; the `, true` means "local" - i.e. it'll
-- be rolled back with the transaction - do not forget to add this!
select
set_config('role', 'YOUR_GRAPHQL_ROLE_HERE', true),
set_config('jwt.claims.user_id', 27, true),
set_config...;

-- run the SQL you want to test
select * from my_function();

-- rollback the transaction
rollback;

Each of the statements above would normally have to be issued via pgClient.query(...) using a Postgres client that your retrieve from a pg.Pool (and release afterwards); however that's a lot of boilerplate for our tests, so it makes sense to extract the common patterns into helper functions.

(Click to expand.) Create some helpers in test_helpers.ts.

The following code is in TypeScript; you can convert it to JavaScript via https://www.typescriptlang.org/play

import { Pool, PoolClient } from "pg";

if (!process.env.TEST_DATABASE_URL) {
throw new Error("Cannot run tests without a TEST_DATABASE_URL");
}
export const TEST_DATABASE_URL: string = process.env.TEST_DATABASE_URL;

const pools = {};

// Make sure we release those pgPools so that our tests exit!
afterAll(() => {
const keys = Object.keys(pools);
return Promise.all(
keys.map(async (key) => {
try {
const pool = pools[key];
delete pools[key];
await pool.end();
} catch (e) {
console.error("Failed to release connection!");
console.error(e);
}
}),
);
});

const withDbFromUrl = async <T>(url: string, fn: ClientCallback<T>) => {
const pool = poolFromUrl(url);
const client = await pool.connect();
await client.query("BEGIN ISOLATION LEVEL SERIALIZABLE;");

try {
await fn(client);
} catch (e) {
// Error logging can be helpful:
if (typeof e.code === "string" && e.code.match(/^[0-9A-Z]{5}$/)) {
console.error([e.message, e.code, e.detail, e.hint, e.where].join("\n"));
}
throw e;
} finally {
await client.query("ROLLBACK;");
await client.query("RESET ALL;"); // Shouldn't be necessary, but just in case...
await client.release();
}
};

export const withRootDb = <T>(fn: ClientCallback<T>) =>
withDbFromUrl(TEST_DATABASE_URL, fn);

export const becomeRoot = (client: PoolClient) => client.query("reset role");

/******************************************************************************
** **
** BELOW HERE, YOU'LL WANT TO CUSTOMISE FOR YOUR OWN DATABASE SCHEMA **
** **
******************************************************************************/

export type User = {
id: string;
username: string;
_password?: string;
_email?: string;
};
export type Organization = { id: string; name: string };

export const becomeUser = async (
client: PoolClient,
userOrUserId: User | string | null,
) => {
await becomeRoot(client);
const session = userOrUserId
? await createSession(
client,
typeof userOrUserId === "object" ? userOrUserId.id : userOrUserId,
)
: null;
await client.query(
`select set_config('role', $1::text, true),
set_config('jwt.claims.session_id', $2::text, true)`,
[process.env.DATABASE_VISITOR, session ? session.uuid : ""],
);
};

// Enables multiple calls to `createUsers` within the same test to still have
// deterministic results without conflicts.
let userCreationCounter = 0;
beforeEach(() => {
userCreationCounter = 0;
});

export const createUsers = async function createUsers(
client: PoolClient,
count: number = 1,
verified: boolean = true,
) {
const users = [];
if (userCreationCounter > 25) {
throw new Error("Too many users created!");
}
for (let i = 0; i < count; i++) {
const userLetter = "abcdefghijklmnopqrstuvwxyz"[userCreationCounter];
userCreationCounter++;
const password = userLetter.repeat(12);
const email = `${userLetter}${i || ""}@b.c`;
const user: User = (
await client.query(
`SELECT * FROM app_private.really_create_user(
username := $1,
email := $2,
email_is_verified := $3,
name := $4,
avatar_url := $5,
password := $6
)`,
[
`testuser_${userLetter}`,
email,
verified,
`User ${userLetter}`,
null,
password,
],
)
).rows[0];
expect(user.id).not.toBeNull();
user._email = email;
user._password = password;
users.push(user);
}
return users;
};

Then a test file might look like:

import { becomeUser, createUsers, withRootDb } from "../test_helpers";

test("can delete self", () =>
withRootDb(async (pgClient) => {
const [user] = await createUsers(pgClient, 1);

await becomeUser(pgClient, user);
const {
rows: [deletedUser],
} = await pgClient.query(
"delete from app_public.users where id = $1 returning *",
[user.id],
);
expect(deletedUser).toBeTruthy();
}));

For more thorough test helpers (and to see this working in practice), check out Graphile Starter:

Testing the GraphQL middleware

Whereas testing the database functionality can be thought of as unit tests, testing the GraphQL middleware is more akin to integration tests - they pretend to go through the middleware (exercising pgSettings / JWT / etc) and you can place assertions on the results.

First, make sure that you've extracted your PostGraphile (library mode) options into a function that you can import in your tests; for example your PostGraphile server file might look like this:

const express = require("express");
const { postgraphile } = require("postgraphile");

const app = express();

function postgraphileOptions() {
return {
dynamicJson: true,
};
}
exports.postgraphileOptions = postgraphileOptions;

app.use(
postgraphile(
process.env.DATABASE_URL || "postgres:///",
["app_public"],
postgraphileOptions(),
),
);

app.listen(process.env.PORT || 3000);
(Click to expand.) Create a test_helper.ts file for running your queries, responsible for importing this postgraphileOptions function and setting up/tearing down the transaction. Don't forget to set the environment variables used by this file.

The following code is in TypeScript; you can convert it to JavaScript via https://www.typescriptlang.org/play

import { Request, Response } from "express";
import { ExecutionResult, graphql, GraphQLSchema } from "graphql";
import { Pool, PoolClient } from "pg";
import {
createPostGraphileSchema,
PostGraphileOptions,
withPostGraphileContext,
} from "postgraphile";

import { getPostGraphileOptions } from "../src/middleware/installPostGraphile";

const MockReq = require("mock-req");

let known: Record<string, { counter: number; values: Map<unknown, string> }> =
{};
beforeEach(() => {
known = {};
});
/*
* This function replaces values that are expected to change with static
* placeholders so that our snapshot testing doesn't throw an error
* every time we run the tests because time has ticked on in it's inevitable
* march toward the future.
*/
export function sanitize(json: any): any {
/* This allows us to maintain stable references whilst dealing with variable values */
function mask(value: unknown, type: string) {
if (!known[type]) {
known[type] = { counter: 0, values: new Map() };
}
const o = known[type];
if (!o.values.has(value)) {
o.values.set(value, `[${type}-${++o.counter}]`);
}
return o.values.get(value);
}

if (Array.isArray(json)) {
return json.map((val) => sanitize(val));
} else if (json && typeof json === "object") {
const result = { ...json };
for (const k in result) {
if (k === "nodeId" && typeof result[k] === "string") {
result[k] = mask(result[k], "nodeId");
} else if (
k === "id" ||
k === "uuid" ||
(k.endsWith("Id") &&
(typeof json[k] === "number" || typeof json[k] === "string")) ||
(k.endsWith("Uuid") && typeof k === "string")
) {
result[k] = mask(result[k], "id");
} else if (
(k.endsWith("At") || k === "datetime") &&
typeof json[k] === "string"
) {
result[k] = mask(result[k], "timestamp");
} else if (
k.match(/^deleted[A-Za-z0-9]+Id$/) &&
typeof json[k] === "string"
) {
result[k] = mask(result[k], "nodeId");
} else if (k === "email" && typeof json[k] === "string") {
result[k] = mask(result[k], "email");
} else if (k === "username" && typeof json[k] === "string") {
result[k] = mask(result[k], "username");
} else {
result[k] = sanitize(json[k]);
}
}
return result;
} else {
return json;
}
}

// Contains the PostGraphile schema and rootPgPool
interface ICtx {
rootPgPool: Pool;
options: PostGraphileOptions<Request, Response>;
schema: GraphQLSchema;
}
let ctx: ICtx | null = null;

export const setup = async () => {
const rootPgPool = new Pool({
connectionString: process.env.TEST_DATABASE_URL,
});

const options = getPostGraphileOptions({ rootPgPool });
const schema = await createPostGraphileSchema(
rootPgPool,
"app_public",
options,
);

// Store the context
ctx = {
rootPgPool,
options,
schema,
};
};

export const teardown = async () => {
try {
if (!ctx) {
return null;
}
const { rootPgPool } = ctx;
ctx = null;
rootPgPool.end();
return null;
} catch (e) {
console.error(e);
return null;
}
};

export const runGraphQLQuery = async function runGraphQLQuery(
query: string, // The GraphQL query string
variables: { [key: string]: any } | null, // The GraphQL variables
reqOptions: { [key: string]: any } | null, // Any additional items to set on `req` (e.g. `{user: {id: 17}}`)
checker: (
result: ExecutionResult,
context: { pgClient: PoolClient },
) => void | ExecutionResult | Promise<void | ExecutionResult> = () => {}, // Place test assertions in this function
) {
if (!ctx) throw new Error("No ctx!");
const { schema, rootPgPool, options } = ctx;
const req = new MockReq({
url: options.graphqlRoute || "/graphql",
method: "POST",
headers: {
Accept: "application/json",
"Content-Type": "application/json",
},
...reqOptions,
});
const res: any = { req };
req.res = res;

const {
pgSettings: pgSettingsGenerator,
additionalGraphQLContextFromRequest,
} = options;
const pgSettings =
(typeof pgSettingsGenerator === "function"
? await pgSettingsGenerator(req)
: pgSettingsGenerator) || {};

// Because we're connected as the database owner, we should manually switch to
// the authenticator role
if (!pgSettings.role) {
pgSettings.role = process.env.DATABASE_AUTHENTICATOR;
}

await withPostGraphileContext(
{
...options,
pgPool: rootPgPool,
pgSettings,
pgForceTransaction: true,
},
async (context) => {
let checkResult;
const { pgClient } = context;
try {
// This runs our GraphQL query, passing the replacement client
const additionalContext = additionalGraphQLContextFromRequest
? await additionalGraphQLContextFromRequest(req, res)
: null;
const result = await graphql(
schema,
query,
null,
{
...context,
...additionalContext,
__TESTING: true,
},
variables,
);
// Expand errors
if (result.errors) {
if (options.handleErrors) {
result.errors = options.handleErrors(result.errors);
} else {
// This does a similar transform that PostGraphile does to errors.
// It's not the same. Sorry.
result.errors = result.errors.map((rawErr) => {
const e = Object.create(rawErr);
Object.defineProperty(e, "originalError", {
value: rawErr.originalError,
enumerable: false,
});

if (e.originalError) {
Object.keys(e.originalError).forEach((k) => {
try {
e[k] = e.originalError[k];
} catch (err) {
// Meh.
}
});
}
return e;
});
}
}

// This is were we call the `checker` so you can do your assertions.
// Also note that we pass the `replacementPgClient` so that you can
// query the data in the database from within the transaction before it
// gets rolled back.
checkResult = await checker(result, {
pgClient,
});

// You don't have to keep this, I just like knowing when things change!
expect(sanitize(result)).toMatchSnapshot();

return checkResult == null ? result : checkResult;
} finally {
// Rollback the transaction so no changes are written to the DB - this
// makes our tests fairly deterministic.
await pgClient.query("rollback");
}
},
);
};

For more thorough test helpers (and to see this working in practice), check out Graphile Starter:

Your test might look something like this:

const { setup, teardown, runGraphQLQuery } = require("../test_helper");

beforeAll(setup);
afterAll(teardown);

test("GraphQL query nodeId", async () => {
await runGraphQLQuery(
// GraphQL query goes here:
`{ __typename }`,

// GraphQL variables go here:
{},

// Any additional properties you want `req` to have (e.g. if you're using
// `pgSettings`) go here:
{
// Assuming you're using Passport.js / pgSettings, you could pretend
// to be logged in by setting `req.user` to `{id: 17}`:
user: { id: 17 },
},

// This function runs all your test assertions:
async (json, { pgClient }) => {
expect(json.errors).toBeFalsy();
expect(json.data.__typename).toEqual("Query");

// If you need to, you can query the DB within the context of this
// function - e.g. to check that your mutation made the changes you'd
// expect.
const { rows } = await pgClient.query(
`SELECT * FROM app_public.users WHERE id = $1`,
[17],
);
if (rows.length !== 1) {
throw new Error("User not found!");
}
},
);
});