Proposal: allow additional SQL as function parameter for SELECT query

[RadhiFadlillah]

What do you want to change?

Background

Note: most of my experience is using MySQL and SQLite, so just assume that I know nothing about PostgreSQL. While on it, this is my first proposal, so please go easy on me 🥲

One of the most common feature request for CRUD and BI apps is to make an advanced data filter where the users can freely define the parameters to specify which data to be shown. Example of that data filter can be seen in spreadsheet apps like Excel or Libre Calc:

Currently, the suggested approach for dynamic query is using OR operator in WHERE clause like this:

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;

Unfortunately that approach won't work for advanced data filter, because:

• We simply can't predict the user input. What if users has multiple conditions for a single column? What if users want to have nested AND and OR condition? And so on.
• The query will get longer depending on the columns count. In the above example, we only have three columns and we only care for equal and like value. Now imagine if there are 9-10 columns, and we need to consider conditions for unlike or ranged values.
• As far as I know, that approach only work for PostgreSQL and not on MySQL or SQLite.

To solve this issue, I propose we allow additional SQL as parameter for :one and :many queries.

Current behavior

For example (playground), let's say I have query.sql like this:

-- Example queries for sqlc
CREATE TABLE IF NOT EXISTS product (
	id           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
	identifier   VARBINARY(20) DEFAULT NULL,
	category     VARCHAR(80)   NOT NULL,
	name         VARCHAR(80)   NOT NULL,
	qty          DECIMAL(20,4) NOT NULL,
	capital      DECIMAL(20,4) NOT NULL,
	price        DECIMAL(20,4) NOT NULL,
	specs        JSON          DEFAULT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY product_identifier_UNIQUE (identifier)
) CHARACTER SET utf8mb4;

-- name: FetchProducts :many
SELECT id, category, identifier, name, capital, price
FROM product;

-- name: FetchProduct :one
SELECT id, category, identifier, name, capital, price
FROM product
WHERE identifier = ?;

From this query, sqlc will generate following Go code:

const FetchProduct = `-- name: FetchProduct :one
SELECT id, category, identifier, name, capital, price
FROM product
WHERE identifier = ?
`

type FetchProductRow struct {
	// Omitted
}

func (q *Queries) FetchProduct(ctx context.Context, db DBTX, identifier null.String) (FetchProductRow, error) {
	row := db.QueryRowContext(ctx, FetchProduct, identifier)
	var i FetchProductRow
	// Omitted
	return i, err
}

const FetchProducts = `-- name: FetchProducts :many
SELECT id, category, identifier, name, capital, price
FROM product;
`

type FetchProductsRow struct {
	// Omitted
}

func (q *Queries) FetchProducts(ctx context.Context, db DBTX) ([]FetchProductsRow, error) {
	rows, err := db.QueryContext(ctx, FetchProducts)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []FetchProductsRow{}
	// Omitted
	return items, nil
}

As you can see, currently our query for FetchProduct and FetchProducts is run immediately by db.Query, making it impossible for us to define custom SQL filter.

Proposed solution

To allow additional SQL, what if we put the SELECT queries inside derived table subquery? Still using the query.sql above as source, here is how the generated Go code will look like:

type AdditionalSql struct {
	SQL  string
	Args []interface{}
}

func (q *Queries) FetchProduct(ctx context.Context, db DBTX, identifier null.String, as ...AdditionalSql) (FetchProductRow, error) {
	sql := FetchProduct
	args := []interface{}{identifier}
	if len(as) > 0 {
		sql = "SELECT * FROM (" + FetchProduct + ") t\n" + as[0].SQL
		args = append(args, as[0].Args...)
	}
	row := db.QueryRowContext(ctx, sql, args...)
	var i FetchProductRow
	// Omitted
	return i, err
}

func (q *Queries) FetchProducts(ctx context.Context, db DBTX, as ...AdditionalSql) ([]FetchProductsRow, error) {
	sql := FetchProducts
	args := []interface{}{}
	if len(as) > 0 {
		sql = "SELECT * FROM (" + FetchProducts + ") t\n" + as[0].SQL
		args = append(args, as[0].Args...)
	}
	rows, err := db.QueryContext(ctx, sql, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []FetchProductsRow{}
	// Omitted
	return items, nil
}

With that new AdditionalSql parameter, we can apply additional SQL query while fetching data. For example, here is how we will fetch the products using filter in the screenshot above:

query := `WHERE (
	category LIKE ?
	AND name LIKE ?
	AND capital < ?)`
args := []interface{}{
	"%Man%",
	"%Shirt%",
	500}
filter := AdditionalSql{SQL: query, Args: args}
products, err := FetchProducts(ctx, db, filter)

Pros and cons

The advantages of this method are:

• Making data filter is really easy now, since we can simply add whatever SQL queries that we want. Besides for filter, we can also use the additional SQL for other purpose, e.g. data sorting or limit.
• Derived table is a common database feature, which means this method can be used in MySQL and SQLite as well. It also works with CTE and as far as I know there are almost no performance penalty from using subquery.
• For sqlc users, since the AdditionalSql parameter is variadic, it means the generated code can be used as it is so there are no need to modify the existing code.
• For sqlc developers, (AFAIK) the only place we need to modify for this proposal is only in code template and unit tests, so hopefully it should be easy enough to implement.

For the cons, honestly I don't have any right now. I haven't make any tests, but I've made a private fork with this feature and use it for several months and so far there are no trouble though. However, I'm not really experienced with databases so I'm sure there are issues that I missed.

Related issues and discussions

• Support dynamic queries #364
• About GraphQL dynamic queries (questions for software selection) #580
• how to make a dynamic search #645
• Dynamic where clause #975
• how to make an advanced query  #1062
• Pgnation and filter #1908

What database engines need to be changed?

PostgreSQL, MySQL

What programming language backends need to be changed?

Go

[zakaria-chahboun]

Hi @RadhiFadlillah tell me more about your sql generator https://github.com/RadhiFadlillah/dbgen

[RadhiFadlillah]

Oh god, just pretend that doesn't exist 😆

I make it back in 2021 when MySQL support in sqlc is still not really good (IIRC sqlc still not supported CTE and subquery back then). In one hand, I'm not smart enough to contribute additional MySQL parser to sqlc. In other hand, I'm burned out writing CRUD command one by one. So, I decided to build my own SQL generator, as ugly as it is.

The main objective for that generator was to add support for CTE and subquery. The idea is simple: instead of parsing the SQL manually, we just need to run the SELECT query in MySQL then read the returned columns using rows.ColumnTypes function in stdlib.

So, here is how it works:

• User provide the query.sql as input, and an empty database that only used for generator.
• My dbgen run each SELECT queries in that database, then collects the returned columns using rows.ColumnTypes function.
• Generate Go code using the collected columns.

Fortunately, around October 2021 MySQL supports for sqlc is already good enough, so I stopped working on it. In 2023, there are no reasons to use that anymore since sqlc is superior in every aspect.