Drizzle ORM Basics: SQL-First Type-Safe Queries

Quick Answer

Drizzle is a TypeScript ORM where you declare your tables in TypeScript and query with a builder that closely mirrors SQL: select().from(users).where(eq(users.id, 1)). It infers row types from the schema, so a wrong column or a wrong type fails at compile time. It runs on Postgres, MySQL, and SQLite plus their serverless variants, and ships drizzle-kit to generate and run migrations from the schema.

SQL-first: what it means

Prisma-style ORMs give you their own method names, findMany, include, and their own mental model layered over the database. Drizzle's query builder maps almost one-to-one onto SQL clauses instead, so if you know SQL you already know the API.

This query, run for the article, shows the correspondence:

db.select().from(students)
  .where(and(eq(students.city, 'Pune'), gte(students.score, 60)))

// select "id", "name", "city", "score" from "students"
// where ("students"."city" = ? and "students"."score" >= ?)
// params: ["Pune", 60]

Every query also exposes a .toSQL() method that returns exactly the SQL string and the bound parameters it will send, before it runs. That means no query planner quietly turning one call into a dozen round trips, and no guessing about what an ORM relation compiles to. What you build is what executes. The cost of that transparency is that Drizzle does less for you, which the later sections get into.

Defining a schema

Tables are plain TypeScript objects. This schema was used with drizzle-orm 0.45 and better-sqlite3 13:

import { sqliteTable, integer, text } from 'drizzle-orm/sqlite-core';

export const students = sqliteTable('students', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  city: text('city').notNull(),
  score: integer('score').notNull().default(0),
});

This object is the single source of truth for both your types and your database structure. drizzle-kit generate diffs the current schema against previous migrations and writes SQL migration files; drizzle-kit migrate applies the pending ones. You review the generated SQL before it runs, so a rename you did not intend shows up in the diff rather than in production. The column modifiers do double duty: notNull, default, and primaryKey shape the generated CREATE TABLE, and the same declarations drive the inferred types, so a notNull column with no default becomes a required field on insert.

Querying

You get a database handle by wrapping a driver instance, then build queries against the schema:

import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

const db = drizzle(new Database('app.db'));

const rows = db.select().from(students)
  .where(eq(students.city, 'Pune')).all();

const bumped = db.update(students)
  .set({ score: sql`${students.score} + 5` })
  .where(eq(students.name, 'Ravi'))
  .returning().all();
// -> [ { id: 2, name: 'Ravi', city: 'Pune', score: 60 } ]

The filtered select returned only the matching rows and the update with .returning() handed back the changed row, both confirmed at runtime. returning() works on insert, update, and delete for SQLite and Postgres, which saves a follow-up select; MySQL does not support it. The query builder is chainable and lazy, so nothing runs until you call a terminal method. One driver detail matters early: better-sqlite3 is synchronous, so .all(), .get(), and .run() return immediately with no await. The Postgres and MySQL drivers return promises instead, so the same query code needs await there, and mixing the two models up is a common first-day error.

Type inference

Row types are derived from the schema with no hand-written interface. Checked with the TypeScript compiler, rows[0].name is string and rows[0].score is number. Two mistakes that fail to compile:

db.insert(students).values({ name: 'Ravi', city: 123 })
// error TS2769: No overload matches this call.
// (city is a text column; number is not assignable)

db.select().from(students).where(eq(students.email, 'x'))
// error TS2339: Property 'email' does not exist

Because the types come from the same object that generates the migration, they cannot drift from the real table the way a hand-maintained interface can. You can also pull the row types out explicitly with typeof students.$inferSelect and $inferInsert when you need to name them in a function signature.

The gotcha: strict library checking in recent TypeScript versions flags Drizzle's internal type definitions for the drivers you are not using, producing errors from inside node_modules that have nothing to do with your code. Setting skipLibCheck to true in tsconfig.json is the standard fix, and it was required to type-check the examples here.

Aggregates and raw SQL

The sql template tag is the escape hatch for anything the builder does not express directly, such as database-specific functions, and it stays parameterised rather than concatenated. This ran for the article:

db.select({
  city: students.city,
  avg: sql`round(avg(${students.score}), 1)`.as('avg'),
}).from(students).groupBy(students.city).all();
// -> [ { city: 'Delhi', avg: 91 }, { city: 'Pune', avg: 71 } ]

The interpolation rules are worth learning early. Putting a column reference inside the template, ${students.score}, emits the correctly quoted identifier. Putting a plain value in binds it as a parameter, the same as a placeholder in a prepared statement. You never build a SQL string by hand, so the injection risk that comes with string concatenation does not arise even on the raw-SQL path. The .as('avg') call names the computed column so it appears as a typed key on each result row.

The delete gotcha

On delete and update, the .where() call is optional, and Drizzle does not warn you when it is missing. Confirmed at runtime: db.delete(students).run() with no filter emptied the table, the row count dropped to zero, with no error and no confirmation prompt. update without a filter behaves the same way, rewriting every row.

There is no built-in 'you forgot a WHERE' guard, because the abstraction is deliberately thin and a bare DELETE is valid SQL. Protect yourself in application code. Wrap destructive operations in a helper that takes a filter as a required argument, so a missing WHERE is a type error. Run schema changes through drizzle-kit rather than ad hoc statements. Keep multi-step writes inside db.transaction(...), so one bad line can be rolled back instead of committed. This is the one place where Drizzle's closeness to raw SQL works against you.

Frequently Asked Questions