So I have this query:
START TRANSACTION;
CREATE TABLE `users` (
`id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`registration_date` timestamp NOT NULL,
`address` varchar(255),
`description` varchar(1000),
`studio` varchar(255),
`email` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`is_looking_for_work` boolean,
`last_active` timestamp,
`last_login` timestamp,
`profile_photo` varchar(2000)
);
CREATE TABLE `availability` (
`id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
`start_time` timestamp NOT NULL,
`end_time` timestamp NOT NULL,
`description` varchar(500),
`user_id` int NOT NULL
);
CREATE TABLE `chats` (
`id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
`initiator_id` int NOT NULL,
`invited_id` int NOT NULL
);
CREATE TABLE `messages` (
`id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
`chat_id` int NOT NULL,
`author_id` int NOT NULL,
`content` varchar(1000) NOT NULL,
`creation_date` timestamp NOT NULL
);
CREATE TABLE `offers` (
`id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
`author_id` int,
`colors` ENUM ('white', 'red'),
`project_image` varchar(10000),
`finished_image` varchar(10000),
`price` int NOT NULL,
`description` varchar(500),
`time_needed` int NOT NULL,
`is_available` boolean NOT NULL,
`is_in_portfolio` boolean
);
CREATE TABLE `meetings` (
`id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
`provider` int NOT NULL,
`client` int NOT NULL,
`date` timestamp NOT NULL,
`duration` int NOT NULL,
`description` varchar(1000) NOT NULL,
`offer_id` int NOT NULL
);
CREATE TABLE `tags` (
`id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
`tag` varchar(255) NOT NULL,
`description` varchar(1000),
`is_style` boolean NOT NULL
);
CREATE TABLE `tags_to_offers` (
`id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
`offer_id` int NOT NULL,
`tag_id` int NOT NULL
);
CREATE TABLE `tags_to_users` (
`id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL,
`tag_id` int NOT NULL
);
ALTER TABLE `offers` ADD FOREIGN KEY (`author_id`) REFERENCES `users` (`id`);
ALTER TABLE `availability` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);
ALTER TABLE `chats` ADD FOREIGN KEY (`initiator_id`) REFERENCES `users` (`id`);
ALTER TABLE `chats` ADD FOREIGN KEY (`invited_id`) REFERENCES `users` (`id`);
ALTER TABLE `messages` ADD FOREIGN KEY (`chat_id`) REFERENCES `chats` (`id`);
ALTER TABLE `messages` ADD FOREIGN KEY (`author_id`) REFERENCES `users` (`id`);
ALTER TABLE `tags_to_offers` ADD FOREIGN KEY (`offer_id`) REFERENCES `offers` (`id`);
ALTER TABLE `tags_to_offers` ADD FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`);
ALTER TABLE `tags_to_users` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);
ALTER TABLE `tags_to_users` ADD FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`);
ALTER TABLE `meetings` ADD FOREIGN KEY (`provider`) REFERENCES `users` (`id`);
ALTER TABLE `meetings` ADD FOREIGN KEY (`client`) REFERENCES `users` (`id`);
ALTER TABLE `meetings` ADD FOREIGN KEY (`offer_id`) REFERENCES `offers` (`id`);
COMMIT;
It causes prisma to create this schema
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}
model availability {
id Int @id @default(autoincrement())
start_time DateTime
end_time DateTime
description String?
user_id Int
users users @relation(fields: [user_id], references: [id])
@@index([user_id], name: "user_id")
}
model chats {
id Int @id @default(autoincrement())
initiator_id Int
invited_id Int
users_chats_initiator_idTousers users @relation("chats_initiator_idTousers", fields: [initiator_id], references: [id])
users_chats_invited_idTousers users @relation("chats_invited_idTousers", fields: [invited_id], references: [id])
messages messages[]
@@index([initiator_id], name: "initiator_id")
@@index([invited_id], name: "invited_id")
}
model meetings {
id Int @id @default(autoincrement())
provider Int
client Int
date DateTime
duration Int
description String
offer_id Int
users_meetings_clientTousers users @relation("meetings_clientTousers", fields: [client], references: [id])
offers offers @relation(fields: [offer_id], references: [id])
users_meetings_providerTousers users @relation("meetings_providerTousers", fields: [provider], references: [id])
@@index([client], name: "client")
@@index([offer_id], name: "offer_id")
@@index([provider], name: "provider")
}
model messages {
id Int @id @default(autoincrement())
chat_id Int
author_id Int
content String
creation_date DateTime
users users @relation(fields: [author_id], references: [id])
chats chats @relation(fields: [chat_id], references: [id])
@@index([author_id], name: "author_id")
@@index([chat_id], name: "chat_id")
}
model offers {
id Int @id @default(autoincrement())
author_id Int?
colors offers_colors?
project_image String?
finished_image String?
price Int
description String?
time_needed Int
is_available Boolean
is_in_portfolio Boolean?
users users? @relation(fields: [author_id], references: [id])
meetings meetings[]
tags_to_offers tags_to_offers[]
@@index([author_id], name: "author_id")
}
model tags {
id Int @id @default(autoincrement())
tag String
description String?
is_style Boolean
tags_to_offers tags_to_offers[]
tags_to_users tags_to_users[]
}
model tags_to_offers {
id Int @id @default(autoincrement())
offer_id Int
tag_id Int
offers offers @relation(fields: [offer_id], references: [id])
tags tags @relation(fields: [tag_id], references: [id])
@@index([offer_id], name: "offer_id")
@@index([tag_id], name: "tag_id")
}
model tags_to_users {
id Int @id @default(autoincrement())
user_id Int
tag_id Int
tags tags @relation(fields: [tag_id], references: [id])
users users @relation(fields: [user_id], references: [id])
@@index([tag_id], name: "tag_id")
@@index([user_id], name: "user_id")
}
model users {
id Int @id @default(autoincrement())
name String
registration_date DateTime
address String?
description String?
studio String?
email String
password String
is_looking_for_work Boolean?
last_active DateTime?
last_login DateTime?
profile_photo String?
availability availability[]
chats_chats_initiator_idTousers chats[] @relation("chats_initiator_idTousers")
chats_chats_invited_idTousers chats[] @relation("chats_invited_idTousers")
meetings_meetings_clientTousers meetings[] @relation("meetings_clientTousers")
meetings_meetings_providerTousers meetings[] @relation("meetings_providerTousers")
messages messages[]
offers offers[]
tags_to_users tags_to_users[]
}
enum offers_colors {
white
red
}
Which, is not what I was going for. I wanted something like this (GraphQL types):
type User {
id: Int!
name: String!
registration_date: String!
address: String
description: String
studio: String
email: String!
password: String!
is_looking_for_work: Boolean
last_active: String
last_login: String
profile_photo: String
availability: [Availability]
chats: [Chat]
meetings: [Meeting]
tags: [Tag]
}
type Offer {
id: Int!
author_id: Int!
colors: String
project_image: String!
finished_image: String
price: Int!
description: String
time_needed: Int!
is_available: Boolean!
is_in_portfolio: Boolean
tags: [Tag]
}
type Availability {
id: Int!
start_time: String!
end_time: String!
description: String
user_id: Int!
}
type Chat {
id: Int!
initiator_id: Int!
invited_id: Int!
messages: [Message]
}
type Message {
id: Int!
chat_id: Int!
author_id: Int!
content: Int!
creation_date: Int!
}
type Meeting {
id: Int!
providerId: Int!
clientId: Int!
date: String!
duration: Int!
description: String!
offer_id: Int!
}
type Tag {
id: Int!
tag: String!
description: String
is_style: Boolean!
}
For example let's look at user for simplicity:
SQL
CREATE TABLE `users` (
`id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`registration_date` timestamp NOT NULL,
`address` varchar(255),
`description` varchar(1000),
`studio` varchar(255),
`email` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`is_looking_for_work` boolean,
`last_active` timestamp,
`last_login` timestamp,
`profile_photo` varchar(2000)
);
Prisma
model users {
id Int @id @default(autoincrement())
name String
registration_date DateTime
address String?
description String?
studio String?
email String
password String
is_looking_for_work Boolean?
last_active DateTime?
last_login DateTime?
profile_photo String?
availability availability[]
chats_chats_initiator_idTousers chats[] @relation("chats_initiator_idTousers")
chats_chats_invited_idTousers chats[] @relation("chats_invited_idTousers")
meetings_meetings_clientTousers meetings[] @relation("meetings_clientTousers")
meetings_meetings_providerTousers meetings[] @relation("meetings_providerTousers")
// the 4 above: Why are they not in objects?
messages messages[]
offers offers[]
tags_to_users tags_to_users[]
}
What I was hoping for
type User {
id: Int!
name: String!
registration_date: String!
address: String
description: String
studio: String
email: String!
password: String!
is_looking_for_work: Boolean
last_active: String
last_login: String
profile_photo: String
availability: [Availability]
chats: [Chat]
meetings: [Meeting]
// meeting and chats. Why are they not like this?
tags: [Tag]
}
And as you can see
chats_chats_initiator_idTousers chats[] @relation("chats_initiator_idTousers")
chats_chats_invited_idTousers chats[] @relation("chats_invited_idTousers")
meetings_meetings_clientTousers meetings[] @relation("meetings_clientTousers")
meetings_meetings_providerTousers meetings[] @relation("meetings_providerTousers")
This is how prisma made the chats and meethings. Also it added objects. But why did it add the 4 above?
What did I mess up in my schema? Or can I create a db schema from my graphql types?