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

The pnpm test:db command executes 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, 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.

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.

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 can execute without errors.

Idempotent Baseline Application

The script applies 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.

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 as a setup file. This setup file clones the inv_baseline template for each test file, ensuring complete isolation between cross-tenant security checks.

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) 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.

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 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 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 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 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 to prevent concurrent access issues, though file-level isolation via database cloning allows safe serial execution of cross-tenant security checks.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}