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

Commit025b55f

Browse files
authored
chore: Initial database scaffolding (#2)
* chore: Initial database scaffoldingThis 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.* Run tests in CI* Use Docker instead of binaries on the host* Skip database tests on non-Linux operating systems* chore: Add golangci-lint and codecov* Use consistent file names
1 parenta6b2dd7 commit025b55f

23 files changed

+2017
-4
lines changed

‎.eslintignore

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,5 @@
11
###############################################################################
22
# COPY PASTA OF .gitignore
33
###############################################################################
4-
node_modules
4+
node_modules
5+
vendor

‎.gitignore

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,4 +10,5 @@
1010
###############################################################################
1111

1212
node_modules
13+
vendor
1314
.eslintcache

‎.prettierignore

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,4 +4,5 @@
44
# https://github.com/prettier/prettier/issues/8506
55
# https://github.com/prettier/prettier/issues/8679
66
###############################################################################
7-
node_modules
7+
node_modules
8+
vendor

‎Makefile

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,14 @@
1+
# Runs migrations to output a dump of the database.
2+
database/dump.sql:$(wildcard database/migrations/*.sql)
3+
go run database/dump/main.go
4+
5+
# Generates Go code for querying the database.
6+
.PHONY: database/generate
7+
database/generate: database/dump.sql database/query.sql
8+
cd database&& sqlc generate&& rm db_tmp.go
9+
cd database&& gofmt -w -r'Querier -> querier'*.go
10+
cd database&& gofmt -w -r'Queries -> sqlQuerier'*.go
11+
112
fmt/prettier:
213
@echo"--- prettier"
314
# Avoid writing files in CI to reduce file write activity
@@ -9,4 +20,4 @@ endif
920
.PHONY: fmt/prettier
1021

1122
fmt: fmt/prettier
12-
.PHONY: fmt
23+
.PHONY: fmt

‎database/db.go

Lines changed: 75 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,75 @@
1+
// Package database connects to external services for stateful storage.
2+
//
3+
// Query functions are generated using sqlc.
4+
//
5+
// To modify the database schema:
6+
// 1. Add a new migration using "create_migration.sh" in database/migrations/
7+
// 2. Run "make database/generate" in the root to generate models.
8+
// 3. Add/Edit queries in "query.sql" and run "make database/generate" to create Go code.
9+
package database
10+
11+
import (
12+
"context"
13+
"database/sql"
14+
"errors"
15+
16+
"golang.org/x/xerrors"
17+
)
18+
19+
// Store contains all queryable database functions.
20+
// It extends the generated interface to add transaction support.
21+
typeStoreinterface {
22+
querier
23+
24+
InTx(context.Context,func(Store)error)error
25+
}
26+
27+
// DBTX represents a database connection or transaction.
28+
typeDBTXinterface {
29+
ExecContext(context.Context,string,...interface{}) (sql.Result,error)
30+
PrepareContext(context.Context,string) (*sql.Stmt,error)
31+
QueryContext(context.Context,string,...interface{}) (*sql.Rows,error)
32+
QueryRowContext(context.Context,string,...interface{})*sql.Row
33+
}
34+
35+
// New creates a new database store using a SQL database connection.
36+
funcNew(sdb*sql.DB)Store {
37+
return&sqlQuerier{
38+
db:sdb,
39+
sdb:sdb,
40+
}
41+
}
42+
43+
typesqlQuerierstruct {
44+
sdb*sql.DB
45+
dbDBTX
46+
}
47+
48+
// InTx performs database operations inside a transaction.
49+
func (q*sqlQuerier)InTx(ctx context.Context,fnfunc(Store)error)error {
50+
ifq.sdb==nil {
51+
returnnil
52+
}
53+
tx,err:=q.sdb.Begin()
54+
iferr!=nil {
55+
returnxerrors.Errorf("begin transaction: %w",err)
56+
}
57+
deferfunc() {
58+
rerr:=tx.Rollback()
59+
ifrerr==nil||errors.Is(rerr,sql.ErrTxDone) {
60+
// no need to do anything, tx committed successfully
61+
return
62+
}
63+
// couldn't roll back for some reason, extend returned error
64+
err=xerrors.Errorf("defer (%s): %w",rerr.Error(),err)
65+
}()
66+
err=fn(&sqlQuerier{db:tx})
67+
iferr!=nil {
68+
returnxerrors.Errorf("execute transaction: %w",err)
69+
}
70+
err=tx.Commit()
71+
iferr!=nil {
72+
returnxerrors.Errorf("commit transaction: %w",err)
73+
}
74+
returnnil
75+
}

‎database/db_memory.go

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
package database
2+
3+
import"context"
4+
5+
// NewInMemory returns an in-memory store of the database.
6+
funcNewInMemory()Store {
7+
return&memoryQuerier{}
8+
}
9+
10+
typememoryQuerierstruct{}
11+
12+
// InTx doesn't rollback data properly for in-memory yet.
13+
func (q*memoryQuerier)InTx(ctx context.Context,fnfunc(Store)error)error {
14+
returnfn(q)
15+
}
16+
17+
func (q*memoryQuerier)ExampleQuery(ctx context.Context)error {
18+
returnnil
19+
}

‎database/dump.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
-- Code generated by 'make database/generate'. DO NOT EDIT.
2+

‎database/dump/main.go

Lines changed: 89 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,89 @@
1+
package main
2+
3+
import (
4+
"bytes"
5+
"context"
6+
"database/sql"
7+
"fmt"
8+
"io/ioutil"
9+
"os"
10+
"os/exec"
11+
"path/filepath"
12+
"runtime"
13+
14+
"github.com/coder/coder/database"
15+
"github.com/coder/coder/database/postgres"
16+
)
17+
18+
funcmain() {
19+
connection,closeFn,err:=postgres.Open()
20+
iferr!=nil {
21+
panic(err)
22+
}
23+
defercloseFn()
24+
db,err:=sql.Open("postgres",connection)
25+
iferr!=nil {
26+
panic(err)
27+
}
28+
err=database.Migrate(context.Background(),"postgres",db)
29+
iferr!=nil {
30+
panic(err)
31+
}
32+
cmd:=exec.Command(
33+
"pg_dump",
34+
"--schema-only",
35+
connection,
36+
"--no-privileges",
37+
"--no-owner",
38+
"--no-comments",
39+
40+
// We never want to manually generate
41+
// queries executing against this table.
42+
"--exclude-table=schema_migrations",
43+
)
44+
cmd.Env= []string{
45+
"PGTZ=UTC",
46+
"PGCLIENTENCODING=UTF8",
47+
}
48+
varoutput bytes.Buffer
49+
cmd.Stdout=&output
50+
cmd.Stderr=os.Stderr
51+
err=cmd.Run()
52+
iferr!=nil {
53+
panic(err)
54+
}
55+
56+
for_,sed:=range []string{
57+
// Remove all comments.
58+
"/^--/d",
59+
// Public is implicit in the schema.
60+
"s/ public\\./ /",
61+
// Remove database settings.
62+
"s/SET.*;//g",
63+
// Remove select statements. These aren't useful
64+
// to a reader of the dump.
65+
"s/SELECT.*;//g",
66+
// Removes multiple newlines.
67+
"/^$/N;/^\\n$/D",
68+
} {
69+
cmd:=exec.Command("sed","-e",sed)
70+
cmd.Stdin=bytes.NewReader(output.Bytes())
71+
output= bytes.Buffer{}
72+
cmd.Stdout=&output
73+
cmd.Stderr=os.Stderr
74+
err=cmd.Run()
75+
iferr!=nil {
76+
panic(err)
77+
}
78+
}
79+
80+
dump:=fmt.Sprintf("-- Code generated by 'make database/generate'. DO NOT EDIT.\n%s",output.Bytes())
81+
_,mainPath,_,ok:=runtime.Caller(0)
82+
if!ok {
83+
panic("couldn't get caller path")
84+
}
85+
err=ioutil.WriteFile(filepath.Join(mainPath,"..","..","dump.sql"), []byte(dump),0644)
86+
iferr!=nil {
87+
panic(err)
88+
}
89+
}

‎database/migrate.go

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,48 @@
1+
package database
2+
3+
import (
4+
"context"
5+
"database/sql"
6+
"embed"
7+
"errors"
8+
9+
"github.com/golang-migrate/migrate/v4"
10+
"github.com/golang-migrate/migrate/v4/database/postgres"
11+
"github.com/golang-migrate/migrate/v4/source/iofs"
12+
"golang.org/x/xerrors"
13+
)
14+
15+
//go:embed migrations/*.sql
16+
varmigrations embed.FS
17+
18+
// Migrate runs SQL migrations to ensure the database schema is up-to-date.
19+
funcMigrate(ctx context.Context,dbNamestring,db*sql.DB)error {
20+
sourceDriver,err:=iofs.New(migrations,"migrations")
21+
iferr!=nil {
22+
returnxerrors.Errorf("create iofs: %w",err)
23+
}
24+
dbDriver,err:=postgres.WithInstance(db,&postgres.Config{})
25+
iferr!=nil {
26+
returnxerrors.Errorf("wrap postgres connection: %w",err)
27+
}
28+
m,err:=migrate.NewWithInstance("",sourceDriver,dbName,dbDriver)
29+
iferr!=nil {
30+
returnxerrors.Errorf("migrate: %w",err)
31+
}
32+
err=m.Up()
33+
iferr!=nil {
34+
iferrors.Is(err,migrate.ErrNoChange) {
35+
// It's OK if no changes happened!
36+
returnnil
37+
}
38+
returnxerrors.Errorf("up: %w",err)
39+
}
40+
srcErr,dbErr:=m.Close()
41+
ifsrcErr!=nil {
42+
returnxerrors.Errorf("close source: %w",err)
43+
}
44+
ifdbErr!=nil {
45+
returnxerrors.Errorf("close database: %w",err)
46+
}
47+
returnnil
48+
}

‎database/migrate_test.go

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
//go:build linux
2+
3+
package database_test
4+
5+
import (
6+
"context"
7+
"database/sql"
8+
"testing"
9+
10+
"github.com/coder/coder/database"
11+
"github.com/coder/coder/database/postgres"
12+
"github.com/stretchr/testify/require"
13+
"go.uber.org/goleak"
14+
)
15+
16+
funcTestMain(m*testing.M) {
17+
goleak.VerifyTestMain(m)
18+
}
19+
20+
funcTestMigrate(t*testing.T) {
21+
t.Parallel()
22+
23+
connection,closeFn,err:=postgres.Open()
24+
require.NoError(t,err)
25+
defercloseFn()
26+
db,err:=sql.Open("postgres",connection)
27+
require.NoError(t,err)
28+
err=database.Migrate(context.Background(),"postgres",db)
29+
require.NoError(t,err)
30+
}

‎database/migrations/000001_base.down.sql

Whitespace-only changes.

‎database/migrations/000001_base.up.sql

Whitespace-only changes.
Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
#!/usr/bin/env bash
2+
cd"$(dirname"$0")"
3+
4+
if [-z"$1" ];then
5+
echo"First argument is the migration name!"
6+
exit 1
7+
fi
8+
9+
migrate create -ext sql -dir. -seq$1
10+
11+
echo"After making adjustments, run\"make database/generate\" to generate models."

‎database/models.go

Lines changed: 5 additions & 0 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

‎database/postgres/postgres.go

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,59 @@
1+
package postgres
2+
3+
import (
4+
"database/sql"
5+
"fmt"
6+
"log"
7+
"time"
8+
9+
"github.com/ory/dockertest/v3"
10+
"github.com/ory/dockertest/v3/docker"
11+
"golang.org/x/xerrors"
12+
)
13+
14+
// Open creates a new PostgreSQL server using a Docker container.
15+
funcOpen() (string,func(),error) {
16+
pool,err:=dockertest.NewPool("")
17+
iferr!=nil {
18+
return"",nil,xerrors.Errorf("create pool: %w",err)
19+
}
20+
resource,err:=pool.RunWithOptions(&dockertest.RunOptions{
21+
Repository:"postgres",
22+
Tag:"11",
23+
Env: []string{
24+
"POSTGRES_PASSWORD=postgres",
25+
"POSTGRES_USER=postgres",
26+
"POSTGRES_DB=postgres",
27+
"listen_addresses = '*'",
28+
},
29+
},func(config*docker.HostConfig) {
30+
// set AutoRemove to true so that stopped container goes away by itself
31+
config.AutoRemove=true
32+
config.RestartPolicy= docker.RestartPolicy{Name:"no"}
33+
})
34+
iferr!=nil {
35+
log.Fatalf("Could not start resource: %s",err)
36+
}
37+
hostAndPort:=resource.GetHostPort("5432/tcp")
38+
dbURL:=fmt.Sprintf("postgres://postgres:postgres@%s/postgres?sslmode=disable",hostAndPort)
39+
40+
// Docker should hard-kill the container after 120 seconds.
41+
resource.Expire(120)
42+
43+
pool.MaxWait=120*time.Second
44+
err=pool.Retry(func()error {
45+
db,err:=sql.Open("postgres",dbURL)
46+
iferr!=nil {
47+
returnerr
48+
}
49+
err=db.Ping()
50+
_=db.Close()
51+
returnerr
52+
})
53+
iferr!=nil {
54+
return"",nil,err
55+
}
56+
returndbURL,func() {
57+
_=pool.Purge(resource)
58+
},nil
59+
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp