Hi@mehdijoe, I requested for the same in slack but it's not supported as of now. I was thinking even if this got supported there is another mess of switch case statements for right sqlc method to call based on the dynamics parameters passed.

@mehdijoe Can you provide a concrete example of your needs? I'm surprised that the dynamic filters are unknown. Having a concrete example would also make it easier to discuss the best solution.@dharmjit, it sounds like you have the same issue? Could you provide your use case?

Thanks for ur response@kyleconroy

I'm surprised that the dynamic filters are unknown.

Actually unknownnumbers of conditions ..., OK, here is my use case:

createtablet1(    field_fk uuid,    field1varchar(100),    field2varchar(100),);

I need to select ont1, which the filters are determined from UI, so I need any combination of filters, it means I should support following 9 queries on my back-end:

select*from t1where field_fk=$1select*from t1where field_fk=$1AND field1= $2select*from t1where field_fk=$1AND field1like $2select*from t1where field_fk=$1AND field2= $2select*from t1where field_fk=$1AND field2like $2select*from t1where field_fk=$1AND field1= $2and field2= $3select*from t1where field_fk=$1AND field1like $2and field2= $3select*from t1where field_fk=$1AND field1= $2and field2like $3select*from t1where field_fk=$1AND field1like $2and field2like $3

Users can also sort on any column they wish (ASC/DESC), it means for each query listed above I need to append followingOrder By clauses:

order by field1ASCorder by field1DESCorder by field2ASCorder by field2DESC

Which is9*4 = 36 different possible queries for a table with 2 fields.
For where clause filters we can leverageCASE WHEN and sqlc named parameters, but I am not sure aboutCASE WHEN performance using for making a select statement dynamic.

I wish I could use sqlc with such an input query (this is kind of my proposal for the feature):

-- name: GetT1 :manySELECT t1Where field_fk=@field-fk_param::text--optional field1_eq: AND field1=@field1_eq_value::text--optional field1_like: AND field1 like @field1_like_value::text--optional field2_eq: AND field2=@field1_eq_value::text--optional field2_like: AND field2 like @field1_like_value::text--optional order_field1_ASC: ORDER BY field1 ASC--optional order_field1_DESC: ORDER BY field1 DESC--optional order_field2_ASC: ORDER BY field2 ASC--optional order_field2_DESC: ORDER BY field2 DESC--optional_group: order_field1_ASC, order_field1_DESC, order_field2_ASC, order_field2_DESC

A select statement with a number of optional lines, if there is any optional line in the input query the whole query should use named parameters, each optional line would start withoptional word followed by a Boolean named parameter (indicating if the line is going to be added to the query) and a ':' followed by the statement we wish to add to the query.

Andoptional_group indicate that only one of following optional lines could be enabled
(about the optional_group I think I am over complicating, it's king of thinking aloud :) )

Before compiling the query, sqlc needs to un-comment the optional lines

@mehdijoe Thank you so much for providing a concrete example.

sqlc can handle the use case you outlined with a single query. Just a note, I changed the names of the columns so that the example would be more readable.

CREATETABLEfoo (            fk     uuid,            barvarchar(100),            bazvarchar(100));-- name: FilterFoo :manySELECT*FROM fooWHERE fk= @fkAND (CASE WHEN @is_bar::bool THEN bar= @bar ELSE TRUE END)AND (CASE WHEN @lk_bar::bool THEN barLIKE @bar ELSE TRUE END)AND (CASE WHEN @is_baz::bool THEN baz= @baz ELSE TRUE END)AND (CASE WHEN @lk_baz::bool THEN bazLIKE @baz ELSE TRUE END)ORDER BY  CASE WHEN @bar_asc::bool THEN bar ENDasc,  CASE WHEN @bar_desc::bool THEN bar ENDdesc,  CASE WHEN @baz_asc::bool THEN baz ENDasc,  CASE WHEN @baz_desc::bool THEN baz ENDdesc;

You can see what the generated code lookshere in the playground. Below is an example main function that shows how to useFilterParams for dynamic filter and ordering.

Please let me know if I've missed anything!

package mainimport ("context""database/sql""log"_"github.com/lib/pq""github.com/google/uuid")funcrun(ctx context.Context,db*sql.DB)error {q:=New(db)rows,err:=q.Filter(ctx,FilterParams{// Set the values of the two columns that you're comparingFk:uuid.MustParse("example-uuid"),Bar: sql.NullString{Valid:true,String:"foo"},Baz: sql.NullString{Valid:true,String:"bar"},// Set the values of the two columns that you're comparingIsBar:true,// IsBaz:  true,// Configured the order of the resultsBarAsc:true,// BarDesc: true,})iferr!=nil {returnerr}for_,row:=rangerows {log.Printf("%d: bar:%s baz:%s\b",row.Sid,row.Bar.String,row.Baz.String)}returnnil}funcmain() {db,err:=sql.Open("postgres","dbname=example sslmode=disable")iferr!=nil {log.Fatal(err)}iferr:=run(context.Background(),db);err!=nil {log.Fatal(err)}}

@kyleconroy Thank you a lot for the explanation and your time amigo !
Actually we tried providing dynamic queries usingCASE WHERE as a POC but the benchmarks showed considerable impact on query performance and we decided not to useCASE WHERE in our product queries, and that's why I thought maybe we can propose such a feature in sqlc.
Do you have any comment on that ?

Some input to the discussion:

This form of static query with dynamic filtering is generally regarded as one of the worst performanceanti-pattern for RDBMS. The reason for that is the caching of the execution plan where in this case the query planner can't optimize the query and has to prepare for the worst case (all filters are unused) and will decide for aseq_scan in all cases (no indexes will be used).

With respect toGO in case parameters are passed to the query it will be prepared, executed and deallocated. In the case of postgres the execution plan for prepared statements will only start to be cached after 5 executions (as far as i know of).

What this would mean in the case of the combination ofpostgres andGo the impact should not be to big in case where no prepared queries (with Go prepare) are used, but there is always the risk that a suboptimal execution plan will be executed.

Hi@kyleconroy , your comment with sql using dynamic filter kind of solve the issue but sometimes its gets messy to prepare/provide these many arguments to generated method. Do you see this being supported in some other way in sqlc evolution.

the impact should not be to big in case where no prepared queries (with Go prepare) are used, but there is always the risk that a suboptimal execution plan will be executed.

@arddor Thanks for the great write up about why this pattern can result in sub-optimal query plans.

Do you see this being supported in some other way in sqlc evolution

I think there are a few ways we could better support this pattern in sqlc. Right now I'm leaning toward generating multiple queries for a single input query, and then choosing which query to execute based on one of the arguments. Here's what this could look like in practice

-- name: FilterFoo :manySELECT*FROM fooWHERE fk= @fkORDER BY  CASE WHENsqlc.switch('sort','bar_asc') THEN bar ENDasc,  CASE WHENsqlc.switch('sort','bar_desc') THEN bar ENDdesc,  CASE WHENsqlc.switch('sort','baz_asc') THEN baz ENDasc,  CASE WHENsqlc.switch('sort','baz_desc') THEN baz ENDdesc;

package dbimport ("context""database/sql""log""github.com/google/uuid")typeSortstringconst (SortBarAscSort="bar_asc"SortBarDescSort="bar_desc"SortBazAscSort="baz_asc"SortBazDescSort="baz_desc")typeFilterFooParamsstruct {Fk    uuid.UUIDSortSortEnum}

While I think this could work, it's a significant amount of work. Right now I'm focusing my time working on better type checking and inference, and adding SQLite and MySQL support. I don't see this being a priority any time soon. Sorry.

Is this supported using MySQL? Cannot figure out the syntax for this.

Would also be interested in this feature. I think dynamic where/orders etc appear in common use-cases.

Would it make sense to break out of SQL syntax here? This feature is a wish to express multiple possible query structures, rather than parameterising a single static query. Using a syntax that's clearly separate would avoid confusion between what's slotting parameters into one SQL text, and what's about expressing multiple SQL texts.

A rough example of what this could look like (not thinking too much about the right delimiters to use etc):

-- name: FilterFoo :manySELECT*FROM fooWHERE fk= @fkORDER BY     [switch sort        case'bar_desc': sql("bar desc")        case'bar_asc': sql("bar desc")        case'baz_desc': sql("baz desc")        case'baz_asc': sql("baz asc")     ]

One approach would be for the compiler to handle this first and generate multiple outputs, which it could then parse (and therefore validate) as SQL.

Using the multiple queries approach@kyleconroy described, this final output of this would be 4 queries, one for each of the sort options, and a function that decides which to run. Rough example output:

funcFilterFoo(ctx context.Context,paramsFilterFooParams) {// pick the sql to usesql:=""switchparams.Sort {caseSortBarAsc:sql=filterFooSQL_a// ... etc  }// run query}constfilterFooSQL_a=`SELECT *WHERE fk = ?ORDER BY bar desc`// other queries (potentially expressed more optimally than consts with complete fragments)

Dynamic where clauses for a query are often useful, vs selecting a potentially large result set and filtering in memory. As an example of what would be required to express "filter the results by zero or more of these criteria":

SELECT*FROM foo[if any criteria_a critera_b     [combine"AND"       [if present criteria_a          sql("col_a = $criteria_a")       ]       [if present criteria_b          sql("col_b = $criteria_b")       ]] ]

This would output four queries: with where clauses (), (A), (B), (A,B). The parameters generated for the criteria would need to be nullable to distinguish zero values, e.gCriteriaA sqlc.OptionalBool. Then the generated function to run it would know which combination to pick.

A potential challenge: the number of queries generated would double with each new independent parameter. So with 10 parameters there are 1,024 possible SQL texts. It'd need some research/thought to consider how best to mix runtime and compile-time generation to avoid runtime, memory or binary-size overhead.

Clearly this would break out of the "only need to know SQL" paradigm (and would confuse IDEs). But that's the status-quo for what developers would have to do instead while usingsqlc: fall back to dynamically generating SQL with Go. This provides way to do it while benefitting from all the other benefits of sqlc's code-generation strategy (and editors can be updated to understand the syntax). Options to reduce the extra knowledge required would be to embed Go, or Go templating syntax. But Go isn't expression oriented so would be verbose (return sql("...") everywhere), and people might expect Go template behaviour if its syntax was used, where this isn't simply templating.

Anyway, I'd be interested in this feature, and may potentially have time to implement it. But before that I wanted to see if the maintainers and community liked the look of the approach!

Hi@kyleconroy, what do you think about this approach to select only necessary columns? As you can see we declare command:one-columns and then generator should generateif-blocks for each column name from table and construct arguments forQueryRow func.

CREATETABLEusers (    user_id     UUIDPRIMARY KEYNOT NULL,    emailVARCHAR(256)NOT NULL,    first_nameVARCHAR(50)NOT NULL,    last_nameVARCHAR(50)NOT NULL,    created_atTIMESTAMPTZNOT NULL DEFAULT NOW(),    updated_atTIMESTAMPTZNOT NULL DEFAULT NOW());

constuserColumns=`-- name: userColumns :one-columnsSELECT * FROM usersWHERE user_id = $1 LIMIT 1`func (q*Queries)userColumns(ctx context.Context,userID uuid.UUID,columns...string) (User,error) {s:="SELECT "+strings.Join(columns,",")variUserargs:=make([]interface{},len(columns))ifexists(columns,"user_id") {args=append(args,&i.UserID)}ifexists(columns,"email") {args=append(args,&i.Email)}ifexists(columns,"first_name") {args=append(args,&i.FirstName)}ifexists(columns,"last_name") {args=append(args,&i.LastName)}ifexists(columns,"created_at") {args=append(args,&i.CreatedAt)}ifexists(columns,"updated_at") {args=append(args,&i.UpdatedAt)}row:=q.db.QueryRow(ctx,strings.Replace(userColumns,"SELECT *",s,1),userID)err:=row.Scan(args...)returni,err}funcexists(ss []string,sstring)bool {fori:=0;i<len(ss);i++ {ifs==ss[i] {returntrue}}returnfalse}

Hello I just madea sql builder to make sqlc support simple dynamic queries. Not perfect, but enough for me.

Any feedback or suggestion is welcome :)

Try this one

for dynamic optional query
https://play.sqlc.dev/p/0285e94968add560e410a2c8d251649e28c8cb366ba4eef7ea172e9e732584b8

How about a simpler approach for dynamic where clauses and dynamic order by clauses

add support to define queries that if containing ($FILTER) will be replaced by an optional string parameter containing the filter clause or clear out ($FILTER) if empty parameter

when where is present
SELECT t.* FROM table t WHERE t.id=3 and t.name='test' and ($FILTER)

when where is not present
SELECT t.* FROM table t WHERE ($FILTER)

then is dev responsibility to add the correct filter clause string dynamically from their code logic

As for the order by clause it can be predefined as currently is but by adding also an optional string parameter that could contain an overridable order by clause

SELECT t.* FROM table t WHERE t.id=3 and t.name='test' and ($FILTER) order by t.id, t.name;
"t.id, t.name" will have to be kept in a constant when generating the sql.go file

e.g:

const listClasses =-- name: ListClasses :many select t.id, t.name, t.description from table t where t.id=$1 and t.name=$2 and ($FILTER) order by t.id, t.name
const listClassesOrderBy="t.id, t.name"

func (q *Queries) ListClasses(ctx context.Context, arg ListClassesParams, filterClause, order string) ([]*Class, error) {

perform logic to replace filter if filterClause provided or remove ($FILTER) entirely before executing query
perform logic to add or replace order by clause or keep predefined order by if provided

rows, err := q.db.Query(ctx, listClasses, arg.ID, arg.Name)
...
}

My use case today was a select where I'm selecting on sometimes one and sometimes two different values of a column (the actual query is almost 20 lines long, so just duplicating it is unpleasant unless I wrote another tool to template the template ... ugh).

select barfrom twhere fooin (?,?)

My solution for this worked for two values and my particular schema (where foo isn't NULLable):

      ...from twhere fooin (sqlc.arg('foo'),sqlc.narg('foo_2'))

I wrote a proposal#2061 (comment) for asqlc.orderBy() macro. This macro doesn't go so far as to support arbitrary expressions, but would solve the most common use case of wanting to control ordering given the columns specified in the query.

This seems like an approachable compromise to remove some of the friction ofORDER BY without needing to solve the larger architectural issue of "dynamic queries". Thoughts? This is something I'd be willing to contribute.

dynamic sql is a very important feature fordatabase access layer tool.
I come across this library, I think it's on the right path for the solution for database access layer. i have years experiences in java. I like(think) we can implementdynamic feature ashttps://mybatis.org/mybatis-3/dynamic-sql.html.

in factsql is a way to expressbusiness if try to give a solution just from technical respect, it will make things more complicated.
there are tons ofdatabase access layer on the market, but most of them do tooo many magics in the black box.

@andrewmbenton would it be possible to give us an status update on the ideas you are considering or if you already agreed on the path forward? 🙏 Thanks in advance :)

To add to all the above, we would find dynamic filtering / ordering instrumental at the SQLC layer, currently the API is too crufty and potentially non-performant to be considered acceptable above two or three columns, to the point where we must consider abandoning or forking the repository.

It would be helpful for the authors to provide some certainty here, it has been four years and from earlier statements it seems the authors were open to this possibility. Can we be given a clear yes/no regarding support for this?

This might be ugly, but why not just slap Go templates on top of it?

-- name: GetUsers :manyselect u.*from usersas u{{ if .ManagerName }}join managersas monm.id=u.manager_idandm.name=sqlc.arg(ManagerName){{ end }}where true  {{ if .Name }}and name=sqlc.arg(Name) {{ end }}  {{ if .Type }}and type=sqlc.arg(Type) {{ end }}

Tools like dbt use this approach and it must be more or less trivial to implement since there must be a template language parser somewhere intext/template.

Posting this here so I can ask for feedback. What we did for now was create the query with all the possible conditions so it will generate all the types needed (InitFilterThisTable is added so we can easily append filters later on):

-- name: InitFilterThisTable :manySELECT id, foreign_id, name, created_atFROM this_tableWHERE true;-- name: FilterThisTable :manySELECT id, foreign_id, name, created_atFROM this_tableWHERE true    AND foreign_id = @foreign_id    AND name = @name    AND another_field = @another_field    AND (created_at >= @start_date::DATE AND created_at <= @end_date::DATE);

whensqlc generate is ran, it would create static queries and types for this, then we just create a new file and modify the type and function created so we can pass dynamic query to it

type DynamicFilterThisTableParams struct {// Added omitempty so we can optionally pass fields depending on query needForeignID    pgtype.UUID `json:"foreign_id,omitempty"`Name         pgtype.Text `json:"name,omitempty"`AnotherField pgtype.Text `json:"another_field,omitempty"`StartDate    pgtype.Date `json:"start_date,omitempty"`EndDate      pgtype.Date `json:"end_date,omitempty"`}func (q *Queries) DynamicFilterThisTable(ctx context.Context, query string, arg DynamicFilterThisTableParams) ([]FilterThisTableRow, error) {// Added this part so we can pass query (2nd parameter) and used pgx NamedArgs so we don't have to determine which is and which is not passedjarg, _ := json.Marshal(arg)var narg pgx.NamedArgsjson.Unmarshal(jarg, &narg)rows, err := q.db.Query(ctx, query, narg)// End of added partif err != nil {return nil, err}defer rows.Close()var items []FilterThisTableRowfor rows.Next() {var i FilterThisTableRowif err := rows.Scan(&i.ID,&.i.ForeignID,&i.Name,&i.CreatedAt,); err != nil {return nil, err}items = append(items, i)}if err := rows.Err(); err != nil {return nil, err}return items, nil}

Here we addedomitemptys so we can pass arguments optionally, and on the query method, we added the query param and for theargs, we used named parameters so we don't need to determine manually what was used/passed.

The only problem here is when we add other filters, we need to update our own copy ofDynamicFilterThisTableParams but the query method can stand alone since we are using pgx'snamed params.

Usage then afterwards is:

var filters database.DynamicFilterThisTableParamsif err := json.Unmarshal([]byte(request.Body), &filters); err != nil {return helpers.ErrorAPIGatewayProxyResponse(http.StatusBadRequest, err)}var s strings.Builder// We started with InitFilterThisTable so it's easier to append queriess.WriteString(strings.TrimSuffix(database.InitFilterThisTable, ";"))if filters.ForeignID.Valid {s.WriteString(" AND foreign_id = @foreign_id")}if filters.Name.Valid {s.WriteString(" AND name = @name")}if filters.AnotherField.Valid {s.WriteString(" AND another_field = @another_Field")}if filters.StartDate.Valid && filters.EndDate.Valid {s.WriteString(" AND (created_at >= @start_date AND created_at <= @end_date)")} else if filters.StartDate.Valid {s.WriteString(" AND created_at >= @start_date")} else if filters.EndDate.Valid {s.WriteString(" AND created_at <= @end_date")}fmt.Println("query: ", s.String())records, err := database.New(pool).DynamicFilterThisTable(context.Background(), s.String(), filters)

This works for most of our use-cases but is not full-on dynamic.

Here is one more approach to the issue. We could just add special comments to the lines those need to be removed from the query text if the corresponding arguments are empty. Like this:

-- name: SearchQuery :manySELECT *FROM tableWHERE a = @a  AND b = @b -- :if @b  AND c = @c -- :if @cORDER BY  id ASC     -- :if @id_asc  id DESC    -- :if @id_desc  date ASC   -- :if @date_asc  date DESC  -- :if @date_desc

Then, if one specifies, for example, onlya,b andidDesc params, the actual query to be executed becomes this:

-- name: SearchQuery :manySELECT *FROM tableWHERE a = @a  AND b = @b -- :if @bORDER BY  id DESC    -- :if @id_desc

This approach has several advantages:

• It's simple to implement - sqlc just needs to split the query text by lines, parse the comments and keep only needed lines
• It's simple to use - just format your query properly and add the needed comments
• It's aligned with the sqlc's approach to write the query name and type in the first line comment
• It covers the most common case of UI search with filters
• It keeps SQL in SQL files, no table field names in your Go code (only in the generated parts, which is ok)
• It keeps the actual query simple and so does not prevent DBMS from optimizing it and using the indexes

Maybe it does not cover all the cases, I'm not sure, but it's super simple for the most common case and it keeps the best possible performance of the query.

What do you think?

Below is an example of the kind of code that I hope to one day use with SQLC.

Right now, it is usinggithub.com/Masterminds/squirrel (an SQL builder), imported assq.
In real life, it gets much more complicated with 10s ofif statements. If I used aCASE statement for eachif that we want, we'd have the ugliest and worst performing generated query imaginable.

I believe@maxmanuylov'sproposed solution would cover this.

The use of:if @param_name would necessitate a new optional type wrapper on that param, for the generated FilterParams struct.

func (dDAO)SelectAllAccountsByFilter(ctx context.Context,filters models.Filters) ([]models.Account,error) {query:=sq.Select("id","name","email","active","fav_color","fav_numbers","properties","created_at").From("accounts").OrderBy("id")iflen(filters.Names)>0 {query=query.Where(sq.Eq{"name":filters.Names})}iffilters.Active!=nil {query=query.Where(sq.Eq{"active":*filters.Active})}iflen(filters.FavColors)>0 {query=query.Where(sq.Eq{"fav_color":filters.FavColors})}

I have recently started experimenting with sqlc. Most colleagues who I have shown it to also love it but indeed dynamic queries is where things could be improved.

In my specific use case, I often write APIs using Google'sAIP system which allow forfiltering in methods likeList.

This means that your SQL query is essentially based on user input. Writing a static query to cater for a situation like this can become quite hairy and somewhat difficult to maintain if you have a large amount of fields to filter on (Seethis article for more details on the subject).

What I have personally done to avoid this is relied on usinghttps://github.com/Masterminds/squirrel like@veqryn mentioned for these methods which works but means yet another library to pull in, 2 different ways of handling SQL code in the same code base and also more room for mistakes as the code is written by hand.

This is mostly meant to be a "here's the kind of problems I had and solutions I've come up with" post to hopefully allow for a more informed decision on the subject.

Want to share my "solution" to this problem here as well in case it might be useful for anyone else:

SELECT ...FROM ...WHERE<fixed condition>AND version= ifnull(sqlc.arg('version'), version)-- if version == NULL, then version = version, making it a NOOP

With that, sqlc generates aninterface{} field:

typeParamsstruct {Versioninterface{}}

Later in the code you can set theVersion field either as nil or as the pointer to the type:

arg:= internal.Params{Version:storage.Optional(p.Version),}

Our smallOptional helper here is defined as:

// Optional returns a non-nil pointer to v if v is not its zero value.funcOptional[Tcomparable](vT)*T {varzeroTifv==zero {returnnil}return&v}

But would be happy to have native support from sqlc here 🙏

Perhaps one solution could be to have asqlc.optional() function which will be rewritten to be a partial query in the end, such as:

SELECT * FROM tWHERE sqlc.optional(@a, col_a, "col_a = @a") AND (sqlc.optional(@b, col_b, "col_b > @b") OR sqlc.optional(@c, col_c, "col_c < @c"))

which would be rewritten to

SELECT a, b, c FROM tWHERE  col_a = col_a -- @a is null  AND (col_b > @b OR col_c = col_c) -- @c is null

If you don't care about null values in filtered fields then you may do something like this

Table:

CREATETABLEpublic.parcel (        order_idcharacter varying(255) DEFAULT''::character varyingNOT NULL,        shop_namecharacter varying(10) DEFAULTNULL::character varying,);

Query

-- name: ParcelsByFilters :manySELECT*FROM parcelWHERE    (sqlc.narg(shopName)::text isnullor shop_name=sqlc.narg(shopName)::text)and    (sqlc.narg(orderId)::text isnullor order_id=sqlc.narg(orderId)::text);

sqlc.narg means nullable argument. Sqlc will generate structure with pointer fields and you can fill it as you want

orderId:="XXX16549356748884"//shopName := "7507"filters:= repo.ParcelsByFiltersParams{Orderid:&orderId,//Shopname: &shopName,}parcels,err:=queries.ParcelsByFilters(context.Background(),filters)iferr!=nil {log.Fatal(err)}

Seems like there hasn't been any discussions on this since last year. Do people have new workarounds or have moved to another library for this problem?

I've built an example combining sqlc's type safety with Squirrel for dynamic queries. It intercepts the DB layer to replace static queries while keeping sqlc's models intact.
Repo:https://github.com/yiplee/sqlc-dynamic-query-example
Feedback welcome!