Feature proposal: Query Fragments

[nickjackson]

trafficstars

What do you want to change?

I'd like to be able to define part of a query, and then reuse that in other queries. I've called it fragments.

This is handy as it reduces duplication in the query, but also helps reduce mistakes and duplication when passing arguments into queries where fragments are used.

-- name: WorkspaceCheck :fragment
workspace_id = sqlc.arg(workspace_id) AND livemode = sqlc.arg(livemode)

-- name: GetThing :one
SELECT * FROM things WHERE id = sqlc.arg(id) AND sqlc.fragment(WorkspaceCheck)

Ideally, I would like this to produce a params struct with an embedded set of params.

type GetThingParams struct {
  ID             string
  WorkspaceCheck WorkspaceCheckFragmentParams
}

The reason I'm in favour of embedding (rather than inlining) is because it makes it simpler to callers who are most likely abstracting out those params anyway. For example, we can make our auth library produce these params, and pass them into the query on a single line.

I don't really mind how or where fragments are defined, I went with the above as its just a familiar form and easy to explain. I imagine its a probably non-starter as I'm guessing it makes the query file un-parsable.

I've not looked at what it would take to build this, but I would definitely consider it if this was a likely-merge.

Thanks!

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

[nickjackson]

A colleague of mine has brought up a concern with this approach that I had not considered.

What happens to the generated FragmentParams if you were to use the same fragment across two different tables and the argument types are slightly different?

I guess the solution to this is to have a fragment param for every table, e.g. ThingWorkspaceCheckFragmentParams perhaps falling back to a single type if they are comparable.

[Emyrk]

I see this too as a way to reuse queries. An example being if you commonly join 2 tables. It is not ideal to have to copy this join across multiple queries if your select statements differ.

Ideally you could define a query as a fragment or something.

The current workaround is to define it as a view, but you cannot use things such as sqlc.embed in a view. :cry:

[kyleconroy]

Instead of adding a new sqlc.xxx function, I've been thinking about adopting the a templating system as a preprocessor step. Jinja2 supports macro definitions, which should solve your issue without us introducing any new special syntax. Your example you look the the following:

{% macro workspace_check %}
    workspace_id = sqlc.arg(workspace_id) AND livemode = sqlc.arg(livemode)
{% endmacro %}

-- name: GetThing :one
SELECT * FROM things WHERE id = sqlc.arg(id) AND {{ workspace_check() }}

Would that solve your issue?

[Emyrk]

I have done a very similar thing with Go templates before feeding to SQLc while experimenting.

The only downside to the macros is you lose SQL highlighting :cry:. But it does provide a general way to handle this :+1:

[mlucic]

Go templating (or something similar) in sqlc would be incredibly useful.

Consider the following: SELECT * FROM myTable {{- if sqlc.nslice('ids') != nil -}} WHERE id IN (sqlc.nslice('ids')) {{- end -}}

You could imagine in a more complicated query with a larger set of parameters this would be critical to avoid having to create a new query for every possible permutation.

[Nikola-Milovic]

Before SQLC I'd always use go templating with sqlx, it was a pretty good DX, so if anything I'd prefer go's native templating language.

[Emyrk]

Go templating (or something similar) in sqlc would be incredibly useful.

Consider the following: SELECT * FROM myTable {{- if sqlc.nslice('ids') != nil -}} WHERE id IN (sqlc.nslice('ids')) {{- end -}}

You could imagine in a more complicated query with a larger set of parameters this would be critical to avoid having to create a new query for every possible permutation.

I've done this approach as well, but you'd lose your source code being valid SQL. Right now that is a huge upside to SQLc.

[druvv]

Bumping this for visibility -- we use sqlc heavily and we are concerned about maintaining duplicated queries with slightly different WHERE clauses.

[Emyrk]

@druvv you can do CASE WHEN statements to not have to duplicate queries for different WHERE cases. You can see an example of how we do it at Coder: https://github.com/coder/coder/blob/6914862903b9ac4dd20629862c92806e2f8f8128/coderd/database/queries/workspaces.sql#L232-L237

[adamgen]

Sorry if I'm asking a trivial question, but why not use postgres functions?

[Emyrk]

Sorry if I'm asking a trivial question, but why not use postgres functions?

Does SQLc know how to inspect functions?

Regardless, a function is still much heavier than what is requested. A function requires a migration, and mutating that function requires a REPLACE the old funcition.

At present, all queries are generated client side, so making changes is trivial and does not require changing the DB schema.

[wilianto]

I believe this feature will be useful. I also have a usecase where I have three type of queries, list, count and sum.

SELECT * FROM bookings WHERE criteria_a = 'a' AND criteria_b = 'b'; 
SELECT COUNT(*) FROM bookings WHERE criteria_a = 'a' AND criteria_b = 'b';
SELECT SUM(amount) FROM bookings WHERE criteria_a = 'a' AND criteria_b = 'b';

I have more than 10 where condition on that queries. Maintaining those three queries sync is a bit cumbersome.