Back to Blog
November 1, 2025nestjs

TypeORM & Database Design in NestJS: A Practical Guide

TypeORM & Database Design in NestJS: A Practical Guide
nestjstypeormpostgresqldatabasetypescriptbackendormsql

When building a backend, your database design decisions echo through every layer of your application. Get it right, and your code stays clean and maintainable. Get it wrong, and you're fighting your ORM at every turn.

In this post, I'll walk through how I structured the database for my portfolio backend using TypeORM – from basic entities to complex relationships, explaining not just the how but the why behind each decision.

What is TypeORM?

TypeORM is an Object-Relational Mapper for TypeScript and JavaScript. Instead of writing raw SQL queries, you define your database structure using TypeScript classes with decorators. TypeORM translates these into database tables and handles the conversion between database rows and JavaScript objects.

Why use an ORM at all? Three reasons:

  1. Type safety: Your database schema and TypeScript types stay in sync. If you rename a column, the compiler catches everywhere that needs updating.

  2. Abstraction: You write TypeScript, not SQL. Switching databases (say, from PostgreSQL to MySQL) requires minimal code changes.

  3. Relationships: Loading related data (a project with its images) becomes a configuration option rather than manual JOIN queries.

The trade-off is performance – hand-written SQL can be faster for complex queries. But for most CRUD operations, the developer experience improvement is worth it.

Entity Structure Overview

Before diving into code, here's the big picture of how my entities relate to each other:

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│    CvProfile    │────<│   Experience    │     │     Project     │
│                 │────<│   Education     │     │                 │
│                 │────<│   Certificate   │     │                 │
└─────────────────┘     └─────────────────┘     └────────┬────────┘
                                                         │
                                                         │ OneToMany
                                                         ▼
┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│   MediaAsset    │<────│  ProjectMedia   │<────│                 │
│                 │     │  (Join Table)   │     │                 │
└─────────────────┘     └─────────────────┘     └─────────────────┘

A CvProfile has multiple Experience and Education entries (one-to-many). A Project can have multiple MediaAsset items attached through a ProjectMedia join table (many-to-many with extra fields). This structure lets me manage CV data and portfolio projects from a single backend.

Entity Basics: Defining a Table

Let's start with a straightforward entity – a blog post. This example demonstrates all the fundamental TypeORM decorators:

typescript

// entities/blog-post.entity.ts
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
  Index,
} from 'typeorm';

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

  @Index()
  @Column({ length: 200 })
  title!: string;

  @Index({ unique: true })
  @Column({ length: 250, nullable: true })
  slug?: string;

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

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

  @Column({ type: 'simple-array', nullable: true })
  tags?: string[];

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

  @Column({ type: 'timestamptz', nullable: true })
  publishedAt?: Date;

  @CreateDateColumn({ type: 'timestamptz' })
  createdAt!: Date;

  @UpdateDateColumn({ type: 'timestamptz' })
  updatedAt!: Date;
}

Let's break down each decorator:

@Entity('blog_posts') marks this class as a database table. The string argument specifies the table name – without it, TypeORM would use the class name (BlogPost), but I prefer explicit snake_case names that match SQL conventions.

@PrimaryGeneratedColumn('uuid') creates an auto-generated primary key. The 'uuid' argument tells TypeORM to generate UUIDs instead of auto-incrementing integers. UUIDs are better for distributed systems and don't leak information about how many records exist.

@Column({ length: 200 }) defines a varchar column with a maximum length. Always set appropriate limits – it prevents accidental data bloat and helps with indexing.

@Index() creates a database index on the column, making queries that filter by this column much faster. The trade-off is slightly slower writes and more storage, but for frequently-queried columns like title, it's essential.

@Index({ unique: true }) creates a unique index, enforcing that no two rows can have the same value. Perfect for slugs – you never want two blog posts with the same URL.

@Column({ type: 'text' }) stores unlimited-length text. Use this for content that might be long (article body, descriptions) rather than fixed-length varchar.

@Column({ type: 'simple-array' }) is a TypeORM convenience feature. It stores arrays as comma-separated strings in a text column. Simple and works well for small arrays like tags. For complex arrays or when you need to query individual elements, use JSON instead.

@Column({ default: false }) sets a default value at the database level. New rows get this value automatically if you don't specify one.

@CreateDateColumn and @UpdateDateColumn are magic – TypeORM automatically sets createdAt when inserting and updates updatedAt on every save. You never have to manage these manually.

{ type: 'timestamptz' } specifies PostgreSQL's "timestamp with time zone" type. Always use this for dates in PostgreSQL – it stores UTC time and handles timezone conversion correctly. Using timestamp without timezone is a recipe for bugs when your server and users are in different timezones.

The ! (non-null assertion) tells TypeScript "trust me, this will be set." For columns with defaults or auto-generated values, TypeScript can't know they'll be populated, so we assert it.

The ? (optional property) marks nullable columns. This keeps your TypeScript types aligned with your database schema – if a column can be NULL, the property should be optional.

Column Types Reference

Here's a quick reference for mapping TypeORM types to PostgreSQL:

TypeORM TypePostgreSQL TypeWhen to Usestringvarchar(255)Short text, default length{ length: 200 }varchar(200)Short text, specific limittexttextLong text, no length limitintintegerWhole numbersbooleanbooleanTrue/false flagsdatedateDate only, no timetimestamptztimestamp with timezoneDate + time (use this!)jsonjsonbStructured data, queryablesimple-arraytextSimple arrays as CSVuuiduuidUniversally unique IDs

The json type in TypeORM maps to PostgreSQL's jsonb (binary JSON), which is indexed and queryable. This is powerful – you can store complex nested structures and still query individual fields with PostgreSQL's JSON operators.

JSON Columns: When Flexibility Beats Normalization

Not everything needs its own table. For nested data that you always load together and rarely query independently, JSON columns are perfect:

typescript

// cv/entities/cv-profile.entity.ts
@Entity('cv_profile')
export class CvProfile {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @Column({ length: 100 })
  firstName!: string;

  @Column({ length: 100 })
  lastName!: string;

  @Column({ type: 'json', default: '[]' })
  skills!: {
    category: string;
    items: Array<{
      name: string;
      level?: number;
      yearsOfExperience?: number;
    }>;
  }[];

  @Column({ type: 'json', default: '[]' })
  languages!: {
    name: string;
    level: string;
  }[];
}

Here, skills is a complex nested structure – categories containing arrays of skill items with optional metadata. Creating separate SkillCategory and Skill tables would require multiple JOINs every time you load a CV, and you'd never query skills independently anyway.

The JSON approach keeps related data together. When you load a CvProfile, skills come along as a ready-to-use JavaScript object. No extra queries, no object assembly.

When to use JSON columns:

  • Nested or hierarchical data

  • Variable structure (different records might have different fields)

  • Data you always load together with the parent

  • Data you don't need to filter or sort by at the database level

When to use separate tables instead:

  • You need to query or filter by the nested data

  • The data has its own lifecycle (created/updated independently)

  • You need referential integrity (foreign keys)

  • The nested data might be shared across multiple parents

Relationships: OneToMany and ManyToOne

When entities have distinct lifecycles or you need to query them independently, relationships are the way to go. A CV has multiple work experiences – each experience is its own record that can be created, updated, or deleted independently.

Here's the parent side (CvProfile):

typescript

// cv/entities/cv-profile.entity.ts
@Entity('cv_profile')
export class CvProfile {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @OneToMany(() => Experience, (exp) => exp.cvProfile, {
    cascade: true,
  })
  experiences!: Experience[];

  @OneToMany(() => Education, (edu) => edu.cvProfile, {
    cascade: true,
  })
  educations!: Education[];
}

And the child side (Experience):

typescript

// cv/entities/experience.entity.ts
@Entity('experiences')
export class Experience {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @Column({ length: 200 })
  jobTitle!: string;

  @Column({ length: 200 })
  company!: string;

  @Column({ type: 'date' })
  startDate!: Date;

  @Column({ type: 'date', nullable: true })
  endDate?: Date;

  @Column({ default: false })
  isCurrent!: boolean;

  @Column({ type: 'json', default: '[]' })
  technologies!: string[];

  @Column({ type: 'json', default: '[]' })
  highlights!: string[];

  @Column({ type: 'int', default: 0 })
  sortOrder!: number;

  @ManyToOne(() => CvProfile, (cv) => cv.experiences, {
    onDelete: 'CASCADE',
  })
  cvProfile!: CvProfile;
}

Let's unpack the relationship decorators:

@OneToMany(() => Experience, (exp) => exp.cvProfile) defines the "one" side – one CvProfile has many Experiences. The first argument is a function returning the related entity class (a function to avoid circular dependency issues). The second argument specifies which property on the related entity points back to this one.

@ManyToOne(() => CvProfile, (cv) => cv.experiences) is the inverse – many Experiences belong to one CvProfile. This is where the actual foreign key lives in the database.

{ cascade: true } on the OneToMany side enables automatic saving of child entities. When you save a CvProfile with an experiences array, TypeORM automatically inserts/updates all the Experience records. Without cascade, you'd have to save each experience separately.

{ onDelete: 'CASCADE' } on the ManyToOne side defines what happens when the parent is deleted. CASCADE means "delete this record too" – if you delete a CvProfile, all its Experiences are automatically removed. This maintains data integrity without orphaned records.

Notice that Experience uses JSON for technologies and highlights – these are simple string arrays that don't need their own tables. This hybrid approach (relational for entities with lifecycles, JSON for simple nested data) gives you the best of both worlds.

Many-to-Many with Join Tables

Sometimes you need a many-to-many relationship: a Project can have multiple MediaAssets, and a MediaAsset could theoretically be used in multiple Projects. TypeORM has a @ManyToMany decorator for simple cases, but I prefer explicit join tables because they allow extra fields.

The entities:

typescript

// entities/project.entity.ts
@Entity('projects')
export class Project {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @Column({ length: 160 })
  title!: string;

  @OneToMany(() => ProjectMedia, (pm) => pm.project, {
    cascade: true,
  })
  media!: ProjectMedia[];
}

// entities/media-assets.entity.ts
@Entity('media_assets')
export class MediaAsset {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @Column({ length: 200 })
  filename!: string;

  @Column({ length: 500 })
  url!: string;

  @Column({ length: 50 })
  mimeType!: string;

  @Column({ type: 'int', nullable: true })
  width?: number;

  @Column({ type: 'int', nullable: true })
  height?: number;
}

The join table with extra fields:

typescript

// entities/project-media.entity.ts
@Entity('project_media')
export class ProjectMedia {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @ManyToOne(() => Project, (p) => p.media, {
    onDelete: 'CASCADE',
  })
  project!: Project;

  @ManyToOne(() => MediaAsset, {
    eager: true,
    onDelete: 'CASCADE',
  })
  media!: MediaAsset;

  @Index()
  @Column({ type: 'int', default: 0 })
  position!: number;

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

Why go through this complexity instead of using @ManyToMany? The ProjectMedia join table has its own fields:

  • position: Controls the display order of images in a project gallery. You can drag-and-drop to reorder without touching the MediaAsset or Project.

  • caption: A description specific to this usage. The same image might have different captions in different projects.

The { eager: true } option on the MediaAsset relation means it's always loaded automatically. When you fetch a ProjectMedia, you don't have to explicitly request the related MediaAsset – it comes along for free. Use this sparingly (it can cause performance issues if overused), but for small related data you always need, it's convenient.

The Repository Pattern: Services and CRUD

TypeORM provides Repository classes for each entity. You inject these into your services and use them for all database operations:

typescript

// projects/projects.service.ts
import { Injectable, NotFoundException } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';

@Injectable()
export class ProjectsService {
  constructor(
    @InjectRepository(Project) private projects: Repository<Project>,
    @InjectRepository(ProjectMedia) private projectMedia: Repository<ProjectMedia>,
    @InjectRepository(MediaAsset) private mediaAssets: Repository<MediaAsset>,
  ) {}
}

@InjectRepository(Project) tells NestJS to inject the TypeORM repository for the Project entity. This repository has methods like find(), findOne(), save(), remove(), and more.

Let's implement the basic CRUD operations:

Create

typescript

async create(dto: CreateProjectDto): Promise<Project> {
  // Generate a URL-friendly slug from the title
  const slug = this.generateSlug(dto.title);

  // create() instantiates a new entity without saving
  const project = this.projects.create({
    title: dto.title,
    slug,
    description: dto.description,
  });

  // save() inserts into the database and returns the saved entity (with generated ID)
  return this.projects.save(project);
}

The difference between create() and save() is important: create() just creates a JavaScript object with the right type, while save() actually writes to the database. This separation lets you build up an entity before persisting it.

Read with Relations

typescript

async findOne(id: string): Promise<Project> {
  const project = await this.projects.findOne({
    where: { id },
    relations: { media: { media: true } },
  });

  if (!project) {
    throw new NotFoundException('Project not found');
  }

  // Sort media by position for consistent display order
  project.media.sort((a, b) => a.position - b.position);
  
  return project;
}

The relations option specifies which related entities to load. { media: { media: true } } means "load the media relation (ProjectMedia), and for each of those, also load their media relation (MediaAsset)." This is nested relation loading – one query fetches everything.

Without the relations option, project.media would be undefined. TypeORM doesn't load relations by default (except those marked eager: true) because loading everything would be wasteful for large datasets.

Read by Slug

typescript

async findBySlug(slug: string): Promise<Project> {
  const project = await this.projects.findOne({
    where: { slug },
    relations: { media: { media: true } },
  });

  if (!project) {
    throw new NotFoundException('Project not found');
  }

  return project;
}

Using slugs for public URLs (/projects/my-awesome-project) is more user-friendly and SEO-friendly than UUIDs (/projects/550e8400-e29b-41d4-a716-446655440000). The unique index on slug ensures fast lookups and prevents duplicates.

Update

typescript

async update(id: string, dto: UpdateProjectDto): Promise<Project> {
  // First, verify the project exists
  const project = await this.findOne(id);

  // Only update fields that were provided
  if (dto.title !== undefined) project.title = dto.title;
  if (dto.description !== undefined) project.description = dto.description;

  // save() on an existing entity performs an UPDATE
  return this.projects.save(project);
}

The pattern of checking !== undefined (not just truthy) allows setting fields to empty strings or zero. If you used if (dto.title), you couldn't clear a title.

Delete

typescript

async remove(id: string): Promise<{ ok: boolean }> {
  const project = await this.findOne(id);
  
  // remove() deletes the entity from the database
  await this.projects.remove(project);
  
  return { ok: true };
}

Because we defined onDelete: 'CASCADE' on the ProjectMedia relation, deleting a Project automatically deletes all its ProjectMedia entries. The database handles this – no manual cleanup needed.

QueryBuilder: Complex Queries Made Manageable

For simple queries, the repository methods work great. But when you need filtering, pagination, search, and complex ordering, the QueryBuilder provides full control:

typescript

async findAll(filters?: ProjectFilterDto): Promise<PaginatedResult<Project>> {
  // Default pagination values
  const page = filters?.page || 1;
  const limit = filters?.limit || 10;
  const search = filters?.search;

  // Start building the query
  const queryBuilder = this.projects
    .createQueryBuilder('project')
    .leftJoinAndSelect('project.media', 'projectMedia')
    .leftJoinAndSelect('projectMedia.media', 'media');

  // Conditional search across multiple columns
  if (search) {
    queryBuilder.where(
      'project.title ILIKE :search OR project.description ILIKE :search',
      { search: `%${search}%` },
    );
  }

  // Complex ordering: use projectDate if available, fall back to createdAt
  queryBuilder.orderBy(
    'COALESCE(project.projectDate, project.createdAt)',
    'DESC'
  );
  
  // Secondary sort: images in position order
  queryBuilder.addOrderBy('projectMedia.position', 'ASC');

  // Get total count before pagination (for "page X of Y")
  const total = await queryBuilder.getCount();
  
  // Apply pagination
  const data = await queryBuilder
    .skip((page - 1) * limit)
    .take(limit)
    .getMany();

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

Let's break down the QueryBuilder methods:

createQueryBuilder('project') starts a new query, with 'project' as the alias for the Project table. You'll use this alias throughout the query.

leftJoinAndSelect() performs a LEFT JOIN and includes the joined data in the result. Without AndSelect, the join would filter results but not load the related data.

ILIKE is PostgreSQL's case-insensitive LIKE operator. The % wildcards match any characters before and after the search term.

:search is a parameter placeholder. TypeORM escapes these properly, preventing SQL injection. Never concatenate user input directly into queries.

COALESCE() is a PostgreSQL function that returns the first non-NULL argument. Here, it sorts by projectDate when available, falling back to createdAt. This handles projects where the actual project date differs from when you added it to the database.

skip() and take() implement pagination. skip is the offset (how many records to skip), take is the limit (how many to return).

Cascade Operations: Saving Related Entities

With cascade: true, saving a parent automatically saves its children:

typescript

@OneToMany(() => Experience, (exp) => exp.cvProfile, {
  cascade: true,
})
experiences!: Experience[];

This enables elegant code:

typescript

const cv = new CvProfile();
cv.firstName = 'David';
cv.lastName = 'Developer';
cv.experiences = [
  { 
    jobTitle: 'Senior Developer', 
    company: 'Tech GmbH',
    startDate: new Date('2022-01-01'),
    isCurrent: true,
    technologies: ['TypeScript', 'NestJS', 'PostgreSQL'],
    highlights: ['Led team of 5', 'Reduced deploy time by 60%'],
  },
  { 
    jobTitle: 'Developer', 
    company: 'Startup AG',
    startDate: new Date('2019-06-01'),
    endDate: new Date('2021-12-31'),
    isCurrent: false,
    technologies: ['JavaScript', 'React', 'Node.js'],
    highlights: ['Built MVP from scratch'],
  },
];

// One save() call inserts the CvProfile AND both Experiences
await cvRepo.save(cv);

Without cascade, you'd need to save the CvProfile first (to get its ID), then save each Experience individually with the cvProfileId. Cascade handles this automatically.

Delete Strategies: onDelete Options

When a parent record is deleted, what should happen to its children? The onDelete option controls this:

StrategyWhat HappensUse CaseCASCADEDelete children tooChildren meaningless without parentSET NULLSet foreign key to NULLChildren can exist independentlyRESTRICTPrevent deletion if children existRequire explicit cleanup firstNO ACTIONSame as RESTRICT (default)Safety default

Examples:

typescript

// Experience is meaningless without a CvProfile – delete it too
@ManyToOne(() => CvProfile, (cv) => cv.experiences, {
  onDelete: 'CASCADE',
})
cvProfile!: CvProfile;

// MediaAsset can exist without a folder – just unfile it
@ManyToOne(() => MediaFolder, (folder) => folder.media, {
  nullable: true,
  onDelete: 'SET NULL',
})
folder?: MediaFolder;

Choose based on your domain logic. For my CV app, deleting a CvProfile should remove all associated experiences – they don't make sense on their own. But a media asset might be temporarily unfiled, so SET NULL makes sense there.

Module Setup: Wiring It All Together

Finally, here's how to register your entities with NestJS:

typescript

// projects/projects.module.ts
import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { Project } from '../entities/project.entity';
import { ProjectMedia } from '../entities/project-media.entity';
import { MediaAsset } from '../entities/media-assets.entity';
import { ProjectsController } from './projects.controller';
import { ProjectsService } from './projects.service';

@Module({
  imports: [
    // Register entities for this module's repositories
    TypeOrmModule.forFeature([Project, ProjectMedia, MediaAsset]),
  ],
  controllers: [ProjectsController],
  providers: [ProjectsService],
  exports: [ProjectsService], // Allow other modules to use this service
})
export class ProjectsModule {}

TypeOrmModule.forFeature() makes repositories for the specified entities available for injection within this module. Each entity you want to @InjectRepository() must be listed here.

Summary

Here's a cheatsheet of the patterns covered:

┌─────────────────────────────────────────────────────────────┐
│  TypeORM Patterns                                           │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  @Entity()         →  Defines a database table              │
│  @Column()         →  Defines a column                      │
│  @PrimaryGeneratedColumn('uuid')  →  Auto-generated ID      │
│  @CreateDateColumn →  Auto-set on insert                    │
│  @UpdateDateColumn →  Auto-updated on save                  │
│                                                             │
│  @OneToMany / @ManyToOne  →  1:n relationship               │
│  @ManyToMany              →  n:m relationship               │
│  Join Table               →  n:m with extra fields          │
│                                                             │
│  cascade: true     →  Auto-save children                    │
│  onDelete: 'CASCADE'  →  Auto-delete children               │
│  eager: true       →  Auto-load relation                    │
│                                                             │
│  Repository.find() →  Simple queries                        │
│  QueryBuilder      →  Complex queries with full control     │
│                                                             │
└─────────────────────────────────────────────────────────────┘

The key insight is matching your data model to TypeORM's strengths: use JSON for nested data you always load together, use relations for entities with independent lifecycles, and use QueryBuilder when the standard repository methods aren't enough.

TypeORM isn't the only choice for NestJS (Prisma and MikroORM are solid alternatives), but its decorator-based approach fits naturally with NestJS's style. Once you understand the patterns, database operations become almost declarative – you describe what you want, and TypeORM figures out the SQL.