Repository: genjidb/genji
Pull request #417 (2021-06-28 18:05:56)

Create sequence for docids
Each table with no primary key now creates a sequence to manage docids


Patch:
diff --git a/cmd/genji/dbutil/dump.go b/cmd/genji/dbutil/dump.go
index 1f80c686..eb5a2a2b 100644
--- a/cmd/genji/dbutil/dump.go
+++ b/cmd/genji/dbutil/dump.go
@@ -107,7 +107,7 @@ func dumpSchema(tx *genji.Tx, w io.Writer, query string, tableName string) error
 	// Indexes statements.
 	res, err := tx.Query(`
 		SELECT sql FROM __genji_catalog WHERE 
-			type = 'index' AND constraint_path IS NULL AND table_name = ?
+			type IN ('index', 'sequence') AND constraint_path IS NULL AND table_name = ?
 	`, tableName)
 	if err != nil {
 		return err
@@ -115,14 +115,14 @@ func dumpSchema(tx *genji.Tx, w io.Writer, query string, tableName string) error
 	defer res.Close()
 
 	return res.Iterate(func(d document.Document) error {
-		var indexQuery string
+		var q string
 
-		err = document.Scan(d, &indexQuery)
+		err = document.Scan(d, &q)
 		if err != nil {
 			return err
 		}
 
-		_, err = fmt.Fprintf(w, "%s;\n", indexQuery)
+		_, err = fmt.Fprintf(w, "%s;\n", q)
 		return err
 	})
 }
diff --git a/cmd/genji/dbutil/schema.go b/cmd/genji/dbutil/schema.go
index a6072fa6..8895cfbb 100644
--- a/cmd/genji/dbutil/schema.go
+++ b/cmd/genji/dbutil/schema.go
@@ -10,7 +10,7 @@ import (
 )
 
 func QueryTables(tx *genji.Tx, tables []string, fn func(name, query string) error) error {
-	query := "SELECT name, sql FROM __genji_catalog WHERE type = 'table'"
+	query := "SELECT name, sql FROM __genji_catalog WHERE type = 'table' AND name != '__genji_sequence'"
 	if len(tables) > 0 {
 		query += " AND name IN ?"
 	}
diff --git a/cmd/genji/shell/command.go b/cmd/genji/shell/command.go
index 72722a0e..82ee1388 100644
--- a/cmd/genji/shell/command.go
+++ b/cmd/genji/shell/command.go
@@ -102,7 +102,7 @@ func runHelpCmd() error {
 
 // runTablesCmd displays all tables.
 func runTablesCmd(db *genji.DB, w io.Writer) error {
-	res, err := db.Query("SELECT name FROM __genji_catalog WHERE type = 'table'")
+	res, err := db.Query("SELECT name FROM __genji_catalog WHERE type = 'table' AND name != '__genji_sequence'")
 	if err != nil {
 		return err
 	}
diff --git a/internal/catalog/cache.go b/internal/catalog/cache.go
index 783ed9e5..44222c16 100644
--- a/internal/catalog/cache.go
+++ b/internal/catalog/cache.go
@@ -385,18 +385,33 @@ func (c *catalogCache) AddSequence(tx *database.Transaction, seq *database.Seque
 	c.mu.Lock()
 	defer c.mu.Unlock()
 
-	// checking if sequence exists with the same name
-	if _, ok := c.sequences[seq.Info.Name]; ok {
-		return errs.AlreadyExistsError{Name: seq.Info.Name}
-	}
+	// auto-generate sequence name if needed
+	if seq.Info.Name == "" {
+		if seq.Info.Owner.TableName == "" {
+			return errors.New("sequence name not provided")
+		}
 
-	// checking if table exists with the same name
-	if _, ok := c.tables[seq.Info.Name]; ok {
-		return errs.AlreadyExistsError{Name: seq.Info.Name}
+		var sb strings.Builder
+		sb.WriteString(seq.Info.Owner.TableName)
+		if seq.Info.Owner.Path != nil {
+			sb.WriteString("_")
+			sb.WriteString(seq.Info.Owner.Path.String())
+		}
+		sb.WriteString("_seq")
+		seq.Info.Name = sb.String()
+
+		i := 0
+		for {
+			if !c.objectExists(seq.Info.Name) {
+				break
+			}
+
+			i++
+			seq.Info.Name = stringutil.Sprintf("%s%d", sb.String(), i)
+		}
 	}
 
-	// checking if index exists with the same name
-	if _, ok := c.indexes[seq.Info.Name]; ok {
+	if c.objectExists(seq.Info.Name) {
 		return errs.AlreadyExistsError{Name: seq.Info.Name}
 	}
 
@@ -412,6 +427,25 @@ func (c *catalogCache) AddSequence(tx *database.Transaction, seq *database.Seque
 	return nil
 }
 
+func (c *catalogCache) objectExists(name string) bool {
+	// checking if sequence exists with the same name
+	if _, ok := c.sequences[name]; ok {
+		return true
+	}
+
+	// checking if table exists with the same name
+	if _, ok := c.tables[name]; ok {
+		return true
+	}
+
+	// checking if index exists with the same name
+	if _, ok := c.indexes[name]; ok {
+		return true
+	}
+
+	return false
+}
+
 func (c *catalogCache) GetSequence(name string) (*database.Sequence, error) {
 	c.mu.RLock()
 	defer c.mu.RUnlock()
diff --git a/internal/catalog/catalog.go b/internal/catalog/catalog.go
index 76297b30..80ce933b 100644
--- a/internal/catalog/catalog.go
+++ b/internal/catalog/catalog.go
@@ -1,25 +1,22 @@
 package catalog
 
 import (
+	"encoding/binary"
+	"math"
 	"sort"
-	"strings"
 
 	"github.com/genjidb/genji/document"
+	errs "github.com/genjidb/genji/errors"
 	"github.com/genjidb/genji/internal/database"
-	"github.com/genjidb/genji/internal/query/statement"
-	"github.com/genjidb/genji/internal/sql/parser"
 	"github.com/genjidb/genji/internal/stringutil"
 )
 
 const (
-	internalPrefix = "__genji_"
-)
-
-const (
-	CatalogTableName         = internalPrefix + "catalog"
+	CatalogTableName         = database.InternalPrefix + "catalog"
 	CatalogTableTableType    = "table"
 	CatalogTableIndexType    = "index"
 	CatalogTableSequenceType = "sequence"
+	CatalogStoreSequence     = CatalogTableName + "_seq"
 )
 
 // Catalog manages all database objects such as tables, indexes and sequences.
@@ -38,79 +35,41 @@ func New() *Catalog {
 
 func (c *Catalog) Load(tx *database.Transaction) error {
 	c.CatalogTable = NewCatalogTable(tx)
+
+	// ensure the catalog table exists
 	err := c.CatalogTable.Init(tx)
 	if err != nil {
 		return err
 	}
 
-	return c.loadCatalog(tx)
-}
-
-func (c *Catalog) loadCatalog(tx *database.Transaction) error {
-	tb := c.CatalogTable.GetTable(tx)
-
-	var tables []database.TableInfo
-	var indexes []database.IndexInfo
-	var sequences []database.SequenceInfo
-
-	err := tb.AscendGreaterOrEqual(document.Value{}, func(d document.Document) error {
-		s, err := d.GetByField("sql")
-		if err != nil && err != document.ErrFieldNotFound {
-			return err
-		}
-		if err == document.ErrFieldNotFound {
-			return nil
-		}
-
-		stmt, err := parser.NewParser(strings.NewReader(s.V.(string))).ParseStatement()
-		if err != nil {
-			return err
-		}
+	// load catalog information
+	err = c.loadCatalog(tx)
+	if err != nil {
+		return err
+	}
 
-		tp, err := d.GetByField("type")
-		if err != nil {
+	// ensure the catalog table sequence exists
+	err = c.CreateSequence(tx, &database.SequenceInfo{
+		Name:        CatalogStoreSequence,
+		IncrementBy: 1,
+		Min:         1, Max: math.MaxInt64,
+		Start: 1,
+		Cache: 16,
+		Owner: database.SequenceInfoOwner{
+			TableName: CatalogTableName,
+		},
+	})
+	if err != nil {
+		if _, ok := err.(errs.AlreadyExistsError); !ok {
 			return err
 		}
+	}
 
-		switch tp.V.(string) {
-		case CatalogTableTableType:
-			ti := stmt.(*statement.CreateTableStmt).Info
-
-			v, err := d.GetByField("store_name")
-			if err != nil {
-				return err
-			}
-			ti.StoreName = v.V.([]byte)
-
-			tables = append(tables, ti)
-		case CatalogTableIndexType:
-			i := stmt.(*statement.CreateIndexStmt).Info
-
-			v, err := d.GetByField("store_name")
-			if err != nil {
-				return err
-			}
-			i.StoreName = v.V.([]byte)
-
-			cpath, err := d.GetByField("constraint_path")
-			if err != nil && err != document.ErrFieldNotFound {
-				return err
-			}
-			if err == nil {
-				i.ConstraintPath, err = parser.ParsePath(cpath.V.(string))
-				if err != nil {
-					return err
-				}
-			}
-
-			indexes = append(indexes, i)
-		case CatalogTableSequenceType:
-			i := stmt.(*statement.CreateSequenceStmt).Info
-			sequences = append(sequences, i)
-		}
+	return nil
+}
 
-		return nil
-	})
+func (c *Catalog) loadCatalog(tx *database.Transaction) error {
+	tables, indexes, sequences, err := c.CatalogTable.Load(tx)
 	if err != nil {
 		return err
 	}
@@ -167,6 +126,21 @@ func (c *Catalog) loadSequences(tx *database.Transaction, info []database.Sequen
 	return sequences, nil
 }
 
+func (c *Catalog) generateStoreName(tx *database.Transaction) ([]byte, error) {
+	seq, err := c.GetSequence(CatalogStoreSequence)
+	if err != nil {
+		return nil, err
+	}
+	v, err := seq.Next(tx)
+	if err != nil {
+		return nil, err
+	}
+
+	buf := make([]byte, binary.MaxVarintLen64)
+	n := binary.PutUvarint(buf, uint64(v))
+	return buf[:n], nil
+}
+
 func (c *Catalog) GetTable(tx *database.Transaction, tableName string) (*database.Table, error) {
 	ti, err := c.Cache.GetTable(tableName)
 	if err != nil {
@@ -210,6 +184,13 @@ func (c *Catalog) CreateTable(tx *database.Transaction, tableName string, info *
 		return err
 	}
 
+	if info.StoreName == nil {
+		info.StoreName, err = c.generateStoreName(tx)
+		if err != nil {
+			return err
+		}
+	}
+
 	err = c.CatalogTable.InsertTable(tx, tableName, info)
 	if err != nil {
 		return err
@@ -253,6 +234,13 @@ func (c *Catalog) CreateIndex(tx *database.Transaction, info *database.IndexInfo
 		return err
 	}
 
+	if info.StoreName == nil {
+		info.StoreName, err = c.generateStoreName(tx)
+		if err != nil {
+			return err
+		}
+	}
+
 	err = c.CatalogTable.InsertIndex(tx, info)
 	if err != nil {
 		return err
@@ -430,26 +418,26 @@ func (c *Catalog) GetSequence(name string) (*database.Sequence, error) {
 }
 
 // CreateSequence creates a sequence with the given name.
-func (c *Catalog) CreateSequence(tx *database.Transaction, name string, info *database.SequenceInfo) error {
+func (c *Catalog) CreateSequence(tx *database.Transaction, info *database.SequenceInfo) error {
 	if info == nil {
 		info = new(database.SequenceInfo)
 	}
-	info.Name = name
 
-	err := c.CatalogTable.InsertSequence(tx, info)
+	seq := database.Sequence{
+		Info: info,
+	}
+
+	err := c.Cache.AddSequence(tx, &seq)
 	if err != nil {
 		return err
 	}
 
-	seq := database.Sequence{
-		Info: info,
-	}
-	err = seq.Init(tx)
+	err = c.CatalogTable.InsertSequence(tx, info)
 	if err != nil {
 		return err
 	}
 
-	return c.Cache.AddSequence(tx, &seq)
+	return seq.Init(tx)
 }
 
 // DropSequence deletes a sequence from the catalog.
diff --git a/internal/catalog/storage.go b/internal/catalog/storage.go
index de8482ce..2b8e330e 100644
--- a/internal/catalog/storage.go
+++ b/internal/catalog/storage.go
@@ -1,15 +1,166 @@
 package catalog
 
 import (
-	"encoding/binary"
+	"strings"
 
 	"github.com/genjidb/genji/document"
 	"github.com/genjidb/genji/engine"
 	errs "github.com/genjidb/genji/errors"
 	"github.com/genjidb/genji/internal/database"
+	"github.com/genjidb/genji/internal/query/statement"
+	"github.com/genjidb/genji/internal/sql/parser"
 	"github.com/genjidb/genji/internal/stringutil"
 )
 
+func tableInfoToDocument(ti *database.TableInfo) document.Document {
+	buf := document.NewFieldBuffer()
+	buf.Add("name", document.NewTextValue(ti.TableName))
+	buf.Add("type", document.NewTextValue(CatalogTableTableType))
+	buf.Add("store_name", document.NewBlobValue(ti.StoreName))
+	buf.Add("sql", document.NewTextValue(ti.String()))
+	if ti.DocidSequenceName != "" {
+		buf.Add("docid_sequence_name", document.NewTextValue(ti.DocidSequenceName))
+	}
+
+	return buf
+}
+
+func tableInfoFromDocument(d document.Document) (*database.TableInfo, error) {
+	s, err := d.GetByField("sql")
+	if err != nil {
+		return nil, err
+	}
+
+	stmt, err := parser.NewParser(strings.NewReader(s.V.(string))).ParseStatement()
+	if err != nil {
+		return nil, err
+	}
+
+	ti := stmt.(*statement.CreateTableStmt).Info
+
+	v, err := d.GetByField("store_name")
+	if err != nil {
+		return nil, err
+	}
+	ti.StoreName = v.V.([]byte)
+
+	v, err = d.GetByField("docid_sequence_name")
+	if err != nil && err != document.ErrFieldNotFound {
+		return nil, err
+	}
+	if err == nil {
+		ti.DocidSequenceName = v.V.(string)
+	}
+
+	return &ti, nil
+}
+
+func indexInfoToDocument(i *database.IndexInfo) document.Document {
+	buf := document.NewFieldBuffer()
+	buf.Add("name", document.NewTextValue(i.IndexName))
+	buf.Add("type", document.NewTextValue(CatalogTableIndexType))
+	buf.Add("store_name", document.NewBlobValue(i.StoreName))
+	buf.Add("table_name", document.NewTextValue(i.TableName))
+	buf.Add("sql", document.NewTextValue(i.String()))
+	if i.ConstraintPath != nil {
+		buf.Add("constraint_path", document.NewTextValue(i.ConstraintPath.String()))
+	}
+
+	return buf
+}
+
+func indexInfoFromDocument(d document.Document) (*database.IndexInfo, error) {
+	s, err := d.GetByField("sql")
+	if err != nil {
+		return nil, err
+	}
+
+	stmt, err := parser.NewParser(strings.NewReader(s.V.(string))).ParseStatement()
+	if err != nil {
+		return nil, err
+	}
+
+	i := stmt.(*statement.CreateIndexStmt).Info
+
+	v, err := d.GetByField("store_name")
+	if err != nil {
+		return nil, err
+	}
+	i.StoreName = v.V.([]byte)
+
+	v, err = d.GetByField("constraint_path")
+	if err != nil && err != document.ErrFieldNotFound {
+		return nil, err
+	}
+	if err == nil {
+		i.ConstraintPath, err = parser.ParsePath(v.V.(string))
+		if err != nil {
+			return nil, err
+		}
+	}
+
+	return &i, nil
+}
+
+func sequenceInfoToDocument(seq *database.SequenceInfo) document.Document {
+	buf := document.NewFieldBuffer()
+	buf.Add("name", document.NewTextValue(seq.Name))
+	buf.Add("type", document.NewTextValue(CatalogTableSequenceType))
+	buf.Add("sql", document.NewTextValue(seq.String()))
+
+	if seq.Owner.TableName != "" {
+		owner := document.NewFieldBuffer().Add("table_name", document.NewTextValue(seq.Owner.TableName))
+		if seq.Owner.Path != nil {
+			owner.Add("path", document.NewTextValue(seq.Owner.Path.String()))
+		}
+
+		buf.Add("owner", document.NewDocumentValue(owner))
+	}
+
+	return buf
+}
+
+func sequenceInfoFromDocument(d document.Document) (*database.SequenceInfo, error) {
+	s, err := d.GetByField("sql")
+	if err != nil {
+		return nil, err
+	}
+
+	stmt, err := parser.NewParser(strings.NewReader(s.V.(string))).ParseStatement()
+	if err != nil {
+		return nil, err
+	}
+
+	i := stmt.(*statement.CreateSequenceStmt).Info
+
+	v, err := d.GetByField("owner")
+	if err != nil && err != document.ErrFieldNotFound {
+		return nil, err
+	}
+	if err == nil {
+		d := v.V.(document.Document)
+		v, err := d.GetByField("table_name")
+		if err != nil {
+			return nil, err
+		}
+
+		i.Owner.TableName = v.V.(string)
+
+		v, err = d.GetByField("path")
+		if err != nil && err != document.ErrFieldNotFound {
+			return nil, err
+		}
+		if err == nil {
+			i.Owner.Path, err = parser.ParsePath(v.V.(string))
+			if err != nil {
+				return nil, err
+			}
+		}
+	}
+
+	return &i, nil
+}
+
 type CatalogTable struct {
 	Info *database.TableInfo
 }
@@ -71,9 +222,46 @@ func (s *CatalogTable) Init(tx *database.Transaction) error {
 	if err == engine.ErrStoreNotFound {
 		err = tx.Tx.CreateStore([]byte(CatalogTableName))
 	}
+
 	return err
 }
 
+func (s *CatalogTable) Load(tx *database.Transaction) (tables []database.TableInfo, indexes []database.IndexInfo, sequences []database.SequenceInfo, err error) {
+	tb := s.GetTable(tx)
+
+	err = tb.AscendGreaterOrEqual(document.Value{}, func(d document.Document) error {
+		tp, err := d.GetByField("type")
+		if err != nil {
+			return err
+		}
+
+		switch tp.V.(string) {
+		case CatalogTableTableType:
+			ti, err := tableInfoFromDocument(d)
+			if err != nil {
+				return err
+			}
+			tables = append(tables, *ti)
+		case CatalogTableIndexType:
+			i, err := indexInfoFromDocument(d)
+			if err != nil {
+				return err
+			}
+
+			indexes = append(indexes, *i)
+		case CatalogTableSequenceType:
+			i, err := sequenceInfoFromDocument(d)
+			if err != nil {
+				return err
+			}
+			sequences = append(sequences, *i)
+		}
+
+		return nil
+	})
+	return
+}
+
 func (s *CatalogTable) GetTable(tx *database.Transaction) *database.Table {
 	st, err := tx.Tx.GetStore([]byte(CatalogTableName))
 	if err != nil {
@@ -87,54 +275,12 @@ func (s *CatalogTable) GetTable(tx *database.Transaction) *database.Table {
 	}
 }
 
-func (s *CatalogTable) tableInfoToDocument(ti *database.TableInfo) document.Document {
-	buf := document.NewFieldBuffer()
-	buf.Add("name", document.NewTextValue(ti.TableName))
-	buf.Add("type", document.NewTextValue(CatalogTableTableType))
-	buf.Add("store_name", document.NewBlobValue(ti.StoreName))
-	buf.Add("sql", document.NewTextValue(ti.String()))
-	return buf
-}
-
-func (s *CatalogTable) indexInfoToDocument(i *database.IndexInfo) document.Document {
-	buf := document.NewFieldBuffer()
-	buf.Add("name", document.NewTextValue(i.IndexName))
-	buf.Add("type", document.NewTextValue(CatalogTableIndexType))
-	buf.Add("store_name", document.NewBlobValue(i.StoreName))
-	buf.Add("table_name", document.NewTextValue(i.TableName))
-	buf.Add("sql", document.NewTextValue(i.String()))
-	if i.ConstraintPath != nil {
-		buf.Add("constraint_path", document.NewTextValue(i.ConstraintPath.String()))
-	}
-
-	return buf
-}
-
-func (s *CatalogTable) sequenceInfoToDocument(seq *database.SequenceInfo) document.Document {
-	buf := document.NewFieldBuffer()
-	buf.Add("name", document.NewTextValue(seq.Name))
-	buf.Add("type", document.NewTextValue(CatalogTableSequenceType))
-	buf.Add("sql", document.NewTextValue(seq.String()))
-
-	return buf
-}
-
 // InsertTable inserts a new tableInfo for the given table name.
 // If info.StoreName is nil, it generates one and stores it in info.
 func (s *CatalogTable) InsertTable(tx *database.Transaction, tableName string, info *database.TableInfo) error {
 	tb := s.GetTable(tx)
 
-	if info.StoreName == nil {
-		seq, err := tb.Store.NextSequence()
-		if err != nil {
-			return err
-		}
-		buf := make([]byte, binary.MaxVarintLen64)
-		n := binary.PutUvarint(buf, seq)
-		info.StoreName = buf[:n]
-	}
-
-	_, err := tb.Insert(s.tableInfoToDocument(info))
+	_, err := tb.Insert(tableInfoToDocument(info))
 	if err == errs.ErrDuplicateDocument {
 		return errs.AlreadyExistsError{Name: tableName}
 	}
@@ -146,7 +292,7 @@ func (s *CatalogTable) InsertTable(tx *database.Transaction, tableName string, i
 func (s *CatalogTable) ReplaceTable(tx *database.Transaction, tableName string, info *database.TableInfo) error {
 	tb := s.GetTable(tx)
 
-	_, err := tb.Replace([]byte(tableName), s.tableInfoToDocument(info))
+	_, err := tb.Replace([]byte(tableName), tableInfoToDocument(info))
 	return err
 }
 
@@ -159,18 +305,7 @@ func (s *CatalogTable) DeleteTable(tx *database.Transaction, tableName string) e
 func (s *CatalogTable) InsertIndex(tx *database.Transaction, info *database.IndexInfo) error {
 	tb := s.GetTable(tx)
 
-	if info.StoreName == nil {
-		seq, err := tb.Store.NextSequence()
-		if err != nil {
-			return err
-		}
-
-		buf := make([]byte, binary.MaxVarintLen64)
-		n := binary.PutUvarint(buf, seq)
-		info.StoreName = buf[:n]
-	}
-
-	_, err := tb.Insert(s.indexInfoToDocument(info))
+	_, err := tb.Insert(indexInfoToDocument(info))
 	if err == errs.ErrDuplicateDocument {
 		return errs.AlreadyExistsError{Name: info.IndexName}
 	}
@@ -181,7 +316,7 @@ func (s *CatalogTable) InsertIndex(tx *database.Transaction, info *database.Inde
 func (s *CatalogTable) ReplaceIndex(tx *database.Transaction, indexName string, info *database.IndexInfo) error {
 	tb := s.GetTable(tx)
 
-	_, err := tb.Replace([]byte(indexName), s.indexInfoToDocument(info))
+	_, err := tb.Replace([]byte(indexName), indexInfoToDocument(info))
 	return err
 }
 
@@ -194,7 +329,7 @@ func (s *CatalogTable) DeleteIndex(tx *database.Transaction, indexName string) e
 func (s *CatalogTable) InsertSequence(tx *database.Transaction, info *database.SequenceInfo) error {
 	tb := s.GetTable(tx)
 
-	_, err := tb.Insert(s.sequenceInfoToDocument(info))
+	_, err := tb.Insert(sequenceInfoToDocument(info))
 	if err == errs.ErrDuplicateDocument {
 		return errs.AlreadyExistsError{Name: info.Name}
 	}
@@ -205,7 +340,7 @@ func (s *CatalogTable) InsertSequence(tx *database.Transaction, info *database.S
 func (s *CatalogTable) ReplaceSequence(tx *database.Transaction, name string, info *database.SequenceInfo) error {
 	tb := s.GetTable(tx)
 
-	_, err := tb.Replace([]byte(name), s.sequenceInfoToDocument(info))
+	_, err := tb.Replace([]byte(name), sequenceInfoToDocument(info))
 	return err
 }
 
diff --git a/internal/database/catalog.go b/internal/database/catalog.go
index 63adea82..a43a7f7c 100644
--- a/internal/database/catalog.go
+++ b/internal/database/catalog.go
@@ -14,6 +14,6 @@ type Catalog interface {
 	ReIndex(tx *Transaction, indexName string) error
 	ReIndexAll(tx *Transaction) error
 	GetSequence(name string) (*Sequence, error)
-	CreateSequence(tx *Transaction, name string, info *SequenceInfo) error
+	CreateSequence(tx *Transaction, info *SequenceInfo) error
 	DropSequence(tx *Transaction, name string) error
 }
diff --git a/internal/database/constraint.go b/internal/database/constraint.go
index 9720d239..f58a7975 100644
--- a/internal/database/constraint.go
+++ b/internal/database/constraint.go
@@ -24,6 +24,7 @@ type FieldConstraint struct {
 	IsNotNull    bool
 	IsUnique     bool
 	DefaultValue document.Value
+	Identity     *FieldConstraintIdentity
 	IsInferred   bool
 	InferredBy   []document.Path
 }
@@ -57,6 +58,10 @@ func (f *FieldConstraint) IsEqual(other *FieldConstraint) (bool, error) {
 		}
 	}
 
+	if !f.Identity.IsEqual(other.Identity) {
+		return false, nil
+	}
+
 	return true, nil
 }
 
@@ -411,3 +416,20 @@ func (f FieldConstraints) convertArrayAtPath(path document.Path, a document.Arra
 
 	return vb, err
 }
+
+type FieldConstraintIdentity struct {
+	SequenceName string
+	Always       bool
+}
+
+func (f *FieldConstraintIdentity) IsEqual(other *FieldConstraintIdentity) bool {
+	if f == nil {
+		return other == nil
+	}
+
+	if other == nil {
+		return false
+	}
+
+	return f.SequenceName == other.SequenceName && f.Always == other.Always
+}
diff --git a/internal/database/database.go b/internal/database/database.go
index f583ca69..580095c6 100644
--- a/internal/database/database.go
+++ b/internal/database/database.go
@@ -10,6 +10,10 @@ import (
 	"github.com/genjidb/genji/engine"
 )
 
+const (
+	InternalPrefix = "__genji_"
+)
+
 // A Database manages a list of tables in an engine.
 type Database struct {
 	ng engine.Engine
@@ -56,6 +60,7 @@ func New(ctx context.Context, ng engine.Engine, opts Options) (*Database, error)
 	defer tx.Rollback()
 
 	db.Catalog = opts.Catalog
+	tx.Catalog = db.Catalog
 
 	err = db.Catalog.Load(tx)
 	if err != nil {
diff --git a/internal/database/index.go b/internal/database/index.go
index e45f0df1..1dd3fee8 100644
--- a/internal/database/index.go
+++ b/internal/database/index.go
@@ -172,7 +172,7 @@ func (idx *Index) Set(vs []document.Value, k []byte) error {
 		}
 
 		// the value already exists
-		// add a prefix to that value
+		// add a suffix to that value
 		seq, err := st.NextSequence()
 		if err != nil {
 			return err
diff --git a/internal/database/info.go b/internal/database/info.go
index a85841b2..ed628fd1 100644
--- a/internal/database/info.go
+++ b/internal/database/info.go
@@ -17,6 +17,9 @@ type TableInfo struct {
 	ReadOnly  bool
 
 	FieldConstraints FieldConstraints
+
+	// Name of the docid sequence if any.
+	DocidSequenceName string
 }
 
 // String returns a SQL representation.
@@ -123,6 +126,7 @@ type SequenceInfo struct {
 	Start       int64
 	Cache       uint64
 	Cycle       bool
+	Owner       SequenceInfoOwner
 }
 
 // String returns a SQL representation.
@@ -165,3 +169,13 @@ func (s *SequenceInfo) String() string {
 func (s SequenceInfo) Clone() *SequenceInfo {
 	return &s
 }
+
+// SequenceInfoOwner is used to determine who owns a sequence.
+// If a sequence has been created by a table (for docids for example),
+// only the TableName is filled.
+// If it has been created by a field constraint (for identities), the
+// path must also be filled.
+type SequenceInfoOwner struct {
+	TableName string
+	Path      document.Path
+}
diff --git a/internal/database/table.go b/internal/database/table.go
index 27856c8d..3cb6f557 100644
--- a/internal/database/table.go
+++ b/internal/database/table.go
@@ -539,12 +539,16 @@ func (t *Table) generateKey(info *TableInfo, d document.Document) ([]byte, error
 		return buf.Bytes(), nil
 	}
 
-	docid, err := t.Store.NextSequence()
+	seq, err := t.Tx.Catalog.GetSequence(t.Info.DocidSequenceName)
+	if err != nil {
+		return nil, err
+	}
+	docid, err := seq.Next(t.Tx)
 	if err != nil {
 		return nil, err
 	}
 
 	buf := make([]byte, binary.MaxVarintLen64)
-	n := binary.PutUvarint(buf, docid)
+	n := binary.PutUvarint(buf, uint64(docid))
 	return buf[:n], nil
 }
diff --git a/internal/query/statement/create.go b/internal/query/statement/create.go
index 3a0e26ac..0e8e6793 100644
--- a/internal/query/statement/create.go
+++ b/internal/query/statement/create.go
@@ -1,6 +1,8 @@
 package statement
 
 import (
+	"math"
+
 	"github.com/genjidb/genji/document"
 	errs "github.com/genjidb/genji/errors"
 	"github.com/genjidb/genji/internal/database"
@@ -23,6 +25,25 @@ func (stmt *CreateTableStmt) IsReadOnly() bool {
 func (stmt *CreateTableStmt) Run(tx *database.Transaction, args []expr.Param) (Result, error) {
 	var res Result
 
+	// if there are no primary key, create a docid sequence
+	if stmt.Info.FieldConstraints.GetPrimaryKey() == nil {
+		seq := database.SequenceInfo{
+			IncrementBy: 1,
+			Min:         1, Max: math.MaxInt64,
+			Start: 1,
+			Cache: 32,
+			Owner: database.SequenceInfoOwner{
+				TableName: stmt.Info.TableName,
+			},
+		}
+		err := tx.Catalog.CreateSequence(tx, &seq)
+		if err != nil {
+			return res, err
+		}
+
+		stmt.Info.DocidSequenceName = seq.Name
+	}
+
 	err := tx.Catalog.CreateTable(tx, stmt.Info.TableName, &stmt.Info)
 	if stmt.IfNotExists {
 		if _, ok := err.(errs.AlreadyExistsError); ok {
@@ -30,6 +51,7 @@ func (stmt *CreateTableStmt) Run(tx *database.Transaction, args []expr.Param) (R
 		}
 	}
 
+	// create a unique index for every unique constraint
 	for _, fc := range stmt.Info.FieldConstraints {
 		if fc.IsUnique {
 			err = tx.Catalog.CreateIndex(tx, &database.IndexInfo{
@@ -94,7 +116,7 @@ func (stmt *CreateSequenceStmt) IsReadOnly() bool {
 func (stmt *CreateSequenceStmt) Run(tx *database.Transaction, args []expr.Param) (Result, error) {
 	var res Result
 
-	err := tx.Catalog.CreateSequence(tx, stmt.Info.Name, &stmt.Info)
+	err := tx.Catalog.CreateSequence(tx, &stmt.Info)
 	if stmt.IfNotExists {
 		if _, ok := err.(errs.AlreadyExistsError); ok {
 			return res, nil
diff --git a/internal/stringutil/custom.go b/internal/stringutil/custom.go
index eafe8777..8a26a376 100644
--- a/internal/stringutil/custom.go
+++ b/internal/stringutil/custom.go
@@ -69,6 +69,10 @@ func toString(v interface{}) string {
 		return t.String()
 	case int:
 		return strconv.Itoa(t)
+	case int64:
+		return strconv.Itoa(int(t))
+	case uint64:
+		return strconv.Itoa(int(t))
 	case error:
 		return t.Error()
 	default:


Test patch:
diff --git a/driver/driver_test.go b/driver/driver_test.go
index b6ba9569..242ffc0a 100644
--- a/driver/driver_test.go
+++ b/driver/driver_test.go
@@ -6,7 +6,6 @@ import (
 	"testing"
 	"time"
 
-	"github.com/genjidb/genji/engine"
 	"github.com/stretchr/testify/require"
 )
 
@@ -260,7 +259,7 @@ func TestDriver(t *testing.T) {
 			INSERT INTO test (a, b, c) VALUES (12, 13, 14);
 			SELECT * FROM test;
 		`)
-		require.Equal(t, err, engine.ErrTransactionReadOnly)
+		require.EqualError(t, err, "cannot increment sequence on read-only transaction")
 	})
 }
 
diff --git a/internal/catalog/catalog_test.go b/internal/catalog/catalog_test.go
index 9d1c842f..f71d56c9 100644
--- a/internal/catalog/catalog_test.go
+++ b/internal/catalog/catalog_test.go
@@ -3,6 +3,7 @@ package catalog_test
 import (
 	"bytes"
 	"errors"
+	"fmt"
 	"testing"
 
 	"github.com/genjidb/genji"
@@ -297,7 +298,11 @@ func TestCatalogCreateIndex(t *testing.T) {
 		defer cleanup()
 
 		update(t, db, func(tx *database.Transaction) error {
-			err := tx.Catalog.CreateTable(tx, "test", nil)
+			err := tx.Catalog.CreateTable(tx, "test", &database.TableInfo{
+				FieldConstraints: database.FieldConstraints{
+					{Path: testutil.ParseDocumentPath(t, "a"), IsPrimaryKey: true},
+				},
+			})
 			if err != nil {
 				return err
 			}
@@ -469,7 +474,11 @@ func TestCatalogReIndex(t *testing.T) {
 	prepareTableFn := func(t *testing.T, db *database.Database) {
 
 		update(t, db, func(tx *database.Transaction) error {
-			err := tx.Catalog.CreateTable(tx, "test", nil)
+			err := tx.Catalog.CreateTable(tx, "test", &database.TableInfo{
+				FieldConstraints: database.FieldConstraints{
+					{Path: testutil.ParseDocumentPath(t, "a"), IsPrimaryKey: true},
+				},
+			})
 			require.NoError(t, err)
 			tb, err := tx.Catalog.GetTable(tx, "test")
 			require.NoError(t, err)
@@ -517,7 +526,11 @@ func TestCatalogReIndex(t *testing.T) {
 		defer cleanup()
 
 		update(t, db, func(tx *database.Transaction) error {
-			err := tx.Catalog.CreateTable(tx, "test", nil)
+			err := tx.Catalog.CreateTable(tx, "test", &database.TableInfo{
+				FieldConstraints: database.FieldConstraints{
+					{Path: testutil.ParseDocumentPath(t, "a"), IsPrimaryKey: true},
+				},
+			})
 			require.NoError(t, err)
 
 			tb, err := tx.Catalog.GetTable(tx, "test")
@@ -602,12 +615,20 @@ func TestReIndexAll(t *testing.T) {
 		defer cleanup()
 
 		update(t, db, func(tx *database.Transaction) error {
-			err := tx.Catalog.CreateTable(tx, "test1", nil)
+			err := tx.Catalog.CreateTable(tx, "test1", &database.TableInfo{
+				FieldConstraints: database.FieldConstraints{
+					{Path: testutil.ParseDocumentPath(t, "a"), IsPrimaryKey: true},
+				},
+			})
 			require.NoError(t, err)
 			tb1, err := tx.Catalog.GetTable(tx, "test1")
 			require.NoError(t, err)
 
-			err = tx.Catalog.CreateTable(tx, "test2", nil)
+			err = tx.Catalog.CreateTable(tx, "test2", &database.TableInfo{
+				FieldConstraints: database.FieldConstraints{
+					{Path: testutil.ParseDocumentPath(t, "a"), IsPrimaryKey: true},
+				},
+			})
 			require.NoError(t, err)
 			tb2, err := tx.Catalog.GetTable(tx, "test2")
 			require.NoError(t, err)
@@ -702,13 +723,19 @@ func TestReadOnlyTables(t *testing.T) {
 	err = res.Iterate(func(d document.Document) error {
 		switch i {
 		case 0:
-			testutil.RequireDocJSONEq(t, d, `{"name":"foo", "type":"table", "store_name":"AQ==", "sql":"CREATE TABLE foo (a INTEGER, b[3].c DOUBLE UNIQUE)"}`)
+			testutil.RequireDocJSONEq(t, d, `{"name":"__genji_catalog_seq", "owner":{"table_name":"__genji_catalog"}, "sql":"CREATE SEQUENCE __genji_catalog_seq CACHE 16", "type":"sequence"}`)
 		case 1:
-			testutil.RequireDocJSONEq(t, d, `{"constraint_path":"b[3].c", "name":"foo_b[3].c_idx", "sql":"CREATE UNIQUE INDEX foo_b[3].c_idx ON foo (b[3].c)", "store_name":"Ag==", "table_name":"foo", "type":"index"}`)
+			testutil.RequireDocJSONEq(t, d, `{"name":"__genji_sequence", "sql":"CREATE TABLE __genji_sequence (name TEXT PRIMARY KEY, seq INTEGER)", "store_name":"X19nZW5qaV9zZXF1ZW5jZQ==", "type":"table"}`)
 		case 2:
+			testutil.RequireDocJSONEq(t, d, `{"name":"foo", "docid_sequence_name":"foo_seq", "sql":"CREATE TABLE foo (a INTEGER, b[3].c DOUBLE UNIQUE)", "store_name":"AQ==", "type":"table"}`)
+		case 3:
+			testutil.RequireDocJSONEq(t, d, `{"name":"foo_b[3].c_idx", "constraint_path":"b[3].c", "sql":"CREATE UNIQUE INDEX foo_b[3].c_idx ON foo (b[3].c)", "store_name":"Ag==", "table_name":"foo", "type":"index"}`)
+		case 4:
+			testutil.RequireDocJSONEq(t, d, `{"name":"foo_seq", "owner":{"table_name":"foo"}, "sql":"CREATE SEQUENCE foo_seq CACHE 32", "type":"sequence"}`)
+		case 5:
 			testutil.RequireDocJSONEq(t, d, `{"name":"idx_foo_a", "sql":"CREATE INDEX idx_foo_a ON foo (a)", "store_name":"Aw==", "table_name":"foo", "type":"index"}`)
 		default:
-			t.Fatalf("count should be 2, got %d", i)
+			t.Fatalf("count should be 5, got %d", i)
 		}
 
 		i++
@@ -723,7 +750,7 @@ func TestCatalogCreateSequence(t *testing.T) {
 		defer cleanup()
 
 		update(t, db, func(tx *database.Transaction) error {
-			err := tx.Catalog.CreateSequence(tx, "test1", &database.SequenceInfo{Name: "test1", IncrementBy: 1})
+			err := tx.Catalog.CreateSequence(tx, &database.SequenceInfo{Name: "test1", IncrementBy: 1})
 			if err != nil {
 				return err
 			}
@@ -746,7 +773,7 @@ func TestCatalogCreateSequence(t *testing.T) {
 		clone := cloneCatalog(db.Catalog)
 
 		update(t, db, func(tx *database.Transaction) error {
-			err := tx.Catalog.CreateSequence(tx, "test2", &database.SequenceInfo{Name: "test2", IncrementBy: 1})
+			err := tx.Catalog.CreateSequence(tx, &database.SequenceInfo{Name: "test2", IncrementBy: 1})
 			if err != nil {
 				return err
 			}
@@ -760,16 +787,41 @@ func TestCatalogCreateSequence(t *testing.T) {
 		require.Equal(t, clone, db.Catalog)
 	})
 
+	t.Run("Should generate a sequence name if not provided", func(t *testing.T) {
+		db, cleanup := testutil.NewTestDB(t)
+		defer cleanup()
+
+		update(t, db, func(tx *database.Transaction) error {
+			for i := 0; i < 10; i++ {
+				seqInfo := &database.SequenceInfo{IncrementBy: 1, Owner: database.SequenceInfoOwner{
+					TableName: "foo",
+				}}
+				err := tx.Catalog.CreateSequence(tx, seqInfo)
+				if err != nil {
+					return err
+				}
+
+				if i == 0 {
+					require.Equal(t, "foo_seq", seqInfo.Name)
+				} else {
+					require.Equal(t, fmt.Sprintf("foo_seq%d", i), seqInfo.Name)
+				}
+			}
+
+			return nil
+		})
+	})
+
 	t.Run("Should fail if it already exists", func(t *testing.T) {
 		db, cleanup := testutil.NewTestDB(t)
 		defer cleanup()
 
 		update(t, db, func(tx *database.Transaction) error {
-			return tx.Catalog.CreateSequence(tx, "test", nil)
+			return tx.Catalog.CreateSequence(tx, &database.SequenceInfo{Name: "test"})
 		})
 
 		update(t, db, func(tx *database.Transaction) error {
-			err := tx.Catalog.CreateSequence(tx, "test", nil)
+			err := tx.Catalog.CreateSequence(tx, &database.SequenceInfo{Name: "test"})
 			require.Equal(t, errs.AlreadyExistsError{Name: "test"}, err)
 			return nil
 		})
diff --git a/internal/database/sequence_test.go b/internal/database/sequence_test.go
index ba13c398..9baedb82 100644
--- a/internal/database/sequence_test.go
+++ b/internal/database/sequence_test.go
@@ -158,7 +158,7 @@ func TestSequence(t *testing.T) {
 			require.NoError(t, err)
 			defer tx.Rollback()
 
-			err = tx.Catalog.CreateSequence(tx, test.info.Name, &test.info)
+			err = tx.Catalog.CreateSequence(tx, &test.info)
 			require.NoError(t, err)
 
 			seq := database.Sequence{
@@ -195,7 +195,7 @@ func TestSequence(t *testing.T) {
 		require.NoError(t, err)
 		defer tx.Rollback()
 
-		err = tx.Catalog.CreateSequence(tx, "a", &database.SequenceInfo{
+		err = tx.Catalog.CreateSequence(tx, &database.SequenceInfo{
 			Name:        "a",
 			IncrementBy: 1,
 			Min:         1, Max: 5,
@@ -233,7 +233,7 @@ func TestSequence(t *testing.T) {
 		require.NoError(t, err)
 		defer tx.Rollback()
 
-		err = tx.Catalog.CreateSequence(tx, "a", &database.SequenceInfo{
+		err = tx.Catalog.CreateSequence(tx, &database.SequenceInfo{
 			Name:        "a",
 			IncrementBy: 1,
 			Min:         1, Max: 9,
@@ -280,7 +280,7 @@ func TestSequence(t *testing.T) {
 		require.NoError(t, err)
 		defer tx.Rollback()
 
-		err = tx.Catalog.CreateSequence(tx, "a", &database.SequenceInfo{
+		err = tx.Catalog.CreateSequence(tx, &database.SequenceInfo{
 			Name:        "a",
 			IncrementBy: -1,
 			Min:         -5, Max: 9,
@@ -327,7 +327,7 @@ func TestSequence(t *testing.T) {
 		tx, err := db.Begin(true)
 		require.NoError(t, err)
 
-		err = tx.Catalog.CreateSequence(tx, "a", &database.SequenceInfo{
+		err = tx.Catalog.CreateSequence(tx, &database.SequenceInfo{
 			Name:        "a",
 			IncrementBy: -1,
 			Min:         -4, Max: 9,
diff --git a/internal/database/table_test.go b/internal/database/table_test.go
index 8fe9e95f..da1def80 100644
--- a/internal/database/table_test.go
+++ b/internal/database/table_test.go
@@ -14,6 +14,7 @@ import (
 	"github.com/genjidb/genji/internal/binarysort"
 	"github.com/genjidb/genji/internal/catalog"
 	"github.com/genjidb/genji/internal/database"
+	"github.com/genjidb/genji/internal/query/statement"
 	"github.com/genjidb/genji/internal/testutil"
 	"github.com/stretchr/testify/require"
 )
@@ -41,12 +42,33 @@ func update(t testing.TB, db *database.Database, fn func(tx *database.Transactio
 func newTestTable(t testing.TB) (*database.Table, func()) {
 	_, tx, fn := testutil.NewTestTx(t)
 
-	err := tx.Catalog.CreateTable(tx, "test", nil)
+	return createTable(t, tx, database.TableInfo{TableName: "test"}), fn
+}
+
+func createTable(t testing.TB, tx *database.Transaction, info database.TableInfo) *database.Table {
+	stmt := statement.CreateTableStmt{Info: info}
+
+	res, err := stmt.Run(tx, nil)
+	require.NoError(t, err)
+	res.Close()
+
+	tb, err := tx.Catalog.GetTable(tx, stmt.Info.TableName)
+	require.NoError(t, err)
+
+	return tb
+}
+
+func createTableIfNotExists(t testing.TB, tx *database.Transaction, info database.TableInfo) *database.Table {
+	stmt := statement.CreateTableStmt{Info: info, IfNotExists: true}
+
+	res, err := stmt.Run(tx, nil)
 	require.NoError(t, err)
-	tb, err := tx.Catalog.GetTable(tx, "test")
+	res.Close()
+
+	tb, err := tx.Catalog.GetTable(tx, stmt.Info.TableName)
 	require.NoError(t, err)
 
-	return tb, fn
+	return tb
 }
 
 func newDocument() *document.FieldBuffer {
@@ -178,10 +200,7 @@ func TestTableInsert(t *testing.T) {
 		insertDoc := func(db *database.Database) (rawKey []byte) {
 			update(t, db, func(tx *database.Transaction) error {
 				// create table if not exists
-				_ = tx.Catalog.CreateTable(tx, "test", nil)
-
-				tb, err := tx.Catalog.GetTable(tx, "test")
-				require.NoError(t, err)
+				tb := createTableIfNotExists(t, tx, database.TableInfo{TableName: "test"})
 
 				doc := newDocument()
 				d, err := tb.Insert(doc)
@@ -211,7 +230,8 @@ func TestTableInsert(t *testing.T) {
 		b, _ := binary.Uvarint(key2)
 		require.NoError(t, err)
 
-		require.Equal(t, a+1, b)
+		// for now, closing a database doesn't reclaim unused docids
+		require.Equal(t, int64(a+32), int64(b))
 	})
 
 	t.Run("Should use the right field if primary key is specified", func(t *testing.T) {
@@ -249,18 +269,16 @@ func TestTableInsert(t *testing.T) {
 		_, tx, cleanup := newTestTx(t)
 		defer cleanup()
 
-		err := tx.Catalog.CreateTable(tx, "test", &database.TableInfo{
+		tb := createTable(t, tx, database.TableInfo{
+			TableName: "test",
 			FieldConstraints: []*database.FieldConstraint{
 				{Path: testutil.ParseDocumentPath(t, "foo"), Type: document.ArrayValue},
 				{Path: testutil.ParseDocumentPath(t, "foo[0]"), Type: document.IntegerValue},
 			},
 		})
-		require.NoError(t, err)
-		tb, err := tx.Catalog.GetTable(tx, "test")
-		require.NoError(t, err)
 
 		var doc document.FieldBuffer
-		err = doc.UnmarshalJSON([]byte(`{"foo": [100]}`))
+		err := doc.UnmarshalJSON([]byte(`{"foo": [100]}`))
 		require.NoError(t, err)
 
 		// insert
@@ -309,10 +327,9 @@ func TestTableInsert(t *testing.T) {
 		_, tx, cleanup := newTestTx(t)
 		defer cleanup()
 
-		err := tx.Catalog.CreateTable(tx, "test", nil)
-		require.NoError(t, err)
+		createTable(t, tx, database.TableInfo{TableName: "test"})
 
-		err = tx.Catalog.CreateIndex(tx, &database.IndexInfo{
+		err := tx.Catalog.CreateIndex(tx, &database.IndexInfo{
 			IndexName: "idxFoo", TableName: "test", Paths: []document.Path{testutil.ParseDocumentPath(t, "foo")},
 		})
 		require.NoError(t, err)
@@ -356,15 +373,13 @@ func TestTableInsert(t *testing.T) {
 		_, tx, cleanup := newTestTx(t)
 		defer cleanup()
 
-		err := tx.Catalog.CreateTable(tx, "test", &database.TableInfo{
+		tb := createTable(t, tx, database.TableInfo{
+			TableName: "test",
 			FieldConstraints: []*database.FieldConstraint{
-				{testutil.ParseDocumentPath(t, "foo"), document.DocumentValue, false, false, false, document.Value{}, true, []document.Path{testutil.ParseDocumentPath(t, "foo.bar")}},
-				{testutil.ParseDocumentPath(t, "foo.bar"), document.IntegerValue, false, false, false, document.Value{}, true, []document.Path{testutil.ParseDocumentPath(t, "foo")}},
+				{testutil.ParseDocumentPath(t, "foo"), document.DocumentValue, false, false, false, document.Value{}, nil, true, []document.Path{testutil.ParseDocumentPath(t, "foo.bar")}},
+				{testutil.ParseDocumentPath(t, "foo.bar"), document.IntegerValue, false, false, false, document.Value{}, nil, true, []document.Path{testutil.ParseDocumentPath(t, "foo")}},
 			},
 		})
-		require.NoError(t, err)
-		tb, err := tx.Catalog.GetTable(tx, "test")
-		require.NoError(t, err)
 
 		doc := document.NewFieldBuffer().
 			Add("foo", document.NewDocumentValue(
@@ -403,7 +418,7 @@ func TestTableInsert(t *testing.T) {
 
 		err := tx.Catalog.CreateTable(tx, "test", &database.TableInfo{
 			FieldConstraints: []*database.FieldConstraint{
-				{testutil.ParseDocumentPath(t, "foo"), document.DoubleValue, false, false, false, document.Value{}, false, nil},
+				{testutil.ParseDocumentPath(t, "foo"), document.DoubleValue, false, false, false, document.Value{}, nil, false, nil},
 			},
 		})
 		require.NoError(t, err)
@@ -423,27 +438,23 @@ func TestTableInsert(t *testing.T) {
 		defer cleanup()
 
 		// no enforced type, not null
-		err := tx.Catalog.CreateTable(tx, "test1", &database.TableInfo{
+		tb1 := createTable(t, tx, database.TableInfo{
+			TableName: "test1",
 			FieldConstraints: []*database.FieldConstraint{
-				{testutil.ParseDocumentPath(t, "foo"), 0, false, true, false, document.Value{}, false, nil},
+				{testutil.ParseDocumentPath(t, "foo"), 0, false, true, false, document.Value{}, nil, false, nil},
 			},
 		})
-		require.NoError(t, err)
-		tb1, err := tx.Catalog.GetTable(tx, "test1")
-		require.NoError(t, err)
 
 		// enforced type, not null
-		err = tx.Catalog.CreateTable(tx, "test2", &database.TableInfo{
+		tb2 := createTable(t, tx, database.TableInfo{
+			TableName: "test2",
 			FieldConstraints: []*database.FieldConstraint{
-				{testutil.ParseDocumentPath(t, "foo"), document.IntegerValue, false, true, false, document.Value{}, false, nil},
+				{testutil.ParseDocumentPath(t, "foo"), document.IntegerValue, false, true, false, document.Value{}, nil, false, nil},
 			},
 		})
-		require.NoError(t, err)
-		tb2, err := tx.Catalog.GetTable(tx, "test2")
-		require.NoError(t, err)
 
 		// insert with empty foo field should fail
-		_, err = tb1.Insert(document.NewFieldBuffer().
+		_, err := tb1.Insert(document.NewFieldBuffer().
 			Add("bar", document.NewDoubleValue(1)))
 		require.Error(t, err)
 
@@ -478,24 +489,20 @@ func TestTableInsert(t *testing.T) {
 		defer cleanup()
 
 		// no enforced type, not null
-		err := tx.Catalog.CreateTable(tx, "test1", &database.TableInfo{
+		tb1 := createTable(t, tx, database.TableInfo{
+			TableName: "test1",
 			FieldConstraints: []*database.FieldConstraint{
-				{testutil.ParseDocumentPath(t, "foo"), 0, false, true, false, document.NewIntegerValue(42), false, nil},
+				{testutil.ParseDocumentPath(t, "foo"), 0, false, true, false, document.NewIntegerValue(42), nil, false, nil},
 			},
 		})
-		require.NoError(t, err)
-		tb1, err := tx.Catalog.GetTable(tx, "test1")
-		require.NoError(t, err)
 
 		// enforced type, not null
-		err = tx.Catalog.CreateTable(tx, "test2", &database.TableInfo{
+		tb2 := createTable(t, tx, database.TableInfo{
+			TableName: "test2",
 			FieldConstraints: []*database.FieldConstraint{
-				{testutil.ParseDocumentPath(t, "foo"), document.IntegerValue, false, true, false, document.NewIntegerValue(42), false, nil},
+				{testutil.ParseDocumentPath(t, "foo"), document.IntegerValue, false, true, false, document.NewIntegerValue(42), nil, false, nil},
 			},
 		})
-		require.NoError(t, err)
-		tb2, err := tx.Catalog.GetTable(tx, "test2")
-		require.NoError(t, err)
 
 		// insert with empty foo field shouldn't fail
 		d, err := tb1.Insert(document.NewFieldBuffer().
@@ -544,17 +551,15 @@ func TestTableInsert(t *testing.T) {
 		_, tx, cleanup := newTestTx(t)
 		defer cleanup()
 
-		err := tx.Catalog.CreateTable(tx, "test1", &database.TableInfo{
+		tb := createTable(t, tx, database.TableInfo{
+			TableName: "test1",
 			FieldConstraints: []*database.FieldConstraint{
-				{testutil.ParseDocumentPath(t, "foo[1]"), 0, false, true, false, document.Value{}, false, nil},
+				{testutil.ParseDocumentPath(t, "foo[1]"), 0, false, true, false, document.Value{}, nil, false, nil},
 			},
 		})
-		require.NoError(t, err)
-		tb, err := tx.Catalog.GetTable(tx, "test1")
-		require.NoError(t, err)
 
 		// insert table with only one value
-		_, err = tb.Insert(document.NewFieldBuffer().
+		_, err := tb.Insert(document.NewFieldBuffer().
 			Add("foo", document.NewArrayValue(document.NewValueBuffer().Append(document.NewIntegerValue(1)))))
 		require.Error(t, err)
 		_, err = tb.Insert(document.NewFieldBuffer().
@@ -567,20 +572,17 @@ func TestTableInsert(t *testing.T) {
 		_, tx, cleanup := newTestTx(t)
 		defer cleanup()
 
-		err := tx.Catalog.CreateTable(tx, "test", &database.TableInfo{
+		tb := createTable(t, tx, database.TableInfo{
+			TableName: "test",
 			FieldConstraints: []*database.FieldConstraint{
-				{testutil.ParseDocumentPath(t, "foo"), 0, true, true, false, document.Value{}, false, nil},
+				{testutil.ParseDocumentPath(t, "foo"), 0, true, true, false, document.Value{}, nil, false, nil},
 			}})
-		require.NoError(t, err)
-
-		tb, err := tx.Catalog.GetTable(tx, "test")
-		require.NoError(t, err)
 
 		doc := document.NewFieldBuffer().
 			Add("foo", document.NewIntegerValue(10))
 
 		// insert first
-		_, err = tb.Insert(doc)
+		_, err := tb.Insert(doc)
 		require.NoError(t, err)
 
 		// insert again, should fail
@@ -592,14 +594,11 @@ func TestTableInsert(t *testing.T) {
 		_, tx, cleanup := newTestTx(t)
 		defer cleanup()
 
-		err := tx.Catalog.CreateTable(tx, "test", &database.TableInfo{
+		tb := createTable(t, tx, database.TableInfo{
+			TableName: "test",
 			FieldConstraints: []*database.FieldConstraint{
-				{testutil.ParseDocumentPath(t, "foo"), 0, true, true, false, document.Value{}, false, nil},
+				{testutil.ParseDocumentPath(t, "foo"), 0, true, true, false, document.Value{}, nil, false, nil},
 			}})
-		require.NoError(t, err)
-
-		tb, err := tx.Catalog.GetTable(tx, "test")
-		require.NoError(t, err)
 
 		doc := document.NewFieldBuffer().
 			Add("foo", document.NewIntegerValue(10))
@@ -611,7 +610,7 @@ func TestTableInsert(t *testing.T) {
 		}
 
 		// insert first
-		_, err = tb.InsertWithConflictResolution(doc, onConflict)
+		_, err := tb.InsertWithConflictResolution(doc, onConflict)
 		require.NoError(t, err)
 		require.Equal(t, 0, called)
 
@@ -625,9 +624,8 @@ func TestTableInsert(t *testing.T) {
 		_, tx, cleanup := newTestTx(t)
 		defer cleanup()
 
-		err := tx.Catalog.CreateTable(tx, "test", nil)
-		require.NoError(t, err)
-		err = tx.Catalog.CreateIndex(tx, &database.IndexInfo{
+		createTable(t, tx, database.TableInfo{TableName: "test"})
+		err := tx.Catalog.CreateIndex(tx, &database.IndexInfo{
 			TableName: "test",
 			IndexName: "idx_test_foo",
 			Paths:     []document.Path{testutil.ParseDocumentPath(t, "foo")},
@@ -654,9 +652,8 @@ func TestTableInsert(t *testing.T) {
 		_, tx, cleanup := newTestTx(t)
 		defer cleanup()
 
-		err := tx.Catalog.CreateTable(tx, "test", nil)
-		require.NoError(t, err)
-		err = tx.Catalog.CreateIndex(tx, &database.IndexInfo{
+		createTable(t, tx, database.TableInfo{TableName: "test"})
+		err := tx.Catalog.CreateIndex(tx, &database.IndexInfo{
 			TableName: "test",
 			IndexName: "idx_test_foo",
 			Paths:     []document.Path{testutil.ParseDocumentPath(t, "foo")},
@@ -691,14 +688,12 @@ func TestTableInsert(t *testing.T) {
 		_, tx, cleanup := newTestTx(t)
 		defer cleanup()
 
-		err := tx.Catalog.CreateTable(tx, "test", &database.TableInfo{
+		tb := createTable(t, tx, database.TableInfo{
+			TableName: "test",
 			FieldConstraints: []*database.FieldConstraint{
-				{testutil.ParseDocumentPath(t, "foo"), 0, false, true, false, document.Value{}, false, nil},
-			}})
-		require.NoError(t, err)
-
-		tb, err := tx.Catalog.GetTable(tx, "test")
-		require.NoError(t, err)
+				{testutil.ParseDocumentPath(t, "foo"), 0, false, true, false, document.Value{}, nil, false, nil},
+			},
+		})
 
 		doc := document.NewFieldBuffer().
 			Add("bar", document.NewIntegerValue(10))
@@ -710,7 +705,7 @@ func TestTableInsert(t *testing.T) {
 		}
 
 		// insert
-		_, err = tb.InsertWithConflictResolution(doc, onConflict)
+		_, err := tb.InsertWithConflictResolution(doc, onConflict)
 		require.NoError(t, err)
 		require.Equal(t, 1, called)
 	})
@@ -721,7 +716,7 @@ func TestTableInsert(t *testing.T) {
 
 		err := tx.Catalog.CreateTable(tx, "test", &database.TableInfo{
 			FieldConstraints: []*database.FieldConstraint{
-				{testutil.ParseDocumentPath(t, "foo"), 0, true, true, false, document.Value{}, false, nil},
+				{testutil.ParseDocumentPath(t, "foo"), 0, true, true, false, document.Value{}, nil, false, nil},
 			}})
 		require.NoError(t, err)
 
@@ -747,7 +742,7 @@ func TestTableInsert(t *testing.T) {
 
 		err := tx.Catalog.CreateTable(tx, "test", &database.TableInfo{
 			FieldConstraints: []*database.FieldConstraint{
-				{testutil.ParseDocumentPath(t, "foo"), 0, false, true, false, document.Value{}, false, nil},
+				{testutil.ParseDocumentPath(t, "foo"), 0, false, true, false, document.Value{}, nil, false, nil},
 			}})
 		require.NoError(t, err)
 
@@ -858,14 +853,11 @@ func TestTableReplace(t *testing.T) {
 		_, tx, cleanup := newTestTx(t)
 		defer cleanup()
 
-		err := tx.Catalog.CreateTable(tx, "test1", nil)
-		require.NoError(t, err)
-
-		err = tx.Catalog.CreateTable(tx, "test2", nil)
-		require.NoError(t, err)
+		createTable(t, tx, database.TableInfo{TableName: "test1"})
+		createTable(t, tx, database.TableInfo{TableName: "test2"})
 
 		// simple indexes
-		err = tx.Catalog.CreateIndex(tx, &database.IndexInfo{
+		err := tx.Catalog.CreateIndex(tx, &database.IndexInfo{
 			Paths:     []document.Path{document.NewPath("a")},
 			Unique:    true,
 			TableName: "test1",
diff --git a/internal/query/statement/drop_test.go b/internal/query/statement/drop_test.go
index 375b292b..f12dac1f 100644
--- a/internal/query/statement/drop_test.go
+++ b/internal/query/statement/drop_test.go
@@ -43,13 +43,18 @@ func TestDropTable(t *testing.T) {
 	require.NoError(t, err)
 	require.NoError(t, res.Close())
 
-	require.Equal(t, []string{"test2", "test3"}, tables)
+	require.Equal(t, []string{"__genji_sequence", "test2", "test3"}, tables)
 
 	// Assert the unique index test1_a_idx, created upon the creation of the table,
 	// has been dropped as well.
 	_, err = db.QueryDocument("SELECT 1 FROM __genji_catalog WHERE name = 'test1_a_idx'")
 	require.Error(t, err)
 
+	// Assert the docid sequence test1_seq, created upon the creation of the table,
+	// has been dropped as well.
+	// _, err = db.QueryDocument("SELECT 1 FROM __genji_catalog WHERE name = 'test1_seq'")
+	// require.Error(t, err)
+
 	// Dropping a read-only table should fail.
 	err = db.Exec("DROP TABLE __genji_catalog")
 	require.Error(t, err)
diff --git a/internal/query/statement/gen_insert_statements_test.go b/internal/query/statement/gen_insert_statements_test.go
index e2c8315c..c93bc738 100644
--- a/internal/query/statement/gen_insert_statements_test.go
+++ b/internal/query/statement/gen_insert_statements_test.go
@@ -915,7 +915,7 @@ INSERT INTO test_oc (b, c) VALUES (1, 1) ON CONFLICT DO REPLACE;
 		t.Run(`CREATE TABLE test_oc(a INTEGER UNIQUE);`, func(t *testing.T) {
 			q := `
 CREATE TABLE test_oc(a INTEGER UNIQUE);
-CREATE SEQUENCE test_seq;
+CREATE SEQUENCE test_seq1;
 INSERT INTO test_oc (a) VALUES (NEXT VALUE FOR test_seq);
 INSERT INTO test_oc (a) VALUES (NEXT VALUE FOR test_seq), (NEXT VALUE FOR test_seq);
 SELECT * FROM test_oc;
diff --git a/internal/query/statement/gen_insert_statements_test.sql b/internal/query/statement/gen_insert_statements_test.sql
index 6e6d0323..2a5cdd48 100644
--- a/internal/query/statement/gen_insert_statements_test.sql
+++ b/internal/query/statement/gen_insert_statements_test.sql
@@ -347,7 +347,7 @@ INSERT INTO test_oc (b, c) VALUES (1, 1) ON CONFLICT DO REPLACE;
 
 -- test: insert with NEXT VALUE FOR
 CREATE TABLE test_oc(a INTEGER UNIQUE);
-CREATE SEQUENCE test_seq;
+CREATE SEQUENCE test_seq1;
 INSERT INTO test_oc (a) VALUES (NEXT VALUE FOR test_seq);
 INSERT INTO test_oc (a) VALUES (NEXT VALUE FOR test_seq), (NEXT VALUE FOR test_seq);
 SELECT * FROM test_oc;
diff --git a/open_test.go b/open_test.go
index f6d04467..8aac0a8d 100644
--- a/open_test.go
+++ b/open_test.go
@@ -49,41 +49,51 @@ func TestOpen(t *testing.T) {
 	err = res1.Iterate(func(d document.Document) error {
 		count++
 		if count == 1 {
-			testutil.RequireDocJSONEq(t, d, `{"name":"__genji_sequence", "sql":"CREATE TABLE __genji_sequence (name TEXT PRIMARY KEY, seq INTEGER)", "store_name":"X19nZW5qaV9zZXF1ZW5jZQ==", "type":"table"}`)
+			testutil.RequireDocJSONEq(t, d, `{"name":"__genji_catalog_seq", "owner":{"table_name":"__genji_catalog"}, "sql":"CREATE SEQUENCE __genji_catalog_seq CACHE 16", "type":"sequence"}`)
 			return nil
 		}
 
 		if count == 2 {
-			testutil.RequireDocJSONEq(t, d, `{"name":"seqD", "sql":"CREATE SEQUENCE seqD INCREMENT BY 10 MINVALUE 100 START WITH 500 CYCLE", "type":"sequence"}`)
+			testutil.RequireDocJSONEq(t, d, `{"name":"__genji_sequence", "sql":"CREATE TABLE __genji_sequence (name TEXT PRIMARY KEY, seq INTEGER)", "store_name":"X19nZW5qaV9zZXF1ZW5jZQ==", "type":"table"}`)
 			return nil
 		}
 
 		if count == 3 {
-			testutil.RequireDocJSONEq(t, d, `{"name":"tableA", "sql":"CREATE TABLE tableA (a INTEGER NOT NULL UNIQUE, b.c[0].d DOUBLE PRIMARY KEY)", "store_name":"AQ==", "type":"table"}`)
+			testutil.RequireDocJSONEq(t, d, `{"name":"seqD", "sql":"CREATE SEQUENCE seqD INCREMENT BY 10 MINVALUE 100 START WITH 500 CYCLE", "type":"sequence"}`)
 			return nil
 		}
 
 		if count == 4 {
-			testutil.RequireDocJSONEq(t, d, `{"constraint_path":"a", "name":"tableA_a_idx", "sql":"CREATE UNIQUE INDEX tableA_a_idx ON tableA (a)", "store_name":"Ag==", "table_name":"tableA", "type":"index"}`)
+			testutil.RequireDocJSONEq(t, d, `{"name":"tableA", "sql":"CREATE TABLE tableA (a INTEGER NOT NULL UNIQUE, b.c[0].d DOUBLE PRIMARY KEY)", "store_name":"AQ==", "type":"table"}`)
 			return nil
 		}
 
 		if count == 5 {
-			testutil.RequireDocJSONEq(t, d, `{"name":"tableB", "sql":"CREATE TABLE tableB (a TEXT NOT NULL PRIMARY KEY DEFAULT \"hello\")", "store_name":"Aw==", "type":"table"}`)
+			testutil.RequireDocJSONEq(t, d, `{"name":"tableA_a_idx", "constraint_path":"a",  "sql":"CREATE UNIQUE INDEX tableA_a_idx ON tableA (a)", "store_name":"Ag==", "table_name":"tableA", "type":"index"}`)
 			return nil
 		}
 
 		if count == 6 {
-			testutil.RequireDocJSONEq(t, d, `{"name":"tableC", "sql":"CREATE TABLE tableC", "store_name":"BA==", "type":"table"}`)
+			testutil.RequireDocJSONEq(t, d, `{"name":"tableB", "sql":"CREATE TABLE tableB (a TEXT NOT NULL PRIMARY KEY DEFAULT \"hello\")", "store_name":"Aw==", "type":"table"}`)
 			return nil
 		}
 
 		if count == 7 {
+			testutil.RequireDocJSONEq(t, d, `{"name":"tableC", "docid_sequence_name":"tableC_seq", "sql":"CREATE TABLE tableC", "store_name":"BA==", "type":"table"}`)
+			return nil
+		}
+
+		if count == 8 {
 			testutil.RequireDocJSONEq(t, d, `{"name":"tableC_a_b_idx", "sql":"CREATE INDEX tableC_a_b_idx ON tableC (a, b)", "store_name":"BQ==", "table_name":"tableC", "type":"index"}`)
 			return nil
 		}
 
-		return errors.New("more than 7 relations")
+		if count == 9 {
+			testutil.RequireDocJSONEq(t, d, `{"name":"tableC_seq", "owner":{"table_name":"tableC"}, "sql":"CREATE SEQUENCE tableC_seq CACHE 32", "type":"sequence"}`)
+			return nil
+		}
+
+		return errors.New("more than 8 relations")
 	})
 	require.NoError(t, err)
 
@@ -93,5 +103,9 @@ func TestOpen(t *testing.T) {
 
 	d, err = db.QueryDocument("SELECT * FROM __genji_sequence")
 	require.NoError(t, err)
+	testutil.RequireDocJSONEq(t, d, `{"name":"__genji_catalog_seq", "seq":16}`)
+
+	d, err = db.QueryDocument("SELECT * FROM __genji_sequence OFFSET 1")
+	require.NoError(t, err)
 	testutil.RequireDocJSONEq(t, d, `{"name": "seqD", "seq": 500}`)
 }