Evolve your Prisma schema with Prisma Migrate : node-cockroachdb

/ Get started / Set up Prisma / Add to existing project / Relational databases

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:

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

Create a new SQL migration file for the migration
Apply the generated SQL migration to the database
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 🚀

Querying the database Next steps

Was this helpful?

Edit this page on GitHub

Evolve your schema

Add a `Tag` model to your schema

Was this helpful?

Product

Developers

Use Cases

Company

Newsletter

Evolve your schema

Add a Tag model to your schema

Was this helpful?

Product

Developers

Use Cases

Company

Newsletter

Add a `Tag` model to your schema