Postgres with Drizzle · Chapter 14 of 40

The relational web

By the end of this chapter you can link a row to another, read rows from two tables together, keep the rows with nothing to match, and get nested answers such as a member with all their posts. 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 feed shows every post with its author's name. A profile page shows a member with their posts, and Rohan's dashboard needs how many posts each member has written, including those who wrote none.

A post holds only the id of its author. With what you know, you would select the posts, then run one more query per post to find its author: fifty posts, fifty-one queries. Or you would fetch both tables whole and match them up in JavaScript. A join asks Postgres to do the matching, in one query.

What you build: 5 tasks

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

  1. Task 1

    createPostForUser: a post that belongs to a member

    Write createPostForUser(userId, content). userId is a number, content a string. It adds one post whose author is the member with that id (author_id is userId) and whose content is content.

    Return the inserted post as one object, through Drizzle: the check reads content and authorId.

  2. Task 2

    getPostsWithAuthor: every post with its author

    Write getPostsWithAuthor(). It returns every post together with the member who wrote it, using an inner join of posts and users on the post's author_id.

    Return an array with one element per post, in the shape a join with an empty select gives: an object with posts (the whole post row) and users (the whole user row), so that the check can read users.username. Any order.

  3. Task 3

    getUserWithPosts: a member and all their posts

    Write getUserWithPosts(userId). userId is a number. It returns the member with that id, with all their posts nested inside, using Drizzle's relational query API.

    Return one object: the whole user row (the check reads username) with a posts key holding an array of that member's post rows (the check expects 3).

  4. Task 4

    getPostsByAuthorUsername: the posts of one member, by name

    Write getPostsByAuthorUsername(username). username is a string such as user1. It returns only the posts written by the member with that username, using an inner join of posts and users and a filter on the users table's username.

    Return an array in the join's shape, with posts and users in each element (the check reads users.username on every one). Any order. With the check's data, user1 has 2 posts and user2 has 3.

  5. Task 5

    countPostsPerUser: posts per member, zero included

    Write countPostsPerUser(). It returns one row per member, including members who wrote no posts, with how many posts each has written.

    Return an array of objects with username and postCount, where postCount is a number (0 for a member with no posts, not a string). Any order: the check finds each member by username. With the check's data, user1 has 4, user2 has 2 and user3 has 0.

What you will learn

  • Foreign keys: a row holds the id of the row it belongs to.
  • innerJoin: one answer row per matching pair, nested by table.
  • leftJoin: rows with no match are kept, with null on the other side, and how to count them as 0.
  • db.query with with: nested objects from the relations in the schema.

More in JSON & Relations

Build The relational web 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.