ORMs: Drizzle and Prisma

What an ORM is

An ORM (Object-Relational Mapper) lets you work with the database through TypeScript code instead of SQL written as text. You define the tables once, as a schema, and the ORM gives you:

• typed queries — autocomplete on columns, compile-time errors if you get a name wrong;
• migrations — a versioned history of structural changes;
• built-in protection against SQL injection.

Ways to access the database

Drizzle — the schema and queries

// shared/api/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').notNull().unique(),
})

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  userId: integer('user_id').notNull().references(() => users.id),
  title: text('title').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

import { drizzle } from 'drizzle-orm/neon-http'
import { desc, eq } from 'drizzle-orm'

const db = drizzle(process.env.DATABASE_URL!)

const latest = await db.select().from(posts).where(eq(posts.userId, 1)).orderBy(desc(posts.createdAt)).limit(10)
const [created] = await db.insert(posts).values({ userId: 1, title: 'Hello' }).returning()
type Post = typeof posts.$inferSelect                    // a row's type, from the schema

Notice: userId in the code, user_id in the database — the camelCase ↔ snake_case mapping is done by the ORM.

Prisma — the same idea, a different style

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

const latest = await prisma.post.findMany({ where: { authorId: 1 }, orderBy: { id: 'desc' }, take: 10, include: { author: true } })

Migrations

A migration is a file that describes a structural change (a new table, a new column), applied only once, in order, to every database (local, preview, production).

npx drizzle-kit generate    # from the TS schema → a SQL migration file
npx drizzle-kit migrate     # applies the migrations that haven't run yet

Where they live in FSD

The schema and the DB client → shared/api/. An entity's queries → entities/<x>/api/ (like entities/lesson/api/lesson-api.ts in this project). Components never import the DB client directly.

Summary

• An ORM = a schema in TS → typed queries + migrations + injection protection.
• Drizzle = close to SQL, light; Prisma = a simple API, rich tooling; raw SQL = full control.
• Migrations are versioned in Git and applied in order, never edited after being applied.

Official sources

• Drizzle ORM — Overview
• Drizzle — Get started with Neon
• Prisma — What is Prisma ORM?
• Kysely