# Test the database

A database test runs SQL against a local Postgres that Testkit builds from your schema. Use it for constraints, queries, and anything else the database decides.

## Before you start

Start Docker. Testkit runs Postgres in a container. It runs `psql` and `pg_dump` in a container too, so you do not need Postgres on the host.

## Declare the database

Add `database.postgres(...)` to the service that owns the schema.

```ts title=testkit.config.ts
import { app, database, defineConfig } from "@gfdelarue/testkit/config";

export default defineConfig({
	services: {
		api: app.node({
			build: null,
			start: "npm start",
			port: 4392,
			database: database.postgres({
				template: {
					inputs: ["db/schema.sql"],
					migrate: [{ kind: "sql-file", path: "db/schema.sql" }],
				},
			}),
		}),
	},
});
```

```sql title=db/schema.sql
CREATE TABLE refunds (
	id text PRIMARY KEY,
	order_id text NOT NULL UNIQUE,
	amount_cents integer NOT NULL
);
```

Testkit builds a template database from the `migrate` steps and caches it. It rebuilds the template when a file in `inputs` changes.

The service process receives its database as `DATABASE_URL`.

## Write the test

```ts title=src/db/__testkit__/refunds.dal.testkit.ts
import { defineFile } from "@gfdelarue/testkit";
import { defineDalSuite, expect } from "@gfdelarue/testkit/k6";

export const testkit = defineFile({ engine: "k6" });

const suite = defineDalSuite(({ db }) => {
	db.exec("INSERT INTO refunds (id, order_id, amount_cents) VALUES ('r1', '8841', 1200)");
	const error = expect.captureError(() => {
		db.exec("INSERT INTO refunds (id, order_id, amount_cents) VALUES ('r2', '8841', 1200)");
	});
	expect.error.captured(
		error,
		(caught) => String(caught).includes("refunds_order_id_key"),
		"the database rejects a second refund",
	);
	const rows = db.query("SELECT id FROM refunds WHERE order_id = '8841'");
	expect.rowCount(rows, 1, "one refund is stored");
});

export const options = suite.options;
export const setup = suite.setup;
export default suite.exec;
```

`db.exec(sql)` runs a statement. `db.query(sql)` returns the rows.

`expect.captureError` runs a function and returns what it threw. `expect.error.captured` takes that error, a predicate, and a label. The predicate must be a function.

The last three lines are required, as in an [HTTP test](https://turnstone.cloud/docs/http-tests.md).

## Run it

```console $ npx testkit run dal
[1/1] PASS api dal src/db/__testkit__/refunds.dal.testkit.ts 0s

Summary
Result: PASSED
Passed: 1
Failed: 0
Skipped: 0
Not run: 0
Files: 1
Duration: 4s
Run ID: 1791890158174-1344714-af8dd695
```

By default, Testkit runs the `dal` files for one service database one at a time.

## Read a failure

When an `expect` helper fails, the run prints what it expected and what it got.

```console $ npx testkit run dal
[1/1] FAIL api dal src/db/__testkit__/refunds.dal.testkit.ts 0s
    k6 thresholds failed: checks(rate==1.0)
    expected 2 row(s), got 1
```

| Helper | Passes when |
| --- | --- |
| `expect.rowCount(rows, n, label)` | The query returned `n` rows |
| `expect.singleRow(rows, label)` | The query returned one row. It returns that row. |
| `expect.noRows(rows, label)` | The query returned no rows |
| `expect.error.captured(error, predicate, label)` | The predicate accepts the captured error |

## Seed rows with fixtures

`defineDalFixtures` gives a file named fixtures. Each fixture seeds its rows once and returns the values the test needs.

```ts title=src/db/__testkit__/seeded.dal.testkit.ts
import { defineFile } from "@gfdelarue/testkit";
import { defineDalFixtures, defineDalSuite, expect } from "@gfdelarue/testkit/k6";

export const testkit = defineFile({ engine: "k6" });

const fixtures = defineDalFixtures(({ db, fixtureScope }) => ({
	refund() {
		return fixtureScope.seed("refund", "primary", { amountCents: 1200 }, () => {
			const refundId = fixtureScope.id("refund");
			const orderId = fixtureScope.id("order");
			db.exec(`
				INSERT INTO refunds (id, order_id, amount_cents)
				VALUES ('${refundId}', '${orderId}', 1200)
			`);
			return { refundId };
		});
	},
}));

const suite = defineDalSuite({ fixtures }, ({ db, fixtures }) => {
	const refund = fixtures.refund();
	const rows = db.query(`SELECT amount_cents FROM refunds WHERE id = '${refund.refundId}'`);
	expect.rowCount(rows, 1, "the seeded refund is stored");
});

export const options = suite.options;
export const setup = suite.setup;
export default suite.exec;
```

`fixtureScope.seed(kind, key, signature, create)` identifies a fixture by its `kind` and `key`.

- A second call with the same `kind`, `key`, and `signature` returns the first call's value.
- A second call with a different `signature` fails the file.
- Testkit saves the seeded records with the run as a `testkit.dal-fixtures` artifact.

`fixtureScope` also makes values that are stable for a label: `id(label)`, `uuid(label)`, `slug(label)`, `email(label)`, `string(label, options)`, and `token(label)`.

## Remove the database

`testkit destroy` removes the Postgres container of each service. It keeps `.testkit/results`, so earlier run records stay readable.

```console $ npx testkit destroy
Destroyed api
```

The next run that needs the database builds it again.