TypeScript Database Access — Prisma, Drizzle, TypeORM Guide

In this tutorial, you will learn about TypeScript Database Access. We cover key concepts, practical examples, and best practices to help you master this topic.

TypeScript database access with modern ORMs like Prisma and Drizzle lets you write type-safe queries where the compiler catches column name typos, type mismatches, and missing relations before you ever connect to a database.

What You'll Learn

• Prisma schema and client setup
• Drizzle ORM and Zod integration
• TypeORM entities and repositories
• Database migrations
• Connection pooling and transactions
• Type-safe raw queries

Why It Matters

Raw SQL or untyped ORMs let you write SELECT * FROM users WHERE emial = ? — a typo that only crashes at runtime. TypeScript ORMs generate types from your schema, making every query a compile-time checked operation.

Real-World Use

The DodaZIP license validation service uses Prisma to manage user subscriptions. The Prisma schema defines the database structure, and TypeScript ensures that every query accessing user data uses the correct fields — zero database-level runtime errors in production.

Learning Path

flowchart LR
  A[Express APIs] --> B[Database Access]
  B --> C[Testing]
  B --> D[You Are Here]
  C --> E[Advanced Patterns]
  D --> F[SOLID Principles]

Type-Safe Database Access with Prisma

Prisma is the most popular TypeScript ORM. It generates a fully typed client from your schema:

npm install prisma @prisma/client
npx prisma init

Defining the Schema

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

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

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        String   @id @default(uuid())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  String
  createdAt DateTime @default(now())
}

Generating and Using the Client

npx prisma generate
npx prisma migrate dev --name init

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// Fully typed queries — every field is checked at compile time
async function getUsersWithPosts() {
  const users = await prisma.user.findMany({
    include: {
      posts: {
        where: { published: true },
        select: { id: true, title: true },
      },
    },
  });

  // users is typed as (User & { posts: { id: string; title: string }[] })[]
  return users;
}

async function createUser(data: { email: string; name: string }) {
  // TypeScript ensures email and name exist and are strings
  const user = await prisma.user.create({ data });
  return user;
}

Expected output:

[
  {
    "id": "uuid-1",
    "email": "alice@example.com",
    "name": "Alice",
    "posts": [{ "id": "uuid-2", "title": "Hello World" }]
  }
]

Drizzle ORM — SQL-Like TypeScript

Drizzle takes a different approach — it feels closer to SQL while maintaining type safety:

npm install drizzle-orm @libsql/client
npm install --save-dev drizzle-kit

Defining Tables

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

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

export const posts = sqliteTable('posts', {
  id: text('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content'),
  authorId: text('author_id').references(() => users.id),
});

Querying with Drizzle

import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';
import { users, posts } from './schema';
import { eq } from 'drizzle-orm';

const client = createClient({ url: 'file:./data.db' });
const db = drizzle(client);

// Typed query — Drizzle infers the result type
async function getAdultUsers() {
  const result = await db
    .select()
    .from(users)
    .where(eq(users.age, 18)); // TypeScript catches `aege` typo

  return result; // typed as { id: string; email: string; name: string; age: number }[]
}

// Join query
async function getUsersWithPosts() {
  const result = await db
    .select()
    .from(users)
    .leftJoin(posts, eq(users.id, posts.authorId));

  return result;
}

TypeORM — Entity Pattern

TypeORM uses decorators to define entities (works best with classes):

import { Entity, PrimaryGeneratedColumn, Column, OneToMany, createConnection } from 'typeorm';

@Entity()
export class User {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @Column({ unique: true })
  email!: string;

  @Column()
  name!: string;

  @OneToMany(() => Post, (post) => post.author)
  posts!: Post[];
}

async function connectAndQuery() {
  const connection = await createConnection({
    type: 'postgres',
    url: process.env.DATABASE_URL,
    entities: [User, Post],
    synchronize: true, // development only
  });

  const userRepository = connection.getRepository(User);
  const users = await userRepository.find({
    relations: ['posts'],
    where: { email: 'alice@example.com' },
  });

  return users;
}

Migrations — Schema Evolution

Prisma migrations are generated from schema changes:

npx prisma migrate dev --name add-bio-field

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  bio       String?  // New field
  posts     Post[]
  createdAt DateTime @default(now())
}

Drizzle generates migrations via drizzle-kit:

npx drizzle-kit generate:sqlite
npx drizzle-kit push:sqlite

Connection Pooling

For production applications, use connection pooling:

import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma = globalForPrisma.prisma ?? new PrismaClient({
  log: process.env.NODE_ENV === 'development' ? ['query'] : [],
});

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

This pattern prevents connection leaks during hot reloading in development.

Transactions

Type-safe transactions in Prisma:

async function transferPoints(fromUserId: string, toUserId: string, amount: number) {
  await prisma.$transaction([
    prisma.user.update({
      where: { id: fromUserId },
      data: { points: { decrement: amount } },
    }),
    prisma.user.update({
      where: { id: toUserId },
      data: { points: { increment: amount } },
    }),
  ]);
}

Common Mistakes

1. Not running migrations before deployment

Prisma's generated client must match the database schema. Always run migrations as part of deployment.

2. Exposing database IDs directly in API responses

UUIDs are acceptable, but auto-increment IDs expose user counts. Use @map to control JSON field names.

3. N+1 query problem

Fetching related records in a loop creates N+1 queries. Use Prisma's include or Drizzle's join to batch them.

4. Not using prepared statements with raw SQL

Template literal SQL is vulnerable to injection. Always use parameterized queries:

// ❌ Bad
await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE id = ${userId}`);

// ✅ Good
await prisma.$queryRaw`SELECT * FROM users WHERE id = ${userId}`;

5. Ignoring connection pooling limits

Each Prisma client maintains a connection pool. In serverless environments, create a single shared instance.

6. Using synchronize: true in production

TypeORM's synchronize auto-creates tables — it can drop data if entities change. Use migrations in production.

7. Not handling database errors

Unique constraint violations, connection timeouts, and deadlocks are recoverable. Implement retry logic for transient failures.

Practice Questions

1. How does Prisma generate TypeScript types? Prisma parses your schema.prisma file and generates a client with full type definitions for models, relations, and queries.

2. What's the difference between Prisma and Drizzle? Prisma uses a declarative schema language. Drizzle uses TypeScript definitions for tables and feels closer to SQL syntax.

3. How do migrations work in production? Migration files are applied sequentially. Prisma uses prisma migrate deploy, Drizzle uses drizzle-kit push or custom migration runners.

4. What is the N+1 problem? Loading parent records then looping to load child records for each one. Use include (Prisma) or join (Drizzle) to fetch in a single query.

5. How do you handle database connection errors? Use retry logic with exponential backoff for transient failures, and implement health check endpoints to monitor database connectivity.

Challenge

Design a database schema for a library management system with books, authors, members, and borrowing records. Implement it with Prisma including migrations, typed queries for all CRUD operations, and a Transaction for borrowing a book.

FAQ

Mini Project

Build a type-safe data access layer for a content management system:

• Prisma schema: User, Post, Category, Comment models with relations
• Migrations: Create initial migration, add a publishedAt field in a second migration
• CRUD service: Type-safe query functions for each model
• Transaction: Create a post and update category count atomically
• Connection pool: Global PrismaClient with hot reload protection

What's Next

You've mastered type-safe database access with TypeScript. Now learn how to test your database access layer with {{< ref "48-testing" >}}, or explore advanced patterns with {{< ref "49-advanced-patterns" >}}.

For architecture best practices, see {{< ref "50-solid-principles" >}}.