Skip to main content

Load and Regression Test a Database

This guide shows how to turn your app's real PostgreSQL, MySQL, SQL Server or MongoDB traffic into three kinds of test:

  • A quick mock check. Record once, run your app against a mocked database, and replay your app's own traffic to confirm it still works with no database running.
  • A database regression test. Replay the recorded statements once against a new schema, a migration or a new database version, such as PostgreSQL 15 to 16, MySQL 8.0 to 8.4 LTS, SQL Server 2019 to 2022 or MongoDB 7.0 to 8.0, and see every statement that now fails.
  • A database load test. Replay the recorded statements with many concurrent sessions to find out how the database holds up under your app's real statement mix.

Every step works two ways. You can ask an AI coding assistant that has the proxymock MCP server installed, or you can run the proxymock CLI yourself. The load and regression steps also work from the Speedscale dashboard for traffic recorded in Kubernetes.

Pick your database once in any of the tabs below and every section follows. The MySQL steps also work for MariaDB, which speaks the MySQL protocol. The MySQL replay has been tested against MySQL 8.0, MySQL 8.4 and MariaDB 11.4. The SQL Server replay has been tested against SQL Server 2022 with sessions recorded from mssql-jdbc and Microsoft.Data.SqlClient, and against SQL Server 2025 with TDS 8.0 strict encryption. The MongoDB replay has been tested against MongoDB 7.0 and 8.0; for MongoDB, "statement" on this page means a command such as find or insert.

Watch database replay and testing on YouTube.

Before you start​

  • proxymock installed. See installation.
  • An app that talks to your database. The examples use the go-postgres and go-mysql apps from the speedscale/demo repository; go-mysql uses server-side prepared statements and a checkout transaction. The demo repository has no SQL Server app, so the SQL Server tabs use your own. Any client works.
  • For the regression and load tests, a database you can change freely. Replays run real inserts, updates and deletes, so never point them at production.

Quick start: record, mock and check​

This is the shortest path: get some traffic, run the mock locally, and run a quick replay to check the mock.

1. Record. Start the recorder, then start your app with its PostgreSQL port pointed at the recorder:

"Record my app's traffic with proxymock. My app listens on port 8080 and talks to PostgreSQL on localhost:5432, so map a local port to it and start my app pointed at that port."

Use your app for a minute so it runs its usual queries, for example by calling its API with curl. Then stop the recorder. The recording is in the proxymock directory: your app's inbound requests, and every PostgreSQL call it made.

2. Run the mock. Stop your local PostgreSQL server so nothing else is listening on 5432, then start the mock with your app:

"Stop recording and run a proxymock mock server, then start my app against it."

Your app now talks to proxymock on port 5432 as if it were the database.

3. Check the mock with a replay. Replay the recorded inbound requests against your app while the mock answers its statements:

"Replay the recorded traffic against my app on localhost:8080 with the mock server running, and tell me whether every response matched and whether every database call hit a mock."

If every request matches, your app works end to end with no database at all. If some database calls miss their mocks, see Improve Mock Match Rate with AI.

How proxymock decides what to replay​

Every recorded RRPair has a direction. Inbound traffic is what your app received, and outbound traffic is what your app sent, including every database statement. By default a replay sends the inbound traffic to your app as tests and serves the outbound traffic as mocks.

To load or regression test the database itself, flip that for the database traffic with a tests filter: the recorded statements become the tests, and the database is the system under test. Everything the filter does not match is mocked, and the recorded direction never changes.

Where you replayHow to choose the tests
proxymock CLI--tests-filter '(direction IS OUT) AND (tech IS Postgres)', tech IS MySQL, tech IS MongoDB or tech IS "SQL Server", on proxymock replay
AI assistant (proxymock MCP)Ask for it, or pass the tests-filter parameter of the replay_traffic tool
proxymock webThe Tests filter field on the Replay tab
Speedscale dashboardChoose replay tests on the snapshot's Traffic tab, then pick the database under Outbound dependencies

To keep your app's inbound tests too, use (direction IS IN) OR (tech IS Postgres), tech IS MySQL, tech IS MongoDB or tech IS "SQL Server". A technology name with a space is quoted. Choose What a Replay Tests covers the filter syntax, saving a filter with the workspace, promoting a single file, and the removed reverse services setting that tests filters replace.

Connect to the database​

The replay connects to the database with its own sessions, so it needs a user and password. Point the replay at the database with a postgres:// or mysql:// address that includes the host, port and database name, or a SQL Server address in the drivers' form, sqlserver://host:port?database=name, but no credentials. Credentials in the address are rejected so they never end up in shell history or reports.

For MongoDB, use a mongodb://host:port/authdb address. The path names the database that holds the replay user, as in any MongoDB connection string, and admin is used when it is empty. Each command still runs against the database it was recorded on. The address takes only the tls and authSource parameters, and names one host: mongodb+srv:// addresses and host lists are rejected, because the replay connects straight to the one server you name.

There are three ways to give the replay its user and password. Pick one by where the replay runs and by how private the password is:

OptionWorks forWhere the password is keptUse it for
The database client's environmentproxymock on your machineOn your machine onlyLocal regression and load tests
A password typed into the test configproxymock and the Speedscale dashboardIn plain text, in the test config and in every replay report that uses itA test database whose password is not sensitive
A secret reference in the test configproxymock and the Speedscale dashboardIn a file in your proxymock workspace, or in a Kubernetes Secret in the cluster the replay runs in. The test config keeps only the reference.Any password that must stay private

When more than one is set, the database name and TLS mode in the address win over the test config, and the test config wins over the environment. A SQL Server replay with no database set anywhere connects to the database the recorded app signed in to. MongoDB clients share no standard environment variables, so for MongoDB use option 2 or 3.

Option 1: the database client's environment​

For a replay you run on your own machine, proxymock reads the same settings as the database's command-line client, so nothing is saved with the test config or the report.

proxymock reads the user and password the same way psql does: the PGUSER and PGPASSWORD environment variables, or a ~/.pgpass file. PGDATABASE, PGSSLMODE and PGCONNECT_TIMEOUT work too.

PGUSER=replay PGPASSWORD=<password> proxymock replay \
--tests-filter '(direction IS OUT) AND (tech IS Postgres)' \
--test-against postgres://localhost:5432/tasks_db

An AI assistant runs proxymock through the MCP server, which inherits the environment the assistant was started with, so ~/.pgpass is the simplest option there.

A replay in a Kubernetes cluster does not read your machine's environment, so for a replay started from the Speedscale dashboard use option 2 or 3.

Option 2: type the password into the test config​

If the database is a test database and its password is test data, you can type the password straight into the test config.

warning

A typed password is saved in plain text with the test config and with every replay report that uses it. Anyone in your Speedscale tenant who can open the test config or one of those reports can read it. Never type a production credential here, or a password that also opens any other system. Use a secret reference for those.

In the Speedscale dashboard:

  1. Open the test config you replay with, and select its Traffic tab.
  2. Under Database credentials, select the Postgres, MySQL or SQL Server tab. On the SQL Server tab, Sign-in chooses a SQL Server login or Microsoft Entra ID; with Entra ID there is no username or password to fill in.
  3. Fill in Username and Database, and set TLS mode and Connect timeout if you need them. A field left empty falls back to the database client's own default.
  4. Under Password, select Constant and type the password. The field is masked; select the eye icon to show it. A warning above the field reminds you that it is stored in plain text.
  5. Save the test config.

When you open the test config again, the Password field starts on Constant because it holds a typed password. Switching between Constant and Kubernetes secret clears the field.

The same settings are the generator.postgres, generator.mysql or generator.mssql section of the test config's JSON, which is also how you set them for proxymock. Each section takes username, password, database, tlsMode and connectTimeout, and generator.mssql also takes authMethod.

MongoDB uses the generator.mongodb section. The dashboard's Database credentials form does not show it yet, so in the dashboard edit it in the test config's JSON on the Advanced tab. Its database is the database that holds the user, admin when empty. It also takes clientCertificate and caCertificate; see TLS.

{
"generator": {
"postgres": {
"username": "replay",
"password": "test-only-password",
"database": "tasks_db",
"tlsMode": "PROTOCOL_TLS_MODE_REQUIRE"
}
}
}

proxymock also logs a warning when a test config holds a typed password.

Option 3: a secret reference in the test config​

To keep the password out of the test config and out of reports, store it as a secret and put a reference to it in the Password field, or in password in the JSON, such as ${{secret:pg-creds/password}}. The reference names the secret and the key inside it. The username and database can be references too.

Where the reference is read from depends on where the replay runs:

Where the replay runsWhat ${{secret:pg-creds/password}} reads
proxymock on your machineThe file proxymock/secrets/pg-creds/password in your workspace. See Secrets.
A Kubernetes cluster: the Speedscale dashboard, proxymock cloud replay or proxymock cluster replayThe key password of the Kubernetes Secret pg-creds, in the namespace the replay runs in. Files in proxymock/secrets are not uploaded.

Create the secret first. On your machine, write the password to a file, and keep proxymock/secrets out of version control:

mkdir -p proxymock/secrets/pg-creds
printf '%s' '<password>' > proxymock/secrets/pg-creds/password

For a replay in a cluster, create a Kubernetes Secret in the replay namespace:

kubectl create secret generic pg-creds --from-literal=password='<password>' -n <namespace>

In the Speedscale dashboard, select Kubernetes secret under Password, which is where an empty password starts. Then either type the reference or select Pick secret to choose the cluster, namespace, Secret and key from your cluster. The field then holds the reference, not the password. If the Secret does not exist yet, No secret yet? shows the kubectl create secret command to create it.

With Speedscale v2.5.1371 or later in the cluster, the operator mounts every Secret that generator.postgres, generator.mysql, generator.mssql or generator.mongodb references into the replay. If the Secret or the key is missing, the replay stops before it sends any traffic with a message such as Generator cannot start: Secret pg-creds is missing in namespace .... With an older operator, also add the Secret to the replay's secretRefs.

TLS​

Set the TLS mode with ?sslmode= on the address, with tlsMode in the test config, or with PGSSLMODE.

Before any load starts, the replay connects once to each database. A wrong password or a missing database stops the replay straight away with the server's error message, such as 1045 for a MySQL access denied, 18456 for a SQL Server login failure or AuthenticationFailed for MongoDB, instead of failing every statement.

Regression test the database​

Replay the recorded statements once against the new version of your database: a migrated schema, a new database release, or a different instance type. Start from a database in the state the recording expects, for example a copy with the same schema, or a copy restored from the dump you recorded against.

"Replay only the recorded Postgres traffic against postgres://localhost:5432/tasks_db once, and list every statement whose result differs from the recording, with the SQL error."

A statement that succeeded when it was recorded but returns an error now is reported under RESULT MISMATCH for its query. For example, after dropping a column that the app still writes, every INSERT and UPDATE that uses it is reported as a result mismatch. The replayed RRPair in the output directory holds the full PostgreSQL error, including its SQLSTATE code, such as 42703 for an undefined column.

The --fail-if condition makes the command exit with code 1 when any statement's result changed, which is what you want in CI.

Load test the database​

Replay the recorded statements with several concurrent sessions for a fixed time:

"Load test my Postgres database at postgres://localhost:5432/tasks_db with the recorded Postgres traffic: 10 virtual users for one minute in load-test mode. Summarize latency and failures by query."

Each virtual user opens one database session, keeps it for the whole run, and replays the recorded statements in order, so prepared statements carry over from one statement to the next. A SQL Server session prepares each statement once, however many recorded connections prepared it. When a pass through the recording ends inside a transaction, that transaction is rolled back so the next pass starts clean. --load-test skips response scoring, which keeps the replay itself light so the database is the bottleneck. The results table shows latency percentiles and throughput for every statement, and the FAILED column shows how many statements failed, with their share of the total.

While the test runs, the replay's sessions appear in pg_stat_activity with application_name set to speedscale-generator:

SELECT state, count(*) FROM pg_stat_activity
WHERE application_name = 'speedscale-generator' GROUP BY state;

A handful of failures right at the end of a timed run are statements that were still running when the time ran out.

Regenerate unique values​

To stop recorded unique keys from colliding, add a blueprint that regenerates them. An extractor picks one parameter of a prepared statement, and a transform such as rand_string replaces it on every replay. Save the blueprint as proxymock/blueprints/unique-users.json in the workspace.

The postgres_param extractor picks the parameter by its placeholder number, so 3 selects $3:

{
"id": "unique-users",
"name": "Unique users",
"tokenizeConfig": {
"generator": [
{
"filters": {"filters": [{"include": true, "detectedLocation": "INSERT INTO users", "operator": "CONTAINS"}]},
"extractor": {"type": "postgres_param", "config": {"index": "3"}},
"transforms": [{"type": "rand_string", "config": {"pattern": "[a-z0-9]{12}@load\\.test"}}]
},
{
"filters": {"filters": [{"include": true, "detectedLocation": "INSERT INTO users", "operator": "CONTAINS"}]},
"extractor": {"type": "postgres_param", "config": {"index": "4"}},
"transforms": [{"type": "rand_string", "config": {"pattern": "user-[a-z0-9]{12}"}}]
}
]
}
}

With it, the go-postgres demo's user INSERT went from every replayed statement failing with 23505 duplicate key value violates unique constraint to no mismatches, and each pass wrote new users. You can build the same blueprint in the Blueprints editor of proxymock web, where the extractor is listed as Postgres Statement Parameter. See postgres_param.

Replay from the Speedscale dashboard​

For traffic recorded in Kubernetes, you can run the same regression and load tests from the dashboard. You can also upload a local recording with proxymock cloud push snapshot.

  1. Open the snapshot's Traffic tab and select Choose replay tests. The action is available once the snapshot has finished analyzing and is not locked.
  2. Select Outbound dependencies and check the database, shown as its host and port with the detected technology. Check Also keep inbound tests if you want your app's inbound traffic replayed as well. The dialog previews the filter it will save.
  3. Save. The snapshot is reanalyzed, the snapshot page shows the active selection as Replay tests: followed by the filter, and the database appears as a service you can replay against. To go back, choose Inbound traffic (default) and save. A selection that was set outside the dashboard is shown as read-only filter text with a Reset to default button.
  4. Give the replay the database user and password in the test config's Database credentials section, on its Traffic tab. For a test database whose password is test data you can type the password there; otherwise pick a Kubernetes Secret. See Connect to the database for both options.
  5. Start a replay and target the database's address.

What gets replayed​

  • Sent: simple queries, named prepared statements, and executions of prepared or unnamed statements with their recorded parameters.
  • Folded into the execution that follows: Bind, Describe and unnamed Parse messages.
  • Skipped: connection startup, TLS negotiation, cancel and terminate messages, which the replay handles itself, plus COPY and function calls.
  • Recorded results: each replayed statement keeps its command tag, such as SELECT 13157, and a sample of up to 20 rows.

Tips and limits​

  • Use a disposable database. Replays change data. Reset it between runs, for example with TRUNCATE ... RESTART IDENTITY in PostgreSQL, by restoring a dump with mongorestore --drop for MongoDB, or by restoring a snapshot.
  • Row ids come from the recording. Recorded updates and deletes use the row ids from the original database, so on a fresh copy they may match no rows, and repeated inserts can hit unique constraints. In MySQL, auto-increment ids can also differ once concurrent inserts fail and use up ids, so recorded foreign keys may point at rows that do not exist and fail with 1452. These show up as SQL results, not replay failures. Start regression tests from a copy of the recorded database, and a blueprint can regenerate the unique values.
  • Transactions across connections. Recorded traffic mixes statements from many app connections, and a replay does not yet regroup them by connection. A replay can therefore send a COMMIT without its BEGIN or START TRANSACTION, and proxymock logs a warning when that happens. Workloads that depend on multi-statement transactions are not reproduced faithfully yet.
  • Always Encrypted replays need the same keys. The replay sends the ciphertext the app sent, so the database must hold the same column encryption keys, such as a restored copy of the recorded one. A key created again with the same value works when the recording holds the sp_describe_parameter_encryption calls the app made, because the replay learns the new key's identity from them. An app that cached its parameter descriptions before the recording started sends no such calls.
  • MySQL and SQL Server statements prepared before recording started are skipped, because the recording does not hold their SQL. Start recording before your app opens its connections.
  • Insert ids and timestamps differ from the recording on every run. They do not count as mismatches: a statement's result is scored by whether it succeeded, not by the values it returned.
  • MongoDB replies compare without server bookkeeping. Cluster time, operation time, election ids and the other fields a server fills in for every reply differ on every run, so they are left out when a recorded reply is compared with a replayed one, and live cursor ids are compared as the recorded ids they stand in for. A command whose result did not change therefore matches its recording with no assertion config. The raw recorded reply still holds those fields.
  • MongoDB replays connect to one server. The replay talks straight to the host in the recording or in the address, without discovering the rest of a replica set, so point it at the primary for writes and transactions. Transactions need a replica set or a sharded cluster, not a standalone server.
  • Queries that return a whole table get slower as a load test inserts more rows. That is real behaviour of your query mix, but reset the data between runs you want to compare.

Loading search...