How to do inserts in many-to-many relations ?

I'm new to Drizzle and I don't really know if my tables relation is correct or not. Here's the schema:

export const musics = pgTable('musics', {
    id: serial('id').primaryKey(),
    url: text('url').notNull().unique(),
    number: integer('number').notNull(),
    title: text('title').notNull(),
    duration: integer('duration').notNull(),
    albumId: serial('album_id')
        .notNull()
        .references(() => albums.id, { onDelete: 'cascade' }),
});

export const musicsRelations = relations(musics, ({ one, many }) => ({
    album: one(albums, { fields: [musics.albumId], references: [albums.id] }),
    musicsToAuthors: many(musicsToAuthors),
}));

export const authors = pgTable('authors', {
    id: serial('id').primaryKey(),
    name: text('name').notNull().unique(),
});

export const authorsRelations = relations(authors, ({ many }) => ({
    musicsToAuthors: many(musicsToAuthors),
}));

export const musicsToAuthors = pgTable(
    'musics_to_authors',
    {
        musicId: integer('music_id')
            .notNull()
            .references(() => musics.id),
        authorId: integer('author_id')
            .notNull()
            .references(() => authors.id),
    },
    (t) => ({
        pk: primaryKey(t.musicId, t.authorId),
    }),
);

export const musicToAuthorRelations = relations(musicsToAuthors, ({ one }) => ({
    music: one(musics, {
        fields: [musicsToAuthors.musicId],
        references: [musics.id],
    }),
    author: one(authors, {
        fields: [musicsToAuthors.authorId],
        references: [authors.id],
    }),
}));

export const musics = pgTable('musics', {
    id: serial('id').primaryKey(),
    url: text('url').notNull().unique(),
    number: integer('number').notNull(),
    title: text('title').notNull(),
    duration: integer('duration').notNull(),
    albumId: serial('album_id')
        .notNull()
        .references(() => albums.id, { onDelete: 'cascade' }),
});

export const musicsRelations = relations(musics, ({ one, many }) => ({
    album: one(albums, { fields: [musics.albumId], references: [albums.id] }),
    musicsToAuthors: many(musicsToAuthors),
}));

export const authors = pgTable('authors', {
    id: serial('id').primaryKey(),
    name: text('name').notNull().unique(),
});

export const authorsRelations = relations(authors, ({ many }) => ({
    musicsToAuthors: many(musicsToAuthors),
}));

export const musicsToAuthors = pgTable(
    'musics_to_authors',
    {
        musicId: integer('music_id')
            .notNull()
            .references(() => musics.id),
        authorId: integer('author_id')
            .notNull()
            .references(() => authors.id),
    },
    (t) => ({
        pk: primaryKey(t.musicId, t.authorId),
    }),
);

export const musicToAuthorRelations = relations(musicsToAuthors, ({ one }) => ({
    music: one(musics, {
        fields: [musicsToAuthors.musicId],
        references: [musics.id],
    }),
    author: one(authors, {
        fields: [musicsToAuthors.authorId],
        references: [authors.id],
    }),
}));

I chose to use a many-to-many relation because a music can have multiple authors and an author can have multiple music. Is my schema correct ? Insert in comment

1 Reply

TiboOP•10mo ago

Here's how I handle the insert for now (it's not complete):

artists?.forEach(async (artist) => {
    const artistExists = await db.query.authors.findFirst({
        where: eq(authors.name, artist),
    });
    if (!artistExists) {
        const newAuthor = await db
            .insert(authors)
            .values({
                name: artist,
            })
            .returning({ authorId: authors.id });
        const authorId = newAuthor[0].authorId;
    }
});
const trackUrl = CLOUDFRONT_URL + filename;
await db.insert(musics).values({
    title: metadata.common.title,
    url: trackUrl,
    duration: Math.round(metadata.format?.duration ?? 0),
    number: metadata.common.track.no,
    albumId,
});

artists?.forEach(async (artist) => {
    const artistExists = await db.query.authors.findFirst({
        where: eq(authors.name, artist),
    });
    if (!artistExists) {
        const newAuthor = await db
            .insert(authors)
            .values({
                name: artist,
            })
            .returning({ authorId: authors.id });
        const authorId = newAuthor[0].authorId;
    }
});
const trackUrl = CLOUDFRONT_URL + filename;
await db.insert(musics).values({
    title: metadata.common.title,
    url: trackUrl,
    duration: Math.round(metadata.format?.duration ?? 0),
    number: metadata.common.track.no,
    albumId,
});

I'm using the music metadata. I'm currently looping over each artist and checking whether they exist. If they don't I'm creating them and I get their ID. This is where I'm stuck, as there are multiple tables for the relations I don't know in which one I should do the insert and if I'm using the right method. I guess that I need to get the author id and the music id and put them in one of the relation tables but I don't know if I should do that in the loop or outside and if there are risks that doing the insert in a loop could break it. Do I have the correct way of doing it or is there a better way ? How would you refactor my code ?

Gaming

Programming

How to do inserts in many-to-many relations ?