Postgres with Drizzle · Chapter 34 of 40
The production countdown
By the end of this chapter you can look inside the connection pool, reuse a prepared query, time a query, turn a database error into data, and read the database's vital signs. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Production Patterns
- 5 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.
Why you need this
MingleSphere launches on Friday. Vikram, the CTO, wants a health page for the on-call engineer: is the pool keeping up, how slow are queries, what went wrong when one failed, and how big and busy is the database.
So far your code sends a query and either gets rows or crashes. When a page is slow at 2 AM, "it crashed" tells nobody anything. The pg driver and Postgres already know the answers: the pool counts its connections, every result carries its columns, every error carries a code, and Postgres keeps statistics about itself. You only need to ask.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
getConnectionPoolConfig: what the pool is doing
Write getConnectionPoolConfig(). It is a plain function, not async: the check calls it without await. It reads the lab's pool from getPool().
Return an object with totalCount, idleCount and waitingCount (the pool's three counters, as it reports them now) and max (the most connections the pool may open, read from the options the pool was created with: 10). All four are numbers.
- Task 2
preparedStatementQuery: a member by username, prepared
Write preparedStatementQuery(username). username is a string. It runs a named prepared statement called find_user_by_username that selects the id, username and email of the member with that username.
Return result.rows: objects with exactly id, username and email, no other keys. With the check's members, asking for user1 gives one row.
- Task 3
measureQueryPerformance: how long, and which columns
Write measureQueryPerformance(query). query is a string of SQL. It runs that query through the pool and measures how long it took with performance.now().
Return an object with rowCount (the result's row count), durationMs (the milliseconds it took, a number) and fields (the names of the result's columns, in order: an array of strings, not Field objects).
- Task 4
gracefulErrorHandling: errors as data
Write gracefulErrorHandling(query). query is a string of SQL. It runs the query and never throws.
On success, return an object with success: true and rowCount (the result's row count). When the query fails, return an object with success: false, errorCode, errorMessage and errorSeverity: the code, message and severity of the error Postgres reported (for an unknown table, a message naming it and the severity ERROR).
- Task 5
getDatabaseStats: the database's vital signs
Write getDatabaseStats(). In one query, it reads four numbers about the current database: active_connections (rows in pg_stat_activity for the current database), database_size_bytes (the size from pg_database_size), table_count (rows in information_schema.tables in the public schema) and index_count (rows in pg_indexes in the public schema).
Return one object (the first row), not an array. active_connections, table_count and index_count are numbers; database_size_bytes stays a string, as a bigint arrives.
What you will learn
- The pool's counters: connections in total, idle, and requests waiting.
- Named prepared statements: a query Postgres parses once per connection and then reuses.
- performance.now() and result.fields: how long a query took and which columns it returned.
- Errors as data: code, severity and message on a caught error, and the catalog views that describe the database.
More in Production Patterns
Build The production countdown yourself
MingleSphereQL grows up: its backend talks to Postgres through Drizzle. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.