how to define many-to-many relationship in prisma/psql

user_6406489 0
Viewed 54

I'm struggling a bunch with some relatively simple Postgres that I was originally trying to define in Prisma but wasn't having much luck. It's a many to many relationship joined on a single field between two tables as such:

CREATE TABLE scheduleDate (
 schedule_day_number    int NOT NULL
, schedule_date date NOT NULL
,CONSTRAINT scheduledate_pkey PRIMARY KEY (schedule_date, schedule_day_number)
);

CREATE TABLE schoolDayBlock (
  start_time varchar(10) NOT NULL
,  end_time varchar(10)  NOT NULL
,school_block_day_number int  NOT NULL
, block_name     varchar(10) NOT NULL
,CONSTRAINT schooldayblock_pkey PRIMARY KEY (start_time, end_time, school_block_day_number, block_name )
);

I eventually want to be able to get all the possible combinations between these two tables with the day number fields being the link. Ideally, later down the line, I can query Prisma for a block name and get all of the dates and times where that block name shows up. Does anyone know how I could

  1. Write this up in a Prisma schema or
  2. Write this up in postgres.sql and use introspection to generate a Prisma schema that accomplishes this.
prisma many-to-many postgresql sql
1 Answers

Here's the prisma schema equivalent of your SQL query for creating two tables.

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

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

model scheduleDate {
  schedule_day_number Int
  schedule_date       DateTime

  @@id([schedule_day_number, schedule_date], name: "scheduledate_pkey")
}

model schoolDayBlock {
  start_time              DateTime
  end_time                DateTime
  school_block_day_number Int
  block_name              String

  @@id([start_time, end_time, school_block_day_number, block_name], name: "schooldayblock_pkey")
}
user_41540620
Related