WIP

Emyrk · Emyrk · commit 229d77068b06 · 2023-07-17T12:14:36.000-04:00
diff --git a/cli/server.go b/cli/server.go
@@ -607,6 +607,7 @@ func (r *RootCmd) Server(newAPI func(context.Context, *coderd.Options) (*coderd.
 				options.Pubsub = pubsub.NewInMemory()
 			} else {
 				sqlDB, err := connectToPostgres(ctx, logger, sqlDriver, cfg.PostgresURL.String())
+
 				if err != nil {
 					return xerrors.Errorf("connect to postgres: %w", err)
 				}
diff --git a/coderd/database/migrations/000138_join_users.down.sql b/coderd/database/migrations/000138_join_users.down.sql
@@ -0,0 +1,5 @@
+BEGIN;
+
+DROP VIEW visible_users;
+
+COMMIT;
diff --git a/coderd/database/migrations/000138_join_users.up.sql b/coderd/database/migrations/000138_join_users.up.sql
@@ -0,0 +1,29 @@
+BEGIN;
+
+CREATE VIEW
+	visible_users
+AS
+SELECT
+	id, username, avatar_url
+FROM
+	users;
+
+COMMENT ON VIEW visible_users IS 'Visible fields of users are allowed to be joined with other tables for including context of other resources.';
+
+CREATE VIEW
+    template_with_users
+AS
+    SELECT
+        templates.*,
+        visible_users.username AS created_by_username,
+        visible_users.avatar_url AS created_by_avatar_url
+    FROM
+        templates
+    LEFT JOIN
+		visible_users
+	ON
+	    templates.created_by = visible_users.id;
+
+COMMENT ON VIEW template_with_users IS 'Joins in the username + avatar url of the created by user.';
+
+COMMIT;
diff --git a/coderd/templates_test.go b/coderd/templates_test.go
@@ -2,6 +2,7 @@ package coderd_test
 
 import (
 	"context"
+	"database/sql"
 	"net/http"
 	"sync/atomic"
 	"testing"
@@ -29,7 +30,10 @@ func TestTemplate(t *testing.T) {
 
 	t.Run("Get", func(t *testing.T) {
 		t.Parallel()
-		client := coderdtest.New(t, nil)
+		db, _ := sql.Open("postgres", "postgresql://postgres:postgres@localhost:5432?sslmode=disable")
+		client := coderdtest.New(t, &coderdtest.Options{
+			Database: database.New(db),
+		})
 		user := coderdtest.CreateFirstUser(t, client)
 		version := coderdtest.CreateTemplateVersion(t, client, user.OrganizationID, nil)
 		template := coderdtest.CreateTemplate(t, client, user.OrganizationID, version.ID)

Original file line number	Diff line number	Diff line change
`@@ -607,6 +607,7 @@ func (r RootCmd) Server(newAPI func(context.Context, coderd.Options) (*coderd.`
`607`	`607`	`options.Pubsub = pubsub.NewInMemory()`
`608`	`608`	`} else {`
`609`	`609`	`sqlDB, err := connectToPostgres(ctx, logger, sqlDriver, cfg.PostgresURL.String())`
	`610`	`+`
`610`	`611`	`if err != nil {`
`611`	`612`	`return xerrors.Errorf("connect to postgres: %w", err)`
`612`	`613`	`}`
-Original file line number
+Diff line change
@@ @@ -0,0 +1,5 @@ @@
 +BEGIN;
++
 +DROP VIEW visible_users;
++
 +COMMIT;