The Prisma Schema and Migrations

In Prisma, all database interactions begin with the schema.

This can be found in the prisma/schema.prisma file in the root of your project.

The schema is a file that defines the database tables and their fields. It also defines the relationships between the tables.

Let’s take a look at the Prisma schema for our starter project:

Info

So far, the schema is virtually empty, but it already has some crucial code.

1. It defines a generator that Prisma uses to generate the client library for the database, and source of the database file that Prisma will connect our application to.

In this case, the source is a SQLite database, which is a file-based database that is easy to set up and use for development purposes.

The .env file and .gitignore

Let’s take a moment to look at the DATABASE_URL line in the schema:

url     = env("DATABASE_URL")

What exactly is "DATABASE_URL"? And where does it come from?

"DATABASE_URL" is an environment variable that Prisma uses to connect to the database. It is defined in the .env file in the root of the project.

If you open the .env file, you will see that the value of DATABASE_URL is set to a connection string pointing to the SQLite database file:

DATABASE_URL="file:./dev.db"

So why not just put this directly into the Prisma schema?

The answer is security.

The .env file is a place to keep sensitive information like database connection strings out of a version control repository like Github.

Remember that every time you make a commit to a Git repository, you are potentially sharing your code with the world. If you commit a file that contains sensitive information, you are potentially sharing that information with the world too.

To stop this from happening, we would usually add the .env file to the .gitignore file in the root of the project. As the name suggests, this file contains a list of files and folders thats Git ignores when making commits.

But where is the .env file here?

Because this is an educational project and there is no sensitive information that will be added, we’ve removed the .env file from .gitignore to prevent any confusion during the initial setup stage.

In a real-world project, you would add the .env file to .gitignore and ensure it was never committed to the version control history from the outset.

Adding a Model to the Schema

We will start by adding a model to the schema. A model is just a description of how a table should be created in the database.

Let’s go ahead, and add a User model to the very bottom of the schema file:

prisma/schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

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

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

Take a moment to work out what you think this model doing. What fields does the User table have? What are the @ symbols doing?

Let’s break this down line by line:

id    String @id @default(cuid())

id is a unique identifier for the user. It is a string and is a unique string of characters automatically generated using the cuid() function. The @id attribute tells Prisma that this field is the primary key for the table.

email String @unique

email is a string, and of course stores the email address of the user. The @unique attribute tells Prisma that this field must be unique for each user. If more that one user tries to sign up with the same email address, the database will throw an error.

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

createdAt and updatedAt are both date and time fields. createdAt is automatically set to the current date and time when a new user is created. updatedAt is automatically updated to the current date and time whenever the user is updated.

Defining the User model

Applying changes to the database

So far, we’ve told Prisma how we want the User table to look in the database, but we haven’t actually made any changes to the database itself yet.

We need to push these changes to the database using something called a migration.

A migration is a file that translates the changes we’ve made to the Prisma schema into SQL commands that can be run on the database.

Creating a migration file

If your server is still running, stop it by pressing Ctrl + C in your terminal.

To create a migration with the name of add-user-model, run the following command in your terminal:

npx prisma migrate dev --name add-user-model

This command tells Prisma to create a new migration file with the name add-user-model. The dev flag tells Prisma to run the migration on the database.

When you run this command, Prisma will generate a new migration file in the prisma/migrations directory. The file will contain the SQL commands needed to create the User table in the database.

If all is well, you should see a confirmation message that looks something like this in your terminal:

You should also be able to see the new migration file in the prisma/migrations directory.

Next, we will use Prisma Studio to view the changes we’ve made to the database.