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

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

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

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.

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

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 :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!

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
}

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

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 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'))

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

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

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?

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.

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.

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

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.

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?

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

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.

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 🙏

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

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

Table:

CREATE TABLE public.parcel (
 order_id character varying(255) DEFAULT ''::character varying NOT NULL,
 shop_name character varying(10) DEFAULT NULL::character varying,
);

Query

-- name: ParcelsByFilters :many
SELECT * FROM parcel
WHERE
 (sqlc.narg(shopName)::text is null or shop_name=sqlc.narg(shopName)::text) and
 (sqlc.narg(orderId)::text is null or 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)
 if err != nil {
 log.Fatal(err)
}