DEV Community

Atlas Whoff
Atlas Whoff

Posted on

Drizzle ORM: Type-Safe SQL Without the Prisma Overhead

#typescript #sql #database #node

Drizzle ORM: Type-Safe SQL Without the Prisma Overhead

Prisma is great until you hit a complex query it can't express, or until you notice the cold start penalty in serverless. Drizzle gives you full SQL power with TypeScript types and no runtime overhead.

Why Drizzle

  • Zero dependencies, tiny bundle
  • SQL-like syntax — no magic, no surprises
  • Works in Edge Runtime (Prisma doesn't)
  • Fully typed without a code generation step
  • Migrations are plain SQL files you control

Install 

npm install drizzle-orm postgres
npm install --save-dev drizzle-kit
Enter fullscreen mode Exit fullscreen mode

Define Schema 

// db/schema.ts
import { pgTable, text, integer, timestamp, boolean } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  plan: text('plan', { enum: ['free', 'pro', 'enterprise'] }).default('free'),
  createdAt: timestamp('created_at').defaultNow(),
});

export const posts = pgTable('posts', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  title: text('title').notNull(),
  userId: text('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  published: boolean('published').default(false),
  createdAt: timestamp('created_at').defaultNow(),
});
Enter fullscreen mode Exit fullscreen mode

Connect 

// db/index.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';

const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client, { schema });
Enter fullscreen mode Exit fullscreen mode

Queries 

import { db } from '@/db';
import { users, posts } from '@/db/schema';
import { eq, and, desc, like } from 'drizzle-orm';

// Select
const user = await db.select().from(users).where(eq(users.email, 'user@example.com'));

// Join
const userPosts = await db
  .select({
    postTitle: posts.title,
    userName: users.name,
  })
  .from(posts)
  .innerJoin(users, eq(posts.userId, users.id))
  .where(and(eq(posts.published, true), like(posts.title, '%typescript%')))
  .orderBy(desc(posts.createdAt))
  .limit(10);

// Insert
const newUser = await db.insert(users).values({
  email: 'new@example.com',
  name: 'New User',
}).returning();

// Update
await db.update(users)
  .set({ plan: 'pro' })
  .where(eq(users.id, userId));

// Delete
await db.delete(posts).where(eq(posts.userId, userId));
Enter fullscreen mode Exit fullscreen mode

Migrations 

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: { url: process.env.DATABASE_URL! },
});
Enter fullscreen mode Exit fullscreen mode 
npx drizzle-kit generate  # Generate SQL migration
npx drizzle-kit migrate   # Apply to database
npx drizzle-kit studio    # Visual database browser
Enter fullscreen mode Exit fullscreen mode

Transactions 

await db.transaction(async (tx) => {
  const user = await tx.insert(users).values({ email, name }).returning();
  await tx.insert(accounts).values({ userId: user[0].id, provider: 'email' });
});
Enter fullscreen mode Exit fullscreen mode

Drizzle is available as a database option in the AI SaaS Starter Kit — full schema, migrations, and query helpers pre-configured. $99 at whoffagents.com.

Top comments (0)