Deepening DB Design with NestJS × Prisma ─ Models, Relations, and Operational Design

Introduction

Up to this point in the series, we’ve used NestJS and Prisma to implement basic CRUD operations for an article posting API, and we’ve worked on logging, error handling, and setting up a test environment.

In this fourth part, we’ll take one step further into database design with Prisma and organize more practical, operations-oriented topics such as defining multiple models, designing relations, and managing migrations.

We’ve already introduced the design of the PrismaClient instance (DI support via PrismaService and PrismaModule), so this time we’ll briefly review that and then expand into the relationships between multiple models and database operations that take into account the full lifecycle from development to production.

This article is intended for readers like the following:

• Those who want to seriously start backend development using NestJS and Prisma
• Those who still feel uncertain about database design and want to grasp best practices for relation definitions and migration management
• Those who have introduced Prisma but are struggling with coordination between multiple models or with operational policies

This article continues from the content covered in parts 1–3.
If you haven’t read them yet, it’s recommended to start from part 1 and read through to part 3 in order.

Part 1

https://shinagawa-web.com/en/blogs/nestjs-blog-series-setup-and-config

Part 2

https://shinagawa-web.com/en/blogs/nestjs-blog-series-crud-and-prisma-intro

Part 3

https://shinagawa-web.com/en/blogs/nestjs-blog-series-logging-error-testing

Overall structure of this series and where this part fits

Series structure

1. Start Web App Development with NestJS ─ Learn Project Structure and Configuration Management by Building a Blog Site
2. Build an Article Posting API with NestJS ─ Basics of Introducing Prisma and Implementing CRUD
3. Improve Application Reliability ─ Logging, Error Handling, and Test Strategy
4. Deep Dive into Prisma and DB Design ─ Models, Relations, and Operational Design ← This article
5. Build the UI with React and Deploy to Production ─ Docker and Environment Setup

Defining Multiple Models with Prisma

In this chapter, we’ll organize how to actually define multiple models in a Prisma schema file.

As an application gradually grows, it’s not realistic to keep managing data with a single model.
You’ll typically have multiple models such as User, Post, and Comment, and you’ll need to design their relationships appropriately.

Here, we’ll define a simple example with a User model and a Post model.

Example of User and Post models

Define the following in your Prisma schema file:

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

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

Here we’re defining a one-to-many (1:N) relation between User and Post.
One user can have multiple posts, and each post must be associated with exactly one user.

Recommendations for model definitions that leverage type completion

One of Prisma’s strengths is that as soon as you write your schema file, types are immediately generated on the TypeScript side (Prisma Client).

Once you define models, you can use type-safe completion like this:

const newPost = await prisma.post.create({
  data: {
    title: 'My first post',
    content: 'Hello World!',
    author: {
      connect: { id: 1 },
    },
  },
});

In this case, field name or type mistakes are caught at compile time, greatly reducing the risk of runtime errors.
Carefully organizing the intent behind field names and types at the model design stage has a huge impact on the later development experience.

Tips for naming and directives in Prisma schema

In a Prisma schema, small practices like the following can lead to long-term development efficiency:

• Use singular form for model names (e.g., User, Post, Comment)
• Always include createdAt and updatedAt
• Use plural names for relation fields that are lists (e.g., User.posts)
• Clearly distinguish between optional fields (?) and required fields

By keeping these in mind, the schema file itself can function as documentation and provide a data structure that’s easy to understand for team members who join the project later.

Common naming mistakes

On the other hand, there are some common naming mistakes that tend to occur in the early stages of model definition:

• Using plural names for models
  Example: Users, Posts
  → This leads to awkward code like prisma.users.findMany() in PrismaClient.

• Using singular names for relation fields that are actually lists
  Example: User.post: Post[]
  → The actual data is plural, but the name is singular, which causes confusion when reading code.

• Using generic nouns that don’t convey intent for field names
  Example: User.data, Post.info
  → It’s unclear “what” they refer to, increasing the burden of reading the schema later.

To avoid these mistakes, it’s important to keep in mind: singular for models, plural for list-type relations, and field names as concrete as possible.

Defining Relations and Points to Watch

In applications with multiple models, how you connect those models (relation design) becomes extremely important.

Here, we’ll organize the basics of defining relations in Prisma and the points you should pay attention to during design.

How to write @relation with fields and references

In Prisma, you use the @relation attribute when defining relations.

For example, here’s how you’d write a case where the Post model is linked to the User model:

model User {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]
}

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

Key points here are:

• The Post side holds the foreign key (authorId)
• @relation(fields: [...], references: [...]) explicitly specifies which columns are connected

In Prisma, explicitly specifying fields and references is very important.
If you omit them, you may end up with unintended relations or frequent migration errors.

Patterns for 1:N, 1:1, and N:N

There are several relation patterns:

• One-to-many (1:N)
  Example: A User has multiple Posts

• One-to-one (1:1)
  Example: A User has exactly one Profile

• Many-to-many (N:N)
  Example: A Post is linked to multiple Tags, and a Tag is linked to multiple Posts

Each pattern requires an appropriate relation design.
Especially for N:N, it’s important to intentionally choose whether to auto-generate a join table or to create an intermediate model manually.

Usage examples and considerations for onDelete and onUpdate

When designing relations, you need to decide what to do with child models when a parent model is deleted.

In Prisma, you can specify onDelete and onUpdate as options in the @relation attribute.

Example:

author   User   @relation(fields: [authorId], references: [id], onDelete: Cascade)

• Cascade
  When the parent is deleted, the children are automatically deleted as well (e.g., deleting a User also deletes their Posts)

• Restrict
  The parent cannot be deleted as long as children exist (default)

• SetNull
  When the parent is deleted, the child’s foreign key is set to null

It’s important to choose the appropriate setting based on your operational policy.
In particular, when using Cascade, you must be very careful because accidentally deleting parent data can also delete a large amount of child data.

Common Prisma-specific relation definition mistakes

With Prisma, the following kinds of trouble due to relation misconfiguration are common:

• Defining fields on only one side
  (e.g., Post has authorId, but User doesn’t have posts, etc.)

• Omitting fields and references, causing migration errors

• Getting confused by the auto-generated join table in N:N relations

To prevent such mistakes:

• As a rule, design relations so they can be traversed “from both sides”
• Always explicitly specify fields/references

In the next chapter, we’ll look at how to manage migrations for the models and relations we’ve defined, and how to handle the DB across the development-to-production flow.

Migration Strategy with Prisma

After designing your models, you need to reflect them in the actual database.
Prisma’s migration feature manages this process.

In this chapter, we’ll organize how to use Prisma’s migration commands and the points to watch out for in operations.

Differences and use cases for migrate dev, db push, and migrate deploy

Prisma provides several commands for applying schema changes. Their roles and use cases are as follows:

During development, you mainly use migrate dev,
and in test environments you may use db push when you want to try things quickly.

In production, you should always use migrate deploy to maintain consistent migration history.

Handling migration history (migrations/)

When you run migrate dev, migration files are generated in the prisma/migrations/ directory.

These files should be managed with Git just like your code.
In other words, think of the evolution history of your schema as part of your source code.

Points to note here:

• Don’t arbitrarily edit migration files
• For unnecessary migrations (do-overs), basically recreate them or explicitly reset

It’s important to have such operational rules.

Editing schema.prisma and Git workflow considerations

Once you edit your schema file (schema.prisma), the basic flow is:

1. Edit the schema (add, remove, or modify fields)
2. Run npx prisma migrate dev to generate migration files
3. Verify behavior (check that the DB has been updated as expected)
4. Commit the changes to Git (both the schema and migrations)

If you change only the schema and commit without generating migrations,
you risk not being able to reproduce the environment later.

Therefore, make it a habit to “always run migrate after modifying schema.prisma.”

Reviewing the PrismaClient Instance Design

Here, we’ll review the PrismaClient instance design we’ve introduced so far.

PrismaClient is a critical component that communicates directly with the database, and when integrating it with a framework like NestJS, you need a design that takes lifecycle management (connect/disconnect) into account.

DI considerations and the structure of PrismaService

In NestJS, you use dependency injection (DI) to keep components loosely coupled.
PrismaClient is no exception; the basic approach is to make it DI-ready as a service class (PrismaService).

The PrismaService we created looks like this:

import { Injectable, OnModuleInit, OnModuleDestroy } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy {
  async onModuleInit() {
    await this.$connect(); // Connect to DB when the app starts
  }

  async onModuleDestroy() {
    await this.$disconnect(); // Disconnect from DB when the app shuts down
  }
}

With this structure:

• The app automatically connects to the DB at startup
• It safely closes the DB connection at shutdown

so lifecycle management is automated.

Reuse design with PrismaModule

To reuse PrismaService across components, we created PrismaModule and registered it in NestJS’s imports so it can be used.

PrismaModule is very simple:

import { Module } from '@nestjs/common';
import { PrismaService } from './prisma.service';

@Module({
  providers: [PrismaService],
  exports: [PrismaService],
})
export class PrismaModule {}

This allows you to import it in, for example, PostsModule like this:

@Module({
  imports: [PrismaModule],
  providers: [PostsService],
})
export class PostsModule {}

With this design:

• PrismaService can be safely shared across multiple modules
• Even if the implementation of PrismaService changes, it can be swapped out without affecting dependents

giving you a highly flexible structure.

Connection control with onModuleInit / onModuleDestroy

By implementing OnModuleInit and OnModuleDestroy in PrismaService:

• The DB is connected immediately after the app starts
• The connection is cleanly closed when the app stops

This prevents issues like leftover unnecessary connections when you repeatedly start and stop the app in E2E tests.

It’s a small mechanism, but having it greatly improves the overall stability and operational ease of the system.

Environment Variables and Switching DB Connections

Depending on the environment in which your application runs—development, test, production, etc.—you need to properly switch the database you connect to and the settings you use.

In this chapter, we’ll organize how to separate DB connections by environment and how to practically switch them with NestJS and Prisma.

Separating connection targets for E2E / CI environments and why it matters

In test environments (especially E2E tests and CI environments), it’s better to configure the system so that it connects only to a dedicated test DB, in order to prevent accidents like “breaking the development DB” or “accessing production data.”

This gives you benefits such as:

• You can reset data when tests fail
• Tests don’t affect other environments
• You can safely protect production and development environments

It may look like a minor design detail, but it has a very significant impact when you think about long-term operations.

Migrations and Initialization in the Test Environment

Once you’ve prepared a test database, the next step is to apply the necessary schema to that database and set up a mechanism to keep it in a clean state for each test run.

In this chapter, we’ll organize how to apply migrations in the test environment and a practical flow for initializing the database before tests.

Timing for db push or migrate deploy

For preparing schemas in the test environment, you use the following two commands depending on the situation:

In E2E tests, you often want to reflect schema changes quickly, so db push tends to be used more.

On the other hand, in CI/CD pipelines and production environments, the basic practice is to use migrate deploy to maintain consistent history.

Resetting the database before tests

In the test environment, it’s very important to reset the database to a clean state for each test run.

One example is to prepare a helper function for resetting in something like test/utils/db.ts.

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

export async function resetDatabase() {
  await prisma.post.deleteMany();
  // Reset other tables as needed
}

In your test code, call this in beforeAll:

beforeAll(async () => {
  await resetDatabase();
});

This ensures that:

• Every test starts from an independent, clean state
• Test results are not affected by side effects from previous tests

creating a healthy test environment.

Handling seed data and points to watch in tests

In some test cases, you may want to run tests under the assumption that the database is not empty but has some initial data.

For example:

• You want to test fetching an article list, so you need a few articles registered beforehand.
• For tests that require user authentication, you need to create a test user account in advance.

In such cases, create minimal test data during test setup.

Concrete example: inserting initial data for tests

For example, you can create initial data after resetting inside the resetDatabase() function:

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

export async function resetDatabase() {
  // Reset all tables
  await prisma.post.deleteMany();
  await prisma.user.deleteMany();

  // Create necessary initial data
  const user = await prisma.user.create({
    data: {
      email: 'test@example.com',
      name: 'Test User',
    },
  });

  await prisma.post.createMany({
    data: [
      { title: 'First Post', content: 'Content 1', authorId: user.id },
      { title: 'Second Post', content: 'Content 2', authorId: user.id },
    ],
  });
}

With this setup, every test can reliably start from a state where “a specific user and multiple posts exist,” so the preconditions for each test run remain consistent.

✅ Points to keep in mind when designing initial data

• Create only the minimum necessary data
  If you create a lot of data unrelated to the test target, tests become slower and failures harder to diagnose.

• Always keep “what exists” explicit
  Structure things so that it’s natural in test code to assume, for example, “there is a user with id: 1.”

• Always run reset → seed as a pair
  Completely eliminate side effects between tests and create an independent world for each test.

Being conscious of this initial data management greatly improves test readability, maintainability, and ease of debugging when failures occur.
It’s a subtle but very important part that underpins test reliability.

Conclusion

In this article, we’ve gone through the fundamentals of more practical database design, relation definitions, and migration operations when developing with NestJS and Prisma.

Rather than just building APIs that “work,” we’ve focused on:

• Carefully designing relationships between models
• Integrating PrismaClient naturally with NestJS
• Switching DB connections for development, test, and production environments
• Keeping the test environment in a clean state

and have steadily built up “operations-aware design skills.”

The topics covered this time may each look like small, mundane tasks.
However, carefully stacking these up leads to a big difference when the project grows.

Next time, we’ll finally move on to connecting with the frontend (React) and designing the deployment setup for production.
Let’s proceed together through the process of making the system accessible from the browser, not just via APIs.

https://shinagawa-web.com/en/blogs/nestjs-blog-series-react-deploy