When i using in local db and localhost the migration is succuussfully worked. but when i deploy the server db. the migration is failed
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema
// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
// shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}
enum Role {
ADMIN
COACH
CLEINT
}
model User {
id Int @id @default(autoincrement())
email String
profile Profile?
role Role @default(CLEINT)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
underCoach Int?
coach User? @relation("CoachToUser", fields: [underCoach], references: [id])
clients User[] @relation("CoachToUser")
connectRequestsFrom ConnectRequest[] @relation("ConnectRequestFrom")
connectRequestsTo ConnectRequest[] @relation("ConnectRequestTo")
meal Meal[]
MealAssignment MealAssignment[]
// setByUser MealTime[] @relation("setBy")
// setToUser MealTime[] @relation("setTo")
WorkoutsAssignment WorkoutsAssignment[]
Workouts Workouts[]
}
model Profile {
id Int @id @default(autoincrement())
name String
dob String
gender Gender @default(MALE)
height Int?
weight Int?
sugar Int?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
userId Int @unique
user User @relation(fields: [userId], references: [id])
coachTypeId Int?
coach Coach? @relation(fields: [coachTypeId], references: [id])
}
enum Gender {
MALE
FEMALE
TRANSGENDER
}
model Coach {
id Int @id @default(autoincrement())
coachName String @unique
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
profile Profile[]
}
model ConnectRequest {
id Int @id @default(autoincrement())
fromId Int
toId Int
status RequestStatus @default(PENDING)
from User @relation("ConnectRequestFrom", fields: [fromId], references: [id])
to User @relation("ConnectRequestTo", fields: [toId], references: [id])
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
enum RequestStatus {
PENDING
ACCEPTED
REJECTED
}
model Meal {
id Int @id @unique @default(autoincrement())
name String
protiens Int
fats Int
carbs Int
mealType MealType
// mealPlan MealPlan[] @relation("meals")
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
userId Int
user User? @relation(fields: [userId], references: [id])
MealAssignment MealAssignment[]
}
model MealAssignment {
id Int @id
userId Int // Adding userId directly instead of using @relation
mealId Int // Adding mealId directly instead of using @relation
user User @relation(fields: [userId], references: [id])
meal Meal @relation(fields: [mealId], references: [id])
day Int // Day number for which the meal is assigned
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
enum MealType {
BREAKFAST
LUNCH
DINNER
SNACK
}
model MealTime {
id Int @id @unique @default(autoincrement())
setBy Int
setTo Int
whichMeal MealType
time String
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
model WorkoutsAssignment {
id Int @id @unique @default(autoincrement())
assigneddUserId Int
workoutId Int
user User @relation(fields: [assigneddUserId], references: [id])
workout Workouts @relation(fields: [workoutId], references: [id])
day Int
completed Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
model Workouts {
id Int @id @unique @default(autoincrement())
name String
vcLink String?
createdUserId Int
user User @relation(fields: [createdUserId], references: [id])
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
WorkoutsAssignment WorkoutsAssignment[]
}
i run the cmd of npx prisma migrate dev
the error shows this
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "dev-fitish-v2" at "***n.com:25060"
Error: P3014
Prisma Migrate could not create the shadow database. Please make sure the database user has permission to create databases. Read more about the shadow database (and workarounds) at https://pris.ly/d/migrate-shadow
Original error: Error code: P1010
User `fitish` was denied access on the database `dev-fitish-v2`
if i run the cmd of npx prisma db push
the error showsm like this..
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "dev-fitish-v2" at "***n.com:25060"
Error: REFERENCES command denied to user 'fitish'@'*.*.*.*' for table 'dev-fitish-v2.User'
0: sql_schema_connector::apply_migration::migration_step
with step=AddForeignKey { foreign_key_id: ForeignKeyId(0) }
at schema-engine/connectors/sql-schema-connector/src/apply_migration.rs:21
1: sql_schema_connector::apply_migration::apply_migration
at schema-engine/connectors/sql-schema-connector/src/apply_migration.rs:10
2: schema_core::state::SchemaPush
at schema-engine/core/src/state.rs:433
how to resolve this....
i want to migrate the schema model in my server db succussfully
Why did you comment out the shadow database setting?
The shadow database is a database that has the same schema/structure as your database. If you change the prisma.schema file and run the migrate command, it will compare your database with the shadow database, and then it will create a migration SQL file.
You need to create a shadow database in your database server with the name <your-db-name>-shadow, and remove the comment mark for the shadow database setting.
please run following 2 commands one by one.
npx prisma migrate dev --create-only
npx prisma migrate deploy
The first command will create an SQL file containing the SQL commands for migration, and the second command will apply that SQL file to your real database.