Schema updates

Objectives

In this section, we will begin the process of adding articles to the app, associating them with users and displaying some raw article data on the front-end. This will involve:

Updating the Prisma schema found at prisma/schema.prisma
Updating the seed data generated in prisma/seed.ts to populate our database with some dummy articles
Changing the file structure of the project to add ‘articles’ as well as the ‘notes’ which already exist
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 the code snippet below is a copy of the User, Note and NoteImage 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? 🧐

1
model User {
14 collapsed lines
2
  id       String  @id @default(cuid())
3
  email    String  @unique
4
  username String  @unique
5
  name     String?
6

7
  createdAt DateTime @default(now())
8
  updatedAt DateTime @updatedAt
9

10
  image       UserImage?
11
  password    Password?
12
  notes       Note[]
13
  roles       Role[]
14
  sessions    Session[]
15
  connections Connection[]
16
}
17

18
model Note {
16 collapsed lines
19
  id      String @id @default(cuid())
20
  title   String
21
  content String
22

23
  createdAt DateTime @default(now())
24
  updatedAt DateTime @updatedAt
25

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

29
  images NoteImage[]
30

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

37
model NoteImage {
13 collapsed lines
38
  id          String  @id @default(cuid())
39
  altText     String?
40
  contentType String
41
  blob        Bytes
42

43
  createdAt DateTime @default(now())
44
  updatedAt DateTime @updatedAt
45

46
  note   Note   @relation(fields: [noteId], references: [id], onDelete: Cascade, onUpdate: Cascade)
47
  noteId String
48

49
  // non-unique foreign key
50
  @@index([noteId])
51
}

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. Note 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 notes, 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?
  notes       Note[]
  roles       Role[]
  sessions    Session[]
  connections Connection[]
}

The `User` Model

`Note`

A note must have an id, title and content field. The id field is the primary key and is generated automatically using the cuid() function.
A note can have many images.
A note belongs to a user (the owner field). This is known as a one-to-many relationship, as a user can have many notes, but a note 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 Note {
  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 NoteImage[]

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

The `Note` model

`NoteImage`

A noteImage belongs to a note. This is known as a one-to-many relationship, as a note can have many images, but an image can only belong to one note.
The noteId field is a foreign key that references the id field of the Note model.
The noteId field is indexed to improve performance. We can tell this because of the @@index directive at the end of the model.

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

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

  note   Note   @relation(fields: [noteId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  noteId String

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

The `NoteImage` model

Creating an `Article` model

Our goal is to add articles to the app. To do this, we need to create:

an Article model in the Prisma schema.
an ArticleImage model to store images associated with articles.

Many of the features that we need for articles are already present in the Note model.

We will use this as a blueprint for the Article model, and make the necessary changes to reflect the differences between notes and articles.

Add an `Article` and `ArticleImage` model

Place the code below directly below the NoteImage model in your schema.prisma file.

model NoteImage {
13 collapsed lines
  id          String  @id @default(cuid())
  altText     String?
  contentType String
  blob        Bytes

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

  note   Note   @relation(fields: [noteId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  noteId String

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

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 NoteImage[]

  // 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

  note   Note   @relation(fields: [noteId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  noteId String

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

Update `User` and `Article`

We now need to update the existing User article. You will find this around line 13.

Add a relationship in the User model to the Article model like so:

13
model User {
7 collapsed lines
14
  id       String  @id @default(cuid())
15
  email    String  @unique
16
  username String  @unique
17
  name     String?
18

19
  createdAt DateTime @default(now())
20
  updatedAt DateTime @updatedAt
21

22
  image       UserImage?
23
  password    Password?
24
  notes       Note[]
25
  articles    Article[]
26
  roles       Role[]
27
  sessions    Session[]
28
  connections Connection[]
29
}

Next, we will update the Article model, around line 66.

Edit the code below to reference the ArticleImage model instead of the NoteImage model.

66
model Article {
5 collapsed lines
67
  id      String @id @default(cuid())
68
  title   String
69
  content String
70

71
  createdAt DateTime @default(now())
72
  updatedAt DateTime @updatedAt
73

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

77
  images NoteImage[]
78
  images ArticleImage[]
79

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

Don’t worry that images ArticleImage[] is still underlined red - there’s one last change to make.

Update the ArticleImage model
Section titled “Update the ArticleImage model”

The ArticleImage model is a little more complex, as it references the Article model as the parent.

In other words, each ArticleImage belongs to a single Article that it needs to reference via a foreign key.

We therefore need to make several changes here:
Extra care needed!
This is a complex update, so take your time to place the code snippets exactly where shown in the diff below.
Remember:
- green lines are additions
- red lines are deletions.
prisma/schema.prisma
```
85
model ArticleImage {
86
  id          String    @id @default(cuid())
87
  altText     String?
88
  contentType String
89
  blob        Bytes
90

91
  createdAt   DateTime  @default(now())
92
  updatedAt   DateTime  @updatedAt
93

94
  note        Note      @relation(fields: [noteId], references: [id], onDelete: Cascade, onUpdate: Cascade)
95
  noteId      String
96
  article     Article   @relation(fields: [articleId], references: [id], onDelete: Cascade, onUpdate: Cascade)
97
  articleId   String
98

99
  // non-unique foreign key
100
  @@index([noteId])
101
  @@index([articleId])
102
}
```
Note
These changes have:
- renamed the note field to article
- referenced the Article model instead of the Note model
- renamed the noteId field to articleId
- referenced the id field of the Article model instead of the Note model
- updated the @@index directive to reference articleId instead of noteId

Additional `Article` fields

Finally, 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:

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

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

9 collapsed lines
  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.

Click to reveal

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.

Create an ArticleCategory model in the Prisma schema.

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

87
model ArticleImage {
13 collapsed lines
88
  id          String  @id @default(cuid())
89
  altText     String?
90
  contentType String
91
  blob        Bytes
92

93
  createdAt DateTime @default(now())
94
  updatedAt DateTime @updatedAt
95

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

99
  // non-unique foreign key
100
  @@index([articleId])
101
}
102

103
model ArticleCategory {
104
  id          String @id @default(cuid())
105
  name        String
106
  slug        String @unique
107
  createdAt   DateTime @default(now())
108
  updatedAt   DateTime @updatedAt
109

110
  articles    Article[]
111
}

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

66
model Article {
67
  id          String @id @default(cuid())
68
  title       String
69
  content     String
70
  isPublished Boolean @default(false)
71

72
  publishedAt DateTime?
73
  createdAt   DateTime @default(now())
74
  updatedAt   DateTime @updatedAt
75

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

79
  category    ArticleCategory? @relation(fields: [categoryId], references: [id], onDelete: Cascade, onUpdate: Cascade)
80
  categoryId  String?
81

82
  images      ArticleImage[]
83

84
  // non-unique foreign key
85
  @@index([ownerId])
86
  @@index([categoryId])
87
  // This helps our order by in the user search a LOT
88
  @@index([ownerId, updatedAt])
89
}

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:

Article migration file

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:

Check article migration

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 Note model.
Migrated these changes to the database.

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.

Schema updates

Objectives

The schema file

User

Note

NoteImage

Creating an Article model

Add an Article and ArticleImage model

Update User and Article

Update the ArticleImage model

Additional Article fields