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.

  1. 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.

  2. 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.

  3. 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).

  4. 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).

  5. 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.