Query Logging

DBBat logs every query or command executed through the proxy — across all supported engines (PostgreSQL, Oracle, MySQL, MariaDB, MongoDB, SQL Server) — providing complete visibility into database activity.

What's Logged

For each query, DBBat records:

• SQL text: the complete query as sent by the client (or the prepared statement text for binary protocols)
• Parameters: bound parameters for prepared/extended-query statements (PostgreSQL extended query, MySQL COM_STMT_EXECUTE)
• User: which DBBat user executed the query
• Database: which target server the query ran against
• Connection: the connection UID (links to connection metadata)
• Started at: when the query started
• Duration: how long the query took (milliseconds)
• Rows affected: number of rows returned or modified
• Error: error text if the query failed
• Result rows: optionally captured up to query_storage.max_result_rows / max_result_bytes
• Capture completeness: results_truncated and results_dropped (see Partial captures)

Blocked statements

A statement a grant refuses — a write under read_only, a CREATE TABLE under block_ddl, a COPY under block_copy — never reaches the upstream database, and is written to query history anyway, with the refusal as its error and duration_ms / rows_affected at zero. An attempt to do something a grant did not permit is evidence in its own right, so all five engines record it the same way and the UI badges it like any other failed query.

Engine-specific notes

• PostgreSQL: both Simple Query (Q) and Extended Query (P/B/E) are logged. Parameter values are stored as JSONB.
• MySQL / MariaDB: text protocol (COM_QUERY) and binary protocol (COM_STMT_EXECUTE) are decoded and stored uniformly. COM_INIT_DB is logged as USE <db>. COM_PING / COM_QUIT are not logged.
• Oracle: SQL is parsed out of TTC Execute (function 0x03, sub-op 0x5e) packets. Row capture works for SELECT results decoded from the first response and continuation packets; DML row counts are not captured from v315+ responses.

Viewing Queries

List recent queries:

curl -H "Authorization: Bearer $DBBAT_API_KEY" \
  "http://localhost:4200/api/v1/queries"

The global list resolves and returns user, server and connection columns alongside each query, so a single call is enough to see who ran what, where, and under which session — no follow-up lookups needed to make the list readable.

Filtering

# By user
curl -H "Authorization: Bearer $DBBAT_API_KEY" \
  "http://localhost:4200/api/v1/queries?user_id=$USER_UID"

# By server
curl -H "Authorization: Bearer $DBBAT_API_KEY" \
  "http://localhost:4200/api/v1/queries?database_id=$SERVER_UID"

# By time range (RFC 3339)
curl -H "Authorization: Bearer $DBBAT_API_KEY" \
  "http://localhost:4200/api/v1/queries?start_time=2024-01-15T00:00:00Z&end_time=2024-01-16T00:00:00Z"

# By connection
curl -H "Authorization: Bearer $DBBAT_API_KEY" \
  "http://localhost:4200/api/v1/queries?connection_id=$CONN_UID"

Query Details

Get a single query (without rows):

curl -H "Authorization: Bearer $DBBAT_API_KEY" \
  http://localhost:4200/api/v1/queries/$QUERY_UID

Response:

{
  "uid": "550e8400-e29b-41d4-a716-446655440000",
  "connection_id": "660e8400-e29b-41d4-a716-446655440000",
  "sql_text": "SELECT id, name FROM users WHERE active = $1",
  "parameters": {
    "values": ["true"],
    "format_codes": [0],
    "type_oids": [16]
  },
  "executed_at": "2024-01-15T10:30:00Z",
  "duration_ms": 12.5,
  "rows_affected": 5,
  "error": null
}

Every query names its owning connection through connection_id. In the web UI the query-detail page surfaces that link in its breadcrumb, so you can walk from a single statement back up to the session that issued it.

Query Result Rows

Result rows are stored separately and fetched on demand with cursor-based pagination — capped at 1000 rows or 1 MB per response, whichever comes first.

curl -H "Authorization: Bearer $DBBAT_API_KEY" \
  "http://localhost:4200/api/v1/queries/$QUERY_UID/rows?limit=100"

Response:

{
  "rows": [
    { "row_number": 0, "row_data": {"id": 1, "name": "Alice"}, "row_size_bytes": 32 }
  ],
  "next_cursor": "eyJvZmZzZXQiOjEwMH0=",
  "has_more": true,
  "total_rows": 500
}

Pass the next_cursor value back as ?cursor=… to fetch the next page.

Rows are persisted by one batched writer shared by every protocol and session. It flushes whenever ~1000 rows or ~8 MB have accumulated, or as soon as the queue runs dry — so an idle proxy writes rows immediately, and a busy one amortizes the round-trip across queries. Capture never holds a whole result set in memory waiting for the query to end, and never blocks the query on DBBat's own storage.

Partial captures

Two independent flags say a stored result set is not the whole story. They are deliberately separate, because they mean opposite things:

Flag	Meaning
results_truncated	Capture stopped at a configured limit (max_result_rows / max_result_bytes). The stored rows are the beginning of the result set. Expected and explainable.
results_dropped	DBBat lost rows it meant to keep: row storage fell behind the proxy (the writer's queue was full) or a batch insert failed. The stored rows have gaps.

A drop never affects the client: the rows still reach the database client untouched. DBBat degrades its own capture rather than stalling a customer's query behind its storage — so results_dropped is a signal that DBBat's store is under-provisioned for the traffic, not that anything went wrong upstream.

Both flags are on the query record and are surfaced as badges on the query detail page.

Retention

By default DBBat keeps query history forever — it is an audit trail, so nothing is deleted unless you ask for it. Set DBB_QUERY_STORAGE_RETENTION (or query_storage.retention) to a Go duration to enable a sweep:

DBB_QUERY_STORAGE_RETENTION=720h   # 30 days

The sweep runs once at startup and then hourly, deleting in batches:

• Connections that were closed before the connection cutoff, along with whatever queries and rows they still have.
• Queries executed before the query cutoff that hang off a session the first pass left alone.

Connections that are still open are never deleted, however old they are — the session may still be live. A connection's queries counter is a lifetime counter, not a count of retained rows.

Two windows: statements and the session ledger

The two cutoffs above are configured separately, because the two things they delete are not the same kind of data.

Variable	Deletes	Default
DBB_QUERY_STORAGE_RETENTION	Statements and their captured result rows	0 — keep forever
DBB_CONNECTION_RETENTION	Closed connections (cascading to any statements they still have)	Unset — inherit the query window

Statements and captured rows are the bulk of the store, and captured rows can hold customer data, so they are what an operator wants to expire after 30 or 90 days. A connection is one small row per session — who connected, from where, to which database, under which grant, when — the ledger a security review asks for a year later, and it costs almost nothing to keep.

DBB_QUERY_STORAGE_RETENTION=720h    # 30 days of statements
DBB_CONNECTION_RETENTION=8760h      # a year of sessions

DBB_CONNECTION_RETENTION=0 with a query window set keeps the session ledger forever while statements still expire.

Leaving DBB_CONNECTION_RETENTION unset makes it inherit the query window, so upgrading dbbat sweeps exactly what it swept before.

The connection window must be greater than or equal to the query window: deleting a session cascades to its statements, so a shorter one would delete query history earlier than you asked for. A shorter window, a malformed value on either side, or a non-zero connection window while queries are kept forever is a misconfiguration — dbbat disables both sweeps, logs a warning naming both values at startup, and deletes nothing. It never refuses to start over a retention typo.

With two windows, a closed connection can outlive all of its statements — in fact every closed session between the two cutoffs is in that state. The connection detail page says so explicitly ("past the retention window") instead of showing an empty list, and dbbat audit verify --queries counts those sessions under chains_emptied_by_retention rather than reporting them as tampering. See the audit chain notes.

Session captures are uploaded under an object key recorded on the connection row, so if you upload dumps to a bucket, keep the ledger window at least as long as the bucket's lifecycle policy — a deleted connection row leaves its object in place with nothing able to find it.

A crash or a SIGKILL never runs the normal session teardown, so those connections would stay "open" — and therefore un-reapable — forever. To stop that leak, dbbat marks the connections left open by a process that is no longer running as disconnected on its next start, before any proxy accepts, using each session's last activity time so retention still measures from when the session actually stopped. That covers both its own leftovers and those of any other replica that shut down cleanly or stopped heartbeating; a replica that is up and heartbeating is never touched, so a live session can never be closed out from under it. The second half is also re-run every few minutes by every running process, so a replica that crashes while the rest of the deployment stays up is reclaimed without waiting for an unrelated restart. See DBB_INSTANCE_ID for the registry and the grace period.

Set DBB_QUERY_STORAGE_RETENTION to 0 (the default), or leave it unset, to keep history forever. An unparseable value also leaves retention off and logs a warning at startup, rather than falling back to some other period.

Note this is separate from session packet dumps, which have their own DBB_DUMP_RETENTION (default 24h).

Connection Tracking

Queries are linked to connections. View connection details:

curl -H "Authorization: Bearer $DBBAT_API_KEY" \
  http://localhost:4200/api/v1/connections

A single connection can also be fetched directly:

curl -H "Authorization: Bearer $DBBAT_API_KEY" \
  http://localhost:4200/api/v1/connections/$CONN_UID

The web UI has a matching connection detail page.

Connection metadata includes:

• Source IP address
• Connecting user
• Target server
• Connection start, last-activity, and disconnect timestamps
• Aggregated query count and bytes transferred

Upstream Identity

DBBat does not only log queries on its own side — it also tags the upstream connection with the DBBat username, so the target database's own monitoring attributes activity to the real human instead of to the shared credentials DBBat connects with:

Engine	Field carrying the DBBat username
PostgreSQL	application_name
MySQL / MariaDB	program_name
Oracle	AUTH_PROGRAM_NM

This makes DBBat's query log correlatable with what a DBA sees in pg_stat_activity, v$session, SHOW PROCESSLIST, or engine-level audit logs — useful when someone spots a heavy query upstream and needs to know who to talk to.

Use Cases

Security Auditing

Track all database access for compliance:

• Who accessed what data
• When queries were executed
• What SQL was run

Performance Analysis

Identify slow queries:

• Sort by duration_ms
• Find patterns in slow queries
• Analyze query frequency

Debugging

Troubleshoot application issues:

• See exactly what queries your application runs
• Verify parameter values for prepared statements
• Check timing and sequencing across engines

Data Access Review

Regular reviews of who accessed sensitive data:

• Filter by table or keyword in sql_text
• Time-boxed reports
• User activity summaries