Repository: genjidb/genji
Pull request #410 (2021-06-06 15:50:40)

Normalize table and index storage
Table and index information is now stored the same way, with minimal data:
- `sql`: query used to create the table / index
- `table_name` / `index_name`
- `store_name`


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

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

```diff
@@            Coverage Diff             @@
##             main     #410      +/-   ##
==========================================
+ Coverage   68.39%   68.54%   +0.15%     
==========================================
  Files          83       83              
  Lines        7811     7837      +26     
==========================================
+ Hits         5342     5372      +30     
+ Misses       1795     1792       -3     
+ Partials      674      673       -1     
```


| [Impacted Files](https://codecov.io/gh/genjidb/genji/pull/410?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb) | Coverage Δ | |
|---|---|---|
| [internal/database/table.go](https://codecov.io/gh/genjidb/genji/pull/410/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb#diff-aW50ZXJuYWwvZGF0YWJhc2UvdGFibGUuZ28=) | `56.75% <ø> (+0.45%)` | :arrow_up: |
| [internal/database/catalog.go](https://codecov.io/gh/genjidb/genji/pull/410/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb#diff-aW50ZXJuYWwvZGF0YWJhc2UvY2F0YWxvZy5nbw==) | `85.61% <94.87%> (+0.47%)` | :arrow_up: |
| [internal/database/config.go](https://codecov.io/gh/genjidb/genji/pull/410/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb#diff-aW50ZXJuYWwvZGF0YWJhc2UvY29uZmlnLmdv) | `88.23% <100.00%> (-1.06%)` | :arrow_down: |
| [internal/database/constraint.go](https://codecov.io/gh/genjidb/genji/pull/410/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb#diff-aW50ZXJuYWwvZGF0YWJhc2UvY29uc3RyYWludC5nbw==) | `81.57% <100.00%> (+3.57%)` | :arrow_up: |
| [internal/database/index.go](https://codecov.io/gh/genjidb/genji/pull/410/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb#diff-aW50ZXJuYWwvZGF0YWJhc2UvaW5kZXguZ28=) | `65.82% <100.00%> (-0.18%)` | :arrow_down: |
| [internal/query/statement/create.go](https://codecov.io/gh/genjidb/genji/pull/410/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=genjidb#diff-aW50ZXJuYWwvcXVlcnkvc3RhdGVtZW50L2NyZWF0ZS5nbw==) | `84.00% <100.00%> (+2.18%)` | :arrow_up: |

------

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


Patch:
diff --git a/Makefile b/Makefile
index 8dd266d2..253de5b3 100644
--- a/Makefile
+++ b/Makefile
@@ -14,8 +14,8 @@ gen: $(NAME)
 
 test:
 	go test -cover -timeout=1m ./...
-	cd cmd/genji && go test -cover -timeout=1m
-	cd engine/badgerengine/ && go test -cover -timeout=1m
+	cd cmd/genji && go test -cover -timeout=1m ./...
+	cd engine/badgerengine/ && go test -cover -timeout=1m ./...
 
 testtinygo:
 	go test -tags=tinygo -cover -timeout=1m ./...
diff --git a/cmd/genji/commands/app.go b/cmd/genji/commands/app.go
index c2da9824..83538934 100644
--- a/cmd/genji/commands/app.go
+++ b/cmd/genji/commands/app.go
@@ -37,13 +37,13 @@ func NewApp() *cli.App {
 		useBolt := c.Bool("bolt")
 		useBadger := c.Bool("badger")
 		if useBolt && useBadger {
-			return cli.NewExitError("cannot use bolt and badger options at the same time", 2)
+			return cli.Exit("cannot use bolt and badger options at the same time", 2)
 		}
 
 		dbpath := c.Args().First()
 
 		if (useBolt || useBadger) && dbpath == "" {
-			return cli.NewExitError("db path required when using bolt or badger", 2)
+			return cli.Exit("db path required when using bolt or badger", 2)
 		}
 
 		engine := "memory"
diff --git a/cmd/genji/commands/restore.go b/cmd/genji/commands/restore.go
index bb44b9a1..23caf031 100644
--- a/cmd/genji/commands/restore.go
+++ b/cmd/genji/commands/restore.go
@@ -1,19 +1,10 @@
 package commands
 
 import (
-	"bufio"
-	"context"
 	"errors"
-	"fmt"
-	"io"
 	"os"
 
-	"github.com/dgraph-io/badger/v3"
-	"github.com/genjidb/genji"
 	"github.com/genjidb/genji/cmd/genji/dbutil"
-	"github.com/genjidb/genji/engine"
-	"github.com/genjidb/genji/engine/badgerengine"
-	"github.com/genjidb/genji/engine/boltengine"
 	"github.com/urfave/cli/v2"
 )
 
@@ -65,60 +56,3 @@ func NewRestoreCommand() (cmd *cli.Command) {
 		},
 	}
 }
-
-func executeRestore(ctx context.Context, r io.Reader, e, dbPath string) error {
-	var (
-		ng  engine.Engine
-		err error
-	)
-
-	switch e {
-	case "bolt":
-		ng, err = boltengine.NewEngine(dbPath, 0660, nil)
-	case "badger":
-		ng, err = badgerengine.NewEngine(badger.DefaultOptions(dbPath).WithLogger(nil))
-	default:
-		return fmt.Errorf(`engine should be "bolt" or "badger, got %q`, e)
-	}
-	if err != nil {
-		return err
-	}
-
-	db, err := genji.New(ctx, ng)
-	if err != nil {
-		return err
-	}
-	db = db.WithContext(ctx)
-	defer db.Close()
-
-	scanner := bufio.NewScanner(r)
-
-	// Every query ends with a semicolon.
-	scanner.Split(func(data []byte, atEOF bool) (advance int, token []byte, err error) {
-		for i := 0; i < len(data); i++ {
-			if data[i] == ';' {
-				return i + 1, data[:i], nil
-			}
-		}
-
-		if !atEOF {
-			return 0, nil, nil
-		}
-
-		return 0, data, bufio.ErrFinalToken
-	})
-
-	for scanner.Scan() {
-		q := scanner.Text()
-		if err := db.Exec(q); err != nil {
-			_ = db.Exec("ROLLBACK")
-			return err
-		}
-	}
-
-	if err := scanner.Err(); err != nil {
-		return err
-	}
-
-	return nil
-}
diff --git a/cmd/genji/dbutil/dump.go b/cmd/genji/dbutil/dump.go
index 3a53ddfa..78150374 100644
--- a/cmd/genji/dbutil/dump.go
+++ b/cmd/genji/dbutil/dump.go
@@ -7,6 +7,9 @@ import (
 
 	"github.com/genjidb/genji"
 	"github.com/genjidb/genji/document"
+	"github.com/genjidb/genji/internal/database"
+	"github.com/genjidb/genji/internal/query/statement"
+	"github.com/genjidb/genji/internal/sql/parser"
 	"go.uber.org/multierr"
 )
 
@@ -23,20 +26,8 @@ func Dump(ctx context.Context, db *genji.DB, w io.Writer, tables ...string) erro
 		return err
 	}
 
-	query := "SELECT table_name FROM __genji_tables"
-	if len(tables) > 0 {
-		query += " WHERE table_name IN ?"
-	}
-
-	res, err := tx.Query(query, tables)
-	if err != nil {
-		_, er := fmt.Fprintln(w, "ROLLBACK;")
-		return multierr.Append(err, er)
-	}
-	defer res.Close()
-
 	i := 0
-	err = res.Iterate(func(d document.Document) error {
+	err = queryTables(tx, tables, func(query string, ti *database.TableInfo) error {
 		// Blank separation between tables.
 		if i > 0 {
 			if _, err := fmt.Fprintln(w, ""); err != nil {
@@ -45,13 +36,7 @@ func Dump(ctx context.Context, db *genji.DB, w io.Writer, tables ...string) erro
 		}
 		i++
 
-		// Get table name.
-		var tableName string
-		if err := document.Scan(d, &tableName); err != nil {
-			return err
-		}
-
-		return dumpTable(tx, w, tableName)
+		return dumpTable(tx, w, query, ti.TableName)
 	})
 	if err != nil {
 		_, er := fmt.Fprintln(w, "ROLLBACK;")
@@ -62,10 +47,38 @@ func Dump(ctx context.Context, db *genji.DB, w io.Writer, tables ...string) erro
 	return err
 }
 
+func queryTables(tx *genji.Tx, tables []string, fn func(string, *database.TableInfo) error) error {
+	query := "SELECT sql FROM __genji_tables"
+	if len(tables) > 0 {
+		query += " WHERE table_name IN ?"
+	}
+
+	res, err := tx.Query(query, tables)
+	if err != nil {
+		return err
+	}
+	defer res.Close()
+
+	return res.Iterate(func(d document.Document) error {
+		// Get table name.
+		var query string
+		if err := document.Scan(d, &query); err != nil {
+			return err
+		}
+
+		q, err := parser.ParseQuery(query)
+		if err != nil {
+			return err
+		}
+
+		return fn(query, &q.Statements[0].(*statement.CreateTableStmt).Info)
+	})
+}
+
 // dumpTable displays the content of the given table as SQL statements.
-func dumpTable(tx *genji.Tx, w io.Writer, tableName string) error {
+func dumpTable(tx *genji.Tx, w io.Writer, query, tableName string) error {
 	// Dump schema first.
-	if err := dumpSchema(tx, w, tableName); err != nil {
+	if err := dumpSchema(tx, w, query, tableName); err != nil {
 		return err
 	}
 
@@ -101,19 +114,8 @@ func DumpSchema(ctx context.Context, db *genji.DB, w io.Writer, tables ...string
 	}
 	defer tx.Rollback()
 
-	query := "SELECT table_name FROM __genji_tables"
-	if len(tables) > 0 {
-		query += " WHERE table_name IN ?"
-	}
-
-	res, err := tx.Query(query, tables)
-	if err != nil {
-		return err
-	}
-	defer res.Close()
-
 	i := 0
-	return res.Iterate(func(d document.Document) error {
+	return queryTables(tx, tables, func(query string, ti *database.TableInfo) error {
 		// Blank separation between tables.
 		if i > 0 {
 			if _, err := fmt.Fprintln(w, ""); err != nil {
@@ -122,40 +124,22 @@ func DumpSchema(ctx context.Context, db *genji.DB, w io.Writer, tables ...string
 		}
 		i++
 
-		// Get table name.
-		var tableName string
-		if err := document.Scan(d, &tableName); err != nil {
-			return err
-		}
-
-		return dumpSchema(tx, w, tableName)
+		return dumpSchema(tx, w, query, ti.TableName)
 	})
 }
 
 // dumpSchema displays the schema of the given table as SQL statements.
-func dumpSchema(tx *genji.Tx, w io.Writer, tableName string) error {
-	d, err := tx.QueryDocument("SELECT sql FROM __genji_tables WHERE table_name = ?", tableName)
-	if err != nil {
-		return err
-	}
-
-	var tableSchema string
-	err = document.Scan(d, &tableSchema)
-	if err != nil {
-		return err
-	}
-
-	_, err = fmt.Fprintf(w, "%s;\n", tableSchema)
+func dumpSchema(tx *genji.Tx, w io.Writer, query string, tableName string) error {
+	_, err := fmt.Fprintf(w, "%s;\n", query)
 	if err != nil {
 		return err
 	}
 
 	// Indexes statements.
-	res, err := tx.Query("SELECT sql FROM __genji_indexes WHERE table_name = ?", tableName)
+	res, err := tx.Query("SELECT sql FROM __genji_indexes WHERE sql LIKE ?", "%ON "+tableName+" %")
 	if err != nil {
 		return err
 	}
-
 	defer res.Close()
 
 	return res.Iterate(func(d document.Document) error {
@@ -174,23 +158,31 @@ func dumpSchema(tx *genji.Tx, w io.Writer, tableName string) error {
 func ListIndexes(ctx context.Context, db *genji.DB, tableName string) ([]string, error) {
 	var listName []string
 	err := db.View(func(tx *genji.Tx) error {
-		q := "SELECT index_name FROM __genji_indexes"
+		q := "SELECT sql FROM __genji_indexes"
+		var param string
 		if tableName != "" {
-			q += " WHERE table_name = ?"
+			q += " WHERE sql LIKE ?"
+			param = "%ON " + tableName + " %"
 		}
-		res, err := tx.Query(q, tableName)
+		res, err := tx.Query(q, param)
 		if err != nil {
 			return err
 		}
 		defer res.Close()
 
 		return res.Iterate(func(d document.Document) error {
-			var name string
-			err = document.Scan(d, &name)
+			var query string
+			err = document.Scan(d, &query)
+			if err != nil {
+				return err
+			}
+
+			q, err := parser.ParseQuery(query)
 			if err != nil {
 				return err
 			}
-			listName = append(listName, name)
+
+			listName = append(listName, q.Statements[0].(*statement.CreateIndexStmt).Info.IndexName)
 			return nil
 		})
 	})
diff --git a/cmd/genji/dbutil/insert.go b/cmd/genji/dbutil/insert.go
index b53fac8e..36269a9d 100644
--- a/cmd/genji/dbutil/insert.go
+++ b/cmd/genji/dbutil/insert.go
@@ -53,7 +53,7 @@ func InsertJSON(db *genji.DB, table string, r io.Reader) error {
 		}
 
 		dec := json.NewDecoder(rd)
-		t, err := dec.Token()
+		_, err := dec.Token()
 		if err != nil {
 			return err
 		}
@@ -70,7 +70,7 @@ func InsertJSON(db *genji.DB, table string, r io.Reader) error {
 			}
 		}
 
-		t, err = dec.Token()
+		t, err := dec.Token()
 		if err != nil {
 			return err
 		}
diff --git a/go.mod b/go.mod
index 50b0125c..306f85db 100644
--- a/go.mod
+++ b/go.mod
@@ -4,14 +4,11 @@ go 1.16
 
 require (
 	github.com/buger/jsonparser v1.1.1
-	github.com/davecgh/go-spew v1.1.1 // indirect
 	github.com/google/btree v1.0.1
 	github.com/google/go-cmp v0.5.5
-	github.com/kr/pretty v0.1.0 // indirect
 	github.com/stretchr/testify v1.7.0
 	github.com/vmihailenco/msgpack/v5 v5.3.2
 	go.etcd.io/bbolt v1.3.5
 	golang.org/x/sync v0.0.0-20210220032951-036812b2e83c
 	golang.org/x/tools v0.1.1
-	gopkg.in/check.v1 v1.0.0-20180628173108-788fd7840127 // indirect
 )
diff --git a/go.sum b/go.sum
index 7252b59f..16e1b4b9 100644
--- a/go.sum
+++ b/go.sum
@@ -1,17 +1,11 @@
 github.com/buger/jsonparser v1.1.1 h1:2PnMjfWD7wBILjqQbt530v576A/cAbQvEW9gGIpYMUs=
 github.com/buger/jsonparser v1.1.1/go.mod h1:6RYKKt7H4d4+iWqouImQ9R2FZql3VbhNgx27UK13J/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/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c=
-github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
 github.com/google/btree v1.0.1 h1:gK4Kx5IaGY9CD5sPJ36FHiBJ6ZXl0kilRiiCj+jdYp4=
 github.com/google/btree v1.0.1/go.mod h1:xXMiIv4Fb/0kKde4SpL7qlzvu5cMJDRkFDxJfI9uaxA=
 github.com/google/go-cmp v0.5.5 h1:Khx7svrCpmxxtHBq5j2mp/xVjsi8hQMfNLvJFAlrGgU=
 github.com/google/go-cmp v0.5.5/go.mod h1:v8dTdLbMG2kIc/vJvl+f65V22dbkXbowE6jgT/gNBxE=
-github.com/kr/pretty v0.1.0 h1:L/CwN0zerZDmRFUapSPitk6f+Q3+0za1rQkzVuMiMFI=
-github.com/kr/pretty v0.1.0/go.mod h1:dAy3ld7l9f0ibDNOQOHHMYYIIbhfbHSm3C4ZsoJORNo=
-github.com/kr/pty v1.1.1/go.mod h1:pFQYn66WHrOpPYNljwOMqo10TkYh1fy3cYio2l3bCsQ=
-github.com/kr/text v0.1.0 h1:45sCR5RtlFHMR4UwH9sdQ5TC8v0qDQCHnXt+kaKSTVE=
-github.com/kr/text v0.1.0/go.mod h1:4Jbv+DJW3UT/LiOwJeYQe1efqtUx/iVham/4vfdArNI=
 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=
@@ -54,8 +48,7 @@ golang.org/x/xerrors v0.0.0-20191011141410-1b5146add898/go.mod h1:I/5z698sn9Ka8T
 golang.org/x/xerrors v0.0.0-20191204190536-9bdfabe68543/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/check.v1 v1.0.0-20180628173108-788fd7840127 h1:qIbj1fsPNlZgppZ+VLlY7N33q108Sa+fhmuc+sWQYwY=
-gopkg.in/check.v1 v1.0.0-20180628173108-788fd7840127/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/internal/database/catalog.go b/internal/database/catalog.go
index 46f19e4d..2586a549 100644
--- a/internal/database/catalog.go
+++ b/internal/database/catalog.go
@@ -13,7 +13,8 @@ import (
 )
 
 const (
-	storePrefix        = 't'
+	tableStorePrefix   = 't'
+	indexStorePrefix   = 'i'
 	internalPrefix     = "__genji_"
 	tableInfoStoreName = internalPrefix + "tables"
 	indexInfoStoreName = internalPrefix + "indexes"
@@ -668,7 +669,16 @@ func GetTableStore(tx *Transaction) *Table {
 				{
 					Path: document.Path{
 						document.PathFragment{
-							FieldName: "statements",
+							FieldName: "table_name",
+						},
+					},
+					Type:         document.TextValue,
+					IsPrimaryKey: true,
+				},
+				{
+					Path: document.Path{
+						document.PathFragment{
+							FieldName: "sql",
 						},
 					},
 					Type: document.TextValue,
@@ -676,11 +686,10 @@ func GetTableStore(tx *Transaction) *Table {
 				{
 					Path: document.Path{
 						document.PathFragment{
-							FieldName: "table_name",
+							FieldName: "store_name",
 						},
 					},
-					Type:         document.TextValue,
-					IsPrimaryKey: true,
+					Type: document.BlobValue,
 				},
 			},
 		},
@@ -703,7 +712,16 @@ func GetIndexStore(tx *Transaction) *Table {
 				{
 					Path: document.Path{
 						document.PathFragment{
-							FieldName: "statements",
+							FieldName: "index_name",
+						},
+					},
+					Type:         document.TextValue,
+					IsPrimaryKey: true,
+				},
+				{
+					Path: document.Path{
+						document.PathFragment{
+							FieldName: "sql",
 						},
 					},
 					Type: document.TextValue,
@@ -711,11 +729,10 @@ func GetIndexStore(tx *Transaction) *Table {
 				{
 					Path: document.Path{
 						document.PathFragment{
-							FieldName: "index_name",
+							FieldName: "store_name",
 						},
 					},
-					Type:         document.TextValue,
-					IsPrimaryKey: true,
+					Type: document.BlobValue,
 				},
 			},
 		},
@@ -733,7 +750,7 @@ func insertTable(tx *Transaction, tableName string, info *TableInfo) error {
 			return err
 		}
 		buf := make([]byte, binary.MaxVarintLen64+1)
-		buf[0] = storePrefix
+		buf[0] = tableStorePrefix
 		n := binary.PutUvarint(buf[1:], seq)
 		info.StoreName = buf[:n+1]
 	}
@@ -763,17 +780,36 @@ func replaceTable(tx *Transaction, tableName string, info *TableInfo) error {
 func insertIndex(tx *Transaction, info *IndexInfo) error {
 	tb := GetIndexStore(tx)
 
+	var seq uint64
+	var err error
 	// auto-generate index name
 	if info.IndexName == "" {
-		seq, err := tb.Store.NextSequence()
+		seq, err = tb.Store.NextSequence()
 		if err != nil {
 			return err
 		}
 
-		info.IndexName = stringutil.Sprintf("%sautoindex_%s_%d", internalPrefix, info.TableName, seq)
+		if info.FromConstraint {
+			info.IndexName = stringutil.Sprintf("%sautoindexc_%s_%d", internalPrefix, info.TableName, seq)
+		} else {
+			info.IndexName = stringutil.Sprintf("%sautoindex_%s_%d", internalPrefix, info.TableName, seq)
+		}
 	}
 
-	_, err := tb.Insert(info.ToDocument())
+	// reuse the same sequence if it exists
+	if seq == 0 {
+		seq, err = tb.Store.NextSequence()
+		if err != nil {
+			return err
+		}
+	}
+
+	buf := make([]byte, binary.MaxVarintLen64+1)
+	buf[0] = byte(indexStorePrefix)
+	n := binary.PutUvarint(buf[1:], seq)
+	info.StoreName = buf[:n+1]
+
+	_, err = tb.Insert(info.ToDocument())
 	if err == errs.ErrDuplicateDocument {
 		return errs.ErrIndexAlreadyExists
 	}
diff --git a/internal/database/config.go b/internal/database/config.go
index d86ba064..9761533c 100644
--- a/internal/database/config.go
+++ b/internal/database/config.go
@@ -57,30 +57,7 @@ func (ti *TableInfo) String() string {
 			s.WriteString(", ")
 		}
 
-		// Path
-		s.WriteString(fc.Path.String())
-
-		// Type
-		if fc.Type != 0 {
-			stringutil.Fprintf(&s, " %s", strings.ToUpper(fc.Type.String()))
-		}
-
-		// Not null
-		if fc.IsNotNull {
-			s.WriteString(" NOT NULL")
-		}
-
-		// Default value
-		if fc.HasDefaultValue() {
-			stringutil.Fprintf(&s, " DEFAULT %s", fc.DefaultValue.String())
-		}
-
-		// Primary key
-		if fc.IsPrimaryKey {
-			s.WriteString(" PRIMARY KEY")
-		}
-
-		// Unique must not be written as it an index is already created for it
+		s.WriteString(fc.String())
 	}
 
 	if len(ti.FieldConstraints) > 0 {
@@ -101,6 +78,8 @@ func (ti *TableInfo) Clone() *TableInfo {
 // IndexInfo holds the configuration of an index.
 type IndexInfo struct {
 	TableName string
+	// name of the store associated with the index.
+	StoreName []byte
 	IndexName string
 	Paths     []document.Path
 
@@ -109,6 +88,10 @@ type IndexInfo struct {
 
 	// If set, the index is typed and only accepts values of those types.
 	Types []document.ValueType
+
+	// If set, this index has been created from a table constraint
+	// i.e CREATE TABLE tbl(a INT UNIQUE)
+	FromConstraint bool
 }
 
 // ToDocument creates a document from an IndexConfig.
@@ -116,7 +99,7 @@ func (i *IndexInfo) ToDocument() document.Document {
 	buf := document.NewFieldBuffer()
 	buf.Add("sql", document.NewTextValue(i.String()))
 	buf.Add("index_name", document.NewTextValue(i.IndexName))
-	buf.Add("table_name", document.NewTextValue(i.TableName))
+	buf.Add("store_name", document.NewBlobValue(i.StoreName))
 
 	return buf
 }
@@ -125,7 +108,12 @@ func (i *IndexInfo) ToDocument() document.Document {
 func (i *IndexInfo) String() string {
 	var s strings.Builder
 
-	stringutil.Fprintf(&s, "CREATE INDEX %s ON %s (", i.IndexName, i.TableName)
+	s.WriteString("CREATE ")
+	if i.Unique {
+		s.WriteString("UNIQUE ")
+	}
+
+	stringutil.Fprintf(&s, "INDEX %s ON %s (", i.IndexName, i.TableName)
 
 	for i, p := range i.Paths {
 		if i > 0 {
diff --git a/internal/database/constraint.go b/internal/database/constraint.go
index 00a9b34b..f3c85c59 100644
--- a/internal/database/constraint.go
+++ b/internal/database/constraint.go
@@ -22,7 +22,7 @@ type FieldConstraint struct {
 	Type         document.ValueType
 	IsPrimaryKey bool
 	IsNotNull    bool
-	IsUnique     bool // not stored, only set during table creation
+	IsUnique     bool
 	DefaultValue document.Value
 	IsInferred   bool
 	InferredBy   []document.Path
@@ -65,14 +65,20 @@ func (f *FieldConstraint) String() string {
 
 	s.WriteString(f.Path.String())
 	s.WriteString(" ")
-	s.WriteString(f.Type.String())
+	s.WriteString(strings.ToUpper(f.Type.String()))
+
 	if f.IsNotNull {
 		s.WriteString(" NOT NULL")
 	}
+
 	if f.IsPrimaryKey {
 		s.WriteString(" PRIMARY KEY")
 	}
 
+	if f.IsUnique {
+		s.WriteString(" UNIQUE")
+	}
+
 	if f.HasDefaultValue() {
 		s.WriteString(" DEFAULT ")
 		s.WriteString(f.DefaultValue.String())
diff --git a/internal/database/index.go b/internal/database/index.go
index 594a5672..72e731b6 100644
--- a/internal/database/index.go
+++ b/internal/database/index.go
@@ -11,11 +11,6 @@ import (
 	"github.com/genjidb/genji/internal/stringutil"
 )
 
-const (
-	// indexStorePrefix is the prefix used to name the index stores.
-	indexStorePrefix = "i"
-)
-
 var (
 	// ErrIndexDuplicateValue is returned when a value is already associated with a key
 	ErrIndexDuplicateValue = errors.New("duplicate value")
@@ -33,8 +28,26 @@ var (
 type Index struct {
 	Info *IndexInfo
 
-	tx        engine.Transaction
-	storeName []byte
+	tx engine.Transaction
+}
+
+// NewIndex creates an index that associates values with a list of keys.
+func NewIndex(tx engine.Transaction, idxName string, opts *IndexInfo) *Index {
+	if opts == nil {
+		opts = &IndexInfo{
+			Types: []document.ValueType{document.AnyType},
+		}
+	}
+
+	// if no types are provided, it implies that it's an index for single untyped values
+	if opts.Types == nil {
+		opts.Types = []document.ValueType{document.AnyType}
+	}
+
+	return &Index{
+		tx:   tx,
+		Info: opts,
+	}
 }
 
 // indexValueEncoder encodes a field based on its type; if a type is provided,
@@ -93,26 +106,6 @@ func (e *indexValueEncoder) EncodeValue(v document.Value) error {
 	return err
 }
 
-// NewIndex creates an index that associates values with a list of keys.
-func NewIndex(tx engine.Transaction, idxName string, opts *IndexInfo) *Index {
-	if opts == nil {
-		opts = &IndexInfo{
-			Types: []document.ValueType{document.AnyType},
-		}
-	}
-
-	// if no types are provided, it implies that it's an index for single untyped values
-	if opts.Types == nil {
-		opts.Types = []document.ValueType{document.AnyType}
-	}
-
-	return &Index{
-		tx:        tx,
-		storeName: append([]byte(indexStorePrefix), idxName...),
-		Info:      opts,
-	}
-}
-
 var errStop = errors.New("stop")
 
 // IsComposite returns true if the index is defined to operate on at least more than one value.
@@ -148,7 +141,7 @@ func (idx *Index) Set(vs []document.Value, k []byte) error {
 		}
 	}
 
-	st, err := getOrCreateStore(idx.tx, idx.storeName)
+	st, err := getOrCreateStore(idx.tx, idx.Info.StoreName)
 	if err != nil {
 		return nil
 	}
@@ -205,7 +198,7 @@ func (idx *Index) Exists(vs []document.Value) (bool, []byte, error) {
 		return false, nil, stringutil.Errorf("required arity of %d", len(idx.Info.Types))
 	}
 
-	st, err := idx.tx.GetStore(idx.storeName)
+	st, err := idx.tx.GetStore(idx.Info.StoreName)
 	if err != nil {
 		if err == engine.ErrStoreNotFound {
 			return false, nil, nil
@@ -236,7 +229,7 @@ func (idx *Index) Exists(vs []document.Value) (bool, []byte, error) {
 
 // Delete all the references to the key from the index.
 func (idx *Index) Delete(vs []document.Value, k []byte) error {
-	st, err := getOrCreateStore(idx.tx, idx.storeName)
+	st, err := getOrCreateStore(idx.tx, idx.Info.StoreName)
 	if err != nil {
 		return nil
 	}
@@ -354,7 +347,7 @@ func (idx *Index) iterateOnStore(pivot Pivot, reverse bool, fn func(val, key []b
 		}
 	}
 
-	st, err := idx.tx.GetStore(idx.storeName)
+	st, err := idx.tx.GetStore(idx.Info.StoreName)
 	if err != nil && err != engine.ErrStoreNotFound {
 		return err
 	}
@@ -386,7 +379,7 @@ func (idx *Index) iterateOnStore(pivot Pivot, reverse bool, fn func(val, key []b
 
 // Truncate deletes all the index data.
 func (idx *Index) Truncate() error {
-	err := idx.tx.DropStore(idx.storeName)
+	err := idx.tx.DropStore(idx.Info.StoreName)
 	if err != nil && err != engine.ErrStoreNotFound {
 		return err
 	}
diff --git a/internal/database/table.go b/internal/database/table.go
index 5b3c16be..e86d6978 100644
--- a/internal/database/table.go
+++ b/internal/database/table.go
@@ -23,7 +23,7 @@ type Table struct {
 	Info *TableInfo
 	// List of Indexes of this table.
 	// May not represent the most up to date data.
-	// Always get a fresh Table instance relying on this field.
+	// Always get a fresh Table instance before relying on this field.
 	Indexes Indexes
 }
 
diff --git a/internal/query/statement/create.go b/internal/query/statement/create.go
index 9be63b85..e68c2ad6 100644
--- a/internal/query/statement/create.go
+++ b/internal/query/statement/create.go
@@ -31,10 +31,11 @@ func (stmt *CreateTableStmt) Run(tx *database.Transaction, args []expr.Param) (R
 	for _, fc := range stmt.Info.FieldConstraints {
 		if fc.IsUnique {
 			err = tx.Catalog.CreateIndex(tx, &database.IndexInfo{
-				TableName: stmt.Info.TableName,
-				Paths:     []document.Path{fc.Path},
-				Unique:    true,
-				Types:     []document.ValueType{fc.Type},
+				TableName:      stmt.Info.TableName,
+				Paths:          []document.Path{fc.Path},
+				Unique:         true,
+				Types:          []document.ValueType{fc.Type},
+				FromConstraint: true,
 			})
 			if err != nil {
 				return res, err
@@ -66,6 +67,9 @@ func (stmt *CreateIndexStmt) Run(tx *database.Transaction, args []expr.Param) (R
 	if stmt.IfNotExists && err == errs.ErrIndexAlreadyExists {
 		return res, nil
 	}
+	if err != nil {
+		return res, err
+	}
 
 	err = tx.Catalog.ReIndex(tx, stmt.Info.IndexName)
 	return res, err


Test patch:
diff --git a/cmd/genji/dbutil/dump_test.go b/cmd/genji/dbutil/dump_test.go
index 12854d2c..e678dc46 100644
--- a/cmd/genji/dbutil/dump_test.go
+++ b/cmd/genji/dbutil/dump_test.go
@@ -16,7 +16,7 @@ func TestDump(t *testing.T) {
 		tables []string
 	}{
 		{"All tables", nil},
-		{"Selection of tables", []string{"test", "foo"}},
+		{"Selection of tables", []string{"tblA", "foo"}},
 	}
 
 	for _, tt := range tests {
diff --git a/internal/database/catalog_test.go b/internal/database/catalog_test.go
index 3306f869..56dc8064 100644
--- a/internal/database/catalog_test.go
+++ b/internal/database/catalog_test.go
@@ -679,22 +679,25 @@ func TestReadOnlyTables(t *testing.T) {
 	doc, err := db.QueryDocument(`CREATE TABLE foo (a int, b[3].c double unique); SELECT * FROM __genji_tables`)
 	require.NoError(t, err)
 
-	testutil.RequireDocJSONEq(t, doc, `{"sql":"CREATE TABLE foo (a INTEGER, b[3].c DOUBLE)", "store_name":"dAE=", "table_name":"foo"}`)
+	testutil.RequireDocJSONEq(t, doc, `{"sql":"CREATE TABLE foo (a INTEGER, b[3].c DOUBLE UNIQUE)", "store_name":"dAE=", "table_name":"foo"}`)
 
 	res, err := db.Query(`CREATE INDEX idx_foo_a ON foo(a); SELECT * FROM __genji_indexes`)
 	require.NoError(t, err)
 	defer res.Close()
 
-	testutil.RequireStreamEq(t, `
-		{
-			"sql": "CREATE INDEX __genji_autoindex_foo_1 ON foo (b[3].c)",
-			"index_name": "__genji_autoindex_foo_1",
-			"table_name": "foo"
+	var i int
+	err = res.Iterate(func(d document.Document) error {
+		switch i {
+		case 0:
+			testutil.RequireDocJSONEq(t, d, `{"index_name":"__genji_autoindexc_foo_1", "sql": "CREATE UNIQUE INDEX __genji_autoindexc_foo_1 ON foo (b[3].c)", "store_name": "aQE="}`)
+		case 1:
+			testutil.RequireDocJSONEq(t, d, `{"index_name":"idx_foo_a", "sql": "CREATE INDEX idx_foo_a ON foo (a)", "store_name":"aQI="}`)
+		default:
+			t.Fatalf("count should be 1, got %d", i)
 		}
-		{
-			"sql": "CREATE INDEX idx_foo_a ON foo (a)",
-			"index_name": "idx_foo_a",
-			"table_name": "foo"
-		}
-	`, res)
+
+		i++
+		return nil
+	})
+	require.NoError(t, err)
 }
diff --git a/internal/query/statement/create_test.go b/internal/query/statement/create_test.go
index 38d96071..e2553c74 100644
--- a/internal/query/statement/create_test.go
+++ b/internal/query/statement/create_test.go
@@ -253,17 +253,17 @@ func TestCreateTable(t *testing.T) {
 				IsUnique: true,
 			}, tb.Info.FieldConstraints[2])
 
-			idx, err := tx.Catalog.GetIndex(tx, "__genji_autoindex_test_1")
+			idx, err := tx.Catalog.GetIndex(tx, "__genji_autoindexc_test_1")
 			require.NoError(t, err)
 			require.Equal(t, document.IntegerValue, idx.Info.Types[0])
 			require.True(t, idx.Info.Unique)
 
-			idx, err = tx.Catalog.GetIndex(tx, "__genji_autoindex_test_2")
+			idx, err = tx.Catalog.GetIndex(tx, "__genji_autoindexc_test_2")
 			require.NoError(t, err)
 			require.Equal(t, document.DoubleValue, idx.Info.Types[0])
 			require.True(t, idx.Info.Unique)
 
-			idx, err = tx.Catalog.GetIndex(tx, "__genji_autoindex_test_3")
+			idx, err = tx.Catalog.GetIndex(tx, "__genji_autoindexc_test_3")
 			require.NoError(t, err)
 			require.Zero(t, idx.Info.Types[0])
 			require.True(t, idx.Info.Unique)
@@ -280,6 +280,7 @@ func TestCreateIndex(t *testing.T) {
 	}{
 		{"Basic", "CREATE INDEX idx ON test (foo)", false},
 		{"If not exists", "CREATE INDEX IF NOT EXISTS idx ON test (foo.bar)", false},
+		{"Duplicate", "CREATE INDEX idx ON test (foo.bar);CREATE INDEX idx ON test (foo.bar)", true},
 		{"Unique", "CREATE UNIQUE INDEX IF NOT EXISTS idx ON test (foo[1])", false},
 		{"No name", "CREATE UNIQUE INDEX ON test (foo[1])", false},
 		{"No name if not exists", "CREATE UNIQUE INDEX IF NOT EXISTS ON test (foo[1])", true},
diff --git a/open_test.go b/open_test.go
index 1b43a084..24211830 100644
--- a/open_test.go
+++ b/open_test.go
@@ -22,7 +22,7 @@ func TestOpen(t *testing.T) {
 	require.NoError(t, err)
 
 	err = db.Exec(`
-		CREATE TABLE tableA (a INTEGER NOT NULL, b.c[0].d DOUBLE UNIQUE PRIMARY KEY);
+		CREATE TABLE tableA (a INTEGER UNIQUE NOT NULL, b.c[0].d DOUBLE PRIMARY KEY);
 		CREATE TABLE tableB (a TEXT NOT NULL DEFAULT 'hello', PRIMARY KEY (a));
 		CREATE TABLE tableC;
 
@@ -47,7 +47,7 @@ func TestOpen(t *testing.T) {
 		count++
 		if count == 1 {
 			fb := testutil.MakeDocument(t, `{
-				"sql": "CREATE TABLE tableA (a INTEGER NOT NULL, b.c[0].d DOUBLE PRIMARY KEY)",
+				"sql": "CREATE TABLE tableA (a INTEGER NOT NULL UNIQUE, b.c[0].d DOUBLE PRIMARY KEY)",
 				"table_name": "tableA"
 			}`).(*document.FieldBuffer)
 
@@ -58,7 +58,7 @@ func TestOpen(t *testing.T) {
 
 		if count == 2 {
 			fb := testutil.MakeDocument(t, `{
-				"sql": "CREATE TABLE tableB (a TEXT NOT NULL DEFAULT \"hello\" PRIMARY KEY)",
+				"sql": "CREATE TABLE tableB (a TEXT NOT NULL PRIMARY KEY DEFAULT \"hello\")",
 				"table_name": "tableB"
 			}`).(*document.FieldBuffer)
 
@@ -89,13 +89,13 @@ func TestOpen(t *testing.T) {
 	err = res2.Iterate(func(d document.Document) error {
 		count++
 		if count == 1 {
-			fb := testutil.MakeDocument(t, `{
-				"sql": "CREATE INDEX __genji_autoindex_tableA_1 ON tableA (b.c[0].d)",
-				"index_name": "__genji_autoindex_tableA_1",
-				"table_name": "tableA"
-			}`).(*document.FieldBuffer)
+			j := `{
+				"sql": "CREATE UNIQUE INDEX __genji_autoindexc_tableA_1 ON tableA (a)",
+				"index_name": "__genji_autoindexc_tableA_1",
+				"store_name":"aQE="
+			}`
 
-			testutil.RequireDocEqual(t, fb, d)
+			testutil.RequireDocJSONEq(t, d, j)
 			return nil
 		}