Skip to main content

PostgreSQL Mocking

This guide covers how to use proxymock to mock PostgreSQL database connections and queries for local development and testing.

Introduction to PostgreSQL​

PostgreSQL is one of the world's most advanced open-source relational database management systems (RDBMS). It uses Structured Query Language (SQL) for accessing and managing data stored in relational tables. proxymock is able to record and mock PostgreSQL databases. This allows you to mock a PostgreSQL database, including real data, without running a PostgreSQL database or populating it with data. To do this, we record your app talking to a PostgreSQL database and simulate the database in subsequent tests. To learn more about proxymock recording and architecture, work through the agent tutorial.

Demo App​

The following instructions will work with most PostgreSQL clients. However, for demonstration purposes we'll use a simple gp application that connects to a PostgreSQL database. If you are using your own app skip ahead to recording for the environment variables you'll need.

This demo app is available in the speedscale/demo repository and requires Go to be installed. If you'd like to use the demo app, clone the repository and navigate to the go-postgres directory, which contains a basic golang user service that interacts with PostgreSQL:

git clone https://github.com/speedscale/demo && cd demo/go-postgres

Start PostgreSQL on your local machine (instructions in the README). Don't worry if you don't have go installed and can't use this app. Just apply the environment variables to your own PostgreSQL client app.

Recording PostgreSQL Traffic​

The proxymock record command creates RRPair files from real PostgreSQL interactions. Each request will contain a PostgreSQL command (like a SELECT SQL statement) and the response will usually contain row information and values returned by the database.

Start the Recorder​

Start a dedicated terminal window to run the proxymock recorder:

proxymock record --map 15432=localhost:5432 --app-port 8080

This tells the recorder to listen on port 15432 for PostgreSQL traffic and forward it to the real PostgreSQL server at 5432. Your can learn more about the how proxymock records on the architecture page.

PostgreSQL Connection Configuration​

If you are using the demo app, make sure you have PostgreSQL running and you have created the database. The instructions are in the demo readme.

For proxymock to capture PostgreSQL traffic, point your application at the mapped port. Start a new terminal window that will run the demo app server:

PGUSER=<user> PGPORT=15432 go run main.go

Normally PGPORT and PGUSER will default to the default postgres installation values. With homebrew on mac that means your $username and 5432. Since proxymock is intercepting traffic on port localhost:15432 we set PGPORT to use those connection parameters. This is the key ingredient for redirecting traffic to the proxymock recorder with --map.

What Gets Recorded​

You can browse the recording in your browser with proxymock web:

proxymock web

proxymock web shows each statement with its bound values, how it ended and the rest of its connection, and its Live SQL lens follows statements as your app runs them. See Inspect a statement and Watch live SQL. To list every statement your app ran, see SQL inventory, and to turn them into a runnable .sql file, see Export a SQL script.

proxymock captures PostgreSQL traffic as RRPair files containing:

  • Request Data: SQL queries, prepared statements, connection handshakes
  • Response Data: Result sets, error responses, metadata
  • Timing Information: Query execution times and connection latency

The actual wire protocol is binary but proxymock displays request and response data as JSON. The underlying files can be modified if you want your mock to return different values. You can learn more about the structure of the underlying recording by looking at the proxymock directory containing the recording files and the docs.

Troubleshooting Recording​

  • Ensure your PostgreSQL driver supports SOCKS5 proxy or use --map
  • If you are using SOCKS, check that ALL_PROXY or your driver's SOCKS configuration is set correctly
  • Verify PostgreSQL server is accessible from proxymock

Starting the Mock Server​

Make sure to stop your local PostgreSQL server to prevent port conflicts. You can run your app against the normal PostgreSQL port 5432 now and proxymock will simulate the database.

Start the proxymock mock server:

proxymock mock

You can now run your PostgreSQL client normally and it will connect to proxymock on port 5432 like a normal database.

Modifying Responses​

A SQL mock matches on the statement, not on its bound values. See Mock a database for what that means and when a transform should make a value part of the match.

To change a response by hand, edit its RRPair markdown file. To change values on every run, save the transforms in a blueprint instead. Active blueprints in the workspace apply during proxymock mock and proxymock replay without rewriting the recording. They use the same transform engine as Speedscale Cloud, so you do not need to upload the snapshot.

Create and Verify Blueprints shows how to save transforms from proxymock web, preview their changes, and confirm they ran. Use a responder response chain to change the mock response returned to your app, or a responder request chain to change the request used for mock matching.

If your team already keeps a Traffic Transform Template in Speedscale Cloud, pull it into the workspace:

proxymock cloud pull transform <id>

The template is written to proxymock/blueprints/<id>.json, and the next proxymock mock applies it.

Load and Regression Testing​

The queries you recorded can also be replayed against a real PostgreSQL database, to load test it or to catch regressions after a schema change or version upgrade. See Load and Regression Test a Database.

Kubernetes and eBPF​

In Kubernetes environments, Speedscale's eBPF collector (nettap) captures PostgreSQL traffic automatically at the kernel level — no --map configuration, SOCKS proxying, or application changes required. The collector uses kprobes and uprobes to observe TCP and TLS traffic directly, including the PostgreSQL wire protocol.

This means the --map workflow described above applies to local development only. When your application is deployed in Kubernetes with the Speedscale operator, PostgreSQL traffic is captured transparently alongside all other protocols.

For details on eBPF-based traffic collection, see the eBPF Traffic Collection documentation.