Skip to main content

Schema incompatibilities

Overview​

Each section on this page describes a potential problem when upgrading from Prisma 1 to Prisma 2.x and later 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 1 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.x and later versions of 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 introspection in Prisma versions 2.x and later​

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 v2 (and later versions) 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 1 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 1 server at runtime. Because this behavior is not reflected in the database itself, the introspection in Prisma 2.x and later 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 introspection in Prisma versions 2.x and later​

model Post {
  id String @id
}

Because there's no indication of the CUID behavior 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 1 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 1 server at runtime. Because this behavior is not reflected in the database itself, the introspection in Prisma 2.x and later 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 introspection in Prisma 2.x and later versions​

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 createdAt 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 1 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 1 server at runtime. Because this behavior is not reflected in the database itself, the introspection in Prisma 2.x and later 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 introspection in Prisma 2.x and later versions​

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 1 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 introspection in Prisma version 2.x and later, 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 behavior 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 introspection in Prisma 2.x and later versions​

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.

Workaround​

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? @unique
  User User?   @relation(fields: [user], references: [id])
}

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, introspection in Prisma version 2.x and later 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 introspection in Prisma 2.x and later versions​

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 in Prisma version 2.x and later, 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 1 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 introspection in Prisma 2.x and later versions​

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 jsonData JSON;

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 1 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 introspection in Prisma 2.x and later versions​

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.x (or later versions) 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.x (or later versions), 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 introspection in Prisma 2.x and later versions​

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.x and later versions only support 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.x and later versions, 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).

Expand for sample Prisma Client API calls

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