sqlc-dev
Support dynamic queries
Discussed in https://github.com/sqlc-dev/sqlc/discussions/364
Originally posted by mehdijoafshani February 27, 2020
Does it make sense to support dynamic queries ? (Dynamic where and orderBy clauses).
Due to some requirements we have within our team, we need select statements with dynamic filters (a set of unknown numbers of where conditions, or lets say a set of optional where conditions which could be added to the query based on generated function's arguments) and also dynamic columns to order.
I know sqlc only supports static queries, however we still prefer to use sqlc with some extra work to provide dynamic stuff.
I am wondering if you have any plan to support dynamic queries or you are open to have this feature in your repo (I'd like to contribute and can create the PR in next few days I guess). BTW I think if we provide the feature, lots of more people can leverage sqlc.
Hi! Is there any idiomatic solution to this so far?
What is the recommended approach at this time? Creating 8 queries for 3 distinct parameters is a little bit too cumbersome. Also it's a little bit unclear how to use sqlc and self written repository functionality in tandem, so that's it's not confusing for the developer.
Ideally it would be amazing to be able to add these params dynamically, but since we are not there yet, I can imagine there is another better solution. I've seen the options suggested in the discussion #364, but I did not see any option that would be safe and idiomatic enough to use in production codebase.
What are your thoughts and vision on this? If you have any advice on tackling this problem, please share. Thanks a lot in advance!! <3
@kyleconroy 👋
I'm in the process of scoping a plugin for sqlc. Something I aim to do is generate some ORM like functionality with the data made available to sqlc plugins. Before I dive too deep on a plugin, could you provide a status update for this feature request? I know this is in the exploratory phase, I'm not sure what solutions you've tried or how close this is to landing in "planned".
I've been thinking a lot about how sqlc has scaled within our own organization and common patterns we would like to abstract.
- Pagination
- Filtering/dynamic queries
- Interceptors
- Common crud methods
- Common lazy loaders that understand relationships/cardinality
A lot of this is outside the scope of supporting "dynamic queries". But some of the glue code I would like to generate is relevant.
If you're interested in collaborating on this, I would love to chat and see how this work might overlap and if I can contribute.
You are welcome to DM me, I am @devdna on Discord.
I would like to leverage the existing db connection of SQLC to run my custom built queries that I build using a query builder (jet, squirrel, ...) Implementing a function to run raw queries on the existing connection would be perfect! Maybe this is an easy workaround for people that require dynamic queries.
@DrBlury You can do that right now without any change to sqlc itself. Sqlc wraps around the connection (sql.DB or pgx), which you can use for your custom queries as well.
@fira42073 You are absolutely right! My bad. We wrap stuff internally and I got it mixed up. Thanks!
Has there been any more thought given on how to allow SQLC to support this without using the native driver? It would be nice to have some API niceties to add additional conditions ?
