Skip to content

Commit 232d6f9

Browse files
kyleconroyandrewmbenton
kyleconroy
and
andrewmbenton
authored
feat(vet): Run rules against a managed database (#2751)
* feat(vet): Support managed databases * feat(vet): Run rules against a managed database * Add managed to JSON schema * Use different hostname for region discovery * docs: second pass at managed-databases.md --------- Co-authored-by: Andrew Benton <[email protected]>
1 parent 76a3549 commit 232d6f9

File tree

16 files changed

+173
-32
lines changed

16 files changed

+173
-32
lines changed

‎.github/workflows/ci.yml‎

Lines changed: 1 addition & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -32,16 +32,6 @@ jobs:
3232
runs-on: ubuntu-latest
3333

3434
services:
35-
postgres:
36-
image: "postgres:15"
37-
env:
38-
POSTGRES_DB: postgres
39-
POSTGRES_PASSWORD: postgres
40-
POSTGRES_USER: postgres
41-
ports:
42-
- 5432:5432
43-
# needed because the postgres container does not provide a healthcheck
44-
options: --health-cmd pg_isready --health-interval 10s --health-timeout 5s --health-retries 5
4535
mysql:
4636
image: "mysql/mysql-server:8.0"
4737
env:
@@ -69,17 +59,13 @@ jobs:
6959
- name: test ./...
7060
run: gotestsum --junitfile junit.xml -- --tags=examples ./...
7161
env:
72-
PG_USER: postgres
73-
PG_HOST: localhost
74-
PG_DATABASE: postgres
75-
PG_PASSWORD: postgres
76-
PG_PORT: ${{ job.services.postgres.ports['5432'] }}
7762
MYSQL_DATABASE: mysql
7863
MYSQL_HOST: localhost
7964
MYSQL_PORT: ${{ job.services.mysql.ports['3306'] }}
8065
MYSQL_ROOT_PASSWORD: mysecretpassword
8166
CI_SQLC_PROJECT_ID: ${{ secrets.CI_SQLC_PROJECT_ID }}
8267
CI_SQLC_AUTH_TOKEN: ${{ secrets.CI_SQLC_AUTH_TOKEN }}
68+
SQLC_AUTH_TOKEN: ${{ secrets.CI_SQLC_AUTH_TOKEN }}
8369

8470
- name: build internal/endtoend
8571
run: go build ./...

‎docs/howto/managed-databases.md‎

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,68 @@
1+
# Managed databases
2+
3+
*Added in v1.22.0*
4+
5+
`sqlc` can create and maintain hosted databases for your project. These
6+
databases are immediately useful for linting queries with [`sqlc vet`](vet.md)
7+
if your lint rules require a connection to a running database. PostgreSQL
8+
support is available today, with MySQL on the way.
9+
10+
This feature is under active development, and we're interested in supporting
11+
other use-cases. Beyond linting queries, you can use sqlc managed databases
12+
in your tests to quickly stand up a database per test suite or even per test,
13+
providing a real, isolated database for a test run. No cleanup required.
14+
15+
Interested in trying out managed databases? Sign up [here](https://docs.google.com/forms/d/e/1FAIpQLSdxoMzJ7rKkBpuez-KyBcPNyckYV-5iMR--FRB7WnhvAmEvKg/viewform) or send us an email
16+
at <mailto:[email protected]>.
17+
18+
## Configuring managed databases
19+
20+
To configure `sqlc` to use a managed database, remove the `uri` key from your
21+
`database` configuration and replace it with the `managed` key set to `true`.
22+
Set the `project` key in your `cloud` configuration to the value of your
23+
project ID, obtained via the sqlc.dev Dashboard.
24+
25+
```yaml
26+
version: '2'
27+
cloud:
28+
project: '<PROJECT_ID>'
29+
sql:
30+
- schema: schema.sql
31+
queries: query.sql
32+
engine: postgresql
33+
database:
34+
managed: true
35+
```
36+
37+
## Authentication
38+
39+
`sqlc` expects to find a valid auth token in the value of the `SQLC_AUTH_TOKEN`
40+
environment variable. You can create an auth token via the sqlc.dev Dashboard.
41+
42+
```shell
43+
export SQLC_AUTH_TOKEN=sqlc_xxxxxxxx
44+
```
45+
46+
## Linting queries
47+
48+
With managed databases configured, `sqlc vet` will create a database with your
49+
package's schema and use that database when running lint rules that require a
50+
database connection, e.g. any [rule relying on `EXPLAIN ...` output](vet.md#rules-using-explain-output).
51+
52+
If you don't yet have any vet rules, the [built-in sqlc/db-prepare rule](vet.md#sqlc-db-prepare)
53+
is a good place to start. It prepares each of your queries against the database
54+
to ensure the query is valid. Here's a minimal working configuration:
55+
56+
```yaml
57+
version: '2'
58+
cloud:
59+
project: '<PROJECT_ID>'
60+
sql:
61+
- schema: schema.sql
62+
queries: query.sql
63+
engine: postgresql
64+
database:
65+
managed: true
66+
rules:
67+
- sqlc/db-prepare
68+
```

‎examples/authors/sqlc.yaml‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,12 @@
11
version: '2'
2+
cloud:
3+
project: "01HAQMMECEYQYKFJN8MP16QC41"
24
sql:
35
- schema: postgresql/schema.sql
46
queries: postgresql/query.sql
57
engine: postgresql
68
database:
7-
uri: postgresql://${PG_USER}:${PG_PASSWORD}@${PG_HOST}:${PG_PORT}/authors
9+
managed: true
810
rules:
911
- sqlc/db-prepare
1012
- postgresql-query-too-costly

‎examples/batch/sqlc.json‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,8 @@
11
{
22
"version": "1",
3+
"cloud": {
4+
"project": "01HAQMMECEYQYKFJN8MP16QC41"
5+
},
36
"packages": [
47
{
58
"path": "postgresql",
@@ -8,7 +11,7 @@
811
"queries": "postgresql/query.sql",
912
"engine": "postgresql",
1013
"database": {
11-
"uri": "postgresql://${PG_USER}:${PG_PASSWORD}@${PG_HOST}:${PG_PORT}/batch"
14+
"managed": true
1215
},
1316
"rules": [
1417
"sqlc/db-prepare"

‎examples/booktest/sqlc.json‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,8 @@
11
{
22
"version": "1",
3+
"cloud": {
4+
"project": "01HAQMMECEYQYKFJN8MP16QC41"
5+
},
36
"packages": [
47
{
58
"name": "booktest",
@@ -8,7 +11,7 @@
811
"queries": "postgresql/query.sql",
912
"engine": "postgresql",
1013
"database": {
11-
"uri": "postgresql://${PG_USER}:${PG_PASSWORD}@${PG_HOST}:${PG_PORT}/booktest"
14+
"managed": true
1215
},
1316
"rules": [
1417
"sqlc/db-prepare"

‎examples/jets/sqlc.json‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,8 @@
11
{
22
"version": "1",
3+
"cloud": {
4+
"project": "01HAQMMECEYQYKFJN8MP16QC41"
5+
},
36
"packages": [
47
{
58
"path": "postgresql",
@@ -8,7 +11,7 @@
811
"queries": "postgresql/query-building.sql",
912
"engine": "postgresql",
1013
"database": {
11-
"uri": "postgresql://${PG_USER}:${PG_PASSWORD}@${PG_HOST}:${PG_PORT}/jets"
14+
"managed": true
1215
},
1316
"rules": [
1417
"sqlc/db-prepare"

‎examples/ondeck/sqlc.json‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,8 @@
11
{
22
"version": "1",
3+
"cloud": {
4+
"project": "01HAQMMECEYQYKFJN8MP16QC41"
5+
},
36
"packages": [
47
{
58
"path": "postgresql",
@@ -8,7 +11,7 @@
811
"queries": "postgresql/query",
912
"engine": "postgresql",
1013
"database": {
11-
"uri": "postgresql://${PG_USER}:${PG_PASSWORD}@${PG_HOST}:${PG_PORT}/ondeck"
14+
"managed": true
1215
},
1316
"rules": [
1417
"sqlc/db-prepare"

‎internal/cmd/cmd.go‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -37,6 +37,7 @@ func Do(args []string, stdin io.Reader, stdout io.Writer, stderr io.Writer) int
3737
rootCmd.PersistentFlags().StringP("file", "f", "", "specify an alternate config file (default: sqlc.yaml)")
3838
rootCmd.PersistentFlags().BoolP("experimental", "x", false, "DEPRECATED: enable experimental features (default: false)")
3939
rootCmd.PersistentFlags().Bool("no-remote", false, "disable remote execution (default: false)")
40+
rootCmd.PersistentFlags().Bool("remote", false, "enable remote execution (default: false)")
4041
rootCmd.PersistentFlags().Bool("no-database", false, "disable database connections (default: false)")
4142

4243
rootCmd.AddCommand(checkCmd)
@@ -136,17 +137,20 @@ var initCmd = &cobra.Command{
136137
type Env struct {
137138
DryRun bool
138139
Debug opts.Debug
140+
Remote bool
139141
NoRemote bool
140142
NoDatabase bool
141143
}
142144

143145
func ParseEnv(c *cobra.Command) Env {
144146
dr := c.Flag("dry-run")
147+
r := c.Flag("remote")
145148
nr := c.Flag("no-remote")
146149
nodb := c.Flag("no-database")
147150
return Env{
148151
DryRun: dr != nil && dr.Changed,
149152
Debug: opts.DebugFromEnv(),
153+
Remote: r != nil && nr.Value.String() == "true",
150154
NoRemote: nr != nil && nr.Value.String() == "true",
151155
NoDatabase: nodb != nil && nodb.Value.String() == "true",
152156
}

‎internal/cmd/generate.go‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -145,7 +145,7 @@ func Generate(ctx context.Context, e Env, dir, filename string, stderr io.Writer
145145
return nil, err
146146
}
147147

148-
if conf.Cloud.Project != "" && !e.NoRemote {
148+
if conf.Cloud.Project != "" && e.Remote && !e.NoRemote {
149149
return remoteGenerate(ctx, configPath, conf, dir, stderr)
150150
}
151151

‎internal/cmd/vet.go‎

Lines changed: 68 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -26,7 +26,10 @@ import (
2626
"github.com/sqlc-dev/sqlc/internal/debug"
2727
"github.com/sqlc-dev/sqlc/internal/opts"
2828
"github.com/sqlc-dev/sqlc/internal/plugin"
29+
"github.com/sqlc-dev/sqlc/internal/quickdb"
30+
pb "github.com/sqlc-dev/sqlc/internal/quickdb/v1"
2931
"github.com/sqlc-dev/sqlc/internal/shfmt"
32+
"github.com/sqlc-dev/sqlc/internal/sql/sqlpath"
3033
"github.com/sqlc-dev/sqlc/internal/vet"
3134
)
3235

@@ -376,6 +379,64 @@ type checker struct {
376379
Envmap map[string]string
377380
Stderr io.Writer
378381
NoDatabase bool
382+
Client pb.QuickClient
383+
}
384+
385+
func (c *checker) fetchDatabaseUri(ctx context.Context, s config.SQL) (string, func() error, error) {
386+
cleanup := func() error {
387+
return nil
388+
}
389+
390+
if s.Database == nil {
391+
panic("fetch database URI called with nil database")
392+
}
393+
if !s.Database.Managed {
394+
uri, err := c.DSN(s.Database.URI)
395+
return uri, cleanup, err
396+
}
397+
if s.Engine != config.EnginePostgreSQL {
398+
return "", cleanup, fmt.Errorf("managed: only PostgreSQL currently")
399+
}
400+
401+
if c.Client == nil {
402+
// FIXME: Eventual race condition
403+
client, err := quickdb.NewClientFromConfig(c.Conf.Cloud)
404+
if err != nil {
405+
return "", cleanup, fmt.Errorf("managed: client: %w", err)
406+
}
407+
c.Client = client
408+
}
409+
410+
var migrations []string
411+
files, err := sqlpath.Glob(s.Schema)
412+
if err != nil {
413+
return "", cleanup, err
414+
}
415+
for _, query := range files {
416+
contents, err := os.ReadFile(query)
417+
if err != nil {
418+
return "", cleanup, fmt.Errorf("read file: %w", err)
419+
}
420+
migrations = append(migrations, string(contents))
421+
}
422+
423+
resp, err := c.Client.CreateEphemeralDatabase(ctx, &pb.CreateEphemeralDatabaseRequest{
424+
Engine: "postgresql",
425+
Region: quickdb.GetClosestRegion(),
426+
Migrations: migrations,
427+
})
428+
if err != nil {
429+
return "", cleanup, fmt.Errorf("managed: create database: %w", err)
430+
}
431+
432+
cleanup = func() error {
433+
_, err := c.Client.DropEphemeralDatabase(ctx, &pb.DropEphemeralDatabaseRequest{
434+
DatabaseId: resp.DatabaseId,
435+
})
436+
return err
437+
}
438+
439+
return resp.Uri, cleanup, nil
379440
}
380441

381442
func (c *checker) DSN(dsn string) (string, error) {
@@ -422,10 +483,16 @@ func (c *checker) checkSQL(ctx context.Context, s config.SQL) error {
422483
if c.NoDatabase {
423484
return fmt.Errorf("database: connections disabled via command line flag")
424485
}
425-
dburl, err := c.DSN(s.Database.URI)
486+
dburl, cleanup, err := c.fetchDatabaseUri(ctx, s)
426487
if err != nil {
427488
return err
428489
}
490+
defer func() {
491+
if err := cleanup(); err != nil {
492+
fmt.Fprintf(c.Stderr, "error cleaning up: %s\n", err)
493+
}
494+
}()
495+
429496
switch s.Engine {
430497
case config.EnginePostgreSQL:
431498
conn, err := pgx.Connect(ctx, dburl)

0 commit comments

Comments
 (0)