Evolve your schema

JavaScript
SQL Server

Add a Tag model to your schema

In this section, you will evolve your Prisma schema and then generate and apply the migration to your database with prisma migrate dev.

For the purpose of this guide, we'll make the following changes to the Prisma schema:

  1. Create a new model called Tag with the following fields:
    • id: an auto-incrementing integer that will be the primary key for the model
    • name: a non-null String
    • posts: an implicit many-to-many relation field that links to the Post model
  2. Update the Post model with a tags field with an implicit many-to-many relation field that links to the Tag model

Once you've made the changes to your schema, your schema should resemble the one below:

prisma/schema.prisma
1model Post {
2 id Int @id @default(autoincrement())
3 title String @db.VarChar(255)
4 createdAt DateTime @default(now()) @db.Timestamp(6)
5 content String?
6 published Boolean @default(false)
7 authorId Int
8 user User @relation(fields: [authorId], references: [id])
✎ tags Tag[]
10}
11
12model Profile {
13 id Int @id @default(autoincrement())
14 bio String?
15 userId Int @unique
16 user User @relation(fields: [userId], references: [id])
17}
18
19model User {
20 id Int @id @default(autoincrement())
21 name String? @db.VarChar(255)
22 email String @unique @db.VarChar(255)
23 post Post[]
24 profile Profile?
25}
26
✎model Tag {
✎ id Int @id @default(autoincrement())
✎ name String
✎ posts Post[]
✎}

To apply your Prisma schema changes to your database, use the prisma migrate dev CLI command:

$npx prisma migrate dev --name tags-model

This command will:

  1. Create a new SQL migration file for the migration
  2. Apply the generated SQL migration to the database
  3. Regenerate Prisma Client

The following migration will be generated and saved in your prisma/migrations folder:

prisma/migrations/TIMESTAMP_tags_model.sql
1 -- CreateTable
2CREATE TABLE "Tag" (
3 "id" SERIAL NOT NULL,
4 "name" VARCHAR(255) NOT NULL,
5
6 CONSTRAINT "Tag_pkey" PRIMARY KEY ("id")
7);
8
9-- CreateTable
10CREATE TABLE "_PostToTag" (
11 "A" INTEGER NOT NULL,
12 "B" INTEGER NOT NULL
13);
14
15-- CreateIndex
16CREATE UNIQUE INDEX "_PostToTag_AB_unique" ON "_PostToTag"("A", "B");
17
18-- CreateIndex
19CREATE INDEX "_PostToTag_B_index" ON "_PostToTag"("B");
20
21-- AddForeignKey
22ALTER TABLE "_PostToTag" ADD CONSTRAINT "_PostToTag_A_fkey" FOREIGN KEY ("A") REFERENCES "Post"("id") ON DELETE CASCADE ON UPDATE CASCADE;
23
24-- AddForeignKey
25ALTER TABLE "_PostToTag" ADD CONSTRAINT "_PostToTag_B_fkey" FOREIGN KEY ("B") REFERENCES "Tag"("id") ON DELETE CASCADE ON UPDATE CASCADE;
prisma/migrations/TIMESTAMP_tags_model.sql
1 -- CreateTable
2CREATE TABLE "Tag" (
3 "id" SERIAL NOT NULL,
4 "name" VARCHAR(255) NOT NULL,
5
6 CONSTRAINT "Tag_pkey" PRIMARY KEY ("id")
7);
8
9-- CreateTable
10CREATE TABLE "_PostToTag" (
11 "A" INTEGER NOT NULL,
12 "B" INTEGER NOT NULL
13);
14
15-- CreateIndex
16CREATE UNIQUE INDEX "_PostToTag_AB_unique" ON "_PostToTag"("A", "B");
17
18-- CreateIndex
19CREATE INDEX "_PostToTag_B_index" ON "_PostToTag"("B");
20
21-- AddForeignKey
22ALTER TABLE "_PostToTag" ADD CONSTRAINT "_PostToTag_A_fkey" FOREIGN KEY ("A") REFERENCES "Post"("id") ON DELETE CASCADE ON UPDATE CASCADE;
23
24-- AddForeignKey
25ALTER TABLE "_PostToTag" ADD CONSTRAINT "_PostToTag_B_fkey" FOREIGN KEY ("B") REFERENCES "Tag"("id") ON DELETE CASCADE ON UPDATE CASCADE;
prisma/migrations/TIMESTAMP_tags_model.sql
1 -- CreateTable
2CREATE TABLE [dbo].[Tag] (
3 [id] SERIAL NOT NULL,
4 [name] VARCHAR(255) NOT NULL,
5
6 CONSTRAINT [Tag_pkey] PRIMARY KEY ([id])
7);
8
9-- CreateTable
10CREATE TABLE [dbo].[_PostToTag] (
11 [A] INTEGER NOT NULL,
12 [B] INTEGER NOT NULL
13);
14
15-- CreateIndex
16CREATE UNIQUE INDEX [_PostToTag_AB_unique] ON _PostToTag([A], [B]);
17
18-- CreateIndex
19CREATE INDEX [_PostToTag_B_index] ON [_PostToTag]([B]);
20
21-- AddForeignKey
22ALTER TABLE [dbo].[_PostToTag] ADD CONSTRAINT [_PostToTag_A_fkey] FOREIGN KEY ([A]) REFERENCES [dbo].[Post]([id]) ON DELETE CASCADE ON UPDATE CASCADE;
23
24-- AddForeignKey
25ALTER TABLE [dbo].[_PostToTag] ADD CONSTRAINT [_PostToTag_B_fkey] FOREIGN KEY ([B]) REFERENCES [dbo].[Tag]([id]) ON DELETE CASCADE ON UPDATE CASCADE;
prisma/migrations/TIMESTAMP_tags_model.sql
1-- CreateTable
2CREATE TABLE Tag (
3 id SERIAL NOT NULL,
4 name VARCHAR(255) NOT NULL,
5
6 CONSTRAINT Tag_pkey PRIMARY KEY (id)
7);
8
9-- CreateTable
10CREATE TABLE _PostToTag (
11 A INTEGER NOT NULL,
12 B INTEGER NOT NULL
13);
14
15-- CreateIndex
16CREATE UNIQUE INDEX _PostToTag_AB_unique ON _PostToTag(A, B);
17
18-- CreateIndex
19CREATE INDEX _PostToTag_B_index ON _PostToTag(B);
20
21-- AddForeignKey
22ALTER TABLE _PostToTag ADD CONSTRAINT _PostToTag_A_fkey FOREIGN KEY (A) REFERENCES Post(id) ON DELETE CASCADE ON UPDATE CASCADE;
23
24-- AddForeignKey
25ALTER TABLE _PostToTag ADD CONSTRAINT _PostToTag_B_fkey FOREIGN KEY (B) REFERENCES Tag(id) ON DELETE CASCADE ON UPDATE CASCADE;

Congratulations, you just evolved your database with Prisma Migrate 🚀