Named parameters for MySQL inserts / updates

[SebastienMelki]

What do you want to change?

I'm trying to generate code based on queries like this

-- name: InsertPurchaseGuid :exec INSERT INTO purchase_guids (external_id, guid, created_at, updated_at, partner_id, payment_method_id) VALUES (:external_id, :guid, :created_at, :updated_at, :partner_id, :payment_method_id);

So that I can use the NamedExec function of the mysql driver. But whenever I run sqlc generate I get this error

db/queries/subs/insert_purchase_guid.sql:3:10: syntax error near ":external_id, :guid, :created_at, :updated_at, :partner_id, :payment_method_id);"

Is this not supported at all? Or am I doing it wrong?

Thank you!

What database engines need to be changed?

MySQL

What programming language backends need to be changed?

Go

[orisano]

MySQL does not support NamedParameter

[SebastienMelki]

MySQL does not support NamedParameter

Hello, do you mean it's not supported in sqlc? Because MySQL itself does support named parameters just fine as far as my testing goes. I used the .NamedExec of the sql driver in go, and got the expected results

[kyleconroy]

The named parameter syntax you're using is not native to MySQL. It's an extension added by sqlx. To use named parameters with sqlc, you need to use the sqlc.arg function, like so:

/* name: InsertPurchaseGuid :exec */
INSERT INTO purchase_guids (
  external_id,
  guid,
  created_at,
  updated_at,
  partner_id,
  payment_method_id
) VALUES (
  sqlc.arg('external_id'),
  sqlc.arg('guid'),
  sqlc.arg('created_at'),
  sqlc.arg('updated_at'),
  sqlc.arg('partner_id'),
  sqlc.arg('payment_method_id')
 );

[SebastienMelki]

Hi @kyleconroy thank you for the answer. I actually use sqlc only to generate the structs for the relevant tables / queries, but I don't use the generated function to run those queries, I have my own custom ones that include instrumentation and logging. Is there a way to force sqlc to still generate the structs with the sqlx extension syntax? Or absolutely no way?

Thanks for the feedback!

[kyleconroy]

There isn't a way to generate the structs. The SQL isn't valid MySQL syntax, so sqlc can't parse the queries.

[SebastienMelki]

Alright thanks a lot @kyleconroy ! Appreciate it