Postgres with Drizzle · Chapter 4 of 40
Finding your people
By the end of this chapter you can ask Drizzle for just the members you want: an exact match, a match on part of a name in any case, either of two conditions, and one page at a time. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Core CRUD & Querying
- 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 is building the "find your friends" page. It has a box for an exact username, a tab of members who are online, a search by display name (people remember "Anya", not "anya_dev_92"), a list you can browse page by page, and a sign-in form that accepts an email or a username.
In chapter 3 you counted the whole table. With only that, you would select every member and search the array in JavaScript: half a million rows sent over the network to keep one. Postgres can find the rows itself, if you tell it which ones.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
findUserByUsername: one member by exact username
Write findUserByUsername(username). username is a string. It finds the member whose username is exactly that string.
Return that member's whole row as one object, or null when nobody has that username (null, not undefined and not an empty array).
- Task 2
findUsersByStatus: everyone with one status
Write findUsersByStatus(status). status is a status name such as online or offline. It finds every member whose status is exactly that one.
Return an array of whole user rows, empty when nobody has that status. Any order. With the check's five members, online gives all five and offline gives none.
- Task 3
searchUsersByName: part of a display name, any case
Write searchUsersByName(searchTerm). searchTerm is a string. It finds the members whose display_name (displayName in the schema) contains searchTerm anywhere, ignoring upper and lower case.
Return an array of whole user rows. Any order. With the check's members, user and USER both find all five, and User 3 finds only User 3.
- Task 4
findUsersWithPagination: one page of members
Write findUsersWithPagination(limit, offset). Both are numbers: limit is how many members a page holds, offset is how many rows to skip first (a number of rows, not a page number).
Return an array of at most limit whole user rows, sorted by id from smallest to largest, starting after the first offset rows. Two pages in a row must not share a member.
- Task 5
findUserByEmailOrUsername: sign in with either
Write findUserByEmailOrUsername(email, username). Both are strings. It finds a member whose email is the given email or whose username is the given username: either one matching is enough.
Return the first matching member's whole row as one object, or null when neither matches.
What you will learn
- eq: keep the rows where a column equals a value, and what to return when none does.
- ilike: match part of a text, ignoring case, with % wildcards.
- or: keep a row when either condition holds.
- orderBy, limit and offset: one page of a sorted list.
More in Core CRUD & Querying
Build "Finding your people" 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.