Repository: genjidb/genji
Pull request #380 (2021-04-09 10:31:01)

Work out a new way to write integration tests
And possibly GitHub issues, when we'll have iterated enough on it :) 

➡️ check the README for an extensive description of what it does and how it works.

To avoid messing with paths (the test template), it uses `embed` from Go 1.16. It can be an issue, and it could be fixed if needed. 

codecov-io: # [Codecov](https://codecov.io/gh/genjidb/genji/pull/380?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb) Report
> Merging [#380](https://codecov.io/gh/genjidb/genji/pull/380?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb) (4bf9ede) into [main](https://codecov.io/gh/genjidb/genji/commit/ade8778b03ee121d0ff4c0a6c0ebb92af357b80a?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb) (ade8778) will **increase** coverage by `0.19%`.
> The diff coverage is `62.57%`.

[![Impacted file tree graph](https://codecov.io/gh/genjidb/genji/pull/380/graphs/tree.svg?width=650&height=150&src=pr&token=nwR1GpvtyH&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb)](https://codecov.io/gh/genjidb/genji/pull/380?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb)

```diff
@@            Coverage Diff             @@
##             main     #380      +/-   ##
==========================================
+ Coverage   68.15%   68.35%   +0.19%     
==========================================
  Files          74       77       +3     
  Lines        7367     7650     +283     
==========================================
+ Hits         5021     5229     +208     
- Misses       1679     1743      +64     
- Partials      667      678      +11     
```


| [Impacted Files](https://codecov.io/gh/genjidb/genji/pull/380?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb) | Coverage Δ | |
|---|---|---|
| [cmd/examplar/main.go](https://codecov.io/gh/genjidb/genji/pull/380/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb#diff-Y21kL2V4YW1wbGFyL21haW4uZ28=) | `3.03% <3.03%> (ø)` | |
| [cmd/examplar/scanner.go](https://codecov.io/gh/genjidb/genji/pull/380/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb#diff-Y21kL2V4YW1wbGFyL3NjYW5uZXIuZ28=) | `70.19% <70.19%> (ø)` | |
| [cmd/examplar/examplar.go](https://codecov.io/gh/genjidb/genji/pull/380/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb#diff-Y21kL2V4YW1wbGFyL2V4YW1wbGFyLmdv) | `97.05% <97.05%> (ø)` | |
| [sql/parser/drop.go](https://codecov.io/gh/genjidb/genji/pull/380/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb#diff-c3FsL3BhcnNlci9kcm9wLmdv) | `55.17% <0.00%> (-7.69%)` | :arrow_down: |
| [sql/parser/parser.go](https://codecov.io/gh/genjidb/genji/pull/380/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb#diff-c3FsL3BhcnNlci9wYXJzZXIuZ28=) | `81.65% <0.00%> (-2.19%)` | :arrow_down: |
| [sql/driver/driver.go](https://codecov.io/gh/genjidb/genji/pull/380/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb#diff-c3FsL2RyaXZlci9kcml2ZXIuZ28=) | `63.19% <0.00%> (-1.23%)` | :arrow_down: |
| [database/constraint.go](https://codecov.io/gh/genjidb/genji/pull/380/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb#diff-ZGF0YWJhc2UvY29uc3RyYWludC5nbw==) | `69.37% <0.00%> (-1.02%)` | :arrow_down: |
| [database/catalog.go](https://codecov.io/gh/genjidb/genji/pull/380/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb#diff-ZGF0YWJhc2UvY2F0YWxvZy5nbw==) | `81.08% <0.00%> (-0.88%)` | :arrow_down: |
| [document/document.go](https://codecov.io/gh/genjidb/genji/pull/380/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb#diff-ZG9jdW1lbnQvZG9jdW1lbnQuZ28=) | `65.91% <0.00%> (-0.50%)` | :arrow_down: |
| ... and [10 more](https://codecov.io/gh/genjidb/genji/pull/380/diff?src=pr&el=tree-more&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/genjidb/genji/pull/380?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/genjidb/genji/pull/380?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb). Last update [ade8778...4bf9ede](https://codecov.io/gh/genjidb/genji/pull/380?src=pr&el=lastupdated&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb).

asdine: ![image](https://external-content.duckduckgo.com/iu/?u=https%3A%2F%2Ftse1.mm.bing.net%2Fth%3Fid%3DOIP.Yp5PpdZHDSqmhJiC0ELrDQHaHa%26pid%3DApi&f=1)

Patch:
diff --git a/.travis.yml b/.travis.yml
index 8fd85651..8e09b448 100644
--- a/.travis.yml
+++ b/.travis.yml
@@ -4,7 +4,6 @@ env:
   - GO111MODULE=on
 
 go:
-  - "1.15.x"
   - "1.16.x"
   - tip
 
diff --git a/cmd/examplar/.gitignore b/cmd/examplar/.gitignore
new file mode 100644
index 00000000..64a5e0c9
--- /dev/null
+++ b/cmd/examplar/.gitignore
@@ -0,0 +1,3 @@
+examplar
+trace_test.go
+integration
diff --git a/cmd/examplar/README.md b/cmd/examplar/README.md
new file mode 100644
index 00000000..4019a2e9
--- /dev/null
+++ b/cmd/examplar/README.md
@@ -0,0 +1,130 @@
+# Examplar
+
+A tool to generate tests from on an annotated SQL file that specifies an example and its expected result.
+
+A file named `selecting.sql`
+
+```sql
+--- setup:
+CREATE TABLE foo;
+--- teardown:
+DROP TABLE foo;
+--- test: insert something
+INSERT INTO foo (a) VALUES (1);
+SELECT * FROM foo;
+--- `[{"a": 1}]`
+```
+
+Gets transformed into (abbreviated):
+
+```go
+func TestSelecting(t *testing.T) {
+    db, err := genji.Open(":memory:")
+
+    teardown := func() {
+       db.Exec("DROP TABLE foo;")
+    }
+
+    setup := func() {
+        err = db.Exec("CREATE TABLE foo (a int);")
+    }
+
+    t.Run("insert something", func(t *testing.T) {
+        t.Cleanup(teardown)
+        setup()
+
+        res, err := db.Query(
+          "INSERT INTO foo (a) VALUES (1);" +
+          "SELECT * FROM foo;"
+        )
+        require.NoError(t, err)
+        defer res.Close()
+
+        data := jsonResult(t, res)
+
+        expected = `[{"a": 1}]`
+        require.JSONEq(t, expected, string(data))
+    }
+}
+```
+
+## Usage
+
+To populate tests that would be in an `integration` package, create a go file in folder named `integration` with the following code:
+
+```go
+package integration
+
+//go:generate examplar -package=integration fixtures/sql/*.sql
+```
+
+Assuming `fixtures/sql/` contains the following files:
+
+```
+extest1.sql
+extest2.sql
+```
+
+Running `go generate` will generate these tests files in the `integration` folder:
+
+```
+extest1_test.go
+extest2_test.go
+```
+
+Those tests files can be run like any normal tests.
+
+## How it works
+
+Examplar reads a SQL file, looking either for raw text or annotations that specifies what those lines are supposed to do from a testing perpective.
+
+Once a SQL file has been parsed, it generates a `(...)_test.go` file that looks similar to the handwritten test that would have been written.
+
+Every original line in the input SQL file is executed and expected to not return any error.
+
+Those test files have no dependencies on Examplar or the SQL file that has been used to generate the test.
+
+### Annotations
+
+Annotations starts with `---` (the SQL comment `--` and an additional `-`) and can
+be followed by a keyword specified in the list below followed by a `:` or special symbols to pass data to set expectations.
+
+- `setup:`
+
+  - all lines up to the next annotation are to be considered as one single statement for the setup block.
+
+- `teardown:`
+  - all lines up to the next annotation are to be considered as single statement for the teardown block.
+
+:bulb: `setup` and `teardown` blocks will generate code being ran around **each indidual** `test` block.
+They are optional and can be declared in no particular order, but there can only be one of each.
+
+- `test: [TEST NAME]`
+  - a test is composed of one or many statements; a statement composed of one or multiple lines and is terminated by an expectation (see below).
+
+:bulb: Each `test` block will generate an individual `t.Run("[TEST NAME]", ...)` function. At least one test block must be present.
+
+- `` `[JSON]` ``
+
+  - the statement above this annotation will be compared to `[JSON]` when evaluated at the runtime.
+  - Invalid JSON won't yield an error ar generate time, but the generated test will always fail at runtime.
+
+- ` ``` `
+  - the statement above this annotation will be compared to `[JSON]` when evaluated at the runtime.
+  - all the following lines until another triple backtick annoattion is found are to be considered as part of a single multiline JSON data.
+  - indentation will be preserved in the generating test file for readablilty. Similarly, invalid JSON will only yield an error when the resulting test is evaluated.
+
+## Goals and non-goals
+
+Examplar objective is to provide a clear and simple way to write example of Genji SQL code and its expected results. It has to be easy enough for anyone to edit or write an example SQL file without having to read the present documentation. If this objective succeeds, it opens the path Examplar SQL files being used by users and contributors to showcase a bug or a feature request in a Github issue.
+
+By being totally independent from Genji itself for the parsing part, it frees itself from needing to be updated when something changes under the hood and allows to keep the code as merely parsing textual data. In other words, if anything changes in Genji on how to execute queries, only the template needs to be updated.
+
+## Limitations
+
+A breaking change in the API has only
+For now, let's observe how useful Examplar can and what we can make out of it.
+Then we can then see if it's worth addressing the following limitations:
+
+- Expecting an error instead of JSON is not supported.
+- Error messages on failed expectations do not reference the orignal file directly, which could be useful on complex examples sql files.
diff --git a/cmd/examplar/examplar.go b/cmd/examplar/examplar.go
new file mode 100644
index 00000000..2374b295
--- /dev/null
+++ b/cmd/examplar/examplar.go
@@ -0,0 +1,114 @@
+package main
+
+import (
+	"bufio"
+	"fmt"
+	"io"
+	"strings"
+	"text/template"
+)
+
+type Line struct {
+	Orig string
+	Text string
+}
+
+// Test is a list of statements.
+type Test struct {
+	Name       string
+	Orig       string
+	Statements []*Statement
+}
+
+// Statement is a pair composed of a line of code and an expectation on its result when evaluated.
+type Statement struct {
+	Code        []Line
+	Expectation []Line
+}
+
+func (s Statement) expectationText() string {
+	var text string
+	for _, e := range s.Expectation {
+		text += e.Text + "\n"
+	}
+
+	return text
+}
+
+// Examplar represents a group of tests and can optionally include setup code and teardown code.
+type Examplar struct {
+	Name             string
+	originalFilename string
+	setup            []Line
+	teardown         []Line
+	examples         []*Test
+}
+
+func (ex *Examplar) origLoc(num int) string {
+	return fmt.Sprintf("%s:%d", ex.originalFilename, num)
+}
+
+// HasSetup returns true if setup code is provided.
+func (ex *Examplar) HasSetup() bool {
+	return len(ex.setup) > 0
+}
+
+// HasSetup returns true if teardown code is provided.
+func (ex *Examplar) HasTeardown() bool {
+	return len(ex.teardown) > 0
+}
+
+func (ex *Examplar) appendTest(name string, num int) *Test {
+	test := Test{
+		Name: name,
+		Orig: ex.origLoc(num),
+	}
+	ex.examples = append(ex.examples, &test)
+
+	return &test
+}
+
+// Parse reads annotated textual data and transforms it into a
+// structured representation. Only annotations are parsed, the
+// textual data itself is irrelevant to this function.
+//
+// It will panic if an error is encountered.
+func Parse(r io.Reader, name string, originalFilename string) *Examplar {
+	ex := Examplar{
+		Name:             name,
+		originalFilename: originalFilename,
+	}
+
+	scanner := &Scanner{ex: &ex}
+	scanner.Run(bufio.NewScanner(r))
+
+	return &ex
+}
+
+func normalizeTestName(name string) string {
+	name = strings.TrimSpace(name)
+	name = strings.Title(name)
+	return strings.ReplaceAll(name, " ", "")
+}
+
+// Generate takes a structured representation of the original textual data in order
+// to write a valid go test file.
+func Generate(ex *Examplar, packageName string, w io.Writer) error {
+	tmpl := template.Must(template.ParseFS(tmplFS, "test_template.go.tmpl"))
+
+	bindings := struct {
+		Package  string
+		TestName string
+		Setup    []Line
+		Teardown []Line
+		Tests    []*Test
+	}{
+		packageName,
+		normalizeTestName(ex.Name),
+		ex.setup,
+		ex.teardown,
+		ex.examples,
+	}
+
+	return tmpl.Execute(w, bindings)
+}
diff --git a/cmd/examplar/main.go b/cmd/examplar/main.go
new file mode 100644
index 00000000..97607022
--- /dev/null
+++ b/cmd/examplar/main.go
@@ -0,0 +1,85 @@
+package main
+
+import (
+	"embed"
+	"flag"
+	"fmt"
+	"log"
+	"os"
+	"path"
+	"path/filepath"
+	"strings"
+)
+
+//go:embed "test_template.go.tmpl"
+var tmplFS embed.FS
+
+var packageName string
+
+func init() {
+	flag.StringVar(&packageName, "package", "", "package name for the generated files")
+	flag.Usage = func() {
+		fmt.Println("Usage: ./examplar -package=[NAME] input1 input2 ...")
+	}
+}
+
+func main() {
+	flag.Parse()
+
+	if packageName == "" {
+		flag.Usage()
+		os.Exit(-1)
+	}
+
+	paths := os.Args[2:]
+	if len(paths) < 1 {
+		flag.Usage()
+		os.Exit(-1)
+	}
+
+	for _, p := range paths {
+		// use globs because when invoked from go generate, there will be no shell
+		// to expand it for us
+		gpaths, err := filepath.Glob(p)
+		if err != nil {
+			log.Fatal(err)
+		}
+
+		if len(gpaths) < 1 {
+			log.Fatalf("%s does not exist", p)
+		}
+
+		for _, gp := range gpaths {
+			err := genFile(gp, packageName)
+			if err != nil {
+				log.Fatal(err)
+			}
+		}
+	}
+}
+
+func genFile(p string, packageName string) error {
+	in, err := os.Open(p)
+	if err != nil {
+		return err
+	}
+	defer in.Close()
+
+	base := path.Base(p)
+	name := strings.TrimSuffix(base, path.Ext(p))
+
+	ex := Parse(in, name, base)
+
+	out, err := os.OpenFile(name+"_test.go", os.O_CREATE|os.O_WRONLY|os.O_TRUNC, 0644)
+	if err != nil {
+		return err
+	}
+	defer out.Close()
+
+	err = Generate(ex, packageName, out)
+	if err != nil {
+		return err
+	}
+
+	return nil
+}
diff --git a/cmd/examplar/scanner.go b/cmd/examplar/scanner.go
new file mode 100644
index 00000000..ef9e595f
--- /dev/null
+++ b/cmd/examplar/scanner.go
@@ -0,0 +1,211 @@
+package main
+
+import (
+	"bufio"
+	"fmt"
+	"regexp"
+	"strings"
+)
+
+const commentPrefix = "---"
+
+type Tag int
+
+const (
+	UNKNOWN Tag = iota
+	SETUP
+	TEARDOWN
+	TEST
+)
+
+type stateFn func(*Scanner) stateFn
+
+type Scanner struct {
+	line string
+	num  int
+	ex   *Examplar
+	err  error
+
+	curTest *Test
+	curStmt *Statement
+}
+
+func (s *Scanner) origLoc() string {
+	return s.ex.origLoc(s.num)
+}
+
+func initialState(s *Scanner) stateFn {
+	if tag, data := parseTag(s.line); tag != UNKNOWN {
+		switch tag {
+		case SETUP:
+			return setupState
+		case TEARDOWN:
+			return teardownState
+		case TEST:
+			s.curTest = s.ex.appendTest(data, s.num)
+			return testState
+		}
+	}
+
+	return initialState
+}
+
+func setupState(s *Scanner) stateFn {
+	if tag, data := parseTag(s.line); tag != UNKNOWN {
+		switch tag {
+		case SETUP:
+			return errorState
+		case TEARDOWN:
+			if s.ex.HasTeardown() {
+				return errorState
+			} else {
+				return teardownState
+			}
+		case TEST:
+			s.curTest = s.ex.appendTest(data, s.num)
+			return testState
+		}
+	}
+
+	s.ex.setup = append(s.ex.setup, Line{s.origLoc(), s.line})
+	return setupState
+}
+
+func teardownState(s *Scanner) stateFn {
+	if tag, data := parseTag(s.line); tag != UNKNOWN {
+		switch tag {
+		case SETUP:
+			if s.ex.HasSetup() {
+				return errorState
+			} else {
+				return setupState
+			}
+		case TEARDOWN:
+			return errorState
+		case TEST:
+			s.curTest = s.ex.appendTest(data, s.num)
+			return testState
+		}
+	}
+
+	s.ex.teardown = append(s.ex.teardown, Line{s.origLoc(), s.line})
+	return teardownState
+}
+
+func testState(s *Scanner) stateFn {
+	if tag, data := parseTag(s.line); tag != UNKNOWN {
+		switch tag {
+		case SETUP:
+			return errorState
+		case TEARDOWN:
+			return errorState
+		case TEST:
+			s.curTest = s.ex.appendTest(data, s.num)
+			return testState
+		}
+	}
+
+	if s.curStmt == nil {
+		stmt := &Statement{}
+		s.curTest.Statements = append(s.curTest.Statements, stmt)
+		s.curStmt = stmt
+	}
+
+	if hasMultilineAssertionTag(s.line) {
+		return multilineAssertionState
+	}
+
+	if assertion := parseSingleAssertion(s.line); len(assertion) > 0 {
+		exp := &s.curStmt.Expectation
+		*exp = []Line{{s.origLoc(), assertion}}
+
+		// current statement is now finished
+		s.curStmt = nil
+		return testState
+	}
+
+	s.curStmt.Code = append(s.curStmt.Code, Line{s.origLoc(), s.line})
+
+	return testState
+}
+
+// TODO check that all lines are sharing the same amount of space, if yes
+// trim them, so everything is aligned perfectly in the resulting test file.
+func multilineAssertionState(s *Scanner) stateFn {
+	re := regexp.MustCompile(`^\s*` + commentPrefix + `(.*)`)
+	matches := re.FindStringSubmatch(s.line)
+
+	if matches == nil {
+		return multilineAssertionState
+	}
+
+	code := strings.TrimRight(matches[1], " \t")
+
+	// final triple backtick, go back to testState
+	if strings.TrimSpace(matches[1]) == "```" {
+		s.curStmt = nil
+		return testState
+	}
+
+	exp := &s.curStmt.Expectation
+	*exp = append(*exp, Line{s.origLoc(), code})
+
+	return multilineAssertionState
+}
+
+func errorState(s *Scanner) stateFn {
+	s.err = fmt.Errorf(s.line)
+	return errorState
+}
+
+func (s *Scanner) Run(io *bufio.Scanner) error {
+	for state := initialState; io.Scan(); {
+		s.line = io.Text()
+		s.line = strings.TrimSpace(s.line)
+		s.num++
+
+		if s.line == "" {
+			continue
+		}
+		state = state(s)
+	}
+
+	return s.err
+}
+
+func parseTag(line string) (Tag, string) {
+	re := regexp.MustCompile(`^\s*` + commentPrefix + `\s*(\w+):\s*(.*)`)
+	matches := re.FindStringSubmatch(line)
+	if matches == nil {
+		return UNKNOWN, ""
+	}
+
+	var tag Tag
+	switch strings.ToLower(matches[1]) {
+	case "setup":
+		tag = SETUP
+	case "teardown":
+		tag = TEARDOWN
+	case "test":
+		tag = TEST
+	default:
+		return UNKNOWN, ""
+	}
+
+	return tag, matches[2]
+}
+
+func parseSingleAssertion(line string) string {
+	re := regexp.MustCompile(`^\s*` + commentPrefix + `\s*` + "`" + `([^` + "`" + `]+)`)
+	matches := re.FindStringSubmatch(line)
+	if matches == nil {
+		return ""
+	}
+	return matches[1]
+}
+
+func hasMultilineAssertionTag(line string) bool {
+	re := regexp.MustCompile(`^\s*` + commentPrefix + `\s*` + "```" + `(\w*)`)
+	matches := re.FindStringSubmatch(line)
+	return matches != nil
+}


Test patch:
diff --git a/cmd/examplar/examplar_test.go b/cmd/examplar/examplar_test.go
new file mode 100644
index 00000000..0eef89aa
--- /dev/null
+++ b/cmd/examplar/examplar_test.go
@@ -0,0 +1,79 @@
+package main
+
+import (
+	"io/ioutil"
+	"os"
+	"strings"
+	"testing"
+
+	"github.com/stretchr/testify/require"
+)
+
+func TestParse(t *testing.T) {
+	f, err := os.Open("extest1.sql")
+	require.NoError(t, err)
+
+	defer f.Close()
+	ex := Parse(f, "extest1", "extest1.sql")
+
+	require.Equal(t, []Line{{"extest1.sql:2", "CREATE TABLE foo (a int);"}}, ex.setup)
+	require.Equal(t, []Line{{"extest1.sql:5", "DROP TABLE foo;"}}, ex.teardown)
+
+	// first test
+	example := ex.examples[0]
+	require.NotNil(t, example)
+	require.Equal(t, "insert something", example.Name)
+	require.Equal(t, 3, len(example.Statements))
+
+	stmt := example.Statements[0]
+	require.Equal(t, "INSERT INTO foo (a) VALUES (1);", stmt.Code[0].Text)
+	require.Equal(t, "SELECT * FROM foo;", stmt.Code[1].Text)
+	require.Equal(t, `[{"a": 1}]`, stmt.Expectation[0].Text)
+
+	stmt = example.Statements[1]
+	require.Equal(t, "SELECT a, b FROM foo;", stmt.Code[0].Text)
+	require.JSONEq(t, `[{"a": 1, "b": null}]`, stmt.expectationText())
+
+	stmt = example.Statements[2]
+	require.Equal(t, "SELECT z FROM foo;", stmt.Code[0].Text)
+	require.Equal(t, `[{"z": null}]`, stmt.Expectation[0].Text)
+
+	// second test
+	example = ex.examples[1]
+	require.NotNil(t, example)
+	require.Equal(t, "something else", example.Name)
+
+	require.Equal(t, 1, len(example.Statements))
+	stmt = example.Statements[0]
+	require.Equal(t, "INSERT INTO foo (c) VALUES (3);", stmt.Code[0].Text)
+	require.Equal(t, "SELECT * FROM foo;", stmt.Code[1].Text)
+	require.Equal(t, `[{"c": 3}]`, stmt.Expectation[0].Text)
+}
+
+func TestTemplate(t *testing.T) {
+	g, err := os.Open("extest1_test.go.gold")
+	require.NoError(t, err)
+	defer g.Close()
+
+	gb, err := ioutil.ReadAll(g)
+	require.NoError(t, err)
+
+	gold := string(gb)
+
+	f, err := os.Open("extest1.sql")
+	require.NoError(t, err)
+	defer f.Close()
+
+	ex := Parse(f, "foo bar", "extest1.sql")
+
+	var b strings.Builder
+
+	err = Generate(ex, "main", &b)
+	require.NoError(t, err)
+
+	// some code to generate the gold version if needed
+	// err = ioutil.WriteFile("trace_test.go", []byte(b.String()), 0644)
+	// require.NoError(t, err)
+
+	require.Equal(t, strings.Split(gold, "\n"), strings.Split(b.String(), "\n"))
+}
diff --git a/cmd/examplar/extest1.sql b/cmd/examplar/extest1.sql
new file mode 100644
index 00000000..59b55b7d
--- /dev/null
+++ b/cmd/examplar/extest1.sql
@@ -0,0 +1,26 @@
+--- setup:
+CREATE TABLE foo (a int);
+
+--- teardown:
+DROP TABLE foo;
+
+--- test: insert something
+INSERT INTO foo (a) VALUES (1);
+SELECT * FROM foo;
+--- `[{"a": 1}]`
+
+SELECT a, b FROM foo;
+--- ```json
+--- [{
+---   "a": 1,
+---   "b": null
+--- }]
+--- ```
+
+SELECT z FROM foo;
+--- `[{"z": null}]`
+
+--- test: something else
+INSERT INTO foo (c) VALUES (3);
+SELECT * FROM foo;
+--- `[{"c": 3}]`
diff --git a/cmd/examplar/extest1_test.go.gold b/cmd/examplar/extest1_test.go.gold
new file mode 100644
index 00000000..64d5d9ab
--- /dev/null
+++ b/cmd/examplar/extest1_test.go.gold
@@ -0,0 +1,142 @@
+package main
+
+import (
+    "encoding/json"
+    "testing"
+
+    "github.com/genjidb/genji"
+    "github.com/genjidb/genji/document"
+    "github.com/genjidb/genji/query"
+    "github.com/stretchr/testify/require"
+)
+
+func TestFooBar(t *testing.T) {
+    db, err := genji.Open(":memory:")
+    require.NoError(t, err)
+    defer db.Close()
+
+    // query results wrapper
+    jsonResult := func(t *testing.T, res *query.Result) []byte {
+        t.Helper()
+        vb := document.NewValueBuffer()
+        err = res.Iterate(func(d document.Document) error {
+            vb = vb.Append(document.NewDocumentValue(d))
+            return nil
+        })
+        require.NoError(t, err)
+        data, err := json.Marshal(vb)
+        require.NoError(t, err)
+        return data
+    }
+
+    // teardown
+    teardown := func() {
+        // extest1.sql:5
+        q := `
+        DROP TABLE foo;
+        `
+        err := db.Exec(q)
+        require.NoError(t, err)
+    }
+
+    // setup
+    setup := func() {
+        // extest1.sql:2
+        q := `
+        CREATE TABLE foo (a int);
+        `
+        err := db.Exec(q)
+        require.NoError(t, err)
+    }
+    
+    // extest1.sql:7
+    t.Run("insert something", func(t *testing.T) {
+        t.Cleanup(teardown)
+        setup()
+
+        var err error
+        var data []byte
+        var expected string
+        var q string
+        var res *query.Result
+        
+        // extest1.sql:8
+        q = `
+        INSERT INTO foo (a) VALUES (1);
+        SELECT * FROM foo;
+        `
+
+        res, err = db.Query(q)
+        require.NoError(t, err)
+        defer res.Close()
+
+        data = jsonResult(t, res)
+        require.NoError(t, err)
+        
+        expected = `[{"a": 1}]` // extest1.sql:10
+        require.JSONEq(t, expected, string(data))
+        // extest1.sql:12
+        q = `
+        SELECT a, b FROM foo;
+        `
+
+        res, err = db.Query(q)
+        require.NoError(t, err)
+        defer res.Close()
+
+        data = jsonResult(t, res)
+        require.NoError(t, err)
+        
+        // extest1.sql:14
+        expected = `
+         [{
+           "a": 1,
+           "b": null
+         }]
+        `
+        require.JSONEq(t, expected, string(data))
+        // extest1.sql:20
+        q = `
+        SELECT z FROM foo;
+        `
+
+        res, err = db.Query(q)
+        require.NoError(t, err)
+        defer res.Close()
+
+        data = jsonResult(t, res)
+        require.NoError(t, err)
+        
+        expected = `[{"z": null}]` // extest1.sql:21
+        require.JSONEq(t, expected, string(data))
+    })
+    
+    // extest1.sql:23
+    t.Run("something else", func(t *testing.T) {
+        t.Cleanup(teardown)
+        setup()
+
+        var err error
+        var data []byte
+        var expected string
+        var q string
+        var res *query.Result
+        
+        // extest1.sql:24
+        q = `
+        INSERT INTO foo (c) VALUES (3);
+        SELECT * FROM foo;
+        `
+
+        res, err = db.Query(q)
+        require.NoError(t, err)
+        defer res.Close()
+
+        data = jsonResult(t, res)
+        require.NoError(t, err)
+        
+        expected = `[{"c": 3}]` // extest1.sql:26
+        require.JSONEq(t, expected, string(data))
+    })
+    
+ }
diff --git a/cmd/examplar/test_template.go.tmpl b/cmd/examplar/test_template.go.tmpl
new file mode 100644
index 00000000..32108713
--- /dev/null
+++ b/cmd/examplar/test_template.go.tmpl
@@ -0,0 +1,99 @@
+package {{ .Package }}
+
+import (
+    "encoding/json"
+    "testing"
+
+    "github.com/genjidb/genji"
+    "github.com/genjidb/genji/document"
+    "github.com/genjidb/genji/query"
+    "github.com/stretchr/testify/require"
+)
+
+func Test{{ .TestName }}(t *testing.T) {
+    db, err := genji.Open(":memory:")
+    require.NoError(t, err)
+    defer db.Close()
+
+    // query results wrapper
+    jsonResult := func(t *testing.T, res *query.Result) []byte {
+        t.Helper()
+        vb := document.NewValueBuffer()
+        err = res.Iterate(func(d document.Document) error {
+            vb = vb.Append(document.NewDocumentValue(d))
+            return nil
+        })
+        require.NoError(t, err)
+        data, err := json.Marshal(vb)
+        require.NoError(t, err)
+        return data
+    }
+
+    // teardown
+    teardown := func() {
+        // {{ (index .Teardown 0).Orig }}
+        q := `
+        {{- range .Teardown }}
+        {{ .Text }}
+        {{- end}}
+        `
+        err := db.Exec(q)
+        require.NoError(t, err)
+    }
+
+    // setup
+    setup := func() {
+        // {{ (index .Setup 0).Orig }}
+        q := `
+        {{- range .Setup }}
+        {{ .Text }}
+        {{- end}}
+        `
+        err := db.Exec(q)
+        require.NoError(t, err)
+    }
+    {{ "" }}
+    {{- range .Tests }}
+    // {{ .Orig }}
+    t.Run("{{ .Name }}", func(t *testing.T) {
+        t.Cleanup(teardown)
+        setup()
+
+        var err error
+        var data []byte
+        var expected string
+        var q string
+        var res *query.Result
+        {{ "" }}
+
+        {{- range .Statements }}
+        // {{ (index .Code 0).Orig }}
+        q = `
+        {{- range .Code }}
+        {{ .Text }}
+        {{- end}}
+        `
+
+        res, err = db.Query(q)
+        require.NoError(t, err)
+        defer res.Close()
+
+        data = jsonResult(t, res)
+        require.NoError(t, err)
+        {{ "" }}
+        {{- if gt (len .Expectation) 1 }}
+        // {{ (index .Expectation 0).Orig }}
+        expected = `
+        {{- range .Expectation }}
+        {{ .Text }}
+        {{- end  }}
+        `
+        {{- else }}
+        expected = `{{ (index .Expectation 0).Text }}` // {{ (index .Expectation 0).Orig }}
+        {{- end }}
+        require.JSONEq(t, expected, string(data))
+        {{- end }}
+    })
+    {{ "" }}
+    {{- end }}
+ }