ORM Integration with Prisma and Drizzle

Integrate Prisma or Drizzle ORM into a Koa 2 application, organise queries into service modules, and understand where migrations fit in the workflow.

5 min read Level 3/5 #koa#data#orm

What you'll learn

Bootstrap a Prisma or Drizzle client and connect it to a Koa app
Isolate database logic inside service modules instead of inline handlers
Run and apply migrations to keep the database schema in sync

Raw SQL works, but ORMs add compile-time type safety, auto-completion, and schema migration tooling. Two popular choices in the Node ecosystem are Prisma (schema-first) and Drizzle (code-first, lightweight).

Prisma Setup

npm install prisma @prisma/client
npx prisma init          # creates prisma/schema.prisma + .env

Define a model in prisma/schema.prisma:

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  createdAt DateTime @default(now())
}

Apply the schema to your database and generate the typed client:

npx prisma migrate dev --name init

Prisma Client Singleton

Instantiate the client once and share it as a module export.

// lib/prisma.js
import { PrismaClient } from '@prisma/client';

export const prisma = new PrismaClient();

Service Layer

Keep database calls out of route files. Services are plain async functions that the handler calls.

// services/userService.js
import { prisma } from '../lib/prisma.js';

export async function listUsers() {
  return prisma.user.findMany({ orderBy: { id: 'asc' } });
}

export async function getUserById(id) {
  return prisma.user.findUnique({ where: { id: Number(id) } });
}

export async function createUser(data) {
  return prisma.user.create({ data });
}

// routes/users.js
import Router from '@koa/router';
import { listUsers, getUserById, createUser } from '../services/userService.js';

const router = new Router({ prefix: '/users' });

router.get('/', async (ctx) => {
  ctx.body = await listUsers();
});

router.get('/:id', async (ctx) => {
  const user = await getUserById(ctx.params.id);
  if (!user) ctx.throw(404, 'User not found');
  ctx.body = user;
});

router.post('/', async (ctx) => {
  ctx.status = 201;
  ctx.body = await createUser(ctx.request.body);
});

export default router;

Drizzle Alternative

Drizzle is schema-in-code with zero query builder magic — it compiles to SQL you can read.

// db/schema.js
import { pgTable, serial, text } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id:    serial('id').primaryKey(),
  name:  text('name').notNull(),
  email: text('email').notNull().unique(),
});

// db/index.js
import { drizzle } from 'drizzle-orm/node-postgres';
import pg from 'pg';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
export const db = drizzle(pool);

// handler snippet
import { db } from '../db/index.js';
import { users } from '../db/schema.js';

router.get('/', async (ctx) => {
  ctx.body = await db.select().from(users);
});

Migrations Note

Both tools ship a CLI for migrations. Run migrations during your CI/CD deploy step — never auto-migrate in production app startup code, as this can cause race conditions across multiple instances.

Up Next

Learn how to accept file uploads in Koa using multipart form data and @koa/multer.

Handling File Uploads →