Relation many record MySQL

How to create many record using forEach in array and relrelation it to parent table in MySQL like Prisma

await ctx.prisma.post.create({
        data: {
          content: input.content,
          imageUrl: input.imageUrl,
          authorId: userId,
          hashtags: {
            connectOrCreate: input.tags?.map((tag) => ({
              where: { name: tag },
              create: { name: tag },
            })),
          },
        },
      })

await ctx.prisma.post.create({
        data: {
          content: input.content,
          imageUrl: input.imageUrl,
          authorId: userId,
          hashtags: {
            connectOrCreate: input.tags?.map((tag) => ({
              where: { name: tag },
              create: { name: tag },
            })),
          },
        },
      })

11 Replies

Mykhailo•12mo ago

Hello, @Kai Revona! As for now, there is no create method in relational queries, so you have to use core queries such as insert().values() for posts and hashtags with your logic

Kai RevonaOP•12mo ago

Please give me an example to help me understand better

Angelelz•12mo ago

Prisma spoiled devs too much IMO. The usual way to do this, is starting a transaction, insert the post and receive the post id, then insert the tags with the post id.

Mykhailo•12mo ago

It could look something like this:

  const tags = ['TestTag1', 'TestTag2', 'TestTag3']; // input.tags

  const response = await db.transaction(async (tx) => {
    const [{ postId }] = await tx
      .insert(posts)
      .values({
        content: 'TestContent',
        imageUrl: 'TestImageUrl',
        authorId: 'TestUserId',
      })
      .returning({
        postId: posts.id,
      });

    let hashtagId: number;

    for (const tag of tags) {
      const [hashtag] = await tx.select().from(hashtags).where(eq(hashtags.name, tag));
      if (!hashtag) {
        const [createdHashtag] = await tx
          .insert(hashtags)
          .values({
            name: tag,
          })
          .returning({
            id: hashtags.id,
          });

        hashtagId = createdHashtag.id;
      } else {
        hashtagId = hashtag.id;
      }

      await tx
        .insert(postHashtags)
        .values({
          postId,
          hashtagId,
        })
        .onConflictDoNothing();
    }

    return tx
      .select()
      .from(posts)
      .where(eq(posts.id, postId))
      .leftJoin(postHashtags, eq(postHashtags.postId, postId))
      .leftJoin(hashtags, eq(hashtags.id, postHashtags.hashtagId));
  });

  const tags = ['TestTag1', 'TestTag2', 'TestTag3']; // input.tags

  const response = await db.transaction(async (tx) => {
    const [{ postId }] = await tx
      .insert(posts)
      .values({
        content: 'TestContent',
        imageUrl: 'TestImageUrl',
        authorId: 'TestUserId',
      })
      .returning({
        postId: posts.id,
      });

    let hashtagId: number;

    for (const tag of tags) {
      const [hashtag] = await tx.select().from(hashtags).where(eq(hashtags.name, tag));
      if (!hashtag) {
        const [createdHashtag] = await tx
          .insert(hashtags)
          .values({
            name: tag,
          })
          .returning({
            id: hashtags.id,
          });

        hashtagId = createdHashtag.id;
      } else {
        hashtagId = hashtag.id;
      }

      await tx
        .insert(postHashtags)
        .values({
          postId,
          hashtagId,
        })
        .onConflictDoNothing();
    }

    return tx
      .select()
      .from(posts)
      .where(eq(posts.id, postId))
      .leftJoin(postHashtags, eq(postHashtags.postId, postId))
      .leftJoin(hashtags, eq(hashtags.id, postHashtags.hashtagId));
  });

Angelelz•12mo ago

This is technically correct. Although I would change the for loop for an upsert type of insert with onConflictDoUpdate() or a promise.allSettled() to avoid the blocking behavior you'll have here sending one tag after the other

Mykhailo•12mo ago

ye, super, thanks

Kai RevonaOP•12mo ago

Can you update your code with onConflictDoUpdate() or promise.allSettled() Please help me because I am junior

Mykhailo•12mo ago

@Kai Revona

 const tags = ['TestTag1', 'TestTag2', 'TestTag3']; // input.tags

  const response = await db.transaction(async (tx) => {
    const [{ postId }] = await tx
      .insert(posts)
      .values({
        content: 'TestContent',
        imageUrl: 'TestImageUrl',
        authorId: 'TestUserId',
      })
      .returning({
        postId: posts.id,
      });

    const hashtagPromises = tags.map(async (tag) => {
      const [hashtag] = await tx
        .insert(hashtags)
        .values({ name: tag })
        .onConflictDoUpdate({
          target: [hashtags.name],
          set: { name: tag },
        })
        .returning({
          id: hashtags.id,
        });

      return tx
        .insert(postHashtags)
        .values({
          postId,
          hashtagId: hashtag.id,
        })
        .onConflictDoNothing();
    });

    await Promise.allSettled(hashtagPromises);

    return tx
      .select()
      .from(posts)
      .where(eq(posts.id, postId))
      .leftJoin(postHashtags, eq(postHashtags.postId, postId))
      .leftJoin(hashtags, eq(hashtags.id, postHashtags.hashtagId));
  });

 const tags = ['TestTag1', 'TestTag2', 'TestTag3']; // input.tags

  const response = await db.transaction(async (tx) => {
    const [{ postId }] = await tx
      .insert(posts)
      .values({
        content: 'TestContent',
        imageUrl: 'TestImageUrl',
        authorId: 'TestUserId',
      })
      .returning({
        postId: posts.id,
      });

    const hashtagPromises = tags.map(async (tag) => {
      const [hashtag] = await tx
        .insert(hashtags)
        .values({ name: tag })
        .onConflictDoUpdate({
          target: [hashtags.name],
          set: { name: tag },
        })
        .returning({
          id: hashtags.id,
        });

      return tx
        .insert(postHashtags)
        .values({
          postId,
          hashtagId: hashtag.id,
        })
        .onConflictDoNothing();
    });

    await Promise.allSettled(hashtagPromises);

    return tx
      .select()
      .from(posts)
      .where(eq(posts.id, postId))
      .leftJoin(postHashtags, eq(postHashtags.postId, postId))
      .leftJoin(hashtags, eq(hashtags.id, postHashtags.hashtagId));
  });

Kai RevonaOP•12mo ago

Thank you Schema

import {
  int,
  mysqlTable,
  primaryKey,
  serial,
  timestamp,
  varchar,
} from "drizzle-orm/mysql-core"

export const hashtags = mysqlTable("hashtags", {
  id: serial("id").primaryKey().autoincrement(),
  name: varchar("name", { length: 191 }).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").onUpdateNow(),
})
export const hashtagsToPosts = mysqlTable(
  "hashtags_to_posts",
  {
    hashtagId: int("hashtag_id").notNull(),
    postId: int("post_id").notNull(),
  },
  (t) => {
    return {
      pk: primaryKey({
        columns: [t.hashtagId, t.postId],
      }),
    }
  }
)

import {
  int,
  mysqlTable,
  primaryKey,
  serial,
  timestamp,
  varchar,
} from "drizzle-orm/mysql-core"

export const hashtags = mysqlTable("hashtags", {
  id: serial("id").primaryKey().autoincrement(),
  name: varchar("name", { length: 191 }).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").onUpdateNow(),
})
export const hashtagsToPosts = mysqlTable(
  "hashtags_to_posts",
  {
    hashtagId: int("hashtag_id").notNull(),
    postId: int("post_id").notNull(),
  },
  (t) => {
    return {
      pk: primaryKey({
        columns: [t.hashtagId, t.postId],
      }),
    }
  }
)

And relations

export const hashtagsRelations = relations(hashtags, ({ many }) => ({
  hashtagsToPosts: many(hashtagsToPosts),
}))
export const hashtagsToPostsRelations = relations(
  hashtagsToPosts,
  ({ one }) => ({
    posts: one(posts, {
      fields: [hashtagsToPosts.postId],
      references: [posts.id],
    }),
    hashtags: one(hashtags, {
      fields: [hashtagsToPosts.hashtagId],
      references: [hashtags.id],
    }),
  })
)

export const hashtagsRelations = relations(hashtags, ({ many }) => ({
  hashtagsToPosts: many(hashtagsToPosts),
}))
export const hashtagsToPostsRelations = relations(
  hashtagsToPosts,
  ({ one }) => ({
    posts: one(posts, {
      fields: [hashtagsToPosts.postId],
      references: [posts.id],
    }),
    hashtags: one(hashtags, {
      fields: [hashtagsToPosts.hashtagId],
      references: [hashtags.id],
    }),
  })
)

Function

await db.transaction(async (tx) => {
    const insertedPost = await tx.insert(posts).values(newPost)
    const hashtagPromises = post?.tags?.map(async (tag) => {
      const insertedHashtags = await tx
      .insert(hashtags)
      .values({ name: tag })
      .onDuplicateKeyUpdate({
        set: { name: tag },
      })
      const hashtagId = insertedHashtags.insertId //string
      return tx
        .insert(hashtagsToPosts)
        .values({
          hashtagId //ts-error here,
          postId: 0,
        })
    })
  })

await db.transaction(async (tx) => {
    const insertedPost = await tx.insert(posts).values(newPost)
    const hashtagPromises = post?.tags?.map(async (tag) => {
      const insertedHashtags = await tx
      .insert(hashtags)
      .values({ name: tag })
      .onDuplicateKeyUpdate({
        set: { name: tag },
      })
      const hashtagId = insertedHashtags.insertId //string
      return tx
        .insert(hashtagsToPosts)
        .values({
          hashtagId //ts-error here,
          postId: 0,
        })
    })
  })

hashtagId return string and cannot able to create hashtagId. I have to use insertId because MySQL doesn't support returning. Please help me to fix this error

Mykhailo•12mo ago

@Kai Revona if you don't use relational queries you don't need relation tables. Then you have to update your schemas code. schema.ts

import { bigint, mysqlTable, primaryKey, serial, timestamp, varchar } from 'drizzle-orm/mysql-core';

export const posts = mysqlTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 191 }).notNull(),
});

export const hashtags = mysqlTable('hashtags', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 191 }).notNull().unique(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').onUpdateNow(),
});

export const hashtagsToPosts = mysqlTable(
  'hashtags_to_posts',
  {
    hashtagId: bigint('hashtag_id', { mode: 'number', unsigned: true })
      .notNull()
      .references(() => hashtags.id),
    postId: bigint('post_id', { mode: 'number', unsigned: true })
      .notNull()
      .references(() => posts.id),
  },
  (t) => {
    return {
      pk: primaryKey({
        columns: [t.hashtagId, t.postId],
      }),
    };
  },
);

import { bigint, mysqlTable, primaryKey, serial, timestamp, varchar } from 'drizzle-orm/mysql-core';

export const posts = mysqlTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 191 }).notNull(),
});

export const hashtags = mysqlTable('hashtags', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 191 }).notNull().unique(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').onUpdateNow(),
});

export const hashtagsToPosts = mysqlTable(
  'hashtags_to_posts',
  {
    hashtagId: bigint('hashtag_id', { mode: 'number', unsigned: true })
      .notNull()
      .references(() => hashtags.id),
    postId: bigint('post_id', { mode: 'number', unsigned: true })
      .notNull()
      .references(() => posts.id),
  },
  (t) => {
    return {
      pk: primaryKey({
        columns: [t.hashtagId, t.postId],
      }),
    };
  },
);

your function

 const input = {
    tags: ['tag1', 'tag2', 'tag3'],
  };

  const response = await db.transaction(async (tx) => {
    const [post] = await tx.insert(posts).values({
      title: 'test_title2',
    });

    const hashtagPromises = input.tags.map(async (tag) => {
      const [hashtag] = await tx
        .insert(hashtags)
        .values({ name: tag })
        .onDuplicateKeyUpdate({
          set: { name: tag },
        });

      return tx
        .insert(hashtagsToPosts)
        .values({
          hashtagId: hashtag.insertId,
          postId: post.insertId,
        })
        .onDuplicateKeyUpdate({
          set: {
            hashtagId: sql`hashtag_id`,
            postId: sql`post_id`,
          },
        });
    });

    await Promise.allSettled(hashtagPromises);

    return tx
      .select()
      .from(posts)
      .where(eq(posts.id, post.insertId))
      .leftJoin(hashtagsToPosts, eq(hashtagsToPosts.postId, post.insertId))
      .leftJoin(hashtags, eq(hashtags.id, hashtagsToPosts.hashtagId));
  });

 const input = {
    tags: ['tag1', 'tag2', 'tag3'],
  };

  const response = await db.transaction(async (tx) => {
    const [post] = await tx.insert(posts).values({
      title: 'test_title2',
    });

    const hashtagPromises = input.tags.map(async (tag) => {
      const [hashtag] = await tx
        .insert(hashtags)
        .values({ name: tag })
        .onDuplicateKeyUpdate({
          set: { name: tag },
        });

      return tx
        .insert(hashtagsToPosts)
        .values({
          hashtagId: hashtag.insertId,
          postId: post.insertId,
        })
        .onDuplicateKeyUpdate({
          set: {
            hashtagId: sql`hashtag_id`,
            postId: sql`post_id`,
          },
        });
    });

    await Promise.allSettled(hashtagPromises);

    return tx
      .select()
      .from(posts)
      .where(eq(posts.id, post.insertId))
      .leftJoin(hashtagsToPosts, eq(hashtagsToPosts.postId, post.insertId))
      .leftJoin(hashtags, eq(hashtags.id, hashtagsToPosts.hashtagId));
  });

Here there are docs, where you can find useful information related to this code: 1. https://orm.drizzle.team/docs/column-types/mysql#serial 2. https://orm.drizzle.team/docs/insert#on-duplicate-key-update 3. https://orm.drizzle.team/docs/joins

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Kai RevonaOP•12mo ago

Ok, i will test them soon and reply you

Gaming

Programming

Relation many record MySQL