How do I mock PostgreSQL queries in tests?

Record your application once against a real Postgres with Keploy. It captures each query, its bind parameters, and the rows and type OIDs the server returned, then answers the same queries from that recording during tests with no database running.

Is Keploy a Testcontainers Postgres alternative?

For replay, yes. Testcontainers boots a real Postgres in every CI job and needs a Docker runtime; Keploy records that server once and replays its responses with neither. Keep Testcontainers when you need to run new, unrecorded SQL.

Can I use Keploy instead of pg-mem?

Yes, and with higher fidelity. pg-mem reimplements Postgres in JavaScript and documents unsupported features, so tests can pass on SQL real Postgres rejects. Keploy replays responses that came from Postgres itself, type OIDs included.

Does Keploy capture Postgres type OIDs correctly?

Yes. The RowDescription message carries a type OID per column, and Keploy records it alongside the data rows. That means driver-level conversion — numeric as string, timestamptz zones, array parsing — is exercised on replay rather than assumed.

Does Keploy work with pgx, psycopg, and JDBC?

Yes, and identically. Capture happens at the Postgres frontend/backend protocol beneath the driver, so pgx, psycopg, asyncpg, node-postgres, and the JDBC driver all record and replay the same result sets.

Can Keploy mock Postgres transactions?

Yes. BEGIN, COMMIT, ROLLBACK, and savepoints are protocol-level messages, so a multi-statement transaction replays in the same order with the same command tags and error responses it originally received.

Keploy as a PostgreSQL testing framework

Keploy mocks PostgreSQL by recording its frontend/backend protocol beneath your driver, capturing the exact rows and column types each query returned, then replaying them during tests. No Postgres runs in CI, no schema is migrated, and no in-memory substitute is involved.

Generate PostgreSQL tests free

keploy record -c "gunicorn app.wsgi"

18.4K+

1.2M+300M+mocks createdSlack

What Keploy gives a PostgreSQL team

Captured at the frontend/backend protocol

Keploy reads the protocol beneath your driver, so each query replays with the data rows Postgres sent and the RowDescription that described them — type OIDs included.

Below pgx, psycopg, node-postgres, JDBC
Type OIDs from RowDescription
Bind parameters captured per query
Transactions and savepoints replay in order

Try Keploy for free Explore the docs

The problem

Why PostgreSQL integration tests slow teams down

The friction is rarely the assertions. It is everything around them — spinning up dependencies, keeping mocks honest, and repairing tests after every refactor.

Book a call Explore the docs

Three ways to do it

Testing PostgreSQL: by hand, with Testcontainers Postgres, or with Keploy

Testcontainers gives full Postgres semantics and charges a Docker runtime, an image pull, and a migration run per job. Keploy records that server once and replays its responses.

By hand

Testcontainers Postgres

With Keploy

Select any row for the full comparison, with code.

Get started Book a call

Same coverage, three costs

What you write for PostgreSQL vs what Keploy records

All three produce the same assertion. Only the third still passes after the next refactor without anyone editing it.

By hand2–4 hours

order_repo_stub.py

hand-written

# Hand-written stub: no SQL is executed, parsed, or type-mapped.

from dataclasses import dataclass

from decimal import Decimal

@dataclass

class Order:

id: str

amount: Decimal

state: str

class OrderRepoStub:

def __init__(self):

self._rows = {

# A guess: real Postgres numeric may arrive as a string.

"ord_1": Order("ord_1", Decimal("42.00"), "NEW"),

}

def get(self, order_id):

return self._rows.get(order_id)

def set_state(self, order_id, state):

# No constraint, no trigger, no unique index is checked here.

self._rows[order_id].state = state

The stub decides its own types, so the classic bug — a numeric column arriving as a string — is impossible to reproduce and impossible to catch.

Testcontainers Postgres1–2 hours

test_order_repo.py

tool-assisted

# Testcontainers Postgres: real server, Docker in CI.

import pytest

from testcontainers.postgres import PostgresContainer

@pytest.fixture(scope="session")

def pg():

with PostgresContainer("postgres:16") as container:

run_migrations(container.get_connection_url())

seed_orders(container.get_connection_url())

yield container

def test_reads_a_seeded_order(pg):

repo = OrderRepo(connect(pg.get_connection_url()))

order = repo.get("ord_1")

assert order.state == "NEW"

assert order.amount == Decimal("42.00")

Accurate down to type OIDs — and every job pulls a Postgres 16 image, boots a server, and replays the whole migration history first.

With Keploy~5 minutes

mocks.yaml

auto-generated

# Recorded with: keploy record -c 'gunicorn app.wsgi'

# The Postgres response, captured at the protocol level.

version: api.keploy.io/v1beta1

kind: Postgres

name: mock-2

spec:

request:

query: "SELECT id, amount, state FROM orders WHERE id = $1"

params:

- "ord_1"

response:

# RowDescription, exactly as the server sent it.

row_description:

- name: id

type_oid: 25

- name: amount

type_oid: 1700

- name: state

type_oid: 25

data_rows:

- ["ord_1", "42.00", "NEW"]

command_complete: "SELECT 1"

Type OID 1700 is numeric, and the value replays as the string Postgres actually sent — so driver conversion is exercised, not assumed.

Times are estimates for authoring one endpoint’s coverage from scratch, not measurements.

Explore the docs Request a trial

Keploy vs the alternatives

PostgreSQL testing tools, compared

The options a team on relational databases actually reaches for, and where each one genuinely wins. Select a row for the full comparison.

Keploy

Testcontainers

H2

DBUnit

pg-mem

Postman

SmartBear ReadyAPI

Best in classStrongPartialNot covered

Assessments reflect each tool’s documented behaviour, not benchmark measurements.

Request a trial Book a call

How it works

Record your PostgreSQL app once, replay it forever

Keploy sits below your PostgreSQL process at the network layer. It watches the calls your app already makes, then serves them back on replay so tests run with no dependencies attached.

Your PostgreSQL appGET/api/v1/orders/{id}200

RecordingKeploy proxyeBPF · userspace

Pythonpsycopg, asyncpg, and SQLAlchemy

Node.jsnode-postgres, Prisma, and Drizzle

Gopgx and lib/pq, with GORM or sqlc on top

JavaThe PostgreSQL JDBC driver and Spring Data JPA

+4 more PostgreSQL clients

An example shape of a captured call. Your own endpoints and dependencies come from your real traffic, so nothing here has to be written by hand.

Try Keploy for free Request a trial

Mock coverage

PostgreSQL clients Keploy records, driver by driver

Keploy captures PostgreSQL at the wire protocol, so 6 of these 8 clients need no adapter, no test double, and no PostgreSQL instance in CI.

Auto-mocked
PythonView page
psycopg, asyncpg, and SQLAlchemy
Auto-mocked
Node.jsView page
node-postgres, Prisma, and Drizzle
Auto-mocked
GoView page
pgx and lib/pq, with GORM or sqlc on top
Auto-mocked
JavaView page
The PostgreSQL JDBC driver and Spring Data JPA
Auto-mocked
DjangoView page
The Django ORM's Postgres backend
Auto-mocked
Spring BootView page
Spring Data JPA over Hibernate
Partial
Rust
tokio-postgres and sqlx
Partial
.NET
Npgsql and Entity Framework Core

Explore the docs Book a call

Quick start

Your first PostgreSQL test suite in under five minutes

Every command below runs against your existing PostgreSQL service. Nothing in your source tree changes.

1
1Install the Keploy CLI
One binary on the recording machine. Nothing is installed into Postgres, and no extension is required.
curl -sSL https://keploy.io/install.sh | bash
2
2Record with your development database reachable
Run the app as usual. Every query, its bind parameters, and the rows Postgres returned are captured together.
keploy record -c "gunicorn app.wsgi --bind 0.0.0.0:8000"
3
3Exercise the paths that hit the database
Reads, writes, transactions, and prepared statements all record with their parameters and responses.
curl localhost:8000/orders/ord_1
curl -X POST localhost:8000/orders/ord_1/confirm -d '{"payment_token":"tok_123"}'
4
4Replay with Postgres stopped
Shut Postgres down and run the suite. If replay passes, every query your code makes is answered by a recorded mock.
keploy test -c "gunicorn app.wsgi --bind 0.0.0.0:8000" --delay 12