chore: Initial database scaffolding

This implements migrations and code generation for interfacing with a PostgreSQL database. A dependency is added for the "postgres" binary on the host, but that seems like an acceptable requirement considering it's our primary database. An in-memory database object can be created for simple cross-OS and fast testing.
coder · kylecarbs · Jan 5, 2022 · Jan 4, 2022 · Jan 4, 2022 · Jan 4, 2022
commit a5af7f4834b7c34e6a121d25f68ee21a1b183a1a
diff --git a/.eslintignore b/.eslintignore
@@ -1,4 +1,5 @@
 ###############################################################################
 # COPY PASTA OF .gitignore
 ###############################################################################
-node_modules
+node_modules
+vendor
diff --git a/.gitignore b/.gitignore
@@ -10,4 +10,5 @@
 ###############################################################################
 
 node_modules
+vendor
 .eslintcache
diff --git a/.prettierignore b/.prettierignore
@@ -4,4 +4,5 @@
 # https://github.com/prettier/prettier/issues/8506
 # https://github.com/prettier/prettier/issues/8679
 ###############################################################################
-node_modules
+node_modules
+vendor
diff --git a/Makefile b/Makefile
@@ -1,3 +1,14 @@
+# Runs migrations to output a dump of the database.
+database/dump.sql: $(wildcard database/migrations/*.sql)
+	go run database/dump/main.go
+
+# Generates Go code for querying the database.
+.PHONY: database/generate
+database/generate: database/dump.sql database/query.sql
+	cd database && sqlc generate && rm db_tmp.go
+	cd database && gofmt -w -r 'Querier -> querier' *.go
+	cd database && gofmt -w -r 'Queries -> sqlQuerier' *.go
+
 fmt/prettier:
 	@echo "--- prettier"
 # Avoid writing files in CI to reduce file write activity
@@ -9,4 +20,4 @@ endif
 .PHONY: fmt/prettier
 
 fmt: fmt/prettier
-.PHONY: fmt
+.PHONY: fmt
diff --git a/database/db.go b/database/db.go
@@ -0,0 +1,75 @@
+// Package database connects to external services for stateful storage.
+//
+// Query functions are generated using sqlc.
+//
+// To modify the database schema:
+// 1. Add a new migration using "create_migration.sh" in database/migrations/
+// 2. Run "make database/generate" in the root to generate models.
+// 3. Add/Edit queries in "query.sql" and run "make database/generate" to create Go code.
+package database
+
+import (
+	"context"
+	"database/sql"
+	"errors"
+
+	"golang.org/x/xerrors"
+)
+
+// Store contains all queryable database functions.
+// It extends the generated interface to add transaction support.
+type Store interface {
+	querier
+
+	InTx(context.Context, func(Store) error) error
+}
+
+// DBTX represents a database connection or transaction.
+type DBTX interface {
+	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
+	PrepareContext(context.Context, string) (*sql.Stmt, error)
+	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
+	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
+}
+
+// New creates a new database store using a SQL database connection.
+func New(sdb *sql.DB) Store {
+	return &sqlQuerier{
+		db:  sdb,
+		sdb: sdb,
+	}
+}
+
+type sqlQuerier struct {
+	sdb *sql.DB
+	db  DBTX
+}
+
+// InTx performs database operations inside a transaction.
+func (q *sqlQuerier) InTx(ctx context.Context, fn func(Store) error) error {
+	if q.sdb == nil {
+		return nil
+	}
+	tx, err := q.sdb.Begin()
+	if err != nil {
+		return xerrors.Errorf("begin transaction: %w", err)
+	}
+	defer func() {
+		rerr := tx.Rollback()
+		if rerr == nil || errors.Is(rerr, sql.ErrTxDone) {
+			// no need to do anything, tx committed successfully
+			return
+		}
+		// couldn't roll back for some reason, extend returned error
+		err = xerrors.Errorf("defer (%s): %w", rerr.Error(), err)
+	}()
+	err = fn(&sqlQuerier{db: tx})
+	if err != nil {
+		return xerrors.Errorf("execute transaction: %w", err)
+	}
+	err = tx.Commit()
+	if err != nil {
+		return xerrors.Errorf("commit transaction: %w", err)
+	}
+	return nil
+}
diff --git a/database/db_memory.go b/database/db_memory.go
@@ -0,0 +1,19 @@
+package database
+
+import "context"
+
+// NewInMemory returns an in-memory store of the database.
+func NewInMemory() Store {
+	return &memoryQuerier{}
+}
+
+type memoryQuerier struct{}
+
+// InTx doesn't rollback data properly for in-memory yet.
+func (q *memoryQuerier) InTx(ctx context.Context, fn func(Store) error) error {
+	return fn(q)
+}
+
+func (q *memoryQuerier) ExampleQuery(ctx context.Context) error {
+	return nil
+}
diff --git a/database/dump.sql b/database/dump.sql
@@ -0,0 +1,2 @@
+-- Code generated by 'make database/generate'. DO NOT EDIT.
+
diff --git a/database/dump/main.go b/database/dump/main.go
@@ -0,0 +1,89 @@
+package main
+
+import (
+	"bytes"
+	"context"
+	"database/sql"
+	"fmt"
+	"io/ioutil"
+	"os"
+	"os/exec"
+	"path/filepath"
+	"runtime"
+
+	"github.com/coder/coder/database"
+	"github.com/coder/coder/database/postgres"
+)
+
+func main() {
+	connection, closeFn, err := postgres.Open()
+	if err != nil {
+		panic(err)
+	}
+	defer closeFn()
+	db, err := sql.Open("postgres", connection)
+	if err != nil {
+		panic(err)
+	}
+	err = database.Migrate(context.Background(), "postgres", db)
+	if err != nil {
+		panic(err)
+	}
+	cmd := exec.Command(
+		"pg_dump",
+		"--schema-only",
+		connection,
+		"--no-privileges",
+		"--no-owner",
+		"--no-comments",
+
+		// We never want to manually generate
+		// queries executing against this table.
+		"--exclude-table=schema_migrations",
+	)
+	cmd.Env = []string{
+		"PGTZ=UTC",
+		"PGCLIENTENCODING=UTF8",
+	}
+	var output bytes.Buffer
+	cmd.Stdout = &output
+	cmd.Stderr = os.Stderr
+	err = cmd.Run()
+	if err != nil {
+		panic(err)
+	}
+
+	for _, sed := range []string{
+		// Remove all comments.
+		"/^--/d",
+		// Public is implicit in the schema.
+		"s/ public\\./ /",
+		// Remove database settings.
+		"s/SET.*;//g",
+		// Remove select statements. These aren't useful
+		// to a reader of the dump.
+		"s/SELECT.*;//g",
+		// Removes multiple newlines.
+		"/^$/N;/^\\n$/D",
+	} {
+		cmd := exec.Command("sed", "-e", sed)
+		cmd.Stdin = bytes.NewReader(output.Bytes())
+		output = bytes.Buffer{}
+		cmd.Stdout = &output
+		cmd.Stderr = os.Stderr
+		err = cmd.Run()
+		if err != nil {
+			panic(err)
+		}
+	}
+
+	dump := fmt.Sprintf("-- Code generated by 'make database/generate'. DO NOT EDIT.\n%s", output.Bytes())
+	_, mainPath, _, ok := runtime.Caller(0)
+	if !ok {
+		panic("couldn't get caller path")
+	}
+	err = ioutil.WriteFile(filepath.Join(mainPath, "..", "..", "dump.sql"), []byte(dump), 0644)
+	if err != nil {
+		panic(err)
+	}
+}
diff --git a/database/migrate.go b/database/migrate.go
@@ -0,0 +1,48 @@
+package database
+
+import (
+	"context"
+	"database/sql"
+	"embed"
+	"errors"
+
+	"github.com/golang-migrate/migrate/v4"
+	"github.com/golang-migrate/migrate/v4/database/postgres"
+	"github.com/golang-migrate/migrate/v4/source/iofs"
+	"golang.org/x/xerrors"
+)
+
+//go:embed migrations/*.sql
+var migrations embed.FS
+
+// Migrate runs SQL migrations to ensure the database schema is up-to-date.
+func Migrate(ctx context.Context, dbName string, db *sql.DB) error {
+	sourceDriver, err := iofs.New(migrations, "migrations")
+	if err != nil {
+		return xerrors.Errorf("create iofs: %w", err)
+	}
+	dbDriver, err := postgres.WithInstance(db, &postgres.Config{})
+	if err != nil {
+		return xerrors.Errorf("wrap postgres connection: %w", err)
+	}
+	m, err := migrate.NewWithInstance("", sourceDriver, dbName, dbDriver)
+	if err != nil {
+		return xerrors.Errorf("migrate: %w", err)
+	}
+	err = m.Up()
+	if err != nil {
+		if errors.Is(err, migrate.ErrNoChange) {
+			// It's OK if no changes happened!
+			return nil
+		}
+		return xerrors.Errorf("up: %w", err)
+	}
+	srcErr, dbErr := m.Close()
+	if srcErr != nil {
+		return xerrors.Errorf("close source: %w", err)
+	}
+	if dbErr != nil {
+		return xerrors.Errorf("close database: %w", err)
+	}
+	return nil
+}
diff --git a/database/migrate_test.go b/database/migrate_test.go
@@ -0,0 +1,28 @@
+package database_test
+
+import (
+	"context"
+	"database/sql"
+	"testing"
+
+	"github.com/coder/coder/database"
+	"github.com/coder/coder/database/postgres"
+	"github.com/stretchr/testify/require"
+	"go.uber.org/goleak"
+)
+
+func TestMain(m *testing.M) {
+	goleak.VerifyTestMain(m)
+}
+
+func TestMigrate(t *testing.T) {
+	t.Parallel()
+
+	connection, closeFn, err := postgres.Open()
+	require.NoError(t, err)
+	defer closeFn()
+	db, err := sql.Open("postgres", connection)
+	require.NoError(t, err)
+	err = database.Migrate(context.Background(), "postgres", db)
+	require.NoError(t, err)
+}
diff --git a/database/migrations/000001_base.down.sql b/database/migrations/000001_base.down.sql
diff --git a/database/migrations/000001_base.up.sql b/database/migrations/000001_base.up.sql
diff --git a/database/migrations/create_migration.sh b/database/migrations/create_migration.sh
@@ -0,0 +1,11 @@
+#!/usr/bin/env bash
+cd "$(dirname "$0")"
+
+if [ -z "$1" ]; then
+    echo "First argument is the migration name!"
+    exit 1
+fi
+
+migrate create -ext sql -dir . -seq $1
+
+echo "After making adjustments, run \"make database/generate\" to generate models."
diff --git a/database/models.go b/database/models.go
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,2 @@
		-- Code generated by 'make database/generate'. DO NOT EDIT.