Upgrading the Prisma layer
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):
$
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
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
):
$
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:
$
Now you can run init
and it will succeed:
$
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-started2. 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-schema34datasource db {5 provider = "postgresql"6 url = env("DATABASE_URL")7}89generator 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 inindex.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 theprovider
field. - If you're using MySQL, it needs to define the value
"mysql"
in theprovider
field.
Switch around with the tabs in the code block to see examples of both:
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: 44663 databases:4 default:5 connector: postgres6 host: postgres7 port: 54328 user: prisma9 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: 44663 databases:4 default:5 connector: mysql6 host: mysql7 port: 33068 user: root9 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):
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:
$
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? @unique4 name String5 role String6 jsonData String?7 Profile Profile[]8 Post Post[]9}1011model Post {12 id String @id @default(cuid())13 createdAt DateTime14 updatedAt DateTime15 title String16 content String?17 published Boolean18 Category Category[]19 User User[]20}2122model Profile {23 id String @id @default(cuid())24 bio String?25 user String? @unique26 User User? @relation(fields: [user], references: [id])27}2829model Category {30 id String @id @default(cuid())31 name String32 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
andupdatedAt
fields onPost
- no default value for the
role
field onUser
- no default value for the
published
field onPost
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 relationUser
↔Post
is a m-n instead of 1-n relation- relation fields are uppercased (e.g.
Profile
andPost
onUser
) - the
jsonData
field onUser
is of typeString
instead ofJson
- the
role
field onUser
is of typeString
instead ofRole
, theenum
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:
$
The CLI will greet you with the following message:
◮ Welcome to the interactive Prisma Upgrade CLI that helps with theupgrade 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➤ GoalThe Upgrade CLI helps you resolve the schema incompatibilitiesbetween Prisma 1 and Prisma 2. Learn more in the docs:https://pris.ly/d/schema-incompatibilities➤ How it worksThroughout the process, you'll need to adjust your database schema by sendingSQL 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.➤ NoteIt is recommended that you make a full backup of your existing data before startingthe upgrade process. If possible, the migration should be performed in a stagingenvironment before executed against a production environment.➤ HelpIf you have any questions or run into any problems along the way,please create an issue at:https://github.com/prisma/upgrade/issues/newAre 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 schemaRun the following SQL statements against your database:Fix columns with ENUM data typeshttps://pris.ly/d/schema-incompatibilities#enums-are-represented-as-text-in-databaseCREATE 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 databasehttps://pris.ly/d/schema-incompatibilities#default-values-arent-represented-in-databaseALTER 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 typeshttps://pris.ly/d/schema-incompatibilities#json-type-is-represented-as-text-in-databaseALTER TABLE "default$default"."User" ALTER COLUMN "jsonData" SET DATA TYPE JSONB USING "jsonData"::TEXT::JSONB;Replicate `@createdAt` behavior in Prisma 2https://pris.ly/d/schema-incompatibilities#createdat-isnt-represented-in-databaseALTER TABLE "default$default"."Post" ALTER COLUMN "createdAt" SET DEFAULT CURRENT_TIMESTAMP;Fix 1-1 relations by adding `UNIQUE` constraintshttps://pris.ly/d/schema-incompatibilities#inline-1-1-relations-are-recognized-as-1-n-missing-unique-constraintALTER TABLE "default$default"."Profile" ADD UNIQUE ("user");Migrate IDs from varchar(25) to varchar(30)https://pris.ly/d/schema-incompatibilities#mismatching-cuid-lengthALTER 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 detectedIn order to fully optimize your database schema, you'll need to run a few SQLstatements that can break your Prisma 1 setup. Note that these changes are optionaland 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 wheneveryou 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 schemaRun the following SQL statements against your database:Fix columns with ENUM data typeshttps://pris.ly/d/schema-incompatibilities#enums-are-represented-as-text-in-databaseALTER TABLE `User` CHANGE `role` `role` ENUM('ADMIN', 'CUSTOMER') NOT NULL;Add missing `DEFAULT` constraints to the databasehttps://pris.ly/d/schema-incompatibilities#default-values-arent-represented-in-databaseALTER 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 typeshttps://pris.ly/d/schema-incompatibilities#json-type-is-represented-as-text-in-databaseALTER TABLE `User` CHANGE `jsonData` `jsonData` JSON ;Replicate `@createdAt` behavior in Prisma 2.0https://pris.ly/d/schema-incompatibilities#createdat-isnt-represented-in-databaseALTER TABLE `Post` CHANGE `createdAt` `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;Fix 1-1 relations by adding `UNIQUE` constraintshttps://pris.ly/d/schema-incompatibilities#inline-1-1-relations-are-recognized-as-1-n-missing-unique-constraintALTER TABLE `Profile` ADD UNIQUE (`user`);Migrate IDs from varchar(25) to varchar(30)https://pris.ly/d/schema-incompatibilities#mismatching-cuid-lengthSET 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 detectedIn order to fully optimize your database schema, you'll need to run a few SQLstatements that can break your Prisma 1 setup. Note that these changes are optionaland 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 wheneveryou 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 typeshttps://pris.ly/d/schema-incompatibilities#enums-are-represented-as-text-in-databaseCREATE 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 typeshttps://pris.ly/d/schema-incompatibilities#enums-are-represented-as-text-in-databaseALTER 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 databasehttps://pris.ly/d/schema-incompatibilities#default-values-arent-represented-in-databaseALTER 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 databasehttps://pris.ly/d/schema-incompatibilities#default-values-arent-represented-in-databaseALTER 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 typeshttps://pris.ly/d/schema-incompatibilities#json-type-is-represented-as-text-in-databaseALTER 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 typeshttps://pris.ly/d/schema-incompatibilities#json-type-is-represented-as-text-in-databaseALTER 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.0https://pris.ly/d/schema-incompatibilities#createdat-isnt-represented-in-databaseALTER 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.0https://pris.ly/d/schema-incompatibilities#createdat-isnt-represented-in-databaseALTER 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` constraintshttps://pris.ly/d/schema-incompatibilities#inline-1-1-relations-are-recognized-as-1-n-missing-unique-constraintALTER 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` constraintshttps://pris.ly/d/schema-incompatibilities#inline-1-1-relations-are-recognized-as-1-n-missing-unique-constraintALTER 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-lengthALTER 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-lengthSET 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 theUser
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 schemaRun the following SQL statements against your database:Fix one-to-many table relationshttps://pris.ly/d/schema-incompatibilities#all-non-inline-relations-are-recognized-as-m-nALTER 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 StepsAfter 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 checkthe 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 canskip to the last step where the Upgrade CLI adds missing attributes to your Prisma 2schema 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 thePost
table. This column should be a foreign key that references theid
field of theUser
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 columnA
- Inserts the value from column
B
as the value forauthorId
into thatPost
record
UPDATE Post, _PostToUserSET Post.authorId = _PostToUser.BWHERE Post.id = _PostToUser.A - Finds the respective
- Delete the
_PostToUser
relation tableDROP TABLE `_PostToUser`;
➤ Adjust your database schemaRun the following SQL statements against your database:Fix one-to-many table relationshttps://pris.ly/d/schema-incompatibilities#all-non-inline-relations-are-recognized-as-m-nALTER 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 StepsAfter 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 checkthe 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 canskip to the last step where the Upgrade CLI adds missing attributes to your Prisma 2schema 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 thePost
table. This column should be a foreign key that references theid
field of theUser
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 columnA
- Inserts the value from column
B
as the value forauthorId
into thatPost
record
UPDATE `Post`, `_PostToUser` SET `Post`.`authorId` = `_PostToUser`.B where `_PostToUser`.A = `Post`.`id`; - Finds the respective
- Delete the
_PostToUser
relation tableDROP 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:
$
This time, the resulting Prisma schema looks as follows:
schema.prisma
1model User {2 id String @id3 name String4 email String? @unique5 jsonData Json?6 role Role @default(CUSTOMER)7 Post Post[]8 Profile Profile?9}1011model Post {12 id String @id13 createdAt DateTime @default(now())14 updatedAt DateTime15 title String16 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}2223model Category {24 id String @id25 name String26 Post Post[] @relation(references: [id])27}2829model Profile {30 bio String?31 id String @id32 user String? @unique33 User User? @relation(fields: [user], references: [id])34}3536enum Role {37 ADMIN38 CUSTOMER39}
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 nextAs a last step, some final adjustments will be made to your Prisma 2 schemato 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.0schema to carry over some Prisma-level attributes that aren't pickedup by introspection.WarningYour current Prisma 2.0 schema will be overwritten, so pleasemake 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:
$
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 StepsAfter 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 checkthe 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 canskip to the last step where the Upgrade CLI adds missing attributes to your Prisma 2schema 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 nextAs a last step, some final adjustments will be made to your Prisma 2 schemato 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.0schema to carry over some Prisma-level attributes that aren't pickedup by introspection.WarningYour current Prisma 2.0 schema will be overwritten, so pleasemake 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!➤ NoteIf 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 incompatibilitiesbetween 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 stepsOtherwise you can continue your upgrade process by installing Prisma Client 2:npm install @prisma/clientYou 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 String4 email String? @unique5 jsonData Json?6 role Role @default(CUSTOMER)7 Post Post[]8 Profile Profile?9}1011model Post {12 id String @id @default(cuid())13 createdAt DateTime @default(now())14 updatedAt DateTime @updatedAt15 title String16 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}2223model Profile {24 id String @id @default(cuid())25 bio String?26 user String? @unique27 User User? @relation(fields: [user], references: [id])28}2930model Category {31 id String @id @default(cuid())32 name String33 Post Post[] @relation(references: [id])34}3536enum Role {37 ADMIN38 CUSTOMER39}
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}56model Post {7 User User? @relation(fields: [authorId], references: [id])8 Category Category[] @relation(references: [id])9}1011model Profile {12 User User? @relation(fields: [user], references: [id])13}1415model 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}56model Post {7 author User? @relation(fields: [authorId], references: [id])8 categories Category[] @relation(references: [id])9}1011model Profile {12 user String? @unique13 owner User? @relation(fields: [user], references: [id])14}1516model Category {17 posts Post[] @relation(references: [id])18}
Note: For the 1-1-relation between
User
andProfile
it was not possible to set the old nameuser
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:
$
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
const user = await prisma.user({ id: 1 })
await prisma.user.findUnique({where: { id: 1 },})
Reading lists of records
const user = await prisma.users()
await prisma.user.findMany()
Filtering lists
const users = await prisma.users({where: {name: 'Alice',},})
await prisma.user.findMany({where: {name: 'Alice',},})
Paginating lists
const posts = await prisma.posts({skip: 5,first: 10,})
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
await prisma.posts({orderBy: 'title_ASC',})
await prisma.posts({orderBy: {title: 'asc',},})
Creating records
await prisma.createUser({name: 'Alice',})
await prisma.user.create({data: {name: 'Alice',},})
Updating records
await prisma.updateUser({where: { id: 1 },data: {name: 'James',email: 'james@prisma.io',},})
await prisma.user.update({where: { id: 1 },data: {name: 'James',email: 'james@prisma.io',},})
Deleting records
await prisma.deleteUser({ id: 1 })
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
, ...
await prisma.user({ id: 1 }).$fragment(`fragment NameAndEmail on User { id email }``)
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,},})