Prisma models are at the core of database schema design in Prisma, allowing developers to define data structures in a readable and intuitive way. If you’re new to Prisma, think of models as representations of database tables, where each model field translates to a table column. This blog will walk you through creating models in Prisma, using a practical example of a Post model.

What is a Prisma Model?

In Prisma, a model is defined in the schema.prisma file, which serves as the single source of truth for your database schema. Prisma automatically generates the SQL statements required to create or modify your database structure, enabling you to work in a fully type-safe, database-agnostic environment.

Setting Up a Basic Post Model

Below is an example Post model in Prisma, which represents a blog post with attributes such as title, content, published status, timestamps, and author information. This structure gives us insight into Prisma's flexibility and how each field works.

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

  @@map("posts")
}

Let’s break down each component of this model.

Model Fields

1. id Int @id @default(autoincrement()): This is the primary key, uniquely identifying each record. Prisma automatically increments this integer value, making it ideal for primary keys.

2. title String: This field represents the post’s title, stored as a text field in the database.

3. content String: Stores the main content of the post, defined as a text string.

4. published Boolean @default(false): A boolean field indicating whether the post is published, with a default value of false.

5. authorId Int: A foreign key linking each post to an author in the User model (not shown here but referenced).

6. createdAt DateTime @default(now()): A timestamp that records the date and time when the post is created. @default(now()) ensures Prisma assigns the current time automatically when a new record is created.

7. updatedAt DateTime @updatedAt: A timestamp that Prisma automatically updates whenever the post record is modified.

8. postCategory PostCategory[]: Defines a relationship with a PostCategory model, indicating that a post can belong to multiple categories.

9. author User @relation(fields: [authorId], references: [id]): This establishes a relation between the Post and User models. It references the id field in the User model, enabling Prisma to enforce foreign key constraints.

Table Mapping

The @@map("posts") attribute maps the model to an actual database table named posts. This is useful if your table naming convention doesn’t match your model names or if you’re working with an existing database.

Applying the Model with Prisma Migrate

Once the model is defined, you can apply it to your database using Prisma's migration tool. Run the following command to generate and apply the migration:

npx prisma migrate dev --name post_table

Prisma will generate a new migration file under the migrations folder, where you can inspect the SQL code that Prisma automatically generated.

In our example, here’s the SQL statement created by Prisma for the Post model:

-- CreateTable
CREATE TABLE "posts" (
    "id" SERIAL NOT NULL,
    "title" TEXT NOT NULL,
    "content" TEXT NOT NULL,
    "published" BOOLEAN NOT NULL DEFAULT false,
    "authorId" INTEGER NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "posts_pkey" PRIMARY KEY ("id")
);

As you can see, Prisma translated each field in the Post model into a corresponding column in the posts table, complete with the primary key and default values where specified.

Why Prisma Models Are Powerful

Using Prisma models offers several advantages:

• Type Safety: The Prisma Client, generated from these models, enforces type safety, reducing potential runtime errors.
• Automatic Migrations: Prisma Migrate simplifies schema changes by auto-generating SQL scripts based on your Prisma schema file.
• Readable and Maintainable: The declarative syntax in schema.prisma is highly readable and easier to maintain compared to raw SQL.

When to Use Prisma Models

If you’re building a Node.js application that requires efficient and type-safe interaction with a database, Prisma models can significantly enhance productivity and reliability. They are particularly useful for applications that handle complex data relationships or require consistent schema evolution.

Next Steps

In the next blog, we’ll dive into data insertion for the Post model and cover how to interact with Prisma to create records in our database.

Stay tuned for a hands-on tutorial on adding data into the posts table using Prisma!