Upgrading the Prisma layer to Prisma 2

Overview

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

Add the Prisma 2 CLI as a development dependency
Create your Prisma 2 schema
Determine your connection URL and connect to your database
Introspect your database (that was so far managed with Prisma 1)
Use the Prisma 1 Upgrade CLI to resolve the schema incompatibilities in the new Prisma 2 data model
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 CLI

The Prisma 2 CLI is available as the prisma 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 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 --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 prisma1
$
$# remove local installation
$npm uninstall prisma1

Expand if you want to keep using your Prisma 1 CLI side-by-side

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

2. Create your Prisma 2 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:


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:


✔ Your Prisma schema was created at prisma/schema.prisma.
  You can now open it in your favorite editor.

Next steps:
1. 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
2. Set the `provider` of your `datasource` block in `schema.prisma` to match your database: `postgresql`, `mysql` or `sqlite`.
3. Run `prisma db pull` 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, and two files:

prisma/schema.prisma: Your Prisma schema file that specifies the data source, generator and data model (note that the data model doesn't exist yet, it will be generated via introspection).
.env: A dotenv file to configure your database connection URL.

Your initial Prisma schema looks as follows:

schema.prisma
1// This is your Prisma schema file,
2// learn more about it in the docs: https://pris.ly/d/prisma-schema
3
4datasource db {
5  provider = "postgresql"
6  url      = env("DATABASE_URL")
7}
8
9generator client {
10  provider = "prisma-client-js"
11}

With Prisma 1, you specify which language variant of Prisma Client you wanted to use in your prisma.yml. With Prisma 2, this information is now specified 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 to 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, 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 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 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:

docker-compose.yml
1PRISMA_CONFIG: |
2  port: 4466
3  databases:
4    default:
5      connector: postgres
6      host: postgres
7      port: 5432
8      user: prisma
9      password: prisma

Also assume your endpoint in prisma.yml is configured as follows:

prisma.yml
1endpoint: http://localhost:4466/myproject/dev

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

.env
1DATABASE_URL="postgresql://janedoe:randompassword@localhost:5432/prisma?schema=myproject$dev"

Note that the schema argument is typically composed of your service name and service stage (which are part of the endpoint in prisma.yml), separated by the $ character.

Sometimes no service name and stage are specified in prisma.yml:

prisma.yml
1endpoint: http://localhost:4466/

In that case, the schema must be specified as follows:

.env
1DATABASE_URL="postgresql://janedoe:randompassword@localhost:5432/prisma?schema=default$default"

docker-compose.yml
1PRISMA_CONFIG: |
2  port: 4466
3  databases:
4    default:
5      connector: mysql
6      host: mysql
7      port: 3306
8      user: root
9      password: randompassword

Also assume your endpoint in prisma.yml is configured as follows:

prisma.yml
1endpoint: http://localhost:4466/myproject/dev

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

.env
1DATABASE_URL="mysql://root:randompassword@localhost:3306/myproject@dev"

Note that the database name in the connection URL is typically composed of your service name and service stage (which are part of the endpoint in prisma.yml), separated by the @ character.

Sometimes no service name and stage are specified in prisma.yml:

prisma.yml
1endpoint: http://localhost:4466/

In that case, the database name must be specified as follows:

.env
1DATABASE_URL="mysql://root:randompassword@localhost:3306/default@default"

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: User ↔ Profile
1-n: User ↔ Post (maintained via the _PostToUser relation table)
m-n: Post ↔ Category (maintained via the _CategoryToPost relation table)

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


$npx prisma db pull

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

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

schema.prisma
1model User {
2  id       String    @id @default(cuid())
3  email    String?   @unique
4  name     String
5  role     String
6  jsonData String?
7  Profile  Profile[]
8  Post     Post[]
9}
10
11model Post {
12  id        String     @id @default(cuid())
13  createdAt DateTime
14  updatedAt DateTime
15  title     String
16  content   String?
17  published Boolean
18  Category  Category[]
19  User      User[]
20}
21
22model Profile {
23  id   String  @id @default(cuid())
24  bio  String?
25  user String? @unique
26  User User?   @relation(fields: [user], references: [id])
27}
28
29model Category {
30  id   String @id @default(cuid())
31  name String
32  Post Post[]
33}

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

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:

User ↔ Profile is a 1-n instead of 1-1 relation
User ↔ Post 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 issue 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 1 Upgrade CLI is an interactive tool that helps you upgrading your Prisma schema and ironing out most of the inconsistencies listed above.

The Prisma 1 Upgrade CLI works in two major phases:

Fix the database schema via plain SQL
Add missing attributes to the Prisma 2 schema and other schema fixes

During the first phase, it will generate and print a number of SQL statements that you should run against your database to adjust the database schema. You can either run all of the statements or a subset of them before continuing to the second phase.

During the second phase, you don't need to do anything manually. The Upgrade CLI will make changes to your Prisma schema by adding certain Prisma-level attributes (like @default(cuid)) or @updatedAt), adjusting the names of relation fields to match the ones from your Prisma 1 datamodel and ensure 1-1-relations that were required on both sides in your Prisma 1 datamodel are also required in the Prisma 2 schema.

Note that you can start over at any time during the process and go back from the second to the first phase.

In this illustration, the green area shows the first phase, the blue area shows the second phase. Note that you can optionally run prisma db pull in between the phases to update your Prisma data model:

To use the Upgrade CLI, you can either install it locally in your project, or invoke it once without installation using npx as done here:


$npx prisma-upgrade prisma1/prisma.yml prisma/schema.prisma

The CLI will greet you with the following message:


◮ Welcome to the interactive Prisma Upgrade CLI that helps with the
upgrade process from Prisma 1 to Prisma 2.

Please read the docs to learn more about the upgrade process:
https://pris.ly/d/how-to-upgrade

➤ Goal
The Upgrade CLI helps you resolve the schema incompatibilities
between Prisma 1 and Prisma 2. Learn more in the docs:
https://pris.ly/d/schema-incompatibilities

➤ How it works
Throughout the process, you'll need to adjust your database schema by sending
SQL statements to it. The SQL statements are provided by the Upgrade CLI.

Note that the Upgrade CLI never makes changes to your database,
you are in full control over any operations that are executed against it.

You can stop and re-run the Upgrade CLI at any time.

These are the different steps of the upgrade process:

  1. The Upgrade CLI generates SQL commands for you to run on your database.
  2. You run the SQL commands against your database.
  3. You run the `npx prisma db pull` command again.
  4. You run the `npx prisma-upgrade` command again.
  5. The Upgrade CLI adjusts the Prisma 2 schema by adding missing attributes.

➤ Note
It is recommended that you make a full backup of your existing data before starting
the upgrade process. If possible, the migration should be performed in a staging
environment before executed against a production environment.

➤ Help
If you have any questions or run into any problems along the way,
please create an issue at:
https://github.com/prisma/upgrade/issues/new

Are you ready? [Y/n]

Press the Y button, then confirm by hitting RETURN on your keyboard to continue.

Once you confirmed, the CLI outputs the SQL statements you should be running against your database:


➤ Adjust your database schema
Run the following SQL statements against your database:

  Fix columns with ENUM data types
  https://pris.ly/d/schema-incompatibilities#enums-are-represented-as-text-in-database

    CREATE TYPE "default$default"."Role" AS ENUM ('ADMIN', 'CUSTOMER');
    ALTER TABLE "default$default"."User" ALTER COLUMN "role" SET DATA TYPE "default$default"."Role" using "role"::"default$default"."Role";


  Add missing `DEFAULT` constraints to the database
  https://pris.ly/d/schema-incompatibilities#default-values-arent-represented-in-database

    ALTER TABLE "default$default"."User" ALTER COLUMN "role" SET DEFAULT 'CUSTOMER';
    ALTER TABLE "default$default"."Post" ALTER COLUMN "published" SET DEFAULT false;


  Fix columns with JSON data types
  https://pris.ly/d/schema-incompatibilities#json-type-is-represented-as-text-in-database

    ALTER TABLE "default$default"."User" ALTER COLUMN "jsonData" SET DATA TYPE JSONB USING "jsonData"::TEXT::JSONB;


  Replicate `@createdAt` behavior in Prisma 2
  https://pris.ly/d/schema-incompatibilities#createdat-isnt-represented-in-database

    ALTER TABLE "default$default"."Post" ALTER COLUMN "createdAt" SET DEFAULT CURRENT_TIMESTAMP;


  Fix 1-1 relations by adding `UNIQUE` constraints
  https://pris.ly/d/schema-incompatibilities#inline-1-1-relations-are-recognized-as-1-n-missing-unique-constraint

    ALTER TABLE "default$default"."Profile" ADD UNIQUE ("user");


  Migrate IDs from varchar(25) to varchar(30)
  https://pris.ly/d/schema-incompatibilities#mismatching-cuid-length

    ALTER TABLE "default$default"."Category" ALTER COLUMN "id" SET DATA TYPE character varying(30);
    ALTER TABLE "default$default"."Post" ALTER COLUMN "id" SET DATA TYPE character varying(30);
    ALTER TABLE "default$default"."Profile" ALTER COLUMN "id" SET DATA TYPE character varying(30);
    ALTER TABLE "default$default"."Profile" ALTER COLUMN "user" SET DATA TYPE character varying(30);
    ALTER TABLE "default$default"."User" ALTER COLUMN "id" SET DATA TYPE character varying(30);

➤ Breaking changes detected

In order to fully optimize your database schema, you'll need to run a few SQL
statements that can break your Prisma 1 setup. Note that these changes are optional
and if you are upgrading gradually and running Prisma 1 and Prisma 2 side-by-side,
you should not perform these changes yet. Instead, you can perform them whenever
you are ready to completely remove Prisma 1 from your project.
If you are upgrading all at once, you can safely perform these changes now.

Learn more in the docs:
https://pris.ly/d/how-to-upgrade'


➤ Adjust your database schema
Run the following SQL statements against your database:

  Fix columns with ENUM data types
  https://pris.ly/d/schema-incompatibilities#enums-are-represented-as-text-in-database

    ALTER TABLE `User` CHANGE `role` `role` ENUM('ADMIN', 'CUSTOMER') NOT NULL;


  Add missing `DEFAULT` constraints to the database
  https://pris.ly/d/schema-incompatibilities#default-values-arent-represented-in-database

    ALTER TABLE `User` CHANGE `role` `role` ENUM('ADMIN', 'CUSTOMER') NOT NULL DEFAULT 'CUSTOMER';
    ALTER TABLE `Post` CHANGE `published` `published` TINYINT(1) NOT NULL DEFAULT 0;


  Fix columns with JSON data types
  https://pris.ly/d/schema-incompatibilities#json-type-is-represented-as-text-in-database

    ALTER TABLE `User` CHANGE `jsonData` `jsonData` JSON ;


  Replicate `@createdAt` behavior in Prisma 2.0
  https://pris.ly/d/schema-incompatibilities#createdat-isnt-represented-in-database

    ALTER TABLE `Post` CHANGE `createdAt` `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;


  Fix 1-1 relations by adding `UNIQUE` constraints
  https://pris.ly/d/schema-incompatibilities#inline-1-1-relations-are-recognized-as-1-n-missing-unique-constraint

    ALTER TABLE `Profile` ADD UNIQUE (`user`);


  Migrate IDs from varchar(25) to varchar(30)
  https://pris.ly/d/schema-incompatibilities#mismatching-cuid-length

    SET FOREIGN_KEY_CHECKS=0;
    ALTER TABLE `Category` CHANGE `id` `id` char(30) CHARACTER SET utf8 NOT NULL;
    ALTER TABLE `Post` CHANGE `id` `id` char(30) CHARACTER SET utf8 NOT NULL;
    ALTER TABLE `Profile` CHANGE `id` `id` char(30) CHARACTER SET utf8 NOT NULL;
    ALTER TABLE `Profile` CHANGE `user` `user` char(30) CHARACTER SET utf8 ;
    ALTER TABLE `User` CHANGE `id` `id` char(30) CHARACTER SET utf8 NOT NULL;
    SET FOREIGN_KEY_CHECKS=1;

➤ Breaking changes detected

In order to fully optimize your database schema, you'll need to run a few SQL
statements that can break your Prisma 1 setup. Note that these changes are optional
and if you are upgrading gradually and running Prisma 1 and Prisma 2 side-by-side,
you should not perform these changes yet. Instead, you can perform them whenever
you are ready to completely remove Prisma 1 from your project.
If you are upgrading all at once, you can safely perform these changes now.

Learn more in the docs:
https://pris.ly/d/how-to-upgrade'

Note: If you're seeing the note about breaking changes, you can ignore it for now. We'll discuss it later.

The shown SQL statements are categorized into a number of "buckets", all aiming to resolve a certain schema incompatibility:

Fix columns with ENUM data types
Add missing DEFAULT constraints to the database
Fix columns with JSON data types
Replicate @createdAt behavior in Prisma 2
Fix 1-1 relations by adding UNIQUE constraints

As a next step, you can start sending the SQL statements to your database. Note that all of these changes are non-breaking and you'll be able to continue using Prisma 1 side-by-side with Prisma 2.

The next sections cover the different kinds of SQL statements to be sent to your database individually.

5.1. Fix the database schema via plain SQL (non-breaking)

In this section, we'll walk through the printed SQL statements and run them against the database one by one.

5.1.1. Fix columns with ENUM data types

The first thing the tool does is help you ensure that enum definitions in your Prisma 1 datamodel will be represented as actual ENUM types 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:


Fix columns with ENUM data types
https://pris.ly/d/schema-incompatibilities#enums-are-represented-as-text-in-database

  CREATE TYPE "default$default"."Role" AS ENUM ('ADMIN', 'CUSTOMER');
  ALTER TABLE "default$default"."User" ALTER COLUMN "role" SET DATA TYPE "default$default"."Role" using "role"::"default$default"."Role";

⚠️ Warning: If you are running Prisma 1 and Prisma 2 side-by-side, these SQL statements will break your Prisma 1 setup. The docs will be updated to reflect this soon.


Fix columns with ENUM data types
https://pris.ly/d/schema-incompatibilities#enums-are-represented-as-text-in-database

  ALTER TABLE `User` CHANGE `role` `role` ENUM('ADMIN', 'CUSTOMER') NOT NULL;

Go ahead and run these statements against your database now.

5.1.2. Add missing `DEFAULT` constraints to the database

Next, the Upgrade CLI helps 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:


Add missing `DEFAULT` constraints to the database
https://pris.ly/d/schema-incompatibilities#default-values-arent-represented-in-database

  ALTER TABLE "default$default"."User" ALTER COLUMN "role" SET DEFAULT 'CUSTOMER';
  ALTER TABLE "default$default"."Post" ALTER COLUMN "published" SET DEFAULT false;

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


Add missing `DEFAULT` constraints to the database
https://pris.ly/d/schema-incompatibilities#default-values-arent-represented-in-database

  ALTER TABLE `User` CHANGE `role` `role` ENUM('ADMIN', 'CUSTOMER') 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:

5.1.3. Fix columns with JSON data types

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).

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

The CLI currently shows the following output:


Fix columns with JSON data types
https://pris.ly/d/schema-incompatibilities#json-type-is-represented-as-text-in-database

  ALTER TABLE "default$default"."User" ALTER COLUMN "jsonData" TYPE JSON  USING "jsonData"::json;

⚠️ Warning: If you are running Prisma 1 and Prisma 2 side-by-side, these SQL statements will break your Prisma 1 setup. The docs will be updated to reflect this soon.

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


Fix columns with JSON data types
https://pris.ly/d/schema-incompatibilities#json-type-is-represented-as-text-in-database

  ALTER TABLE `User` CHANGE `jsonData` `jsonData` JSON ;

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

5.1.4. Replicate `@createdAt` behavior in Prisma 2

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

The CLI currently shows the following output:


Replicate `@createdAt` behavior in Prisma 2.0
https://pris.ly/d/schema-incompatibilities#createdat-isnt-represented-in-database

  ALTER TABLE "default$default"."Post" ALTER COLUMN "createdAt" SET DEFAULT CURRENT_TIMESTAMP;

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


Replicate `@createdAt` behavior in Prisma 2.0
https://pris.ly/d/schema-incompatibilities#createdat-isnt-represented-in-database

  ALTER TABLE `Post` CHANGE `createdAt` `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

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

5.1.5. Fix 1-1 relations by adding `UNIQUE` constraints

Now, the tool will help you turn the current 1-n relation between User ↔ Profile 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:


Fix 1-1 relations by adding `UNIQUE` constraints
https://pris.ly/d/schema-incompatibilities#inline-1-1-relations-are-recognized-as-1-n-missing-unique-constraint

  ALTER TABLE "default$default"."Profile" ADD UNIQUE ("user");

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


Fix 1-1 relations by adding `UNIQUE` constraints
https://pris.ly/d/schema-incompatibilities#inline-1-1-relations-are-recognized-as-1-n-missing-unique-constraint

  ALTER TABLE `Profile` ADD UNIQUE (`user`);

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

5.1.6. Fix mismatch of CUID length

Note: These SQL statements will keep appearing in the Upgrade CLI even after you have changed the column types in the underlying database. This is a currently a limitation in the Upgrade CLI.

Finally, the tool will help you turn the current ID columns of type VARCHAR(25) into VARCHAR(30) 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:


Migrate IDs from varchar(25) to varchar(30)
https://pris.ly/d/schema-incompatibilities#mismatching-cuid-length

  ALTER TABLE "default$default"."Category" ALTER COLUMN "id" SET DATA TYPE character varying(30);
  ALTER TABLE "default$default"."Post" ALTER COLUMN "id" SET DATA TYPE character varying(30);
  ALTER TABLE "default$default"."Profile" ALTER COLUMN "id" SET DATA TYPE character varying(30);
  ALTER TABLE "default$default"."Profile" ALTER COLUMN "user" SET DATA TYPE character varying(30);
  ALTER TABLE "default$default"."User" ALTER COLUMN "id" SET DATA TYPE character varying(30);

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


Migrate IDs from varchar(25) to varchar(30)
https://pris.ly/d/schema-incompatibilities#mismatching-cuid-length

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE `Category` CHANGE `id` `id` char(30) CHARACTER SET utf8 NOT NULL;
ALTER TABLE `Post` CHANGE `id` `id` char(30) CHARACTER SET utf8 NOT NULL;
ALTER TABLE `Profile` CHANGE `id` `id` char(30) CHARACTER SET utf8 NOT NULL;
ALTER TABLE `Profile` CHANGE `user` `user` char(30) CHARACTER SET utf8 ;
ALTER TABLE `User` CHANGE `id` `id` char(30) CHARACTER SET utf8 NOT NULL;
SET FOREIGN_KEY_CHECKS=1;

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

5.1.7. Breaking changes detected

In case the Upgrade CLI has printed a note about breaking changes, your database schema needs some adjustments that will break Prisma 1 compatibility in order to be fully optimized.

If there are no breaking changes detected, you can skip forward to section 5.2

Depending on your upgrade strategy, you can either perform these changes now or skip to the next phase of the Upgrade CLI:

If you are following the gradual side-by-side upgrade strategy, do not perform these changes yet since they will break your Prisma 1 setup. In that case, you can continue to the next phase of the Upgrade CLI by typing n and hitting RETURN.
If you are following the all at once upgrade strategy, you can perform these changes now. In that case, continue by typing Y and hitting RETURN.

5.2. Fix the database schema via plain SQL (breaking)

In this section, you'll resolve the schema incompatibilities that are breaking your Prisma 1 setup. Do not perform these changes if you are still running Prisma 1 in your project!

5.2.1. Fix incorrect m-n relations

Now, the Upgrade CLI helps you 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 schema. Concretely, this is the case for the User ↔ Post relation which currently is defined as m-n but should really be a 1-n relation.

To fix this, you'll need to perform the following migration:

Create a new foreign key column on Post to link directly to the User table.
Migrate the foreign key values from the relation table into the new foreign key column on Post.
Delete the relation table.

These instructions are now printed by the CLI:


➤ Adjust your database schema
Run the following SQL statements against your database:

  Fix one-to-many table relations
  https://pris.ly/d/schema-incompatibilities#all-non-inline-relations-are-recognized-as-m-n

    ALTER TABLE "default$default"."Post" ADD COLUMN "authorId" character varying(25) ;
    ALTER TABLE "default$default"."Post" ADD CONSTRAINT "author" FOREIGN KEY ("authorId") REFERENCES "default$default"."User"("id");
    UPDATE "default$default"."Post" SET "authorId" = "default$default"."_PostToUser"."B" FROM "default$default"."_PostToUser" WHERE "default$default"."_PostToUser"."A" = "default$default"."Post"."id";
    DROP TABLE "default$default"."_PostToUser";


➤ Next Steps

After you executed one or more of the previous SQL statements against your database,
please run the following two commands to refresh your Prisma 2 schema and check
the changes.

  1. Run `npx prisma db pull` again to refresh your Prisma 2 schema.
  2. Run `npx prisma-upgrade` again.

If you can't or don't want to execute the remaining SQL statements right now, you can
skip to the last step where the Upgrade CLI adds missing attributes to your Prisma 2
schema that are not picked up by introspection.

Skip to the last step? [Y/n]?

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

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`);

Write a SQL query that reads all the rows from the _PostToUser relation table and for each row:

Finds the respective Post record by looking up the value from column A
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

Delete the _PostToUser relation table


DROP TABLE `_PostToUser`;


➤ Adjust your database schema
Run the following SQL statements against your database:

  Fix one-to-many table relations
  https://pris.ly/d/schema-incompatibilities#all-non-inline-relations-are-recognized-as-m-n

    ALTER TABLE `Post` ADD COLUMN `authorId` char(25) CHARACTER SET utf8 ;
    ALTER TABLE `Post` ADD CONSTRAINT author FOREIGN KEY (`authorId`) REFERENCES `User`(`id`);
    UPDATE `Post`, `_PostToUser` SET `Post`.`authorId` = `_PostToUser`.B where `_PostToUser`.A = `Post`.`id`;
    DROP TABLE `_PostToUser`;


➤ Next Steps

After you executed one or more of the above SQL statements against your database,
please run the following two commands to refresh your Prisma 2 Schema and check
the changes.

  1. Run `npx prisma db pull` again to refresh your Prisma 2 schema.
  2. Run `npx prisma-upgrade` again.

If you can't or don't want to execute the remaining SQL statements right now, you can
skip to the last step where the Upgrade CLI adds missing attributes to your Prisma 2
schema that are not picked up by introspection.

Skip to the last step? [Y/n]?

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

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` char(25) CHARACTER SET utf8 ;
ALTER TABLE `Post` ADD CONSTRAINT author FOREIGN KEY (`authorId`) REFERENCES `User`(`id`);

Write a SQL query that reads all the rows from the _PostToUser relation table and for each row:

Finds the respective Post record by looking up the value from column A
Inserts the value from column B as the value for authorId into that Post record


UPDATE `Post`, `_PostToUser` SET `Post`.`authorId` = `_PostToUser`.B where `_PostToUser`.A = `Post`.`id`;

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.

5.2. Re-introspect your database to update your Prisma schema

At this point, you've resolved the schema incompatibilities with the Upgrade CLI. You can now exit the Upgrade CLI for now by typing n and hitting RETURN.

In this section, you'll update your Prisma schema with another introspection round. This time, the previous flaws of the Prisma schema will be resolved because the database schema has been adjusted:


$npx prisma db pull

This time, the resulting Prisma schema looks as follows:

schema.prisma
1model User {
2  id       String   @id
3  name     String
4  email    String?  @unique
5  jsonData Json?
6  role     Role     @default(CUSTOMER)
7  Post     Post[]
8  Profile  Profile?
9}
10
11model Post {
12  id        String     @id
13  createdAt DateTime   @default(now())
14  updatedAt DateTime
15  title     String
16  content   String?
17  published Boolean    @default(false)
18  authorId  String?
19  User      User?      @relation(fields: [authorId], references: [id])
20  Category  Category[] @relation(references: [id])
21}
22
23model Category {
24  id   String @id
25  name String
26  Post Post[] @relation(references: [id])
27}
28
29model Profile {
30  bio  String?
31  id   String  @id
32  user String? @unique
33  User User?   @relation(fields: [user], references: [id])
34}
35
36enum Role {
37  ADMIN
38  CUSTOMER
39}

This schema has most issues resolved, but it still lacks the following:

5.2. Add missing attributes to the Prisma 2 schema and other schema fixes

The CLI now prints the following:


➤ What happens next
As a last step, some final adjustments will be made to your Prisma 2 schema
to carry over some Prisma-level attributes that aren't picked up by introspection.

As a last step, some final adjustments will be made to your Prisma 2.0
schema to carry over some Prisma-level attributes that aren't picked
up by introspection.

Warning
Your current Prisma 2.0 schema will be overwritten, so please
make sure you have a backup!

Are you ready? [Y/n]

At this point, you either ran all the SQL statement that were printed by the CLI or you skipped some of them. Either way, you can now move on the last step and let the Upgrade CLI add the missing Prisma 2 attributes. Typically these are the following:

@default(cuid()) for your @id fields
@updatedAt for any fields that were using this attribute in Prisma 1
@map and @@map as replacements for @db and @@db from Prisma 1

In that step, the Upgrade CLI also fixes other issues that occurred in the transition to Prisma 2:

it makes sure that 1-1-relations that were required on both sides in Prisma 1 are also required in your Prisma 2 schema
it renames relation fields to the same names they had in your Prisma 1 datamodel (coming soon)

To apply these changes, you can re-run the Upgrade CLI:


$npx prisma-upgrade prisma1/prisma.yml prisma/schema.prisma

If you did not resolve all schema incompatibilities, the Upgrade CLI now prints the remaining SQL statements (as well as the ones for migrating IDs). You can just ignore them at this point and continue to the last step by continuously typing Y and hitting RETURN when prompted.

If you did resolve all schema incompatibilities, no SQL statements will be printed and the Upgrade CLI only outputs the following:


$ npx prisma-upgrade prisma1/prisma.yml prisma/schema.prisma

➤ Next Steps

After you executed one or more of the previous SQL statements against your database,
please run the following two commands to refresh your Prisma 2 schema and check
the changes.

  1. Run `npx prisma db pull` again to refresh your Prisma 2 schema.
  2. Run `npx prisma-upgrade` again.

If you can't or don't want to execute the remaining SQL statements right now, you can
skip to the last step where the Upgrade CLI adds missing attributes to your Prisma 2
schema that are not picked up by introspection.

Skip to the last step? [Y/n]?

One more time, type Y and hit RETURN to confirm.

The final prompt of the Upgrade CLI now asks you to confirm the above mentioned changes it will make to your Prisma schema:


➤ What happens next
As a last step, some final adjustments will be made to your Prisma 2 schema
to carry over some Prisma-level attributes that aren't picked up by introspection.

As a last step, some final adjustments will be made to your Prisma 2.0
schema to carry over some Prisma-level attributes that aren't picked
up by introspection.

Warning
Your current Prisma 2.0 schema will be overwritten, so please
make sure you have a backup!

Are you ready? [Y/n]

One last time, type Y and hit RETURN to confirm.

This is the final output of the Upgrade CLI:


Updating prisma/schema.prisma...
Done updating prisma/schema.prisma!

✔ Congratulations, you're all set!

➤ Note
If you didn't execute all generated SQL commands against your database,
you can re-run the Upgrade CLI at any time.

Note that the Upgrade CLI doesn't resolve all of the schema incompatibilities
between Prisma 1 and Prisma 2. If you want to resolve the remaining ones,
you can do so manually by following this guide:
https://pris.ly/d/upgrading-the-prisma-layer

➤ Next steps
Otherwise you can continue your upgrade process by installing Prisma Client 2:
npm install @prisma/client

You can find guides for different upgrade scenarios in the docs:
https://pris.ly/d/upgrade-from-prisma-1

5.3. Final result

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

schema.prisma
1model User {
2  id       String   @id @default(cuid())
3  name     String
4  email    String?  @unique
5  jsonData Json?
6  role     Role     @default(CUSTOMER)
7  Post     Post[]
8  Profile  Profile?
9}
10
11model Post {
12  id        String     @id @default(cuid())
13  createdAt DateTime   @default(now())
14  updatedAt DateTime   @updatedAt
15  title     String
16  content   String?
17  published Boolean    @default(false)
18  authorId  String?
19  User      User?      @relation(fields: [authorId], references: [id])
20  Category  Category[] @relation(references: [id])
21}
22
23model Profile {
24  id   String  @id @default(cuid())
25  bio  String?
26  user String? @unique
27  User User?   @relation(fields: [user], references: [id])
28}
29
30model Category {
31  id   String @id @default(cuid())
32  name String
33  Post Post[] @relation(references: [id])
34}
35
36enum Role {
37  ADMIN
38  CUSTOMER
39}

5.4. Rename relation fields

One thing you'll notice with this version of the Prisma 2 schema is that all relation fields are named after their respective models, e.g:

schema.prisma
1model User {
2  Post    Post[]
3  Profile Profile?
4}
5
6model Post {
7  User     User?      @relation(fields: [authorId], references: [id])
8  Category Category[] @relation(references: [id])
9}
10
11model Profile {
12  User User? @relation(fields: [user], references: [id])
13}
14
15model Category {
16  Post Post[] @relation(references: [id])
17}

This is not ideal and you can in fact manually rename all of them to their previous versions!

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

Here's what they look like after the rename:

schema.prisma
1model User {
2  posts   Post[]
3  profile Profile?
4}
5
6model Post {
7  author     User?      @relation(fields: [authorId], references: [id])
8  categories Category[] @relation(references: [id])
9}
10
11model Profile {
12  user  String? @unique
13  owner User?   @relation(fields: [user], references: [id])
14}
15
16model Category {
17  posts Post[] @relation(references: [id])
18}

Note: For the 1-1-relation between User and Profile it was not possible to set the old name user for the relation field. This is because there'd be a naming conflict with the already existing relation scalar field that holds the foreign key. In that case, you can choose a different name or alternatively rename the foreign key column directly in the database via SQL.

5.5. Resolving remaining schema incompatibilities

There are a few schema incompatibilities that were not yet resolved by the Upgrade CLI. 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. Install and generate Prisma Client

Now that you have your Prisma 2 schema ready, you can install Prisma Client with the following command:


$npm install @prisma/client

7. Next steps

Congratulations, you have now upgraded your Prisma layer to Prisma 2! From here on, you can move on to update your application code using one of the following guides:

Old to new Nexus: Choose this guide if you're currently running Prisma 1 with GraphQL Nexus.
prisma-binding to Nexus: Choose this guide if you're currently running Prisma 1 with prisma-binding and want to upgrade to Nexus (and TypeScript).
prisma-binding to SDL-first: Choose this guide if you're currently running Prisma 1 with prisma-binding and want to upgrade to an SDL-first GraphQL server.
REST API: Choose this guide if you're currently running Prisma 1 using Prisma Client 1 and are building a REST API.

Bonus: Prisma Client API comparison

This section contains a high-level and side-by-side comparison of the Prisma Client APIs of Prisma 1 and Prisma 2. For more details about the new Prisma Client API, you can explore the Prisma Client docs.

Reading single records

Prisma Client (v1)


const user = await prisma.user({ id: 1 })

Prisma Client (v2)


await prisma.user.findUnique({
  where: { id: 1 },
})

Reading lists of records

Prisma Client (v1)


const user = await prisma.users()

Prisma Client (v2)


await prisma.user.findMany()

Filtering lists

Prisma Client (v1)


const users = await prisma.users({
  where: {
    name: 'Alice',
  },
})

Prisma Client (v2)


await prisma.user.findMany({
  where: {
    name: 'Alice',
  },
})

Paginating lists

Prisma Client (v1)


const posts = await prisma.posts({
  skip: 5,
  first: 10,
})

Prisma Client (v2)


await prisma.user.findMany({
  skip: 5,
  take: 10,
})

Note: You can learn more about the new pagination API in the respective release notes or the Pagination page in the docs.

Sorting lists

Prisma Client (v1)


await prisma.posts({
  orderBy: 'title_ASC',
})

Prisma Client (v2)


await prisma.posts({
  orderBy: {
    title: 'asc',
  },
})

Creating records

Prisma Client (v1)


await prisma.createUser({
  name: 'Alice',
})

Prisma Client (v2)


await prisma.user.create({
  data: {
    name: 'Alice',
  },
})

Updating records

Prisma Client (v1)


await prisma.updateUser({
  where: { id: 1 },
  data: {
    name: 'James',
    email: 'james@prisma.io',
  },
})

Prisma Client (v2)


await prisma.user.update({
  where: { id: 1 },
  data: {
    name: 'James',
    email: 'james@prisma.io',
  },
})

Deleting records

Prisma Client (v1)


await prisma.deleteUser({ id: 1 })

Prisma Client (v2)


await prisma.user.delete({
  where: { id: 1 },
})

Selecting fields & loading relations

In Prisma 1, the only ways to select specific fields and/or load relations of an object was by using the string-based $fragment and $graphql functions. With Prisma 2, this is now done in a clean and type-safe manner using select and include.

Another benefit of this approach is that you can use select and include on any Prisma Client query, e.g. findUnique, findMany, create, update, delete, ...

Prisma Client (v1)


await prisma.user({ id: 1 }).$fragment(`
  fragment NameAndEmail on User { id email }`
`)

Prisma Client (v2)


await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    email: true,
  },
})

As an example, creating a new record and only retrieving the id in the returned object was not possible in Prisma 1. With Prisma 2 you can achieve this as follows:


await prisma.user.create({
  data: {
    name: 'Alice',
  },
  select: {
    id: true,
  },
})

Was this helpful?

Edit this page on GitHub

Prisma ORM

Prisma Data Platform

Latest from the blog

Upgrading the Prisma layer

Overview

1. Install Prisma 2 CLI

2. Create your Prisma 2 schema

3. Determine your connection URL and connect to your database

4. Introspect your database

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

5.1. Fix the database schema via plain SQL (non-breaking)

5.1.1. Fix columns with ENUM data types

5.1.2. Add missing DEFAULT constraints to the database

5.1.3. Fix columns with JSON data types

5.1.4. Replicate @createdAt behavior in Prisma 2

5.1.5. Fix 1-1 relations by adding UNIQUE constraints

5.1.6. Fix mismatch of CUID length

5.1.7. Breaking changes detected

5.2. Fix the database schema via plain SQL (breaking)

5.2.1. Fix incorrect m-n relations

5.2. Re-introspect your database to update your Prisma schema

5.2. Add missing attributes to the Prisma 2 schema and other schema fixes

5.3. Final result

5.4. Rename relation fields

5.5. Resolving remaining schema incompatibilities

6. Install and generate Prisma Client

7. Next steps

Bonus: Prisma Client API comparison

Reading single records

Reading lists of records

Filtering lists

Paginating lists

Sorting lists

Creating records

Updating records

Deleting records

Selecting fields & loading relations

Was this helpful?

Product

Developers

Use Cases

Company

Newsletter

5.1.2. Add missing `DEFAULT` constraints to the database

5.1.4. Replicate `@createdAt` behavior in Prisma 2

5.1.5. Fix 1-1 relations by adding `UNIQUE` constraints