Feedback for “Quering with SQL-like syntax [CRUD]”

[CamTheGoblin]

OnConflict and Upsert [insert or update] section: It does not appear that the .onConflictDoNothing and .onConflictDoUpdate functionality exists for all db types. specifically I am using the PlanetScaleDatabase and they do not exist. Might want to update the documentation to inform people that it is not universal functionality.

[JonathonRP]

ran into this today. is there a way to do upsert with planetscale?

[andreifilip123]

From what I could scrap through the repo, I didn't find any way to do upserts in mysql. It would be really nice if this was an option in mysql as well 🙏

[spadaval]

db.insert().ignore() does work, and it basically does what onConflictDoNothing does.

[noah-haub]

I've got the same issue.

Found the function onDuplicateKeyUpdate(). So: db.insert().values().onDuplicateKeyUpdate()

But I do not know how to use it because it is not documented anywhere. Really wish there was a way to upsert.

[kevinmitch14]

You can use it like this

await db.insert(usersTable)
    .values({ id: 1, name: 'John' })
    .onDuplicateKeyUpdate({ set: { name: 'John1' } });

[noah-haub]

Awesome thank you. Is it also possible to specify which key is a duplicate?

So for example for users. If I have a row with the email "[email protected]", I want to update it if not I want to create it.

[ryoppippi]

what is the difference between onDuplicateKeyUpdate and ignore

(also, I don't think that we cannot specify which key is duplicate, like prisma does)

Awesome thank you. Is it also possible to specify which key is a duplicate?

So for example for users. If I have a row with the email "[email protected]", I want to update it if not I want to create it.

@noah-haub I think we need to write SQL directly. like this

type NewUser = InferModel<typeof user, 'insert'>;

const newUser = {name: 'John', email: '[email protected]'} as const satisfies NewUser;

const { insertId: id } = await db.execute(sql`
			INSERT INTO ${users} (${users.name}, ${users.email})
			SELECT ${newUser.name}, ${newUser.email}
			WHERE NOT EXISTS (
				SELECT ${users.id} FROM ${users} WHERE ${users.email} = ${newUser.email}
			);
		`);

@AlexBlokh is there any way to write this SQL in drizzle?

[paul-uz]

db.insert().ignore() does work, and it basically does what onConflictDoNothing does.

I get this error

Property 'ignore' does not exist on type 'MySqlInsert<any, MySql2QueryResultHKT, MySql2PreparedQueryHKT>'

await tx.insert(schema.players).values({
                    teamID: team.$.uID,
                    playerID: player.$.uID,
                  }).ignore();

Seems to not throw an error for the following code, but its useless, because I need to add the .values() ....

await tx.insert(schema.teamPlayers).ignore();

[Stefandasbach]

? @AlexBlokh

[Cmosley]

Trying to work through this issue as well:

onDuplicateKeyUpdate(config: { set: MySqlUpdateSetSource<TTable>; }): this;

This is the type def for onDuplicateKeyUpdate but usage doesn't appear to get past config errors.

await db.insert(dailySummary).values(rows).onDuplicateKeyUpdate(config: { set: {rows}})

Would be nice to have documentation for usage of onDuplicateKeyUpdate or to add upsert functionality for mysql.

[paul-uz]

Trying to work through this issue as well:

onDuplicateKeyUpdate(config: { set: MySqlUpdateSetSource<TTable>; }): this;

This is the type def for onDuplicateKeyUpdate but usage doesn't appear to get past config errors.

await db.insert(dailySummary).values(rows).onDuplicateKeyUpdate(config: { set: {rows}})

Would be nice to have documentation for usage of onDuplicateKeyUpdate or to add upsert functionality for mysql.

You need to do

await db.insert(dailySummary).values(rows).onDuplicateKeyUpdate({ set: {rows}})

[jeffminsungkim]

Guys, are onConflictDoNothing and onConflictDoUpdate deprecated? It seems like onDuplicateKeyUpdate is the only method I could use for now, but the documentation is outdated. I'm using v0.27.2 atm.

[paul-uz]

Guys, are onConflictDoNothing and onConflictDoUpdate deprecated? It seems like onDuplicateKeyUpdate is the only method I could use for now, but the documentation is outdated. I'm using v0.27.2 atm.

They simply are not methods available for MySQL, and the docs don't mention onDuplicateKeyUpdate() or ignore()

[rhutikcodes]

+1

[heysanil]

Added a PR to the docs (https://github.com/drizzle-team/drizzle-orm-docs/pull/95) to help clarify this. In the meantime, here are the docs I wrote for anyone seeking to handle upserts/conflicts with MySQL databases (including PlanetScale):

MySQL supports ON DUPLICATE KEY UPDATE instead of ON CONFLICT clauses. MySQL will automatically determine the conflict target based on the primary key and unique indexes, and will update the row if any unique index conflicts.

Drizzle supports this through the onDuplicateKeyUpdate method:

// Note that MySQL automatically determines targets based on the primary key and unique indexes
await db.insert(users)
  .values({ id: 1, name: 'John' })
  .onDuplicateKeyUpdate({ set: { name: 'John' } });

While MySQL does not directly support doing nothing on conflict, you can perform a no-op by setting any column's value to itself and achieve the same effect:

import { sql } from 'drizzle-orm';

await db.insert(users)
  .values({ id: 1, name: 'John' })
  .onDuplicateKeyUpdate({ set: { id: sql`id` } });

[aliez-ren]

Just use:

await db.insert().ignore().values();

[paul-uz]

Just use:

await db.insert().ignore().values();

Yes, but only if you actually want to ignore upserting the values.

[tomernahum]

Btw here's what I ended up doing when I wanted to upsert multiple rows, in case anyone finds it helpful:

db.insert(posts).values(
    [ {myCol1: "myVal1", myCol2: "myVal2"}, {myCol1: "myValA", myCol2: "myValB}, ... ]
).onDuplicateKeyUpdate({set: {
    myCol1: sql`VALUES(myCol1)`,
    myCol2: sql`VALUES(myCol2)`,
}})

I hardcoded the column names for now but i think I could have used the sql``.mapWith() method from the docs to derrive it from my schema variables

idk if there is a way to do this without the sql operator

[Angelelz]

I'll close this issue as it is now documented on the docs.

[paul-uz]

I'll close this issue as it is now documented on the docs.

But the docs are wrong?

While MySQL does not directly support doing nothing on conflict, you can perform a no-op by setting any column’s value to itself and achieve the same effect

This isn't true, there is .ignore() for mysql, isn't there?

[Angelelz]

But the docs are wrong?

How so?

[tomernahum]

But the docs are wrong?

How so?

Seems like for mysql, there is an INSERT IGNORE statement which seems to me to be the same thing as onConflictDoNothing() with no target parameter, though I could be mistaken. I found pull request #305 where support for this was added to drizzle. So this should probably be added to the docs insert page as well I'm guessing. Maybe you guys could even make it so that onConflictDoNothing() is supported for mysql as well, (idk whether that is or isn't inline with the project's vision)

[Angelelz]

This issue was closed because the report in OP was taken care of in the docs. Please open a new one for proper tracking of the new issue.

[paul-uz]

This issue was closed because the report in OP was taken care of in the docs. Please open a new one for proper tracking of the new issue.

The docs are still missing .ignore() for mysql.

[heysanil]

INSERT IGNORE in MySQL is not quite the same as ON CONFLICT DO NOTHING in Postgres/SQLite—INSERT IGNORE will ignore certain other insert errors (including certain invalid/missing column errors) rather than just key conflicts, which can result in unexpected behavior. For example, if you try to insert a row with a NULL value for a non-null column, using INSERT IGNORE would completely skip inserting the row without throwing any error for your application to handle, even if there was no key conflict in the insertion.

Using a no-op with ON DUPLICATE KEY UPDATE is the primary way to do nothing upon a key conflict while avoiding those side effects, and is why I wrote the documentation as such. .ignore() is not an equivalent to doing nothing on conflict and should not be a recommended solution for this specific intention around conflicts (which is the original intent of this issue).

.ignore() should still definitely be added to the documentation, but that's a separate concern and should be tracked in a new issue as @Angelelz suggested.