Postgres with Drizzle · Chapter 39 of 40
The integrity guardians
By the end of this chapter you can make Postgres keep a computed column right, refuse overlapping bookings, and apply one named rule to every column that needs it. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Mastery
- 9 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 meetup feature lets members book a venue for a time slot. Last weekend two members got the same hall for overlapping hours. The code checked for a clash, then inserted; two requests checked at the same moment, both saw no clash, and both inserted. The events page also showed a three-hour workshop as one hour long: someone moved its end time and forgot to update duration_hours.
Checks in the app are good for friendly messages, but they run outside the database, so a race, a script or a forgotten line gets past them. Anya wants these rules inside Postgres, where every insert and update meets them.
What you build: 9 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
createEventTableWithGeneratedColumn: a duration that keeps itself right
Write createEventTableWithGeneratedColumn(). It drops the table events if it exists, then creates events with the columns id (serial primary key), title (text, not null), starts_at and ends_at (both timestamptz, not null), and duration_hours: a numeric column generated always, and stored, as the number of hours from starts_at to ends_at (the seconds between them divided by 3600.0).
Send raw SQL with getPool().query. It returns nothing. Afterwards: information_schema.columns shows duration_hours with is_generated ALWAYS.
- Task 2
createBookingTableWithExclusion: no double bookings
Write createBookingTableWithExclusion(). It creates the extension btree_gist if it does not exist, drops venue_bookings if it exists, and creates venue_bookings with the columns id (serial primary key), venue_id (integer, not null), starts_at and ends_at (timestamptz, not null) and booked_by (integer, not null), and an exclusion constraint using gist that refuses two rows with the same venue_id whose time ranges, tstzrange(starts_at, ends_at), overlap.
It returns nothing. Afterwards: venue_bookings has an exclusion constraint (contype x in pg_constraint).
- Task 3
testOverlapPrevention: an overlapping booking is refused
Write testOverlapPrevention(venueId, start1, end1, start2, end2). venueId is a number; the four times are strings such as 2025-06-01 09:00:00+00. It inserts a booking of venueId from start1 to end1 with booked_by 1, then tries a second booking of venueId from start2 to end2 with booked_by 2.
Return { blocked: true, errorCode } when the second insert throws, errorCode being the error's code (23P01 for an exclusion violation), and { blocked: false } when it succeeds. The check creates the table with your task 2 function first, then books 09:00 to 12:00 and 11:00 to 14:00.
- Task 4
testOverlapPrevention: back-to-back bookings are fine
The same testOverlapPrevention must return { blocked: false } when the two bookings do not overlap: the check books venue 1 from 09:00 to 12:00 and from 13:00 to 16:00.
- Task 5
createBookingTableWithExclusion: other venues do not clash
The constraint from task 2 must compare bookings of the same venue only. The check creates the table with createBookingTableWithExclusion(), books venue 1 from 09:00 to 12:00 and venue 2 from 10:00 to 13:00, and expects the second insert to succeed.
- Task 6
createDomainTypes: three named rules
Write createDomainTypes(). It drops the domains positive_amount, email_address and short_text if they exist (with cascade), then creates them: positive_amount, a numeric whose value must be greater than 0; email_address, a text whose value must contain @ (a regular expression match); short_text, a text at most 280 characters long.
It returns nothing. Afterwards: all three exist as domains in pg_type.
- Task 7
useDomainsInTable: the domains at work
Write useDomainsInTable(). It drops donations if it exists and creates it with id (serial primary key), amount (positive_amount, not null), email (email_address, not null) and note (short_text). Then it tries four inserts, each on its own: a valid one (amount 10, email a@b.com, note Thanks), an amount of -5, an email of invalid, and a note of 281 characters (with an otherwise valid row).
Return { validInserted, negativeFailed, emailFailed, longTextFailed }: validInserted is true when the valid insert worked, and each of the other three is true when its insert threw. With working domains all four are true. The check runs your createDomainTypes() first.
- Task 8
getGeneratedColumnValues: Postgres fills in the duration
Write getGeneratedColumnValues(eventTitle, startsAt, endsAt). The times are strings such as 2025-06-01 09:00:00+00. It inserts an event with that title, starts_at and ends_at into events, giving no duration.
Return the inserted row as one object (not an array), with every column, duration_hours included. For 09:00 to 12:00 duration_hours is 3; it arrives as a string, because node-postgres returns numeric as text. The check creates events with your task 1 function first.
- Task 9
createEventTableWithGeneratedColumn: recomputed after an update
The duration_hours column from task 1 must be recomputed when a row changes. The check inserts an event from 10:00 to 12:00 (2 hours), moves its end to 15:00, and expects 5.
What you will learn
- Generated columns: a column Postgres computes from others in the row, on every insert and update.
- Exclusion constraints: no two rows whose ranges overlap.
- Domains: a type with a rule attached, reused by many columns.
More in Mastery
Build The integrity guardians 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.