# How `pnpm test:db` Spins Up an Ephemeral PostgreSQL 15 Container to Run RLS Cross-Tenant Invariants

> Learn how pnpm test:db spins up an ephemeral PostgreSQL 15 container using Docker to test Row-Level Security policies and prevent cross-tenant data leakage with Vitest.

- Repository: [Rafael Melgaço/DeskcommCRM](https://github.com/melgarafael/DeskcommCRM)
- Tags: internals
- Published: 2026-09-13

---

**The `pnpm test:db` command executes [`scripts/test-db.sh`](https://github.com/melgarafael/DeskcommCRM/blob/main/scripts/test-db.sh) to launch a disposable `pgvector/pgvector:pg15` Docker container, apply the Supabase baseline schema with idempotent migrations, and run isolated Vitest suites that verify Row-Level Security policies prevent cross-tenant data leakage.**

In the **melgarafael/DeskcommCRM** repository, the `pnpm test:db` task provides a deterministic way to validate multi-tenant data isolation. This command orchestrates a short-lived PostgreSQL 15 environment that mirrors production Supabase semantics, ensuring Row-Level Security (RLS) invariants hold under clean, reproducible conditions.

## Orchestration via scripts/test-db.sh

The entire lifecycle begins in [`scripts/test-db.sh`](https://github.com/melgarafael/DeskcommCRM/blob/main/scripts/test-db.sh), a Bash script that handles Docker container management, schema seeding, and test invocation. It acts as the single entry point that transforms a bare PostgreSQL image into a fully configured Supabase-compatible instance.

### Launching the Ephemeral pg15 Container

The script pulls and starts the `pgvector/pgvector:pg15` image, pinning the PostgreSQL version to match the production baseline. It applies Docker labels to distinguish concurrent runs by worktree and branch, then exposes the container’s internal port 5432 on an ephemeral host port allocated by Docker.

```bash
docker run -d --rm --name "$CONTAINER" \
  -p "$PUBLICACAO" \
  --label "deskcomm.harness=test-db" \
  --label "deskcomm.worktree=$DONO_WORKTREE" \
  --label "deskcomm.branch=$DONO_BRANCH" \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=postgres \
  "$IMAGE" >/dev/null

```

### Dynamic Port Discovery and Environment Export

Rather than hard-coding ports, the script lets Docker assign a free port and extracts the mapping using `docker port`. This value is exported as `TEST_DB_PORT`, allowing the Vitest process to connect without configuration conflicts.

```bash
docker exec "$CONTAINER" psql -U postgres -d postgres -q -c "create database $TEMPLATE"
PORT="$(docker port "$CONTAINER" 5432/tcp | head -1 | sed 's/.*://')"
export TEST_DB_PORT="$PORT"

```

## Database Template Strategy for Test Isolation

To guarantee that each test file receives a pristine database state, the script creates a **template database** named `inv_baseline` immediately after container startup. This template serves as the golden master for all invariant tests.

### Installing Supabase Stubs and Extensions

Because the `pgvector` image lacks Supabase-specific objects, the script injects required roles (`anon`, `authenticated`, `service_role`), schemas (`auth`, `extensions`, `storage`), and extensions via a heredoc executed inside the container. This stubs the environment so that [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql) can execute without errors.

### Idempotent Baseline Application

The script applies [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql) twice with `ON_ERROR_STOP=1`. The first run performs the initial installation; the second run validates idempotency, ensuring that migration scripts can be reapplied safely without raising errors or causing drift.

## Executing RLS Cross-Tenant Invariants with Vitest

Once the template database is ready, the script invokes Vitest using the dedicated configuration file [`vitest.db.config.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/vitest.db.config.ts).

### Configuration for Serial Execution and Isolation

The Vitest configuration targets only `tests/invariants/**/*.test.ts`, disables parallel execution via `fileParallelism: false`, and specifies [`tests/db/banco-limpo-por-arquivo.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/db/banco-limpo-por-arquivo.ts) as a setup file. This setup file clones the `inv_baseline` template for each test file, ensuring complete isolation between cross-tenant security checks.

```typescript
export default defineConfig({
  test: {
    environment: "node",
    include: ["tests/invariants/**/*.test.ts"],
    fileParallelism: false,
    setupFiles: ["./tests/db/banco-limpo-por-arquivo.ts"],
    // …
  },
});

```

### Validating Row-Level Security Policies

Individual invariant tests (such as those in [`tests/invariants/rls-isolation.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/invariants/rls-isolation.test.ts)) connect to the temporary database and assert that RLS policies correctly filter rows by `organization_id`. By simulating queries from different tenant contexts within isolated database clones, the suite reliably detects regressions in multi-tenant security boundaries.

```typescript
import { expect, test } from "vitest";
import { supabase } from "@/lib/supabase/server";

test("RLS isolates tenants", async () => {
  // Insert a row for organization 1
  await supabase.from("contacts").insert({ organization_id: 1, name: "Alice" });

  // Query as organization 2 – should return empty
  const { data } = await supabase
    .rpc("set_current_organization", { org_id: 2 })
    .from("contacts")
    .select("*");
  expect(data).toHaveLength(0);
});

```

## Summary

- [`scripts/test-db.sh`](https://github.com/melgarafael/DeskcommCRM/blob/main/scripts/test-db.sh) orchestrates the ephemeral container lifecycle and schema seeding.
- The `pgvector/pgvector:pg15` image provides a production-matching PostgreSQL 15 environment with dynamic port binding.
- A template database (`inv_baseline`) ensures each test file receives an isolated, pristine schema copy.
- Supabase-specific roles and extensions are injected to support the baseline SQL execution.
- [`vitest.db.config.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/vitest.db.config.ts) configures serial execution against `tests/invariants/**/*.test.ts` to prevent cross-test contamination.
- RLS cross-tenant invariants verify that tenants cannot access each other’s data, validating the multi-tenant security model.

## Frequently Asked Questions

### What Docker image does `pnpm test:db` use?

The command uses the `pgvector/pgvector:pg15` image to ensure PostgreSQL 15 compatibility with the production Supabase environment while including the pgvector extension required by the application.

### How does the test suite ensure database isolation between test files?

The [`banco-limpo-por-arquivo.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/banco-limpo-por-arquivo.ts) setup file clones the `inv_baseline` template database for each test file, guaranteeing that RLS invariants run against a fresh schema copy without interference from previous tests.

### Why is [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql) applied twice during the test setup?

The baseline is applied twice to enforce idempotency; if the second run produces errors or unexpected changes, it indicates that the migration scripts are not safely repeatable in production scenarios.

### Can the database tests run in parallel?

No, `fileParallelism: false` is explicitly set in [`vitest.db.config.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/vitest.db.config.ts) to prevent concurrent access issues, though file-level isolation via database cloning allows safe serial execution of cross-tenant security checks.