Repository: supabase/cli
Pull request #326 (2022-08-03 09:57:38)

feat: add db test command using pgtap
## What kind of change does this PR introduce?

feature: `supabase db test`

## What is the current behavior?

no cli native way to run tests

## What is the new behavior?

- installs `pg_prove` on `supabase_db_cli` container once
- copies `supabase/tests` into `supabase_db_cli` container
- enable pgtap extension temporarily
- runs `pg_prove` via docker exec and prints out results

## Additional context

Example `order_test.pg`
```sql
BEGIN;
SELECT plan(1);

SELECT has_column(
    'public',
    'order',
    'status',
    'status should exist'
);

SELECT * FROM finish();
ROLLBACK;
```

Output
```bash
$ supabase db test
supabase/tests/order_test.pg .. ok
supabase/tests/pet_test.pg .... ok
All tests successful.
Files=2, Tests=2,  1 wallclock secs ( 0.01 usr  0.00 sys +  0.04 cusr  0.02 csys =  0.07 CPU)
Result: PASS
```

coveralls: ## Pull Request Test Coverage Report for [Build 2788525934](https://coveralls.io/builds/51378774)

* **1** of **99**   **(1.01%)**  changed or added relevant lines in **2** files are covered.
* No unchanged relevant lines lost coverage.
* Overall coverage decreased (**-0.4%**) to **21.163%**

---

|  Changes Missing Coverage | Covered Lines | Changed/Added Lines | % |
| :-----|--------------|--------|---: |
| [cmd/db.go](https://coveralls.io/builds/51378774/source?filename=cmd%2Fdb.go#L142) | 1 | 4 | 25.0%
| [internal/db/test/test.go](https://coveralls.io/builds/51378774/source?filename=internal%2Fdb%2Ftest%2Ftest.go#L30) | 0 | 95 | 0.0%
<!-- | **Total:** | **1** | **99** | **1.01%** | -->


|  Totals | [![Coverage Status](https://coveralls.io/builds/51378774/badge)](https://coveralls.io/builds/51378774) |
| :-- | --: |
| Change from base [Build 2772753045](https://coveralls.io/builds/51300365): |  -0.4% |
| Covered Lines: | 1106 |
| Relevant Lines: | 5226 |

---
##### 💛  - [Coveralls](https://coveralls.io)

psteinroe: this is awesome!! Are tests within nested directories also supported?
soedirgo: I think instead of manually traversing the dirs, we can instead use one file as an entrypoint and use the [`\i`/`\include` directive](https://github.com/theory/pgtap/blob/ff4a30b59d602ab7a3ea48ce91402fa9bc83abdf/test/setup.sql#L1) to include other files.

Or use the [`--recurse` flag](https://pgtap.org/pg_prove.html) from pg_prove.
psteinroe: > I think instead of manually traversing the dirs, we can instead use one file as an entrypoint and use the [`\i`/`\include` directive](https://github.com/theory/pgtap/blob/ff4a30b59d602ab7a3ea48ce91402fa9bc83abdf/test/setup.sql#L1) to include other files.
> 
> 
> 
> Or use the [`--recurse` flag](https://pgtap.org/pg_prove.html) from pg_prove.

+1 for the recurse flag. The \include directive would require the user to manually include hundreds of files eventually. 
sweatybridge: Thanks for the suggestions! Added `--recurse` flag and undid changes to initial schema.

Patch:
diff --git a/cmd/db.go b/cmd/db.go
index 85aeb5f7..54c9b503 100644
--- a/cmd/db.go
+++ b/cmd/db.go
@@ -17,6 +17,7 @@ import (
 	"github.com/supabase/cli/internal/db/remote/changes"
 	"github.com/supabase/cli/internal/db/remote/commit"
 	"github.com/supabase/cli/internal/db/reset"
+	"github.com/supabase/cli/internal/db/test"
 	"github.com/supabase/cli/internal/utils"
 )
 
@@ -149,7 +150,17 @@ var (
 		Use:   "lint",
 		Short: "Checks local database for typing error",
 		RunE: func(cmd *cobra.Command, args []string) error {
-			return lint.Run(cmd.Context(), schema, level.Value, afero.NewOsFs())
+			ctx, _ := signal.NotifyContext(cmd.Context(), os.Interrupt)
+			return lint.Run(ctx, schema, level.Value, afero.NewOsFs())
+		},
+	}
+
+	dbTestCmd = &cobra.Command{
+		Use:   "test",
+		Short: "Tests local database with pgTAP.",
+		RunE: func(cmd *cobra.Command, args []string) error {
+			ctx, _ := signal.NotifyContext(cmd.Context(), os.Interrupt)
+			return test.Run(ctx, afero.NewOsFs())
 		},
 	}
 )
@@ -187,5 +198,7 @@ func init() {
 	lintFlags.StringSliceVarP(&schema, "schema", "s", []string{"public"}, "List of schema to include.")
 	lintFlags.Var(&level, "level", "Error level to emit.")
 	dbCmd.AddCommand(dbLintCmd)
+	// Build test command
+	dbCmd.AddCommand(dbTestCmd)
 	rootCmd.AddCommand(dbCmd)
 }
diff --git a/internal/db/branch/switch_/switch_.go b/internal/db/branch/switch_/switch_.go
index 4e800765..9e7b66d7 100644
--- a/internal/db/branch/switch_/switch_.go
+++ b/internal/db/branch/switch_/switch_.go
@@ -65,7 +65,7 @@ func switchDatabase(ctx context.Context, source, target string, options ...func(
 	if err != nil {
 		return err
 	}
-	defer conn.Close(ctx)
+	defer conn.Close(context.Background())
 	if err := reset.DisconnectClients(ctx, conn); err != nil {
 		return err
 	}
diff --git a/internal/db/diff/migra.go b/internal/db/diff/migra.go
index a680fbcd..a88e02ad 100644
--- a/internal/db/diff/migra.go
+++ b/internal/db/diff/migra.go
@@ -103,7 +103,7 @@ func ApplyMigrations(ctx context.Context, url string, fsys afero.Fs, options ...
 	if err != nil {
 		return err
 	}
-	defer conn.Close(ctx)
+	defer conn.Close(context.Background())
 	// Apply migrations
 	if migrations, err := afero.ReadDir(fsys, utils.MigrationsDir); err == nil {
 		for i, migration := range migrations {
diff --git a/internal/db/lint/lint.go b/internal/db/lint/lint.go
index 269ea1bc..404e6dc0 100644
--- a/internal/db/lint/lint.go
+++ b/internal/db/lint/lint.go
@@ -51,7 +51,7 @@ func Run(ctx context.Context, schema []string, level string, fsys afero.Fs, opts
 	if err != nil {
 		return err
 	}
-	defer conn.Close(ctx)
+	defer conn.Close(context.Background())
 	result, err := LintDatabase(ctx, conn, schema)
 	if err != nil {
 		return err
diff --git a/internal/db/reset/reset.go b/internal/db/reset/reset.go
index 0eebf7cc..34411206 100644
--- a/internal/db/reset/reset.go
+++ b/internal/db/reset/reset.go
@@ -95,7 +95,7 @@ func SeedDatabase(ctx context.Context, url string, fsys afero.Fs, options ...fun
 	if err != nil {
 		return err
 	}
-	defer conn.Close(ctx)
+	defer conn.Close(context.Background())
 	// Batch seed commands, safe to use statement cache
 	batch := pgx.Batch{}
 	lines, err := parser.Split(sql)
@@ -130,7 +130,7 @@ func ActivateDatabase(ctx context.Context, branch string, options ...func(*pgx.C
 	if err != nil {
 		return err
 	}
-	defer conn.Close(ctx)
+	defer conn.Close(context.Background())
 	if err := DisconnectClients(ctx, conn); err != nil {
 		return err
 	}
diff --git a/internal/utils/misc.go b/internal/utils/misc.go
index d498d57f..1b61f841 100644
--- a/internal/utils/misc.go
+++ b/internal/utils/misc.go
@@ -75,6 +75,7 @@ DO 'BEGIN WHILE (
 	CurrBranchPath = "supabase/.branches/_current_branch"
 	MigrationsDir  = "supabase/migrations"
 	FunctionsDir   = "supabase/functions"
+	DbTestsDir     = "supabase/tests"
 	SeedDataPath   = "supabase/seed.sql"
 )
 


Test patch:
diff --git a/internal/db/test/templates/test.sh b/internal/db/test/templates/test.sh
new file mode 100755
index 00000000..0295245f
--- /dev/null
+++ b/internal/db/test/templates/test.sh
@@ -0,0 +1,29 @@
+#!/bin/bash
+set -euo pipefail
+
+# TODO: move this dependency to base image
+if ! command -v pg_prove &> /dev/null; then
+    apt-get -qq update && apt-get -qq install postgresql-14-pgtap
+fi
+
+# temporarily enable pgtap
+enable="create extension if not exists pgtap with schema extensions"
+notice=$(psql -h localhost -U postgres -p 5432 -d postgres -c "$enable" 2>&1 >/dev/null)
+
+# run postgres unit tests
+pg_prove -h localhost -U postgres -r "$@"
+
+cleanup() {
+    # save the return code of the script
+    status=$?
+    # disable pgtap if previously not enabled
+    if [ -z "$notice" ]; then
+        psql -h localhost -U postgres -p 5432 -d postgres -c "drop extension if exists pgtap"
+    fi
+    # clean up test files
+    rm -rf "$@"
+    # actually quit
+    exit $status
+}
+
+trap cleanup EXIT
diff --git a/internal/db/test/test.go b/internal/db/test/test.go
new file mode 100644
index 00000000..e42d10ce
--- /dev/null
+++ b/internal/db/test/test.go
@@ -0,0 +1,114 @@
+package test
+
+import (
+	"archive/tar"
+	"bytes"
+	"context"
+	_ "embed"
+	"fmt"
+	"io"
+	"os"
+	"path/filepath"
+
+	"github.com/docker/docker/api/types"
+	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/utils"
+)
+
+var (
+	//go:embed templates/test.sh
+	testScript string
+)
+
+func Run(ctx context.Context, fsys afero.Fs) error {
+	// Sanity checks.
+	{
+		if err := utils.LoadConfigFS(fsys); err != nil {
+			return err
+		}
+
+		if err := utils.AssertSupabaseDbIsRunning(); err != nil {
+			return err
+		}
+	}
+
+	var buf bytes.Buffer
+	if err := compress(utils.DbTestsDir, &buf, fsys); err != nil {
+		return err
+	}
+	dstPath := "/tmp"
+	if err := utils.Docker.CopyToContainer(ctx, utils.DbId, dstPath, &buf, types.CopyToContainerOptions{}); err != nil {
+		return err
+	}
+
+	// Requires unix path inside container
+	cmd := []string{"/bin/bash", "-c", testScript, dstPath + "/" + utils.DbTestsDir}
+	out, err := utils.DockerExecOnce(ctx, utils.DbId, nil, cmd)
+	if err != nil {
+		return err
+	}
+
+	fmt.Print(out)
+	return nil
+}
+
+// Ref 1: https://medium.com/@skdomino/taring-untaring-files-in-go-6b07cf56bc07
+// Ref 2: https://gist.github.com/mimoo/25fc9716e0f1353791f5908f94d6e726
+func compress(src string, buf io.Writer, fsys afero.Fs) error {
+	tw := tar.NewWriter(buf)
+
+	// walk through every file in the folder
+	if err := afero.Walk(fsys, src, func(file string, fi os.FileInfo, err error) error {
+		// return on any error
+		if err != nil {
+			fmt.Println(err)
+			return err
+		}
+
+		// return on non-regular files
+		if !fi.Mode().IsRegular() {
+			return nil
+		}
+
+		// create a new dir/file header
+		header, err := tar.FileInfoHeader(fi, fi.Name())
+		if err != nil {
+			return err
+		}
+
+		// must provide real name
+		header.Name = filepath.ToSlash(file)
+
+		// write the header
+		if err := tw.WriteHeader(header); err != nil {
+			return err
+		}
+
+		// open files for taring
+		f, err := fsys.Open(file)
+		if err != nil {
+			return err
+		}
+
+		// copy file data into tar writer
+		if _, err := io.Copy(tw, f); err != nil {
+			return err
+		}
+
+		// manually close here after each file operation; defering would cause each file close
+		// to wait until all operations have completed.
+		if err := f.Close(); err != nil {
+			return err
+		}
+
+		return nil
+	}); err != nil {
+		return err
+	}
+
+	// produce tar
+	if err := tw.Close(); err != nil {
+		return err
+	}
+	return nil
+}
diff --git a/internal/db/test/test_test.go b/internal/db/test/test_test.go
new file mode 100644
index 00000000..9ee0a208
--- /dev/null
+++ b/internal/db/test/test_test.go
@@ -0,0 +1,23 @@
+package test
+
+import (
+	"bytes"
+	"path/filepath"
+	"testing"
+
+	"github.com/spf13/afero"
+	"github.com/stretchr/testify/assert"
+	"github.com/stretchr/testify/require"
+)
+
+func TestTarDirectory(t *testing.T) {
+	t.Run("tars a given directory", func(t *testing.T) {
+		// Setup in-memory fs
+		fsys := afero.NewMemMapFs()
+		dir := "/tests"
+		require.NoError(t, afero.WriteFile(fsys, filepath.Join(dir, "order_test.pg"), []byte("SELECT 0;"), 0644))
+		// Run test
+		var buf bytes.Buffer
+		assert.NoError(t, compress(dir, &buf, fsys))
+	})
+}