Modelling The Database

To create our schema, we head over to the schema.prisma file.

Models / Tables

To create a model, we use the model key word. See our User table below:

model User {
  id Int @id @default(autoincrement())
  userId String
  username String
  name String
  email String
}

Notable mentions:

• id: Unique row identifier
  • @id attribute defines a single-field ID on the model
    • it acts as the PRIMARY KEY
  • @default defines a default value for the field
    • in our case, we are using autoincrement
    • autoincrement creates a sequence of integers in the database and assigns the value to the id field
• userId: will keep track of the users auth id
  • Supabase Authentication stores user data in a hidden auth.users table
  • to uniquely identify each user based on their auth information, we store their auth id in the table

We will also have a model to store each users' posts:

model Post {
  id Int @id @default(autoincrement())
  content String
}

Relations

A relation is a connection between two models in the Prisma schema.

To create a one-to-many relation between the User and Post table, we can do:

model User {
  id Int @id @default(autoincrement())
  name String
  email String
  tickets Ticket[]
}

model Ticket {
  id Int @id @default(autoincrement())
  title String
  description String
  assignee User @relation(fields: [assigneeId], references: [id])
  assigneeId Int
}

How do they work?

To create the relation, we have two relation fields. These fields are:

• User.posts
• Post.author These relation fields DO NOT exist in the database. They are only used to generate the Prisma Client.

The Post.authorId field is called a scalar field. This fields does exist in the database. It is the foreign key that connects Post and User.

Generating the Prisma Client

Whenever we make a change to the schema.prisma file, we need to run the command

npx prisma generate

This generates the Prisma Client.

If you do not generate the Prisma Client after changing the schema, such as adding a new field to a model, the client will not recognise its existence, or lack thereof, and will show as an error in the editor.

Migrations

To map our data model to the database schema, we need to run the command

npx prisma migrate dev --name {name_of_migration}

This command does two things:

• creates a new SQL migration file for this database under a new prisma/migrations folder
• runs the SQL migration file against the database

We should get a file like the following:

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "userId" TEXT NOT NULL,
    "username" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "email" TEXT NOT NULL,

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

-- CreateTable
CREATE TABLE "Post" (
    "id" SERIAL NOT NULL,
    "content" TEXT NOT NULL,
    "authorId" INTEGER NOT NULL,

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

-- AddForeignKey
ALTER TABLE "Post" ADD CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;