Queries: select, insert, delete

Drizzle queries read like the SQL they generate. The verbs (select, insert, delete) line up with the HTTP methods you already know. Once you have the schema, the query builder feels like writing English with autocomplete.

after/db/index.ts
typescript
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export const db = drizzle(pool);

The one file that connects DATABASE_URL to Drizzle. Every query in the app goes through this db object.

This is the whole database client. A pg connection pool reads DATABASE_URL, drizzle() wraps it, and the exported db object is what every service imports. Create it once, import it everywhere.

after/services/book.service.ts
typescript
import { db } from '../db';
import { books, Book, NewBook } from '../db/schema';
import { eq } from 'drizzle-orm';

export async function getAllBooks(): Promise<Book[]> {
  return await db.select().from(books);
}

export async function getBookById(id: number): Promise<Book | undefined> {
  const result = await db.select().from(books).where(eq(books.id, id));
  return result[0];
}

export async function createBook(input: NewBook): Promise<Book> {
  const result = await db.insert(books).values(input).returning();
  return result[0];
}

export async function deleteBook(id: number): Promise<boolean> {
  const result = await db
    .delete(books)
    .where(eq(books.id, id))
    .returning({ deletedId: books.id });
  return result.length > 0;
}

The same service interface from the REST APIs phase, now backed by Postgres.

One detail worth a second look: .returning(). By default, INSERT and DELETE in Postgres do not return the row they touched. .returning() asks Postgres to send it back. That is how createBook gives you the new id and createdAt without a separate SELECT.

eq(books.id, id) is the Drizzle way of saying WHERE books.id = $1. The actual id value is passed as a parameter, not interpolated into the SQL string. This makes the query immune to SQL injection. It is one of the underrated benefits of using a query builder over raw string concatenation.

Postgres receives the query and the parameters separately. The parameters are values, never code. So even if a user tries to pass something like 1; DROP TABLE books; as an id, Postgres treats the entire string as a value, not as a new statement. The hardening phase goes deeper on security patterns. For now, know that parameterized queries are non-negotiable.

Quiz: Quiz

Loading practice…

AI prompt: Try it: see the SQL Drizzle generates

Loading practice…