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