Support dynamic queries

[mehdijoafshani]

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.

[dharmjit]

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.

[kyleconroy]

@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?

[mehdijoafshani]

Thanks for ur response @kyleconroy

I'm surprised that the dynamic filters are unknown.

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

create table t1
(
    field_fk uuid,
    field1    varchar(100),
    field2    varchar(100),
);

I need to select on t1, 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 t1 where field_fk =$1
select * from t1 where field_fk =$1 AND field1 = $2
select * from t1 where field_fk =$1 AND field1 like $2
select * from t1 where field_fk =$1 AND field2 = $2
select * from t1 where field_fk =$1 AND field2 like $2
select * from t1 where field_fk =$1 AND field1 = $2 and field2 = $3
select * from t1 where field_fk =$1 AND field1 like $2 and field2 = $3
select * from t1 where field_fk =$1 AND field1 = $2 and field2 like $3
select * from t1 where field_fk =$1 AND field1 like $2 and field2 like $3

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

order by field1 ASC
order by field1 DESC
order by field2 ASC
order by field2 DESC

Which is 9*4 = 36 different possible queries for a table with 2 fields.
For where clause filters we can leverage CASE WHEN and sqlc named parameters, but I am not sure about CASE 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 :many
SELECT t1 Where 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 with optional 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.

And optional_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

[kyleconroy]

@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.

CREATE TABLE foo (
            fk     uuid,
            bar    varchar(100),
            baz    varchar(100)
);

-- name: FilterFoo :many
SELECT * FROM foo
WHERE fk = @fk
  AND (CASE WHEN @is_bar::bool THEN bar = @bar ELSE TRUE END)
  AND (CASE WHEN @lk_bar::bool THEN bar LIKE @bar ELSE TRUE END)
  AND (CASE WHEN @is_baz::bool THEN baz = @baz ELSE TRUE END)
  AND (CASE WHEN @lk_baz::bool THEN baz LIKE @baz ELSE TRUE END)
ORDER BY
  CASE WHEN @bar_asc::bool THEN bar END asc,
  CASE WHEN @bar_desc::bool THEN bar END desc,
  CASE WHEN @baz_asc::bool THEN baz END asc,
  CASE WHEN @baz_desc::bool THEN baz END desc;

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

Please let me know if I've missed anything!

package main

import (
	"context"
	"database/sql"
	"log"

	_ "github.com/lib/pq"
	"github.com/google/uuid"
)

func run(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 comparing
		Fk:  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 comparing
		IsBar:  true,
		// IsBaz:  true,

		// Configured the order of the results
		BarAsc: true,
		// BarDesc: true,
	})
	if err != nil {
		return err
	}
	for _, row := range rows {
		log.Printf("%d: bar:%s baz:%s\b", row.Sid, row.Bar.String, row.Baz.String)
	}
	return nil
}

func main() {
	db, err := sql.Open("postgres", "dbname=example sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	if err := run(context.Background(), db); err != nil {
		log.Fatal(err)
	}
}

[theenoahmason]

Could you please provide syntax when using with MySQL?

[bnllb]

you just need to remove all "::bool"

[tadejsv]

This approach breaks down when bar and baz are of different (incompatible) types - specifically this hapens in the WHERE statement.

[andrei-dascalu]

@bnllb this doesn't seem to work in mySQL. Eg: SELECT * FROM Calendar c WHERE c.Active = 'true' AND (CASE WHEN @bar THEN c.UniqueKey IN (bar) ELSE TRUE END) doesn't result in error, but the method generated provides no params as it doesn't recognize any parameters.

WHERE c.Active = 'true'
AND (CASE WHEN ? != "" THEN c.UniqueKey IN (?) ELSE TRUE END)``` does work through, but the result method accepts 2 params: one being a `Column1` and the other `UniqueKey`

[mehdijoafshani]

@kyleconroy Thank you a lot for the explanation and your time amigo !
Actually we tried providing dynamic queries using CASE WHERE as a POC but the benchmarks showed considerable impact on query performance and we decided not to use CASE 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 ?

[arddor]

Some input to the discussion:

This form of static query with dynamic filtering is generally regarded as one of the worst performance anti-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 a seq_scan in all cases (no indexes will be used).

With respect to GO 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 of postgres and Go 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.

[ovadbar]

I am not sure this is the same for postgresql as I think its optimizer optimizes the queries, however ordering with a case statement will not be able to use the indexes which critical.

I might be mistaken because I don't usually write my queries with the case statement work around. For example the following where I don't expect baz to ever have a value of an empty string

SELECT * FROM foo
WHERE fk = @fk
  AND (bar = @bar OR is_bar::bool)
  AND (bar LIKE @barlike OR @lk_bar::bool)
  AND (baz = @bar OR @baz="")
  AND (baz LIKE @bazlike OR @bazlike = "")

[dharmjit]

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.

[kyleconroy]

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 :many
SELECT * FROM foo
WHERE fk = @fk
ORDER BY
  CASE WHEN sqlc.switch('sort', 'bar_asc') THEN bar END asc,
  CASE WHEN sqlc.switch('sort', 'bar_desc') THEN bar END desc,
  CASE WHEN sqlc.switch('sort', 'baz_asc') THEN baz END asc,
  CASE WHEN sqlc.switch('sort', 'baz_desc') THEN baz END desc;

package db

import (
	"context"
	"database/sql"
	"log"


	"github.com/google/uuid"
)

type Sort string

const (
    SortBarAsc  Sort = "bar_asc"
    SortBarDesc  Sort = "bar_desc"
    SortBazAsc  Sort = "baz_asc"
    SortBazDesc  Sort = "baz_desc"
)

type FilterFooParams struct {
    Fk    uuid.UUID
    Sort SortEnum
}

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.

[ghandic]

Is this on the roadmap?

[ovadbar]

The problem with this is it fixes the sort options. For example lets say I had a table like order_item, and It had a price and quantiy column, and I wanted to sort by quantiy * price.

[mportugal]

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

[AndrewRayCode]

I'm running into this issue too. This is currently impossible on MySQL with SQLC. And the behavior is weird. Doing something like

SELECT *
FROM foo
WHERE (CASE WHEN ? THEN `status` IN (?) ELSE TRUE END)

Produces this error when running sqlc generate:

unsupported reference type: < nil >

Except sqlc returns a success status code despite the error/bug, and generates the queries.sql.go and puts exactly ... WHEN ? ... in the query body

So @kyleconroy it doesn't look like sqlc has any support for dynamic queries, only that postgres does and sqlc lets you pass in variables postgres can use.

It's also unclear to me why we can't type query inputs in the msql driver, it's undocumented why mysql uses question mark ? vs postgres uses dollar sign $ and maybe @ too, and how ::typename comes into play here.

In MySQL with sqlc making a query like

AND (CASE WHEN @is_something::text

raises the error

queries.sql:82:24: syntax error near "::text

I think mysql users are out of luck here and have to deal with combinatorial explosion for now

[ovadbar]

I think mysql is a little easier.

SELECT *
FROM foo
WHERE IF(?,`status` IN (?),1)

Or you can do it like

SELECT *
FROM foo
WHERE (`status` IN (?) OR ?)

[timruffles]

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 :many
SELECT * FROM foo
WHERE fk = @fk
ORDER 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:

func FilterFoo(ctx context.Context, params FilterFooParams) {
  // pick the sql to use
  sql := ""
  switch params.Sort {
     case SortBarAsc: sql = filterFooSQL_a
     // ... etc
  }
  // run query
}

const filterFooSQL_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.g CriteriaA 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 using sqlc: 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!

[piyongcai]

@timruffles
why?
You can generate multiple sql statements, then gen one func use different sql by args and sorts.

[timsolov]

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 generate if-blocks for each column name from table and construct arguments for QueryRow func.

CREATE TABLE users (
    user_id     UUID   PRIMARY KEY NOT NULL,
    email       VARCHAR(256)  NOT NULL,
    first_name  VARCHAR(50)   NOT NULL,
    last_name   VARCHAR(50)   NOT NULL,
    created_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

const userColumns = `-- name: userColumns :one-columns
SELECT * FROM users
WHERE user_id = $1 LIMIT 1
`

func (q *Queries) userColumns(ctx context.Context, userID uuid.UUID, columns ...string) (User, error) {
	s := "SELECT "+strings.Join(columns, ",")
	var i User
	args := make([]interface{},len(columns))
	if exists(columns,"user_id") {
		args = append(args, &i.UserID)
	}
	if exists(columns, "email") {
		args = append(args, &i.Email)
	}
	if exists(columns, "first_name") {
		args = append(args, &i.FirstName)
	}
	if exists(columns, "last_name") {
		args = append(args, &i.LastName)
	}
	if exists(columns, "created_at") {
		args = append(args, &i.CreatedAt)
	}
	if exists(columns, "updated_at") {
		args = append(args, &i.UpdatedAt)
	}
	row := q.db.QueryRow(ctx, strings.Replace(userColumns, "SELECT *", s,1), userID)
	err := row.Scan(args...)
	return i, err
}

func exists(ss []string, s string) bool {
	for i := 0; i < len(ss); i++ {
		if s==ss[i] {
			return true
		}
	}
	return false
}

[Smithx10]

Is there a PR or Branch that has this behavior?

[yiplee]

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

Any feedback or suggestion is welcome :)

[jwc-clinnection]

I found your query builder while researching dynamic query solutions for sqlc. However, I can' quite seem to figure out how to use it.

[akhmadnurmuhammad]

Try this one

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

[go-aegian]

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)
...
}

[mvrhov]

I don't get it. The queries are written with $1 or ? in them. So this means that the data is bound by the sql driver to the proper place. and the driver knows if the input data is string and calls proper quote function.. Or driver prepares the statement and then binds parameters.

[go-aegian]

@kyleconroy

I proposed these approaches (back in Feb 2023 - tickets listed below), for the simple reason that this will solve when the where clause comes predefined as a string, the sql-injection issue will have to be addressed by the implementing person to parse the sql string before sending it as parameter, but if you can address these tickets fairly quick it will at least let us take care of the choking point which is currently not supporting dynamic where clause and/or order by.

#2060 - dynamic where clause
#2061 - dynamic order by clause

Please let us know when these could be addressed, as we look forward to at least have these bare minimum enhancements done

[jwc-clinnection]

I have implemented @go-aegian proposed solution in pull request #2343

[ovadbar]

So I think I have a similar solution that prevents sqlc injection. Instead of having a string like $FILTER. I allow a function sqlc.dynamic which maps to an interface that has a function that returns a string and a slice of arguments (For postgresql it. will be a function that accepts an integer which would be the next number after the dollar sign) for mysql it doesn't accept the integer.
This function can be used multiple times so in a where clause or an order by clause. This link provides some examples of how this would work. This link shows the added dynamic util.

Now I do not have any types that meet the definition of the interface, however the mysql one is trivial. The postgresql one could be a struct that accepts a string in a mysql format and a variadic of interfaces. In the ToSql function replace the question marks with the appropriate dollar symbols.

[ovadbar]

I decided to create a pull request for this #2859

[abh]

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 bar from t where foo in (?,?)

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

      ... from t where foo in (sqlc.arg('foo'),sqlc.narg('foo_2'))

[ovadbar]

I think sqlc has a sqlc.slice option for mysql. And you can use arrays for postgresql. See https://docs.sqlc.dev/en/latest/howto/select.html#passing-a-slice-as-a-parameter-to-a-query

[andrei-dascalu]

I'm wondering if there's any news on possible support for this?

I have cases where I'd like to dynamically pass ORDER BY field and direction (eg: ORDER BY sqlc.arg("sortByField") sqlc.arg("sortByDirection") ) as well as LIMIT (eg: LIMIT sqlc.arg("offset"), sqlc.arg("limit"))

I've tried something like

SELECT * FROM Calendar c
WHERE c.Active = 'true'
AND (CASE WHEN sqlc.arg("UseFilter") > 0 THEN c.IdKey IN (sqlc.slice('uniqueKeys')) ELSE TRUE END)
ORDER BY
    CASE WHEN sqlc.arg("created_asc") > 0 THEN c.CreateDate END ASC,
    CASE WHEN sqlc.arg("created_desc") > 0 THEN c.CreateDate END DESC
LIMIT ?,?;

which may do the job but it seems quite a drag to CASE all the possible combinations

[andrewmbenton]

We have been thinking quite a lot about this, but haven't made a decision yet on a path forward. I agree it's a drag to CASE all of the combinations, but that's the most viable workaround at the moment. Of course this only really works for a small number of parameters.

[daison12006013]

after using sqlc I don't wanna go back to use gorm.

Will this be feasible in the future, something like this?

Raw:

SELECT * FROM users
WHERE sqlc.condition("filterUuid", "uuid = ?", true)
ORDER BY sqlc.condition("sortByCreatedAt", "created_at desc", "created_at asc")
LIMIT ?, ?;

Transpile under the query string:

stmt := "SELECT * FROM users WHERE {{filterUuid}} ORDER BY {{sortByCreatedAtDesc}} LIMIT ?, ?;"

// replace the {{filterUuid}} with the value
if arg.X.filterUuid {
    // use the 2nd argument
} else {
    // use the 3rd argument
}

// replace the {{sortByCreatedAt}} with the value
if arg.X.sortByCreatedAt {
    // use the 2nd argument
} else {
    // use the 3rd argument
}

Those arguments shall be:

• 1st argument to check if we're going to filter
• 2nd argument to be the real query
• 3rd argument shall be the default

Concerns: though might be prone to injections

[batazor]

The discussion is three years old, perhaps we should add support for custom generators? For example, it is done in a few packages for working with OpenAPI. This will allow people to generate additional methods with custom logic without interfering with thinking about your solution

[batazor]

although plugins are probably designed for this purpose, I'll try experimenting with them.

[mvrhov]

IMHO, supporting FIQL would solve things: Go implementation and author also provides a package that converts AST to SQL. Or Go implementation of RSQL

[piavgh]

I didn't want to use Gorm, so I chose sqlc.

But now I'm facing this problem with my ORDER BY query.
I tried this solution #830 (comment) but I got got error ERROR: CASE types text and integer cannot be matched (SQLSTATE 42804) or something like that because my columns are not the same type

So I guess the best solution at the moment is to use another SQL builder until sqlc can resolve this issue, right?

[ovadbar]

You can do something like this

ORDER BY 
CASE WHEN sqlc.arg(int_order)::boolean THEN intcol ELSE 1 ASC, 
CASE WHEN sqlc.arg(str_order)::boolean THEN strcol ELSE "" ASC

It gets ugly though.

[piavgh]

@ovadbar : thanks. So this is the only way at the moment, right?

[ovadbar]

As far as I know.

I forgot the END for the caste statements. But with the current version of sqlc it is the only way. I tend to use something like mastermind/squirrel for dynamic ordering just because of how ugly sqlc looks.

I think there are a couple of proposals for how to allow dynamic sql but it doesn't look like there will be a decision any time soon.

[jwc-clinnection]

As some one that have contributed both code and financially to this project, I have great hope for the future of sqlc and am eager to see how this problem is solved. However, in the mean time, we had do move on with xo/xo. Its not an ideal solution, but we were able to safely solve the dynamic query problem with a way that is supported by xo.

[seanlaff]

I wrote a proposal #2061 (comment) for a sqlc.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 of ORDER BY without needing to solve the larger architectural issue of "dynamic queries". Thoughts? This is something I'd be willing to contribute.

[kcmvp]

dynamic sql is a very important feature for database 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 implement dynamic feature as https://mybatis.org/mybatis-3/dynamic-sql.html.

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

[glerchundi]

@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 :)

[zack-littke-smith-ai]

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?

[roveo]

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

-- name: GetUsers :many
select u.*
from users as u
{{ if .ManagerName }}
join managers as m
  on m.id = u.manager_id and m.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 in text/template.

[alecguintu]

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 :many
SELECT id, foreign_id, name, created_at
FROM this_table
WHERE true;

-- name: FilterThisTable :many
SELECT id, foreign_id, name, created_at
FROM this_table
WHERE 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);

when sqlc 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 need
	ForeignID    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 passed
	jarg, _ := json.Marshal(arg)
	var narg pgx.NamedArgs
	json.Unmarshal(jarg, &narg)

	rows, err := q.db.Query(ctx, query, narg)
	// End of added part
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []FilterThisTableRow
	for rows.Next() {
		var i FilterThisTableRow
		if 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 added omitemptys so we can pass arguments optionally, and on the query method, we added the query param and for the args, 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 of DynamicFilterThisTableParams but the query method can stand alone since we are using pgx's named params.

Usage then afterwards is:

var filters database.DynamicFilterThisTableParams
if 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 queries
s.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.

[hieunguyen1815]

this idea is actually workable

[maxmanuylov]

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 :many
SELECT *
FROM table
WHERE a = @a
  AND b = @b -- :if @b
  AND c = @c -- :if @c
ORDER 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, only a, b and idDesc params, the actual query to be executed becomes this:

-- name: SearchQuery :many
SELECT *
FROM table
WHERE a = @a
  AND b = @b -- :if @b
ORDER 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?

[veqryn]

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

Right now, it is using github.com/Masterminds/squirrel (an SQL builder), imported as sq.
In real life, it gets much more complicated with 10s of if statements. If I used a CASE statement for each if that we want, we'd have the ugliest and worst performing generated query imaginable.

I believe @maxmanuylov's proposed 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 (d DAO) 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")

	if len(filters.Names) > 0 {
		query = query.Where(sq.Eq{"name": filters.Names})
	}
	if filters.Active != nil {
		query = query.Where(sq.Eq{"active": *filters.Active})
	}
	if len(filters.FavColors) > 0 {
		query = query.Where(sq.Eq{"fav_color": filters.FavColors})
	}

[alethenorio]

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's AIP system which allow for filtering in methods like List.

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 (See this article for more details on the subject).

What I have personally done to avoid this is relied on using https://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.

[fraenky8]

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 an interface{} field:

type Params struct {
  Version interface{}
}

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

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

Our small Optional helper here is defined as:

// Optional returns a non-nil pointer to v if v is not its zero value.
func Optional[T comparable](v T) *T {
	var zero T
	if v == zero {
		return nil
	}
	return &v
}

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

[pengux]

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

SELECT * FROM t
WHERE 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 t
WHERE
  col_a = col_a -- @a is null
  AND (col_b > @b OR col_c = col_c) -- @c is null