Postgres with Drizzle · Chapter 10 of 40

The great edit

By the end of this chapter you can change members safely: record when a row changed, add to a counter without losing a step, change only the fields you were given, and change a row only if it is still as you expect. 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's profile page lets members edit their display name, set a status such as away (with "last updated" under it) and fill in the fields they want. A banner asks members without a bio to write one. Vikram wants every member's post counter to stay right when hundreds of people post at the same moment.

Chapter 5's update can do each of these, but the obvious way breaks: read a member, change the object in JavaScript, write it back, and two requests at the same moment overwrite each other. One post is never counted; a bio written in one tab is replaced from another. Let Postgres do the change, and the check, inside the update.

What you build: 5 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    updateUserDisplayName: a new display name

    Write updateUserDisplayName(userId, newName). userId is a number, newName a string. It changes the display_name of the member with that id to newName, and changes no other member.

    Return the updated row as one object, through Drizzle, so that the check can read displayName and id under those camelCase names.

  2. Task 2

    updateUserStatus: a new status, and when it changed

    Write updateUserStatus(userId, status). userId is a number; status is one of online, offline, away or busy. In one update, it sets that member's status to status and their updated_at to the current moment.

    Return the updated row as one object, through Drizzle: the check reads status and updatedAt.

  3. Task 3

    incrementPostCount: one more post

    Write incrementPostCount(userId). userId is a number. It adds 1 to the post_count of the member with that id, with the addition done by Postgres inside the update (not read, add one in JavaScript, then write).

    Return the updated row as one object, through Drizzle: the check reads postCount, which goes from 0 to the number 1.

  4. Task 4

    updateMultipleFields: only the fields given

    Write updateMultipleFields(userId, fields). userId is a number; fields is an object that may have displayName, bio and avatarUrl, each a string. It changes, in one update, exactly the fields present in the object, on the member with that id. Fields that are missing keep their values.

    Return the updated row as one object, through Drizzle: the check passes all three fields and reads displayName, bio and avatarUrl.

  5. Task 5

    conditionalUpdate: a bio only if there is none

    Write conditionalUpdate(userId, newBio). userId is a number, newBio a string. It sets that member's bio to newBio only if their bio is NULL right now; a member who already has a bio keeps it.

    Return the updated row as one object when the bio was set, and null (not undefined) when nothing changed. The check first empties the member's bio, then calls it twice: the first call sets the bio, the second must return null.

What you will learn

  • updatedAt: no column changes by itself; you set it.
  • ` sql${column} + 1 `: arithmetic done by Postgres, so updates at the same moment do not lose a step.
  • Partial set: keys that are undefined are left out.
  • Conditional updates: an extra condition in where, and [] when it fails.

More in Core CRUD & Querying

Build The great edit 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.