Postgres with Drizzle · Chapter 23 of 40
The vault
By the end of this chapter you can make several changes happen together or not at all, undo part of a transaction, and choose what a transaction sees of everyone else's changes. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Database Powers
- 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 shipped a feature that lets creators move likes from one of their posts to another. A week later a member reported that ten likes had vanished. The move is two updates: take likes off one post, add them to the other. The server restarted between the two, so the first one happened and the second never did. Sign-up has the same weakness: it creates the member and then a welcome post, and a failed second insert leaves a member with no post.
Two statements in a row are never safe on their own, and repairing by hand does not scale. Postgres can treat a group of statements as one: every change is kept, or none is.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
transferLikes: move likes from one post to another
Write transferLikes(fromPostId, toPostId, amount). All three are numbers. In one Drizzle transaction, it takes amount likes off the likes_count of the post fromPostId and adds them to the post toPostId, then reads both posts back.
Return an object with from and to: each the whole post row after the transfer, as a Drizzle select gives it, so that the check can read likesCount. With the check's posts (10 and 5 likes) and amount 3, from has 7 and to has 8.
- Task 2
transactionRollbackOnError: a throw undoes the change
Write transactionRollbackOnError(userId). userId is a number, the id of a member. It reads the member's bio, then runs a Drizzle transaction that changes that bio and throws an error, catches the error outside the transaction, and reads the bio again.
Return an object with rolledBack: true when the bio after is the same as before (the change was rolled back).
- Task 3
createUserWithPost: a member and their first post, together
Write createUserWithPost(username, email, postContent). All three are strings. In one Drizzle transaction, it inserts a member with that username and email, then a post by that member with that content.
Return an object with user and post: the two inserted rows as Drizzle returns them, so that the check can read user.username, post.content and post.authorId, which must equal user.id.
- Task 4
savepointExample: undo only the second change
Write savepointExample(userId). userId is a number, the id of a member. On one client from the pool, with raw SQL: begin a transaction, set the member's bio to first update, make a savepoint named sp1, set the bio to second update, roll back to sp1, and commit. Then read the bio and release the client.
Return an object with bio: the member's bio after the commit, which must be first update.
- Task 5
isolationLevelTest: a serializable transaction
Write isolationLevelTest(). On one client from the pool, it begins a transaction at the serializable isolation level, reads the transaction's isolation level, counts the members, and commits.
Return an object with isolationLevel (the level as Postgres reports it: serializable) and count (the number of members, a number, not a string). With the check's three members, count is 3.
What you will learn
- db.transaction(async (tx) => ...): Drizzle's transaction; it commits when the callback returns and rolls back when it throws.
- One connection: inside, every query goes through tx; outside, pool.connect() gives you one client for BEGIN and COMMIT.
- SAVEPOINT: a mark you can roll back to without losing the rest.
- Isolation levels: what a transaction sees of other connections' commits.
More in Database Powers
Build The vault 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.