Repository: sqlc-dev/sqlc
Pull request #2751 (2023-09-21 00:50:20)

feat(vet): Run rules against a managed database
The newest `vet` feature is running rules against databases hosted by us. You don't need to have PostgreSQL running locally, we'll take care of everything for you. This feature will be included in the next release as a private beta. We're still working out kinks in our infrastructure.

```yaml
version: '2'
cloud:
  project: "01HAQMMECEYQYKFJN8MP16QC41"
sql:
- schema: postgresql/schema.sql
  queries: postgresql/query.sql
  engine: postgresql
  database:
    managed: true
  rules:
  - sqlc/db-prepare
```


Patch:
diff --git a/.github/workflows/ci.yml b/.github/workflows/ci.yml
index 26bf764df..845cb5c2a 100644
--- a/.github/workflows/ci.yml
+++ b/.github/workflows/ci.yml
@@ -32,16 +32,6 @@ jobs:
     runs-on: ubuntu-latest
 
     services:
-      postgres:
-        image: "postgres:15"
-        env:
-          POSTGRES_DB: postgres
-          POSTGRES_PASSWORD: postgres
-          POSTGRES_USER: postgres
-        ports:
-        - 5432:5432
-        # needed because the postgres container does not provide a healthcheck
-        options: --health-cmd pg_isready --health-interval 10s --health-timeout 5s --health-retries 5
       mysql:
         image: "mysql/mysql-server:8.0"
         env:
@@ -69,17 +59,13 @@ jobs:
     - name: test ./...
       run: gotestsum --junitfile junit.xml -- --tags=examples ./...
       env:
-        PG_USER: postgres
-        PG_HOST: localhost
-        PG_DATABASE: postgres
-        PG_PASSWORD: postgres
-        PG_PORT: ${{ job.services.postgres.ports['5432'] }}
         MYSQL_DATABASE: mysql
         MYSQL_HOST: localhost
         MYSQL_PORT: ${{ job.services.mysql.ports['3306'] }}
         MYSQL_ROOT_PASSWORD: mysecretpassword
         CI_SQLC_PROJECT_ID: ${{ secrets.CI_SQLC_PROJECT_ID }}
         CI_SQLC_AUTH_TOKEN: ${{ secrets.CI_SQLC_AUTH_TOKEN }}
+        SQLC_AUTH_TOKEN: ${{ secrets.CI_SQLC_AUTH_TOKEN }}
 
     - name: build internal/endtoend
       run: go build ./...
diff --git a/docs/howto/managed-databases.md b/docs/howto/managed-databases.md
new file mode 100644
index 000000000..70b62b2f9
--- /dev/null
+++ b/docs/howto/managed-databases.md
@@ -0,0 +1,68 @@
+# Managed databases
+
+*Added in v1.22.0*
+
+`sqlc` can create and maintain hosted databases for your project. These
+databases are immediately useful for linting queries with [`sqlc vet`](vet.md)
+if your lint rules require a connection to a running database. PostgreSQL
+support is available today, with MySQL on the way.
+
+This feature is under active development, and we're interested in supporting
+other use-cases. Beyond linting queries, you can use sqlc managed databases
+in your tests to quickly stand up a database per test suite or even per test,
+providing a real, isolated database for a test run. No cleanup required.
+
+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
+at <mailto:hello@sqlc.dev>.
+
+## Configuring managed databases
+
+To configure `sqlc` to use a managed database, remove the `uri` key from your
+`database` configuration and replace it with the `managed` key set to `true`.
+Set the `project` key in your `cloud` configuration to the value of your
+project ID, obtained via the sqlc.dev Dashboard.
+
+```yaml
+version: '2'
+cloud:
+  project: '<PROJECT_ID>'
+sql:
+- schema: schema.sql
+  queries: query.sql
+  engine: postgresql
+  database:
+    managed: true
+```
+
+## Authentication
+
+`sqlc` expects to find a valid auth token in the value of the `SQLC_AUTH_TOKEN`
+environment variable. You can create an auth token via the sqlc.dev Dashboard.
+
+```shell
+export SQLC_AUTH_TOKEN=sqlc_xxxxxxxx
+```
+
+## Linting queries
+
+With managed databases configured, `sqlc vet` will create a database with your
+package's schema and use that database when running lint rules that require a
+database connection, e.g. any [rule relying on `EXPLAIN ...` output](vet.md#rules-using-explain-output).
+
+If you don't yet have any vet rules, the [built-in sqlc/db-prepare rule](vet.md#sqlc-db-prepare)
+is a good place to start. It prepares each of your queries against the database
+to ensure the query is valid. Here's a minimal working configuration:
+
+```yaml
+version: '2'
+cloud:
+  project: '<PROJECT_ID>'
+sql:
+- schema: schema.sql
+  queries: query.sql
+  engine: postgresql
+  database:
+    managed: true
+  rules:
+  - sqlc/db-prepare
+```
diff --git a/docs/requirements.txt b/docs/requirements.txt
index 05d306c51..215c2c144 100644
--- a/docs/requirements.txt
+++ b/docs/requirements.txt
@@ -24,4 +24,4 @@ sphinxcontrib-serializinghtml==1.1.9
 sphinxcontrib-htmlhelp==2.0.4
 sphinxcontrib-jsmath==1.0.1
 sphinxcontrib-qthelp==1.0.6
-urllib3==2.0.4
+urllib3==2.0.5
diff --git a/examples/authors/sqlc.yaml b/examples/authors/sqlc.yaml
index d43fb976d..8270782ed 100644
--- a/examples/authors/sqlc.yaml
+++ b/examples/authors/sqlc.yaml
@@ -1,10 +1,12 @@
 version: '2'
+cloud:
+  project: "01HAQMMECEYQYKFJN8MP16QC41"
 sql:
 - schema: postgresql/schema.sql
   queries: postgresql/query.sql
   engine: postgresql
   database:
-    uri: postgresql://${PG_USER}:${PG_PASSWORD}@${PG_HOST}:${PG_PORT}/authors
+    managed: true
   rules:
   - sqlc/db-prepare
   - postgresql-query-too-costly
diff --git a/examples/batch/sqlc.json b/examples/batch/sqlc.json
index dfd7ac809..8c27bc540 100644
--- a/examples/batch/sqlc.json
+++ b/examples/batch/sqlc.json
@@ -1,5 +1,8 @@
 {
   "version": "1",
+  "cloud": {
+    "project": "01HAQMMECEYQYKFJN8MP16QC41"
+  },
   "packages": [
     {
       "path": "postgresql",
@@ -8,7 +11,7 @@
       "queries": "postgresql/query.sql",
       "engine": "postgresql",
       "database": {
-        "uri": "postgresql://${PG_USER}:${PG_PASSWORD}@${PG_HOST}:${PG_PORT}/batch"
+        "managed": true
       },
       "rules": [
         "sqlc/db-prepare"
diff --git a/examples/jets/sqlc.json b/examples/jets/sqlc.json
index f8c5ef1ba..85a35a4f7 100644
--- a/examples/jets/sqlc.json
+++ b/examples/jets/sqlc.json
@@ -1,5 +1,8 @@
 {
   "version": "1",
+  "cloud": {
+    "project": "01HAQMMECEYQYKFJN8MP16QC41"
+  },
   "packages": [
     {
       "path": "postgresql",
@@ -8,7 +11,7 @@
       "queries": "postgresql/query-building.sql",
       "engine": "postgresql",
       "database": {
-        "uri": "postgresql://${PG_USER}:${PG_PASSWORD}@${PG_HOST}:${PG_PORT}/jets"
+        "managed": true
       },
       "rules": [
         "sqlc/db-prepare"
diff --git a/examples/ondeck/sqlc.json b/examples/ondeck/sqlc.json
index 61862a2a0..c9290db56 100644
--- a/examples/ondeck/sqlc.json
+++ b/examples/ondeck/sqlc.json
@@ -1,5 +1,8 @@
 {
   "version": "1",
+  "cloud": {
+    "project": "01HAQMMECEYQYKFJN8MP16QC41"
+  },
   "packages": [
     {
       "path": "postgresql",
@@ -8,7 +11,7 @@
       "queries": "postgresql/query",
       "engine": "postgresql",
       "database": {
-        "uri": "postgresql://${PG_USER}:${PG_PASSWORD}@${PG_HOST}:${PG_PORT}/ondeck"
+        "managed": true
       },
       "rules": [
         "sqlc/db-prepare"
diff --git a/go.mod b/go.mod
index dc49418bc..6200e08ef 100644
--- a/go.mod
+++ b/go.mod
@@ -22,7 +22,7 @@ require (
 	github.com/spf13/pflag v1.0.5
 	github.com/xeipuuv/gojsonschema v1.2.0
 	golang.org/x/sync v0.3.0
-	google.golang.org/grpc v1.58.1
+	google.golang.org/grpc v1.58.2
 	google.golang.org/protobuf v1.31.0
 	gopkg.in/yaml.v3 v3.0.1
 )
diff --git a/go.sum b/go.sum
index a75cc1c48..bd1b67b2d 100644
--- a/go.sum
+++ b/go.sum
@@ -299,8 +299,8 @@ google.golang.org/genproto/googleapis/api v0.0.0-20230803162519-f966b187b2e5 h1:
 google.golang.org/genproto/googleapis/api v0.0.0-20230803162519-f966b187b2e5/go.mod h1:5DZzOUPCLYL3mNkQ0ms0F3EuUNZ7py1Bqeq6sxzI7/Q=
 google.golang.org/genproto/googleapis/rpc v0.0.0-20230803162519-f966b187b2e5 h1:eSaPbMR4T7WfH9FvABk36NBMacoTUKdWCvV0dx+KfOg=
 google.golang.org/genproto/googleapis/rpc v0.0.0-20230803162519-f966b187b2e5/go.mod h1:zBEcrKX2ZOcEkHWxBPAIvYUWOKKMIhYcmNiUIu2ji3I=
-google.golang.org/grpc v1.58.1 h1:OL+Vz23DTtrrldqHK49FUOPHyY75rvFqJfXC84NYW58=
-google.golang.org/grpc v1.58.1/go.mod h1:tgX3ZQDlNJGU96V6yHh1T/JeoBQ2TXdr43YbYSsCJk0=
+google.golang.org/grpc v1.58.2 h1:SXUpjxeVF3FKrTYQI4f4KvbGD5u2xccdYdurwowix5I=
+google.golang.org/grpc v1.58.2/go.mod h1:tgX3ZQDlNJGU96V6yHh1T/JeoBQ2TXdr43YbYSsCJk0=
 google.golang.org/protobuf v0.0.0-20200109180630-ec00e32a8dfd/go.mod h1:DFci5gLYBciE7Vtevhsrf46CRTquxDuWsQurQQe4oz8=
 google.golang.org/protobuf v0.0.0-20200221191635-4d8936d0db64/go.mod h1:kwYJMbMJ01Woi6D6+Kah6886xMZcty6N08ah7+eCXa0=
 google.golang.org/protobuf v0.0.0-20200228230310-ab0ca4ff8a60/go.mod h1:cfTl7dwQJ+fmap5saPgwCLgHXTUD7jkjRqWcaiX5VyM=
diff --git a/internal/cmd/cmd.go b/internal/cmd/cmd.go
index 3548ce0df..4b542d2f4 100644
--- a/internal/cmd/cmd.go
+++ b/internal/cmd/cmd.go
@@ -37,6 +37,7 @@ func Do(args []string, stdin io.Reader, stdout io.Writer, stderr io.Writer) int
 	rootCmd.PersistentFlags().StringP("file", "f", "", "specify an alternate config file (default: sqlc.yaml)")
 	rootCmd.PersistentFlags().BoolP("experimental", "x", false, "DEPRECATED: enable experimental features (default: false)")
 	rootCmd.PersistentFlags().Bool("no-remote", false, "disable remote execution (default: false)")
+	rootCmd.PersistentFlags().Bool("remote", false, "enable remote execution (default: false)")
 	rootCmd.PersistentFlags().Bool("no-database", false, "disable database connections (default: false)")
 
 	rootCmd.AddCommand(checkCmd)
@@ -136,17 +137,20 @@ var initCmd = &cobra.Command{
 type Env struct {
 	DryRun     bool
 	Debug      opts.Debug
+	Remote     bool
 	NoRemote   bool
 	NoDatabase bool
 }
 
 func ParseEnv(c *cobra.Command) Env {
 	dr := c.Flag("dry-run")
+	r := c.Flag("remote")
 	nr := c.Flag("no-remote")
 	nodb := c.Flag("no-database")
 	return Env{
 		DryRun:     dr != nil && dr.Changed,
 		Debug:      opts.DebugFromEnv(),
+		Remote:     r != nil && nr.Value.String() == "true",
 		NoRemote:   nr != nil && nr.Value.String() == "true",
 		NoDatabase: nodb != nil && nodb.Value.String() == "true",
 	}
diff --git a/internal/cmd/generate.go b/internal/cmd/generate.go
index 40e1dcbca..589295e74 100644
--- a/internal/cmd/generate.go
+++ b/internal/cmd/generate.go
@@ -145,7 +145,7 @@ func Generate(ctx context.Context, e Env, dir, filename string, stderr io.Writer
 		return nil, err
 	}
 
-	if conf.Cloud.Project != "" && !e.NoRemote {
+	if conf.Cloud.Project != "" && e.Remote && !e.NoRemote {
 		return remoteGenerate(ctx, configPath, conf, dir, stderr)
 	}
 
diff --git a/internal/cmd/vet.go b/internal/cmd/vet.go
index da0189e9d..48fbc2a41 100644
--- a/internal/cmd/vet.go
+++ b/internal/cmd/vet.go
@@ -26,7 +26,10 @@ import (
 	"github.com/sqlc-dev/sqlc/internal/debug"
 	"github.com/sqlc-dev/sqlc/internal/opts"
 	"github.com/sqlc-dev/sqlc/internal/plugin"
+	"github.com/sqlc-dev/sqlc/internal/quickdb"
+	pb "github.com/sqlc-dev/sqlc/internal/quickdb/v1"
 	"github.com/sqlc-dev/sqlc/internal/shfmt"
+	"github.com/sqlc-dev/sqlc/internal/sql/sqlpath"
 	"github.com/sqlc-dev/sqlc/internal/vet"
 )
 
@@ -376,6 +379,64 @@ type checker struct {
 	Envmap     map[string]string
 	Stderr     io.Writer
 	NoDatabase bool
+	Client     pb.QuickClient
+}
+
+func (c *checker) fetchDatabaseUri(ctx context.Context, s config.SQL) (string, func() error, error) {
+	cleanup := func() error {
+		return nil
+	}
+
+	if s.Database == nil {
+		panic("fetch database URI called with nil database")
+	}
+	if !s.Database.Managed {
+		uri, err := c.DSN(s.Database.URI)
+		return uri, cleanup, err
+	}
+	if s.Engine != config.EnginePostgreSQL {
+		return "", cleanup, fmt.Errorf("managed: only PostgreSQL currently")
+	}
+
+	if c.Client == nil {
+		// FIXME: Eventual race condition
+		client, err := quickdb.NewClientFromConfig(c.Conf.Cloud)
+		if err != nil {
+			return "", cleanup, fmt.Errorf("managed: client: %w", err)
+		}
+		c.Client = client
+	}
+
+	var migrations []string
+	files, err := sqlpath.Glob(s.Schema)
+	if err != nil {
+		return "", cleanup, err
+	}
+	for _, query := range files {
+		contents, err := os.ReadFile(query)
+		if err != nil {
+			return "", cleanup, fmt.Errorf("read file: %w", err)
+		}
+		migrations = append(migrations, string(contents))
+	}
+
+	resp, err := c.Client.CreateEphemeralDatabase(ctx, &pb.CreateEphemeralDatabaseRequest{
+		Engine:     "postgresql",
+		Region:     quickdb.GetClosestRegion(),
+		Migrations: migrations,
+	})
+	if err != nil {
+		return "", cleanup, fmt.Errorf("managed: create database: %w", err)
+	}
+
+	cleanup = func() error {
+		_, err := c.Client.DropEphemeralDatabase(ctx, &pb.DropEphemeralDatabaseRequest{
+			DatabaseId: resp.DatabaseId,
+		})
+		return err
+	}
+
+	return resp.Uri, cleanup, nil
 }
 
 func (c *checker) DSN(dsn string) (string, error) {
@@ -422,10 +483,16 @@ func (c *checker) checkSQL(ctx context.Context, s config.SQL) error {
 		if c.NoDatabase {
 			return fmt.Errorf("database: connections disabled via command line flag")
 		}
-		dburl, err := c.DSN(s.Database.URI)
+		dburl, cleanup, err := c.fetchDatabaseUri(ctx, s)
 		if err != nil {
 			return err
 		}
+		defer func() {
+			if err := cleanup(); err != nil {
+				fmt.Fprintf(c.Stderr, "error cleaning up: %s\n", err)
+			}
+		}()
+
 		switch s.Engine {
 		case config.EnginePostgreSQL:
 			conn, err := pgx.Connect(ctx, dburl)
diff --git a/internal/config/config.go b/internal/config/config.go
index 35270fbba..d28ec0e62 100644
--- a/internal/config/config.go
+++ b/internal/config/config.go
@@ -69,7 +69,8 @@ type Project struct {
 }
 
 type Database struct {
-	URI string `json:"uri" yaml:"uri"`
+	URI     string `json:"uri" yaml:"uri"`
+	Managed bool   `json:"managed" yaml:"managed"`
 }
 
 type Cloud struct {
diff --git a/internal/config/v_one.json b/internal/config/v_one.json
index 5d464a802..329b012ff 100644
--- a/internal/config/v_one.json
+++ b/internal/config/v_one.json
@@ -77,6 +77,9 @@
                         "properties": {
                             "uri": {
                                 "type": "string"
+                            },
+                            "managed": {
+                                "type": "boolean"
                             }
                         }
                     },
diff --git a/internal/config/v_two.json b/internal/config/v_two.json
index bfbfaab67..65fdd7bb7 100644
--- a/internal/config/v_two.json
+++ b/internal/config/v_two.json
@@ -77,6 +77,9 @@
                         "properties": {
                             "uri": {
                                 "type": "string"
+                            },
+                            "managed": {
+                                "type": "boolean"
                             }
                         }
                     },
diff --git a/internal/config/validate.go b/internal/config/validate.go
index e0e056fd6..207a888ec 100644
--- a/internal/config/validate.go
+++ b/internal/config/validate.go
@@ -12,8 +12,8 @@ func Validate(c *Config) error {
 			return fmt.Errorf("invalid config: emit_methods_with_db_argument and emit_prepared_queries settings are mutually exclusive")
 		}
 		if sql.Database != nil {
-			if sql.Database.URI == "" {
-				return fmt.Errorf("invalid config: database must have a non-empty URI")
+			if sql.Database.URI == "" && !sql.Database.Managed {
+				return fmt.Errorf("invalid config: database must be managed or have a non-empty URI")
 			}
 		}
 	}
diff --git a/internal/quickdb/region.go b/internal/quickdb/region.go
index bc03e3b3f..1900e33a1 100644
--- a/internal/quickdb/region.go
+++ b/internal/quickdb/region.go
@@ -10,9 +10,9 @@ var once sync.Once
 
 func GetClosestRegion() string {
 	once.Do(func() {
-		resp, err := http.Get("https://debug.fly.dev")
+		resp, err := http.Get("https://find-closest-db-region.sqlc.dev")
 		if err == nil {
-			region = resp.Header.Get("Fly-Region")
+			region = resp.Header.Get("Region")
 		}
 	})
 	return region


Test patch:
diff --git a/examples/booktest/sqlc.json b/examples/booktest/sqlc.json
index 8220d3601..72cd08936 100644
--- a/examples/booktest/sqlc.json
+++ b/examples/booktest/sqlc.json
@@ -1,5 +1,8 @@
 {
   "version": "1",
+  "cloud": {
+    "project": "01HAQMMECEYQYKFJN8MP16QC41"
+  },
   "packages": [
     {
       "name": "booktest",
@@ -8,7 +11,7 @@
       "queries": "postgresql/query.sql",
       "engine": "postgresql",
       "database": {
-        "uri": "postgresql://${PG_USER}:${PG_PASSWORD}@${PG_HOST}:${PG_PORT}/booktest"
+        "managed": true
       },
       "rules": [
         "sqlc/db-prepare"
diff --git a/internal/endtoend/vet_test.go b/internal/endtoend/vet_test.go
index d453189e2..74f3ea903 100644
--- a/internal/endtoend/vet_test.go
+++ b/internal/endtoend/vet_test.go
@@ -51,11 +51,6 @@ func TestExamplesVet(t *testing.T) {
 			path := filepath.Join(examples, tc)
 
 			if tc != "kotlin" && tc != "python" {
-				if s, found := findSchema(t, filepath.Join(path, "postgresql")); found {
-					db, cleanup := sqltest.CreatePostgreSQLDatabase(t, tc, false, []string{s})
-					defer db.Close()
-					defer cleanup()
-				}
 				if s, found := findSchema(t, filepath.Join(path, "mysql")); found {
 					db, cleanup := sqltest.CreateMySQLDatabase(t, tc, []string{s})
 					defer db.Close()