Schema incompatibilities

MySQL

Overview

Each section on this page describes a potential problem when upgrading from Prisma 1 to Prisma 2 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 introspection can't recognize it.

Example

Prisma 1 datamodel

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

Prisma 1 generated SQL migration

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

Result of Prisma 2 introspection

model Post {
id String @id
published Boolean
}

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 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:

ALTER TABLE "Post"
ALTER COLUMN published SET DEFAULT false;
ALTER TABLE `Post`
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:

model Post {
id String @id
published Boolean @default(false)
}

Manually add a @default attribute to the Prisma model

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

model Post {
id String
published Boolean @default(false)
}

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

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 introspection can't recognize it.

Example

Prisma 1 datamodel

type Post {
id: ID! @id
}

Prisma 1 generated SQL migration

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

Result of Prisma 2 introspection

model Post {
id String @id
}

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:

model Post {
id String @id @default(cuid())
}

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 introspection can't recognize it.

Example

Prisma 1 datamodel

type Post {
id: ID! @id
createdAt: DateTime! @createdAt
}

Prisma 1 generated SQL migration

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

Result of Prisma 2 introspection

model Post {
id String @id
createdAt DateTime
}

Workarounds

Manually add DEFAULT CURRENT_TIMESTAMP to the database column

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

ALTER TABLE "Post"
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:

model Post {
id String
createdAt DateTime @default(now())
}

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:

model Post {
id String @id
createdAt DateTime @default(now())
}

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 introspection can't recognize it.

Example

Prisma 1 datamodel

type Post {
id: ID! @id
updatedAt: DateTime! @updatedAt
}

Prisma 1 generated SQL migration

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

Result of Prisma 2 introspection

model Post {
id String @id
updatedAt DateTime
}

Workarounds

Manually add the @updatedAt attribute to the Prisma model

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

model Post {
id String @id
updatedAt DateTime @updatedAt
}

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

type User {
id: ID! @id
profile: Profile @relation(link: INLINE)
}
type Profile {
id: ID! @id
user: User
}

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

CREATE TABLE "User" (
id VARCHAR(25) PRIMARY KEY NOT NULL
);
CREATE TABLE "Profile" (
id VARCHAR(25) PRIMARY KEY NOT NULL,
"user" VARCHAR(25),
FOREIGN KEY ("user") REFERENCES "User"(id)
);

Result of Prisma 2 introspection

model User {
id String @id
Profile Profile[]
}
model Profile {
id String @id
user String?
User User? @relation(fields: [user], references: [id])
}

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:

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

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

model User {
id String @id
Profile Profile?
}
model Profile {
id String @id
user String?
User User? @relation(fields: [userId], references: [id])
}

Manually remove the [] type modifier from the Prisma model

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

model User {
id String @id
Profile Profile
}
model Profile {
id String @id
userId String
User User @relation(fields: [userId], references: [id])
}

The drawback of this approach is that re-introspection restores the [] type modifier and you will have to remove it again. We recommend that you add a UNIQUE constraint.

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

type User {
id: ID! @id
posts: [Post!]!
}
type Post {
id: ID! @id
author: User! @relation(link: TABLE)
}

Prisma 1 generated SQL migration

CREATE TABLE "User" (
id VARCHAR(25) PRIMARY KEY NOT NULL
);
CREATE TABLE "Post" (
id VARCHAR(25) PRIMARY KEY NOT NULL
);
CREATE TABLE "_PostToUser" (
"A" VARCHAR(25) NOT NULL REFERENCES "Post"(id) ON DELETE CASCADE,
"B" VARCHAR(25) NOT NULL REFERENCES "User"(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX "_PostToUser_AB_unique" ON "_PostToUser"("A" text_ops,"B" text_ops);
CREATE INDEX "_PostToUser_B" ON "_PostToUser"("B" text_ops);

Result of Prisma 2 introspection

model User {
id String @id
Post Post[] @relation(references: [id])
}
model Post {
id String @id
User User[] @relation(references: [id])
}

Because the relation table that was created by Prisma 1 uses the same conventions for relation tables as Prisma 2, 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:
    ALTER TABLE "Post" ADD COLUMN "authorId" VARCHAR(25);
    ALTER TABLE "Post"
    ADD CONSTRAINT fk_author
    FOREIGN KEY ("authorId")
    REFERENCES "User"("id");
  2. Write a SQL query that reads all the rows from the _PostToUser relation table and for each row:
    1. Finds the respective Post record by looking up the value from column A
    2. Inserts the value from column B as the value for authorId into that Post record
    UPDATE "Post" post
    SET "authorId" = post_to_user."B"
    FROM "_PostToUser" post_to_user
    WHERE post_to_user."A" = post."id";
  3. Delete the _PostToUser relation table
    DROP 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:
    ALTER TABLE `Post` ADD COLUMN `authorId` VARCHAR(25);
    ALTER TABLE `Post` ADD FOREIGN KEY (`authorId`) REFERENCES `User` (`id`);
  2. Write a SQL query that reads all the rows from the _PostToUser relation table and for each row:
    1. Finds the respective Post record by looking up the value from column A
    2. Inserts the value from column B as the value for authorId into that Post record
    UPDATE Post, _PostToUser
    SET Post.authorId = _PostToUser.B
    WHERE Post.id = _PostToUser.A
  3. Delete the _PostToUser relation table
    DROP TABLE `_PostToUser`;

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

model User {
id String @id
Post Post[]
}
model Post {
id String @id
User User @relation(fields: [authorId], references: [id])
authorId String
}

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

type User {
id: ID! @id
jsonData: Json
}

Prisma 1 generated SQL migration

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

Result of Prisma 2 introspection

model User {
id String @id
jsonData String?
}

Workaround

You can manually change the type of the column to JSON

ALTER TABLE "User" ALTER COLUMN "jsonData" TYPE JSON USING "jsonData"::json;
ALTER 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:

model User {
id String @id
jsonData Json?
}

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

type User {
id: ID! @id
role: Role
}
enum Role {
ADMIN
CUSTOMER
}

Prisma 1 generated SQL migration

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

Result of Prisma 2 introspection

model User {
id String @id
role String?
}

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:
    CREATE TYPE "Role" AS ENUM ('CUSTOMER', 'ADMIN');
  2. Change the type from TEXT to your new enum:
    ALTER TABLE "User" ALTER COLUMN "role" TYPE "Role"
    USING "role"::text::"Role";

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

model User {
id String @id
role Role?
}
enum Role {
ADMIN
CUSTOMER
}

Mismatching CUID length

Problem

Prisma 1 uses CUIDs as ID values for all database records. In the underlying database, these IDs are represented as strings with a maximum size of 25 characters (as VARCHAR(25)). However, when configuring default CUIDs in your Prisma 2 schema with @default(cuid()) the generated ID values might exceed the limit of 25 characters (the maximum length might be 30 characters). To make your IDs proof for Prisma 2, you therefore need to adjust the column type to VARCHAR(30).

Example

Prisma 1 datamodel

type User {
id: ID! @id
}

Prisma 1 generated SQL migration

CREATE TABLE "User" (
id VARCHAR(25) PRIMARY KEY NOT NULL
);

Result of Prisma 2 introspection

model User {
id String @id
}

Workaround

You can manually turn the VARCHAR(25) columns into VARCHAR(30):

ALTER TABLE "User" ALTER COLUMN "id" SET DATA TYPE character varying(30);
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE `User` CHANGE `id` `id` char(30) CHARACTER SET utf8 NOT NULL;
SET FOREIGN_KEY_CHECKS=1;

Note: When fixing this issue with the Upgrade CLI, the generated 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.

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 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, 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

type User {
id: ID! @id
coinflips: [Boolean!]! @scalarList(strategy: RELATION)
}

Prisma 1 generated SQL migration

CREATE TABLE "User" (
id VARCHAR(25) PRIMARY KEY NOT NULL
);
CREATE TABLE "User_coinflips" (
"nodeId" VARCHAR(25) REFERENCES "User"(id),
position INTEGER,
value BOOLEAN NOT NULL,
CONSTRAINT "User_coinflips_pkey" PRIMARY KEY ("nodeId", position)
);
CREATE UNIQUE INDEX "User_coinflips_pkey" ON "User_coinflips"("nodeId" text_ops,position int4_ops);

Result of Prisma 2 introspection

model User {
id String @id
User_coinflips User_coinflips[]
}
model User_coinflips {
nodeId String
position Int
value Boolean
User User @relation(fields: [nodeId], references: [id])
@@id([nodeId, position])
}

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:

const user = await prisma.user.findUnique({
where: { id: 1 },
include: {
coinflips: {
orderBy: { position: "asc" }
}
}
})

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

This is the `result of the query:

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

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

const 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:

[ 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:
    ALTER TABLE "User" ADD COLUMN coinflips BOOLEAN[];
  2. Migrate the data from "User_coinflips".value to "User.coinflips":
    UPDATE "User"
    SET coinflips = t.flips
    FROM (
    SELECT "nodeId", array_agg(VALUE ORDER BY position) AS flips
    FROM "User_coinflips"
    GROUP BY "nodeId"
    ) t
    where t."nodeId" = "User"."id";
  3. To cleanup, you can delete the User_coinflips table:
    DROP TABLE "User_coinflips";

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

model User {
id String @id
coinflips Boolean[]
}

You can keep using Prisma Client as before:

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

This is the result from the API call:

{
id: 1,
name: 'Alice',
coinflips: [ false, true, false, true, true, false ]
}

Cascading deletes are not supported in Prisma 2

Problem

Prisma 1 supports configuration of cascading deletes via the @relation(onDelete: CASCADE) directive. Prisma 2 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