Problem

Our current use of SQLc uses almost 0 joins throughout all of our queries. This causes excessive db round trips. The functionworkspaceData does 9 database calls to completely populate the returned workspace from the api. This can be reduced withjoins.

Whendbauthz is enabled by default, this compounds the issue as many objects require fetching related objects to run authorization. Examples:workspace builds,template versions, jobs, build parameters, ... .

Solutions(this PR talks about both)

Allowingqueries to leverage joins can reduce db round trips. The issue is how to handle these. We currently use SQLc, which would create a new model for each query and it becomes cumbersome. Additionally, we often have multipleSELECT queries for each datatype.

I have 2 proposals.

Keeping SQLc(originally investigated here)

If we want to keep SQLc, the best way we can do this is withviews. I am suggesting non-materialized views. So essentially these are saved queries in our postgres database that we can reference from SQLc.

Because views are saved in postgres, they require the same migration maintenance a table would.

A view is a "table" for SQLc, so a model is generated for it. In our.sql files, we just reference the view.

Abandon SQLc and use SQLx + Go Templates(an implementation here)

We use SQLc to generate Golang code from our sql. But this code isn't actually that complex in Golang if we use SQLx. And then we can use Go templates to build dynamic queries.

A very basic implementation of this makes our*.sql files look like this. Note the highlighting will be a bit off as we are mixing SQL and Go templates.

{{ define"workspace_builds_rbac" }}(SELECTworkspace_builds.*,workspaces.organization_idAS organization_id,workspaces.owner_idAS workspace_owner_idFROMworkspace_buildsINNER JOINworkspacesONworkspace_builds.workspace_id=workspaces.id){{ end }}-- To use the template above{{ define"GetWorkspaceBuildByID" }}SELECT*FROM {{ template"workspace_builds_rbac" }}WHEREid= @build_id{{ end }}

I investigatedIDE Highlighting, but the tl;dr is that just usingsql highlighting is likely the best. It will never be perfect with Go templates 😢.

How to use template query in golang?

I made a package calledsqxqueriers that handles templates and keeping our@param named parameters for easier to read sqlc. You can see thathere.

To call a query you made, you can use a generic function called sqlxGet for fetching one row:

The type embeds the sqlcWorkspaceBuild type. We would keep SQLc for generating models. We'd just move queries to this SQLx. Thedb struct tags are used for matching to columns. Obviously the query can be handled more manually if the tags do not match.

Comparison

Highligting

SQL template highlighting in vscode isn't that bad. In Goland it's pretty terrible.

Golang Code

SQLx + Templates requires more infrastructure code to support, but this also gives us opportunity to add in features. SQLc is slow to update and add features we need.

Joined Types

SQLc joined types are duplicated structs with identical fields. We can do anonymous embeds for template joined types.

I would really like to seedbauthz not adhere todb.Store and then we can do some better type handling at this layer to make types more consistent. You cannot insert into a view, so when updating or inserting data, you cannot return the joined data. Meaning 2 types will exist. Athin and ajoined.

Dynamic Queries

We currently useCASE WHEN statements for dynamic where clauses. This works fine, but is only supported inWHERE. Things not supported currently:

• Conditional sort order (ASC vs DESC)
• Conditional sort column
• Conditional updates, only update fields provided.Caused bug in v1