Support implicit many-to-many relations

[Aulos]

It seems Prisma creates a relation table to connect two tables, but prisma-kisely does not create a type for it. I am not sure how the relation table is generated, but it seems to have _{Tab1}On{Tab2} name and A and B (sic!) columns.

[janpio]

Here are the conventions for the table (name, columns, etc): https://www.prisma.io/docs/concepts/components/prisma-schema/relations/many-to-many-relations#conventions-for-relation-tables-in-implicit-m-n-relations

[valtyr]

prisma-kysely already has support for this. Are you having trouble getting it to work? If so would you be willing to share your schema so I can investigate?

Here's a link to the code and the tests for the code.

[valtyr]

Here's an example:

Schema:

datasource db {
    provider = "sqlite"
    url      = "file:./dev.db"
}

generator kysely {
    provider  = "node ./dist/bin.js"
    camelCase = true
}

model User {
    id                Int      @id @default(autoincrement())
    publicId          String
    email             String   @unique
    password          String?
    firstName         String
    lastName          String
    profilePictureUrl String?
    createdAt         DateTime @default(now())
    updatedAt         DateTime @updatedAt

    groups Group[]
}

model Group {
    id   Int    @id @default(autoincrement())
    name String

    users User[]
}

Output:

import type { ColumnType } from "kysely";

export type Generated<T> = T extends ColumnType<infer S, infer I, infer U>
    ? ColumnType<S, I | undefined, U>
    : ColumnType<T, T | undefined, T>;

export type Timestamp = ColumnType<Date, Date | string, Date | string>;

export type Group = {
    id: Generated<number>;
    name: string;
};

export type GroupToUser = {
    A: number;
    B: number;
};

export type User = {
    id: Generated<number>;
    publicId: string;
    email: string;
    password: string | null;
    firstName: string;
    lastName: string;
    profilePictureUrl: string | null;
    createdAt: Generated<Timestamp>;
    updatedAt: Timestamp;
};

export type DB = {
    Group: Group;
    GroupToUser: GroupToUser;
    User: User;
};

[valtyr]

Oh wait the name of the table is missing an underscore! My bad. I'll fix this in a PR tomorrow.

[Aulos]

I forgot to mention - I am using 1.0.9 version.

The (simplified) schema:

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

generator kysely {
  provider = "prisma-kysely"
}

model MenuItem {
  id           String     @id @default(cuid())
  category Category[]
}

model Category {
  id           String     @id @default(cuid())
  menuItems MenuItem[]
}

Output:

import type { ColumnType } from "kysely";
export type Generated<T> = T extends ColumnType<infer S, infer I, infer U>
  ? ColumnType<S, I | undefined, U>
  : ColumnType<T, T | undefined, T>;
export type Timestamp = ColumnType<Date, Date | string, Date | string>;
export type Category = {
  id: string;
};
export type MenuItem = {
  id: string;
};
export type DB = {
  Category: Category;
  MenuItem: MenuItem;
};

No _CategoryToMenuItem as you can see.

[Aulos]

Hm, that's interesting. When I tried to generate from the cloned repo it generated a relation table (w/o leading underscore, but still).

[Aulos]

I did some console logging and it seems field.relationToFields is an empty array, so the fields are being filtered out from generating an implicit relation table. I am not sure why though 🤷

[valtyr]

Oh, strange! I need to take a good look at this part of the codebase. It was hastily ripped from another library. I might not have adapted it properly. If you find the issue in the meantime though feel free to submit a PR (and add a test case so this doesn't happen again) 😄

[Aulos]

Seems to be some change from version 3.x to 4.x of Prisma. In my project, I use 4.x and it fails, but it works fine when using 3.x.

[valtyr]

Huh, must be some breaking change in Prisma. I'll look into this as soon as I get a chance.

[valtyr]

Turns out this was caused by a breaking change in Prisma's DMMF where the "relationToFields" entry on fields is now empty (nice catch @Aulos). Found a good reference in this issue: notiz-dev/prisma-dbml-generator#46.

I've created a fix for this in pr #28. It would be super helpful if you guys could try it out using snapshot 0.0.0-snapshot-20230515174340 and let me know if it fixes things.

@janpio is there some place where breaking changes to the DMMF format are announced, or should we treat this as an internal API?

Edit: Forgot to prefix the table names with an underscore. I've updated the snapshot version in this comment to match a new one that has correct table names.

[Aulos]

Unfortunately, I migrated to explicit many-to-many 😢

[valtyr]

Oh well, sorry for the delay. I think for projects that are only using Kysely explicit many to many relationships are probably better in most cases anyways. This is mainly meant to support people that are adding Kysely to a project that already heavily uses the Prisma JS client.

[sgrund14]

hey there @valtyr! thanks for your work on this. just updated to v1.4.0 and still not seeing the implicit many-to-many types being generated in the prisma-kysely output. it seems like having a one-to-many relationship in the same model as a many-to-many can mess with the generation

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

generator client {
  provider        = "prisma-kysely"
  previewFeatures = ["fullTextSearch", "jsonProtocol"]
}

model User {
  id            String    @id @default(dbgenerated("(uuid())"))
  // need to remove this to generate the TagToUpload relationship
  uploadedTags Tag[] @relation("createdBy")
}

model Upload {
  id          String     @id @default(dbgenerated("(uuid())"))
  title       String
  tags        Tag[]
}

model Tag {
  id      Int      @id @default(dbgenerated("(uuid())"))
  name    String   @unique
  userId  String?
  // remove this and the _TagToUpload type is generated
  createdBy User?    @relation("createdBy", fields: [userId], references: [id])
  uploads Upload[]
  @@index([userId])
}

if I keep the createdBy relationship in the Tag, this is the outputted types (no TagToUpload):

import type { ColumnType, Generated, GeneratedAlways } from 'kysely';
export type Timestamp = ColumnType<Date, Date | string, Date | string>;

export type Tag = {
    id: Generated<number>;
    name: string;
    userId: string | null;
};
export type Upload = {
    id: Generated<string>;
    title: string;
};
export type User = {
    id: Generated<string>;
};
export type DB = {
    Tag: Tag;
    Upload: Upload;
    User: User;
};

then if I remove it, I get this (with TagToUpload)

import type { ColumnType, Generated, GeneratedAlways } from 'kysely';
export type Timestamp = ColumnType<Date, Date | string, Date | string>;

export type Tag = {
    id: Generated<number>;
    name: string;
    userId: string | null;
};
export type TagToUpload = {
    A: number;
    B: string;
};
export type Upload = {
    id: Generated<string>;
    title: string;
};
export type User = {
    id: Generated<string>;
};
export type DB = {
    Tag: Tag;
    _TagToUpload: TagToUpload;
    Upload: Upload;
    User: User;
};

any thoughts?

[valtyr]

@sgrund14 Thank you for the detailed report! The release might have been a bit premature hmmm. I'll add more test cases later today and see if I can get this sorted.

[sgrund14]

thanks much 😄

[darasus]

I'm missing one-to-many types as well tried on both prisma 4.x and 5.x

[karrui]

Also missing many-to-many types on prisma v4.16.2 and prisma-kysely v1.6.0

[whygee-dev]

Missing many to many types on prisma 5.1.1 and prisma-kysely 1.6.0

[valtyr]

Hey there. For everyone that's been following this issue, I'm about to release version 1.7.0 that should fix the issue! Thanks for the patience and huuuuge thanks to @dextertanyj for submitting a pull request with the fix!