Postgres / Neon

The Postgres store uses the postgres package with tagged-template SQL. It works with any standard PostgreSQL server and Postgres-compatible services including Neon, Railway, and Supabase direct connections.

Installation

bun add -D @flaky-tests/store-postgres
# or: npm install -D @flaky-tests/store-postgres

Setup

1. Get a Postgres connection string

   • Neon (free tier, serverless): create a project at neon.tech, copy the connection string
   • Railway: provision a Postgres plugin, copy the DATABASE_URL
   • Self-hosted: postgres://user:password@host:5432/dbname

2. Run migrations — the store creates tables automatically on first use:

   import { PostgresStore } from '@flaky-tests/store-postgres'
   
   const store = new PostgresStore({ connectionString: process.env.POSTGRES_URL! })
   await store.migrate()

3. Add to your environment

   POSTGRES_URL=postgres://user:password@host:5432/dbname

Usage

import { PostgresStore } from '@flaky-tests/store-postgres'

const store = new PostgresStore({
  connectionString: process.env.POSTGRES_URL!,
})
await store.migrate()

The CLI auto-runs migrate() before every query, so the explicit call above is only needed when using the store directly. Migrations are idempotent.

Constructor options

Provide either a connectionString or individual host/port/credential fields — not both.

interface PostgresStoreOptions {
  /** Full PostgreSQL connection string (mutually exclusive with host/port/etc) */
  connectionString?: string
  host?: string
  port?: number
  database?: string
  username?: string
  password?: string
  ssl?: boolean | 'require' | 'prefer' | 'allow'
  /** Prefix for `{prefix}_runs` / `{prefix}_failures` table names. Default: `flaky_test`. */
  tablePrefix?: string
  /** Retry tuning for read methods. Defaults: 3 attempts, 100ms base delay. */
  retry?: { attempts?: number; baseMs?: number }
}

Error handling, retries, and cancellation

Driver errors are wrapped in StoreError with cause preserved. Read methods auto-retry transient failures (network transport errors, HTTP 5xx) with exponential backoff and jitter; writes are not retried (no idempotency key). Read methods accept an optional signal?: AbortSignal — on abort the adapter calls postgres-js’s query.cancel() for server-side cancellation and rejects with the signal’s reason so callers see a uniform AbortError.

CLI usage

FLAKY_TESTS_STORE=postgres \
  POSTGRES_URL=postgres://... \
  bunx @flaky-tests/core

Pattern detection

The Postgres store uses COUNT(*) FILTER (WHERE ...) — a single-pass aggregate that’s more efficient than the Supabase two-fetch approach:

SELECT
  test_file,
  test_name,
  COUNT(*) FILTER (WHERE failed_at >= $recent_start) AS recent_fails,
  COUNT(*) FILTER (WHERE failed_at < $recent_start AND failed_at >= $prior_start) AS prior_fails
FROM failures
WHERE failed_at >= $prior_start
GROUP BY test_file, test_name
HAVING COUNT(*) FILTER (WHERE failed_at >= $recent_start) >= $threshold
  AND COUNT(*) FILTER (WHERE failed_at < $recent_start AND failed_at >= $prior_start) = 0

Using with Neon

Neon’s free tier includes 0.5 GB storage, 5 branches, and auto-suspend after inactivity (which may add cold-start latency on the first query after a pause).

# Neon connection string format
POSTGRES_URL=postgres://user:password@ep-something-12345.us-east-2.aws.neon.tech/neondb?sslmode=require

Tip

The ?sslmode=require parameter is important for Neon. The postgres package enables SSL automatically when it sees this parameter.

Using with Supabase direct connection

If you’re already on Supabase and want better query performance than the JS client provides, use the Postgres store with Supabase’s direct connection string:

# Supabase → Settings → Database → Connection string → URI
POSTGRES_URL=postgres://postgres.abcdef:password@aws-0-us-east-1.pooler.supabase.com:6543/postgres