Postgres with Drizzle · Chapter 3 of 40

Opening the gates

By the end of this chapter you can add rows with Drizzle, one or many in a single statement, get back exactly the fields you need, and count what is there. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Foundations
  • 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 is opening MingleSphere's beta. The sign-up form adds one member and shows them their new profile. An invite import adds a whole list of friends at once. The sign-up API answers with only the new member's id and username, for the profile link. And the launch page shows how many members there are.

With pool.query from chapters 1 and 2 you would write every INSERT by hand: list the columns, keep $1, $2, $3 in step with an array of values, and build a longer string for each extra row. The rows come back with snake_case keys like display_name, while the rest of the app uses displayName. A typo in a column name is found only when the query runs. Drizzle builds that SQL for you from src/schema.ts.

What you build: 5 tasks

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

  1. Task 1

    insertSingleUser: sign up one member

    Write insertSingleUser(userData). userData is an object with username, email and displayName. Add one member to the users table with exactly those values, using Drizzle's insert.

    Return the new member's whole row as one object (not an array), as Drizzle gives it: with its id and the schema's camelCase keys, such as displayName.

  2. Task 2

    insertMultipleUsers: import a list of invites

    Write insertMultipleUsers(usersData). usersData is an array of objects, each with username and email. Add every one of them to the users table, in one insert.

    Return an array of the new members' whole rows, in the same order as usersData.

  3. Task 3

    insertUserWithDefaults: let the table fill in the rest

    Write insertUserWithDefaults(username, email). Both are strings. Add a member with only these two values and let the users table's defaults fill in everything else.

    Return the new member's whole row as one object, with the schema's camelCase keys: the check reads status (offline), isVerified (false), postCount (0), and createdAt and updatedAt (JavaScript Dates).

  4. Task 4

    insertAndReturnSpecificFields: answer with id and username only

    Write insertAndReturnSpecificFields(userData). userData has username, email and displayName. Add the member as in task 1.

    Return one object with exactly two keys, id first and then username, and no other keys. The check compares the list of keys, so the order counts.

  5. Task 5

    getInsertedUserCount: how many members?

    Write getInsertedUserCount(). It counts the rows in the users table.

    Return the count as a JavaScript number (the check adds two members and expects the number 2), not a string and not the row it arrives in.

What you will learn

  • db.insert(table).values(...): one object adds one row, an array of objects adds many rows in one statement.
  • .returning(): get the stored rows back, defaults filled in, or only the fields you name.
  • count(): how many rows a table has, as a number.

More in Foundations

Build "Opening the gates" 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.