Insert users and notes

An insert should contain only the values the caller actually owns. The ID, the timestamp, the defaults: those belong to the database. Drizzle makes this natural, because it derives the accepted insert shape from the table.

Let’s create our first user:

import { db } from './db'
import { users, notes } from './db/schema'

const [user] = await db
  .insert(users)
  .values({
    email: '[email protected]',
    name: 'Flavio',
  })
  .returning()

console.log(user)

{ id: 1, email: '[email protected]', name: 'Flavio' }

We passed two fields. TypeScript required both, because both are notNull() without a default. We did not pass id, and TypeScript didn’t ask for it, because it’s auto-incremented.

Use the returned row

.returning() gives back the rows the statement created, including the generated id. SQLite supports this, and so does PostgreSQL. It returns an array, which is why I destructure the first element.

The alternative is a second query: insert, then select by email to find the ID. Don’t. It’s slower, and between the two statements another process could change things. The row you get from returning() is the truth about what was written.

Now a note for that user:

const [note] = await db
  .insert(notes)
  .values({
    authorId: user.id,
    title: 'First note',
  })
  .returning()

body was omitted and got its default ''. createdAt was omitted and SQLite filled in the timestamp. Both show up in the returned row.

Expected failures

Insert the same email twice and the database says no:

UNIQUE constraint failed: users.email

Insert a note with authorId: 999 and you get:

FOREIGN KEY constraint failed

Drizzle wraps these in a DrizzleQueryError, with SQLite’s message in the cause. A duplicate email is a normal outcome in a signup flow, not a bug. Catch it and turn it into a message like “that email is already registered”. Don’t show the raw SQL to the user, and don’t assume every database error means the same thing. Check the message before you translate it.

Never spread the request body

This is the mistake I see most often. A handler receives JSON and does .values({ ...request.body }). TypeScript is happy, because the type of request.body is probably any.

But the browser sent that JSON. It could include an authorId to plant a note on another user’s account, or an id you never meant to accept. TypeScript checked your code, not the network.

Validate the request at runtime, then build the insert object field by field, with the author ID coming from the session, never from the body. We formalize this split in the types lesson.