What Is an ORM? Drizzle and Prisma Examples

Writing raw SQL in your application code works, but it has drawbacks:

// Raw SQL in Node.js
const result = await pool.query(
  'SELECT u.name, u.email, COUNT(o.id) as order_count ' +
  'FROM users u ' +
  'LEFT JOIN orders o ON u.id = o.user_id ' +
  'WHERE u.role = $1 ' +
  'GROUP BY u.id',
  ['customer']
);
// result.rows is an array of objects, but TypeScript has no idea what shape they are

Problems with raw SQL:

• No type safety: TypeScript cannot check that your SQL is correct. A typo in a column name only crashes at runtime.
• SQL injection risk: If you forget to parameterize inputs, attackers can manipulate your queries.
• No autocomplete: Your editor cannot suggest column names or table names.
• Schema drift: You rename a column in the database but forget to update the SQL in your code. It breaks silently.

An ORM solves these problems by mapping your database tables to objects in your code. You define the schema once, and the ORM generates type-safe queries.

Drizzle is a TypeScript ORM that stays close to SQL. If you know SQL, Drizzle feels familiar. It generates the SQL you would write by hand, but with full type safety.

Define your schema in TypeScript:

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

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

export const orders = pgTable('orders', {
  id: serial('id').primaryKey(),
  userId: integer('user_id').references(() => users.id).notNull(),
  total: integer('total').notNull(),
  status: text('status').notNull().default('pending'),
});

Query with type-safe builders:

import { db } from './db';
import { users, orders } from './db/schema';
import { eq, count } from 'drizzle-orm';

// Get all customers
const customers = await db
  .select()
  .from(users)
  .where(eq(users.role, 'customer'));
// TypeScript knows: { id: number, name: string, email: string, ... }[]

// Insert a new user
const [newUser] = await db
  .insert(users)
  .values({ name: 'Wanjiku', email: 'wanjiku@example.com' })
  .returning();

// Join tables
const usersWithOrders = await db
  .select({
    name: users.name,
    orderCount: count(orders.id),
  })
  .from(users)
  .leftJoin(orders, eq(users.id, orders.userId))
  .groupBy(users.id);

Drizzle generates clean SQL that is easy to reason about. You can see the generated SQL with .toSQL() on any query.

Prisma takes a different approach. You define your schema in a special .prisma file, and Prisma generates a fully typed client for you.

Define your schema:

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  role      String   @default("customer")
  orders    Order[]
  createdAt DateTime @default(now()) @map("created_at")

  @@map("users")
}

model Order {
  id     Int    @id @default(autoincrement())
  user   User   @relation(fields: [userId], references: [id])
  userId Int    @map("user_id")
  total  Int
  status String @default("pending")

  @@map("orders")
}

Run npx prisma generate to create the client, then query:

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

// Get all customers
const customers = await prisma.user.findMany({
  where: { role: 'customer' },
});
// TypeScript knows the exact shape

// Insert a new user
const newUser = await prisma.user.create({
  data: { name: 'Ochieng', email: 'ochieng@example.com' },
});

// Get users with their orders
const usersWithOrders = await prisma.user.findMany({
  include: { orders: true },
});
// Each user has a typed orders array

Prisma's API is more abstract than SQL. You do not write joins. You use include and select to specify related data. This is easier for beginners but can generate surprising SQL for complex queries.

Choose Drizzle if:

• You know SQL and want your ORM to feel like SQL.
• You want a lightweight library with no code generation step.
• You care about the exact SQL being generated.
• You want to use edge runtimes (Cloudflare Workers, Vercel Edge) where bundle size matters.

Choose Prisma if:

• You prefer a higher-level API and do not want to think in SQL.
• You want excellent migration tooling out of the box (prisma migrate).
• You value Prisma Studio, a visual database browser.
• Your team has mixed SQL experience.

Honest tradeoffs:

• Prisma's generated client adds to your node_modules size. Drizzle is smaller.
• Prisma requires a generation step after schema changes. Drizzle schema changes are immediate.
• Drizzle gives you more control over the SQL. Prisma sometimes generates queries that are less efficient than what you would write by hand.
• Prisma has a larger community and more tutorials. Drizzle is newer but growing fast.

Both are good choices. For McTaba projects, Drizzle is the primary ORM because it aligns well with the Next.js and Supabase stack.

ORMs are great for standard CRUD operations. But sometimes raw SQL is the better tool:

• Complex reports: Queries with multiple CTEs, window functions, or recursive queries are often clearer in SQL.
• Performance-critical queries: When you need full control over the query plan.
• Database-specific features: PostgreSQL extensions, full-text search, or JSONB operations that the ORM does not support natively.
• Database migrations: Schema changes (CREATE TABLE, ALTER TABLE) are written in SQL even when using an ORM.

Both Drizzle and Prisma let you run raw SQL when needed:

// Raw SQL in Drizzle
import { sql } from 'drizzle-orm';

const result = await db.execute(
  sql`SELECT name, SUM(total) as revenue
      FROM users
      JOIN orders ON users.id = orders.user_id
      WHERE orders.status = 'completed'
      GROUP BY users.id
      HAVING SUM(total) > 50000`
);

// Raw SQL in Prisma
const result = await prisma.$queryRaw`
  SELECT name, SUM(total) as revenue
  FROM users
  JOIN orders ON users.id = orders.user_id
  WHERE orders.status = 'completed'
  GROUP BY users.id
  HAVING SUM(total) > 50000
`;

The bottom line: learn SQL first, then pick an ORM. The ORM is a productivity tool, not a replacement for understanding how databases work.