Postgres with Drizzle · Chapter 16 of 40
Going deeper
By the end of this chapter you can load nested data several levels deep in one query, follow a relation in both directions, and store and read threaded replies. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: JSON & Relations
- 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
Meera's post page shows the post, its author, and every comment with its commenter's name and photo. Members want to reply to a comment, not only to the post, and see the replies under it. A profile page shows a member's posts, each with its comments.
Chapter 14's with loads one level. Loading the post, then the comments, then one query per comment for its author, then one per comment for its replies, is the "N+1" pattern: a post with forty comments costs eighty queries, and the page slows down as it gets popular. with can nest, and Drizzle still sends one query.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
getPostWithCommentsAndAuthors: a post page in one query
Write getPostWithCommentsAndAuthors(postId). postId is a number. With Drizzle's relational query API it returns the post with that id, with its author and its comments, and each comment with its own author.
Return one object: the post row, plus author (the user row of the post's author; the check reads author.username) and comments (an array of comment rows, each with an author object; the check reads comments[0].author.username). In the check, user1 wrote the post and user2 wrote both comments.
- Task 2
createThreadedComment: a comment or a reply
Write createThreadedComment(postId, authorId, content, parentId). postId and authorId are numbers, content a string, parentId the id of the comment being answered, or null for a comment on the post itself. It adds one comment with those four values.
Return the inserted comment as one object, through Drizzle: the check reads parentId (null for the root, the root's id for the reply) and content.
- Task 3
getCommentThread: the replies to a comment
Write getCommentThread(parentId). parentId is a number, the id of a comment. With the relational query API it returns every comment whose parent_id is parentId, each with its author and its own replies.
Return an array of comment rows, each with author (the user row) and replies (an array of comments that answer it). Any order. In the check, the comment has two replies.
- Task 4
getUserWithPostsAndComments: a member, their posts, and the comments
Write getUserWithPostsAndComments(userId). userId is a number. With the relational query API it returns the member with that id, with their posts, and each post with its comments.
Return one object: the user row (the check reads username), plus posts, an array of post rows, each with comments, an array of comment rows. In the check, user1 has two posts, with three comments and one.
- Task 5
getPostsWithAllRelations: every post, fully loaded
Write getPostsWithAllRelations(). With the relational query API it returns every post with its author, its comments (each with its author) and its tag links, each link with its tag.
Return an array of post rows, each with author (a user row), comments (an array, each comment with author) and postTags (an array of links, each with tag, the tag row). Any order. In the check there is one post, with two comments and two tags.
What you will learn
- Nested with: inside a relation, another with for that relation's own relations.
- Both directions: many relations give arrays, one relations give an object.
- A table that points at itself: a comment's parent_id holds the id of the comment it answers; replies and parent follow it.
- Several relations at once: more keys in the same with.
More in JSON & Relations
Build "Going deeper" 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.