Postgres with Drizzle · Chapter 25 of 40

The pipeline

By the end of this chapter you can shape rows into exactly what a page needs inside one query: lists, joined strings, several counts at once, fallbacks for empty values, and the top row per member. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Database Powers
  • 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

Rohan's analytics dashboard needs the members grouped by status, each post's tags as one line of text, three counts on one tile, a name to show for every member even when they never set one, and each member's latest post.

So far, a query returns flat rows and JavaScript reshapes them: loop over the members to build a map of status to names, join tag names with commas, run three count queries, check every value for null, and for the latest post run one query per member. That is slow and a lot of code to get wrong. Postgres can return the final shape directly.

What you build: 5 tasks

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

  1. Task 1

    arrayAggUsers: members grouped by status

    Write arrayAggUsers(). It returns one row per status that has members, with the usernames of the members who have that status collected into an array, sorted by username.

    Return result.rows: objects with status and usernames (a JavaScript array of strings). Order: by status. With the check's members, online has alice and bob, and offline has carol.

  2. Task 2

    stringAggTags: a post's tags as one line

    Write stringAggTags(postId). postId is a number, the id of a post. It returns that post's id and the names of its tags joined into one string, sorted alphabetically and separated by a comma and a space. Reach the tags through post_tags, with joins that keep the post even when it has no tags.

    Return one object (the first row), not an array, with id and tag_list. With the check's tags, tag_list is graphql, javascript, typescript.

  3. Task 3

    conditionalAggregation: three counts in one query

    Write conditionalAggregation(). In one query over all members, it counts the members who are online, the members who are offline, and the members who are verified (is_verified is true).

    Return one object (the first row) with online_count, offline_count and verified_count, all numbers, not strings. With the check's five members: 3, 2 and 3.

  4. Task 4

    coalesceAndNullif: a name to show, and no empty bios

    Write coalesceAndNullif(userId). userId is a number, the id of a member. It returns that member's id, effective_name (their display_name, or their username when display_name is NULL) and bio_or_null (their bio, or NULL when the bio is an empty string).

    Return one object (the first row), not an array. For the check's member, who has no display name and an empty bio, effective_name is noname and bio_or_null is null.

  5. Task 5

    lateralJoinExample: each member's latest post

    Write lateralJoinExample(). For every member, it finds their newest post (latest created_at), with a lateral subquery. Members with no posts are kept, with null.

    Return result.rows: objects with id, username, latest_post_content and latest_post_date (the post's content and created_at), ordered by the member's id. With the check's data, user1's latest_post_content is Latest post and user2's is null.

What you will learn

  • ARRAY_AGG and STRING_AGG: a group of rows becomes one array or one string.
  • FILTER (WHERE ...): an aggregate that counts only some rows, so one query fills several counts.
  • COALESCE and NULLIF: a fallback for NULL, and an empty value turned into NULL.
  • LATERAL: a subquery that runs once for each row on its left.

More in Database Powers

Build The pipeline 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.