|$ curl https://forge-ai.dev/api/markdown?path=docs/databases/orm

$cat docs/orms.md

updated Recently·50 min read·published

ORMs

◆ORM◆Prisma◆Drizzle◆TypeORM◆Beginner to Advanced🎯Free Tools

Introduction

An ORM (Object-Relational Mapper) maps database tables to programming language objects, allowing you to interact with your database using native language constructs instead of raw SQL strings. ORMs handle query building, type safety, connection pooling, and schema management.

The JavaScript/TypeScript ecosystem has several excellent ORMs, each with different philosophies. Prisma prioritizes developer experience and type safety. Drizzle emphasizes SQL-like APIs and performance. TypeORM follows the Active Record pattern popular in Ruby on Rails. Sequelize is the battle-tested veteran.

This guide compares these ORMs in depth, shows practical examples, and helps you decide when to use an ORM versus raw SQL.

Prisma

Prisma is the most popular ORM for TypeScript. It uses a declarative schema language, generates a fully typed client, and provides a visual database GUI (Prisma Studio). Its query API is intuitive and its migration system is excellent.

prisma-setup.sh

Bash

1	# Install Prisma
2	npm install prisma @prisma/client
3	npx prisma init
4
5	# This creates:
6	# prisma/schema.prisma — schema definition
7	# .env — DATABASE_URL

prisma-schema.prisma

Bash

1	// prisma/schema.prisma
2	generator client {
3	provider = "prisma-client-js"
4	}
5
6	datasource db {
7	provider = "postgresql"
8	url = env("DATABASE_URL")
9	}
10
11	model User {
12	id String @id @default(uuid())
13	email String @unique
14	name String?
15	role Role @default(USER)
16	posts Post[]
17	profile Profile?
18	createdAt DateTime @default(now()) @map("created_at")
19	updatedAt DateTime @updatedAt @map("updated_at")
20
21	@@map("users")
22	}
23
24	model Post {
25	id String @id @default(uuid())
26	title String
27	content String?
28	published Boolean @default(false)
29	author User @relation(fields: [authorId], references: [id])
30	authorId String @map("author_id")
31	tags String[]
32	metadata Json?
33	createdAt DateTime @default(now()) @map("created_at")
34
35	@@index([authorId])
36	@@index([createdAt(sort: Desc)])
37	@@map("posts")
38	}
39
40	model Profile {
41	id String @id @default(uuid())
42	bio String?
43	avatar String?
44	user User @relation(fields: [userId], references: [id])
45	userId String @unique @map("user_id")
46
47	@@map("profiles")
48	}
49
50	enum Role {
51	USER
52	ADMIN
53	MODERATOR
54	}

prisma-queries.ts

TypeScript

1	import { PrismaClient, Prisma } from "@prisma/client";
2
3	const prisma = new PrismaClient({
4	log: ["query", "info", "warn", "error"], // debug logging
5	});
6
7	// CREATE
8	const user = await prisma.user.create({
9	data: {
10	email: "alice@example.com",
11	name: "Alice Johnson",
12	profile: {
13	create: { bio: "Software engineer" }
14	}
15	}
16	});
17
18	// READ with relations
19	const users = await prisma.user.findMany({
20	where: {
21	role: "ADMIN",
22	posts: { some: { published: true } }
23	},
24	include: {
25	posts: {
26	where: { published: true },
27	orderBy: { createdAt: "desc" },
28	take: 5
29	},
30	profile: true
31	},
32	orderBy: { createdAt: "desc" },
33	take: 20
34	});
35
36	// UPDATE
37	await prisma.user.update({
38	where: { id: userId },
39	data: { role: "ADMIN" }
40	});
41
42	// UPSERT
43	const post = await prisma.post.upsert({
44	where: { id: existingId },
45	update: { title: "Updated Title" },
46	create: {
47	title: "New Post",
48	authorId: userId,
49	content: "Hello world"
50	}
51	});
52
53	// TRANSACTION
54	await prisma.$transaction(async (tx) => {
55	const sender = await tx.account.update({
56	where: { id: senderId },
57	data: { balance: { decrement: 100 } }
58	});
59
60	if (sender.balance < 0) {
61	throw new Error("Insufficient funds");
62	}
63
64	await tx.account.update({
65	where: { id: receiverId },
66	data: { balance: { increment: 100 } }
67	});
68
69	await tx.transfer.create({
70	data: { from: senderId, to: receiverId, amount: 100 }
71	});
72	});
73
74	// RAW SQL when needed
75	const result = await prisma.$queryRaw`
76	SELECT u.name, COUNT(p.id) AS post_count
77	FROM users u
78	LEFT JOIN posts p ON p.author_id = u.id
79	GROUP BY u.id, u.name
80	ORDER BY post_count DESC
81	LIMIT 10
82	`;

ℹ

info

Prisma generates a fully typed client from your schema. Usenpx prisma generate after schema changes. The generated types enable autocompletion and type checking for all queries, relations, and results.

Drizzle ORM

Drizzle is a lightweight, type-safe ORM that stays close to SQL. It does not generate a client — instead, it provides a SQL-like API that produces standard SQL queries. This makes it faster and more predictable than Prisma for complex queries.

drizzle-schema.ts

TypeScript

1	// drizzle/schema.ts
2	import { pgTable, uuid, varchar, text, boolean, timestamp, jsonb, integer, pgEnum } from "drizzle-orm/pg-core";
3
4	export const roleEnum = pgEnum("role", ["USER", "ADMIN", "MODERATOR"]);
5
6	export const users = pgTable("users", {
7	id: uuid("id").defaultRandom().primaryKey(),
8	email: varchar("email", { length: 255 }).notNull().unique(),
9	name: varchar("name", { length: 255 }),
10	role: roleEnum("role").default("USER").notNull(),
11	createdAt: timestamp("created_at").defaultNow().notNull(),
12	updatedAt: timestamp("updated_at").defaultNow().notNull()
13	});
14
15	export const posts = pgTable("posts", {
16	id: uuid("id").defaultRandom().primaryKey(),
17	title: varchar("title", { length: 255 }).notNull(),
18	content: text("content"),
19	published: boolean("published").default(false).notNull(),
20	authorId: uuid("author_id").notNull().references(() => users.id),
21	tags: text("tags").array(),
22	metadata: jsonb("metadata"),
23	createdAt: timestamp("created_at").defaultNow().notNull()
24	}, (table) => ({
25	authorIdx: index("idx_posts_author").on(table.authorId),
26	createdIdx: index("idx_posts_created").on(table.createdAt)
27	}));
28
29	// drizzle/index.ts
30	import { drizzle } from "drizzle-orm/postgres-js";
31	import postgres from "postgres";
32	import * as schema from "./schema";
33
34	const connectionString = process.env.DATABASE_URL!;
35	const client = postgres(connectionString);
36	export const db = drizzle(client, { schema });

drizzle-queries.ts

TypeScript

1	// Drizzle queries: SQL-like API
2	import { eq, and, desc, sql, like } from "drizzle-orm";
3	import { users, posts } from "./schema";
4
5	// SELECT
6	const allUsers = await db.select().from(users).where(
7	eq(users.role, "ADMIN")
8	);
9
10	// SELECT with JOIN
11	const userPosts = await db
12	.select({
13	userName: users.name,
14	postTitle: posts.title,
15	postCreated: posts.createdAt
16	})
17	.from(users)
18	.leftJoin(posts, eq(users.id, posts.authorId))
19	.where(like(users.name, "%Alice%"))
20	.orderBy(desc(posts.createdAt))
21	.limit(20);
22
23	// INSERT
24	const [newUser] = await db.insert(users).values({
25	email: "bob@example.com",
26	name: "Bob Smith",
27	role: "USER"
28	}).returning();
29
30	// INSERT with relation
31	await db.insert(posts).values({
32	title: "My First Post",
33	content: "Hello world",
34	authorId: newUser.id,
35	tags: ["intro", "hello"]
36	});
37
38	// UPDATE
39	await db.update(users)
40	.set({ role: "ADMIN" })
41	.where(eq(users.id, userId));
42
43	// UPSERT
44	await db.insert(users).values({
45	email: "alice@example.com",
46	name: "Alice"
47	}).onConflictDoUpdate({
48	target: users.email,
49	set: { name: "Alice Updated" }
50	});
51
52	// DELETE
53	await db.delete(posts).where(eq(posts.authorId, userId));
54
55	// Aggregation
56	const stats = await db
57	.select({
58	role: users.role,
59	count: sql`count(*)::int`,
60	avgPosts: sql`count(${posts.id})::float / count(distinct ${users.id})`
61	})
62	.from(users)
63	.leftJoin(posts, eq(users.id, posts.authorId))
64	.groupBy(users.role);

🔥

pro tip

Drizzle's SQL-like API makes it easy to switch between Drizzle and raw SQL. For complex analytical queries, Drizzle providessql template tags that let you write raw SQL while maintaining type safety.

TypeORM

TypeORM follows the Active Record pattern (similar to Ruby on Rails). It uses decorators to define entities and supports both Active Record and Data Mapper patterns. It is widely used in NestJS applications.

typeorm-entities.ts

TypeScript

1	// entities/User.ts
2	import {
3	Entity, PrimaryGeneratedColumn, Column, CreateDateColumn,
4	UpdateDateColumn, OneToMany, ManyToOne, Index
5	} from "typeorm";
6	import { Post } from "./Post";
7
8	@Entity("users")
9	export class User {
10	@PrimaryGeneratedColumn("uuid")
11	id: string;
12
13	@Column({ type: "varchar", length: 255, unique: true })
14	email: string;
15
16	@Column({ type: "varchar", length: 255, nullable: true })
17	name: string;
18
19	@Column({ type: "enum", enum: ["USER", "ADMIN", "MODERATOR"], default: "USER" })
20	role: string;
21
22	@OneToMany(() => Post, (post) => post.author)
23	posts: Post[];
24
25	@CreateDateColumn({ name: "created_at" })
26	createdAt: Date;
27
28	@UpdateDateColumn({ name: "updated_at" })
29	updatedAt: Date;
30	}
31
32	// entities/Post.ts
33	@Entity("posts")
34	export class Post {
35	@PrimaryGeneratedColumn("uuid")
36	id: string;
37
38	@Column({ type: "varchar", length: 255 })
39	title: string;
40
41	@Column({ type: "text", nullable: true })
42	content: string;
43
44	@Column({ type: "boolean", default: false })
45	published: boolean;
46
47	@ManyToOne(() => User, (user) => user.posts)
48	author: User;
49
50	@Column({ name: "author_id" })
51	authorId: string;
52
53	@CreateDateColumn({ name: "created_at" })
54	createdAt: Date;
55	}

typeorm-queries.ts

TypeScript

1	// TypeORM Repository pattern
2	import { AppDataSource } from "./data-source";
3	import { User } from "./entities/User";
4
5	const userRepository = AppDataSource.getRepository(User);
6
7	// Find
8	const users = await userRepository.find({
9	where: { role: "ADMIN" },
10	relations: ["posts"],
11	order: { createdAt: "DESC" },
12	take: 20
13	});
14
15	// Find one
16	const user = await userRepository.findOne({
17	where: { id: userId },
18	relations: ["posts"]
19	});
20
21	// Create and save
22	const newUser = userRepository.create({
23	email: "charlie@example.com",
24	name: "Charlie"
25	});
26	await userRepository.save(newUser);
27
28	// Update
29	await userRepository.update(userId, { role: "ADMIN" });
30
31	// Query builder (more control)
32	const result = await userRepository
33	.createQueryBuilder("user")
34	.leftJoinAndSelect("user.posts", "post")
35	.where("user.role = :role", { role: "ADMIN" })
36	.andWhere("post.published = :published", { published: true })
37	.orderBy("post.createdAt", "DESC")
38	.limit(20)
39	.getMany();
40
41	// Transaction
42	await AppDataSource.transaction(async (manager) => {
43	await manager.getRepository(User).update(senderId, {
44	balance: () => "balance - 100"
45	});
46	await manager.getRepository(User).update(receiverId, {
47	balance: () => "balance + 100"
48	});
49	});

Sequelize

Sequelize is the oldest and most battle-tested Node.js ORM. It supports PostgreSQL, MySQL, SQLite, and MSSQL. While its TypeScript support is less polished than Prisma or Drizzle, it remains widely used in production applications.

sequelize.ts

TypeScript

1	import { DataTypes, Model, Sequelize } from "sequelize";
2
3	const sequelize = new Sequelize(process.env.DATABASE_URL!, {
4	dialect: "postgres",
5	logging: false,
6	pool: { max: 20, min: 5, acquire: 30000, idle: 10000 }
7	});
8
9	class User extends Model {
10	declare id: string;
11	declare email: string;
12	declare name: string;
13	declare role: string;
14	declare createdAt: Date;
15	declare updatedAt: Date;
16	}
17
18	User.init({
19	id: {
20	type: DataTypes.UUID,
21	defaultValue: DataTypes.UUIDV4,
22	primaryKey: true
23	},
24	email: {
25	type: DataTypes.STRING(255),
26	unique: true,
27	allowNull: false,
28	validate: { isEmail: true }
29	},
30	name: {
31	type: DataTypes.STRING(255),
32	allowNull: true
33	},
34	role: {
35	type: DataTypes.ENUM("USER", "ADMIN", "MODERATOR"),
36	defaultValue: "USER"
37	}
38	}, {
39	sequelize,
40	modelName: "User",
41	tableName: "users",
42	timestamps: true,
43	underscored: true
44	});
45
46	// Associations
47	User.hasMany(Post, { foreignKey: "authorId", as: "posts" });
48	Post.belongsTo(User, { foreignKey: "authorId", as: "author" });
49
50	// Queries
51	const users = await User.findAll({
52	where: { role: "ADMIN" },
53	include: [{ model: Post, as: "posts", where: { published: true } }],
54	order: [[{ model: Post, as: "posts" }, "createdAt", "DESC"]],
55	limit: 20
56	});
57
58	// Scoped queries
59	User.addScope("active", {
60	where: { role: { [Op.ne]: "BANNED" } }
61	});
62
63	const activeUsers = await User.scope("active").findAll();

ORM Comparison

Each ORM makes different trade-offs. Here is a comprehensive comparison to help you choose the right one for your project.

Feature	Prisma	Drizzle	TypeORM	Sequelize
TypeScript Support	Excellent (generated)	Excellent (inferred)	Good (decorators)	Moderate
Query Performance	Moderate	Excellent	Good	Good
Learning Curve	Low	Medium	Medium	Low
Raw SQL Escape	$queryRaw	sql template	Query Builder	sequelize.query
Migrations	prisma migrate	drizzle-kit	TypeORM CLI	sequelize-cli
Bundle Size	Large	Small	Large	Large
Active Development	Very Active	Very Active	Moderate	Moderate
Best For	DX-first apps	Performance-critical	NestJS / legacy	Existing projects

✓

best practice

Recommendation for 2026: Start with Drizzle for new projects — it is fast, lightweight, and gives you full SQL control. UsePrisma if developer experience and rapid prototyping are your priority. Use raw SQL with pg library for complex analytical queries regardless of which ORM you choose.

ORM vs Raw SQL

ORMs are excellent for CRUD operations and simple queries, but raw SQL is better for complex analytical queries, performance-critical paths, and database-specific features. The best approach is often a hybrid.

orm-vs-raw.ts

TypeScript

1	// Hybrid approach: ORM for CRUD, raw SQL for analytics
2
3	// CRUD with Drizzle ORM
4	const user = await db.insert(users).values({ email, name }).returning();
5	const posts = await db.select().from(posts).where(eq(posts.authorId, user.id));
6
7	// Complex analytics with raw SQL
8	const analytics = await db.execute(sql`
9	WITH weekly_stats AS (
10	SELECT
11	DATE_TRUNC('week', o.created_at) AS week,
12	p.category,
13	COUNT(DISTINCT o.id) AS orders,
14	SUM(o.total) AS revenue
15	FROM orders o
16	JOIN order_items oi ON oi.order_id = o.id
17	JOIN products p ON p.id = oi.product_id
18	WHERE o.status = 'completed'
19	GROUP BY DATE_TRUNC('week', o.created_at), p.category
20	)
21	SELECT
22	week,
23	category,
24	orders,
25	revenue,
26	ROUND(
27	(revenue - LAG(revenue) OVER (PARTITION BY category ORDER BY week))
28	/ NULLIF(LAG(revenue) OVER (PARTITION BY category ORDER BY week), 0) * 100,
29	2
30	) AS growth_pct
31	FROM weekly_stats
32	ORDER BY week DESC, revenue DESC
33	`);
34
35	// When to use raw SQL:
36	// - Complex JOINs with multiple conditions
37	// - Window functions and CTEs
38	// - Database-specific features (JSONB operators, full-text search)
39	// - Bulk operations (INSERT ... ON CONFLICT)
40	// - Analytical queries with aggregations
41	// - Performance-critical paths where query plan matters
42
43	// When to use ORM:
44	// - Simple CRUD operations
45	// - Typed queries where you want autocompletion
46	// - Basic filtering, sorting, pagination
47	// - Relations and eager loading
48	// - When team includes developers unfamiliar with SQL

ℹ

info

A practical rule: if a query fits in 10 lines with your ORM, use the ORM. If it requires more than 10 lines of ORM code, write raw SQL. You can always wrap raw SQL in typed functions for type safety.

Avoiding the N+1 Problem

The N+1 problem occurs when your ORM lazily loads related data, issuing a separate query for each parent row. This is one of the most common performance pitfalls in ORM usage.

n-plus-one.ts

TypeScript

1	// BAD: N+1 problem (Prisma example)
2	const users = await prisma.user.findMany(); // 1 query
3	for (const user of users) {
4	const posts = await prisma.post.findMany({
5	where: { authorId: user.id }
6	}); // N queries — one per user!
7	console.log(`${user.name} has ${posts.length} posts`);
8	}
9
10	// GOOD: Eager loading with include
11	const usersWithPosts = await prisma.user.findMany({
12	include: {
13	posts: {
14	select: { id: true, title: true },
15	orderBy: { createdAt: "desc" }
16	}
17	}
18	}); // Single query with JOIN
19
20	// GOOD: Drizzle with relations
21	import { relations } from "drizzle-orm";
22
23	const usersRelations = relations(users, ({ many }) => ({
24	posts: many(posts)
25	}));
26
27	const result = await db.query.users.findMany({
28	with: { posts: { columns: { id: true, title: true } } }
29	});
30
31	// GOOD: Raw SQL with JOIN
32	const result = await db.execute(sql`
33	SELECT u.id, u.name, p.id AS post_id, p.title
34	FROM users u
35	LEFT JOIN posts p ON p.author_id = u.id
36	ORDER BY u.name, p.created_at DESC
37	`);
38
39	// Detect N+1: Enable query logging
40	const prisma = new PrismaClient({
41	log: [{ level: "query", emit: "event" }]
42	});
43
44	prisma.$on("query", (e) => {
45	console.log("Query: " + e.query);
46	console.log("Duration: " + e.duration + "ms");
47	});

X GitHub YouTube Instagram Facebook Discord Telegram Buy me a coffee Sponsor