Drizzle ORM, SQL that knows your types

Drizzle ORM, SQL that knows your types

Most tools mapping a database onto objects begin by hiding SQL and end with you needing to know what query came out anyway. Drizzle goes the other way: you write queries in a syntax close to SQL, and TypeScript checks column names, condition types, and result shape.

The effect is that you learn no new query language and gain completion and checking in the one you already know. That is the shortest description of what sets this library apart from the previous generation of tools.

Schema in TypeScript

You describe tables in code, and types follow from that description automatically. There is no separate schema language and no client generation step.

import { pgTable, serial, text, timestamp, boolean, integer } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull()
})

export const tasks = pgTable('tasks', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  done: boolean('done').default(false).notNull(),
  authorId: integer('author_id').references(() => users.id).notNull()
})

export type Task = typeof tasks.$inferSelect
export type NewTask = typeof tasks.$inferInsert

The two types at the end save the most time in practice. The first describes a row read from the database, the second the data needed for an insert, where columns with defaults are optional. A schema change reaches both immediately.

The column name in the database and the field name in code are separate, so the database can use underscores and the code camel case, with no translation layer in between.

Queries

The syntax tracks SQL closely enough that translating in either direction is obvious.

const open = await db
  .select({ id: tasks.id, title: tasks.title, author: users.name })
  .from(tasks)
  .innerJoin(users, eq(tasks.authorId, users.id))
  .where(and(eq(tasks.done, false), eq(users.id, authorId)))
  .orderBy(desc(tasks.id))
  .limit(20)

The result type follows from what you selected, so referencing a field outside the list is a compile error rather than a runtime surprise. That single property removes a whole category of mistakes when changing a query.

When you need something the interface cannot express, you write plain SQL and still keep type control over it.

const stats = await db.execute(sql`
  SELECT author_id, count(*)::int AS total
  FROM tasks
  WHERE done = true
  GROUP BY author_id
`)

That is an important practical property. In tools hiding SQL, dropping to a raw query usually means losing types and stepping outside the model. Here it is a normal route rather than an escape hatch.

Relational queries

The SQL style interface returns flat rows, so fetching a task with its author and comments needs joins and manual assembly. A second interface serves that, returning data in nested form.

const result = await db.query.tasks.findMany({
  where: (t, { eq }) => eq(t.done, false),
  with: {
    author: true,
    comments: { limit: 5, orderBy: (c, { desc }) => desc(c.createdAt) }
  }
})

The key advantage is that one query goes to the database rather than a separate one per relation. The many query problem, familiar from previous generation tools, simply does not arise here.

The second version of this interface, available in a preview release, adds capabilities the first lacked: full logical operators in conditions and filtering parent rows by a child relation's column. That second item was the most reported gap, since asking for users with at least one open task meant dropping to joins.

The syntax for declaring relations changed too, so migration means rewriting the definitions file. Plan for that rather than discovering it mid upgrade.

Migrations

The companion tool compares the schema in code against the database state and generates SQL files holding the difference.

pnpm drizzle-kit generate
pnpm drizzle-kit migrate

The generated file is ordinary SQL you can read and adjust before running. That matters on risky changes, such as dropping a column or changing a type, where automation cannot know what to do with existing data.

There is also a mode pushing changes straight to the database with no migration file. It suits prototyping and a development environment, while in production you want change history in the repository, so stay with file based migrations.

A separate graphical tool lets you browse data and schema in a browser. Recent releases expanded the schema view and added a multiline editor, making it a usable replacement for a database client in daily work.

Working in an edge runtime

This is where the library outperformed its competition and gained most of its users. No runtime dependencies and no binary engine mean the code runs where previous generation tools needed workarounds.

import { drizzle } from 'drizzle-orm/neon-http'
import { neon } from '@neondatabase/serverless'

const db = drizzle(neon(process.env.DATABASE_URL!), { schema })

With serverless functions and an edge runtime in Next.js, cold start time matters too, and it grows with bundle size. A library without a binary engine adds a fraction of what solutions with a separate process do.

Connection pooling is a separate matter. A database has a limited connection count, and serverless functions can spin up in the hundreds at once. The answer is an HTTP based driver or pooling on the database provider's side rather than the library itself.

A second route is a database that speaks HTTP by design. Turso, an SQLite compatible database run as a service, adds a replica embedded in the application process, so a read stops being a network call. The libSQL driver plugs in like any other, and the price is twofold: writes still go to the primary and reach the replica with a delay, and the whole database has to fit inside the application.

Transactions and compound operations

Operations touching several tables must complete entirely or not at all. A transaction wraps a block of code, and an exception inside rolls everything back.

await db.transaction(async (tx) => {
  const [order] = await tx
    .insert(orders)
    .values({ customerId, amount })
    .returning()

  await tx.insert(lineItems).values(
    cart.map((p) => ({ orderId: order.id, productId: p.id, quantity: p.quantity }))
  )

  await tx
    .update(inventory)
    .set({ stock: sql`${inventory.stock} - ${1}` })
    .where(inArray(inventory.productId, cart.map((p) => p.id)))
})

Note the stock update expressed by referencing the column's current value rather than reading and writing in code. The latter opens a race where two concurrent orders read the same stock and write the same value, losing one decrement.

Remember too that inside a transaction you must use the passed object rather than the global connection. A call on the latter runs outside the transaction and will not roll back on error, a bug that is hard to spot because the code looks right.

Insert many rows in one call with a list of values rather than in a loop. At a thousand rows the difference is a second against dozens, since every separate call is a separate round trip to the database.

Tests and the development environment

The database in tests is where seemingly convenient solutions turn costly to maintain. Replacing the access layer with a stub means testing your own stubs rather than your queries.

The practical approach runs a real database in a container and applies migrations before the test suite. Queries then genuinely get exercised, conditions, indexes, and constraints included.

Isolation between tests comes most simply from a transaction rolled back after each case. The test runs inside a transaction, and rolling it back restores the initial state faster than truncating tables.

Seed data is a separate matter. A function filling the database with a realistic record set, kept in the repository, helps both in tests and when onboarding somebody new. Write it early, since later nobody has time for it.

Drizzle against the alternatives

Choosing between the first two comes down to one question: does the team know SQL and want control over it. If so, this library gives types without taking control away. If not, the competition's more mature ecosystem saves learning.

There is one more argument, rarely raised and sometimes decisive: the cost of leaving. Queries written in a syntax close to SQL translate into plain SQL almost mechanically, so abandoning the library after two years does not mean rewriting the access layer from nothing. With a tool carrying its own query language that cost is considerably higher, which is worth weighing on a project meant to live long.

Note that the last two rows are different categories. A query builder has neither migrations nor relational queries, and a database platform is a whole backend in which this library can serve as the access layer.

Performance and where it gets lost

A typed interface does not stop you writing a slow query, so a few things deserve checking yourself.

The first is the query plan. A method returning the finished SQL lets you paste it into a database client and check whether an index is used.

const query = db.select().from(tasks).where(eq(tasks.authorId, 42))
console.log(query.toSQL())

The second is round trips per request. Code fetching a list and then pulling data for each element in a loop looks innocent and produces a hundred queries instead of one. The relational interface or a join solves it in one call.

The third is prepared statements. A frequently executed query can be prepared once and called with parameters, saving the library time spent building SQL.

const byAuthor = db
  .select()
  .from(tasks)
  .where(eq(tasks.authorId, sql.placeholder('author')))
  .prepare('tasks_by_author')

const result = await byAuthor.execute({ author: 42 })

The fourth is offset pagination on large tables. Skipping a hundred thousand rows means scanning them, so on deep pages keyset pagination works better, meaning a condition on the identifier of the last row seen.

Common mistakes

The first is using push mode in production. Without a migration file there is no change history and no way back, and an automatically detected difference is sometimes interpreted differently than you assumed.

The second is missing indexes on columns used in conditions. The library lets you declare them in the schema, so there is no reason to wait for a performance problem.

The third is selecting every column when three are needed. The interface lets you pick exactly what you want, and on wide tables the transfer difference is noticeable.

The fourth is forgetting transactions on related operations. Inserting an order and its line items without a transaction leaves an order with no items when something fails.

The fifth is holding a connection as a module variable in a serverless environment without checking how the provider manages instances. Misconfigured, you exhaust the database connection limit on the first real traffic.

The sixth is skipping review of the generated migration file. It is ordinary SQL, reading it takes a minute, and it guards against data loss on a column type change.

Migrating from another tool

Moving from a previous generation tool rarely needs everything rewritten at once and goes better spread over time.

The first step is reconstructing the schema. The companion tool can read an existing database and generate a schema file from it, so you do not retype tables by hand. Review the generated file, since names and types do not always come out as you would have written them.

The second is coexistence. Both libraries can run side by side on the same database, since neither demands exclusivity. New queries go in the new one and old ones stay while they work.

The third is migrations. Exclusivity is necessary here, since two tools tracking schema state will get in each other's way. Settle on one place where migrations are created and hold to it from the start of the transition.

The fourth is rewrite order. Begin with the queries changed most often, since that is where types pay off. Stable queries nobody has touched in a year come last or never.

FAQ

Drizzle or Prisma?

Choose Drizzle when the team knows SQL and wants control over queries, when light weight matters, or when you work in an edge runtime. Prisma wins on ecosystem maturity and a legible schema language, so it often suits a team that would rather not think about SQL.

Is Drizzle production ready?

Yes, the library has been used in production for years and has a stable core interface. Do check the status of the features you reach for, though, since some additions, such as the second version of relational queries, arrive first in preview releases.

Which databases are supported?

PostgreSQL, MySQL, and SQLite along with their cloud provider variants, serverless offerings included. Schema and queries look similar in each case, though column types and some functions are specific to a given database.

Is a code generation step needed?

Not for types, since they follow directly from the TypeScript schema. Generation concerns migration files alone, meaning SQL describing database changes, and you run it deliberately when the schema changes.

How does it work with vector search?

Through a database extension, pgvector for instance, which you declare in the schema as a column type and query normally. No separate layer is needed, since it remains the same database on the same connection.

Documentation sits on the project site, and the relational query changes appear in the migration guide.