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)
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])
}
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
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:
datasource db {
provider = "mongodb"
url = env("DATABASE_URL")
}
MySQL Configuration:
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:
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:
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
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
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.
