Postgres with Drizzle · Chapter 5 of 40

The column codex

By the end of this chapter you can store and read every kind of value a profile needs (text, numbers, true or false, JSON and moments in time), look inside a JSON column, and change a row that is already there. 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 richer profiles: a bio, a verified badge, a post counter, settings such as theme and plan, and "member since" dates. Rohan wants to know how many members are on the pro plan, which lives inside those settings.

So far every value you have stored was a short string. You could keep the settings as JSON text and JSON.parse it in JavaScript, but then Postgres cannot look inside it: to find the pro members you would fetch everyone and parse every row. Dates kept as strings sort and compare badly. And chapter 2 showed that a value can arrive as the wrong type, a count as '16'. You need to know what each Postgres type becomes in TypeScript, and how to reach inside JSON.

What you build: 5 tasks

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

  1. Task 1

    insertUserWithAllTypes: a full profile

    Write insertUserWithAllTypes(data). data has username, email, displayName, bio (text), isVerified (a boolean), postCount (a number) and metadata (a plain object). Add one member with all of these values.

    Return the new member's whole row as one object, through Drizzle: the check reads bio, isVerified (true), postCount (the number 42) and metadata (equal to the object it passed), under those camelCase names.

  2. Task 2

    queryJsonbField: members by one setting

    Write queryJsonbField(key, value). Both are strings. It finds the members whose metadata has that key with exactly that value (the key's value as text equals value), for example key plan and value pro.

    Return an array of the matching member rows; the check only counts them, so raw rows from the pool are fine. Any order.

  3. Task 3

    getUserTimestamps: when a member joined and last changed

    Write getUserTimestamps(userId). userId is a number. It reads the created_at and updated_at of the member with that id.

    Return one object (not an array) with the keys createdAt and updatedAt, each a JavaScript Date.

  4. Task 4

    findVerifiedUsers: members with the badge

    Write findVerifiedUsers(). It finds every member whose is_verified is true.

    Return an array of whole user rows through Drizzle, so each has isVerified under its camelCase name. Any order. With the check's three members, two come back.

  5. Task 5

    updateUserMetadata: replace a member's settings

    Write updateUserMetadata(userId, metadata). userId is a number; metadata is a plain object. It changes the metadata of the member with that id to exactly this object, replacing what was there, and changes no other member.

    Return the updated row as one object, with its id and the new metadata.

What you will learn

  • Column types: text, integer, boolean, jsonb and timestamptz, and the JavaScript value each one arrives as.
  • ->>: one key's value from a jsonb column, as text, inside a WHERE.
  • db.update(table).set(...).where(...): change existing rows and get them back with .returning().

More in Core CRUD & Querying

Build The column codex 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.