DT
Drizzle Team•9mo ago
charliearlie

Unable to infer relationship between tables

I wanted to try out Turso with Drizzle and am feeling stupid here but I am following the docs and I just cannot get this to work My set up 
import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";

const turso = createClient({
  url: process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN,
});

export const db = drizzle(turso);
import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";

const turso = createClient({
  url: process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN,
});

export const db = drizzle(turso);
My schema 
import { relations, sql } from "drizzle-orm";
import { text, integer, sqliteTable } from "drizzle-orm/sqlite-core";

export const albums = sqliteTable("albums", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  genre: text("genre"),
  image: text("image"),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .default(sql`(unixepoch())`),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .notNull()
    .default(sql`(unixepoch())`),
});

export const artists = sqliteTable("artists", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  bio: text("bio"),
  image: text("image"),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .default(sql`(unixepoch())`),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .notNull()
    .default(sql`(unixepoch())`),
});

export const albumRelations = relations(albums, ({ many }) => ({
  artists: many(artists),
}));

export const artistRelations = relations(artists, ({ many }) => ({
  albums: many(albums),
}));
import { relations, sql } from "drizzle-orm";
import { text, integer, sqliteTable } from "drizzle-orm/sqlite-core";

export const albums = sqliteTable("albums", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  genre: text("genre"),
  image: text("image"),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .default(sql`(unixepoch())`),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .notNull()
    .default(sql`(unixepoch())`),
});

export const artists = sqliteTable("artists", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  bio: text("bio"),
  image: text("image"),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .default(sql`(unixepoch())`),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .notNull()
    .default(sql`(unixepoch())`),
});

export const albumRelations = relations(albums, ({ many }) => ({
  artists: many(artists),
}));

export const artistRelations = relations(artists, ({ many }) => ({
  albums: many(albums),
}));
Error: 
Error: There is not enough information to infer relation "__public__.albums.artists"
Error: There is not enough information to infer relation "__public__.albums.artists"
What am I doing wrong here? I have the option of creating a join/through table but the docs suggest that isn't needed. So how can I correctly infer these relationships?
10 Replies
vehler
vehler•9mo ago
i have the same issue, i came by to see if there were answers lol,
Sillvva
Sillvva•9mo ago
You have not created any way to link those two tables together. The many-to-many example in the Drizzle docs has an additional table that maps the two entities to each other. See below: 
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
});
export const usersRelations = relations(users, ({ many }) => ({
  usersToGroups: many(usersToGroups),
}));
export const groups = pgTable('groups', {
  id: serial('id').primaryKey(),
  name: text('name'),
});
export const groupsRelations = relations(groups, ({ many }) => ({
  usersToGroups: many(usersToGroups),
}));
export const usersToGroups = pgTable('users_to_groups', {
    userId: integer('user_id').notNull().references(() => users.id),
    groupId: integer('group_id').notNull().references(() => groups.id),
  }, (t) => ({
    pk: primaryKey(t.userId, t.groupId),
  }),
);
export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
  group: one(groups, {
    fields: [usersToGroups.groupId],
    references: [groups.id],
  }),
  user: one(users, {
    fields: [usersToGroups.userId],
    references: [users.id],
  }),
}));
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
});
export const usersRelations = relations(users, ({ many }) => ({
  usersToGroups: many(usersToGroups),
}));
export const groups = pgTable('groups', {
  id: serial('id').primaryKey(),
  name: text('name'),
});
export const groupsRelations = relations(groups, ({ many }) => ({
  usersToGroups: many(usersToGroups),
}));
export const usersToGroups = pgTable('users_to_groups', {
    userId: integer('user_id').notNull().references(() => users.id),
    groupId: integer('group_id').notNull().references(() => groups.id),
  }, (t) => ({
    pk: primaryKey(t.userId, t.groupId),
  }),
);
export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
  group: one(groups, {
    fields: [usersToGroups.groupId],
    references: [groups.id],
  }),
  user: one(users, {
    fields: [usersToGroups.userId],
    references: [users.id],
  }),
}));
Drizzle ORM - Query
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
Sillvva
Sillvva•9mo ago
In your case: 
export const albums = sqliteTable("albums", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  ...
});

export const albumRelations = relations(albums, ({ many }) => ({
-   artists: many(artists),
+   artistsToAlbums: many(artistsToAlbums),
}));

export const artists = sqliteTable("artists", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  ...
});

export const artistRelations = relations(artists, ({ many }) => ({
-   albums: many(albums),
+   artistsToAlbums: many(artistsToAlbums),
}));

+ export const artistsToAlbums = sqliteTable('artists_to_albums', {
+     artistId: integer('artist_id' { mode: "number" }).notNull().references(() => artists.id),
+     albumId: integer('album_id' { mode: "number" }).notNull().references(() => albums.id),
+   }, (t) => ({
+     pk: primaryKey(t.artistId, t.albumId),
+   }),
+ );
+ export const artistsToAlbumsRelations = relations(artistsToAlbums, ({ one }) => ({
+   artist: one(artists, {
+     fields: [artistsToAlbums.groupId],
+     references: [artists.id],
+   }),
+   album: one(albums, {
+     fields: [artistsToAlbums.userId],
+     references: [albums.id],
+   }),
+ }));
export const albums = sqliteTable("albums", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  ...
});

export const albumRelations = relations(albums, ({ many }) => ({
-   artists: many(artists),
+   artistsToAlbums: many(artistsToAlbums),
}));

export const artists = sqliteTable("artists", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  ...
});

export const artistRelations = relations(artists, ({ many }) => ({
-   albums: many(albums),
+   artistsToAlbums: many(artistsToAlbums),
}));

+ export const artistsToAlbums = sqliteTable('artists_to_albums', {
+     artistId: integer('artist_id' { mode: "number" }).notNull().references(() => artists.id),
+     albumId: integer('album_id' { mode: "number" }).notNull().references(() => albums.id),
+   }, (t) => ({
+     pk: primaryKey(t.artistId, t.albumId),
+   }),
+ );
+ export const artistsToAlbumsRelations = relations(artistsToAlbums, ({ one }) => ({
+   artist: one(artists, {
+     fields: [artistsToAlbums.groupId],
+     references: [artists.id],
+   }),
+   album: one(albums, {
+     fields: [artistsToAlbums.userId],
+     references: [albums.id],
+   }),
+ }));
vehler
vehler•9mo ago
For my case, i've re-worked the relations like you suggested here and I'm getting the relations from the many to many (groupId, userId from the example) as expected but i dont know how to get the list of groups from the relationship when i query with "findMany" and
{
  with: {
    usersToGroups: true
  }
}
{
  with: {
    usersToGroups: true
  }
}
Sillvva
Sillvva•9mo ago
const users = db.query.users.findMany({
  with: {
    usersToGroups: {
      with: {
        group: true
      }
    }
  }
});

/*
const users: {
    id: number;
    name: string | null;
    usersToGroups: {
        userId: number;
        groupId: number;
        group: {
            id: number;
            name: string | null;
        };
    }[];
}[]
*/
const users = db.query.users.findMany({
  with: {
    usersToGroups: {
      with: {
        group: true
      }
    }
  }
});

/*
const users: {
    id: number;
    name: string | null;
    usersToGroups: {
        userId: number;
        groupId: number;
        group: {
            id: number;
            name: string | null;
        };
    }[];
}[]
*/
vehler
vehler•9mo ago
charliearlie
charliearlieOP•9mo ago
I had done this before but when inserting, I couldn't see the relation, so just assumed I was doing something wrong 😂 . How would i insert something into the albums table and link the artist?
Sillvva
Sillvva•9mo ago
When you have an entry in your albums and artists tables you need to link, you'd insert a record in the artistsToAlbums table with the id for each of them.
charliearlie
charliearlieOP•9mo ago
Yeah, that felt strange to me coming from prisma where I can just create an album and connect to an artist. I guess it was coming out of that mindset. Thanks for your help.
Sillvva
Sillvva•9mo ago
Yeah, that's just how many-to-many relationships work. You use an intermediary table to form the relationships. For one-to-many, for example one artist to many albums, you'd add the artistId column to the albums table.
Want results from more Discord servers?
Add your server