René's URL Explorer Experiment


Title: Use postgres views to reduce database calls in api (allow `JOIN`s in pg queries) · Issue #2201 · coder/coder · GitHub

Open Graph Title: Use postgres views to reduce database calls in api (allow `JOIN`s in pg queries) · Issue #2201 · coder/coder

X Title: Use postgres views to reduce database calls in api (allow `JOIN`s in pg queries) · Issue #2201 · coder/coder

Description: This is just tracking a suggestion and making a place for discussion. Branch that implements this suggestion: stevenmasley/build_initiator_username PR that started conversation: #2137 Enable use of JOIN in sqlc queries Currently the UI r...

Open Graph Description: This is just tracking a suggestion and making a place for discussion. Branch that implements this suggestion: stevenmasley/build_initiator_username PR that started conversation: #2137 Enable use of...

X Description: This is just tracking a suggestion and making a place for discussion. Branch that implements this suggestion: stevenmasley/build_initiator_username PR that started conversation: #2137 Enable use of...

Opengraph URL: https://github.com/coder/coder/issues/2201

X: @github

direct link

Domain: github.com


Hey, it has json ld scripts:
{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Use postgres views to reduce database calls in api (allow `JOIN`s in pg queries)","articleBody":"_This is just tracking a suggestion and making a place for discussion._\r\n\r\nBranch that implements this suggestion: [`stevenmasley/build_initiator_username`](https://github.com/coder/coder/tree/stevenmasley/build_initiator_username)\r\n\r\nPR that started conversation: https://github.com/coder/coder/pull/2137\r\n\r\n---\r\n\r\n# Enable use of `JOIN` in sqlc queries\r\n\r\nCurrently the UI requires additional information regarding data structures returned by the database. An example is the `workspace_build` table does not include the following fields that the UI wants and uses [`convertWorkspaceBuild`][convertWorkspaceBuild] to add these fields.\r\n\r\n- workspace owner (id & name)\r\n- initiator name\r\n- workspace name\r\n- ~~provisioner job~~ (although included, going to not JOIN this data in right now)\r\n\r\nThis requires 2 extra database calls (fetch users, and workspace). Ideally we could fetch this information with a `JOIN`.\r\n\r\n```sql\r\n-- Make the SELECT a VIEW\r\nCREATE VIEW workspace_build_with_names AS\r\nSELECT\r\n    -- coalesce is used because technically the joins do not guarantee a value.\r\n    -- If we setup proper foreign keys, we can remove the coalesce.\r\n\tcoalesce(initiator_user.username, 'unknown') AS initiator_username,\r\n\tcoalesce(workspaces.owner_id, '00000000-00000000-00000000-00000000') AS owner_id,\r\n\tcoalesce(owner_user.username, 'unknown') AS owner_name,\r\n\tcoalesce(workspaces.name, 'unknown') AS workspace_name,\r\n        -- This information also seems helpful for UI purposes and is used in some areas of the UI.\r\n\tcoalesce(templates.id, '00000000-00000000-00000000-00000000') AS template_id,\r\n\tcoalesce(templates.name, 'unknown') AS template_name,\r\n\tcoalesce(templates.active_version_id, '00000000-00000000-00000000-00000000') AS template_active_version,\r\n\tworkspace_builds.*\r\nFROM workspace_builds\r\n\t\t LEFT JOIN users AS initiator_user ON workspace_builds.initiator_id = initiator_user.id\r\n\t\t LEFT JOIN workspaces ON workspaces.id = workspace_builds.workspace_id\r\n\t\t LEFT JOIN users AS owner_user ON workspaces.owner_id = owner_user.id\r\n\t\t LEFT JOIN templates ON workspaces.template_id = templates.id;\r\n```\r\n\r\nThe solution to using joins is to make a migration that makes the above a VIEW in our database that can be reused. This simplifies all the queries around `workspace_builds` as we do not need to copy the `JOIN` more than once (DRY). \r\n\r\n# Downsides to views\r\n\r\n## Maintaining the view\r\n\r\nIf a column is added to `workspace_build`, that column is **not** automatically added to the view. So your migration will look like this:\r\n\r\n```sql\r\n-- We must drop the old view first, then recreate it. We cannot just update the old view\r\n-- as the new view must have the same columns.\r\nDROP VIEW workspace_build_with_initiator;\r\n\r\n-- This alter table could be adding a column OR removing a column. In both cases, we will \r\n-- need to drop the view first.\r\n-- You cannot delete a column that has a dependency on it, and a view is a dependency.\r\nALTER TABLE workspace_builds\r\n    ADD extra_column int DEFAULT 1 NOT NULL;\r\n\r\nCREATE VIEW workspace_build_with_names AS\r\nSELECT -- ... Copy the Select query again ...\r\n```\r\n\r\nThe biggest downside is that is adds some complexity to our migrations. The developer writing the migration needs to know which views to also update. Postgres will fail if a view is a dependency and the underlying table is changed. They will likely have to copy the `CREATE VIEW ...` code block from migration to migration.\r\n\r\nThis is just the cost of the views.\r\n\r\n### Con: Defaulting to using joins\r\n\r\nOne 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.\r\n\r\nI don't think join performance will be so bad that this is really an issue, but it does cause over-fetching. \r\n\r\nIf we use the [sqlc.embed][embed] fork of sqlc, we can make db calls with the join, and some db calls without the join. Having this mix can prevent overfetching if we hit performance issues. Using the `embed` means we can nest data-structs and have the UI data-structure be something like:\r\n\r\n```golang\r\ntype WorkspaceBuildWithNames struct {\r\n  // The backend can work exclusively with 'WorkspaceBuild' type. So calling the FE version of the call\r\n  // is still usable for BE purposes.\r\n  WorkspaceBuild\r\n  OwnerName string\r\n  // .. The rest of the fields\r\n}\r\n```\r\n\r\n## The upside \r\n\r\n- Less db calls as we can merge them into 1 with a join\r\n- Still 1 `database.Type` shared by all sqlc queries\r\n- Join queries are reused, and only typed once as a view.\r\n- Filter options can use names rather than ids. (owner_name vs owner_id from a user perspective)\r\n\r\n## Alternative (Status Quo)\r\n\r\nThe alternative is to do the status quo, where we make multiple db calls and merge the resulting structs. \r\n\r\n- Query for workspace build\r\n- Query for owner & initiator user's\r\n- Merge in a go `convertWorkspaceBuild` function.\r\n  - Handling errors in convert is currently inconsistent. The SQL joins handle these more consistently.\r\n\r\n### Extra links\r\n\r\nSQLc implementations that could help. But gated since these features do not exist yet :(.\r\n\r\n- `sqlc.embed()` fork: https://github.com/kyleconroy/sqlc/pull/1615. \r\n- This was a design sqlc dropped, but [`template: workspace_build_initiator`](https://github.com/kyleconroy/sqlc/issues/112) support for putting the views in the queries.sql\r\n  - Downside is that we have to copy the view's SELECT query with the join to every query. \r\n\r\n[embed]: https://github.com/kyleconroy/sqlc/pull/1615\r\n[convertWorkspaceBuild]: https://github.com/coder/coder/blob/fff59ef6ad4e1d9026b228e8d2164004a5c7ff60/coderd/workspacebuilds.go#L524-L524","author":{"url":"https://github.com/Emyrk","@type":"Person","name":"Emyrk"},"datePublished":"2022-06-09T14:04:10Z","url":"https://github.com/coder/coder/issues/2201"}

react-profiling0
react-import-mapreact,react-dom,react-dom/client,react-dom/profiling,react-is,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@b8488ee2c322
route-pattern/_view_fragments/issues/show/:user_id/:repository/:id/issue_layout(.:format)
route-controllervoltron_issues_fragments
route-actionissue_layout
fetch-noncev2:3a5cf5c0-7525-30bd-d773-8975fd7051fb
current-catalog-service-hash81bb79d38c15960b92d99bca9288a9108c7a47b18f2423d0f6438c5b7bcd2114
request-idE31C:A71F9:174E24A:1D5CFDF:6B01756E
html-safe-nonce665ac2b4e4f0990893070185841826a67f42c64deb6ebd21445e96a3d66a99b4
visitor-payloadeyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJFMzFDOkE3MUY5OjE3NEUyNEE6MUQ1Q0ZERjo2QjAxNzU2RSIsInZpc2l0b3JfaWQiOiIzMzAwNjc2NzgxMDE4ODA1NjE0IiwicmVnaW9uX2VkZ2UiOiJpYWQiLCJyZWdpb25fcmVuZGVyIjoiaWFkIn0=
visitor-hmac59968970ed8d66a3e11d6f7477fb0972c970615616c53d76de7a05773037e520
hovercard-subject-tagissue:1266186795
github-keyboard-shortcutsrepository,issues,copilot
google-site-verificationApib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I
octolytics-urlhttps://collector.github.com/github/collect
analytics-location///voltron/issues_fragments/issue_layout
fb:app_id1401488693436528
apple-itunes-appapp-id=1477376905, app-argument=https://github.com/_view_fragments/issues/show/coder/coder/2201/issue_layout
twitter:imagehttps://opengraph.githubassets.com/7ae9b79e1b8d0856bd203399742c136bcc4a82ca0d1d476d7522d16d19e1a0e2/coder/coder/issues/2201
twitter:cardsummary_large_image
og:imagehttps://opengraph.githubassets.com/7ae9b79e1b8d0856bd203399742c136bcc4a82ca0d1d476d7522d16d19e1a0e2/coder/coder/issues/2201
og:image:altThis is just tracking a suggestion and making a place for discussion. Branch that implements this suggestion: stevenmasley/build_initiator_username PR that started conversation: #2137 Enable use of...
og:image:width1200
og:image:height600
og:site_nameGitHub
og:typeobject
og:author:usernameEmyrk
hostnamegithub.com
expected-hostnamegithub.com
Nonecc5dfdd32f62ebd3223cab3af3aa2b268d3605218237326a48a99d1efd89495c
turbo-cache-controlno-preview
go-importgithub.com/coder/coder git https://github.com/coder/coder.git
octolytics-dimension-user_id95932066
octolytics-dimension-user_logincoder
octolytics-dimension-repository_id440752086
octolytics-dimension-repository_nwocoder/coder
octolytics-dimension-repository_publictrue
octolytics-dimension-repository_is_forkfalse
octolytics-dimension-repository_network_root_id440752086
octolytics-dimension-repository_network_root_nwocoder/coder
turbo-body-classeslogged-out env-production page-responsive
disable-turbofalse
browser-stats-urlhttps://api.github.com/_private/browser/stats
browser-errors-urlhttps://api.github.com/_private/browser/errors
release8c77c9e8439031ad54c75a250694842892f693e1
ui-targetfull
theme-color#1e2327
color-schemelight dark
react-app-nameissues

Links:

Skip to contenthttps://github.com/coder/coder/issues/2201#start-of-content
https://github.com/
Sign inhttps://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fcoder%2Fcoder%2Fissues%2F2201
GitHubRepository hosting, code review, and collaborationhttps://github.com/platform
CopilotAI coding agent for tasks, projects, and automated workhttps://github.com/features/copilot
ActionsCI/CD and automationhttps://github.com/features/actions
Code QualityQuality analysis, fixes, and enforcementhttps://github.com/features/code-quality
CodespacesCloud development environmentshttps://github.com/features/codespaces
Code SecurityVulnerability detection and remediationhttps://github.com/security/advanced-security/code-security
Secret ProtectionSecret scanning and leak preventionhttps://github.com/security/advanced-security/secret-protection
All Featureshttps://github.com/features
Documentationhttps://docs.github.com/en
Traininghttps://learn.github.com/skills
Certificationshttps://learn.github.com/credentials
Build with Copilothttps://github.com/features/ai
Customize Copilothttps://docs.github.com/en/copilot/how-tos/copilot-on-github/customize-copilot/customize-copilot-overview
Integrationshttps://github.com/marketplace
Trending reposhttps://github.com/trending
GitHub Sponsorshttps://github.com/open-source/sponsors
Developer articleshttps://github.com/resources/articles
ChangelogYour source for the latest features, improvements, and fixes across GitHub products, all in one place.https://github.blog/changelog/
Enterpriseshttps://github.com/enterprise
Teamhttps://github.com/team
Startupshttps://github.com/enterprise/startups
Nonprofitshttps://github.com/solutions/industry/nonprofits
Healthcarehttps://github.com/solutions/industry/healthcare
Financial serviceshttps://github.com/solutions/industry/financial-services
Manufacturinghttps://github.com/solutions/industry/manufacturing
Governmenthttps://github.com/solutions/industry/government
See all industrieshttps://github.com/solutions/industry
Why GitHubhttps://github.com/why-github
Customer Storieshttps://github.com/customer-stories
Business insightshttps://github.com/solutions/executive-insights
Trust Centerhttps://github.com/trust-center
GitHub Advanced SecurityEnterprise-grade code securityhttps://github.com/security/advanced-security
Copilot for businessEnterprise-grade AI featureshttps://github.com/features/copilot/copilot-business
Premium SupportEnterprise-grade 24/7 supporthttps://github.com/enterprise/premium-support
Documentationhttps://docs.github.com/en
Resource centerhttps://github.com/resources
Bloghttps://github.blog/
Events & webinarshttps://github.com/resources/events
Community Forumhttps://github.com/orgs/community/discussions
Customer Storieshttps://github.com/customer-stories
Integrationshttps://github.com/marketplace
Customer supporthttps://help.github.com/
Traininghttps://learn.github.com/skills
GitHub Statushttps://www.githubstatus.com/
Abouthttps://github.com/about
Partnershttps://github.com/partners
Changeloghttps://github.blog/changelog/
GitHub UniverseThe event for developers, by developers — talks, workshops, and what’s next for GitHub.https://githubuniverse.com/
Pricinghttps://github.com/pricing
Sign inhttps://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fcoder%2Fcoder%2Fissues%2F2201
Sign uphttps://github.com/signup?ref_cta=Sign+up&ref_loc=header+logged+out&ref_page=%2F%3Cuser-name%3E%2F%3Crepo-name%3E%2Fvoltron%2Fissues_fragments%2Fissue_layout&source=header-repo&source_repo=coder%2Fcoder
Reloadhttps://github.com/coder/coder/issues/2201
Reloadhttps://github.com/coder/coder/issues/2201
Reloadhttps://github.com/coder/coder/issues/2201
coderhttps://github.com/coder
coderhttps://github.com/coder/coder
Codehttps://github.com/coder/coder
Issues320 (320)https://github.com/coder/coder/issues
Pull requests470 (470)https://github.com/coder/coder/pulls
Discussionshttps://github.com/coder/coder/discussions
Actionshttps://github.com/coder/coder/actions
Projectshttps://github.com/coder/coder/projects
Security and quality49 (49)https://github.com/coder/coder/security
Insightshttps://github.com/coder/coder/insights
https://github.com/coder/coder
New issuehttps://github.com/login?return_to=https://github.com/coder/coder/issues/2201
New issuehttps://github.com/login?return_to=https://github.com/coder/coder/issues/2201
Use postgres views to reduce database calls in api (allow JOINs in pg queries)https://github.com/coder/coder/issues/2201#top
apiArea: HTTP APIhttps://github.com/coder/coder/issues?q=state%3Aopen%20label%3A%22api%22
design neededRequest for more beautyhttps://github.com/coder/coder/issues?q=state%3Aopen%20label%3A%22design%20needed%22
needs decisionNeeds a higher-level decision to be unblocked.https://github.com/coder/coder/issues?q=state%3Aopen%20label%3A%22needs%20decision%22
staleThis issue is like stale bread.https://github.com/coder/coder/issues?q=state%3Aopen%20label%3A%22stale%22
https://github.com/Emyrk
Emyrkhttps://github.com/Emyrk
on Jun 9, 2022https://github.com/coder/coder/issues/2201#issue-1266186795
stevenmasley/build_initiator_usernamehttps://github.com/coder/coder/tree/stevenmasley/build_initiator_username
#2137https://github.com/coder/coder/pull/2137
convertWorkspaceBuildhttps://github.com/coder/coder/blob/fff59ef6ad4e1d9026b228e8d2164004a5c7ff60/coderd/workspacebuilds.go#L524-L524
sqlc.embedhttps://github.com/kyleconroy/sqlc/pull/1615
feat: add sqlc.embed to allow model re-use sqlc-dev/sqlc#1615https://github.com/sqlc-dev/sqlc/pull/1615
template: workspace_build_initiatorhttps://github.com/kyleconroy/sqlc/issues/112
Emyrkhttps://github.com/Emyrk
needs rfchttps://github.com/coder/coder/issues?q=state%3Aopen%20label%3A%22needs%20rfc%22
needs decisionNeeds a higher-level decision to be unblocked.https://github.com/coder/coder/issues?q=state%3Aopen%20label%3A%22needs%20decision%22
apiArea: HTTP APIhttps://github.com/coder/coder/issues?q=state%3Aopen%20label%3A%22api%22
chorehttps://github.com/coder/coder/issues?q=state%3Aopen%20label%3A%22chore%22
design neededRequest for more beautyhttps://github.com/coder/coder/issues?q=state%3Aopen%20label%3A%22design%20needed%22
on Jun 9, 2022https://github.com/coder/coder/issues/2201#event-6778300655
https://github.com/apps/github-actions
github-actionshttps://github.com/apps/github-actions
on Aug 10, 2022https://github.com/coder/coder/issues/2201#issuecomment-1210011038
github-actionshttps://github.com/apps/github-actions
staleThis issue is like stale bread.https://github.com/coder/coder/issues?q=state%3Aopen%20label%3A%22stale%22
on Aug 10, 2022https://github.com/coder/coder/issues/2201#event-7159560836
github-actionshttps://github.com/apps/github-actions
completedhttps://github.com/coder/coder/issues?q=is%3Aissue%20state%3Aclosed%20archived%3Afalse%20reason%3Acompleted
on Aug 23, 2022https://github.com/coder/coder/issues/2201#event-7237270101
Sign up for freehttps://github.com/signup?return_to=https://github.com/coder/coder/issues/2201
Sign in to commenthttps://github.com/login?return_to=https://github.com/coder/coder/issues/2201
apiArea: HTTP APIhttps://github.com/coder/coder/issues?q=state%3Aopen%20label%3A%22api%22
design neededRequest for more beautyhttps://github.com/coder/coder/issues?q=state%3Aopen%20label%3A%22design%20needed%22
needs decisionNeeds a higher-level decision to be unblocked.https://github.com/coder/coder/issues?q=state%3Aopen%20label%3A%22needs%20decision%22
staleThis issue is like stale bread.https://github.com/coder/coder/issues?q=state%3Aopen%20label%3A%22stale%22
https://github.com
Termshttps://docs.github.com/site-policy/github-terms/github-terms-of-service
Privacyhttps://docs.github.com/site-policy/privacy-policies/github-privacy-statement
Securityhttps://github.com/security
Statushttps://www.githubstatus.com/
Communityhttps://github.community/
Docshttps://docs.github.com/
Contacthttps://support.github.com?tags=dotcom-footer

Viewport: width=device-width


URLs of crawlers that visited me.