Postgres with Drizzle · Chapter 1 of 40

The genesis of MingleSphereQL

By the end of this chapter you can send SQL to Postgres from TypeScript, pull the value you want out of the answer, and read how busy the connection pool is. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Foundations
  • 5 tasks
  • PostgreSQL
  • Free chapter

Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.

Why you need this

Vikram wants a health check for MingleSphere before any feature ships: a small page the team can open when something feels slow. It should say whether the app can reach Postgres at all, which database it is talking to, which Postgres version runs there, which extensions are installed (later chapters need PostGIS, pgvector and pg_trgm), and how many connections are busy.

You could type these questions into psql by hand, but a health check runs inside the app, from code, every time someone opens the page. And the app cannot open a fresh connection for every question: each one costs a handshake with the server, and this Postgres allows only 100 connections in total. The app needs a pool of connections it can reuse, and a way to ask questions through it.

The lesson

Sending a query

getPool() from shared/connection.ts returns the app's one shared pool. pool.query takes SQL as a string and returns a promise, so you await it:

const pool = getPool();
const result = await pool.query('SELECT 2 + 2 AS answer');

console.log(result.rows);
console.log(result.rows[0]);
console.log(result.rows[0].answer);
console.log(result.rowCount, result.command);
[ { answer: 4 } ]
{ answer: 4 }
4
1 SELECT

rows is always an array, even for one row. Each row is an object whose keys are the column names, so AS answer decides the key. Leave the name out and Postgres invents one:

(await pool.query('SELECT 2 + 2')).rows
// [ { '?column?': 4 } ]

Forget the await and result is still a promise, with no rows on it. The check then says TypeError: Cannot read properties of undefined (reading '0').

Asking Postgres about itself

Some questions are functions you SELECT. The key is the function's name:

(await pool.query('SELECT current_user')).rows
// [ { current_user: 'app' } ]

Others are settings, read with SHOW. The key is the setting's name, and the value is always text, even when it looks like a number:

(await pool.query('SHOW timezone')).rows
// [ { TimeZone: 'Etc/UTC' } ]
(await pool.query('SHOW max_connections')).rows
// [ { max_connections: '100' } ]

A misspelt name fails the whole query. The check prints Postgres's own words, such as error: unrecognized configuration parameter "time_zone" or error: column "current_usr" does not exist.

The check connects to a database called minglesphereql_test, while psql in your terminal opens minglesphereql. So ask the database for its name; never type a name into your code.

The catalog: tables about the database

Postgres keeps its own facts in catalog tables you can query like any other. pg_database has one row per database:

const result = await pool.query(
  'SELECT datname, datallowconn FROM pg_database ORDER BY datname',
);
console.log(result.rows.map((row) => row.datname));
[
  'minglesphereql',
  'minglesphereql_test',
  'postgres',
  'template0',
  'template1'
]

map turns the row objects into plain strings. Return result.rows itself and you hand back objects where strings were wanted.

The connection pool

The pool opens connections only when it needs them (at most ten here), lends one to each query and takes it back afterwards, open and idle, ready for the next query. When all ten are busy, new queries wait in line. The pool counts all this in plain number properties, so reading them needs no SQL and no await:

const pool = getPool();
console.log(pool.totalCount, pool.waitingCount);  // 0 0
await pool.query('SELECT 1');
console.log(pool.totalCount, pool.waitingCount);  // 1 0
const running = Array.from({ length: 12 }, () =>
  pool.query('SELECT pg_sleep(1)'),
);
await new Promise((r) => setTimeout(r, 400));
console.log(pool.totalCount, pool.waitingCount);  // 10 2
await Promise.all(running);
console.log(pool.totalCount, pool.waitingCount);  // 10 0

Your turn

Your five tasks are at the top, all in exercise.ts, one function each. Until you write one, Check says Error: Not implemented. Watch the shape: expected [ { connected: 1 } ] to deeply equal { connected: 1 } means you returned the whole rows array where one row was wanted, and expected 'object' to be 'string' means you returned a row where its value was wanted.

What you build: 5 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    verifyConnection: is Postgres there?

    Write verifyConnection(). It proves the database answers by running the smallest possible query through getPool(): select the number 1 under the column name connected.

    Return the first row of the answer, which must be exactly the object { connected: 1 }: one key, connected, holding the number 1. Not the rows array, not the whole result.

  2. Task 2

    getDatabaseName: which database is this?

    Write getDatabaseName(). It asks Postgres for the name of the database the pool is connected to, using the Postgres function that reports the current database.

    Return the name as a string, not a row object. Do not type the name into your code: the check runs against a different database than your terminal's psql.

  3. Task 3

    getServerVersion: which Postgres is running?

    Write getServerVersion(). It reads the server_version setting of the Postgres server.

    Return its value as a string that starts with the version number (like 16 followed by more text). The long description that begins with the word PostgreSQL does not count.

  4. Task 4

    listExtensions: which extensions are installed?

    Write listExtensions(). It reads the pg_extension catalog table, which has one row per extension installed in the current database; the extension's name is in the extname column.

    Return an array of the names as plain strings (plpgsql, which every database has, must be among them), not an array of row objects. Sorting them by name is tidy, but the check accepts any order.

  5. Task 5

    getConnectionPoolStatus: how busy is the pool?

    Write getConnectionPoolStatus(). It reports the shared pool's three counters: how many connections it has open, how many of those are idle, and how many queries are waiting for a connection.

    Return an object with exactly three keys, totalCount, idleCount and waitingCount, each holding a number. Any values are fine: the check reads the types, not the counts.

What you will learn

  • pool.query: sends one SQL string and gives back a result object.
  • result.rows: the answer as an array of objects, one per row, keyed by column name.
  • Functions, settings and the catalog: Postgres answers questions about itself through ordinary queries.
  • The pool's counters: how many connections exist, how many are idle, how many queries wait.

More in Foundations

Build The genesis of MingleSphereQL 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.

Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.