Drizzle Team•16mo ago

OnConflictDoNothing with mysql

Hi everyone, I want to use the on conflict do nothing method described here: https://orm.drizzle.team/docs/insert#onconflict-and-upsert-insert-or-update. But it seems to be that only onConflictDoUpdate is available. Is this database specific or Am I doing something wrong here? this is my query:

await db.transaction(async (tx) => {
        const storeId = nanoid();
        await tx.insert(convenienceStores).values({
          id: storeId,
          name: store.storeName,
          address: store.address,
          brand: "brand",
          coords: { lon: store.coords.lon, lat: store.coords.lat },
        });

        const newProducts = products.map(
          (product) =>
            ({
              id: nanoid(),
              name: product.name ?? "NONAME",
              mealSource: "STORE",
              image: product.imageUrl,
              calories: product.calories,
              fat: product.fats,
              carbs: product.carbs,
              protein: product.proteins,
              price: product.price,
            } satisfies InferInsertModel<typeof meals>)
        );

        await tx.insert(meals).values(newProducts).onConflictDoNothing();
        await tx.insert(convenienceStoresToMeals).values(
          newProducts.map(
            (meal) =>
              ({
                convenienceStoreId: storeId,
                mealId: meal.id,
              } satisfies InferInsertModel<typeof convenienceStoresToMeals>)
          )
        );
      });

await db.transaction(async (tx) => {
        const storeId = nanoid();
        await tx.insert(convenienceStores).values({
          id: storeId,
          name: store.storeName,
          address: store.address,
          brand: "brand",
          coords: { lon: store.coords.lon, lat: store.coords.lat },
        });

        const newProducts = products.map(
          (product) =>
            ({
              id: nanoid(),
              name: product.name ?? "NONAME",
              mealSource: "STORE",
              image: product.imageUrl,
              calories: product.calories,
              fat: product.fats,
              carbs: product.carbs,
              protein: product.proteins,
              price: product.price,
            } satisfies InferInsertModel<typeof meals>)
        );

        await tx.insert(meals).values(newProducts).onConflictDoNothing();
        await tx.insert(convenienceStoresToMeals).values(
          newProducts.map(
            (meal) =>
              ({
                convenienceStoreId: storeId,
                mealId: meal.id,
              } satisfies InferInsertModel<typeof convenienceStoresToMeals>)
          )
        );
      });

SQL Insert - DrizzleORM

Drizzle ORM | %s

3 Replies

Angelelz•16mo ago

ON CONFLICT DO NOTHING doesn't exists in Mysql, unfortunately the docs are not clear on that fact for MySql we have onDuplicateKeyUpdate

await tx.insert(meals).values(newProducts).onDuplicateKeyUpdate({set: {id: sql.raw("id")}});

await tx.insert(meals).values(newProducts).onDuplicateKeyUpdate({set: {id: sql.raw("id")}});

Angelelz•16mo ago

See here: https://stackoverflow.com/questions/4596390/insert-on-duplicate-key-do-nothing

Stack Overflow

INSERT ... ON DUPLICATE KEY (do nothing)

I have a table with a unique key for two columns: CREATE TABLE xpo.user_permanent_gift ( id INT UNSIGNED NOT NULL AUTO_INCREMENT , fb_user_id INT UNSIGNED NOT NULL , gift_id INT UNSIGNE...

DylanOP•16mo ago

Thankyou that worked for me! 🙂

Gaming

Programming

OnConflictDoNothing with mysql