Drizzle Team•9mo ago

Drizzle with MySQL - most optimal way to insert row and immediately retrieve inserted row

Hey, thanks for helping me out :) I'm currently using Drizzle in a serverless application with PlanetScale. Here is some code where I insert a row and immediately after retrieve it:

create: privateProcedure
  .mutation(async ({ ctx, input }) => {
    const inserted = await ctx.db.insert(posts).values({
      content: input.content,
      authorId: ctx.currentUser,
    });

    return ctx.db
      .select()
      .from(posts)
      .where(eq(posts.id, Number(inserted.insertId)));
  }),

create: privateProcedure
  .mutation(async ({ ctx, input }) => {
    const inserted = await ctx.db.insert(posts).values({
      content: input.content,
      authorId: ctx.currentUser,
    });

    return ctx.db
      .select()
      .from(posts)
      .where(eq(posts.id, Number(inserted.insertId)));
  }),

My question is whether this approach is the most optimal way to use Drizzle for such a pattern, or if there's a recommended best practice or more performent way for insertions followed by immediate retrieval in this context. Thanks!

4 Replies

Angelelz•9mo ago

If you are using mysql2, you can get the inserted Id from inserted[0].insertId. Then you could just construct the object that you just inserted to the database with the data you already have:

create: privateProcedure
  .mutation(async ({ ctx, input }) => {
    const inserted = await ctx.db.insert(posts).values({
      content: input.content,
      authorId: ctx.currentUser,
    });

    return {id: inserted[0].insertId, content: input.content, authorId: ctx.currentUser };
  }),

create: privateProcedure
  .mutation(async ({ ctx, input }) => {
    const inserted = await ctx.db.insert(posts).values({
      content: input.content,
      authorId: ctx.currentUser,
    });

    return {id: inserted[0].insertId, content: input.content, authorId: ctx.currentUser };
  }),

Also, you should wrap your database call in a try/catch and handle the error case accordingly

Adam•9mo ago

Ah I see. Thanks for answering. Just a question about that, what about database generated values (such as created time, updated time, etc) or default values? Is there a good solution for that as well?

Angelelz•9mo ago

Nope, you'll need to select after the insertion

Adam•9mo ago

I see. Ok, thanks :)

Gaming

Programming

Drizzle with MySQL - most optimal way to insert row and immediately retrieve inserted row