Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

chore: Initial database scaffolding#2

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.

Already on GitHub?Sign in to your account

Merged
kylecarbs merged 8 commits intomainfromdatabase
Jan 5, 2022
Merged
Show file tree
Hide file tree
Changes fromall commits
Commits
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
3 changes: 2 additions & 1 deletion.eslintignore
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,5 @@
###############################################################################
# COPY PASTA OF .gitignore
###############################################################################
node_modules
node_modules
vendor
1 change: 1 addition & 0 deletions.gitignore
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -10,4 +10,5 @@
###############################################################################

node_modules
vendor
.eslintcache
3 changes: 2 additions & 1 deletion.prettierignore
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -4,4 +4,5 @@
# https://github.com/prettier/prettier/issues/8506
# https://github.com/prettier/prettier/issues/8679
###############################################################################
node_modules
node_modules
vendor
13 changes: 12 additions & 1 deletionMakefile
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -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
Expand All@@ -9,4 +20,4 @@ endif
.PHONY: fmt/prettier

fmt: fmt/prettier
.PHONY: fmt
.PHONY: fmt
75 changes: 75 additions & 0 deletionsdatabase/db.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -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
}
19 changes: 19 additions & 0 deletionsdatabase/db_memory.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -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
}
2 changes: 2 additions & 0 deletionsdatabase/dump.sql
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
-- Code generated by 'make database/generate'. DO NOT EDIT.

89 changes: 89 additions & 0 deletionsdatabase/dump/main.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -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)
}
}
48 changes: 48 additions & 0 deletionsdatabase/migrate.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -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
}
30 changes: 30 additions & 0 deletionsdatabase/migrate_test.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
//go:build linux

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)
}
View file
Open in desktop
Empty file.
View file
Open in desktop
Empty file.
11 changes: 11 additions & 0 deletionsdatabase/migrations/create_migration.sh
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -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."
5 changes: 5 additions & 0 deletionsdatabase/models.go
View file
Open in desktop

Some generated files are not rendered by default. Learn more abouthow customized files appear on GitHub.

59 changes: 59 additions & 0 deletionsdatabase/postgres/postgres.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,59 @@
package postgres

import (
"database/sql"
"fmt"
"log"
"time"

"github.com/ory/dockertest/v3"
"github.com/ory/dockertest/v3/docker"
"golang.org/x/xerrors"
)

// Open creates a new PostgreSQL server using a Docker container.
func Open() (string, func(), error) {
pool, err := dockertest.NewPool("")
if err != nil {
return "", nil, xerrors.Errorf("create pool: %w", err)
}
resource, err := pool.RunWithOptions(&dockertest.RunOptions{
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

This seems fine to me for now! As we discussed, if Docker is too heavy a dependency... we could look at runningpsql manually in the future.

I think this approach makes for now, though, since the team is familiar with this approach fromcoderd. And if we decide Docker is too heavy, there might be other approaches we take (like revisiting a cloud solution...)

kylecarbs reacted with thumbs up emoji
Repository: "postgres",
Tag: "11",
Env: []string{
"POSTGRES_PASSWORD=postgres",
"POSTGRES_USER=postgres",
"POSTGRES_DB=postgres",
"listen_addresses = '*'",
},
}, func(config *docker.HostConfig) {
// set AutoRemove to true so that stopped container goes away by itself
config.AutoRemove = true
config.RestartPolicy = docker.RestartPolicy{Name: "no"}
})
if err != nil {
log.Fatalf("Could not start resource: %s", err)
}
hostAndPort := resource.GetHostPort("5432/tcp")
dbURL := fmt.Sprintf("postgres://postgres:postgres@%s/postgres?sslmode=disable", hostAndPort)

// Docker should hard-kill the container after 120 seconds.
resource.Expire(120)

pool.MaxWait = 120 * time.Second
err = pool.Retry(func() error {
db, err := sql.Open("postgres", dbURL)
if err != nil {
return err
}
err = db.Ping()
_ = db.Close()
return err
})
if err != nil {
return "", nil, err
}
return dbURL, func() {
_ = pool.Purge(resource)
}, nil
}
Loading
[8]ページ先頭
©2009-2025 Movatter.jp