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
}

[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

[vamshiaruru-virgodesigns]

I'd also find this very useful. Just being able to add dynamic where clauses and order by clauses would make sqlc my goto library. I am considering ent instead because they have support for this.

[Ice3man543]

I wrote a library that supports such Dynamic queries based on the original idea of builder and wrapping ctx by @yiplee. https://github.com/projectdiscovery/sqlc-go-builder

The library uses vitess-sql-parser under the hood to parse MySQL select statements and rewrites the AST to safely add dynamic WHERE, ORDER, etc.

An example using it. Currently only supports MySQL.

package main

import (
	"fmt"

	sqlc "github.com/projectdiscovery/sqlc-go-builder"
)

func main() {
	builder := sqlc.New().
		In("id", 1, 2, 3).
		Where("name = ?", "John").
		Where("age > ?", 18).
        	Group("name, age").
		Order("name ASC, age DESC").
		Offset(10).
		Limit(5)

	query, args, err := builder.Build("select id from user_items")
	if err != nil {
		panic(err)
	}
	fmt.Printf("query= %s\n args= %v\n", query, args)
}

// Output:
// query= select id from user_items where id in (?, ?, ?) and `name` = ? and age > ? group by `name`, age order by `name` asc, age desc limit 10, 5
// args= [1 2 3 John 18]

Have tested out the library extensively using sqlmap to ensure there are no SQL injections and manual code reviews and everything seems safe. Would love others review on this

[go-aegian]

It does much like any other mapper, but it does not solve the problem when the where clause comes as a string that can be dynamically appended to a predefined where clause.

eg:

predefined sql
-- name ListPerson :many
select * from person where orgId='abc' and ($FILTER)

passed as argument
$FILTER = "(name='John' and Age>18) or (name='Mary' and age >=34)"

[vamshiaruru-virgodesigns]

Hi, do you know of a sql parser for postgres?

[go-aegian]

It's what sqlc project is based of off to parse the queries

go.mod
github.com/pganalyze/pg_query_go/v4 v4.2.1

[vamshiaruru-virgodesigns]

Thanks, unfortunately that uses C, so hard for me to include in my code without creating a whole bunch of other headaches.

[eerimez]

I find it utterly flabbergasting that in this day and age, sqlc is bereft of dynamic filters/ordering, a feature so elementary that it is an extremely common use case. It's akin to serving a sumptuous, gourmet meal but forgetting the cutlery. This oversight is not only confounding but irrefutably subverts the developer expectations for a Go database code generator. The Go programming language is closely associated with web applications and it is baffling how sqlc fails to accommodate something so fundamentally imperative to web application development. I urge the developers to reevaluate their roadmap and priorities, lest they continue to alienate and dishearten a user base craving what should be a standard offering.

[mvrhov]

So You are good at complaining. Complaining usually doesn't get you far. Or maybe your experience is different than mine and you get things done by complaining only.

How about doing something for the sqlc community. be proactive and not just complain and wait for others to do all the work:

• Provide a few suggestions on what would be a best way to implement that?
• Try to implement something for e.g PostgreSQL

[jwc-clinnection]

I find comments like these to be less than helpful. This is an open source project, and you have minimally two options readily at your disposal. Why not fork the project and implement a solution? Then, you could create a merge request. I suspect that authors of open source projects don't appreciate comments like this or ignore them all together.

Given the number of requests and proposed solutions to this request, it is clearly a needed feature. I found this discussion while looking for a solution to this problem myself. But, acting like a spoiled brat is not going to further your cause.

[eerimez]

I have to apologize for how that came off. Of all of the Go database solutions, I like sqlc the most. I was just frustrated that an import feature that is desperately needed is lacking. I was looking a gift horse in the mouth and it was rude of me.

[go-aegian]

"... The Go programming language is closely associated with web applications ..." I have to disagree with such a statement for obvious reasons.

Also I would recommend to read and study more in-depth about anything before making statements out of fear or lack of knowledge, humbleness arises from those who are able to overcome their fears and this might apply in life too.

[vietvudanh]

If I had to write something then I would complain the way you typed too. It's easy to get disappointed for something you have great hope for. I had troubles with where and order by too. Contributing codes and proposing solution for the problem would be nice, but sadly I had nothing.

[amanbolat]

I would recommend using go-jet in case you need dynamic sorting and filtering. Initially we considered sqlc as an option for one of the projects, but had the same requirements, when the user sends a complex query that will involve dynamic CTEs and joins of multiple tables. All those workarounds for sqlc mentioned above doesn't make the query dynamic. Using SQL case is not a solution.
Dynamic query is a mandatory requirement for any enterprise application, where the access policy based filter should be applied depending on the user attributes and roles.
Nevertheless, sqlc seems to have its own niche.

P.S.: I'm not an author of go-jet.

[ghandic]

I've found that even jet seems to be very complex with its types if you have an inner query, so much that a little verbosity from sqlc is acceptable, depends on the query obviously

[KevenGoncalves]

SQLC

Sqlc is a very good package, one of the best in go-lang world but it's lacks some features:

Dynamic query ( mainly for where, and order by)

We have some work around using when and case but it's not viable implementation and it's not documented, so the dev need to spend most of time figuring it out how to implement and in worst case you should write one more then 1 query just for the same thing, something like sqlc.where() and sqlc.orderBy() would be amazing

Slices for embed structs

Right now you only can just generate a struct not a slice of struct with slqc.embed(), would be amazing to group all structs in a slice when the id is the same for a join

Lack of functionality in sqlc.embed()

The slqc.embed() is a very good feature but have some problems: when a relation return null the queries return an error, and the sqlc.embed() doesn't support to choice which fields need to be returned from the table we want to select
something like sqlc.nembed() to allow null like sqlc.narg() would be awesome and sqlc.embed(table_1,(id, name))

[andrewmbenton]

We have been thinking a lot about "dynamic queries" but don't yet have a decision on a path forward. I know this is one of the longest outstanding feature requests, so we'd like to resolve it soon one way or another, so that the sqlc user community has clarity.

Slices for embed structs is under discussion here: #2643.

Handling NULL fields from a JOIN is discussed in this issue: #2348

[KevenGoncalves]

Unfortunately the Handling NULL fields from a JOIN in the discussion is not working for me :(

[andrewmbenton]

Unfortunately the Handling NULL fields from a JOIN in the discussion is not working for me :(

We may have a proposal to handle that without sqlc.embed() but we need to run a few tests first.

[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]

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.