Drizzle ORM

In short: A TypeScript library that lets you define database tables as code (instead of plain SQL) and automatically generate/update the matching database schema from it.

In more detail: The tables are declared in a schema file (e.g. db/schema.ts) via pgTable(...). npx drizzle-kit push compares this definition with the real database and brings it in line. drizzle.config.ts tells Drizzle where the schema and the database live.

Our context: Schema changes (e.g. new columns for software downloads) were at first only tested locally via drizzle-kit push — which once meant the production DB didn’t follow along and the admin page crashed. Since then, every schema change also ships with an SQL migration file.

In Depth

push vs. generate/migrate

Drizzle distinguishes two workflows that are easily confused: drizzle-kit push syncs the schema directly and live against the database (fast, but without a traceable history — handy for local development and quick prototyping), while drizzle-kit generate produces a real, versioned SQL migration file that you commit and run in a controlled way against every environment (staging, production) via drizzle-kit migrate. Only the second approach leaves a traceable history of all schema changes in the repo — every migration file is a standalone .sql file with a sequential number that shows exactly which ALTER TABLE/CREATE TABLE statements were executed and when. This also matters from a compliance/audit perspective: with push there is no record of WHEN the schema changed and how, with generate+migrate there is.

Schema definition

A simple table schema looks like this, for example:

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: text("email").notNull().unique(),
  createdAt: timestamp("created_at").defaultNow(),
});
 
export const posts = pgTable("posts", {
  id: uuid("id").primaryKey().defaultRandom(),
  authorId: uuid("author_id").references(() => users.id, { onDelete: "cascade" }),
  title: text("title").notNull(),
});
 
// Relations for type-safe JOIN queries (relational query API)
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));

The relations() construct is purely meant for TypeScript type inference and the relational query API (db.query.posts.findMany({ with: { author: true } })) — it does NOT create any foreign-key constraints in the database itself; only .references() in the table definition does that.

How it differs from Prisma and raw SQL

The query builder is deliberately kept close to real SQL (unlike more heavily abstracted ORMs such as Prisma, which uses its own query language and a separate code-generation step) — db.select().from(users).where(eq(users.email, "...")) reads almost like the SQL query itself, which makes debugging easier because it’s easier to follow which SQL is actually executed in the end (Drizzle also offers .toSQL(), which prints the generated query including its parameters). Prisma, on the other hand, ships a more mature visual admin interface with Prisma Studio, while Drizzle’s counterpart (Drizzle Studio) is leaner. A practical difference in day-to-day work: Drizzle has no code-generation build step of its own (the schema already IS plain TypeScript code), whereas Prisma needs prisma generate after every schema change to regenerate the type-safe client — which tends to make Drizzle’s development loop faster when the schema changes often.

Common pitfalls

A frequent mistake is using push in production even though it’s primarily meant for local development — without a migration history there’s a risk that, for more complex changes (e.g. renaming a column), push interprets the change as “drop column + create new column” and loses the existing data in that column instead of renaming it. drizzle-kit generate detects such renames interactively and explicitly asks whether it’s a rename or a real drop+create.

See also: Neon