---
title: 'Schema incompatibilities in MySQL'
sidebar_label: 'Schema incompatibilities'
metaTitle: 'Schema Incompatibilities | MySQL'
metaDescription: 'Problems and workarounds for Prisma 1 and 2.0 schemas with MySQL'
dbSwitcher: ['postgresql', 'mysql']
sidebar_class_name: hidden-sidebar
pagination_next: orm/more/upgrade-guides/upgrade-from-prisma-1/upgrading-the-prisma-layer-mysql
slugSwitch: /orm/more/upgrade-guides/upgrade-from-prisma-1/schema-incompatibilities-
---
 
## 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

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

#### Prisma 1 generated SQL migration

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

```prisma file=schema.prisma showLineNumbers
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:

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

```prisma line-number file=schema.prisma highlight=3;normal showLineNumbers
model Post {
  id        String  @id
  //highlight-next-line
  published Boolean @default(false)
}
```

#### Manually add a `@default` attribute to the Prisma model

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

```prisma line-number file=schema.prisma highlight=3;add showLineNumbers
model Post {
  id        String
  //add-next-line
  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

```graphql
type Post {
  id: ID! @id
}
```

#### Prisma 1 generated SQL migration

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

#### Result of introspection in Prisma ORM versions 2._x_ and later

```prisma file=schema.prisma showLineNumbers
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:

```prisma line-number file=schema.prisma highlight=2;add showLineNumbers
model Post {
  //add-next-line
  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

```graphql line-number highlight=3;normal
type Post {
  id: ID! @id
  createdAt: DateTime! @createdAt
}
```

#### Prisma 1 generated SQL migration

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

```prisma file=schema.prisma showLineNumbers
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:

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

```prisma file=schema.prisma showLineNumbers
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:

```prisma line-number file=schema.prisma highlight=3;normal showLineNumbers
model Post {
  id        String   @id
  //highlight-next-line
  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

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

#### Prisma 1 generated SQL migration

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

#### Result of introspection in Prisma ORM 2._x_ and later versions

```prisma file=schema.prisma showLineNumbers
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:

```prisma line-number file=schema.prisma highlight=3;add showLineNumbers
model Post {
  id        String   @id
  //add-next-line
  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](https://www.prisma.io/blog/datamodel-v11-lrzqy1f56c90) 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](/orm/prisma-schema/data-model/relations/many-to-many-relations#relation-tables) 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)

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

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

```prisma file=schema.prisma showLineNumbers
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:

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

```prisma line-number file=schema.prisma highlight=3;normal showLineNumbers
model User {
  id      String   @id
  //highlight-next-line
  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

```graphql
type User {
  id: ID! @id
  posts: [Post!]!
}

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

#### Prisma 1 generated SQL migration

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

```prisma file=schema.prisma showLineNumbers
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](/orm/prisma-schema/data-model/relations/many-to-many-relations#conventions-for-relation-tables-in-implicit-m-n-relations) 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:
   ```sql
   ALTER TABLE `Post` ADD COLUMN `authorId` VARCHAR(25);
   ALTER TABLE `Post` ADD FOREIGN KEY (`authorId`) REFERENCES `User` (`id`);
   ```
1. 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`
   1. Inserts the value from column `B` as the value for `authorId` into that `Post` record
   ```sql
   UPDATE Post, _PostToUser
   SET Post.authorId = _PostToUser.B
   WHERE Post.id = _PostToUser.A
   ```
1. Delete the `_PostToUser` relation table
   ```sql
   DROP TABLE `_PostToUser`;
   ```

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

```prisma line-number file=schema.prisma highlight=3,8,9;normal showLineNumbers
model User {
  id   String @id
  //highlight-next-line
  Post Post[]
}

model Post {
  id       String @id
  //highlight-start
  User     User   @relation(fields: [authorId], references: [id])
  authorId String
  //highlight-end
}
```

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

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

#### Prisma 1 generated SQL migration

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

#### Result of introspection in Prisma ORM 2._x_ and later versions

```prisma file=schema.prisma showLineNumbers
model User {
  id       String  @id
  jsonData String?
}
```

### Workaround

You can manually change the type of the column to `JSON`

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

```prisma line-number file=schema.prisma highlight=3;normal showLineNumbers
model User {
  id       String @id
  //highlight-next-line
  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

```graphql
type User {
  id: ID! @id
  role: Role
}

enum Role {
  ADMIN
  CUSTOMER
}
```

#### Prisma 1 generated SQL migration

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

#### Result of introspection in Prisma ORM 2._x_ and later versions

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

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

```prisma line-number file=schema.prisma highlight=3,6-9;normal showLineNumbers
model User {
  id   String @id
  //highlight-next-line
  role Role?
}

//highlight-start
enum Role {
  ADMIN
  CUSTOMER
}
//highlight-end
```

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

```graphql
type User {
  id: ID! @id
}
```

#### Prisma 1 generated SQL migration

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

#### Result of introspection in Prisma ORM 2._x_ and later versions

```prisma file=schema.prisma showLineNumbers
model User {
  id String @id
}
```

### Workaround

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

```sql
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](/orm/more/upgrade-guides/upgrade-from-prisma-1/how-to-upgrade#prisma-1-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](https://www.postgresql.org/docs/9.1/arrays.html).

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

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

#### Prisma 1 generated SQL migration

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

```prisma file=schema.prisma showLineNumbers
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).

<details>

<summary>Expand for sample Prisma Client API calls</summary>

To access the coinflips data, you will now have to always [`include`](/orm/prisma-client/queries/select-fields#return-nested-objects-by-selecting-relation-fields) it in your queries:

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

```js
{
  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:

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

```json
[false, true, false, true, true, false]
```

</details>

### Workaround

The following workaround is only available for PostgreSQL users!

As scalar lists (i.e. [arrays](https://www.postgresql.org/docs/9.1/arrays.html)) 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:
   ```sql
   ALTER TABLE "User" ADD COLUMN coinflips BOOLEAN[];
   ```
1. Migrate the data from `"User_coinflips".value` to `"User.coinflips"`:
   ```sql
   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";
   ```
1. To cleanup, you can delete the `User_coinflips` table:
   ```sql
   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:

```prisma file=schema.prisma showLineNumbers
model User {
  id        String    @id
  coinflips Boolean[]
}
```

You can keep using Prisma Client as before:

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

This is the result from the API call:

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