Postgres with Drizzle · Chapter 6 of 40
The gatekeepers
By the end of this chapter you can let Postgres refuse bad data, catch the refusal in TypeScript, and tell from its code which rule was broken. 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 wants the sign-up form to say "That username is taken" instead of showing an error page. Vikram wants the rules kept in the database, because import scripts and people with psql write to it too.
You could check in JavaScript first: select the username, and insert only if nobody has it. Two people signing up as the same name at the same moment both see "free", and both inserts go in. Only the database sees every write, so only the database can refuse the second one. When it does, the insert throws, and your code has to recognise which rule said no.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
testUniqueConstraint: a username that is taken
Write testUniqueConstraint(). Through the raw pool, add two members with the same username and different emails, so that the second insert breaks the UNIQUE rule on username.
Catch the error and return its code, a string: the check expects '23505'. If no error is caught, return an empty string.
- Task 2
testNotNullConstraint: a member with no username
Write testNotNullConstraint(). Through the raw pool, add a member with only an email and no username, which breaks the NOT NULL rule on username.
Catch the error and return its code, a string: the check expects '23502'. If no error is caught, return an empty string.
- Task 3
testForeignKeyConstraint: a post by nobody
Write testForeignKeyConstraint(). Through the raw pool, add a post whose author_id belongs to no member (the check empties the tables first, so any number will do), which breaks the FOREIGN KEY rule from posts to users.
Catch the error and return its code, a string: the check expects '23503'. If no error is caught, return an empty string.
- Task 4
testCheckConstraintOnLength: a rule of your own
Write testCheckConstraintOnLength(). Through the raw pool, add a CHECK rule named chk_username_length to the users table, requiring the length of username to be at least 3. Then add a member with the two-letter username ab, which breaks it.
Return the caught error's code, a string: the check expects '23514'.
Afterwards the rule must be gone: drop it whatever happens, so that later tasks and your next Check start without it.
- Task 5
testDefaultValues: what Postgres fills in
Write testDefaultValues(). Add a member with only a username and an email, and read back the whole new row.
Return an object with hasCreatedAt (true when created_at is not null), hasStatus (true when status is not null) and statusValue (the member's status: the table's default, offline, which the check expects).
What you will learn
- Constraints: UNIQUE, NOT NULL, FOREIGN KEY and CHECK, rules Postgres checks on every insert and update.
- err.code: the five-character code that says which rule broke.
- try, catch and finally: catching a refusal, and removing a rule you added for a test whatever happens.
- Defaults: what Postgres fills in when you leave a column out.
More in Core CRUD & Querying
Build The gatekeepers 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.