Writing Integration Tests That Assert Query Routing

Problem statement: your read/write split is a piece of production logic on the path of every query, and there is currently no test that would fail if it broke.

Routing is unusually well suited to automated testing — the assertion is a single question with a factual answer — and unusually rarely tested, because the fixture takes a little more effort than a mock. This page builds the fixture, the helper, and the statement list that makes the suite worth having. The correctness properties it verifies are defined in testing read/write splitting correctness.

Symptom Identification

You need this suite if any of the following is true:

  • Routing rules are edited directly in production and verified by watching error rates.
  • A rule change has previously caused a cannot execute UPDATE in a read-only transaction incident.
  • Nobody can state with confidence where SELECT ... FOR UPDATE goes in your stack without checking.
  • The proxy configuration lives in a repository with no tests, or is not in a repository at all.
  • A previous “routing test” consisted of SELECT 1 against each endpoint.

The last case is the most common and the most misleading. Every classifier ever written routes SELECT 1 correctly, so that test passes on a configuration that will fail on the first data-modifying CTE it sees.

Root Cause Analysis

Routing correctness is a property of the interaction between three things: the statement text, the session state, and the rule set. A unit test that exercises only the third — feeding statements to a classifier function — misses every bug that arises from the first two interacting with a real database.

A real standby has three behaviours a mock does not. It returns true from pg_is_in_recovery(). It rejects writes with SQLSTATE 25006 rather than with whatever your mock was told to raise. And it enforces transaction semantics: a BEGIN on the standby is a genuine read-only transaction, so a routing bug that splits a transaction produces a real, observable failure rather than a passing test.

The assertion must come from the server. A router that reports “I selected the replica” is reporting its intent. Between intent and outcome sit connection pinning, failover, a rule loaded to runtime but not to disk, and any number of pooling layers. Asking the database which node executed the statement — from the same session, immediately afterwards — is the only assertion that cannot be fooled.

The value is concentrated in the statement list. A suite of twenty plain SELECTs and INSERTs proves almost nothing. A suite of ten carefully chosen adversarial shapes proves nearly everything, because those shapes are the ways classification fails.

Test fixture depth versus the bug classes each can catch Three columns representing test approaches. A classifier unit test covers statement text only and catches simple keyword errors. A mocked database adds behaviour you modelled yourself and catches what you already anticipated. A real primary and standby pair adds read-only enforcement, recovery state and transaction semantics, and is the only column that catches split transactions, data-modifying CTEs reaching a standby, and session-state pinning. classifier unit test statement text only mocked database + behaviour you modelled real primary + standby + behaviour the database enforces catches keyword mistakes regex typos the above, plus cases you anticipated i.e. not the ones that bite split transactions CTE writes reaching a standby session-state pinning the bugs that reach production The fixture costs about fifteen seconds per CI run and is the only column that fails on a real routing defect. Keep the classifier unit tests too — they are fast and they localise a failure the fixture only reports.

Step-by-Step Resolution

Step 1 — Stand up a real pair

yaml
# docker-compose.routing.yml — a genuine streaming primary/standby plus router.
services:
  primary:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: test
      POSTGRES_INITDB_ARGS: "-c wal_level=replica -c max_wal_senders=4 -c hot_standby=on"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 1s
      retries: 30

  standby:
    image: postgres:16
    depends_on:
      primary: {condition: service_healthy}
    environment: {PGPASSWORD: test}
    # A real base backup, so this is a real standby — not a second primary
    # with a label that says standby.
    command: >
      bash -c "rm -rf /var/lib/postgresql/data/* &&
               pg_basebackup -h primary -U postgres -D /var/lib/postgresql/data
                             -R -Fp -Xs -P &&
               chmod 0700 /var/lib/postgresql/data &&
               exec docker-entrypoint.sh postgres"

  router:
    image: proxysql/proxysql:2.6
    depends_on: [primary, standby]
    volumes: ["./proxysql.test.cnf:/etc/proxysql.cnf:ro"]
    ports: ["6033:6033"]

Inline verification: psql -h localhost -p 5433 -c "SELECT pg_is_in_recovery()" against the standby returns t, and an INSERT against it fails with SQLSTATE 25006.

Step 2 — Write the server-side assertion helper

python
# tests/routing/conftest.py
import pytest, psycopg

ROUTER_DSN = "postgresql://app:test@localhost:6033/app"

def _served_by(cur) -> str:
    """Ask the SERVER which node ran the previous statement. The router's own
    report is intent; this is outcome."""
    cur.execute("SELECT pg_is_in_recovery()")
    return "replica" if cur.fetchone()[0] else "primary"

@pytest.fixture(scope="session")
def router_conn():
    with psycopg.connect(ROUTER_DSN, autocommit=True) as conn:
        yield conn

@pytest.fixture
def routed(router_conn):
    def run(sql: str) -> str:
        with router_conn.cursor() as cur:
            cur.execute(sql)
            return _served_by(cur)          # same session, immediately after
    return run

The probe must run on the same connection and immediately afterwards. Opening a second connection to ask the question tests a different routing decision than the one you made.

Inline verification: routed("SELECT 1") returns a string, and routed("SELECT pg_backend_pid()") returns the same node twice in a row on a connection-pinning router.

Step 3 — Enumerate the adversarial shapes

python
# tests/routing/test_classification.py
import pytest

CASES = [
    # (sql,                                            expected,  why it is here)
    ("SELECT id FROM users WHERE id = 1",              "replica", "baseline"),
    ("UPDATE users SET seen = now() WHERE id = 1",     "primary", "baseline"),
    ("SELECT id FROM users WHERE id = 1 FOR UPDATE",   "primary", "takes row locks"),
    ("SELECT id FROM users WHERE id = 1 FOR SHARE",    "primary", "takes row locks"),
    ("WITH d AS (DELETE FROM queue RETURNING id) SELECT count(*) FROM d",
                                                        "primary", "write starting with WITH"),
    ("WITH c AS (SELECT 1) SELECT * FROM c",           "replica", "read starting with WITH"),
    ("SELECT nextval('users_id_seq')",                 "primary", "advances sequence state"),
    ("SELECT audit_login(1)",                          "primary", "VOLATILE function writes"),
    ("CALL rebuild_summary()",                         "primary", "procedure with side effects"),
    ("/* cached */ SELECT count(*) FROM users",        "replica", "leading comment"),
    ("SELECT count(*) FROM users",                     "replica", "aggregate read"),
]

@pytest.mark.parametrize("sql,expected,why", CASES, ids=[c[2] for c in CASES])
def test_statement_reaches_expected_node(routed, sql, expected, why):
    assert routed(sql) == expected, f"{why}: {sql!r} went to the wrong node"

Note the pairing of the two WITH cases. A classifier that fixes the data-modifying CTE by sending everything starting with WITH to the primary passes the first and fails the second — and the second is the common shape, so that “fix” silently costs replica capacity.

Inline verification: running the suite against a deliberately naive keyword classifier fails on at least the FOR UPDATE, CTE, and nextval cases.

Step 4 — Add the coherence test

python
# tests/routing/test_coherence.py
def test_transaction_does_not_split(router_conn):
    """Every statement inside one transaction must reach the same node."""
    with router_conn.cursor() as cur:
        cur.execute("BEGIN")
        cur.execute("UPDATE users SET seen = now() WHERE id = 1")
        first = _node(cur)
        cur.execute("SELECT seen FROM users WHERE id = 1")
        second = _node(cur)
        cur.execute("COMMIT")
    assert first == second == "primary", \
        f"transaction split across nodes: {first} then {second}"

def test_session_state_pins_the_session(router_conn):
    """A SET (not SET LOCAL) makes the session ineligible for a replica."""
    with router_conn.cursor() as cur:
        cur.execute("SET statement_timeout = '7s'")
        cur.execute("SELECT current_setting('statement_timeout')")
        assert cur.fetchone()[0] == "7s", "session state did not survive routing"

def _node(cur):
    cur.execute("SELECT pg_is_in_recovery()")
    return "replica" if cur.fetchone()[0] else "primary"

The second test asserts something subtler than a destination: that session state set on one statement is visible to the next. A router that moves the session between nodes silently loses the setting, which is a class of bug that produces no error and very confusing behaviour.

Inline verification: both tests pass against a transaction-aware router and the first fails against one without transaction persistence.

Step 5 — Gate the build on it

yaml
# .github/workflows/routing.yml
name: routing
on:
  pull_request:
    paths: ["proxy/**", "src/db/**", ".github/workflows/routing.yml"]
jobs:
  assert-routing:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Bring up primary, standby and router
        run: docker compose -f docker-compose.routing.yml up -d --wait
      - name: Assert routing
        run: pytest tests/routing -q --tb=short
      - name: Router rule set actually loaded  # catches a config that never applied
        run: |
          docker compose -f docker-compose.routing.yml exec -T router \
            mysql -h127.0.0.1 -P6032 -uadmin -padmin \
            -e "SELECT rule_id, match_pattern, destination_hostgroup
                  FROM runtime_mysql_query_rules ORDER BY rule_id"

The paths filter keeps the job off unrelated pull requests while guaranteeing it runs on every change to the proxy config or the data-access layer — the two places a routing regression can originate.

Inline verification: open a pull request that deliberately reorders two rules and confirm the job fails.

Configuration Snippet

ini
# proxysql.test.cnf — the rule set under test. Keep it byte-identical to
# production apart from the host addresses, or the suite tests a fiction.
mysql_query_rules =
(
  # Order matters and is part of what is being tested. Locking reads first.
  { rule_id=10, active=1, match_digest="^SELECT.*FOR (UPDATE|SHARE)",
    destination_hostgroup=10, apply=1 },

  # Data-modifying CTEs: a write that begins with WITH.
  { rule_id=20, active=1,
    match_digest="^WITH .*\\b(INSERT|UPDATE|DELETE)\\b",
    destination_hostgroup=10, apply=1 },

  # Sequence and volatile helpers that mutate state.
  { rule_id=30, active=1, match_digest="\\b(nextval|setval|pg_advisory)",
    destination_hostgroup=10, apply=1 },

  # Everything else that reads, including a plain read-only CTE.
  { rule_id=40, active=1, match_digest="^(WITH|SELECT|SHOW|EXPLAIN)",
    destination_hostgroup=20, apply=1 },

  # Default: primary.
  { rule_id=99, active=1, match_digest=".*", destination_hostgroup=10, apply=1 }
)

mysql_users =
(
  # transaction_persistent is what makes the coherence test pass. Without it,
  # statements inside a transaction are routed independently.
  { username="app", password="test", default_hostgroup=10,
    transaction_persistent=1, active=1 }
)

Rule 20 sitting above rule 40 is the entire reason the two WITH cases behave differently, and rule order is therefore part of the artefact under test. This is why the CI job dumps runtime_mysql_query_rules rather than the config file: what governs behaviour is the ordered runtime set, and a config file that was never loaded looks identical to one that was.

Rule order decides where a data-modifying CTE lands A statement beginning with WITH and containing DELETE descends through five ordered rules. It does not match the locking-read rule, matches the data-modifying CTE rule, and stops there, routed to the primary. A second path shows the same statement when the CTE rule is placed below the general read rule: it now matches the general read rule first and is routed to a replica, where it fails with a read-only transaction error. WITH d AS (DELETE … RETURNING) SELECT … correct order 10 · FOR UPDATE 20 · WITH + write 40 · reads primary ✓ matched and stopped before the read rule rules reordered 10 · FOR UPDATE 40 · reads 20 · WITH + write replica ✗ SQLSTATE 25006 in production Same rules, same statement, different order. This is why the test artefact is the ordered runtime set.

Verification and Rollback

Confirm the suite fails when it should. A test suite that has never failed is not evidence of anything. Introduce a deliberate defect and check the suite catches it:

bash
# Disable the data-modifying CTE rule and re-run — the CTE case must fail.
docker compose exec -T router mysql -h127.0.0.1 -P6032 -uadmin -padmin \
  -e "UPDATE mysql_query_rules SET active=0 WHERE rule_id=20;
      LOAD MYSQL QUERY RULES TO RUNTIME;"
pytest tests/routing -q     # expect exactly one failure: 'write starting with WITH'

If the suite still passes, the test is not exercising the path you think it is — most often because the statement never reached the router, or because the assertion read a cached value.

Confirm the fixture is a real standby, not a second independent database:

bash
docker compose exec -T standby psql -U postgres -Atc "SELECT pg_is_in_recovery()"
# expect: t
docker compose exec -T standby psql -U postgres -c "CREATE TABLE t(i int)" 2>&1 \
  | grep -q "read-only" && echo "genuinely a standby"

Rollback. The suite itself is additive and carries no production risk — the rollback question applies to what you change because of it. When a test reveals that a rule must be added or reordered, deploy the rule change through the canary process rather than directly, since a rule set that satisfies the fixture can still surprise on production statement shapes the fixture does not contain. If the CI gate itself becomes an obstacle — flaky container start-up, for example — fix the fixture rather than removing the gate; a disabled routing test is indistinguishable from no routing test.

Edge Cases and Gotchas

pg_basebackup needs the data directory empty and correctly permissioned

The standby container command removes the data directory before the base backup and sets mode 0700 afterwards, and both are required. Without the removal, pg_basebackup refuses because the directory is not empty; without the chmod, PostgreSQL refuses to start because group or world permissions are too permissive. Both failures happen at container start and are easy to misread as a networking problem.

A router that pools connections can make consecutive statements land differently

In transaction mode, the probe statement may be assigned a different backend than the statement under test — so pg_is_in_recovery() reports the probe’s node, not the subject’s. Wrap the statement and the probe in an explicit transaction when testing against a transaction-pooling router, or test against the router in session mode and cover pooling behaviour separately.

Volatile-function cases need the function to exist

The SELECT audit_login(1) case only tests anything if audit_login exists and is declared VOLATILE. Create the fixture schema as part of the container bootstrap, and mark the function’s volatility explicitly — PostgreSQL defaults to VOLATILE, but a function declared STABLE by mistake changes what the test is asserting without changing its text.

FAQ

Why not mock the router in unit tests?

Because a mock encodes the assumptions you already hold, and the bugs live in the assumptions you do not. A real standby genuinely refuses writes, genuinely reports itself in recovery, and genuinely applies transaction semantics — three behaviours a mock reproduces only as far as you thought to model them. The tests worth having are exactly the ones a mock cannot fail.

How slow is a two-container fixture in CI?

Starting a primary and attaching a standby with pg_basebackup takes roughly ten to twenty seconds on typical CI hardware, and the suite itself runs in seconds. Reuse the containers across the whole routing suite rather than per test, and the fixture cost is paid once per build — cheap relative to discovering a classification gap in production.

Should routing tests run against the application or against the router directly?

Both, and they answer different questions. Testing the router directly with raw statements verifies the rule set. Testing through the application’s own data-access layer verifies that your code produces the statement shapes the rules expect — which is where transaction wrappers and ORM-generated SQL cause surprises. Start with the router, then add a thin layer of application-level cases for the paths that matter most.

What should the test do about replication lag?

Ignore it, deliberately. Routing tests assert where a statement went, not whether the data it read was current — mixing the two makes the tests flaky, because lag in a container fixture is unpredictable. Assert destination only, and cover freshness separately with tests that control lag explicitly by pausing replay.

← Back to Testing Read/Write Splitting Correctness