Mastering Data Relationships: A Comprehensive Guide to Building Prisma Schemas

Hi there 👋,

In the landscape of software development, data management stands as a critical cornerstone that determines the efficiency and scalability of applications.

In this article, we will learn about the basic concept of Prisma and the relationships in Prisma.

Prisma is an open-source Object-Relational Mapping (ORM) tool that makes complex data interactions easy to handle. Prisma’s ORM approach streamlines database workflows by offering a clear and simple way to interact with the database through its auto-generated query builder. It supports a variety of databases, including PostgreSQL, MySQL, SQL Server, SQLite, and MongoDB, and it can be used for a wide range of development requirements.

Why Use Prisma?

Prisma’s design philosophy centers on three core principles: ease of use, performance, and robustness. Unlike traditional ORMs, which often introduce complexity and performance overhead, Prisma operates at a lower level, offering direct access to the database with minimal abstraction. This design choice not only enhances performance but also ensures that developers maintain control over their database operations, a critical aspect for complex applications.

Benefits of Prisma

1. Enhanced Developer Productivity: Prisma’s schema-first approach allows developers to define their application models and relationships in a human-readable Prisma Schema file. Making it easier to manage changes and migrations.
2. Strong Type Safety: One of Prisma’s standout features is its support for type safety. Integrated with TypeScript, Prisma ensures that the data your application reads from and writes to the database is type-checked. This integration reduces bugs and errors at compile-time, enhancing the overall reliability of applications.
3. Database Compatibility: Prisma’s support for multiple databases and its cross-platform nature make it a flexible option for developers working in diverse environments.
4. Simplified Migrations: Prisma Migrate, an integral part of the Prisma suite, automates complex database migrations with ease. It interprets changes to the Prisma schema and translates them into SQL migration files, handling database schema transformations effortlessly.

How Prisma Works?

Prisma fundamentally transforms the way applications interact with databases by introducing an innovative architecture that simplifies data operations. The workflow of Prisma can be broken down into several key stages, starting from schema definition to query execution, which together ensure efficient and optimized database interactions.

#1: Schema Definition and Migration

Everything in Prisma begins with the Prisma schema. This schema is not just a cornerstone for database operations but also acts as a blueprint for your application’s data architecture. In the Prisma schema, you define your database models and the relationships between them using Prisma’s own intuitive schema language. This approach to defining models and relationships is designed to be straightforward and easy to understand, whether you are working with relational databases like PostgreSQL or MySQL, or with non-relational databases like MongoDB.

For instance, here’s a sample Prisma schema model for a relational database using Postgres:

generator client {
    provider      = "prisma-client-js"
}
datasource db {
    provider = "postgresql"
    url      = env("DATABASE_URL")
}
model Post {
  id          Int     @id @default(autoincrement())
  title       String
  content     String?
  isPublished Boolean @default(false)
  createdAt   DateTime @default(now())
}

sample Prisma schema model for a non-relational database using MongoDB:

generator client {
    provider      = "prisma-client-js"
}
datasource db {
    provider = "mongodb"
    url      = env("DATABASE_URL")
}
model Post {
  id          Int     @id @default(auto()) @map("_id") @db.ObjectId
  title       String
  content     String?
  isPublished Boolean @default(false)
  createdAt   DateTime @default(now())
}

These examples illustrate how to define a simple model Post with various fields in Postgres and MongoDB. You can start with such a schema in a new project or generate a similar schema from an existing database, leveraging Prisma’s flexibility and database-agnostic nature.

#2: Migration and Type Generation

Once the schema is defined, the next step involves migrating this schema to your database. This is done using the prisma migrate command, which automatically generates SQL migration files based on your schema changes. These migrations adjust your database schema to match the models defined in your Prisma schema. Simultaneously, Prisma generates TypeScript or JavaScript types in the node_modules/.prisma/client directory. These types are reflective of your models and their relationships, providing type-safe database access and facilitating development by enabling IDE auto-completion and error-checking.

#3: Query Processing and Optimization

When it comes to querying the database, Prisma employs a powerful Query Engine. This engine acts as an intermediary between your application and the database. Here’s how it works: you write queries using the generated Prisma Client, and these queries are then passed to the Prisma Query Engine. The engine optimizes these queries and converts them into database-specific queries.

One of the significant advantages of this engine is its ability to optimize query performance, notably addressing the notorious N+1 query problem common in applications that use GraphQL. This problem occurs when an initial query is followed by multiple subsequent queries, one for each returned record. Prisma’s Query Engine smartly aggregates and optimizes these queries to fetch all required data in the most efficient way possible.

Seamless Integration and Management

Despite its deep involvement in data handling, the Prisma engine is a component you rarely interact with directly. All communications to the database are managed through this engine, ensuring that your data layer remains robust, efficient, and scalable. This architecture not only simplifies the development process but also enhances performance, making Prisma a powerful tool for modern application development.

Relationships in Prisma

Prisma provides a robust framework to model and manage relationships between data entities in both relational and non-relational databases. These relationships are crucial in representing how data connects across different parts of your application.

Prisma supports several types of relationships: one-to-one, one-to-many, and many-to-many. Each relationship type is defined explicitly in the Prisma schema, allowing for clear, understandable data modeling.

#1: One-to-One Relationships (1–1)

A one-to-one relationship occurs when one record in a table is associated with exactly one record in another table.

Example: Consider a user system where each user has a unique profile.

// Relational Database
model User {
  id       Int    @id @default(autoincrement())
  email    String @unique
  profile  Profile?
}
model Profile {
  id       Int    @id @default(autoincrement())
  bio      String
  userId   Int    @unique
  user     User   @relation(fields: [userId], references: [id])
}

// Non-Relational Database
 
model User {
  id       String @id @default(auto()) @map("_id") @db.ObjectId
  email    String @unique
  profile  Profile?
}
 
model Profile {
  id       String @id @default(auto()) @map("_id") @db.ObjectId
  bio      String
  userId   String @unique @db.ObjectId
  user     User   @relation(fields: [userId], references: [id])
}

In this example,

each User can have zero or one Profile (because the profile field is optional on User),

each Profile is linked to exactly one User.

You need to mark the field with the @unique attribute, to guarantee that there is only a single User connected to each Profile.

// Creating a user along with their profile
const user = await prisma.user.create({
  data: {
    email: "vinojan@example.com",
    profile: {
      create: {
        bio: "Lives in Sri Lanka",
      },
    },
  },
});
 
// Retrieving data
const usersWithProfiles = await prisma.user.findMany({
  include: {
    profile: true,
  },
});

Multi-field relations in relational Databases only:

// Relational Database
 
model User {
  firstName String
  lastName  String
  profile   Profile?
 
  @@id([firstName, lastName])
}
 
model Profile {
  id            Int    @id @default(autoincrement())
  userFirstName String
  userLastName  String
  user          User   @relation(fields: [userFirstName, userLastName], references: [firstName, lastName])
 
  @@unique([userFirstName, userLastName])
}

#2: One-to-Many Relationships (1–n)

A one-to-many relationship exists when a single record in one table can be associated with multiple records in another table.

Example: A blog where one user can write multiple posts.

// Relational Database
 
model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  posts Post[]
}
 
model Post {
  id      Int    @id @default(autoincrement())
  title   String
  content String
  userId  Int
  user    User   @relation(fields: [userId], references: [id])
}

// Non-Relational Database
model User {
  id    String @id @default(auto()) @map("_id") @db.ObjectId
  email String @unique
  posts Post[]
}
model Post {
  id      String @id @default(auto()) @map("_id") @db.ObjectId
  title   String
  content String
  userId  String @db.ObjectId
  user    User   @relation(fields: [userId], references: [id])
}

In this example, one User can have zero or multiple Posts, but each Post belongs to only one User.

You need to mark the field with the @unique attribute, to guarantee that there is only a single User connected to each Post.

// Creating a post along with the user
const newPost = await prisma.post.create({
  data: {
    title: "Prisma with Relational Databases",
    content: "Content here",
    user: {
      connect: { email: "vinojan@example.com" }, // Assuming the user already exists
    },
  },
});
// Retrieving data
const usersWithPosts = await prisma.user.findMany({
  include: {
    posts: true,
  },
});

Multi-field relations in relational Databases only:

// Relational Database
model User {
  firstName String
  lastName  String
  post      Post[]
  @@id([firstName, lastName])
}
model Profile {
  id            Int    @id @default(autoincrement())
  userFirstName String
  userLastName  String
  user          User   @relation(fields: [userFirstName, userLastName], references: [firstName, lastName])
}

Comparing ‘one-to-one’ and ‘one-to-many’ relations:

In relational databases, the main difference between a 1–1 and a 1-n relation is that in a 1–1-relation the foreign key must have a UNIQUE constraint defined on it. In non-relational databases, the only difference between a 1–1 and a 1-n is the number of documents referencing another document in the database.

#3.1: Many-to-Many Relationships (m–n) — Relational Databases

Many-to-many relationships allow multiple records in one table to be associated with multiple records in another table.

This type of relationship is often facilitated by a third table known as a “join table” or “junction table.” In relational databases, managing many-to-many relationships involves either explicit or implicit approaches, depending on how much control you need over the relationship and whether additional data is stored in the join table.

Explicit Many-to-Many Relationships:

Explicit many-to-many relationships are used when you need to store additional data on the relationship itself, beyond just the foreign keys. This approach involves manually defining the join table as a model in your schema.

Example: Consider a scenario where authors can write many books, and books can have many authors. You want to also store the role of each author for each book (e.g., writer, editor).

// Relational Database
model Author {
  id    Int    @id @default(autoincrement())
  name  String
  books BookAuthor[]
}
model Book {
  id      Int      @id @default(autoincrement())
  title   String
  authors BookAuthor[]
}
// Explicit join table with additional fields
model BookAuthor {
  bookId   Int
  book     Book   @relation(fields: [bookId], references: [id])
  authorId Int
  author   Author @relation(fields: [authorId], references: [id])
  role     String
  @@id([bookId, authorId])
}

In this example, BookAuthor is an explicit join table that includes bookId, authorId, and an additional field role to store the role of the author in the creation of the book. The @@id attribute specifies a composite primary key, ensuring uniqueness for each combination of bookId and authorId.

Implicit Many-to-Many Relationships:

Implicit many-to-many relationships are simpler and are used when no additional data needs to be stored in the join table other than the foreign keys. Prisma can abstract the join table, so you don’t have to explicitly define it in your schema.

Example: Suppose you have a scenario where students can enroll in many courses, and courses can have many students.

// Relational Database
model Student {
  id      Int      @id @default(autoincrement())
  name    String
  courses Course[] @relation("Enrollments")
}
model Course {
  id       Int       @id @default(autoincrement())
  title    String
  students Student[] @relation("Enrollments")
}

In this example, Prisma implicitly creates a join table to manage the relationship between Student and Course. The table will only contain studentId and courseId columns. Prisma handles the creation and management of this table behind the scenes, and you do not need to define or interact with it directly.

NOTE: Prisma recommend to using implicit m-n-relations if you do not need to store any additional meta-data in the relation table itself.

// Creating a book along with the authors
const newBook = await prisma.book.create({
  data: {
    title: "New Book Title",
    authors: {
      connect: [{ id: 1 }, { id: 2 }], // Assuming the authors with these IDs exist
    },
  },
});
// Retrieving data
const booksWithAuthors = await prisma.book.findMany({
  include: {
    authors: true,
  },
});

#3.2: Many-to-Many Relationships (m–n) — Non-Relational Databases

In non-relational databases like MongoDB, many-to-many relationships are typically managed differently due to the lack of a strict schema and the ability to embed documents. However, Prisma’s support for MongoDB and other NoSQL databases may still require explicit definition of relationships, particularly when using Prisma as it aligns more closely with relational database patterns.

Example: Consider a blogging platform where each post can belong to multiple categories, and each category can contain multiple posts.

// Non-Relational Database
model Post {
  id          String     @id @default(auto()) @map("_id") @db.ObjectId
  categoryIDs String[]   @db.ObjectId // This will store Object IDs of related categories
  categories  Category[] @relation(fields: [categoryIDs], references: [id])
}
model Category {
  id      String   @id @default(auto()) @map("_id") @db.ObjectId
  postIDs String[] @db.ObjectId // This will store Object IDs of related posts
  posts   Post[]   @relation(fields: [postIDs], references: [id])
}

In this example,

Post Model: Each Post document has a field categoryIDs, which is an array of Object IDs. These IDs refer to the Category documents that the post belongs to. The categories field is a virtual relation that uses the IDs from categoryIDs to fetch the related Category documents.

Category Model: Similarly, each Category document has a field postIDs, which is an array of Object IDs. These IDs point to the Post documents that fall under this category. The posts field is a virtual relation that uses the IDs from postIDs to fetch the related Post documents.

Creating a Post with Categories:

// Assume you already have some Category IDs
const categoryIds = ["5f8d4fe24fbd5f8d4fe2b96a", "5f8d4fe24fbd5f8d4fe2b96b"];
const newPost = await prisma.post.create({
  data: {
    categoryIDs: categoryIds, // Linking to existing categories
    // other fields...
  },
});

Fetching Posts with Their Categories:

const postsWithCategories = await prisma.post.findMany({
  include: {
    categories: true, // This includes the related categories for each post
  },
});

Adding a New Category to an Existing Post:

const updatedPost = await prisma.post.update({
  where: { id: "some-post-id" },
  data: {
    categoryIDs: { push: "new-category-id" }, // Adding a new category ID to the array
  },
});

Removing a Category from a Post:

const postUpdate = await prisma.post.update({
  where: { id: "some-post-id" },
  data: {
    categoryIDs: {
      set: post.categoryIDs.filter((id) => id !== "category-id-to-remove"),
    },
  },
});

Note: Prisma’s support for MongoDB does not typically involve automatic join tables as seen in relational databases, and developers may need to handle relationships through document embedding or manual reference management.

Conclusion

Prisma stands out as an essential tool in the modern developer’s toolkit, especially for those looking to build efficient, maintainable, and robust applications. By leveraging Prisma’s capabilities, developers can ensure that their data models and relationships are well-organized, easily updated, and optimally executed, paving the way for future-proof applications that scale with grace and ease. This exploration into Prisma’s relationship management has hopefully illuminated the path to more effective and streamlined database interactions, empowering you to elevate your projects to the next level.

I appreciate you taking the time to read this article.🙌

Before you move on to explore the next article, don’t forget to give your claps 👏 for this article and share with your friends. Stay connected with me on social media. Thanks for your support and have a great rest of your day! 🎊

✍️ Vinojan Veerapathirathasan.