Repository: genjidb/genji
Pull request #396 (2021-05-08 22:49:18)

Simplify examplar and use it for insert tests


Patch:
diff --git a/.gitignore b/.gitignore
index a4393938..6755988c 100644
--- a/.gitignore
+++ b/.gitignore
@@ -27,4 +27,5 @@ cmd/genji/genji
 # VS Code config
 .vscode/
 
+# gopls log
 gopls.log
diff --git a/Makefile b/Makefile
index 35c27f4c..08bfe9bb 100644
--- a/Makefile
+++ b/Makefile
@@ -10,7 +10,6 @@ $(NAME):
 	cd ./cmd/$@ && go install
 
 gen: $(NAME)
-	cd cmd/examplar && go build && cd ../..
 	go generate ./...
 
 test:
diff --git a/cmd/examplar/.gitignore b/cmd/examplar/.gitignore
deleted file mode 100644
index 64a5e0c9..00000000
--- a/cmd/examplar/.gitignore
+++ /dev/null
@@ -1,3 +0,0 @@
-examplar
-trace_test.go
-integration
diff --git a/cmd/examplar/README.md b/cmd/examplar/README.md
deleted file mode 100644
index 4019a2e9..00000000
--- a/cmd/examplar/README.md
+++ /dev/null
@@ -1,130 +0,0 @@
-# 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
deleted file mode 100644
index 2374b295..00000000
--- a/cmd/examplar/examplar.go
+++ /dev/null
@@ -1,114 +0,0 @@
-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/go.mod b/cmd/examplar/go.mod
deleted file mode 100644
index 8c9a1d9d..00000000
--- a/cmd/examplar/go.mod
+++ /dev/null
@@ -1,5 +0,0 @@
-module github.com/genjidb/genji/cmd/examplar
-
-go 1.16
-
-require github.com/stretchr/testify v1.7.0
diff --git a/cmd/examplar/go.sum b/cmd/examplar/go.sum
deleted file mode 100644
index acb88a48..00000000
--- a/cmd/examplar/go.sum
+++ /dev/null
@@ -1,11 +0,0 @@
-github.com/davecgh/go-spew v1.1.0 h1:ZDRjVQ15GmhC3fiQ8ni8+OwkZQO4DARzQgrnXU1Liz8=
-github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
-github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM=
-github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4=
-github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
-github.com/stretchr/testify v1.7.0 h1:nwc3DEeHmmLAfoZucVR881uASk0Mfjw8xYJ99tb5CcY=
-github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg=
-gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405 h1:yhCVgyC4o1eVCa2tZl7eS0r+SDo693bJlVdllGtEeKM=
-gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0=
-gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c h1:dUUwHk2QECo/6vqA44rthZ8ie2QXMNeKRTHCNY2nXvo=
-gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
diff --git a/cmd/examplar/main.go b/cmd/examplar/main.go
deleted file mode 100644
index 97607022..00000000
--- a/cmd/examplar/main.go
+++ /dev/null
@@ -1,85 +0,0 @@
-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
deleted file mode 100644
index ef9e595f..00000000
--- a/cmd/examplar/scanner.go
+++ /dev/null
@@ -1,211 +0,0 @@
-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
-}
diff --git a/cmd/genji/go.sum b/cmd/genji/go.sum
index a985f664..e6fee4ca 100644
--- a/cmd/genji/go.sum
+++ b/cmd/genji/go.sum
@@ -177,8 +177,9 @@ golang.org/x/sys v0.0.0-20200202164722-d101bd2416d5/go.mod h1:h1NjWce9XRLGQEsW7w
 golang.org/x/sys v0.0.0-20200223170610-d5e6a3e2c0ae/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
 golang.org/x/sys v0.0.0-20200909081042-eff7692f9009/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
 golang.org/x/sys v0.0.0-20200918174421-af09f7315aff/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
-golang.org/x/sys v0.0.0-20200930185726-fdedc70b468f h1:+Nyd8tzPX9R7BWHguqsrbFdRx3WQ/1ib8I44HXV5yTA=
 golang.org/x/sys v0.0.0-20200930185726-fdedc70b468f/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
+golang.org/x/sys v0.0.0-20210119212857-b64e53b001e4 h1:myAQVi0cGEoqQVR5POX+8RR2mrocKqNN1hmeMqhX27k=
+golang.org/x/sys v0.0.0-20210119212857-b64e53b001e4/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
 golang.org/x/text v0.3.0/go.mod h1:NqM8EUOU14njkJ3fqMW+pc6Ldnwhi/IjpwHt7yyuwOQ=
 golang.org/x/text v0.3.3/go.mod h1:5Zoc/QRtKVWzQhOtBMvqHzDpF6irO9z98xDceosuGiQ=
 golang.org/x/tools v0.0.0-20180917221912-90fa682c2a6e/go.mod h1:n7NCudcB/nEzxVGmLbDWY5pfWTLqBcC2KZ6jyYvM4mQ=
@@ -188,6 +189,7 @@ golang.org/x/tools v0.0.0-20190311212946-11955173bddd/go.mod h1:LCzVGOaR6xXOjkQ3
 golang.org/x/tools v0.0.0-20191119224855-298f0cb1881e/go.mod h1:b+2E5dAYhXwXZwtnZ6UAqBI28+e2cm9otk0dWdXHAEo=
 golang.org/x/tools v0.0.0-20191127201027-ecd32218bd7f/go.mod h1:b+2E5dAYhXwXZwtnZ6UAqBI28+e2cm9otk0dWdXHAEo=
 golang.org/x/tools v0.0.0-20201105001634-bc3cf281b174/go.mod h1:emZCQorbCU4vsT4fOWvOPXz4eW1wZW4PmDk9uLelYpA=
+golang.org/x/tools v0.1.0/go.mod h1:xkSsbof2nBLbhDlRMhhhyNLN/zl3eTqcnHD5viDpcZ0=
 golang.org/x/xerrors v0.0.0-20190717185122-a985d3407aa7/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
 golang.org/x/xerrors v0.0.0-20191011141410-1b5146add898/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
 golang.org/x/xerrors v0.0.0-20200804184101-5ec99f83aff1/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
diff --git a/engine/badgerengine/go.sum b/engine/badgerengine/go.sum
index 9845d53e..00481494 100644
--- a/engine/badgerengine/go.sum
+++ b/engine/badgerengine/go.sum
@@ -130,8 +130,9 @@ golang.org/x/sys v0.0.0-20190422165155-953cdadca894/go.mod h1:h1NjWce9XRLGQEsW7w
 golang.org/x/sys v0.0.0-20190502145724-3ef323f4f1fd/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
 golang.org/x/sys v0.0.0-20190626221950-04f50cda93cb/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
 golang.org/x/sys v0.0.0-20200202164722-d101bd2416d5/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
-golang.org/x/sys v0.0.0-20200930185726-fdedc70b468f h1:+Nyd8tzPX9R7BWHguqsrbFdRx3WQ/1ib8I44HXV5yTA=
 golang.org/x/sys v0.0.0-20200930185726-fdedc70b468f/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
+golang.org/x/sys v0.0.0-20210119212857-b64e53b001e4 h1:myAQVi0cGEoqQVR5POX+8RR2mrocKqNN1hmeMqhX27k=
+golang.org/x/sys v0.0.0-20210119212857-b64e53b001e4/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
 golang.org/x/text v0.3.0/go.mod h1:NqM8EUOU14njkJ3fqMW+pc6Ldnwhi/IjpwHt7yyuwOQ=
 golang.org/x/text v0.3.3/go.mod h1:5Zoc/QRtKVWzQhOtBMvqHzDpF6irO9z98xDceosuGiQ=
 golang.org/x/tools v0.0.0-20180917221912-90fa682c2a6e/go.mod h1:n7NCudcB/nEzxVGmLbDWY5pfWTLqBcC2KZ6jyYvM4mQ=
@@ -141,6 +142,7 @@ golang.org/x/tools v0.0.0-20190311212946-11955173bddd/go.mod h1:LCzVGOaR6xXOjkQ3
 golang.org/x/tools v0.0.0-20191119224855-298f0cb1881e/go.mod h1:b+2E5dAYhXwXZwtnZ6UAqBI28+e2cm9otk0dWdXHAEo=
 golang.org/x/tools v0.0.0-20191127201027-ecd32218bd7f/go.mod h1:b+2E5dAYhXwXZwtnZ6UAqBI28+e2cm9otk0dWdXHAEo=
 golang.org/x/tools v0.0.0-20201105001634-bc3cf281b174/go.mod h1:emZCQorbCU4vsT4fOWvOPXz4eW1wZW4PmDk9uLelYpA=
+golang.org/x/tools v0.1.0/go.mod h1:xkSsbof2nBLbhDlRMhhhyNLN/zl3eTqcnHD5viDpcZ0=
 golang.org/x/xerrors v0.0.0-20190717185122-a985d3407aa7/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
 golang.org/x/xerrors v0.0.0-20191011141410-1b5146add898/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
 golang.org/x/xerrors v0.0.0-20200804184101-5ec99f83aff1/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
diff --git a/fuzz/go.mod b/fuzz/go.mod
index dd9b86ac..6778ccfa 100644
--- a/fuzz/go.mod
+++ b/fuzz/go.mod
@@ -3,11 +3,12 @@ module github.com/genjidb/genji/fuzz
 go 1.15
 
 require (
-	github.com/dvyukov/go-fuzz v0.0.0-20201003075337-90825f39c90b
+	github.com/dvyukov/go-fuzz v0.0.0-20210429054444-fca39067bc72
 	github.com/elazarl/go-bindata-assetfs v1.0.1 // indirect
-	github.com/genjidb/genji v0.11.0
+	github.com/genjidb/genji v0.13.0
 	github.com/stephens2424/writerset v1.0.2 // indirect
-	golang.org/x/tools v0.0.0-20201013053347-2db1cd791039 // indirect
+	golang.org/x/mod v0.4.2 // indirect
+	golang.org/x/sys v0.0.0-20210507161434-a76c4d0a0096 // indirect
 )
 
-replace github.com/genjidb/genji v0.11.0 => ../
+replace github.com/genjidb/genji v0.13.0 => ../
diff --git a/fuzz/go.sum b/fuzz/go.sum
index 852ae265..d6ad9da1 100644
--- a/fuzz/go.sum
+++ b/fuzz/go.sum
@@ -5,6 +5,8 @@ github.com/davecgh/go-spew v1.1.0 h1:ZDRjVQ15GmhC3fiQ8ni8+OwkZQO4DARzQgrnXU1Liz8
 github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
 github.com/dvyukov/go-fuzz v0.0.0-20201003075337-90825f39c90b h1:CXfDl9Y3NKuhOSxF9kXhiLmuYCdufQDrLY2fO1BzqBU=
 github.com/dvyukov/go-fuzz v0.0.0-20201003075337-90825f39c90b/go.mod h1:11Gm+ccJnvAhCNLlf5+cS9KjtbaD5I5zaZpFMsTHWTw=
+github.com/dvyukov/go-fuzz v0.0.0-20210429054444-fca39067bc72 h1:XiR1YwcWcRFzxjAhWK29HQL4nocj0QWJjpeRi/YASV0=
+github.com/dvyukov/go-fuzz v0.0.0-20210429054444-fca39067bc72/go.mod h1:11Gm+ccJnvAhCNLlf5+cS9KjtbaD5I5zaZpFMsTHWTw=
 github.com/elazarl/go-bindata-assetfs v1.0.1 h1:m0kkaHRKEu7tUIUFVwhGGGYClXvyl4RE03qmvRTNfbw=
 github.com/elazarl/go-bindata-assetfs v1.0.1/go.mod h1:v+YaWX3bdea5J/mo8dSETolEo7R71Vk1u8bnjau5yw4=
 github.com/google/btree v1.0.0 h1:0udJVsspx3VBr5FwtLhQQtuAsVc79tTq0ocGIPAU6qo=
@@ -16,7 +18,6 @@ github.com/stephens2424/writerset v1.0.2 h1:znRLgU6g8RS5euYRcy004XeE4W+Tu44kALzy
 github.com/stephens2424/writerset v1.0.2/go.mod h1:aS2JhsMn6eA7e82oNmW4rfsgAOp9COBTTl8mzkwADnc=
 github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
 github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI=
-github.com/stretchr/testify v1.6.1 h1:hDPOHmpOpP40lSULcqw7IrRb/u7w6RpDC9399XyoNd0=
 github.com/stretchr/testify v1.6.1/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg=
 github.com/stretchr/testify v1.7.0 h1:nwc3DEeHmmLAfoZucVR881uASk0Mfjw8xYJ99tb5CcY=
 github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg=
@@ -32,22 +33,27 @@ golang.org/x/crypto v0.0.0-20191011191535-87dc89f01550/go.mod h1:yigFU9vqHzYiE8U
 golang.org/x/crypto v0.0.0-20200622213623-75b288015ac9/go.mod h1:LzIPMQfyMNhhGPhUkYOs5KpL4U8rLKemX1yGLhDgUto=
 golang.org/x/mod v0.3.0 h1:RM4zey1++hCTbCVQfnWeKs9/IEsaBLA8vTkd0WVtmH4=
 golang.org/x/mod v0.3.0/go.mod h1:s0Qsj1ACt9ePp/hMypM3fl4fZqREWJwdYDEqhRiZZUA=
+golang.org/x/mod v0.4.2 h1:Gz96sIWK3OalVv/I/qNygP42zyoKp3xptRVCWRFEBvo=
+golang.org/x/mod v0.4.2/go.mod h1:s0Qsj1ACt9ePp/hMypM3fl4fZqREWJwdYDEqhRiZZUA=
 golang.org/x/net v0.0.0-20190404232315-eb5bcb51f2a3/go.mod h1:t9HGtf8HONx5eT2rtn7q6eTqICYqUVnKs3thJo3Qplg=
 golang.org/x/net v0.0.0-20190620200207-3b0461eec859/go.mod h1:z5CRVTTTmAJ677TzLLGU+0bjPO0LkuOLi4/5GtJWs/s=
-golang.org/x/net v0.0.0-20200822124328-c89045814202 h1:VvcQYSHwXgi7W+TpUR6A9g6Up98WAHf3f/ulnJ62IyA=
-golang.org/x/net v0.0.0-20200822124328-c89045814202/go.mod h1:/O7V0waA8r7cgGh81Ro3o1hOxt32SMVPicZroKQ2sZA=
+golang.org/x/net v0.0.0-20201021035429-f5854403a974/go.mod h1:sp8m0HH+o8qH0wwXwYZr8TS3Oi6o0r6Gce1SSxlDquU=
 golang.org/x/sync v0.0.0-20190423024810-112230192c58/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
-golang.org/x/sync v0.0.0-20200625203802-6e8e738ad208/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
+golang.org/x/sync v0.0.0-20201020160332-67f06af15bc9/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
 golang.org/x/sys v0.0.0-20190215142949-d0b11bdaac8a/go.mod h1:STP8DvDyc/dI5b8T5hshtkjS+E42TnysNCUPdjciGhY=
 golang.org/x/sys v0.0.0-20190412213103-97732733099d/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
-golang.org/x/sys v0.0.0-20200202164722-d101bd2416d5 h1:LfCXLvNmTYH9kEmVgqbnsWfruoXZIrh4YBgqVHtDvw0=
 golang.org/x/sys v0.0.0-20200202164722-d101bd2416d5/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
-golang.org/x/sys v0.0.0-20200323222414-85ca7c5b95cd h1:xhmwyvizuTgC2qz7ZlMluP20uW+C3Rm0FD/WLDX8884=
-golang.org/x/sys v0.0.0-20200323222414-85ca7c5b95cd/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
+golang.org/x/sys v0.0.0-20200930185726-fdedc70b468f/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
+golang.org/x/sys v0.0.0-20210119212857-b64e53b001e4 h1:myAQVi0cGEoqQVR5POX+8RR2mrocKqNN1hmeMqhX27k=
+golang.org/x/sys v0.0.0-20210119212857-b64e53b001e4/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
+golang.org/x/sys v0.0.0-20210507161434-a76c4d0a0096 h1:5PbJGn5Sp3GEUjJ61aYbUP6RIo3Z3r2E4Tv9y2z8UHo=
+golang.org/x/sys v0.0.0-20210507161434-a76c4d0a0096/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
 golang.org/x/text v0.3.0/go.mod h1:NqM8EUOU14njkJ3fqMW+pc6Ldnwhi/IjpwHt7yyuwOQ=
+golang.org/x/text v0.3.3/go.mod h1:5Zoc/QRtKVWzQhOtBMvqHzDpF6irO9z98xDceosuGiQ=
+golang.org/x/tools v0.0.0-20180917221912-90fa682c2a6e/go.mod h1:n7NCudcB/nEzxVGmLbDWY5pfWTLqBcC2KZ6jyYvM4mQ=
 golang.org/x/tools v0.0.0-20191119224855-298f0cb1881e/go.mod h1:b+2E5dAYhXwXZwtnZ6UAqBI28+e2cm9otk0dWdXHAEo=
-golang.org/x/tools v0.0.0-20201013053347-2db1cd791039 h1:kLBxO4OPBgPwjg8Vvu+/0DCHIfDwYIGNFcD66NU9kpo=
-golang.org/x/tools v0.0.0-20201013053347-2db1cd791039/go.mod h1:z6u4i615ZeAfBE4XtMziQW1fSVJXACjjbWkB/mvPzlU=
+golang.org/x/tools v0.1.0 h1:po9/4sTYwZU9lPhi1tOrb4hCv3qrhiQ77LZfGa2OjwY=
+golang.org/x/tools v0.1.0/go.mod h1:xkSsbof2nBLbhDlRMhhhyNLN/zl3eTqcnHD5viDpcZ0=
 golang.org/x/xerrors v0.0.0-20190717185122-a985d3407aa7/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
 golang.org/x/xerrors v0.0.0-20191011141410-1b5146add898/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
 golang.org/x/xerrors v0.0.0-20200804184101-5ec99f83aff1 h1:go1bK/D/BFZV2I8cIQd1NKEZ+0owSTG1fDTci4IqFcE=
diff --git a/go.mod b/go.mod
index 8fa86605..63bd5c08 100644
--- a/go.mod
+++ b/go.mod
@@ -8,4 +8,5 @@ require (
 	github.com/stretchr/testify v1.7.0
 	github.com/vmihailenco/msgpack/v5 v5.1.4
 	go.etcd.io/bbolt v1.3.5
+	golang.org/x/tools v0.1.0
 )
diff --git a/go.sum b/go.sum
index 3f8eb231..07271749 100644
--- a/go.sum
+++ b/go.sum
@@ -14,10 +14,35 @@ github.com/vmihailenco/msgpack/v5 v5.1.4 h1:6K44/cU6dMNGkVTGGuu7ef2NdSRFMhAFGGLf
 github.com/vmihailenco/msgpack/v5 v5.1.4/go.mod h1:C5gboKD0TJPqWDTVTtrQNfRbiBwHZGo8UTqP/9/XvLI=
 github.com/vmihailenco/tagparser v0.1.2 h1:gnjoVuB/kljJ5wICEEOpx98oXMWPLj22G67Vbd1qPqc=
 github.com/vmihailenco/tagparser v0.1.2/go.mod h1:OeAg3pn3UbLjkWt+rN9oFYB6u/cQgqMEUPoW2WPyhdI=
+github.com/yuin/goldmark v1.2.1/go.mod h1:3hX8gzYuyVAZsxl0MRgGTJEmQBFcNTphYh9decYSb74=
 go.etcd.io/bbolt v1.3.5 h1:XAzx9gjCb0Rxj7EoqcClPD1d5ZBxZJk0jbuoPHenBt0=
 go.etcd.io/bbolt v1.3.5/go.mod h1:G5EMThwa9y8QZGBClrRx5EY+Yw9kAhnjy3bSjsnlVTQ=
-golang.org/x/sys v0.0.0-20200202164722-d101bd2416d5 h1:LfCXLvNmTYH9kEmVgqbnsWfruoXZIrh4YBgqVHtDvw0=
+golang.org/x/crypto v0.0.0-20190308221718-c2843e01d9a2/go.mod h1:djNgcEr1/C05ACkg1iLfiJU5Ep61QUkGW8qpdssI0+w=
+golang.org/x/crypto v0.0.0-20191011191535-87dc89f01550/go.mod h1:yigFU9vqHzYiE8UmvKecakEJjdnWj3jj499lnFckfCI=
+golang.org/x/crypto v0.0.0-20200622213623-75b288015ac9/go.mod h1:LzIPMQfyMNhhGPhUkYOs5KpL4U8rLKemX1yGLhDgUto=
+golang.org/x/mod v0.3.0 h1:RM4zey1++hCTbCVQfnWeKs9/IEsaBLA8vTkd0WVtmH4=
+golang.org/x/mod v0.3.0/go.mod h1:s0Qsj1ACt9ePp/hMypM3fl4fZqREWJwdYDEqhRiZZUA=
+golang.org/x/net v0.0.0-20190404232315-eb5bcb51f2a3/go.mod h1:t9HGtf8HONx5eT2rtn7q6eTqICYqUVnKs3thJo3Qplg=
+golang.org/x/net v0.0.0-20190620200207-3b0461eec859/go.mod h1:z5CRVTTTmAJ677TzLLGU+0bjPO0LkuOLi4/5GtJWs/s=
+golang.org/x/net v0.0.0-20201021035429-f5854403a974/go.mod h1:sp8m0HH+o8qH0wwXwYZr8TS3Oi6o0r6Gce1SSxlDquU=
+golang.org/x/sync v0.0.0-20190423024810-112230192c58/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
+golang.org/x/sync v0.0.0-20201020160332-67f06af15bc9/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
+golang.org/x/sys v0.0.0-20190215142949-d0b11bdaac8a/go.mod h1:STP8DvDyc/dI5b8T5hshtkjS+E42TnysNCUPdjciGhY=
+golang.org/x/sys v0.0.0-20190412213103-97732733099d/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
 golang.org/x/sys v0.0.0-20200202164722-d101bd2416d5/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
+golang.org/x/sys v0.0.0-20200930185726-fdedc70b468f/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
+golang.org/x/sys v0.0.0-20210119212857-b64e53b001e4 h1:myAQVi0cGEoqQVR5POX+8RR2mrocKqNN1hmeMqhX27k=
+golang.org/x/sys v0.0.0-20210119212857-b64e53b001e4/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
+golang.org/x/text v0.3.0/go.mod h1:NqM8EUOU14njkJ3fqMW+pc6Ldnwhi/IjpwHt7yyuwOQ=
+golang.org/x/text v0.3.3/go.mod h1:5Zoc/QRtKVWzQhOtBMvqHzDpF6irO9z98xDceosuGiQ=
+golang.org/x/tools v0.0.0-20180917221912-90fa682c2a6e/go.mod h1:n7NCudcB/nEzxVGmLbDWY5pfWTLqBcC2KZ6jyYvM4mQ=
+golang.org/x/tools v0.0.0-20191119224855-298f0cb1881e/go.mod h1:b+2E5dAYhXwXZwtnZ6UAqBI28+e2cm9otk0dWdXHAEo=
+golang.org/x/tools v0.1.0 h1:po9/4sTYwZU9lPhi1tOrb4hCv3qrhiQ77LZfGa2OjwY=
+golang.org/x/tools v0.1.0/go.mod h1:xkSsbof2nBLbhDlRMhhhyNLN/zl3eTqcnHD5viDpcZ0=
+golang.org/x/xerrors v0.0.0-20190717185122-a985d3407aa7/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
+golang.org/x/xerrors v0.0.0-20191011141410-1b5146add898/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
+golang.org/x/xerrors v0.0.0-20200804184101-5ec99f83aff1 h1:go1bK/D/BFZV2I8cIQd1NKEZ+0owSTG1fDTci4IqFcE=
+golang.org/x/xerrors v0.0.0-20200804184101-5ec99f83aff1/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
 gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405 h1:yhCVgyC4o1eVCa2tZl7eS0r+SDo693bJlVdllGtEeKM=
 gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0=
 gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c h1:dUUwHk2QECo/6vqA44rthZ8ie2QXMNeKRTHCNY2nXvo=
diff --git a/sql/parser/expr.go b/sql/parser/expr.go
index 06d5294c..cee96853 100644
--- a/sql/parser/expr.go
+++ b/sql/parser/expr.go
@@ -229,7 +229,7 @@ func (p *Parser) parseUnaryExpr() (expr.Expr, error) {
 		return expr.LiteralValue(document.NewNullValue()), nil
 	case scanner.LBRACKET:
 		p.Unscan()
-		e, err := p.parseDocument()
+		e, err := p.ParseDocument()
 		return e, err
 	case scanner.LSBRACKET:
 		p.Unscan()
@@ -372,8 +372,8 @@ func (p *Parser) parseType() (document.ValueType, error) {
 	return 0, newParseError(scanner.Tokstr(tok, lit), []string{"type"}, pos)
 }
 
-// parseDocument parses a document
-func (p *Parser) parseDocument() (*expr.KVPairs, error) {
+// ParseDocument parses a document
+func (p *Parser) ParseDocument() (*expr.KVPairs, error) {
 	// Parse { token.
 	if tok, pos, lit := p.ScanIgnoreWhitespace(); tok != scanner.LBRACKET {
 		return nil, newParseError(scanner.Tokstr(tok, lit), []string{"{"}, pos)
diff --git a/sql/parser/insert.go b/sql/parser/insert.go
index e6bb182b..b264f840 100644
--- a/sql/parser/insert.go
+++ b/sql/parser/insert.go
@@ -186,7 +186,7 @@ func (p *Parser) parseParamOrDocument() (expr.Expr, error) {
 	p.Unscan()
 
 	// Expect a document
-	return p.parseDocument()
+	return p.ParseDocument()
 }
 
 // insertConfig holds INSERT configuration.


Test patch:
diff --git a/.github/workflows/test-fuzz.yaml b/.github/workflows/test-fuzz.yaml
index bac331ac..125e1a06 100644
--- a/.github/workflows/test-fuzz.yaml
+++ b/.github/workflows/test-fuzz.yaml
@@ -11,8 +11,8 @@ jobs:
     env:
       GOOS: ${{ matrix.goos }}
       GOARCH: ${{ matrix.goarch }}
-      GOFLAGS: '-trimpath -mod=readonly'
-      GO111MODULE: 'on'
+      GOFLAGS: "-trimpath" # -mod=readonly
+      GO111MODULE: "on"
     strategy:
       fail-fast: false
       matrix:
diff --git a/cmd/examplar/examplar_test.go b/cmd/examplar/examplar_test.go
deleted file mode 100644
index 0eef89aa..00000000
--- a/cmd/examplar/examplar_test.go
+++ /dev/null
@@ -1,79 +0,0 @@
-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
deleted file mode 100644
index 59b55b7d..00000000
--- a/cmd/examplar/extest1.sql
+++ /dev/null
@@ -1,26 +0,0 @@
---- 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
deleted file mode 100644
index 63049e19..00000000
--- a/cmd/examplar/extest1_test.go.gold
+++ /dev/null
@@ -1,137 +0,0 @@
-package main
-
-import (
-    "bytes"
-    "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()
-        var buf bytes.Buffer
-        err := document.IteratorToJSONArray(&buf, res)
-        require.NoError(t, err)
-        return buf.Bytes()
-    }
-
-    // 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
deleted file mode 100644
index 79cdcaeb..00000000
--- a/cmd/examplar/test_template.go.tmpl
+++ /dev/null
@@ -1,94 +0,0 @@
-package {{ .Package }}
-
-import (
-    "bytes"
-    "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()
-        var buf bytes.Buffer
-        err := document.IteratorToJSONArray(&buf, res)
-        require.NoError(t, err)
-        return buf.Bytes()
-    }
-
-    // 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 }}
- }
diff --git a/dev/gensqltest/README.md b/dev/gensqltest/README.md
new file mode 100644
index 00000000..03b44172
--- /dev/null
+++ b/dev/gensqltest/README.md
@@ -0,0 +1,93 @@
+# GenSQLTest
+
+A tool to generate tests from on an annotated SQL file, with queries and their expected results.
+
+A file named `selecting.sql`
+
+```sql
+-- setup:
+CREATE TABLE foo (a int);
+CREATE TABLE bar;
+
+-- test: insert something
+INSERT INTO foo (a) VALUES (1);
+
+SELECT * FROM foo;
+/* result:
+{
+  "a": 1
+}
+*/
+```
+
+Gets transformed into (abbreviated):
+
+```go
+func TestSomething(t *testing.T) {
+	setup := func(t *testing.T, db *genji.DB) {
+		q := `
+CREATE TABLE foo (a int);
+CREATE TABLE bar;
+`
+		err := db.Exec(q)
+		require.NoError(t, err)
+	}
+
+	// --------------------------------------------------------------------------
+	t.Run("insert something", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO foo (a) VALUES (1);`, func(t *testing.T) {
+			q := `
+INSERT INTO foo (a) VALUES (1);
+SELECT * FROM foo;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "a": 1
+}
+`
+			testutil.RequireStreamEq(t, raw, res)
+        })
+	})
+}
+```
+
+## Usage
+
+To populate tests that would be in an `query_test` package, create a go file named `gentests.go` in the `query` package with the following code:
+
+```go
+package query
+
+//go:generate go run ../dev/gensqltest -package=query_test ./*_test.sql
+```
+
+Every test is ran in isolation, with a newly created memory database, with the setup block run on it.
+If multiple results or errors are expected within a test, they share the same database.
+
+### Annotations
+
+- `-- setup:`
+
+  - all lines up to the next annotation are to be considered as one single statement for the setup block.
+
+- `-- 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.
+
+- `/* result: `
+
+  - all lines until the end of that comment are considered to be the expected output
+
+- `- error: [REGEXP]`
+  - expect the above statements to raise an error, that matches `[REPEXP]`
+  - expecting any error can be achieved by giving a blank `[REGEXP]` as in `-error:`
diff --git a/dev/gensqltest/gensqltest_test.go b/dev/gensqltest/gensqltest_test.go
new file mode 100644
index 00000000..dc0569c4
--- /dev/null
+++ b/dev/gensqltest/gensqltest_test.go
@@ -0,0 +1,132 @@
+package main
+
+import (
+	"bytes"
+	// "io/fs"
+	"os"
+	"strings"
+	"testing"
+
+	"github.com/stretchr/testify/require"
+)
+
+var program = `
+-- setup:
+CREATE TABLE foo (a int);
+CREATE TABLE bar;
+
+-- test: insert something
+INSERT INTO foo (a) VALUES (1);
+
+SELECT * FROM foo;
+/* result:
+{
+  "a": 1
+}
+*/
+
+SELECT a, b FROM foo;
+/* result:
+{
+  "a": 1,
+  "b": null
+}
+*/
+
+SELECT z FROM foo;
+/* result:
+{"z": null}
+*/
+
+-- test: something else
+INSERT INTO foo (c) VALUES (3);
+SELECT * FROM foo;
+/* result:
+{"c": 3}
+*/
+
+SELECTARRRR z FROM foo;
+-- error: SELECTARRRR
+
+INVALID;
+-- error:
+`
+
+func TestParse(t *testing.T) {
+	r := strings.NewReader(program)
+	ts := parse(r, "foobar")
+
+	// setup
+	require.Equal(t, "CREATE TABLE foo (a int);", ts.Setup[0])
+	require.Equal(t, "CREATE TABLE bar;", ts.Setup[1])
+
+	// test "insert something"
+	require.Equal(t, "insert something", ts.Tests[0].Name)
+
+	// first block
+	stmt := ts.Tests[0].Statements[0]
+	require.Equal(t, "INSERT INTO foo (a) VALUES (1);", stmt.Expr[0])
+	require.Equal(t, "SELECT * FROM foo;", stmt.Expr[1])
+	want := []string{
+		`{`,
+		`  "a": 1`,
+		`}`,
+	}
+	require.Equal(t, want, stmt.Result)
+
+	// second block
+	stmt = ts.Tests[0].Statements[1]
+	require.Equal(t, "SELECT a, b FROM foo;", stmt.Expr[0])
+	want = []string{
+		`{`,
+		`  "a": 1,`,
+		`  "b": null`,
+		`}`,
+	}
+	require.Equal(t, want, stmt.Result)
+
+	// third block
+	stmt = ts.Tests[0].Statements[2]
+	require.Equal(t, "SELECT z FROM foo;", stmt.Expr[0])
+	want = []string{`{"z": null}`}
+	require.Equal(t, want, stmt.Result)
+
+	// test "something else"
+	require.Equal(t, "something else", ts.Tests[1].Name)
+
+	// first block
+	stmt = ts.Tests[1].Statements[0]
+	require.Equal(t, "INSERT INTO foo (c) VALUES (3);", stmt.Expr[0])
+	require.Equal(t, "SELECT * FROM foo;", stmt.Expr[1])
+	want = []string{`{"c": 3}`}
+	require.Equal(t, want, stmt.Result)
+
+	// second block
+	stmt = ts.Tests[1].Statements[1]
+	require.Equal(t, "SELECTARRRR z FROM foo;", stmt.Expr[0])
+	wantErr := "SELECTARRRR"
+	require.Equal(t, wantErr, stmt.ErrorMatch)
+	require.True(t, stmt.Fails)
+
+	// third block
+	stmt = ts.Tests[1].Statements[2]
+	require.Equal(t, "INVALID;", stmt.Expr[0])
+	require.True(t, stmt.Fails)
+}
+
+func TestGegenerate(t *testing.T) {
+	r := strings.NewReader(program)
+	ts := parse(r, "gold")
+
+	var out bytes.Buffer
+	err := generate(ts, "main", &out)
+	require.NoError(t, err)
+
+	// uncomment below to regenerate the gold file
+	// os.WriteFile("test.go.gold", out.Bytes(), fs.FileMode(os.O_WRONLY))
+
+	b, err := os.ReadFile("test.go.gold")
+	require.NoError(t, err)
+
+	require.Equal(t, string(b), out.String())
+}
diff --git a/dev/gensqltest/main.go b/dev/gensqltest/main.go
new file mode 100644
index 00000000..cbe92521
--- /dev/null
+++ b/dev/gensqltest/main.go
@@ -0,0 +1,240 @@
+package main
+
+import (
+	"bufio"
+	"bytes"
+	"embed"
+	"flag"
+	"fmt"
+	"io"
+	"log"
+	"os"
+	"path"
+	"path/filepath"
+	"strings"
+	"text/template"
+
+	"golang.org/x/tools/imports"
+)
+
+//go:embed "test_template.go.tmpl"
+var tmplFS embed.FS
+
+type statement struct {
+	Expr       []string
+	Result     []string
+	ErrorMatch string
+	Fails      bool
+}
+
+type test struct {
+	Name       string
+	Statements []*statement
+}
+
+type testSuite struct {
+	Filename string
+	Setup    []string
+	Tests    []*test
+}
+
+func parse(r io.Reader, filename string) *testSuite {
+	s := bufio.NewScanner(r)
+	ts := testSuite{
+		Filename: filename,
+	}
+
+	var curTest *test
+	var curStmt *statement
+
+	var readingResult bool
+	var readingSetup bool
+
+	for s.Scan() {
+		line := s.Text()
+
+		// keep result indentation intact
+		if !readingResult {
+			line = strings.TrimSpace(line)
+		}
+
+		switch {
+		case line == "":
+			// ignore blank lines
+		case strings.HasPrefix(line, "-- setup:"):
+			readingSetup = true
+
+		case strings.HasPrefix(line, "-- test:"):
+			readingSetup = false
+
+			// create a new test
+			name := strings.TrimPrefix(line, "-- test: ")
+			curTest = &test{
+				Name: name,
+			}
+			ts.Tests = append(ts.Tests, curTest)
+			curStmt = nil
+
+		case strings.HasPrefix(line, "/* result:"):
+			readingResult = true
+
+		case strings.HasPrefix(line, "-- error:"):
+			error := strings.TrimPrefix(line, "-- error:")
+			error = strings.TrimSpace(error)
+			if error == "" {
+				// handle the case where error was used but without a message
+				curStmt.Fails = true
+			} else {
+				curStmt.ErrorMatch = error
+				curStmt.Fails = true
+			}
+			curStmt = nil
+
+		case strings.HasPrefix(line, "--"): // ignore normal comments
+
+		default:
+			if readingSetup {
+				ts.Setup = append(ts.Setup, line)
+			} else if readingResult && strings.TrimSpace(line) == "*/" {
+				readingResult = false
+				curStmt = nil
+			} else if readingResult {
+				curStmt.Result = append(curStmt.Result, line)
+			} else {
+				if curStmt == nil {
+					curStmt = new(statement)
+					curTest.Statements = append(curTest.Statements, curStmt)
+				}
+
+				curStmt.Expr = append(curStmt.Expr, line)
+			}
+		}
+	}
+
+	return &ts
+}
+
+// some sql query use backticks which can mess up the templating
+func escapeBackticks(text string) string {
+	return strings.ReplaceAll(text, "`", "`+\"`\"+`")
+}
+
+func camelize(str string) string {
+	str = strings.ReplaceAll(str, "_", " ")
+	str = strings.Title(str)
+	str = strings.ReplaceAll(str, " ", "")
+
+	return str
+}
+
+func generate(ts *testSuite, packageName string, w io.Writer) error {
+	name := normalize(ts.Filename)
+
+	funcMap := template.FuncMap{
+		"escapeBackticks": escapeBackticks,
+	}
+
+	tmpl, err := template.New("test_template.go.tmpl").Funcs(funcMap).ParseFS(tmplFS, "test_template.go.tmpl")
+	if err != nil {
+		return err
+	}
+
+	bindings := struct {
+		Package  string
+		TestName string
+		Suite    *testSuite
+	}{
+		packageName,
+		camelize(name),
+		ts,
+	}
+
+	var buf bytes.Buffer
+	err = tmpl.Execute(&buf, bindings)
+	if err != nil {
+		return err
+	}
+
+	output, err := imports.Process("", buf.Bytes(), &imports.Options{
+		TabWidth:  8,
+		TabIndent: true,
+		Comments:  true,
+	})
+
+	if err != nil {
+		return err
+	}
+
+	_, err = w.Write(output)
+	return err
+}
+
+// normalize transforms "/foo/bar/my_foo_test.sql" into "my_foo"
+func normalize(filepath string) string {
+	base := path.Base(filepath)
+	name := strings.TrimSuffix(base, path.Ext(filepath))
+	return strings.TrimSuffix(name, "_test")
+}
+
+var packageName string
+
+func init() {
+	flag.StringVar(&packageName, "package", "", "package name for the generated files")
+	flag.Usage = func() {
+		fmt.Println("Usage: ./gensqltest -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 {
+			f, err := os.Open(gp)
+			if err != nil {
+				panic(err)
+			}
+
+			ts := parse(f, gp)
+
+			name := normalize(gp)
+
+			out, err := os.OpenFile(name+"_test.go", os.O_CREATE|os.O_WRONLY|os.O_TRUNC, 0644)
+			if err != nil {
+				panic(err)
+			}
+
+			err = generate(ts, packageName, out)
+			if err != nil {
+				panic(err)
+			}
+
+			err = out.Close()
+			if err != nil {
+				panic(err)
+			}
+		}
+	}
+}
diff --git a/dev/gensqltest/test.go.gold b/dev/gensqltest/test.go.gold
new file mode 100644
index 00000000..bc2966bf
--- /dev/null
+++ b/dev/gensqltest/test.go.gold
@@ -0,0 +1,124 @@
+/*
+* CODE GENERATED AUTOMATICALLY WITH github.com/genjidb/genji/dev/gensqltest
+* THIS FILE SHOULD NOT BE EDITED BY HAND
+ */
+package main
+
+import (
+	"regexp"
+	"testing"
+
+	"github.com/genjidb/genji"
+	"github.com/genjidb/genji/testutil/testgen"
+	"github.com/stretchr/testify/require"
+)
+
+func TestGold(t *testing.T) {
+	setup := func(t *testing.T, db *genji.DB) {
+		t.Helper()
+
+		q := `
+CREATE TABLE foo (a int);
+CREATE TABLE bar;
+`
+		err := db.Exec(q)
+		require.NoError(t, err)
+	}
+
+	// --------------------------------------------------------------------------
+	t.Run("insert something", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO foo (a) VALUES (1);`, func(t *testing.T) {
+			q := `
+INSERT INTO foo (a) VALUES (1);
+SELECT * FROM foo;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "a": 1
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+		t.Run(`SELECT a, b FROM foo;`, func(t *testing.T) {
+			q := `
+SELECT a, b FROM foo;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "a": 1,
+  "b": null
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+		t.Run(`SELECT z FROM foo;`, func(t *testing.T) {
+			q := `
+SELECT z FROM foo;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{"z": null}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("something else", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO foo (c) VALUES (3);`, func(t *testing.T) {
+			q := `
+INSERT INTO foo (c) VALUES (3);
+SELECT * FROM foo;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{"c": 3}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+		t.Run(`SELECTARRRR z FROM foo;`, func(t *testing.T) {
+			q := `
+SELECTARRRR z FROM foo;
+`
+			err := db.Exec(q)
+			require.NotNil(t, err, "expected error, got nil")
+			require.Regexp(t, regexp.MustCompile("SELECTARRRR"), err.Error())
+		})
+
+		t.Run(`INVALID;`, func(t *testing.T) {
+			q := `
+INVALID;
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+}
diff --git a/dev/gensqltest/test_template.go.tmpl b/dev/gensqltest/test_template.go.tmpl
new file mode 100644
index 00000000..da2c9b47
--- /dev/null
+++ b/dev/gensqltest/test_template.go.tmpl
@@ -0,0 +1,73 @@
+/*
+* CODE GENERATED AUTOMATICALLY WITH github.com/genjidb/genji/dev/gensqltest
+* THIS FILE SHOULD NOT BE EDITED BY HAND
+*/
+package {{ .Package }}
+
+import (
+	"testing"
+
+	"github.com/genjidb/genji"
+	"github.com/genjidb/genji/testutil/testgen"
+	"github.com/stretchr/testify/require"
+)
+
+func Test{{ .TestName }}(t *testing.T) {
+	setup := func(t *testing.T, db *genji.DB) {
+		t.Helper()
+
+		q := `
+{{- range .Suite.Setup }}
+{{ . }}
+{{- end }}
+`
+        err := db.Exec(q)
+        require.NoError(t, err)
+    }
+    {{""}}
+    {{- range .Suite.Tests }}
+    // --------------------------------------------------------------------------
+    t.Run("{{ .Name }}", func(t *testing.T) {
+        db, err := genji.Open(":memory:")
+        require.NoError(t, err)
+        defer db.Close()
+
+        setup(t, db)
+        {{ "" }}
+
+        {{- range .Statements }}
+        t.Run(`{{ escapeBackticks (index .Expr 0) }}`, func(t *testing.T) {
+            q := `
+{{- range .Expr }}
+{{ escapeBackticks . }}
+{{- end }}
+`
+            {{- if .Fails }}
+            err := db.Exec(q)
+                {{- if .ErrorMatch }}
+            require.NotNil(t, err, "expected error, got nil")
+            require.Regexp(t, regexp.MustCompile("{{ .ErrorMatch }}"), err.Error())
+                {{- else }}
+            require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+                {{- end }}
+            {{- else }}
+            res, err := db.Query(q)
+            require.NoError(t, err)
+            defer res.Close()
+
+            {{- if gt (len .Result) 0 }}
+            raw := `
+{{- range .Result }}
+{{ escapeBackticks . }}
+{{- end}}
+`
+            testgen.RequireStreamEq(t, raw, res)
+            {{- end }}
+            {{- end }}
+        })
+        {{ "" }}
+        {{- end }}
+    })
+    {{""}}
+    {{- end }}
+}
diff --git a/query/gen_insert_statements_test.go b/query/gen_insert_statements_test.go
new file mode 100644
index 00000000..16071051
--- /dev/null
+++ b/query/gen_insert_statements_test.go
@@ -0,0 +1,792 @@
+/*
+* CODE GENERATED AUTOMATICALLY WITH github.com/genjidb/genji/dev/gensqltest
+* THIS FILE SHOULD NOT BE EDITED BY HAND
+ */
+package query_test
+
+import (
+	"regexp"
+	"testing"
+
+	"github.com/genjidb/genji"
+	"github.com/genjidb/genji/testutil/testgen"
+	"github.com/stretchr/testify/require"
+)
+
+func TestGenInsertStatements(t *testing.T) {
+	setup := func(t *testing.T, db *genji.DB) {
+		t.Helper()
+
+		q := `
+CREATE TABLE test;
+CREATE TABLE test_idx;
+CREATE INDEX idx_a ON test_idx (a);
+CREATE INDEX idx_b ON test_idx (b);
+CREATE INDEX idx_c ON test_idx (c);
+`
+		err := db.Exec(q)
+		require.NoError(t, err)
+	}
+
+	// --------------------------------------------------------------------------
+	t.Run("values, no columns", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test VALUES ("a", 'b', 'c');`, func(t *testing.T) {
+			q := `
+INSERT INTO test VALUES ("a", 'b', 'c');
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("values, with columns", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test (a, b, c) VALUES ('a', 'b', 'c');`, func(t *testing.T) {
+			q := `
+INSERT INTO test (a, b, c) VALUES ('a', 'b', 'c');
+SELECT pk(), * FROM test;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "pk()": 1,
+  "a": "a",
+  "b": "b",
+  "c":"c"
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("values, ident", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test (a) VALUES (a);`, func(t *testing.T) {
+			q := `
+INSERT INTO test (a) VALUES (a);
+`
+			err := db.Exec(q)
+			require.NotNil(t, err, "expected error, got nil")
+			require.Regexp(t, regexp.MustCompile("field not found"), err.Error())
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("values, ident string", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test (a) VALUES (`+"`"+`a`+"`"+`);`, func(t *testing.T) {
+			q := `
+INSERT INTO test (a) VALUES (` + "`" + `a` + "`" + `);
+`
+			err := db.Exec(q)
+			require.NotNil(t, err, "expected error, got nil")
+			require.Regexp(t, regexp.MustCompile("field not found"), err.Error())
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("values, fields ident string", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test (a, `+"`"+`foo bar`+"`"+`) VALUES ('c', 'd');`, func(t *testing.T) {
+			q := `
+INSERT INTO test (a, ` + "`" + `foo bar` + "`" + `) VALUES ('c', 'd');
+SELECT pk(), * FROM test;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "pk()": 1,
+  "a": "c",
+  "foo bar": "d"
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("values, list", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test (a, b, c) VALUES ("a", 'b', [1, 2, 3]);`, func(t *testing.T) {
+			q := `
+INSERT INTO test (a, b, c) VALUES ("a", 'b', [1, 2, 3]);
+SELECT pk(), * FROM test;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "pk()": 1,
+  "a": "a",
+  "b":"b",
+  "c": [1 , 2, 3]
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("values, document", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test (a, b, c) VALUES ("a", 'b', {c: 1, d: c + 1});`, func(t *testing.T) {
+			q := `
+INSERT INTO test (a, b, c) VALUES ("a", 'b', {c: 1, d: c + 1});
+SELECT pk(), * FROM test;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "pk()": 1,
+  "a": "a",
+  "b": "b",
+  "c": {
+    "c": 1,
+    "d": 2
+  }
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("document", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test VALUES {a: 'a', b: 2.3, c: 1 = 1};`, func(t *testing.T) {
+			q := `
+INSERT INTO test VALUES {a: 'a', b: 2.3, c: 1 = 1};
+SELECT pk(), * FROM test;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "pk()": 1,
+  "a": "a",
+  "b": 2.3,
+  "c": true
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("document, list", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test VALUES {a: [1, 2, 3]};`, func(t *testing.T) {
+			q := `
+INSERT INTO test VALUES {a: [1, 2, 3]};
+SELECT pk(), * FROM test;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "pk()": 1,
+  "a": [
+    1,
+    2
+,
+    3
+  ]
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("document, strings", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test VALUES {'a': 'a', b: 2.3};`, func(t *testing.T) {
+			q := `
+INSERT INTO test VALUES {'a': 'a', b: 2.3};
+SELECT pk(), * FROM test;
+/*result:
+{
+"pk()": 1,
+"a": "a",
+"b": 2.3
+}
+*/
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("document, double quotes", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test VALUES {"a": "b"};`, func(t *testing.T) {
+			q := `
+INSERT INTO test VALUES {"a": "b"};
+SELECT pk(), * FROM test;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "pk()": 1,
+  "a": "b"
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("document, references to other field", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test VALUES {a: 400, b: a * 4};`, func(t *testing.T) {
+			q := `
+INSERT INTO test VALUES {a: 400, b: a * 4};
+SELECT pk(), * FROM test;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{"pk()":1,"a":400,"b":1600}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("index, values, no columns", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test_idx VALUES ("a", 'b', 'c');`, func(t *testing.T) {
+			q := `
+INSERT INTO test_idx VALUES ("a", 'b', 'c');
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("index, values, with columns", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test_idx (a, b, c) VALUES ('a', 'b', 'c');`, func(t *testing.T) {
+			q := `
+INSERT INTO test_idx (a, b, c) VALUES ('a', 'b', 'c');
+SELECT pk(), * FROM test_idx;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "pk()": 1,
+  "a": "a",
+  "b": "b",
+  "c":"c"
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("index, values, ident", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test_idx (a) VALUES (a);`, func(t *testing.T) {
+			q := `
+INSERT INTO test_idx (a) VALUES (a);
+`
+			err := db.Exec(q)
+			require.NotNil(t, err, "expected error, got nil")
+			require.Regexp(t, regexp.MustCompile("field not found"), err.Error())
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("index, values, ident string", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test_idx (a) VALUES (`+"`"+`a`+"`"+`);`, func(t *testing.T) {
+			q := `
+INSERT INTO test_idx (a) VALUES (` + "`" + `a` + "`" + `);
+`
+			err := db.Exec(q)
+			require.NotNil(t, err, "expected error, got nil")
+			require.Regexp(t, regexp.MustCompile("field not found"), err.Error())
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("index, values, fields ident string", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test_idx (a, `+"`"+`foo bar`+"`"+`) VALUES ('c', 'd');`, func(t *testing.T) {
+			q := `
+INSERT INTO test_idx (a, ` + "`" + `foo bar` + "`" + `) VALUES ('c', 'd');
+SELECT pk(), * FROM test_idx;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "pk()": 1,
+  "a": "c",
+  "foo bar": "d"
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("index, values, list", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test_idx (a, b, c) VALUES ("a", 'b', [1, 2, 3]);`, func(t *testing.T) {
+			q := `
+INSERT INTO test_idx (a, b, c) VALUES ("a", 'b', [1, 2, 3]);
+SELECT pk(), * FROM test_idx;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "pk()": 1,
+  "a": "a",
+  "b":"b",
+  "c": [1 , 2, 3]
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("index, values, document", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test_idx (a, b, c) VALUES ("a", 'b', {c: 1, d: c + 1});`, func(t *testing.T) {
+			q := `
+INSERT INTO test_idx (a, b, c) VALUES ("a", 'b', {c: 1, d: c + 1});
+SELECT pk(), * FROM test_idx;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "pk()": 1,
+  "a": "a",
+  "b": "b",
+  "c": {
+    "c": 1,
+    "d": 2
+  }
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("index, document", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test_idx VALUES {a: 'a', b: 2.3, c: 1 = 1};`, func(t *testing.T) {
+			q := `
+INSERT INTO test_idx VALUES {a: 'a', b: 2.3, c: 1 = 1};
+SELECT pk(), * FROM test_idx;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "pk()": 1,
+  "a": "a",
+  "b": 2.3,
+  "c": true
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("index, document, list", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test_idx VALUES {a: [1, 2, 3]};`, func(t *testing.T) {
+			q := `
+INSERT INTO test_idx VALUES {a: [1, 2, 3]};
+SELECT pk(), * FROM test_idx;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "pk()": 1,
+  "a": [
+    1,
+    2
+,
+    3
+  ]
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("index, document, strings", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test_idx VALUES {'a': 'a', b: 2.3};`, func(t *testing.T) {
+			q := `
+INSERT INTO test_idx VALUES {'a': 'a', b: 2.3};
+SELECT pk(), * FROM test_idx;
+/*result:
+{
+"pk()": 1,
+"a": "a",
+"b": 2.3
+}
+*/
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("index, document, double quotes", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test_idx VALUES {"a": "b"};`, func(t *testing.T) {
+			q := `
+INSERT INTO test_idx VALUES {"a": "b"};
+SELECT pk(), * FROM test_idx;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "pk()": 1,
+  "a": "b"
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("index, document, references to other field", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test_idx VALUES {a: 400, b: a * 4};`, func(t *testing.T) {
+			q := `
+INSERT INTO test_idx VALUES {a: 400, b: a * 4};
+SELECT pk(), * FROM test_idx;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{"pk()":1,"a":400,"b":1600}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("read-only tables", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO __genji_tables VALUES {a: 400, b: a * 4};`, func(t *testing.T) {
+			q := `
+INSERT INTO __genji_tables VALUES {a: 400, b: a * 4};
+`
+			err := db.Exec(q)
+			require.NotNil(t, err, "expected error, got nil")
+			require.Regexp(t, regexp.MustCompile("cannot write to read-only table"), err.Error())
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("insert with primary keys", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE testpk (foo INTEGER PRIMARY KEY);`, func(t *testing.T) {
+			q := `
+CREATE TABLE testpk (foo INTEGER PRIMARY KEY);
+INSERT INTO testpk (bar) VALUES (1);
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+		t.Run(`INSERT INTO testpk (bar, foo) VALUES (1, 2);`, func(t *testing.T) {
+			q := `
+INSERT INTO testpk (bar, foo) VALUES (1, 2);
+INSERT INTO testpk (bar, foo) VALUES (1, 2);
+`
+			err := db.Exec(q)
+			require.NotNil(t, err, "expected error, got nil")
+			require.Regexp(t, regexp.MustCompile("duplicate"), err.Error())
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("insert with shadowing", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO test (`+"`"+`pk()`+"`"+`, `+"`"+`key`+"`"+`) VALUES (1, 2);`, func(t *testing.T) {
+			q := `
+INSERT INTO test (` + "`" + `pk()` + "`" + `, ` + "`" + `key` + "`" + `) VALUES (1, 2);
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("insert with types constraints", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_tc(`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_tc(
+b bool, db double,
+i integer, bb blob, byt bytes,
+t text, a array, d document
+);
+INSERT INTO test_tc
+VALUES {
+i: 10000000000, db: 21.21, b: true,
+bb: "YmxvYlZhbHVlCg==", byt: "Ynl0ZXNWYWx1ZQ==",
+t: "text", a: [1, "foo", true], d: {"foo": "bar"}
+};
+SELECT * FROM test_tc;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{
+  "i": 10000000000,
+  "db": 21.21,
+  "b": true,
+  "bb": CAST("YmxvYlZhbHVlCg==" AS BLOB),
+  "byt": CAST("Ynl0ZXNWYWx1ZQ==" AS BYTES),
+  "t": "text",
+  "a": [
+    1,
+    "foo",
+    true
+  ],
+  "d": {
+    "foo": "bar"
+  }
+}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("insert with inferred constraints", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_ic(a INTEGER, s.b TEXT);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_ic(a INTEGER, s.b TEXT);
+INSERT INTO test_ic VALUES {s: 1};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+}
diff --git a/query/gen_insert_statements_test.sql b/query/gen_insert_statements_test.sql
new file mode 100644
index 00000000..f2081e69
--- /dev/null
+++ b/query/gen_insert_statements_test.sql
@@ -0,0 +1,297 @@
+-- setup:
+CREATE TABLE test;
+CREATE TABLE test_idx;
+
+CREATE INDEX idx_a ON test_idx (a);
+CREATE INDEX idx_b ON test_idx (b);
+CREATE INDEX idx_c ON test_idx (c);
+
+-- test: values, no columns
+INSERT INTO test VALUES ("a", 'b', 'c');
+-- error:
+
+-- test: values, with columns
+INSERT INTO test (a, b, c) VALUES ('a', 'b', 'c');
+SELECT pk(), * FROM test;
+/* result:
+{
+  "pk()": 1,
+  "a": "a",
+  "b": "b",
+  "c":"c"
+}
+*/
+
+-- test: values, ident
+INSERT INTO test (a) VALUES (a);
+-- error: field not found
+
+-- test: values, ident string
+INSERT INTO test (a) VALUES (`a`);
+-- error: field not found
+
+-- test: values, fields ident string
+INSERT INTO test (a, `foo bar`) VALUES ('c', 'd');
+SELECT pk(), * FROM test;
+/* result:
+{
+  "pk()": 1,
+  "a": "c",
+  "foo bar": "d"
+}
+*/
+
+-- test: values, list
+INSERT INTO test (a, b, c) VALUES ("a", 'b', [1, 2, 3]);
+SELECT pk(), * FROM test;
+/* result:
+{
+  "pk()": 1,
+  "a": "a",
+  "b":"b",
+  "c": [1 , 2, 3]
+}
+*/
+
+-- test: values, document
+INSERT INTO test (a, b, c) VALUES ("a", 'b', {c: 1, d: c + 1});
+SELECT pk(), * FROM test;
+/* result:
+{
+  "pk()": 1,
+  "a": "a",
+  "b": "b",
+  "c": {
+    "c": 1,
+    "d": 2
+  }
+}
+*/
+
+-- test: document
+INSERT INTO test VALUES {a: 'a', b: 2.3, c: 1 = 1};
+SELECT pk(), * FROM test;
+/* result:
+{
+  "pk()": 1,
+  "a": "a",
+  "b": 2.3,
+  "c": true
+}
+*/
+
+-- test: document, list
+INSERT INTO test VALUES {a: [1, 2, 3]};
+SELECT pk(), * FROM test;
+/* result:
+{
+  "pk()": 1,
+  "a": [
+    1,
+    2
+,
+    3
+  ]
+}
+*/
+
+-- test: document, strings
+INSERT INTO test VALUES {'a': 'a', b: 2.3};
+SELECT pk(), * FROM test;
+/*result:
+{
+  "pk()": 1,
+  "a": "a",
+  "b": 2.3
+}
+*/
+
+-- test: document, double quotes
+INSERT INTO test VALUES {"a": "b"};
+SELECT pk(), * FROM test;
+/* result:
+{
+  "pk()": 1,
+  "a": "b"
+}
+*/
+
+-- test: document, references to other field
+INSERT INTO test VALUES {a: 400, b: a * 4};
+SELECT pk(), * FROM test;
+/* result:
+{"pk()":1,"a":400,"b":1600}
+*/
+
+-- with indexes
+-- test: index, values, no columns
+INSERT INTO test_idx VALUES ("a", 'b', 'c');
+-- error:
+
+-- test: index, values, with columns
+INSERT INTO test_idx (a, b, c) VALUES ('a', 'b', 'c');
+SELECT pk(), * FROM test_idx;
+/* result:
+{
+  "pk()": 1,
+  "a": "a",
+  "b": "b",
+  "c":"c"
+}
+*/
+
+-- test: index, values, ident
+INSERT INTO test_idx (a) VALUES (a);
+-- error: field not found
+
+-- test: index, values, ident string
+INSERT INTO test_idx (a) VALUES (`a`);
+-- error: field not found
+
+-- test: index, values, fields ident string
+INSERT INTO test_idx (a, `foo bar`) VALUES ('c', 'd');
+SELECT pk(), * FROM test_idx;
+/* result:
+{
+  "pk()": 1,
+  "a": "c",
+  "foo bar": "d"
+}
+*/
+
+-- test: index, values, list
+INSERT INTO test_idx (a, b, c) VALUES ("a", 'b', [1, 2, 3]);
+SELECT pk(), * FROM test_idx;
+/* result:
+{
+  "pk()": 1,
+  "a": "a",
+  "b":"b",
+  "c": [1 , 2, 3]
+}
+*/
+
+-- test: index, values, document
+INSERT INTO test_idx (a, b, c) VALUES ("a", 'b', {c: 1, d: c + 1});
+SELECT pk(), * FROM test_idx;
+/* result:
+{
+  "pk()": 1,
+  "a": "a",
+  "b": "b",
+  "c": {
+    "c": 1,
+    "d": 2
+  }
+}
+*/
+
+-- test: index, document
+INSERT INTO test_idx VALUES {a: 'a', b: 2.3, c: 1 = 1};
+SELECT pk(), * FROM test_idx;
+/* result:
+{
+  "pk()": 1,
+  "a": "a",
+  "b": 2.3,
+  "c": true
+}
+*/
+
+-- test: index, document, list
+INSERT INTO test_idx VALUES {a: [1, 2, 3]};
+SELECT pk(), * FROM test_idx;
+/* result:
+{
+  "pk()": 1,
+  "a": [
+    1,
+    2
+,
+    3
+  ]
+}
+*/
+
+-- test: index, document, strings
+INSERT INTO test_idx VALUES {'a': 'a', b: 2.3};
+SELECT pk(), * FROM test_idx;
+/*result:
+{
+  "pk()": 1,
+  "a": "a",
+  "b": 2.3
+}
+*/
+
+-- test: index, document, double quotes
+INSERT INTO test_idx VALUES {"a": "b"};
+SELECT pk(), * FROM test_idx;
+/* result:
+{
+  "pk()": 1,
+  "a": "b"
+}
+*/
+
+-- test: index, document, references to other field
+INSERT INTO test_idx VALUES {a: 400, b: a * 4};
+SELECT pk(), * FROM test_idx;
+/* result:
+{"pk()":1,"a":400,"b":1600}
+*/
+
+-- test: read-only tables
+INSERT INTO __genji_tables VALUES {a: 400, b: a * 4};
+-- error: cannot write to read-only table
+
+-- test: insert with primary keys
+CREATE TABLE testpk (foo INTEGER PRIMARY KEY);
+INSERT INTO testpk (bar) VALUES (1);
+-- error:
+INSERT INTO testpk (bar, foo) VALUES (1, 2);
+INSERT INTO testpk (bar, foo) VALUES (1, 2);
+-- error: duplicate
+
+-- test: insert with shadowing
+INSERT INTO test (`pk()`, `key`) VALUES (1, 2);
+
+-- test: insert with types constraints
+CREATE TABLE test_tc(
+    b bool, db double,
+    i integer, bb blob, byt bytes,
+    t text, a array, d document
+);
+
+INSERT INTO test_tc
+VALUES {
+    i: 10000000000, db: 21.21, b: true,
+    bb: "YmxvYlZhbHVlCg==", byt: "Ynl0ZXNWYWx1ZQ==",
+    t: "text", a: [1, "foo", true], d: {"foo": "bar"}
+};
+
+SELECT * FROM test_tc;
+/* result:
+{
+  "i": 10000000000,
+  "db": 21.21,
+  "b": true,
+  "bb": CAST("YmxvYlZhbHVlCg==" AS BLOB),
+  "byt": CAST("Ynl0ZXNWYWx1ZQ==" AS BYTES),
+  "t": "text",
+  "a": [
+    1,
+    "foo",
+    true
+  ],
+  "d": {
+    "foo": "bar"
+  }
+}
+*/
+
+-- test: insert with inferred constraints
+CREATE TABLE test_ic(a INTEGER, s.b TEXT);
+INSERT INTO test_ic VALUES {s: 1};
+-- error:
+
diff --git a/query/gen_insert_with_constraints_test.go b/query/gen_insert_with_constraints_test.go
new file mode 100644
index 00000000..8fb7c125
--- /dev/null
+++ b/query/gen_insert_with_constraints_test.go
@@ -0,0 +1,898 @@
+/*
+* CODE GENERATED AUTOMATICALLY WITH github.com/genjidb/genji/dev/gensqltest
+* THIS FILE SHOULD NOT BE EDITED BY HAND
+ */
+package query_test
+
+import (
+	"testing"
+
+	"github.com/genjidb/genji"
+	"github.com/stretchr/testify/require"
+)
+
+func TestGenInsertWithConstraints(t *testing.T) {
+	setup := func(t *testing.T, db *genji.DB) {
+		t.Helper()
+
+		q := `
+`
+		err := db.Exec(q)
+		require.NoError(t, err)
+	}
+
+	// --------------------------------------------------------------------------
+	t.Run("insert with errors, not null without type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a NOT NULL);
+INSERT INTO test_e VALUES {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("insert with errors, array / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a ARRAY NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a ARRAY NOT NULL);
+INSERT INTO test_e VALUES {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("insert with errors, array / not null with non-respected type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a ARRAY NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a ARRAY NOT NULL);
+INSERT INTO test_e VALUES {a: 42};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("insert with errors, blob", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a BLOB);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a BLOB);
+INSERT INTO test_e {a: true};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("blob / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a BLOB NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a BLOB NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("blob / not null with non-respected type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a BLOB NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a BLOB NOT NULL);
+INSERT INTO test_e {a: 42};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("bool / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a BOOL NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a BOOL NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("bytes", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a BYTES);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a BYTES);
+INSERT INTO test_e {a: [1,2,3]};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("bytes / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a BYTES NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a BYTES NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("bytes / not null with non-respected type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a BYTES NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a BYTES NOT NULL);
+INSERT INTO test_e {a: 42};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("document", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a DOCUMENT);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a DOCUMENT);
+INSERT INTO test_e {"a": "foo"};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("document / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a DOCUMENT NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a DOCUMENT NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("document / not null with non-respected type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a DOCUMENT NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a DOCUMENT NOT NULL);
+INSERT INTO test_e {a: false};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("double", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a DOUBLE);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a DOUBLE);
+INSERT INTO test_e {a: "foo"};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("double / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a DOUBLE NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a DOUBLE NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("double / not null with non-respected type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a DOUBLE NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a DOUBLE NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("double precision", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a DOUBLE PRECISION);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a DOUBLE PRECISION);
+INSERT INTO test_e {a: "foo"};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("double precision / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a DOUBLE PRECISION NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a DOUBLE PRECISION NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("double precision / not null with non-respected type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a DOUBLE PRECISION NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a DOUBLE PRECISION NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("real", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a REAL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a REAL);
+INSERT INTO test_e {a: "foo"};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("real / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a REAL NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a REAL NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("real / not null with non-respected type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a REAL NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a REAL NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("integer", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a INTEGER);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a INTEGER);
+INSERT INTO test_e {a: "foo"};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("integer / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a INTEGER NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a INTEGER NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("integer / not null with non-respected type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a INTEGER NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a INTEGER NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("int2", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a INT2);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a INT2);
+INSERT INTO test_e {a: "foo"};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("int2 / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a INT2 NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a INT2 NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("int2 / not null with non-respected type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a INT NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a INT NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("int8", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a INT8);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a INT8);
+INSERT INTO test_e {a: "foo"};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("int8 / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a INT8 NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a INT8 NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("int8 / not null with non-respected type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a INT8 NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a INT8 NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("tinyint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a TINYINT);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a TINYINT);
+INSERT INTO test_e {a: "foo"};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("tinyint / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a TINYINT NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a TINYINT NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("tinyint / not null with non-respected type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a TINYINT NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a TINYINT NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("bigint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a BIGINT);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a BIGINT);
+INSERT INTO test_e {a: "foo"};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("bigint / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a BIGINT NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a BIGINT NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("bigint / not null with non-respected type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a BIGINT NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a BIGINT NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("smallint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a SMALLINT);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a SMALLINT);
+INSERT INTO test_e {a: "foo"};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("smallint / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a SMALLINT NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a SMALLINT NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("smallint / not null with non-respected type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a SMALLINT NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a SMALLINT NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("mediumint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a MEDIUMINT);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a MEDIUMINT);
+INSERT INTO test_e {a: "foo"};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("mediumint / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a MEDIUMINT NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a MEDIUMINT NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("mediumint / not null with non-respected type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a MEDIUMINT NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a MEDIUMINT NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("text / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a TEXT NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a TEXT NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("varchar / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a VARCHAR(255) NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a VARCHAR(255) NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("character / not null with type constraint", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`CREATE TABLE test_e (a CHARACTER(64) NOT NULL);`, func(t *testing.T) {
+			q := `
+CREATE TABLE test_e (a CHARACTER(64) NOT NULL);
+INSERT INTO test_e {};
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+}
diff --git a/query/gen_insert_with_constraints_test.sql b/query/gen_insert_with_constraints_test.sql
new file mode 100644
index 00000000..54195548
--- /dev/null
+++ b/query/gen_insert_with_constraints_test.sql
@@ -0,0 +1,229 @@
+-- test: insert with errors, not null without type constraint
+CREATE TABLE test_e (a NOT NULL);
+INSERT INTO test_e VALUES {};
+-- error:
+
+-- test: insert with errors, array / not null with type constraint
+CREATE TABLE test_e (a ARRAY NOT NULL);
+INSERT INTO test_e VALUES {};
+-- error:
+
+-- test: insert with errors, array / not null with non-respected type constraint
+CREATE TABLE test_e (a ARRAY NOT NULL);
+INSERT INTO test_e VALUES {a: 42};
+-- error:
+
+-- test: insert with errors, blob
+CREATE TABLE test_e (a BLOB);
+INSERT INTO test_e {a: true};
+-- error:
+
+-- test: blob / not null with type constraint
+CREATE TABLE test_e (a BLOB NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: blob / not null with non-respected type constraint
+CREATE TABLE test_e (a BLOB NOT NULL);
+INSERT INTO test_e {a: 42};
+-- error:
+
+-- test: bool / not null with type constraint
+CREATE TABLE test_e (a BOOL NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: bytes
+CREATE TABLE test_e (a BYTES);
+INSERT INTO test_e {a: [1,2,3]};
+-- error:
+
+-- test: bytes / not null with type constraint
+CREATE TABLE test_e (a BYTES NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: bytes / not null with non-respected type constraint
+CREATE TABLE test_e (a BYTES NOT NULL);
+INSERT INTO test_e {a: 42};
+-- error:
+
+-- test: document
+CREATE TABLE test_e (a DOCUMENT);
+INSERT INTO test_e {"a": "foo"};
+-- error:
+
+-- test: document / not null with type constraint
+CREATE TABLE test_e (a DOCUMENT NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: document / not null with non-respected type constraint
+CREATE TABLE test_e (a DOCUMENT NOT NULL);
+INSERT INTO test_e {a: false};
+-- error:
+
+-- test: double
+CREATE TABLE test_e (a DOUBLE);
+INSERT INTO test_e {a: "foo"};
+-- error:
+
+-- test: double / not null with type constraint
+CREATE TABLE test_e (a DOUBLE NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: double / not null with non-respected type constraint
+CREATE TABLE test_e (a DOUBLE NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+-- error:
+
+-- test: double precision
+CREATE TABLE test_e (a DOUBLE PRECISION);
+INSERT INTO test_e {a: "foo"};
+-- error:
+
+-- test: double precision / not null with type constraint
+CREATE TABLE test_e (a DOUBLE PRECISION NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: double precision / not null with non-respected type constraint
+CREATE TABLE test_e (a DOUBLE PRECISION NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+-- error:
+
+-- test: real
+CREATE TABLE test_e (a REAL);
+INSERT INTO test_e {a: "foo"};
+-- error:
+
+-- test: real / not null with type constraint
+CREATE TABLE test_e (a REAL NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: real / not null with non-respected type constraint
+CREATE TABLE test_e (a REAL NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+-- error:
+
+-- test: integer
+CREATE TABLE test_e (a INTEGER);
+INSERT INTO test_e {a: "foo"};
+-- error:
+
+-- test: integer / not null with type constraint
+CREATE TABLE test_e (a INTEGER NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: integer / not null with non-respected type constraint
+CREATE TABLE test_e (a INTEGER NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+-- error:
+
+-- test: int2
+CREATE TABLE test_e (a INT2);
+INSERT INTO test_e {a: "foo"};
+-- error:
+
+-- test: int2 / not null with type constraint
+CREATE TABLE test_e (a INT2 NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: int2 / not null with non-respected type constraint
+CREATE TABLE test_e (a INT NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+-- error:
+
+-- test: int8
+CREATE TABLE test_e (a INT8);
+INSERT INTO test_e {a: "foo"};
+-- error:
+
+-- test: int8 / not null with type constraint
+CREATE TABLE test_e (a INT8 NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: int8 / not null with non-respected type constraint
+CREATE TABLE test_e (a INT8 NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+-- error:
+
+-- test: tinyint
+CREATE TABLE test_e (a TINYINT);
+INSERT INTO test_e {a: "foo"};
+-- error:
+
+-- test: tinyint / not null with type constraint
+CREATE TABLE test_e (a TINYINT NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: tinyint / not null with non-respected type constraint
+CREATE TABLE test_e (a TINYINT NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+-- error:
+
+-- test: bigint
+CREATE TABLE test_e (a BIGINT);
+INSERT INTO test_e {a: "foo"};
+-- error:
+
+-- test: bigint / not null with type constraint
+CREATE TABLE test_e (a BIGINT NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: bigint / not null with non-respected type constraint
+CREATE TABLE test_e (a BIGINT NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+-- error:
+
+-- test: smallint
+CREATE TABLE test_e (a SMALLINT);
+INSERT INTO test_e {a: "foo"};
+-- error:
+
+-- test: smallint / not null with type constraint
+CREATE TABLE test_e (a SMALLINT NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: smallint / not null with non-respected type constraint
+CREATE TABLE test_e (a SMALLINT NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+-- error:
+
+-- test: mediumint
+CREATE TABLE test_e (a MEDIUMINT);
+INSERT INTO test_e {a: "foo"};
+-- error:
+
+-- test: mediumint / not null with type constraint
+CREATE TABLE test_e (a MEDIUMINT NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: mediumint / not null with non-respected type constraint
+CREATE TABLE test_e (a MEDIUMINT NOT NULL);
+INSERT INTO test_e {a: [1,2,3]};
+-- error:
+
+-- test: text / not null with type constraint
+CREATE TABLE test_e (a TEXT NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: varchar / not null with type constraint
+CREATE TABLE test_e (a VARCHAR(255) NOT NULL);
+INSERT INTO test_e {};
+-- error:
+
+-- test: character / not null with type constraint
+CREATE TABLE test_e (a CHARACTER(64) NOT NULL);
+INSERT INTO test_e {};
+-- error:
diff --git a/query/gen_insert_with_select_test.go b/query/gen_insert_with_select_test.go
new file mode 100644
index 00000000..52ef68a4
--- /dev/null
+++ b/query/gen_insert_with_select_test.go
@@ -0,0 +1,214 @@
+/*
+* CODE GENERATED AUTOMATICALLY WITH github.com/genjidb/genji/dev/gensqltest
+* THIS FILE SHOULD NOT BE EDITED BY HAND
+ */
+package query_test
+
+import (
+	"testing"
+
+	"github.com/genjidb/genji"
+	"github.com/genjidb/genji/testutil/testgen"
+	"github.com/stretchr/testify/require"
+)
+
+func TestGenInsertWithSelect(t *testing.T) {
+	setup := func(t *testing.T, db *genji.DB) {
+		t.Helper()
+
+		q := `
+CREATE TABLE foo;
+CREATE TABLE bar;
+INSERT INTO bar (a, b) VALUES (1, 10);
+`
+		err := db.Exec(q)
+		require.NoError(t, err)
+	}
+
+	// --------------------------------------------------------------------------
+	t.Run("same table", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO foo SELECT * FROM foo;`, func(t *testing.T) {
+			q := `
+INSERT INTO foo SELECT * FROM foo;
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("No fields / No projection", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO foo SELECT * FROM bar;`, func(t *testing.T) {
+			q := `
+INSERT INTO foo SELECT * FROM bar;
+SELECT pk(), * FROM foo;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{"pk()":1, "a":1, "b":10}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("No fields / Projection", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO foo SELECT a FROM bar;`, func(t *testing.T) {
+			q := `
+INSERT INTO foo SELECT a FROM bar;
+SELECT pk(), * FROM foo;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{"pk()":1, "a":1}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("With fields / No Projection", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO foo (a, b) SELECT * FROM bar;`, func(t *testing.T) {
+			q := `
+INSERT INTO foo (a, b) SELECT * FROM bar;
+SELECT pk(), * FROM foo;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{"pk()":1, "a":1, "b":10}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("With fields / Projection", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO foo (c, d) SELECT a, b FROM bar;`, func(t *testing.T) {
+			q := `
+INSERT INTO foo (c, d) SELECT a, b FROM bar;
+SELECT pk(), * FROM foo;
+`
+			res, err := db.Query(q)
+			require.NoError(t, err)
+			defer res.Close()
+			raw := `
+{"pk()":1, "c":1, "d":10}
+`
+			testgen.RequireStreamEq(t, raw, res)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("Too many fields / No Projection", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO foo (c) SELECT * FROM bar;`, func(t *testing.T) {
+			q := `
+INSERT INTO foo (c) SELECT * FROM bar;
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("Too many fields / Projection", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO foo (c, d) SELECT a, b, c FROM bar;`, func(t *testing.T) {
+			q := `
+INSERT INTO foo (c, d) SELECT a, b, c FROM bar;
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("Too few fields / No Projection", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO foo (c, d, e) SELECT * FROM bar;`, func(t *testing.T) {
+			q := `
+INSERT INTO foo (c, d, e) SELECT * FROM bar;
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+	// --------------------------------------------------------------------------
+	t.Run("Too few fields / Projection", func(t *testing.T) {
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		setup(t, db)
+
+		t.Run(`INSERT INTO foo (c, d) SELECT a FROM bar`+"`"+`;`, func(t *testing.T) {
+			q := `
+INSERT INTO foo (c, d) SELECT a FROM bar` + "`" + `;
+`
+			err := db.Exec(q)
+			require.Errorf(t, err, "expected\n%s\nto raise an error but got none", q)
+		})
+
+	})
+
+}
diff --git a/query/gen_insert_with_select_test.sql b/query/gen_insert_with_select_test.sql
new file mode 100644
index 00000000..0deb49c1
--- /dev/null
+++ b/query/gen_insert_with_select_test.sql
@@ -0,0 +1,52 @@
+-- setup:
+CREATE TABLE foo;
+CREATE TABLE bar;
+INSERT INTO bar (a, b) VALUES (1, 10);
+
+-- test: same table
+INSERT INTO foo SELECT * FROM foo;
+-- error:
+
+-- test: No fields / No projection
+INSERT INTO foo SELECT * FROM bar;
+SELECT pk(), * FROM foo;
+/* result:
+{"pk()":1, "a":1, "b":10}
+*/
+
+-- test: No fields / Projection
+INSERT INTO foo SELECT a FROM bar;
+SELECT pk(), * FROM foo;
+/* result:
+{"pk()":1, "a":1}
+*/
+
+-- test: With fields / No Projection
+INSERT INTO foo (a, b) SELECT * FROM bar;
+SELECT pk(), * FROM foo;
+/* result:
+{"pk()":1, "a":1, "b":10}
+*/
+
+-- test: With fields / Projection
+INSERT INTO foo (c, d) SELECT a, b FROM bar;
+SELECT pk(), * FROM foo;
+/* result:
+{"pk()":1, "c":1, "d":10}
+*/
+
+-- test: Too many fields / No Projection
+INSERT INTO foo (c) SELECT * FROM bar;
+-- error:
+
+-- test: Too many fields / Projection
+INSERT INTO foo (c, d) SELECT a, b, c FROM bar;
+-- error:
+
+-- test: Too few fields / No Projection
+INSERT INTO foo (c, d, e) SELECT * FROM bar;
+-- error:
+
+-- test: Too few fields / Projection
+INSERT INTO foo (c, d) SELECT a FROM bar`;
+-- error:
diff --git a/query/gentests.go b/query/gentests.go
new file mode 100644
index 00000000..17e06c1f
--- /dev/null
+++ b/query/gentests.go
@@ -0,0 +1,3 @@
+package query
+
+//go:generate go run ../dev/gensqltest -package=query_test ./*_test.sql
diff --git a/query/insert_test.go b/query/insert_test.go
index 01fbca59..b64fe770 100644
--- a/query/insert_test.go
+++ b/query/insert_test.go
@@ -3,11 +3,9 @@ package query_test
 import (
 	"bytes"
 	"database/sql"
-	"fmt"
 	"testing"
 
 	"github.com/genjidb/genji"
-	"github.com/genjidb/genji/database"
 	"github.com/genjidb/genji/testutil"
 	"github.com/stretchr/testify/require"
 )
@@ -20,24 +18,11 @@ func TestInsertStmt(t *testing.T) {
 		expected string
 		params   []interface{}
 	}{
-		{"Values / No columns", `INSERT INTO test VALUES ("a", 'b', 'c')`, true, ``, nil},
-		{"Values / With columns", `INSERT INTO test (a, b, c) VALUES ('a', 'b', 'c')`, false, `[{"pk()":1,"a":"a","b":"b","c":"c"}]`, nil},
-		{"Values / Ident", `INSERT INTO test (a) VALUES (a)`, true, ``, nil},
-		{"Values / Ident string", "INSERT INTO test (a) VALUES (`a`)", true, ``, nil},
-		{"Values / With fields ident string", "INSERT INTO test (a, `foo bar`) VALUES ('c', 'd')", false, `[{"pk()":1,"a":"c","foo bar":"d"}]`, nil},
 		{"Values / Positional Params", "INSERT INTO test (a, b, c) VALUES (?, 'e', ?)", false, `[{"pk()":1,"a":"d","b":"e","c":"f"}]`, []interface{}{"d", "f"}},
 		{"Values / Named Params", "INSERT INTO test (a, b, c) VALUES ($d, 'e', $f)", false, `[{"pk()":1,"a":"d","b":"e","c":"f"}]`, []interface{}{sql.Named("f", "f"), sql.Named("d", "d")}},
 		{"Values / Invalid params", "INSERT INTO test (a, b, c) VALUES ('d', ?)", true, "", []interface{}{'e'}},
-		{"Values / List", `INSERT INTO test (a, b, c) VALUES ("a", 'b', [1, 2, 3])`, false, `[{"pk()":1,"a":"a","b":"b","c":[1,2,3]}]`, nil},
-		{"Values / Document", `INSERT INTO test (a, b, c) VALUES ("a", 'b', {c: 1, d: c + 1})`, false, `[{"pk()":1,"a":"a","b":"b","c":{"c":1,"d":2}}]`, nil},
-		{"Documents", "INSERT INTO test VALUES {a: 'a', b: 2.3, c: 1 = 1}", false, `[{"pk()":1,"a":"a","b":2.3,"c":true}]`, nil},
-		{"Documents / Positional Params", "INSERT INTO test VALUES {a: ?, b: 2.3, c: ?}", false, `[{"pk()":1,"a":"a","b":2.3,"c":true}]`, []interface{}{"a", true}},
 		{"Documents / Named Params", "INSERT INTO test VALUES {a: $a, b: 2.3, c: $c}", false, `[{"pk()":1,"a":1,"b":2.3,"c":true}]`, []interface{}{sql.Named("c", true), sql.Named("a", 1)}},
 		{"Documents / List ", "INSERT INTO test VALUES {a: [1, 2, 3]}", false, `[{"pk()":1,"a":[1,2,3]}]`, nil},
-		{"Documents / strings", `INSERT INTO test VALUES {'a': 'a', b: 2.3}`, false, `[{"pk()":1,"a":"a","b":2.3}]`, nil},
-		{"Documents / double quotes", `INSERT INTO test VALUES {"a": "b"}`, false, `[{"pk()":1,"a":"b"}]`, nil},
-		{"Documents / with reference to other fields", `INSERT INTO test VALUES {a: 400, b: a * 4}`, false, `[{"pk()":1,"a":400,"b":1600}]`, nil},
-		{"Read-only tables", `INSERT INTO __genji_tables VALUES {a: 400, b: a * 4}`, true, ``, nil},
 	}
 
 	for _, test := range tests {
@@ -80,35 +65,6 @@ func TestInsertStmt(t *testing.T) {
 		t.Run("With Index/"+test.name, testFn(true))
 	}
 
-	t.Run("with primary key", func(t *testing.T) {
-		db, err := genji.Open(":memory:")
-		require.NoError(t, err)
-		defer db.Close()
-
-		err = db.Exec("CREATE TABLE test (foo INTEGER PRIMARY KEY)")
-		require.NoError(t, err)
-
-		err = db.Exec(`INSERT INTO test (bar) VALUES (1)`)
-		require.Error(t, err)
-		err = db.Exec(`INSERT INTO test (bar, foo) VALUES (1, 2)`)
-		require.NoError(t, err)
-
-		err = db.Exec(`INSERT INTO test (bar, foo) VALUES (1, 2)`)
-		require.Equal(t, err, database.ErrDuplicateDocument)
-	})
-
-	t.Run("with shadowing", func(t *testing.T) {
-		db, err := genji.Open(":memory:")
-		require.NoError(t, err)
-		defer db.Close()
-
-		err = db.Exec("CREATE TABLE test")
-		require.NoError(t, err)
-
-		err = db.Exec("INSERT INTO test (`pk()`, `key`) VALUES (1, 2)")
-		require.NoError(t, err)
-	})
-
 	t.Run("with struct param", func(t *testing.T) {
 		db, err := genji.Open(":memory:")
 		require.NoError(t, err)
@@ -133,148 +89,6 @@ func TestInsertStmt(t *testing.T) {
 		require.NoError(t, err)
 		require.JSONEq(t, `[{"a": "a", "b-b": "b"}]`, buf.String())
 	})
-
-	t.Run("with types constraints", func(t *testing.T) {
-		// This test ensures that we can insert data into every supported types.
-		db, err := genji.Open(":memory:")
-		require.NoError(t, err)
-		defer db.Close()
-
-		err = db.Exec(`CREATE TABLE test(
-			b bool, db double,
-			i integer, bb blob, byt bytes,
-			t text, a array, d document
-		)`)
-		require.NoError(t, err)
-
-		err = db.Exec(`
-			INSERT INTO test
-			VALUES {
-				i: 10000000000, db: 21.21, b: true,
-				bb: "YmxvYlZhbHVlCg==", byt: "Ynl0ZXNWYWx1ZQ==",
-				t: "text", a: [1, "foo", true], d: {"foo": "bar"}
-			}`)
-		require.NoError(t, err)
-
-		res, err := db.Query("SELECT * FROM test")
-		defer res.Close()
-
-		require.NoError(t, err)
-
-		var buf bytes.Buffer
-		err = testutil.IteratorToJSONArray(&buf, res)
-		require.NoError(t, err)
-		require.JSONEq(t, `[{
-			"i": 10000000000,
-			"db": 21.21,
-			"b": true,
-			"bb": "YmxvYlZhbHVlCg==",
-			"byt": "Ynl0ZXNWYWx1ZQ==",
-			"t": "text",
-			"a": [1, "foo", true],
-			"d": {"foo": "bar"}
-		  }]`, buf.String())
-	})
-
-	// cf issue: https://github.com/genjidb/genji/issues/328
-	t.Run("with inferred constraints", func(t *testing.T) {
-		db, err := genji.Open(":memory:")
-		require.NoError(t, err)
-		defer db.Close()
-
-		err = db.Exec(`CREATE TABLE test(a INTEGER, s.b TEXT)`)
-		require.NoError(t, err)
-
-		err = db.Exec(`insert into test VALUES {s: 1}`)
-		require.Error(t, err)
-	})
-
-	t.Run("with tests that require an error", func(t *testing.T) {
-		tests := []struct {
-			name            string
-			fieldConstraint string
-			value           string
-		}{
-			{"not null without type constraint", "NOT NULL", `{}`},
-
-			{"array / not null with type constraint", "ARRAY NOT NULL", `{}`},
-			{"array / not null with non-respected type constraint ", "ARRAY NOT NULL", `{a: 42}`},
-
-			{"blob", "BLOB", `{a: true}`},
-			{"blob / not null with type constraint", "BLOB NOT NULL", `{}`},
-			{"blob / not null with non-respected type constraint ", "BLOB NOT NULL", `{a: 42}`},
-
-			{"bool / not null with type constraint", "BOOL NOT NULL", `{}`},
-
-			{"bytes", "BYTES", `{a: [1,2,3]}`},
-			{"bytes / not null with type constraint", "BYTES NOT NULL", `{}`},
-			{"bytes / not null with non-respected type constraint ", "BYTES NOT NULL", `{a: 42}`},
-
-			{"document", "DOCUMENT", `{"a": "foo"}`},
-			{"document / not null with type constraint", "DOCUMENT NOT NULL", `{}`},
-			{"document / not null with non-respected type constraint ", "DOCUMENT NOT NULL", `{a: false}`},
-
-			{"double", "DOUBLE", `{a: "foo"}`},
-			{"double / not null with type constraint", "DOUBLE NOT NULL", `{}`},
-			{"double / not null with non-respected type constraint ", "DOUBLE NOT NULL", `{a: [1,2,3]}`},
-
-			{"double precision", "DOUBLE PRECISION", `{a: "foo"}`},
-			{"double precision / not null with type constraint", "DOUBLE PRECISION NOT NULL", `{}`},
-			{"double precision / not null with non-respected type constraint ", "DOUBLE PRECISION NOT NULL", `{a: [1,2,3]}`},
-
-			{"real", "REAL", `{a: "foo"}`},
-			{"real / not null with type constraint", "REAL NOT NULL", `{}`},
-			{"real / not null with non-respected type constraint ", "REAL NOT NULL", `{a: [1,2,3]}`},
-
-			{"integer", "INTEGER", `{a: "foo"}`},
-			{"integer / not null with type constraint", "INTEGER NOT NULL", `{}`},
-			{"integer / not null with non-respected type constraint ", "INTEGER NOT NULL", `{a: [1,2,3]}`},
-
-			{"int2", "INT2", `{a: "foo"}`},
-			{"int2 / not null with type constraint", "INT2 NOT NULL", `{}`},
-			{"int2 / not null with non-respected type constraint ", "INT NOT NULL", `{a: [1,2,3]}`},
-
-			{"int8", "INT8", `{a: "foo"}`},
-			{"int8 / not null with type constraint", "INT8 NOT NULL", `{}`},
-			{"int8 / not null with non-respected type constraint ", "INT8 NOT NULL", `{a: [1,2,3]}`},
-
-			{"tinyint", "TINYINT", `{a: "foo"}`},
-			{"tinyint / not null with type constraint", "TINYINT NOT NULL", `{}`},
-			{"tinyint / not null with non-respected type constraint ", "TINYINT NOT NULL", `{a: [1,2,3]}`},
-
-			{"bigint", "BIGINT", `{a: "foo"}`},
-			{"bigint / not null with type constraint", "BIGINT NOT NULL", `{}`},
-			{"bigint / not null with non-respected type constraint ", "BIGINT NOT NULL", `{a: [1,2,3]}`},
-
-			{"smallint", "SMALLINT", `{a: "foo"}`},
-			{"smallint / not null with type constraint", "SMALLINT NOT NULL", `{}`},
-			{"smallint / not null with non-respected type constraint ", "SMALLINT NOT NULL", `{a: [1,2,3]}`},
-
-			{"mediumint", "MEDIUMINT", `{a: "foo"}`},
-			{"mediumint / not null with type constraint", "MEDIUMINT NOT NULL", `{}`},
-			{"mediumint / not null with non-respected type constraint ", "MEDIUMINT NOT NULL", `{a: [1,2,3]}`},
-
-			{"text / not null with type constraint", "TEXT NOT NULL", `{}`},
-			{"varchar / not null with type constraint", "VARCHAR(255) NOT NULL", `{}`},
-			{"character / not null with type constraint", "CHARACTER(64) NOT NULL", `{}`},
-		}
-
-		for _, test := range tests {
-			t.Run(test.name, func(t *testing.T) {
-				db, err := genji.Open(":memory:")
-				require.NoError(t, err)
-				defer db.Close()
-
-				q := fmt.Sprintf("CREATE TABLE test (a %s)", test.fieldConstraint)
-				err = db.Exec(q)
-				require.NoError(t, err)
-
-				q = fmt.Sprintf("INSERT INTO test VALUES %s", test.value)
-				err = db.Exec(q)
-				require.Error(t, err)
-			})
-		}
-	})
 }
 
 func TestInsertSelect(t *testing.T) {
diff --git a/testutil/testgen/gen.go b/testutil/testgen/gen.go
new file mode 100644
index 00000000..f167ca0b
--- /dev/null
+++ b/testutil/testgen/gen.go
@@ -0,0 +1,71 @@
+package testgen
+
+import (
+	"strings"
+	"testing"
+
+	"github.com/genjidb/genji/document"
+	"github.com/genjidb/genji/expr"
+	"github.com/genjidb/genji/query"
+	"github.com/genjidb/genji/sql/parser"
+	"github.com/stretchr/testify/require"
+)
+
+type DocsStream struct {
+	*parser.Parser
+	env *expr.Environment
+}
+
+func (ds *DocsStream) Next() (*document.Value, error) {
+	exp, err := ds.Parser.ParseDocument()
+	if err != nil {
+		return nil, err
+	}
+
+	v, err := exp.Eval(ds.env)
+	if err != nil {
+		return nil, err
+	}
+
+	return &v, nil
+}
+
+func ParseDocsStream(stream string) *DocsStream {
+	p := parser.NewParser(strings.NewReader(stream))
+	env := expr.NewEnvironment(document.NewFieldBuffer())
+
+	return &DocsStream{p, env}
+}
+
+func RequireStreamEq(t *testing.T, raw string, res *query.Result) {
+	t.Helper()
+	var count int
+	docs := ParseDocsStream(raw)
+	err := res.Iterate(func(d document.Document) error {
+		count++
+
+		want, err := docs.Next()
+		require.NoError(t, err)
+		got := document.NewDocumentValue(d)
+
+		eq, err := want.IsEqual(got)
+
+		require.NoError(t, err, "comparing %s and %s yielded an error: %e", want, got, err)
+		require.Truef(t, eq, "expected values to be equal\nwant %s to eq got %s", want.String(), got.String())
+		return err
+	})
+	require.NoError(t, err)
+
+	// there should not be any document left
+	total := count
+	var missing []string
+	for v, err := docs.Next(); err == nil; v, err = docs.Next() {
+		missing = append(missing, v.String())
+		total++
+	}
+
+	require.Equalf(t, total, count, "want %d documents but got %d instead.\nMissing documents:\n%s",
+		total,
+		count,
+		strings.Join(missing, "\n"))
+}