TypeScript

TypeScript with Databases

38 min Lesson 32 of 40

TypeScript with Databases

TypeScript brings powerful type safety to database interactions, helping you catch schema mismatches, invalid queries, and data inconsistencies before they reach production. In this lesson, we'll explore how to type ORM models, work with Prisma and TypeORM, and build type-safe database query patterns that provide compile-time validation and excellent developer experience.

Why Type Your Database Layer?

Adding TypeScript to your database operations provides critical advantages:

Schema Validation: Ensure your code matches your database schema at compile time
Query Safety: Catch typos in column names and invalid filter conditions before runtime
Autocomplete: Get IntelliSense for all model properties, relationships, and query methods
Migration Safety: TypeScript errors alert you to code that needs updating when schema changes
Data Integrity: Enforce required fields, correct data types, and valid relationships

Prisma - Type-Safe ORM

Prisma is a next-generation ORM that generates TypeScript types directly from your database schema. It provides the best TypeScript integration of any ORM.

Prisma Schema Definition:

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        Int      @@id @@default(autoincrement())
  email     String   @@unique
  username  String   @@unique
  firstName String
  lastName  String
  avatar    String?
  posts     Post[]
  comments  Comment[]
  createdAt DateTime @@default(now())
  updatedAt DateTime @@updatedAt

  @@map("users")
}

model Post {
  id          Int       @@id @@default(autoincrement())
  title       String
  slug        String    @@unique
  content     String
  excerpt     String?
  published   Boolean   @@default(false)
  authorId    Int
  author      User      @@relation(fields: [authorId], references: [id])
  comments    Comment[]
  tags        Tag[]
  viewCount   Int       @@default(0)
  publishedAt DateTime?
  createdAt   DateTime  @@default(now())
  updatedAt   DateTime  @@updatedAt

  @@map("posts")
}

model Comment {
  id        Int      @@id @@default(autoincrement())
  content   String
  postId    Int
  post      Post     @@relation(fields: [postId], references: [id])
  userId    Int
  user      User     @@relation(fields: [userId], references: [id])
  createdAt DateTime @@default(now())

  @@map("comments")
}

model Tag {
  id    Int    @@id @@default(autoincrement())
  name  String @@unique
  posts Post[]

  @@map("tags")
}

Note: After defining your Prisma schema, run npx prisma generate to generate fully-typed TypeScript client code. Prisma automatically creates types for all your models, including relationships and query operations.

Prisma Client Usage

The generated Prisma Client provides complete type safety for all database operations:

Type-Safe CRUD Operations:

import { PrismaClient, User, Post, Prisma } from '@prisma/client';

const prisma = new PrismaClient();

// Create - fully typed input
async function createUser(data: {
  email: string;
  username: string;
  firstName: string;
  lastName: string;
  avatar?: string;
}): Promise<User> {
  return prisma.user.create({
    data,
  });
}

// Read - typed return value with relations
async function getUserWithPosts(id: number) {
  return prisma.user.findUnique({
    where: { id },
    include: {
      posts: {
        where: { published: true },
        orderBy: { createdAt: 'desc' },
      },
    },
  });
}
// Return type is automatically inferred:
// User & { posts: Post[] }

// Update - typed input and return
async function updateUser(
  id: number,
  data: Prisma.UserUpdateInput
): Promise<User> {
  return prisma.user.update({
    where: { id },
    data,
  });
}

// Delete
async function deleteUser(id: number): Promise<User> {
  return prisma.user.delete({
    where: { id },
  });
}

Advanced Prisma Queries

Prisma provides sophisticated query capabilities with full type safety:

Complex Queries:

// Filtered queries with nested relations
async function searchPosts(search: string, limit: number = 10) {
  return prisma.post.findMany({
    where: {
      OR: [
        { title: { contains: search, mode: 'insensitive' } },
        { content: { contains: search, mode: 'insensitive' } },
      ],
      published: true,
    },
    include: {
      author: {
        select: {
          id: true,
          username: true,
          avatar: true,
        },
      },
      comments: {
        where: { createdAt: { gte: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000) } },
        take: 3,
      },
      tags: true,
    },
    orderBy: {
      publishedAt: 'desc',
    },
    take: limit,
  });
}

// Aggregations and grouping
async function getPostStatistics() {
  const stats = await prisma.post.aggregate({
    _count: true,
    _avg: { viewCount: true },
    _sum: { viewCount: true },
    _max: { viewCount: true },
    where: { published: true },
  });

  return stats;
}

// Count queries
async function getUserPostCount(userId: number) {
  return prisma.post.count({
    where: {
      authorId: userId,
      published: true,
    },
  });
}

// Pagination
async function getPaginatedPosts(page: number = 1, perPage: number = 20) {
  const skip = (page - 1) * perPage;

  const [posts, total] = await Promise.all([
    prisma.post.findMany({
      skip,
      take: perPage,
      where: { published: true },
      include: { author: true, tags: true },
      orderBy: { publishedAt: 'desc' },
    }),
    prisma.post.count({ where: { published: true } }),
  ]);

  return {
    data: posts,
    meta: {
      page,
      perPage,
      total,
      totalPages: Math.ceil(total / perPage),
    },
  };
}

Tip: Use Prisma's select and include options to control exactly which fields and relations are returned. TypeScript will automatically narrow the return type based on your query structure.

Prisma Transactions

Execute multiple operations atomically with type-safe transactions:

Transaction Examples:

// Sequential transaction
async function createPostWithTags(
  authorId: number,
  postData: {
    title: string;
    slug: string;
    content: string;
    excerpt?: string;
  },
  tagNames: string[]
) {
  return prisma.$transaction(async (tx) => {
    // Create or find tags
    const tags = await Promise.all(
      tagNames.map((name) =>
        tx.tag.upsert({
          where: { name },
          create: { name },
          update: {},
        })
      )
    );

    // Create post with tags
    const post = await tx.post.create({
      data: {
        ...postData,
        authorId,
        tags: {
          connect: tags.map((tag) => ({ id: tag.id })),
        },
      },
      include: {
        tags: true,
        author: true,
      },
    });

    return post;
  });
}

// Batch transaction
async function deleteUserAndContent(userId: number) {
  return prisma.$transaction([
    prisma.comment.deleteMany({ where: { userId } }),
    prisma.post.deleteMany({ where: { authorId: userId } }),
    prisma.user.delete({ where: { id: userId } }),
  ]);
}

TypeORM - Decorator-Based ORM

TypeORM uses TypeScript decorators to define entities and provides excellent type safety:

Entity Definitions:

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
  ManyToOne,
  OneToMany,
  ManyToMany,
  JoinTable,
  Index,
} from 'typeorm';

@@Entity('users')
export class User {
  @@PrimaryGeneratedColumn()
  id: number;

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

  @@Column({ unique: true })
  username: string;

  @@Column()
  firstName: string;

  @@Column()
  lastName: string;

  @@Column({ nullable: true })
  avatar?: string;

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

  @@OneToMany(() => Comment, (comment) => comment.user)
  comments: Comment[];

  @@CreateDateColumn()
  createdAt: Date;

  @@UpdateDateColumn()
  updatedAt: Date;
}

@@Entity('posts')
export class Post {
  @@PrimaryGeneratedColumn()
  id: number;

  @@Column()
  title: string;

  @@Column({ unique: true })
  slug: string;

  @@Column('text')
  content: string;

  @@Column({ type: 'text', nullable: true })
  excerpt?: string;

  @@Column({ default: false })
  published: boolean;

  @@Column()
  authorId: number;

  @@ManyToOne(() => User, (user) => user.posts)
  author: User;

  @@OneToMany(() => Comment, (comment) => comment.post)
  comments: Comment[];

  @@ManyToMany(() => Tag, (tag) => tag.posts)
  @@JoinTable()
  tags: Tag[];

  @@Column({ default: 0 })
  viewCount: number;

  @@Column({ nullable: true })
  publishedAt?: Date;

  @@CreateDateColumn()
  createdAt: Date;

  @@UpdateDateColumn()
  updatedAt: Date;
}

@@Entity('comments')
export class Comment {
  @@PrimaryGeneratedColumn()
  id: number;

  @@Column('text')
  content: string;

  @@Column()
  postId: number;

  @@ManyToOne(() => Post, (post) => post.comments)
  post: Post;

  @@Column()
  userId: number;

  @@ManyToOne(() => User, (user) => user.comments)
  user: User;

  @@CreateDateColumn()
  createdAt: Date;
}

@@Entity('tags')
export class Tag {
  @@PrimaryGeneratedColumn()
  id: number;

  @@Column({ unique: true })
  name: string;

  @@ManyToMany(() => Post, (post) => post.tags)
  posts: Post[];
}

Warning: TypeORM requires experimentalDecorators and emitDecoratorMetadata to be enabled in your tsconfig.json. Without these settings, decorator-based entities will not work.

TypeORM Repository Pattern

TypeORM provides typed repositories for database operations:

Repository Usage:

import { AppDataSource } from './data-source';
import { User, Post } from './entities';

const userRepository = AppDataSource.getRepository(User);
const postRepository = AppDataSource.getRepository(Post);

// Create
async function createUser(data: {
  email: string;
  username: string;
  firstName: string;
  lastName: string;
  avatar?: string;
}): Promise<User> {
  const user = userRepository.create(data);
  return userRepository.save(user);
}

// Read with relations
async function getUserWithPosts(id: number): Promise<User | null> {
  return userRepository.findOne({
    where: { id },
    relations: ['posts', 'posts.tags'],
  });
}

// Complex query with QueryBuilder
async function searchPosts(search: string, limit: number = 10) {
  return postRepository
    .createQueryBuilder('post')
    .leftJoinAndSelect('post.author', 'author')
    .leftJoinAndSelect('post.tags', 'tags')
    .where('post.published = :published', { published: true })
    .andWhere(
      '(post.title ILIKE :search OR post.content ILIKE :search)',
      { search: `%${search}%` }
    )
    .orderBy('post.publishedAt', 'DESC')
    .take(limit)
    .getMany();
}

// Update
async function updateUser(
  id: number,
  data: Partial<User>
): Promise<void> {
  await userRepository.update(id, data);
}

// Delete
async function deleteUser(id: number): Promise<void> {
  await userRepository.delete(id);
}

Custom Repository Classes

Create custom repository classes for complex domain logic:

Custom Repository:

import { Repository } from 'typeorm';
import { Post } from './entities/Post';

export class PostRepository extends Repository<Post> {
  async findPublished(limit: number = 10): Promise<Post[]> {
    return this.find({
      where: { published: true },
      relations: ['author', 'tags'],
      order: { publishedAt: 'DESC' },
      take: limit,
    });
  }

  async findBySlug(slug: string): Promise<Post | null> {
    return this.findOne({
      where: { slug },
      relations: ['author', 'tags', 'comments', 'comments.user'],
    });
  }

  async incrementViewCount(id: number): Promise<void> {
    await this.increment({ id }, 'viewCount', 1);
  }

  async getMostViewed(limit: number = 5): Promise<Post[]> {
    return this.find({
      where: { published: true },
      order: { viewCount: 'DESC' },
      take: limit,
      relations: ['author'],
    });
  }

  async getPostsByTag(tagName: string, limit: number = 10): Promise<Post[]> {
    return this.createQueryBuilder('post')
      .leftJoinAndSelect('post.author', 'author')
      .leftJoinAndSelect('post.tags', 'tags')
      .where('tags.name = :tagName', { tagName })
      .andWhere('post.published = :published', { published: true })
      .orderBy('post.publishedAt', 'DESC')
      .take(limit)
      .getMany();
  }
}

Database Query Type Helpers

Create reusable type helpers for common query patterns:

Type Helpers:

// Generic paginated result type
interface PaginatedResult<T> {
  data: T[];
  meta: {
    page: number;
    perPage: number;
    total: number;
    totalPages: number;
  };
}

// Generic filter builder
type FilterOperator = 'eq' | 'ne' | 'gt' | 'gte' | 'lt' | 'lte' | 'in' | 'contains';

interface Filter<T> {
  field: keyof T;
  operator: FilterOperator;
  value: any;
}

// Generic sort options
type SortDirection = 'asc' | 'desc';

interface SortOption<T> {
  field: keyof T;
  direction: SortDirection;
}

// Query builder helper
interface QueryOptions<T> {
  filters?: Filter<T>[];
  sort?: SortOption<T>[];
  page?: number;
  perPage?: number;
  include?: string[];
}

// Prisma helper function
async function queryWithOptions<T>(
  model: any,
  options: QueryOptions<T>
): Promise<PaginatedResult<T>> {
  const { filters = [], sort = [], page = 1, perPage = 20, include = [] } = options;

  const where: any = {};
  filters.forEach((filter) => {
    // Build where clause based on operator
    // Implementation depends on your ORM
  });

  const orderBy: any = {};
  sort.forEach((s) => {
    orderBy[s.field] = s.direction;
  });

  const skip = (page - 1) * perPage;

  const [data, total] = await Promise.all([
    model.findMany({
      where,
      orderBy,
      skip,
      take: perPage,
      include: include.reduce((acc, rel) => ({ ...acc, [rel]: true }), {}),
    }),
    model.count({ where }),
  ]);

  return {
    data,
    meta: {
      page,
      perPage,
      total,
      totalPages: Math.ceil(total / perPage),
    },
  };
}

Exercise:

Create a Prisma schema with at least 3 related models (e.g., Author, Book, Review)
Generate the Prisma Client and write type-safe CRUD functions for each model
Implement a complex query that filters, sorts, and includes nested relations
Create a transaction that updates multiple related records atomically
Build a custom repository class with domain-specific query methods
Implement a generic pagination helper with type-safe filtering and sorting

Summary

In this lesson, you learned how to work with databases in TypeScript using Prisma and TypeORM. You explored how to define type-safe schemas, perform CRUD operations with full type checking, build complex queries with autocomplete support, and implement transactions and custom repository patterns. These techniques enable you to catch database-related errors at compile time, maintain consistency between your code and schema, and build robust data layers with excellent developer experience.