Postgres with Drizzle · Chapter 13 of 40
The batch express
By the end of this chapter you can add, update and delete many rows in one statement, and decide what happens when a row you insert already exists. 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 has a spreadsheet of five hundred beta testers from a partner, to import tonight. Members who sign up with Google sometimes press the button twice, and the second request must update the account, not fail. Vikram wants a moderators' tool that sets thirty spam accounts to away, or deletes them, in one click.
With one insert per member, the import is five hundred round trips, and a duplicate halfway through throws and leaves half of it in. Checking first ("does this username exist?") and then inserting has a gap: two requests can both check, both see nothing, and the second still hits the unique constraint. Postgres can do each of these as one statement.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
bulkInsertUsers: many members in one insert
Write bulkInsertUsers(usersData). usersData is an array of objects, each with a username and an email. It adds every one of them to the users table in a single insert statement.
Return an array of the inserted rows, through Drizzle, in the same order as usersData: the check passes three members and reads each row's username in order.
- Task 2
upsertUser: insert, or update the member who exists
Write upsertUser(userData). userData has a username, an email and a displayName. In one statement it adds the member; if a member with that username already exists, it updates that member instead, setting their display_name to the new displayName and their updated_at to the current moment.
Return the row, inserted or updated, as one object, through Drizzle: the check reads displayName. It calls the function twice with the same username and email, and afterwards there must be exactly one user.
- Task 3
insertOrIgnore: insert, or skip without an error
Write insertOrIgnore(userData). userData has a username and an email. It adds the member; if a member with that username already exists, it adds nothing and throws no error.
Return the array the insert gives back, through Drizzle: one row when the member was added, an empty array when it was skipped. The check calls it twice with the same username but a different email the second time; afterwards there must be exactly one user.
- Task 4
batchUpdateStatuses: one status for a list of members
Write batchUpdateStatuses(userIds, newStatus). userIds is an array of member ids (numbers), newStatus one of online, offline, away or busy. In one update it sets the status of every member whose id is in the list to newStatus. Members not in the list keep their status.
Return an array of the updated rows, through Drizzle: the check passes three of its four members' ids and expects three rows, each with status busy.
- Task 5
bulkDeleteByIds: delete a list of members
Write bulkDeleteByIds(userIds). userIds is an array of member ids (numbers). In one delete it removes every member whose id is in the list, and nobody else.
Return an array of the deleted rows, through Drizzle: the check passes three of its five members' ids, expects three rows back, and two members left.
What you will learn
- An array in .values(): many rows in one insert, as in chapter 3, and all or nothing.
- onConflictDoUpdate: insert, or update the row that is already there (an upsert).
- onConflictDoNothing: insert, or quietly skip, and [] tells you which happened.
- inArray in an update or a delete: change or remove a whole list of rows at once.
More in JSON & Relations
Build The batch express 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.