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•8mo ago
i have the same issue, i came by to see if there were answers lol,
Sillvva
Sillvva•8mo 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•8mo 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•8mo 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•8mo 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;
};
}[];
}[]
*/
charliearlie
charliearlieOP•8mo 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•8mo 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•8mo 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•8mo 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