# Migrate MongoDB to MySQL using Prism Key Differences (ok)

## Example 1 chuyển từ postgresql sang mysql

Sử dụng bài <https://learnreact.gitbook.io/learnreact/postgresql/nextjs-prisma-postgres-demo-ok>&#x20;

### Ban đầu có sử dụng cơ sở dữ liệu là postgresql

<figure><img src="/files/8GQ5g9cf4uDo5PSUqmyA" alt=""><figcaption></figcaption></figure>

prisma\schema.prisma

```prisma
generator client {
  provider = "prisma-client-js"
}
// datasource db {
//   provider = "postgresql"
//   url      = env("DATABASE_URL")
// }
datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}
model Account {
  type              String
  provider          String
  providerAccountId String
  refresh_token     String?
  access_token      String?
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String?
  session_state     String?
  createdAt         DateTime @default(now())
  updatedAt         DateTime @default(now())
  userId            Int
  User              User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  @@id([provider, providerAccountId])
}
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  Int
  createdAt DateTime @default(now())
  updatedAt DateTime @default(now())
  User      User     @relation(fields: [authorId], references: [id])
}
model Session {
  sessionToken String   @unique
  expires      DateTime
  createdAt    DateTime @default(now())
  updatedAt    DateTime @default(now())
  id           Int      @id @default(autoincrement())
  userId       Int
  User         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}
model User {
  name          String?
  email         String    @unique
  createdAt     DateTime  @default(now())
  emailVerified DateTime?
  image         String?
  updatedAt     DateTime  @default(now())
  id            Int       @id @default(autoincrement())
  Account       Account[]
  Post          Post[]
  Session       Session[]
}

```

.env

```properties
#DATABASE_URL="postgresql://postgres:123456@localhost:5432/practice"
DATABASE_URL="mysql://root@localhost:3306/prisma"
AUTH_SECRET="P3QNKlJz6Gob2ZxKkPyJWPfmeLbV0OkRP/38HVWxgak="
AUTH_GITHUB_ID=Ov23lir84nNhYsRt3vvZ
AUTH_GITHUB_SECRET=41355bd062ff5433da463defcf069ece92567995

```

<figure><img src="/files/FeTzEBh9kXNrLTyEVrK8" alt=""><figcaption></figcaption></figure>

<figure><img src="/files/0d8CnGXh0FDh1p1bruPo" alt=""><figcaption></figcaption></figure>

## Example 2

<figure><img src="/files/tyGGD9JHW4HfJ87fmsdw" alt=""><figcaption></figcaption></figure>

.env

```properties
# Core configs
NEXT_PUBLIC_API_URL=http://localhost:3001
CLIENT_APP_URL=http://localhost:3000
NODE_ENV="development"
# Prisma
#DATABASE_URL="mongodb+srv://<username>:<password>@cluster0.ns1yp.mongodb.net/myFirstDatabase"
DATABASE_URL="mysql://root@localhost:3306/prisma"
# Clerk Auth
NEXT_PUBLIC_CLERK_PUBLISHABLE_KEY=""
CLERK_SECRET_KEY=""
NEXT_PUBLIC_CLERK_SIGN_IN_URL=""
NEXT_PUBLIC_CLERK_SIGN_UP_URL=""
NEXT_PUBLIC_CLERK_AFTER_SIGN_IN_URL=""
NEXT_PUBLIC_CLERK_AFTER_SIGN_UP_URL=""
# Cloudinary
NEXT_PUBLIC_CLOUDINARY_CLOUD_NAME=""
NEXT_PUBLIC_CLOUDINARY_API_KEY=""
# Stripe
STRIPE_API_KEY=sk_test_your_stripe_secret_key
STRIPE_WEBHOOK_KEY="whsec_your_stripe_webhook_key"

```

prisma\schema.prisma (mongo)

```prisma
generator client {
  provider = "prisma-client-js"

datasource db {
  provider = "mongodb"
  url      = env("DATABASE_URL")
}
model Store {
  id         String      @id @default(auto()) @map("_id") @db.ObjectId
  name       String
  userId     String
  billboards Billboard[] @relation("StoreToBillboard")
  categories Category[]  @relation("StoreToCategory")
  sizes      Size[]      @relation("StoreToSize")
  colors     Color[]     @relation("StoreToColor")
  products   Product[]   @relation("StoreToProduct")
  orders     Order[]     @relation("StoreToOrder")
  createdAt  DateTime    @default(now())
  updatedAt  DateTime    @updatedAt
}
model Billboard {
  id         String     @id @default(auto()) @map("_id") @db.ObjectId
  storeId    String     @db.ObjectId
  store      Store      @relation("StoreToBillboard", fields: [storeId], references: [id])
  label      String
  imageUrl   String
  categories Category[]
  createdAt  DateTime   @default(now())
  updatedAt  DateTime   @updatedAt
  @@index([storeId])
}
model Category {
  id          String    @id @default(auto()) @map("_id") @db.ObjectId
  name        String
  slug        String?
  storeId     String    @db.ObjectId
  store       Store     @relation("StoreToCategory", fields: [storeId], references: [id])
  products    Product[] @relation("CategoryToProduct")
  billboardId String    @db.ObjectId
  billboard   Billboard @relation(fields: [billboardId], references: [id])
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  @@index([storeId])
  @@index([billboardId])
}
model Size {
  id        String    @id @default(auto()) @map("_id") @db.ObjectId
  name      String
  value     String
  storeId   String    @db.ObjectId
  store     Store     @relation("StoreToSize", fields: [storeId], references: [id])
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  product   Product[]
  @@index([storeId])
}
model Color {
  id        String    @id @default(auto()) @map("_id") @db.ObjectId
  name      String
  value     String
  storeId   String    @db.ObjectId
  store     Store     @relation("StoreToColor", fields: [storeId], references: [id])
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  product   Product[]
  @@index([storeId])
}
model Product {
  id         String      @id @default(auto()) @map("_id") @db.ObjectId
  storeId    String      @db.ObjectId
  store      Store       @relation("StoreToProduct", fields: [storeId], references: [id])
  categoryId String      @db.ObjectId
  category   Category    @relation("CategoryToProduct", fields: [categoryId], references: [id])
  sizeId     String      @db.ObjectId
  size       Size        @relation(fields: [sizeId], references: [id])
  colorId    String      @db.ObjectId
  color      Color       @relation(fields: [colorId], references: [id])
  name       String
  price      Float
  slug       String?
  orderItems OrderItem[]
  isFeatured Boolean     @default(false)
  isArchived Boolean     @default(false)
  createdAt  DateTime    @default(now())
  updatedAt  DateTime    @updatedAt
  images     Image[]
  @@index([storeId])
  @@index([categoryId])
  @@index([sizeId])
  @@index([colorId])
}
model Image {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  productId String   @db.ObjectId
  product   Product  @relation(fields: [productId], references: [id], onDelete: Cascade)
  url       String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  @@index([productId])
}
model Order {
  id         String      @id @default(auto()) @map("_id") @db.ObjectId
  storeId    String      @db.ObjectId
  store      Store       @relation("StoreToOrder", fields: [storeId], references: [id])
  orderItems OrderItem[]
  isPaid     Boolean     @default(false)
  phone      String      @default("")
  address    String      @default("")
  createdAt  DateTime    @default(now())
  updatedAt  DateTime    @updatedAt
  @@index([storeId])
}
model OrderItem {
  id        String  @id @default(auto()) @map("_id") @db.ObjectId
  orderId   String  @db.ObjectId
  order     Order   @relation(fields: [orderId], references: [id])
  productId String  @db.ObjectId
 orderId   Int  
  order     Order   @relation(fields: [orderId], r
  @@index([orderId])
  @@index([productId])eferences: [id])
  productId Int  
 orderId   Int  
  order     Order   @relation(fields: [orderId], references: [id])
  productId Int  
  product   Product @relation(fields: [productId], references: [id])
}

```

prisma\schema.prisma (mysql)

```prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model Store {
  id         Int         @id @default(autoincrement())
  name       String
  userId     Int
  billboards Billboard[]
  categories Category[]
  sizes      Size[]
  colors     Color[]
  products   Product[]
  orders     Order[]
  createdAt  DateTime    @default(now())
  updatedAt  DateTime    @updatedAt
}

model Billboard {
  id         Int        @id @default(autoincrement())
  storeId    Int
  store      Store      @relation(fields: [storeId], references: [id])
  label      String
  imageUrl   String
  categories Category[]
  createdAt  DateTime   @default(now())
  updatedAt  DateTime   @updatedAt
}

model Category {
  id          Int       @id @default(autoincrement())
  name        String
  slug        String?
  storeId     Int
  store       Store     @relation(fields: [storeId], references: [id])
  products    Product[]
  billboardId Int
  billboard   Billboard @relation(fields: [billboardId], references: [id])
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
}

model Size {
  id        Int       @id @default(autoincrement())
  name      String
  value     String
  storeId   Int
  store     Store     @relation(fields: [storeId], references: [id])
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  product   Product[]
}

model Color {
  id        Int       @id @default(autoincrement())
  name      String
  value     String
  storeId   Int
  store     Store     @relation(fields: [storeId], references: [id])
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  product   Product[]

  @@index([storeId])
}

model Product {
  id         Int         @id @default(autoincrement())
  storeId    Int
  store      Store       @relation(fields: [storeId], references: [id])
  categoryId Int
  category   Category    @relation(fields: [categoryId], references: [id])
  sizeId     Int
  size       Size        @relation(fields: [sizeId], references: [id])
  colorId    Int
  color      Color       @relation(fields: [colorId], references: [id])
  name       String
  price      Float
  slug       String?
  orderItems OrderItem[]
  isFeatured Boolean     @default(false)
  isArchived Boolean     @default(false)
  createdAt  DateTime    @default(now())
  updatedAt  DateTime    @updatedAt
  images     Image[]
}

model Image {
  id        Int      @id @default(autoincrement())
  productId Int
  product   Product  @relation(fields: [productId], references: [id], onDelete: Cascade)
  url       String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Order {
  id         Int         @id @default(autoincrement())
  storeId    Int
  store      Store       @relation(fields: [storeId], references: [id])
  orderItems OrderItem[]
  isPaid     Boolean     @default(false)
  phone      String      @default("")
  address    String      @default("")
  createdAt  DateTime    @default(now())
  updatedAt  DateTime    @updatedAt
}

model OrderItem {
  id        Int     @id @default(autoincrement())
  orderId   Int
  order     Order   @relation(fields: [orderId], references: [id])
  productId Int
  product   Product @relation(fields: [productId], references: [id])
}

```

{% file src="/files/yebXt728Jz3qwnQshOCU" %}

### Migrate MongoDB to MySQL using Prism Key Differences

The `schema.prisma` file defines the data models, relationships, and database connection details for Prisma. When using different databases like MongoDB and MySQL, the `schema.prisma` file will have differences due to the unique characteristics and data types of each database.

#### Key Differences Between `schema.prisma` for MongoDB and MySQL <a href="#key-differences-between-raw-schemaprisma-endraw-for-mongodb-and-mysql" id="key-differences-between-raw-schemaprisma-endraw-for-mongodb-and-mysql"></a>

**1. Datasource Configuration**

The `datasource` block specifies the database provider (`mongodb` or `mysql`) and connection details. The provider will change depending on the database you are using.

* **MongoDB Configuration:**

```typescript
  datasource db {
    provider = "mongodb"
    url      = env("DATABASE_URL")
  }
```

* **MySQL Configuration:**

```typescript
  datasource db {
    provider = "mysql"
    url      = env("DATABASE_URL")
  }
```

**2. Data Model Differences**

MongoDB and MySQL have different data types and ways of defining primary keys and relationships. Here are the primary differences:

* **Primary Key Definition:**
  * In **MongoDB**, the primary key is usually an `ObjectId` and is mapped using the `@map("_id")` directive.
  * In **MySQL**, the primary key is typically an integer with auto-increment.
* **Data Type Differences:**
  * **MongoDB** supports data types like `String`, `Int`, `Float`, `Boolean`, `DateTime`, and custom types like `Json` and `ObjectId`.
  * **MySQL** has a more structured type system, including `Int`, `Float`, `Double`, `Boolean`, `DateTime`, `Decimal`, and `String`.

**Example of `schema.prisma` for MongoDB:**

```typescript
datasource db {
  provider = "mongodb"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id    String @id @default(auto()) @map("_id") @db.ObjectId
  name  String
  email String @unique
  posts Post[]
}

model Post {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  title     String
  content   String
  published Boolean  @default(false)
  authorId  String   @db.ObjectId
  author    User?    @relation(fields: [authorId], references: [id])
}
```

**Example of `schema.prisma` for MySQL:**

```typescript
datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id    Int    @id @default(autoincrement()) // Integer auto-increment primary key
  name  String
  email String @unique
  posts Post[]
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
}
```

#### Summary of Differences <a href="#summary-of-differences" id="summary-of-differences"></a>

| Feature                      | MongoDB (`schema.prisma`)                                      | MySQL (`schema.prisma`)                                  |
| ---------------------------- | -------------------------------------------------------------- | -------------------------------------------------------- |
| **Datasource Provider**      | `provider = "mongodb"`                                         | `provider = "mysql"`                                     |
| **Primary Key Definition**   | `id String @id @default(auto()) @map("_id") @db.ObjectId`      | `id Int @id @default(autoincrement())`                   |
| **Data Types**               | Supports `String`, `Int`, `Float`, `Boolean`, `DateTime`, etc. | More structured, includes `Int`, `Float`, `Double`, etc. |
| **Foreign Key Relationship** | Foreign keys defined with `@db.ObjectId`                       | Foreign keys with `Int` or other standard SQL types      |
| **Autoincrement ID**         | Not applicable (`@default(auto())`)                            | `@default(autoincrement())`                              |
| **Unique Identifier**        | `@unique` for unique fields                                    | `@unique` for unique fields                              |

#### Conclusion <a href="#conclusion" id="conclusion"></a>

The main differences in the `schema.prisma` file between MongoDB and MySQL revolve around how primary keys are handled, the differences in data types, and the way relationships and fields are mapped to the database. When switching from MongoDB to MySQL, these differences must be adjusted to accommodate the underlying database system's constraints and data modeling.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://javascriptuse.gitbook.io/javascript/advanced/prisma-crud-full/migrate-mongodb-to-mysql-using-prism-key-differences-ok.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.