Schema updates

Objectives

In this section, we will look at how articles are configured in the app database and add some extra features to them. This will involve:

1. Understanding the Prisma schema found at prisma/schema.prisma
2. Adding some extra fields to the article table that will help us to publish and unpublish them on the site
3. Display some raw, unstyled article data in the /news/$category.tsx route file that we created in a previous lesson

🚀 Let’s get started! 🚀

The schema file

A Prisma schema is a file that serves as a blueprint for your database, defining the structure of your data.

Open the Prisma schema for the project which can be found at prisma/schema.prisma.

In a Prisma schema, you define your data models, each representing a table in the database. For each model, you specify the fields (which correspond to columns in the database table), their types, and any constraints or relations they might have.

In the code snippet below is a copy of the User, Article and ArticleImage models from the project.

Click on the collapsed lines to expand them one at a time, and look carefully at the links between them.

Can you see how they are related to each other? 🧐

model User {
  id       String  @id @default(cuid())
  email    String  @unique
  username String  @unique
  name     String?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  image       UserImage?
  password    Password?
  articles    Article[]
  roles       Role[]
  sessions    Session[]
  connections Connection[]
}

model Article {
  id      String @id @default(cuid())
  title   String
  content String

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  owner   User   @relation(fields: [ownerId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  ownerId String

  images ArticleImage[]

  // non-unique foreign key
  @@index([ownerId])
  // This helps our order by in the user search a LOT
  @@index([ownerId, updatedAt])
}

model ArticleImage {
  id          String  @id @default(cuid())
  altText     String?
  contentType String
  blob        Bytes

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  article   Article   @relation(fields: [articleId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  articleId String

  // non-unique foreign key
  @@index([articleId])
}

Let’s take each of the models in turn:

User

• A user has an id, email, username and name field. The id, email and username fields are required and unique. This means that they are all mandatory and must be unique for each user; no two users can share the same email address, for example.
• A user can also have an image and a password. Article that the image and password fields are optional. We can tell this because they are defined with a ? at the end of the type declaration.
• A user can have many articles, roles, sessions and connections.

model User {
  id       String  @id @default(cuid())
  email    String  @unique
  username String  @unique
  name     String?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  image       UserImage?
  password    Password?
  articles       Article[]
  roles       Role[]
  sessions    Session[]
  connections Connection[]
}

The `User` Model

Article

• An article must have an id, title and content field. The id field is the primary key and is generated automatically using the cuid() function.
• An article can have many images.
• An article belongs to a user (the owner field). This is known as a one-to-many relationship, as a user can have many articles, but an article can only belong to one user.
• The ownerId field is a foreign key that references the id field of the User model.
• The ownerId field and the updatedAt field are indexed to improve performance. We can tell this because of the @@index directive at the end of the model.

model Article {
  id      String @id @default(cuid())
  title   String
  content String

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  owner   User   @relation(fields: [ownerId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  ownerId String

  images ArticleImage[]

  // non-unique foreign key
  @@index([ownerId])
  // This helps our order by in the user search a LOT
  @@index([ownerId, updatedAt])
}

The `Article` model

ArticleImage

• An articleImage belongs to an article. This is known as a one-to-many relationship, as an article can have many images, but an image can only belong to one article.
• The articleId field is a foreign key that references the id field of the Article model.
• The articleId field is indexed to improve performance. We can tell this because of the @@index directive at the end of the model.

model ArticleImage {
  id          String  @id @default(cuid())
  altText     String?
  contentType String
  blob        Bytes

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  article   Article   @relation(fields: [articleId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  articleId String

  // non-unique foreign key
  @@index([articleId])
}

The `ArticleImage` model

Additional Article fields

We need to add some additional fields to the Article model to indicate whether an article is published or draft, and when it was published.

1. Add isPublished and publishedAt fields

Carefully add the lines highlighted in green below to the Article model:

prisma/schema.prisma

model Article {
  id          String @id @default(cuid())
  title       String
  content     String
  isPublished Boolean @default(false)

  publishedAt DateTime?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  owner       User   @relation(fields: [ownerId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  ownerId     String

  images      ArticleImage[]

  // non-unique foreign key
  @@index([ownerId])
  // This helps our order by in the user search a LOT
  @@index([ownerId, updatedAt])
}

Default values and optional fields

Notice that the isPublished field is a Boolean type with a default value of false. Similarly, the publishedAt field is a DateTime type that is optional.

Why do you think the publishedAt field is optional, and isPublished has an initial value of false? 🤔

• The isPublished field is a Boolean type because it can only have two values: true or false.

  It has a default value of false because an article is usually a draft when it is first created. When the article is published, the isPublished field will be set to true.

• The publishedAt field is optional because an article might be a draft and not yet published. In this case, the publishedAt field would be null.

Default values and optional fields

2. Add an ArticleCategory model

Finally, we want to arrange the news articles into categories, such as ‘Technology’, ‘Entertainment’ and ‘Business’.

To do this, we will create an ArticleCategory model and link it to the Article model.

1. Create an ArticleCategory model in the Prisma schema.

   This can be placed directly below the ArticleImage model in the Prisma schema.

   prisma/schema.prisma

   model ArticleImage {
     id          String  @id @default(cuid())
     altText     String?
     contentType String
     blob        Bytes
   
     createdAt DateTime @default(now())
     updatedAt DateTime @updatedAt
   
     article     Article   @relation(fields: [articleId], references: [id], onDelete: Cascade, onUpdate: Cascade)
     articleId   String
   
     // non-unique foreign key
     @@index([articleId])
   }
   
   model ArticleCategory {
     id          String @id @default(cuid())
     name        String
     slug        String @unique
     createdAt   DateTime @default(now())
     updatedAt   DateTime @updatedAt
   
     articles    Article[]
   }

2. Link the Article model to the new ArticleCategory model with the code below:

   prisma/schema.prisma

   model Article {
     id          String @id @default(cuid())
     title       String
     content     String
     isPublished Boolean @default(false)
   
     publishedAt DateTime?
     createdAt   DateTime @default(now())
     updatedAt   DateTime @updatedAt
   
     owner       User   @relation(fields: [ownerId], references: [id], onDelete: Cascade, onUpdate: Cascade)
     ownerId     String
   
     category    ArticleCategory? @relation(fields: [categoryId], references: [id], onDelete: Cascade, onUpdate: Cascade)
     categoryId  String?
   
     images      ArticleImage[]
   
     // non-unique foreign key
     @@index([ownerId])
     @@index([categoryId])
     // This helps our order by in the user search a LOT
     @@index([ownerId, updatedAt])
   }

3. Remember to save your changes to the Prisma schema file.

Migrate changes to database

Now that we’ve made all the necessary changes to the Prisma schema, we need to apply these changes to the database.

To do this, run the following command in your terminal:

npx prisma migrate dev --name "add-article-models"

Once the process has finished, you should see a a new migration file has appeared inside your prisma/migrations folder:

Check database changes

Let’s check the database to confirm the changes have been applied. Run the following command in your terminal:

npx prisma studio

This will open the Prisma Studio interface in your browser. You should see the Article, ArticleImage and ArticleCategory models at the top of your models list:

Currently, these tables will be empty, as we haven’t added any seed data yet. We’ll do that in the next section.

Summary

In this section, we have:

• Updated the Prisma schema to include an Article model and an ArticleImage model.
• Updated the User model to include an articles field that references the Article model.
• Updated the Article model to include an isPublished, publishedAt and category field.
• Updated the ArticleImage model to reference the Article model instead of the Article model.
• Migrated these changes to the database.

Assignment

📄 Assignment documentation

We need to update your assignment to document this new feature. 🚀

1. Explain how you updated the Article model to include an isPublished, publishedAt and category field.
2. Explain how you migrated these changes to the database.

Tip

Useful links

• Prisma schema
• Prisma migrations

What’s next?

In the next tutorial, we will make some adjustments to the project structure to reflect the changes we’ve made to the Prisma schema.