sqlc Add sqlc.slice() to support IN clauses in MySQL (#695)

This feature allows passing in a slice to an IN clause. Adding the new function sqlc.slice() as opposed to overloading the parsing of "IN (?)" was chosen to guarantee backwards compatibility.

SELECT * FROM tab WHERE col IN (sqlc.slice("go_param_name"))

This commit is based on https://github.com/kyleconroy/sqlc/pull/1312 by Paul Cameron. I just rebased and did some cleanup.

Co-authored-by: Paul Cameron [email protected]

Aug 23 '22 08:08 Jille

I'm not sure why the tests fail. It complains about codegen.json mismatching and says there's thousands of {mis,}matching lines, but the file is only ~500 lines. I suspect something regenerates it, but I failed to find the culprit.

Aug 23 '22 09:08 Jille

Does this have a path towards supporting multiple IN args? e.g. (col1, col2, ...) IN ((?,?,...), (?,?,...))

Aug 25 '22 13:08 derekperkins

what's the status of this PR? What's missing?

Oct 21 '22 16:10 Rhymond

I'm waiting for @kyleconroy to review. I'm hoping he can give me a pointer for the failing test.

Does this have a path towards supporting multiple IN args?

That should be doable to add on top of this PR.

Oct 21 '22 17:10 Jille

I'm waiting for @kyleconroy to review. I'm hoping he can give me a pointer for the failing test.

Does this have a path towards supporting multiple IN args?

That should be doable to add on top of this PR.

I'm looking at your code and thinking would it be better to do what sqlx did for for queries with IN (please check https://jmoiron.github.io/sqlx/ "in Queries"). In that way you would keep original query as parameter and you wouldn't need to use custom placeholder in your case /*SLICE:ids*/? You could replace all sqlc.slice() funcs with a question mark (same as sqlc.arg does) and then only if sqlc.slice() exists call sqlx.in in the generated function to replace query question marks and prepare argument list. What do you think?

Oct 23 '22 18:10 Rhymond

@Rhymond apologies for the late reply

Your proposal consists of two parts: a) Keep using ? as the place holder, and b) Factor out the generated code and call a library instead.

a) I don't mind much which way to go. /*SLICE:ids*/ was easier to work with in fewer lines of generated code. b) We currently don't ever import github.com/kyleconroy/sqlc, I'd like Kyle's opinion on it before going that direction.

I'd propose to first get this in, and we can easily refactor later.

Jan 02 '23 08:01 Jille

@kyleconroy this is ready to be merged

I've rebased and the test failures magically went away. Maybe I was based on a bad commit before ¯\(ツ)/¯

Jan 02 '23 08:01 Jille

I've tested this branch out in a new project and found it works as expected so far. Would love to see this merged! Thank you @Jille for the work!

Jan 08 '23 22:01 ajaxify

Hi @kyleconroy we're sponsoring this project and would really love to see this feature. It's also been requested a lot (https://github.com/kyleconroy/sqlc/issues/695) by other people.

Is there anything missing that prevents this from getting merged? Happy to help as well.

Thanks for your work!