Just had a conversation with@kylecarbs in discord.

In summary, the views are a method that enables using joins in our SQL queries, which is powerful and can cut down on the number of DB calls we make.

The contention to pushing this PR through is maintaining the view, and defaulting to using joins.

Con: Maintaining the view

This PR adds a view ontop ofworkspace_builds calledworkspace_build_with_initiator. If a column is added toworkspace_build, that column isnot automatically added to the view. So your migration will look like this:

-- We must drop the old view first, then recreate it. We cannot just update the old view-- as the new view must have the same columns.DROPVIEW workspace_build_with_initiator;-- This alter table could be adding a column OR removing a column. In both cases, we will-- need to drop the view first.-- You cannot delete a column that has a dependency on it, and a view is a dependency.ALTERTABLE workspace_builds    ADD extra_columnint DEFAULT1NOT NULL;CREATEVIEWworkspace_build_with_initiatorASSELECT workspace_builds.*, coalesce(users.username,'unknown')AS initiator_usernameFROM workspace_buildsLEFT JOIN usersONworkspace_builds.initiator_id=users.id;

The biggest downside is that is adds some complexity to our migrations. The developer writing the migration needs to know which views to also update. They will likely have to copy theCREATE VIEW ... code block from migration to migration.

This is just the cost of the views.

Con: Defaulting to using joins

One downside is that all our queries now use joins. In the example above, the data being joined is only used by the UI. So internal usage doesn't require the join (at the moment). However, having the additional context could be nice for logging purposes, so it might not be a total waste.

I don't think join performance will be so bad that this is really an issue, but it can cause over-fetching.

The upside

• Less db calls as we can merge them into 1 with a join
• Still 1database.Type shared by all sqlc queries
• Join queries are reused, and only typed once.

Alternative (Status Quo)

The alternative is to do the status quo, where we make multiple db calls and merge the resulting structs.

• Query for workspace build
• Query for owner & initiator user's
• Merge in a goconvertWorkspaceBuild function.
  • Handling errors in convert is currently inconsistent. The SQL joins handle these more consistently.

Extra links

SQLc implementations that could help. But gated since these features do not exist yet :(.

• sqlc.embed() fork:feat: addsqlc.embed to allow model re-use sqlc-dev/sqlc#1615.
• This was a design sqlc dropped, buttemplate: workspace_build_initiator support for putting the views in the queries.sql
  • Downside is that we have to copy the view's SELECT query with the join to every query.

I am going to close this PR in favor of the status quofor now. I will work on this PR to include some other views that could benefit and get a larger team decision about this.

This is a repo design and maintenance change, so will be a discussion. Until then, we still need the feature, so I'll do it in another PR