Overview

This page explains the first step of your upgrade process: Taking your Prisma 1 configuration and upgrading it to Prisma 2.0. Concretely, you will learn how to:

  1. Add the Prisma 2.0 CLI as a development dependency
  2. Create your Prisma 2.0 schema
  3. Determine your connection URL and connect your database
  4. Introspect your database (that was so far managed with Prisma 1)
  5. Use the Prisma schema upgrade CLI to resolve the schema incompatibilities in the new Prisma 2.0 data model
  6. Install and generate Prisma Client

Once done with these steps, you can move on to the next guide that explains how you can upgrade the application layer to use Prisma Client for your database queries.

Note: During the upgrade process it can be helpful to get a graphical view on your database. It is therefore recommended to use a graphical database client to connect to your database, such as TablePlus or Postico.

1. Install Prisma 2.0 CLI

The Prisma 2.0 CLI is available as the @prisma/cli package on npm and is invoked via the prisma command.

Note that the former prisma command for Prisma 1 has been renamed to prisma1. You can learn more about this here.

You can install the Prisma 2.0 CLI in your Node.js project as follows (be sure to invoke this command in the directory where your package.json is located):

npm install @prisma/cli --save-dev

Note: With Prisma 1, it was usually recommended to install the CLI globally. We now recommend to install the Prisma CLI locally to prevent version conflicts.

You can now use the local installation of the prisma CLI by prefixing it with npx:

npx prisma

If you're upgrading your entire project all at once, you can now also uninstall the Prisma 1 CLI (otherwise expand below):

# remove global installation
npm uninstall -g prisma
# remove local installation
npm uninstall prisma

If you want to keep using the Prisma 1 CLI, it is recommend to remove your global installation of it and add the prisma1 CLI as a development dependency:

# installs v1.34 of the Prisma 1 CLI
npm uninstall -g prisma
npm install prisma1 --save-dev

You can now invoke it as follows:

npx prisma1

Note that if you need a CLI version smaller than 1.34 (e.g. 1.30), you can install it as follows:

# installs v1.30 of the Prisma 1 CLI
npm uninstall -g prisma@1.30
npm install prisma@1.30 --save-dev

You can now invoke it as follows:

npx prisma

Because the CLI command of this version is identical to the Prisma 2 CLI command (in both cases, it's just prisma), there might be name clashes between the two. You might have to uninstall the @prisma/cli package if you want to run Prisma 1 commands.

2. Create your Prisma 2.0 schema

For this guide, you'll first create a new Prisma schema using the prisma init command and then "fill" it with a data model using introspection.

Run the following command to create your Prisma schema (note that this throws an error if you already have a folder called prisma):

npx prisma init

If you're seeing the following error, you need to rename your current prisma directory:

$ npx prisma init
ERROR A folder called prisma already exists in your project.
Please try again in a project that is not yet using Prisma.

You can rename the current prisma directory to prisma1 to make it clear that this holds the former Prisma 1 configuration:

mv prisma prisma1

Now you can run init and it will succeed:

npx prisma init

It should print the following output:

$ npx prisma init
✔ Your Prisma schema was created at prisma/schema.prisma.
You can now open it in your favorite editor.
Next steps:
1. Set the provider of the datasource block in schema.prisma to match your database: postgresql, mysql or sqlite.
2. Set the DATABASE_URL in the .env file to point to your existing database. If your database has no tables yet, read https://pris.ly/d/getting-started.
3. Run prisma introspect to turn your database schema into a Prisma data model.
4. Run prisma generate to install Prisma Client. You can then start querying your database.
More information in our documentation:
https://pris.ly/d/getting-started

The command created a new folder called prisma with two files:

Your initial Prisma schema looks as follows:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}

With Prisma 1, you specify which language variant of the Prisma client you wanted to use in your prisma.yml. With Prisma 2.0, this information is now also contained inside the Prisma schema via a generator block.

Note: Unlike Prisma 1, the TypeScript and JavaScript variants of Prisma Client 2.0 use the same generator called prisma-client-js. The generated types in index.d.ts are always included, even in plain JavaScript projects. This enables feature like autocompletion in VS Code even when not using TypeScript.

3. Determine your connection URL and connect your database

With Prisma 1, the database connection is configured in the Docker Compose file that's used to launch the Prisma server. The Prisma server then exposes a GraphQL endpoint (via HTTP) that proxies all database requests from the Prisma client application code. That HTTP endpoint is specified in your prisma.yml.

With Prisma 2.0, the HTTP layer isn't exposed any more and Prisma Client 2.0 is configured to run requests "directly" against the database (that is, requests are proxied by Prisma's query engine, but there isn't an extra server any more).

So, as a next step you'll need to tell Prisma 2.0 what kind of database you use (MySQL or PostgreSQL) and where it is located.

First, you need to ensure that that provider field on the datasource block inside schema.prisma is configured to use the right database.

If you're using PostgreSQL, it needs to define the value postgresql in the provider field. If you're using MySQL, it needs to define the value mysql in the provider field. Switch around with the tabs in the code block to see examples of both:

PostgreSQL
MySQL
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}

With the provider field properly set, you can go ahead and configure the connection URL inside the .env file.

Assume the database configuration in your Docker Compose file that you used to deploy your Prisma server looks as follows:

databases:
default:
connector: postgres
host: host.docker.internal
database: mydb
schema: public
user: janedoe
password: janedoe
ssl: false
rawAccess: true
port: '5432'
migrations: true

Based on these connection details, you need to configure the DATABASE_URL environment variable inside your .env file as follows:

DATABASE_URL="postgresql://janedoe:randompassword@localhost:5432/mydb?schema=public"

When using PostgreSQL, the schema argument is typically composed of your service name and service stage and separated by the $ character.

MySQL doesn't have the concept of schemas, but the same combination of service name and service stage determine the database name, separated by the @ character. Service name and service stage are configured as the last two path componens on the endpoint in your prisma.yml.

Learn more on the Connection URLs page.

4. Introspect your database

For the purpose of this guide, we'll use the following Prisma 1 data model (select the SQL tab below to see what the data model maps to in SQL):

Prisma 1 datamodel
SQL
type User {
id: ID! @id
email: String @unique
name: String!
role: Role! @default(value: CUSTOMER)
jsonData: Json
profile: Profile
posts: [Post!]!
}
type Post {
id: ID! @id
createdAt: DateTime! @createdAt
updatedAt: DateTime! @updatedAt
title: String!
content: String
published: Boolean! @default(value: false)
author: User @relation(link: TABLE)
categories: [Category!]!
}
type Profile {
id: ID! @id
bio: String
user: User! @relation(link: INLINE)
}
type Category {
id: ID! @id
name: String!
posts: [Post!]!
}
enum Role {
ADMIN
CUSTOMER
}

Note that this data model has three relations:

  • 1-1: UserProfile
  • 1-n: UserPost (maintained via the _PostToUser relation table)
  • m-n: PostCategory (maintained via the _CategoryToPost relation table)

Now you can run Prisma's introspection against your database with the following command:

npx prisma introspect

Here's a graphical illustration for what happens when introspect is invoked:

For the above Prisma 1 datamodel, this results in the following Prisma 2.0 schema (note that the models and fields have been reorded to match the initial order of the Prisma 1 datamodel):

model User {
id String @id
email String? @unique
name String
role String
jsonData String?
Profile Profile[]
Post Post[] @relation(references: [id])
}
model Post {
id String @id
createdAt DateTime
updatedAt DateTime
title String
content String?
published Boolean
User User[] @relation(references: [id])
Category Category[] @relation(references: [id])
}
model Profile {
id String @id
bio String?
user String?
User User? @relation(fields: [user], references: [id])
}
model Category {
id String @id
name String
Post Post[] @relation(references: [id])
}

While this is already a valid Prisma 2.0 schema, it lacks a number of features that were part of its Prisma 1 equivalent:

  • no auto-generated default CUID values for @id fields
  • no auto-generated date values for the createdAt and updatedAt fields on Post
  • no default value for the role field on User
  • no default value for the published field on Post

There further are a number of inconsistencies which result in a less idiomatic/ergonomic Prisma Client API:

  • UserProfile is a 1-n instead of 1-1 relation
  • UserPost is a m-n instead of 1-n relation
  • relation fields are uppercased (e.g. Profile and Post on User)
  • the jsonData field on User is of type String instead of Json
  • the role field on User is of type String instead of Role, the enum definition for role is missing altogether

While these inconsistencies don't actually impact the "feature set" you'll have available in your Prisma Client API, they make you lose certain constraints/guarantees that were present before.

For example, Prisma now won't guarantee that a User is connected to at most one Profile because the relation between the tables was recognized as 1-n during introspection, so one User record could now get connected to multiple Profile records.

Another issues is that you can store whatever text for the jsonData and role fields, regardless of whether it's valid JSON or represents a value of the Role enum.

To learn more about these inconsistencies check out the Schema incompatibilities page.

In the following, we'll go through these incompatibilities and fix them one by one using the Prisma schema upgrade CLI.

5. Use the Prisma schema upgrade CLI to resolve schema incompatibilities

The Prisma schema upgrade CLI is an interactive tool that helps you upgrading your Prisma schema and ironing out most of the inconsistencies listed above.

You can either install it locally in your project, or invoke it once without installation using npx:

npx prisma-upgrade prisma1/datamodel.graphql prisma/schema.prisma

The CLI will greet you with the following message:

Welcome to the Prisma 1 to Prisma 2 upgrade tool.
This tool is designed to help you gracefully transition your mysql database from Prisma 1 to Prisma 2.
Here's how it works:
1. We inspect the contents of your Prisma 1 datamodel file.
2. We generate SQL commands for you to run on your database.
3. We re-introspect your database to get a new Prisma 2 Schema.
4. We apply any Prisma-specific changes to the new Prisma 2 Schema
We will not try to migrate your database for you. You are in full control
over the changes to your mysql database.
We suggest you first run the subsequent SQL commands on your testing or staging mysql database.
Then when you're confident with the transition you can migrate your production database.
If you have any questions along the way, please reach out to hi@prisma.io.
✔ Are you ready to get started? y/N

Press the y button on your keyboard to continue.

5.1. Set DEFAULT constraints in the database

The first thing the tool does is help you resolve the issue that default values aren't represented in the database by generating SQL statements that add the respective DEFAULT constraints directly to the database.

In this case, two DEFAULT constraints are missing which are suggested by the tool:

Let's transition Prisma 1's @default's to default values backed by the database. Run the following SQL command against your database:
ALTER TABLE User CHANGE role role varchar(191) NOT NULL DEFAULT 'CUSTOMER';
ALTER TABLE Post CHANGE published published tinyint(1) NOT NULL DEFAULT 0;

You can now run these SQL statements against your database either using a command line client or a GUI like TablePlus:

After having added the DEFAULT constraints successfully, you can move on in the CLI by hitting the y button again.

5.2. Set default values for @createdAt fields

The next thing the tools does is help you resolve the issue that the behaviour of @createdAt isn't represented in database

The CLI currently shows the following output:

Let's transition Prisma 1's @createdAt to a datetime type with a default value of now. Run the following SQL command against your database:
ALTER TABLE Post CHANGE createdAt createdAt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;

The CLI suggests to replicate the behaviour of @createdAt by adding another DEFAULT constraint with the value CURRENT_TIMESTAMP. After introspection, this will not be added as the @createdAt attribute, but as @default(now()) in the new Prisma schema.

Go ahead and run this statement against your database now.

You can move on in the CLI by hitting the y button again.

✔ Done migrating @createdAt? Press 'y' to continue … yes

5.3. Set default values for @updatedAt fields

The next thing the tools does is help you resolve the issue that the behaviour of @updated isn't represented in database.

The CLI currently shows the following output:

Let's transition Prisma 1's @updatedAt to a datetime type with a default value of now. Run the following SQL command against your database:
ALTER TABLE Post CHANGE updatedAt updatedAt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;

The CLI suggests to replicate the behaviour of @updated by adding another DEFAULT constraint with the value CURRENT_TIMESTAMP (see this open GitHub issue).

Go ahead and run this statement against your database now.

You can move on in the CLI by hitting the y button again.

? Done migrating @updatedAt? Press 'y' to continue › (y/N)

5.4. Add UNIQUE constraint on foreign key to enforce 1-1 relationships

Next, the tool will help you turn the current 1-n relation between UserProfile back into a 1-1 relation by adding a UNIQUE constraint to the foreign key column called user (named after the relation field in the Prisma 1 datamodel) in the database.

The CLI currently shows the following output:

Let's transition Prisma 1's 1-to-1 relations with @relation or @relation(link:INLINE) to unique constraints on the database. Run the following SQL command against your database:
ALTER TABLE Profile ADD UNIQUE (user);

Go ahead and run this statement against your database now.

You can move on in the CLI by hitting the y button again.

? Done migrating your inline relations? Press 'y' to continue › (y/N)

5.5. Fix Json columns by changing column types from plain text to Json

Next, the tool helps you ensure that Json fields in your Prisma 1 datamodel will be represented as JSON columns in the underlying database, right now they are represented as plain strings (e.g. as MEDIUMTEXT in MySQL).

The CLI currently shows the following output:

Let's transition Prisma 1's Json type to a json type in the database. Run the following SQL command against your database:
ALTER TABLE User CHANGE jsonData jsonData JSON;

Changing the column type to JSON will ensure that the field is properly recognized as Json during Prisma 2 introspection.

Go ahead and run this statement against your database now.

You can move on in the CLI by hitting the y button again.

? Done migrating Json? Press 'y' to continue › (y/N)

5.6. Final result

The final version of the Prisma schema should look as follows:

model Category {
id String @id
name String
Post Post[] @relation(references: [id])
}
model Post {
content String?
createdAt DateTime @default(now())
id String @id
published Boolean @default(false)
title String
updatedAt DateTime @default(now())
Category Category[] @relation(references: [id])
User User[] @relation(references: [id])
}
model Profile {
bio String?
id String @id
user String? @unique
User User? @relation(fields: [user], references: [id])
}
model User {
email String? @unique
id String @id
jsonData Json?
name String
role String @default("CUSTOMER")
Profile Profile?
Post Post[] @relation(references: [id])
}

6. Manually resolve remaining schema incompatibilities

There are a few schema incompatibilities that were not yet resolved by the Prisma schema upgrade CLI.

Note that this guide will be continuously updated to incorporate the new features of the upgrade CLI. For now, you can just follow the guide here and transfer the given SQL statements to your own setup.

6.1. Fix enum column

Right now, the Role enum is missing from the datamodel. That's because the enum fields from the Prisma 1 datamodel have been represented as plain strings in the database (e.g. as MEDIUMTEXT in MySQL). Consequently, the role field on User is currently defined as String in the new Prisma schema.

To fix this, you can set the type for the role column as a new ENUM with SQL:

ALTER TABLE User MODIFY COLUMN role ENUM('ADMIN', 'CUSTOMER') DEFAULT 'CUSTOMER';

You can run this statement against your database now:

6.2. Fix incorrect m-n relations

As a next step, you'll fix all 1-1 and 1-n relations that Prisma 1 represents with relation tables and that currently only exist as m-n relations in your new Prisma 2.0 schema. Concretely, this is the case for the UserPost relation which currently is defined as m-n but should really be a 1-n relation.

To fix this, you'll need to create a new foreign key column on Post to link directly to the User table. Then you'll need to migrate the foreign key values from the current relation table into the new foreign key column on Post. Finally, you can delete the relation table.

Here's the SQL statements for the sample data model from this guide:

For this fix, you'll need to run 3 SQL statements:

  1. Create new column authorId on the Post table. This column should be a foreign key that references the id field of the User table:
    ALTER TABLE `Post` ADD COLUMN `authorId` VARCHAR(25);
    ALTER TABLE `Post` ADD FOREIGN KEY (`authorId`) REFERENCES `User` (`id`);
  2. Write a SQL query that reads all the rows from the _PostToUser relation table and for each row:
    1. Finds the respective Post record by looking up the value from column A
    2. Inserts the value from column B as the value for authorId into that Post record
    UPDATE Post, _PostToUser
    SET Post.authorId = _PostToUser.B
    WHERE Post.id = _PostToUser.A
  3. Delete the _PostToUser relation table
    DROP TABLE `_PostToUser`;

After these commands, the user ID values of the records from column B of the relation table are migrated to the new authorId column.

6.3. Fix remaining issues

Note, at this point you still haven't fixed scalar lists and cascading deletes. You can find the recommended workarounds for these on the Schema incompatibilities page.

6.4. Introspect database

Assume these have been fixed, you can now run introspection again to generate your updated Prisma schema with proper enum support and the 1-n relation bewtween UserPost:

npx prisma introspect

This should be the result when you now introspect your database:

model Category {
id String @id
name String
Post Post[] @relation(references: [id])
}
model Post {
authorId String?
content String?
createdAt DateTime @default(now())
id String @id
published Boolean @default(false)
title String
updatedAt DateTime @default(now())
Category Category[] @relation(references: [id])
}
model Profile {
bio String?
id String @id
user String? @unique
User User? @relation(fields: [user], references: [id])
}
model User {
email String? @unique
id String @id
jsonData Json?
name String
role User_role? @default(CUSTOMER)
Profile Profile?
}
enum User_role {
ADMIN
CUSTOMER
}

6.5. Rename relation fields

To make sure your Prisma Client queries feel natural and idiomatic, it's recommended to make a few adjustments to the Prisma in order to configure your generated Prisma Client API.

Mostly, this means renaming relation fields.

Because all relation fields are virtual, meaning they don't manifest in the database, you can name them whathever you like.s In this case, all relation fields are lowercased and sometimes pluralized.

7. Install and generate Prisma Client

TBD

Edit this page on Github