Support passing slices to MySQL queries

[kyleconroy]

While MySQL supports the ANY operator, it does not support arrays. This means the currently documented way to pass slices to a query will not work.

[kyleconroy]

Here's some related discussion from the go-sql-driver repository:

• in (?) cannot work and no error  go-sql-driver/mysql#107
• Using ? with slices go-sql-driver/mysql#176

[kyleconroy]

The MEMBER OF syntax may offer a solution. The problem is that you need to encode the slice as JSON before passing it into the query.

CREATE TABLE pkg (
  id text NOT NULL,
  vic VARCHAR(50) NOT NULL,
  kim text NOT NULL,
  order INTEGER NOT NULL,
  PRIMARY KEY (id)
);

/* name: GetPkgID :many */
SELECT id, vic, kim FROM pkg
WHERE vic MEMBER OF(?) AND shop_id = ?;

[kyleconroy]

Okay, here's my current idea. We add a second type parameter to sqlc.arg. That parameter, if passed, will compile to a cast statement. We can support the ARRAY type syntax in this type parameter and generate the correct code.

CREATE TABLE users (id SERIAL);

-- name: FilterUsers :many
SELECT * FROM users
WHERE id MEMBER OF(sqlc.arg('ids', 'SERIAL ARRAY'));

const filterUsers = `-- name: FilterUsers :many
SELECT id FROM users
WHERE id MEMBER OF(CAST(? AS JSON));
`

func (q *Queries) FilterUsers(ctx context.Context, ids []int64) ([]User, error) {
	// ...
}

A few issues I can think of:

• The array type must convert to a scalar type that can be represented inside a JSON array
• We'd need to generate a helper method that converts slices to JSON
• We'd probably also need to add a method that unmarshals JSON as well

[warent]

I'm not sure if it's bad practice, but maybe the generated code could produce a dynamic query, generating N placeholders for each array value. So, something like:

func filterUsers(vals []interface{})
{
return fmt.Sprintf(`-- name: FilterUsers :many
SELECT id FROM users
WHERE id IN (?%v);
`, strings.Repeat(",?", len(vals)-1));
}

[yeka]

Just throwing an idea (or hack) regarding IN statement in SQL.

Current behaviour:

const listAuthors = `-- name: ListAuthors :many
SELECT id, name FROM authors WHERE id IN($1) AND name = $2;
`

type ListAuthorsParams struct {
	ID   int 
	Name string
}

func (q *Queries) ListAuthors(ctx context.Context, arg ListAuthorsParams) ([]Author, error) {
	rows, err := q.db.QueryContext(ctx, listAuthors, arg.ID, arg.Name)

My idea is to change the parameter into slice and expanding the query based on the size of the slice.
What I meant by expanding is to replace id IN ($1) into id IN($1, $2, $3, ...).
If there's 3 ids, name = $2 will become name = $4.
Using the same query, the generated code will now look like this:

type ListAuthorsParams struct {
	ID   []int 
	Name string
}

func (q *Queries) ListAuthors(ctx context.Context, arg ListAuthorsParams) ([]Author, error) {
	query, args := q.expand(listAuthors, expandArg{arg.ID}, arg.Name)
	rows, err := q.db.QueryContext(ctx, query, args...)

The expandArg type is used to differentiate normal slice with slice from IN statement.
The q.expand() function will return modified query and args.

For example, given that arg = ListAuthorsParams{[]int{9, 8, 6, 7}, "Joe"}, q.expand() will return:

query = "SELECT id, name FROM authors WHERE id IN($1, $2, $3, $4) AND name = $5;"
args = []interface{}{9, 8, 6, 7, "Joe"}

The code for q.expand() can be seen here. I haven't managed to find how to change arg.ID into []int.

Not forget to mention, this solution will not work for prepared statement.

[mightyguava]

How about for MySQL only, ( ? ) will be interpreted as accepting a list of items of that type, rather than a single one?

Pros

• It's still valid SQL
• It would not be confusing to users. As in previous issues like [mysql] Not support where id in (?) #706, in (?) cannot work and no error  go-sql-driver/mysql#107, Using ? with slices go-sql-driver/mysql#176, people naturally assume you can pass in a slice. Any strongly-typed language will prevent you from passing in a single value.

Cons

• The type of the binding differs from what you'd have to pass in to the driver, but sqlc already abstracts the driver away so that's fine.
• What would you do about (?, ?, ?) etc...? Imo sqlc could just error in the parse stage and say that you have to write your query as a single (?)

[namndev]

Hi @kyleconroy,

Good your idea. But

SELECT id FROM users
WHERE id MEMBER OF(CAST(? AS JSON))

Syntax error with keyword MEMBER OF ? I use sqlc 1.6.0.

[namndev]

I have an idea,

using FIND_IN_SET

SELECT id FROM users
WHERE FIND_IN_SET(id, ?)

But name of variable after run sqlc generate: FINDINSET and FINDINSET_2, ...
@kyleconroy, How to fix or rename variable in above?

[fr3fou]

How can I achieve this in postgres?

[xiazemin]

Just throwing an idea (or hack) regarding IN statement in SQL.

Current behaviour:

const listAuthors = `-- name: ListAuthors :many
SELECT id, name FROM authors WHERE id IN($1) AND name = $2;
`

type ListAuthorsParams struct {
	ID   int 
	Name string
}

func (q *Queries) ListAuthors(ctx context.Context, arg ListAuthorsParams) ([]Author, error) {
	rows, err := q.db.QueryContext(ctx, listAuthors, arg.ID, arg.Name)

My idea is to change the parameter into slice and expanding the query based on the size of the slice.
What I meant by expanding is to replace id IN ($1) into id IN($1, $2, $3, ...).
If there's 3 ids, name = $2 will become name = $4.
Using the same query, the generated code will now look like this:

type ListAuthorsParams struct {
	ID   []int 
	Name string
}

func (q *Queries) ListAuthors(ctx context.Context, arg ListAuthorsParams) ([]Author, error) {
	query, args := q.expand(listAuthors, expandArg{arg.ID}, arg.Name)
	rows, err := q.db.QueryContext(ctx, query, args...)

The expandArg type is used to differentiate normal slice with slice from IN statement.
The q.expand() function will return modified query and args.

For example, given that arg = ListAuthorsParams{[]int{9, 8, 6, 7}, "Joe"}, q.expand() will return:

query = "SELECT id, name FROM authors WHERE id IN($1, $2, $3, $4) AND name = $5;"
args = []interface{}{9, 8, 6, 7, "Joe"}

The code for q.expand() can be seen here. I haven't managed to find how to change arg.ID into []int.

Not forget to mention, this solution will not work for prepared statement.

i fufill a version that solve this problem https://github.com/xiazemin/sqlc

[asterikx]

Is there a workaround for the time? Listing by IDs seems to be a rather crucial thing to do after all, e.g. for pre-loading associations

[ericraio]

Is there a workaround for the time? Listing by IDs seems to be a rather crucial thing to do after all, e.g. for pre-loading associations

The only work around I ended up doing was creating a separate file that shares the same package name and writing the golang code manually :/

[xiazemin]

Is there a workaround for the time? Listing by IDs seems to be a rather crucial thing to do after all, e.g. for pre-loading associations

The only work around I ended up doing was creating a separate file that shares the same package name and writing the golang code manually :/

my fork solved this issue https://github.com/xiazemin/sqlc

[xiazemin]

and the example of https://github.com/xiazemin/sqlc is here https://github.com/xiazemin/sqlc_study

[ericraio]

hey @xiazemin this is great! Just read your previous messages also, maybe you can make a proposal or pull request to this repo? I recommend contributing to this git repository instead of branching with your fork because your fork seems to be missing a ton of features.

[koolay]

@kyleconroy Hi, any plan to support slice?

[muir]

A good solution to this is crucial. There is an acceptable solution for the postgres driver using pg.Array() but in the comment thread above there was nothing that really works for mysql. In the meantime, I'll fall back to using a query generator like squirrel but that's not my first choice.

[ericraio]

I've been using a forked version of sqlc where they implemented sqlc.slice() and all it does is a string replace in the generated code. Not ideal but works really good and would prefer that we had this then not having the feature at all.

[xiazemin]

my fork also support insert into xxx values xxx， where values is a slice,you can try it!

[xiazemin]

https://github.com/xiazemin/sqlc

[ericraio]

Xiazemin, your sqlc repo renames everything and is not a true fork, your code can’t be migrated back into the main repository.

…

On May 31, 2022, at 8:49 AM, xiazemin ***@***.***> wrote: https://github.com/xiazemin/sqlc <https://github.com/xiazemin/sqlc> — Reply to this email directly, view it on GitHub <#695 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAKW6GUNJEE2FNROJSWWBLVMYYITANCNFSM4QPX4D2Q>. You are receiving this because you commented.

[yeomc-quasar]

When will this feature be updated?

[link-duan]

SELECT * FROM a_table WHERE (a, b) in (?);

How to implement this case in sqlc?

[SebastienMelki]

Hello, this seems like a pretty important feature to have. Anybody have a plan to implement this? If somebody can point me to the correct place I can try submitting a PR.

Thanks

[chengjun-suger]

It works in Postgresql

-- name: BatchUpdateUsageRecordGroupStatus :exec
UPDATE metering.usage_record_group 
SET status = $2, last_update_time = CURRENT_TIMESTAMP 
WHERE organization_id = $1 AND id = ANY(@ids::text[]);

[gmhafiz]

Thanks @chengjun-suger I tried with

-- name: SelectWhereInLastNames :many
SELECT * FROM users WHERE last_name = ANY(@last_name::text[]);

and it generates

import (
	"context"
	"database/sql"
	"encoding/json"

	"github.com/lib/pq"
)

const selectWhereInLastNames = `-- name: SelectWhereInLastNames :many
SELECT id, first_name, middle_name, last_name, email, password, favourite_colour FROM users WHERE last_name = ANY($1::text[])
`

func (q *Queries) SelectWhereInLastNames(ctx context.Context, lastName []string) ([]User, error) {
	rows, err := q.db.QueryContext(ctx, selectWhereInLastNames, pq.Array(lastName))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []User
	for rows.Next() {
		var i User
		if err := rows.Scan(
			&i.ID,
			&i.FirstName,
			&i.MiddleName,
			&i.LastName,
			&i.Email,
			&i.Password,
			&i.FavouriteColour,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

[zc2638]

I see this issue discussed for nearly two years, is there a solution or a plan in the near future, which will determine whether my project can choose to use sqlc instead of others, such as gorm.
Thanks.

[karatekaneen]

Really want this feature as well and started to think about an alternative approach to the problem.

Not sure if this is possible in the way sqlc works, I'm just putting the idea out there to start a discussion.
If we can somehow identify where in the query the problem is and we can modify the query accordingly. One approach to this would be to add some kind of "variadric" configuration based on index

For example, take the following query:

-- name: FindThings :many :variadric(0)
SELECT * 
FROM a_table 
WHERE status IN ( ? )
AND created_at > ?

This would tell sqlc that the first ? is actually a slice of values and would change the generated function to instead expecting T actually expect []T and when calling to do the query it uses the same approach as mentioned here to replace the first ? with the number of items in the slice passed in.

Would this be possible with the current approach? And if so, does anyone know where to start?

[LIQRGV]

Hi @karatekaneen,
for my current project, I create a helper like this
https://gist.github.com/LIQRGV/228c15d9806cc1072acaee54c2d27c43
Using this, we will have to use ??? placeholder like this WHERE column_name IN (???).
Here is the example how to use it.
https://gist.github.com/LIQRGV/930f09a9a938f18cf74df8eec2727a52

Yes, I should put some manual labor to list the args that using IN keyword (see argsWithInKeyword variable).
But well, for myself I'm not sure how to make it more simple.
Any feedback is appreciated.

[karatekaneen]

@LIQRGV Interesting, going to have a deep dive in the code on Monday to see what you've done.
The syntax with ??? is a much better idea imo, only drawback is that it's not valid SQL anymore(?).

Currently looking to doing something similar to what you've done but as a plugin, but haven't decided anything yet.

[LIQRGV]

only drawback is that it's not valid SQL anymore(?)

Yep, @karatekaneen technically it's not a valid SQL anymore since it'll parse the query into another placeholder depend on the number args passed.
So if you have 2 params, it will become
WHERE x_column IN (?,?)

Anyway, do you know how to use IN keyword on normal SQL ? I mean using it on prepared statement.
Maybe I can make it to comply with SQL standard.

[FrancoisPrigent]

FIND_IN_SET does the job

[mrngm]

This waits for #1816 to be reviewed and merged.

[sgielen]

Can this not be closed now that #1816 is merged?

[kyleconroy]

https://docs.sqlc.dev/en/stable/reference/macros.html#sqlc-slice

Adding a link to the docs for future folks

[YvanDaSilva]

@kyleconroy #2002 was marked as a duplicate of this issue.
However, it seems it isn't.

#2002 concerns multiple inserted values. sqlc.slice can't be used for that purpose as it replaces name by multuplie escaped params entries of type string.

We have this other issue #3305 that is open that explains it better IMHO.
https://play.sqlc.dev/p/7bfa28e99e0207fb0c7d2b457ccc452674daa60acb5bccea336d7c3de293b220