Overview

Each section on this page describes a potential problem when upgrading from Prisma 1 to Prisma 2.0 and explains the available workarounds.

Default values aren't represented in database

Problem

When adding the @default directive in a Prisma 1 datamodel, the default values for this field are generated by the Prisma server at runtime. There's no DEFAULT constraint added to the database column. Because this constraint is not reflected in the database itself, the Prisma 2.0 introspection can't recognize it.

Example

Prisma 1 datamodel

1type Post {
2 id: ID! @id
3 published: Boolean @default(value: false)
4}

Prisma 1 generated SQL migration

1CREATE TABLE "Post" (
2 id VARCHAR(25) PRIMARY KEY NOT NULL,
3 published BOOLEAN NOT NULL
4);

Result of Prisma 2.0 introspection

1model Post {
2 id String @id
3 published Boolean
4}

Because the DEFAULT constraint has not been added to the database when mapping the Prisma 1 datamodel to the database with prisma deploy, Prisma 2.0 doesn't recognize it during introspection.

Workarounds

Manually add a DEFAULT constraint to the database column

You can alter the column to add the DEFAULT constraint as follows:

1ALTER TABLE "Post"
2 ALTER COLUMN published SET DEFAULT false;
1ALTER TABLE `Post`
2 ALTER COLUMN published SET DEFAULT false;

After this adjustment, you can re-introspect your database and the @default attribute will be added to the published field:

1model Post {
2 id String @id
3 published Boolean @default(false)
4}

Manually add a @default attribute to the Prisma model

You can add the @default attribute to the Prisma model:

1model Post {
2 id String
+ published Boolean @default(false)
4}

If the @default attribute is set in the Prisma schema and you run prisma generate, the resulting Prisma Client code will generate the specified default values at runtime (similar to what the Prisma server did in Prisma 1).

The main drawback of this approach is that after each re-introspection the @default attribute will be removed (because introspection overwrites the current Prisma schema) and you'll need to re-add it again.

Generated CUIDs as ID values aren't represented in database

Problem

Prisma 1 auto-generates ID values as CUIDs for ID fields when they're annotated with the @id directive. These CUIDs are generated by the Prisma server at runtime. Because this behaviour is not reflected in the database itself, the Prisma 2.0 introspection can't recognize it.

Example

Prisma 1 datamodel

1type Post {
2 id: ID! @id
3}

Prisma 1 generated SQL migration

1CREATE TABLE "Post" (
2 id VARCHAR(25) PRIMARY KEY NOT NULL
3);

Result of Prisma 2.0 introspection

1model Post {
2 id String @id
3}

Because there's no indication of the CUID behaviour in the database, Prisma's introspection doesn't recognize it.

Workaround

As a workaround, you can manually add the @default(cuid()) attribute to the Prisma model:

1model Post {
+ id String @id @default(cuid())
3}

If the @default attribute is set in the Prisma schema and you run prisma generate, the resulting Prisma Client code will generate the specified default values at runtime (similar to what the Prisma server did in Prisma 1).

Note that you'll have to re-add the attribute after each introspection because introspection removes it (as the previous version of the Prisma schema is overwritten)!

@createdAt isn't represented in database

Problem

Prisma 1 auto-generates values for DateTime fields when they're annotated with the @createdAt directive. These values are generated by the Prisma server at runtime. Because this behaviour is not reflected in the database itself, the Prisma 2.0 introspection can't recognize it.

Example

Prisma 1 datamodel

1type Post {
2 id: ID! @id
3 createdAt: DateTime! @createdAt
4}

Prisma 1 generated SQL migration

1CREATE TABLE "Post" (
2 id VARCHAR(25) PRIMARY KEY NOT NULL,
3 "createdAt" TIMESTAMP NOT NULL
4);

Result of Prisma 2.0 introspection

1model Post {
2 id String @id
3 createdAt DateTime
4}

Workarounds

Manually add DEFAULT CURRENT_TIMESTAMP to the database column

You can alter the column to add the DEFAULT constraint as follows:

1ALTER TABLE "Post"
2 ALTER COLUMN "createdAt" SET DEFAULT CURRENT_TIMESTAMP;

After this adjustment, you can re-introspect your database and the @default attribute will be added to the published field:

1model Post {
2 id String
3 createdAt DateTime @default(now())
4}

Manually add the @default(now()) attribute to the Prisma model

As a workaround, you can manually add the @default(now()) attribute to the Prisma model:

1model Post {
2 id String @id
3 createdAt DateTime @default(now())
4}

If the @default attribute is set in the Prisma schema and you run prisma generate, the resulting Prisma Client code will generate the specified default values at runtime (similar to what the Prisma server did in Prisma 1).

Note that you'll have to re-add the attribute after each introspection because introspection removes it (as the previous version of the Prisma schema is overwritten)!

@updatedAt isn't represented in database

Problem

Prisma 1 auto-generates values for DateTime fields when they're annotated with the @updatedAt directive. These values are generated by the Prisma server at runtime. Because this behaviour is not reflected in the database itself, the Prisma 2.0 introspection can't recognize it.

Example

Prisma 1 datamodel

1type Post {
2 id: ID! @id
3 updatedAt: DateTime! @updatedAt
4}

Prisma 1 generated SQL migration

1CREATE TABLE "Post" (
2 id VARCHAR(25) PRIMARY KEY NOT NULL,
3 updatedAt TIMESTAMP
4);

Result of Prisma 2.0 introspection

1model Post {
2 id String @id
3 updatedAt DateTime
4}

Workarounds

Manually add the @updatedAt attribute to the Prisma model

As a workaround, you can manually add the @updatedAt attribute to the Prisma model:

1model Post {
2 id String @id
+ updatedAt DateTime @updatedAt
4}

If the @updatedAt attribute is set in the Prisma schema and you run prisma generate, the resulting Prisma Client code will automatically generate values for this column when an existing record is updated (similar to what the Prisma server did in Prisma 1).

Note that you'll have to re-add the attribute after each introspection because introspection removes it (as the previous version of the Prisma schema is overwritten)!

Inline 1-1 relations are recognized as 1-n (missing UNIQUE constraint)

Problem

In the datamodel v1.1 that was introduced in Prisma v1.31, 1-1 relations can be declared as inline. In that case, the relation will not be maintained via a relation table but via a single foreign key on one of the two tables involved.

When this approach is used, Prisma doesn't add a UNIQUE constraint to the foreign key column which means that after Prisma 2.0 introspection, this former 1-1 relation will be added as a 1-n relation to the Prisma schema.

Example

Prisma datamodel v1.1 (available from Prisma v1.31)

1type User {
2 id: ID! @id
3 profile: Profile @relation(link: INLINE)
4}
5
6type Profile {
7 id: ID! @id
8 user: User
9}

Note that omitting the @relation directive in this case would result in the same behaviour because link: INLINE is the default for 1-1 relations.

Prisma 1 generated SQL migration

1CREATE TABLE "User" (
2 id VARCHAR(25) PRIMARY KEY NOT NULL
3);
4
5CREATE TABLE "Profile" (
6 id VARCHAR(25) PRIMARY KEY NOT NULL,
7 "user" VARCHAR(25),
8 FOREIGN KEY ("user") REFERENCES "User"(id)
9);

Result of Prisma 2.0 introspection

1model User {
2 id String @id
3 Profile Profile[]
4}
5
6model Profile {
7 id String @id
8 user String?
9 User User? @relation(fields: [user], references: [id])
10}

Because there's no UNIQUE constraint defined on the user column (which represents the foreign key in this relation), Prisma's introspection recognizes the relation as 1-n.

Workarounds

Manually add UNIQUE constraint to the foreign key column

You can alter the foreign key column to add the UNIQUE constraint as follows:

1ALTER TABLE "Profile"
2 ADD CONSTRAINT userId_unique UNIQUE ("user");
1ALTER TABLE `Profile`
2 ADD CONSTRAINT userId_unique UNIQUE (`user`);

After this adjustment, you can re-introspect your database and the 1-1 relation will be properly recognized:

1model User {
2 id String @id
3 Profile Profile?
4}
5
6model Profile {
7 id String @id
8 user String?
9 User User? @relation(fields: [userId], references: [id])
10}

Manually remove the [] type modifier from the Prisma model

You can remove the [] type modifier attribute from the relation field in the Prisma schema:

1model User {
2 id String @id
+ Profile Profile
4}
5
6model Profile {
7 id String @id
8 userId String
9 User User @relation(fields: [userId], references: [id])
10}

The main drawback of this approach is that after each re-introspection the [] type modifier will have to be removed again (because introspection overwrites the current Prisma schema) and you'll need to re-add it again.

All non-inline relations are recognized as m-n

Problem

Prisma 1 represents relations as relation tables most of the time:

  • All relations in the Prisma 1 datamodel v1.0 are represented as relation tables
  • In datamodel v1.1, all m-n relations as well as the 1-1 and 1-n relations declared as link: TABLE are represented as relation tables.

Because of this representation, Prisma 2.0 introspection will recognize all these relations as m-n relations, even though they might have been declared as 1-1 or 1-n in Prisma 1.

Example

Prisma 1 datamodel

1type User {
2 id: ID! @id
3 posts: [Post!]!
4}
5
6type Post {
7 id: ID! @id
8 author: User! @relation(link: TABLE)
9}

Prisma 1 generated SQL migration

1CREATE TABLE "User" (
2 id VARCHAR(25) PRIMARY KEY NOT NULL
3);
4
5CREATE TABLE "Post" (
6 id VARCHAR(25) PRIMARY KEY NOT NULL
7);
8
9CREATE TABLE "_PostToUser" (
10 "A" VARCHAR(25) NOT NULL REFERENCES "Post"(id) ON DELETE CASCADE,
11 "B" VARCHAR(25) NOT NULL REFERENCES "User"(id) ON DELETE CASCADE
12);
13CREATE UNIQUE INDEX "_PostToUser_AB_unique" ON "_PostToUser"("A" text_ops,"B" text_ops);
14CREATE INDEX "_PostToUser_B" ON "_PostToUser"("B" text_ops);

Result of Prisma 2.0 introspection

1model User {
2 id String @id
3 Post Post[] @relation(references: [id])
4}
5
6model Post {
7 id String @id
8 User User[] @relation(references: [id])
9}

Because the relation table that was created by Prisma 1 uses the same conventions for relation tables as Prisma 2.0, the relation now gets recognized as a m-n relation.

Workaround

As a workaround, you can migrate the data into a structure that's compatible with Prisma's 1-n relation:

  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:
    1ALTER TABLE "Post" ADD COLUMN "authorId" VARCHAR(25);
    2ALTER TABLE "Post"
    3ADD CONSTRAINT fk_author
    4FOREIGN KEY ("authorId")
    5REFERENCES "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
    1UPDATE "Post" post
    2SET "authorId" = post_to_user."B"
    3FROM "_PostToUser" post_to_user
    4WHERE post_to_user."A" = post."id";
  3. Delete the _PostToUser relation table
    1DROP TABLE "_PostToUser";
  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:
    1ALTER TABLE `Post` ADD COLUMN `authorId` VARCHAR(25);
    2ALTER 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
    1UPDATE Post, _PostToUser
    2SET Post.authorId = _PostToUser.B
    3WHERE Post.id = _PostToUser.A
  3. Delete the _PostToUser relation table
    1DROP TABLE `_PostToUser`;

After that you can introspect your database and the relation will now be recognized as 1-n:

1model User {
2 id String @id
3 Post Post[]
4}
5
6model Post {
7 id String @id
8 User User @relation(fields: [authorId], references: [id])
9 authorId String
10}

Json type is represented as TEXT in database

Problem

Prisma 1 supports the Json data type in its datamodel. However, in the underlying database, fields of type Json are actually stored as plain strings using the TEXT data type of the underlying database. Any parsing and validation of the stored JSON data is done by the Prisma server at runtime.

Example

Prisma 1 datamodel

1type User {
2 id: ID! @id
3 jsonData: Json
4}

Prisma 1 generated SQL migration

1CREATE TABLE "User" (
2 id VARCHAR(25) PRIMARY KEY NOT NULL,
3 jsonData TEXT
4);

Result of Prisma 2.0 introspection

1model User {
2 id String @id
3 jsonData String?
4}

Workaround

You can manually change the type of the column to JSON

1ALTER TABLE "User" ALTER COLUMN "jsonData" TYPE JSON USING "jsonData"::json;
1ALTER TABLE User MODIFY COLUMN role ENUM('ADMIN', 'CUSTOMER') DEFAULT 'CUSTOMER';

After this adjustment, you can re-introspect your database and the field will now be recognized as Json:

1model User {
2 id String @id
3 jsonData Json?
4}

Enums are represented as TEXT in database

Problem

Prisma 1 supports the enum data type in its datamodel. However, in the underlying database, types declared as enum are actually stored as plain strings using the TEXT data type of the underlying database. Any validation of the stored enum data is done by the Prisma server at runtime.

Example

Prisma 1 datamodel

1type User {
2 id: ID! @id
3 role: Role
4}
5
6enum Role {
7 ADMIN
8 CUSTOMER
9}

Prisma 1 generated SQL migration

1CREATE TABLE "User" (
2 id VARCHAR(25) PRIMARY KEY NOT NULL,
3 role TEXT
4);

Result of Prisma 2.0 introspection

1model User {
2 id String @id
3 role String?
4}

Workaround

You can manually turn the role column into an enum with your desired values:

  1. Create an enum in your database that mirrors the enum you defined in the Prisma 1 datamodel:
    1CREATE TYPE "Role" AS ENUM ('CUSTOMER', 'ADMIN');
  2. Change the type from TEXT to your new enum:
    1ALTER TABLE "User" ALTER COLUMN "role" TYPE "Role"
    2USING "role"::text::"Role";

After introspection, the type is now properly recognized as an enum:

1model User {
2 id String @id
3 role Role?
4}
5
6enum Role {
7 ADMIN
8 CUSTOMER
9}

Scalar lists (arrays) are maintained with extra table

Problem

In Prisma 1, you can define lists of scalar types on your models. Under the hood, this is implemented with an extra table that keeps track of the values in the list.

To remove the approach with the extra table which incurred hidden performance costs, Prisma 2.0 only supports scalar lists only when they're natively supported by the database you use. At the moment, only PostgreSQL supports scalar lists (arrays) natively.

With PostgreSQL, you therefore can keep using scalar lists in Prisma 2.0, but you'll need to perform a data migration to transfer the data from the extra table from Prisma 1 into an actual PostgreSQL array.

Example

Prisma 1 datamodel

1type User {
2 id: ID! @id
3 coinflips: Boolean[]
4}

Prisma 1 generated SQL migration

1CREATE TABLE "User" (
2 id VARCHAR(25) PRIMARY KEY NOT NULL
3);
4
5CREATE TABLE "User_coinflips" (
6 "nodeId" VARCHAR(25) REFERENCES "User"(id),
7 position INTEGER,
8 value BOOLEAN NOT NULL,
9 CONSTRAINT "User_coinflips_pkey" PRIMARY KEY ("nodeId", position)
10);
11CREATE UNIQUE INDEX "User_coinflips_pkey" ON "User_coinflips"("nodeId" text_ops,position int4_ops);

Result of Prisma 2.0 introspection

1model User {
2 id String @id
3 User_coinflips User_coinflips[]
4}
5
6model User_coinflips {
7 nodeId String
8 position Int
9 value Boolean
10 User User @relation(fields: [nodeId], references: [id])
11
12 @@id([nodeId, position])
13}

Note that you can now generate Prisma Client and you'll be able to access the data from the scalar lists through the extra table. PostgreSQL users can alternatively migrate the data into a native PostgreSQL array and continue to benefit from the slicker Prisma Client API for scalar lists (read the section below for more info).

To access the coinflips data, you will now have to always include it in your queries:

1const user = await prisma.user.findOne({
2 where: { id: 1 },
3 include: {
4 coinflips: {
5 orderBy: { position: "asc" }
6 }
7 }
8})

Note: The orderBy is important to retain the order of the list.

This is the `result of the query:

1{
2 id: 1,
3 name: 'Alice',
4 coinflips: [
5 { id: 1, position: 1000, value: false },
6 { id: 2, position: 2000, value: true },
7 { id: 3, position: 3000, value: false },
8 { id: 4, position: 4000, value: true },
9 { id: 5, position: 5000, value: true },
10 { id: 6, position: 6000, value: false }
11 ]
12}

To access just the boolean values from the list, you can map over the coinflips on user as follows:

1const currentCoinflips = user!.coinflips.map(cf => cf.value)

Note: The exclamation mark above means that you're force unwrapping the user value. This is necessary because the user returned from the previous query might be null.

Here's the value of currentCoinflips after the call to map:

1[ false, true, false, true, true, false ]

Workaround

The following workaround is only available for PostgreSQL users!

As scalar lists (i.e. arrays) are available as a native PostgreSQL feature, you can keep using the same notation of coinflips: Boolean[] in your Prisma schema.

However, in order to do so you need to manually migrate the underlying data from the User_coinflips table into a PostgreSQL array. Here's how you can do that:

  1. Add the new coinflips column to the User tables:
    1ALTER TABLE "User" ADD COLUMN coinflips BOOLEAN[];
  2. Migrate the data from "User_coinflips".value to "User.coinflips":
    1UPDATE "User"
    2 SET coinflips = t.flips
    3FROM (
    4 SELECT "nodeId", array_agg(VALUE ORDER BY position) AS flips
    5 FROM "User_coinflips"
    6 GROUP BY "nodeId"
    7) t
    8where t."nodeId" = "User"."id";
  3. To cleanup, you can delete the User_coinflips table:
    1DROP TABLE "User_coinflips";

You can now introspect your database and the coinflips field will be represented as an array in your new Prisma schema:

1model User {
2 id String @id
3 coinflips Boolean[]
4}

You can keep using Prisma Client as before:

1const user = await prisma.user.findOne({
2 where: { id: 1 },
3})

This is the result from the API call:

1{
2 id: 1,
3 name: 'Alice',
4 coinflips: [ false, true, false, true, true, false ]
5}

Cascading deletes are not supported in Prisma 2.0

Problem

Prisma 1 supports configuration of cascading deletes via the @relation(onDelete: CASCADE) directive. Prisma 2.0 doesn't have support for cascading deletes yet.

If your relation is configured via table: INLINE, you can still get the same behaviour by configuring cascading deletes directly in your database. If your relation is represented via a relation table, you need to manually implement cascading deletes in your application code.

Cascading deletes are not yet supported in Prisma 2. For inline relations, you can configure similar behaviour as in Prisma 1 in plain SQL (find the guides here). For relations that were represented as relation tables in Prisma 1, you won't be able to configure cascading deletes on the database layer for now and might have to resort to implementing it inside your application code with Prisma Client.

Note: If you need to implement cascading deletes in your application layer, this tool by our community member Ahmed Elywa might come in handy.

Edit this page on Github