Schema incompatibilities in PostgreSQL

PostgreSQL

Overview

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

schema.prisma

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

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

schema.prisma

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:

schema.prisma

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 ORM 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 ORM versions 2.x and later

schema.prisma

model Post {
  id String @id
}

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

Workaround

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

schema.prisma

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 ORM 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 ORM 2.x and later versions

schema.prisma

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:

schema.prisma

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:

schema.prisma

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 ORM 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 ORM 2.x and later versions

schema.prisma

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:

schema.prisma

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 ORM 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 ORM doesn't add a UNIQUE constraint to the foreign key column which means that after introspection in Prisma ORM version 2.x and later, this former 1-1 relation will be added as a 1-n relation to the Prisma schema.

Example

Prisma ORM datamodel v1.1 (available from Prisma ORM 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 ORM 2.x and later versions

schema.prisma

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 ORM'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");

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

schema.prisma

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 ORM 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 ORM 2.x and later versions

schema.prisma

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 ORM 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 ORM'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";

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

schema.prisma

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 ORM 2.x and later versions

schema.prisma

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;

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

schema.prisma

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 ORM 2.x and later versions

schema.prisma

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:

schema.prisma

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 ORM 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 ORM 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 ORM 2.x and later versions

schema.prisma

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

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 ORM 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 ORM 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 ORM 2 introspection

schema.prisma

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:

schema.prisma

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