Postgres with Drizzle · Chapter 12 of 40
The clean sweep
By the end of this chapter you can remove rows for good, hide them so they can come back, list only the rows still live, and know what Postgres removes along with a row. 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 settings page is getting a "Delete my account" button. A member who presses it gets thirty days to change their mind: the account must disappear from the site at once, but come back if they ask. Vikram wants spam accounts removed for good, together with everything they posted.
Nothing you have written so far removes a row. Blanking a member's fields with an update leaves their posts on show, and a row you really erase cannot be given back on day twelve. You need two kinds of delete, a filter that hides the soft-deleted rows, and to know what else a delete takes with it.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
hardDeleteUser: remove a member for good
Write hardDeleteUser(userId). userId is a number. It deletes the member with that id from the users table for good, and no other member: afterwards no row with that id is left.
Return the deleted row as one object, through Drizzle: the check reads its id.
- Task 2
softDeleteUser: mark a member as deleted
Write softDeleteUser(userId). userId is a number. It keeps the member's row but sets their deleted_at to the current moment, on that member only.
Return the updated row as one object, through Drizzle: the check reads id and deletedAt, which must hold a date.
- Task 3
findActiveUsers: only members not deleted
Write findActiveUsers(). It returns the members who are not soft-deleted: their deleted_at is NULL. The check soft-deletes user1 first, so user2 and user3 should come back.
Return an array of whole user rows, as a plain select gives them. Any order.
- Task 4
cascadeDeleteTest: a member's posts go with them
Write cascadeDeleteTest(userId). userId is a number. It adds one post for that member (author_id is userId, any content), then deletes the member for good, then counts two things: the users left with that id, and the posts left whose author_id is that id.
Return an object with userDeleted, a boolean that is true when no user with that id is left, and postsRemaining, the number of posts left for that author as a number (not a string). The foreign key from posts to users is ON DELETE CASCADE, so postsRemaining should be 0.
- Task 5
restoreSoftDeletedUser: bring a member back
Write restoreSoftDeletedUser(userId). userId is a number. The check soft-deletes that member first. It sets the member's deleted_at back to NULL, on that member only.
Return the updated row as one object, through Drizzle: the check reads id, and deletedAt must be null (not undefined).
What you will learn
- db.delete(table).where(...): remove rows for good, with .returning() to see what went.
- Soft delete: an update that stamps deleted_at, so the row stays but counts as gone.
- isNull(...deletedAt): the filter every list of live rows needs.
- ON DELETE CASCADE: rows that point at a deleted row go with it.
More in JSON & Relations
Build The clean sweep 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.