I missed this too. However, I've found you can work around it pretty easily with clauses like CASE WHEN @field != "" THEN column = @field ELSE true END.

Example from the sqlc creator (https://github.com/sqlc-dev/sqlc/discussions/364#discussionc...):

  -- 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 want Jet [0], as mentioned in another comment. I did an extensive survey of golang SQL interfacing libraries before settling on Jet for our team. It's certainly not without warts, but it's got the correct type of API (query builder that maps directly to SQL) and the struct mapping is pretty flexible.

sqlc lacking support for dynamic query generation is just absolutely baffling. Composition of query fragments is, like, one of the main reasons you use a query builder - it's very hard to do in raw SQL! If you don't need that feature, why are you even messing around with SQL code on the application side? Just write and call stored procedures in the database! People will think you're a time traveler from the 1980's but it works!

Golang's SQL ecosystem is... pretty miserable, really. I mean, I look at what jOOQ can do and I weep at the state of things over in go. In golang everything goes through database/sql because that's the "standard" solution, but database/sql is a huge mess with a long track record of disastrous bugs (like the possibility of accidentally running queries outside of the intended transaction) that makes it very hard to take advantage of feature-rich databases like postgres. pgx (postgres connection library) is actually quite good when you use its native API, but because everything has to go through the database/sql interface it gets pretty crippled with a lot of annoyances (try mapping postgres arrays or jsonb documents into structs and you'll see what I mean).

[0]: https://github.com/go-jet/jet

Not everything. Most SQL databases differ enough that it's worth using bindings specific to them. For example https://github.com/go-llsqlite/crawshaw for SQLite.

I can't get behind something like sqlc because if it doesn't support your language or SQL dialect or the feature you need you're worse off than not using it.

What I'm saying is there's an unpleasant tradeoff here. If you use Jet (or whatever other query generation library or ORM or what have you), you get back some sort of query object. In order to execute that query object and map the resulting rowset to some golang data structure, you almost invariably have to go through database/sql's API, because the closest thing golang has to something like JDBC and so that's the interface most query generation libraries will use.

If you want to use a database-specific connector API, like pgx for postgres for example, you usually have to roll your own query execution (including parameter binding) and quite a lot of the result mapping too. I'd expect that to be a significant amount of work, but what's worse is that having that convenience done for you is a big reason for using a library like Jet in the first place.

So you're either stuck with the limitations of database/sql, or you don't get to enjoy a lot of the benefits that a mature database library brings. I don't like it.

> try mapping postgres arrays or jsonb documents into structs and you'll see what I mean

I ended up adding custom value types to wrap our JSON (actually Protobuf) values: https://gist.github.com/Cyberax/07486a2264e29d95ed8c67e002f9... - we codegenerate them from Protobuf descriptions.

Honestly I'm not convinced about using anything else but actual SQL. I'm not an SQL expert and find it very helpful to be able to copy/paste SQL code between the app and a DB client.

If Jet provided a tool to translate their API to/from SQL I might consider it though.

I may be misunderstanding you, but at least for the "to SQL" part I think that's just

  someJetStatement.DebugSql()

All Jet-generated Statement objects have the .DebugSql() method. It returns the generated query as a string with all the bound parameters inlined[0] so you can just print it or grab it with the debugger and copypaste it into your query console.

[0]: that is, it translates the query it'd actually execute, which would look like

  WHERE foo = $1

into

  WHERE foo = 'bound parameter value'

with some rudimentary escaping to avoid the most obvious SQL injection problems (don't use it to actually run queries in production, obviously!!).

That's good but what about the other way around?

I mean if I start writing a complex query in a DB client and then want to translate it to Jet.

No automated way to do that, I'm afraid. That said, most of the Jet statement builder API maps directly to SQL keywords, so it's usually straightforward. Still, some specific things are pretty awkward (CTE's come to mind).