Repository: ariga/atlas
Pull request #369 (2022-01-05 09:17:21)

sql/schema: initial implementation for fluent api
This DSL is still in an "experiment mode", but I plan to stabilize it after finishing the integration with ent. 


Patch:
diff --git a/sql/mysql/convert.go b/sql/mysql/convert.go
index 72dfa6fa..516e7697 100644
--- a/sql/mysql/convert.go
+++ b/sql/mysql/convert.go
@@ -32,10 +32,23 @@ func FormatType(t schema.Type) (string, error) {
 			f = fmt.Sprintf("%s(%d)", f, t.Size)
 		}
 	case *schema.DecimalType:
-		f = strings.ToLower(t.T)
-		if f == tDecimal || f == tNumeric {
-			// In MySQL, NUMERIC is implemented as DECIMAL.
-			f = fmt.Sprintf("decimal(%d,%d)", t.Precision, t.Scale)
+		if f = strings.ToLower(t.T); f != tDecimal && f != tNumeric {
+			return "", fmt.Errorf("mysql: unexpected decimal type: %q", t.T)
+		}
+		switch p, s := t.Precision, t.Scale; {
+		case p < 0 || s < 0:
+			return "", fmt.Errorf("mysql: decimal type must have precision > 0 and scale >= 0: %d, %d", p, s)
+		case p < s:
+			return "", fmt.Errorf("mysql: decimal type must have precision >= scale: %d < %d", p, s)
+		case p == 0 && s == 0:
+			// The default value for precision is 10 (i.e. decimal(0,0) = decimal(10)).
+			p = 10
+			fallthrough
+		case s == 0:
+			// In standard SQL, the syntax DECIMAL(M) is equivalent to DECIMAL(M,0),
+			f = fmt.Sprintf("decimal(%d)", p)
+		default:
+			f = fmt.Sprintf("decimal(%d,%d)", p, s)
 		}
 	case *schema.EnumType:
 		f = fmt.Sprintf("enum(%s)", formatValues(t.Values))
diff --git a/sql/schema/dsl.go b/sql/schema/dsl.go
new file mode 100644
index 00000000..8ad026d5
--- /dev/null
+++ b/sql/schema/dsl.go
@@ -0,0 +1,590 @@
+// Copyright 2021-present The Atlas Authors. All rights reserved.
+// This source code is licensed under the Apache 2.0 license found
+// in the LICENSE file in the root directory of this source tree.
+
+package schema
+
+import (
+	"reflect"
+)
+
+// The functions and methods below provide a DSL for creating schema resources using
+// a fluent interface. Note that some methods create links between the schema elements.
+
+// New creates a new Schema.
+func New(name string) *Schema {
+	return &Schema{Name: name}
+}
+
+// SetCharset sets or appends the Charset attribute
+// to the schema with the given value.
+func (s *Schema) SetCharset(v string) *Schema {
+	replaceOrAppend(&s.Attrs, &Charset{V: v})
+	return s
+}
+
+// SetCollation sets or appends the Collation attribute
+// to the schema with the given value.
+func (s *Schema) SetCollation(v string) *Schema {
+	replaceOrAppend(&s.Attrs, &Collation{V: v})
+	return s
+}
+
+// SetComment sets or appends the Comment attribute
+// to the schema with the given value.
+func (s *Schema) SetComment(v string) *Schema {
+	replaceOrAppend(&s.Attrs, &Comment{Text: v})
+	return s
+}
+
+// AddAttrs adds additional attributes to the schema.
+func (s *Schema) AddAttrs(attrs ...Attr) *Schema {
+	s.Attrs = append(s.Attrs, attrs...)
+	return s
+}
+
+// SetRealm sets the database/realm of the schema.
+func (s *Schema) SetRealm(r *Realm) *Schema {
+	s.Realm = r
+	return s
+}
+
+// AddTables adds and links the given tables to the schema.
+func (s *Schema) AddTables(tables ...*Table) *Schema {
+	for _, t := range tables {
+		t.SetSchema(s)
+	}
+	s.Tables = append(s.Tables, tables...)
+	return s
+}
+
+// NewTable creates a new Table.
+func NewTable(name string) *Table {
+	return &Table{Name: name}
+}
+
+// SetCharset sets or appends the Charset attribute
+// to the table with the given value.
+func (t *Table) SetCharset(v string) *Table {
+	replaceOrAppend(&t.Attrs, &Charset{V: v})
+	return t
+}
+
+// SetCollation sets or appends the Collation attribute
+// to the table with the given value.
+func (t *Table) SetCollation(v string) *Table {
+	replaceOrAppend(&t.Attrs, &Collation{V: v})
+	return t
+}
+
+// SetComment sets or appends the Comment attribute
+// to the table with the given value.
+func (t *Table) SetComment(v string) *Table {
+	replaceOrAppend(&t.Attrs, &Comment{Text: v})
+	return t
+}
+
+// AddChecks appends the given checks to the attribute list.
+func (t *Table) AddChecks(checks ...*Check) *Table {
+	for _, c := range checks {
+		t.Attrs = append(t.Attrs, c)
+	}
+	return t
+}
+
+// SetSchema sets the schema (named-database) of the table.
+func (t *Table) SetSchema(s *Schema) *Table {
+	t.Schema = s
+	return t
+}
+
+// SetPrimaryKey sets the primary-key of the table.
+func (t *Table) SetPrimaryKey(pk *Index) *Table {
+	pk.Table = t
+	t.PrimaryKey = pk
+	for _, p := range pk.Parts {
+		if p.C == nil {
+			continue
+		}
+		if _, ok := t.Column(p.C.Name); !ok {
+			t.AddColumns(p.C)
+		}
+	}
+	return t
+}
+
+// AddColumns appends the given columns to the table column list.
+func (t *Table) AddColumns(columns ...*Column) *Table {
+	t.Columns = append(t.Columns, columns...)
+	return t
+}
+
+// AddIndexes appends the given indexes to the table index list.
+func (t *Table) AddIndexes(indexes ...*Index) *Table {
+	for _, idx := range indexes {
+		idx.Table = t
+	}
+	t.Indexes = append(t.Indexes, indexes...)
+	return t
+}
+
+// AddForeignKeys appends the given foreign-keys to the table foreign-key list.
+func (t *Table) AddForeignKeys(fks ...*ForeignKey) *Table {
+	for _, fk := range fks {
+		fk.Table = t
+	}
+	t.ForeignKeys = append(t.ForeignKeys, fks...)
+	return t
+}
+
+// AddAttrs adds and additional attributes to the table.
+func (t *Table) AddAttrs(attrs ...Attr) *Table {
+	t.Attrs = append(t.Attrs, attrs...)
+	return t
+}
+
+// NewColumn creates a new column with the given name.
+func NewColumn(name string) *Column {
+	return &Column{Name: name}
+}
+
+// NewNullColumn creates a new nullable column with the given name.
+func NewNullColumn(name string) *Column {
+	return NewColumn(name).
+		SetNull(true)
+}
+
+// NewBoolColumn creates a new BoolType column.
+func NewBoolColumn(name, typ string) *Column {
+	return NewColumn(name).
+		SetType(&BoolType{T: typ})
+}
+
+// NewNullBoolColumn creates a new nullable BoolType column.
+func NewNullBoolColumn(name, typ string) *Column {
+	return NewBoolColumn(name, typ).
+		SetNull(true)
+}
+
+// NewIntColumn creates a new IntegerType column.
+func NewIntColumn(name, typ string) *Column {
+	return NewColumn(name).
+		SetType(&IntegerType{T: typ})
+}
+
+// NewNullIntColumn creates a new nullable IntegerType column.
+func NewNullIntColumn(name, typ string) *Column {
+	return NewIntColumn(name, typ).
+		SetNull(true)
+}
+
+// NewUintColumn creates a new unsigned IntegerType column.
+func NewUintColumn(name, typ string) *Column {
+	return NewColumn(name).
+		SetType(&IntegerType{T: typ, Unsigned: true})
+}
+
+// NewNullUintColumn creates a new nullable unsigned IntegerType column.
+func NewNullUintColumn(name, typ string) *Column {
+	return NewUintColumn(name, typ).
+		SetNull(true)
+}
+
+// EnumOption allows configuring EnumType using functional options.
+type EnumOption func(*EnumType)
+
+// EnumName configures the name of the name. This option
+// is useful for databases like PostgreSQL that supports
+// user-defined types for enums.
+func EnumName(name string) EnumOption {
+	return func(e *EnumType) {
+		e.T = name
+	}
+}
+
+// EnumValues configures the values of the enum.
+func EnumValues(values ...string) EnumOption {
+	return func(e *EnumType) {
+		e.Values = values
+	}
+}
+
+// NewEnumColumn creates a new EnumType column.
+func NewEnumColumn(name string, opts ...EnumOption) *Column {
+	t := &EnumType{}
+	for _, opt := range opts {
+		opt(t)
+	}
+	return NewColumn(name).SetType(t)
+}
+
+// NewNullEnumColumn creates a new nullable EnumType column.
+func NewNullEnumColumn(name string, opts ...EnumOption) *Column {
+	return NewEnumColumn(name, opts...).
+		SetNull(true)
+}
+
+// BinaryOption allows configuring BinaryType using functional options.
+type BinaryOption func(*BinaryType)
+
+// BinarySize configures the size of the binary type.
+func BinarySize(size int) BinaryOption {
+	return func(b *BinaryType) {
+		b.Size = size
+	}
+}
+
+// NewBinaryColumn creates a new BinaryType column.
+func NewBinaryColumn(name, typ string, opts ...BinaryOption) *Column {
+	t := &BinaryType{T: typ}
+	for _, opt := range opts {
+		opt(t)
+	}
+	return NewColumn(name).SetType(t)
+}
+
+// NewNullBinaryColumn creates a new nullable BinaryType column.
+func NewNullBinaryColumn(name, typ string, opts ...BinaryOption) *Column {
+	return NewBinaryColumn(name, typ, opts...).
+		SetNull(true)
+}
+
+// StringOption allows configuring StringType using functional options.
+type StringOption func(*StringType)
+
+// StringSize configures the size of the string type.
+func StringSize(size int) StringOption {
+	return func(b *StringType) {
+		b.Size = size
+	}
+}
+
+// NewStringColumn creates a new StringType column.
+func NewStringColumn(name, typ string, opts ...StringOption) *Column {
+	t := &StringType{T: typ}
+	for _, opt := range opts {
+		opt(t)
+	}
+	return NewColumn(name).SetType(t)
+}
+
+// NewNullStringColumn creates a new nullable StringType column.
+func NewNullStringColumn(name, typ string, opts ...StringOption) *Column {
+	return NewStringColumn(name, typ, opts...).
+		SetNull(true)
+}
+
+// DecimalOption allows configuring DecimalType using functional options.
+type DecimalOption func(*DecimalType)
+
+// DecimalPrecision configures the precision of the decimal type.
+func DecimalPrecision(precision int) DecimalOption {
+	return func(b *DecimalType) {
+		b.Precision = precision
+	}
+}
+
+// DecimalScale configures the scale of the decimal type.
+func DecimalScale(scale int) DecimalOption {
+	return func(b *DecimalType) {
+		b.Scale = scale
+	}
+}
+
+// NewDecimalColumn creates a new DecimalType column.
+func NewDecimalColumn(name, typ string, opts ...DecimalOption) *Column {
+	t := &DecimalType{T: typ}
+	for _, opt := range opts {
+		opt(t)
+	}
+	return NewColumn(name).SetType(t)
+}
+
+// NewNullDecimalColumn creates a new nullable DecimalType column.
+func NewNullDecimalColumn(name, typ string, opts ...DecimalOption) *Column {
+	return NewDecimalColumn(name, typ, opts...).
+		SetNull(true)
+}
+
+// FloatOption allows configuring FloatType using functional options.
+type FloatOption func(*FloatType)
+
+// FloatPrecision configures the precision of the float type.
+func FloatPrecision(precision int) FloatOption {
+	return func(b *FloatType) {
+		b.Precision = precision
+	}
+}
+
+// NewFloatColumn creates a new FloatType column.
+func NewFloatColumn(name, typ string, opts ...FloatOption) *Column {
+	t := &FloatType{T: typ}
+	for _, opt := range opts {
+		opt(t)
+	}
+	return NewColumn(name).SetType(t)
+}
+
+// NewNullFloatColumn creates a new nullable FloatType column.
+func NewNullFloatColumn(name, typ string, opts ...FloatOption) *Column {
+	return NewFloatColumn(name, typ, opts...).
+		SetNull(true)
+}
+
+// NewTimeColumn creates a new TimeType column.
+func NewTimeColumn(name, typ string) *Column {
+	return NewColumn(name).
+		SetType(&TimeType{T: typ})
+}
+
+// NewNullTimeColumn creates a new nullable TimeType column.
+func NewNullTimeColumn(name, typ string) *Column {
+	return NewTimeColumn(name, typ).
+		SetNull(true)
+}
+
+// NewJSONColumn creates a new JSONType column.
+func NewJSONColumn(name, typ string) *Column {
+	return NewColumn(name).
+		SetType(&JSONType{T: typ})
+}
+
+// NewNullJSONColumn creates a new nullable JSONType column.
+func NewNullJSONColumn(name, typ string) *Column {
+	return NewJSONColumn(name, typ).
+		SetNull(true)
+}
+
+// NewSpatialColumn creates a new SpatialType column.
+func NewSpatialColumn(name, typ string) *Column {
+	return NewColumn(name).
+		SetType(&SpatialType{T: typ})
+}
+
+// NewNullSpatialColumn creates a new nullable SpatialType column.
+func NewNullSpatialColumn(name, typ string) *Column {
+	return NewSpatialColumn(name, typ).
+		SetNull(true)
+}
+
+// SetNull configures the nullability of the column
+func (c *Column) SetNull(b bool) *Column {
+	if c.Type == nil {
+		c.Type = &ColumnType{}
+	}
+	c.Type.Null = b
+	return c
+}
+
+// SetType configures the type of the column
+func (c *Column) SetType(t Type) *Column {
+	if c.Type == nil {
+		c.Type = &ColumnType{}
+	}
+	c.Type.Type = t
+	return c
+}
+
+// SetDefault configures the default of the column
+func (c *Column) SetDefault(x Expr) *Column {
+	c.Default = x
+	return c
+}
+
+// SetCharset sets or appends the Charset attribute
+// to the column with the given value.
+func (c *Column) SetCharset(v string) *Column {
+	replaceOrAppend(&c.Attrs, &Charset{V: v})
+	return c
+}
+
+// SetCollation sets or appends the Collation attribute
+// to the column with the given value.
+func (c *Column) SetCollation(v string) *Column {
+	replaceOrAppend(&c.Attrs, &Collation{V: v})
+	return c
+}
+
+// SetComment sets or appends the Comment attribute
+// to the column with the given value.
+func (c *Column) SetComment(v string) *Column {
+	replaceOrAppend(&c.Attrs, &Comment{Text: v})
+	return c
+}
+
+// AddAttrs adds additional attributes to the column.
+func (c *Column) AddAttrs(attrs ...Attr) *Column {
+	c.Attrs = append(c.Attrs, attrs...)
+	return c
+}
+
+// NewIndex creates a new index with the given name.
+func NewIndex(name string) *Index {
+	return &Index{Name: name}
+}
+
+// NewUniqueIndex creates a new unique index with the given name.
+func NewUniqueIndex(name string) *Index {
+	return NewIndex(name).SetUnique(true)
+}
+
+// NewPrimaryKey creates a new primary-key index
+// for the given columns.
+func NewPrimaryKey(columns ...*Column) *Index {
+	return new(Index).SetUnique(true).AddColumns(columns...)
+}
+
+// SetName configures the name of the index.
+func (i *Index) SetName(name string) *Index {
+	i.Name = name
+	return i
+}
+
+// SetUnique configures the uniqueness of the index.
+func (i *Index) SetUnique(b bool) *Index {
+	i.Unique = b
+	return i
+}
+
+// SetTable configures the table of the index.
+func (i *Index) SetTable(t *Table) *Index {
+	i.Table = t
+	return i
+}
+
+// AddAttrs adds additional attributes to the index.
+func (i *Index) AddAttrs(attrs ...Attr) *Index {
+	i.Attrs = append(i.Attrs, attrs...)
+	return i
+}
+
+// AddColumns adds the columns to index parts.
+func (i *Index) AddColumns(columns ...*Column) *Index {
+	for _, c := range columns {
+		if !c.hasIndex(i) {
+			c.Indexes = append(c.Indexes, i)
+		}
+		i.Parts = append(i.Parts, &IndexPart{SeqNo: len(i.Parts), C: c})
+	}
+	return i
+}
+
+func (c *Column) hasIndex(idx *Index) bool {
+	for i := range c.Indexes {
+		if c.Indexes[i] == idx {
+			return true
+		}
+	}
+	return false
+}
+
+// AddExprs adds the expressions to index parts.
+func (i *Index) AddExprs(exprs ...Expr) *Index {
+	for _, x := range exprs {
+		i.Parts = append(i.Parts, &IndexPart{SeqNo: len(i.Parts), X: x})
+	}
+	return i
+}
+
+// AddParts appends the given parts.
+func (i *Index) AddParts(parts ...*IndexPart) *Index {
+	for _, p := range parts {
+		if p.C != nil && !p.C.hasIndex(i) {
+			p.C.Indexes = append(p.C.Indexes, i)
+		}
+		i.Parts = append(i.Parts, p)
+	}
+	return i
+}
+
+// NewIndexPart creates a new index part.
+func NewIndexPart(seqno int) *IndexPart {
+	return &IndexPart{SeqNo: seqno}
+}
+
+// AddAttrs adds and additional attributes to the index-part.
+func (p *IndexPart) AddAttrs(attrs ...Attr) *IndexPart {
+	p.Attrs = append(p.Attrs, attrs...)
+	return p
+}
+
+// SetColumn sets the column of the index-part.
+func (p *IndexPart) SetColumn(c *Column) *IndexPart {
+	p.C = c
+	return p
+}
+
+// SetExpr sets the expression of the index-part.
+func (p *IndexPart) SetExpr(x Expr) *IndexPart {
+	p.X = x
+	return p
+}
+
+// NewForeignKey creates a new foreign-key with
+// the given constraints/symbol name.
+func NewForeignKey(symbol string) *ForeignKey {
+	return &ForeignKey{Symbol: symbol}
+}
+
+// SetTable configures the table that holds the foreign-key (child table).
+func (f *ForeignKey) SetTable(t *Table) *ForeignKey {
+	f.Table = t
+	return f
+}
+
+// AddColumns appends columns to the child-table columns.
+func (f *ForeignKey) AddColumns(columns ...*Column) *ForeignKey {
+	for _, c := range columns {
+		if !c.hasForeignKey(f) {
+			c.ForeignKeys = append(c.ForeignKeys, f)
+		}
+	}
+	f.Columns = append(f.Columns, columns...)
+	return f
+}
+
+func (c *Column) hasForeignKey(fk *ForeignKey) bool {
+	for i := range c.ForeignKeys {
+		if c.ForeignKeys[i] == fk {
+			return true
+		}
+	}
+	return false
+}
+
+// SetRefTable configures the referenced/parent table.
+func (f *ForeignKey) SetRefTable(t *Table) *ForeignKey {
+	f.RefTable = t
+	return f
+}
+
+// AddRefColumns appends columns to the parent-table columns.
+func (f *ForeignKey) AddRefColumns(columns ...*Column) *ForeignKey {
+	f.RefColumns = append(f.RefColumns, columns...)
+	return f
+}
+
+// SetOnUpdate sets the ON UPDATE constraint action.
+func (f *ForeignKey) SetOnUpdate(o ReferenceOption) *ForeignKey {
+	f.OnUpdate = o
+	return f
+}
+
+// SetOnDelete sets the ON DELETE constraint action.
+func (f *ForeignKey) SetOnDelete(o ReferenceOption) *ForeignKey {
+	f.OnDelete = o
+	return f
+}
+
+// replaceOrAppend searches an attribute of the same type as v in
+// the list and replaces it. Otherwise, v is appended to the list.
+func replaceOrAppend(attrs *[]Attr, v Attr) {
+	t := reflect.TypeOf(v)
+	for i := range *attrs {
+		if reflect.TypeOf((*attrs)[i]) == t {
+			(*attrs)[i] = v
+			return
+		}
+	}
+	*attrs = append(*attrs, v)
+}
diff --git a/sql/schema/schema.go b/sql/schema/schema.go
index 13f8e421..44bc6d78 100644
--- a/sql/schema/schema.go
+++ b/sql/schema/schema.go
@@ -33,11 +33,13 @@ type (
 
 	// A Column represents a column definition.
 	Column struct {
-		Name        string
-		Type        *ColumnType
-		Default     Expr
-		Attrs       []Attr
-		Indexes     []*Index
+		Name    string
+		Type    *ColumnType
+		Default Expr
+		Attrs   []Attr
+		Indexes []*Index
+		// Foreign keys that this column is
+		// part of their child columns.
 		ForeignKeys []*ForeignKey
 	}
 


Test patch:
diff --git a/internal/integration/mysql_test.go b/internal/integration/mysql_test.go
index ba556788..3499af41 100644
--- a/internal/integration/mysql_test.go
+++ b/internal/integration/mysql_test.go
@@ -155,7 +155,11 @@ func TestMySQL_AddColumns(t *testing.T) {
 			&schema.Column{Name: "k", Type: &schema.ColumnType{Raw: "text", Type: &schema.StringType{T: "text"}}},
 			&schema.Column{Name: "l", Type: &schema.ColumnType{Raw: "longtext", Type: &schema.StringType{T: "longtext"}}},
 			&schema.Column{Name: "m", Type: &schema.ColumnType{Type: &schema.DecimalType{T: "decimal", Precision: 10, Scale: 6}}},
+			&schema.Column{Name: "m1", Type: &schema.ColumnType{Type: &schema.DecimalType{T: "decimal"}}},
+			&schema.Column{Name: "m2", Type: &schema.ColumnType{Type: &schema.DecimalType{T: "decimal", Precision: 2}}},
 			&schema.Column{Name: "n", Type: &schema.ColumnType{Type: &schema.DecimalType{T: "numeric", Precision: 10, Scale: 2}}},
+			&schema.Column{Name: "n1", Type: &schema.ColumnType{Type: &schema.DecimalType{T: "numeric"}}},
+			&schema.Column{Name: "n2", Type: &schema.ColumnType{Type: &schema.DecimalType{T: "numeric", Precision: 2}}},
 			&schema.Column{Name: "o", Type: &schema.ColumnType{Type: &schema.FloatType{T: "float", Precision: 2}}},
 			&schema.Column{Name: "p", Type: &schema.ColumnType{Type: &schema.FloatType{T: "double", Precision: 14}}},
 			&schema.Column{Name: "q", Type: &schema.ColumnType{Type: &schema.FloatType{T: "real", Precision: 14}}},
@@ -169,7 +173,7 @@ func TestMySQL_AddColumns(t *testing.T) {
 			&schema.Column{Name: "z", Type: &schema.ColumnType{Type: &schema.TimeType{T: "timestamp"}}, Default: &schema.RawExpr{X: "CURRENT_TIMESTAMP"}},
 		)
 		changes := t.diff(t.loadUsers(), usersT)
-		require.Len(t, changes, 24)
+		require.Len(t, changes, 28)
 		t.migrate(&schema.ModifyTable{T: usersT, Changes: changes})
 		ensureNoChange(t, usersT)
 	})
diff --git a/sql/schema/dsl_test.go b/sql/schema/dsl_test.go
new file mode 100644
index 00000000..549d48cd
--- /dev/null
+++ b/sql/schema/dsl_test.go
@@ -0,0 +1,157 @@
+// Copyright 2021-present The Atlas Authors. All rights reserved.
+// This source code is licensed under the Apache 2.0 license found
+// in the LICENSE file in the root directory of this source tree.
+
+package schema_test
+
+import (
+	"testing"
+
+	"ariga.io/atlas/sql/schema"
+
+	"github.com/stretchr/testify/require"
+)
+
+func TestTable_AddColumns(t *testing.T) {
+	users := schema.NewTable("users").
+		SetComment("users table").
+		AddColumns(
+			schema.NewBoolColumn("active", "bool"),
+			schema.NewDecimalColumn("age", "decimal"),
+			schema.NewNullStringColumn("name", "varchar", schema.StringSize(255)),
+		)
+	require.Equal(
+		t,
+		&schema.Table{
+			Name: "users",
+			Attrs: []schema.Attr{
+				&schema.Comment{Text: "users table"},
+			},
+			Columns: []*schema.Column{
+				{Name: "active", Type: &schema.ColumnType{Type: &schema.BoolType{T: "bool"}}},
+				{Name: "age", Type: &schema.ColumnType{Type: &schema.DecimalType{T: "decimal"}}},
+				{Name: "name", Type: &schema.ColumnType{Null: true, Type: &schema.StringType{T: "varchar", Size: 255}}},
+			},
+		},
+		users,
+	)
+}
+
+func TestSchema_AddTables(t *testing.T) {
+	userColumns := []*schema.Column{
+		schema.NewIntColumn("id", "int"),
+		schema.NewBoolColumn("active", "boolean"),
+		schema.NewNullStringColumn("name", "varchar", schema.StringSize(255)),
+	}
+	users := schema.NewTable("users").
+		AddColumns(userColumns...).
+		SetPrimaryKey(schema.NewPrimaryKey(userColumns[0])).
+		SetComment("users table").
+		AddIndexes(
+			schema.NewUniqueIndex("unique_name").AddColumns(userColumns[2]),
+		)
+	postColumns := []*schema.Column{
+		schema.NewIntColumn("id", "int"),
+		schema.NewStringColumn("text", "longtext"),
+		schema.NewNullIntColumn("author_id", "int"),
+	}
+	posts := schema.NewTable("posts").
+		AddColumns(postColumns...).
+		SetPrimaryKey(schema.NewPrimaryKey(postColumns[0])).
+		SetComment("posts table").
+		AddForeignKeys(
+			schema.NewForeignKey("author_id").
+				AddColumns(postColumns[2]).
+				SetRefTable(users).
+				AddRefColumns(userColumns[0]).
+				SetOnDelete(schema.Cascade).
+				SetOnUpdate(schema.SetNull),
+		)
+	require.Equal(
+		t,
+		func() *schema.Schema {
+			s := &schema.Schema{Name: "public"}
+			users := &schema.Table{
+				Name:   "users",
+				Schema: s,
+				Attrs: []schema.Attr{
+					&schema.Comment{Text: "users table"},
+				},
+				Columns: []*schema.Column{
+					{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "int"}}},
+					{Name: "active", Type: &schema.ColumnType{Type: &schema.BoolType{T: "boolean"}}},
+					{Name: "name", Type: &schema.ColumnType{Null: true, Type: &schema.StringType{T: "varchar", Size: 255}}},
+				},
+			}
+			s.Tables = append(s.Tables, users)
+			users.PrimaryKey = &schema.Index{Unique: true, Parts: []*schema.IndexPart{{C: users.Columns[0]}}}
+			users.PrimaryKey.Table = users
+			users.Columns[0].Indexes = append(users.Columns[0].Indexes, users.PrimaryKey)
+			users.Indexes = append(users.Indexes, &schema.Index{Name: "unique_name", Unique: true, Parts: []*schema.IndexPart{{C: users.Columns[2]}}})
+			users.Indexes[0].Table = users
+			users.Columns[2].Indexes = users.Indexes
+
+			posts := &schema.Table{
+				Name:   "posts",
+				Schema: s,
+				Attrs: []schema.Attr{
+					&schema.Comment{Text: "posts table"},
+				},
+				Columns: []*schema.Column{
+					{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "int"}}},
+					{Name: "text", Type: &schema.ColumnType{Type: &schema.StringType{T: "longtext"}}},
+					{Name: "author_id", Type: &schema.ColumnType{Null: true, Type: &schema.IntegerType{T: "int"}}},
+				},
+			}
+			s.Tables = append(s.Tables, posts)
+			posts.PrimaryKey = &schema.Index{Unique: true, Parts: []*schema.IndexPart{{C: posts.Columns[0]}}}
+			posts.PrimaryKey.Table = posts
+			posts.Columns[0].Indexes = append(posts.Columns[0].Indexes, posts.PrimaryKey)
+			posts.ForeignKeys = append(posts.ForeignKeys, &schema.ForeignKey{
+				Symbol:     "author_id",
+				Table:      posts,
+				Columns:    posts.Columns[2:],
+				RefTable:   users,
+				RefColumns: users.Columns[0:1],
+				OnDelete:   schema.Cascade,
+				OnUpdate:   schema.SetNull,
+			})
+			posts.Columns[2].ForeignKeys = posts.ForeignKeys
+			return s
+		}(),
+		schema.New("public").AddTables(users, posts),
+	)
+}
+
+func TestSchema_SetCharset(t *testing.T) {
+	s := schema.New("public")
+	require.Empty(t, s.Attrs)
+	s.SetCharset("utf8mb4")
+	require.Len(t, s.Attrs, 1)
+	require.Equal(t, &schema.Charset{V: "utf8mb4"}, s.Attrs[0])
+	s.SetCharset("latin1")
+	require.Len(t, s.Attrs, 1)
+	require.Equal(t, &schema.Charset{V: "latin1"}, s.Attrs[0])
+}
+
+func TestSchema_SetCollation(t *testing.T) {
+	s := schema.New("public")
+	require.Empty(t, s.Attrs)
+	s.SetCollation("utf8mb4_general_ci")
+	require.Len(t, s.Attrs, 1)
+	require.Equal(t, &schema.Collation{V: "utf8mb4_general_ci"}, s.Attrs[0])
+	s.SetCollation("latin1_swedish_ci")
+	require.Len(t, s.Attrs, 1)
+	require.Equal(t, &schema.Collation{V: "latin1_swedish_ci"}, s.Attrs[0])
+}
+
+func TestSchema_SetComment(t *testing.T) {
+	s := schema.New("public")
+	require.Empty(t, s.Attrs)
+	s.SetComment("1")
+	require.Len(t, s.Attrs, 1)
+	require.Equal(t, &schema.Comment{Text: "1"}, s.Attrs[0])
+	s.SetComment("2")
+	require.Len(t, s.Attrs, 1)
+	require.Equal(t, &schema.Comment{Text: "2"}, s.Attrs[0])
+}