Repository: ariga/atlas
Pull request #220 (2021-11-14 11:50:24)

schema/schemaspec: support string and bool lists
More work towards supporting primitive types in HCL lists. 

Next PR will include support for numeric types. 


Patch:
diff --git a/.github/workflows/ci.yml b/.github/workflows/ci.yml
index d4fcaaf0..85f4322d 100644
--- a/.github/workflows/ci.yml
+++ b/.github/workflows/ci.yml
@@ -110,7 +110,7 @@ jobs:
           POSTGRES_DB: test
           POSTGRES_PASSWORD: pass
         ports:
-          - 5433:5432
+          - 5432:5432
         options: >-
           --health-cmd pg_isready
           --health-interval 10s
@@ -118,6 +118,18 @@ jobs:
           --health-retries 5
       postgres13:
         image: postgres:13.1
+        env:
+          POSTGRES_DB: test
+          POSTGRES_PASSWORD: pass
+        ports:
+          - 5433:5432
+        options: >-
+          --health-cmd pg_isready
+          --health-interval 10s
+          --health-timeout 5s
+          --health-retries 5
+      postgres14:
+        image: postgres:14
         env:
           POSTGRES_DB: test
           POSTGRES_PASSWORD: pass
diff --git a/cmd/atlas/main.go b/cmd/atlas/main.go
index 26489b5e..2e520180 100644
--- a/cmd/atlas/main.go
+++ b/cmd/atlas/main.go
@@ -3,6 +3,8 @@ package main
 import (
 	"os"
 
+	_ "github.com/go-sql-driver/mysql"
+	_ "github.com/lib/pq"
 	"github.com/spf13/cobra"
 )
 
diff --git a/internal/integration/docker-compose.yaml b/internal/integration/docker-compose.yaml
index b4229e34..2c49a632 100644
--- a/internal/integration/docker-compose.yaml
+++ b/internal/integration/docker-compose.yaml
@@ -65,7 +65,7 @@ services:
     healthcheck:
       test: pg_isready -U postgres
     ports:
-      - 5433:5432
+      - 5432:5432
 
   postgres13:
     platform: linux/amd64
@@ -76,4 +76,15 @@ services:
     healthcheck:
       test: pg_isready -U postgres
     ports:
-      - 5434:5432
\ No newline at end of file
+      - 5433:5432
+
+  postgres14:
+    platform: linux/amd64
+    image: postgres:13
+    environment:
+      POSTGRES_DB: test
+      POSTGRES_PASSWORD: pass
+    healthcheck:
+      test: pg_isready -U postgres
+    ports:
+      - 5434:5432
diff --git a/schema/schemaspec/extension.go b/schema/schemaspec/extension.go
index faa0b4e5..700c6c4f 100644
--- a/schema/schemaspec/extension.go
+++ b/schema/schemaspec/extension.go
@@ -204,20 +204,41 @@ func setField(field reflect.Value, attr *Attr) error {
 }
 
 // setSliceAttr sets the value of attr to the slice field. This function expects both the target field
-// and the source attr to be slices. Currently, only slices of type *Ref are supported.
-// TODO(rotemtam): support also strings, booleans and numeric values.
+// and the source attr to be slices.
 func setSliceAttr(field reflect.Value, attr *Attr) error {
 	lst, ok := attr.V.(*ListValue)
 	if !ok {
 		return fmt.Errorf("schemaspec: field is of type slice but attr %q does not contain a ListValue", attr.K)
 	}
 	typ := field.Type().Elem()
-	if typ != reflect.TypeOf(&Ref{}) {
-		return fmt.Errorf("schemaspec: currently on ref slice values supported, got %s", typ)
-	}
+
 	slc := reflect.MakeSlice(reflect.SliceOf(typ), 0, len(lst.V))
-	for _, c := range lst.V {
-		slc = reflect.Append(slc, reflect.ValueOf(c))
+	switch typ.Kind() {
+	case reflect.String:
+		s, err := attr.Strings()
+		if err != nil {
+			return fmt.Errorf("cannot read attribute %q as string list: %w", attr.K, err)
+		}
+		for _, item := range s {
+			slc = reflect.Append(slc, reflect.ValueOf(item))
+		}
+	case reflect.Bool:
+		bools, err := attr.Bools()
+		if err != nil {
+			return fmt.Errorf("cannot read attribute %q as bool list: %w", attr.K, err)
+		}
+		for _, item := range bools {
+			slc = reflect.Append(slc, reflect.ValueOf(item))
+		}
+	case reflect.Ptr:
+		if typ != reflect.TypeOf(&Ref{}) {
+			return fmt.Errorf("only pointers to refs supported, got %s", typ)
+		}
+		for _, c := range lst.V {
+			slc = reflect.Append(slc, reflect.ValueOf(c))
+		}
+	default:
+		return fmt.Errorf("slice of unsupported kind: %q", typ.Kind())
 	}
 	field.Set(slc)
 	return nil
@@ -320,17 +341,32 @@ func scanAttr(key string, r *Resource, field reflect.Value) error {
 }
 
 // scanSliceAttr sets an Attr named "key" into the Resource r, by converting
-// the value stored in "field" into a *ListValue. Currently, only slices of *Ref
-// values are supported. TODO(rotemtam): support strings, booleans and numeric values.
+// the value stored in "field" into a *ListValue.
 func scanSliceAttr(key string, r *Resource, field reflect.Value) error {
 	typ := field.Type()
-	if typ.Kind() != reflect.Slice || typ.Elem() != reflect.TypeOf(&Ref{}) {
-		return fmt.Errorf("schemaspec: currently on ref slice values supported, got %s", typ)
-	}
 	lst := &ListValue{}
-	for i := 0; i < field.Len(); i++ {
-		item := field.Index(i).Interface().(*Ref)
-		lst.V = append(lst.V, item)
+
+	switch typ.Elem().Kind() {
+	case reflect.String:
+		for i := 0; i < field.Len(); i++ {
+			item := field.Index(i).Interface().(string)
+			lst.V = append(lst.V, &LiteralValue{V: strconv.Quote(item)})
+		}
+	case reflect.Bool:
+		for i := 0; i < field.Len(); i++ {
+			item := field.Index(i).Interface().(bool)
+			lst.V = append(lst.V, &LiteralValue{V: strconv.FormatBool(item)})
+		}
+	case reflect.Ptr:
+		if typ.Elem() != reflect.TypeOf(&Ref{}) {
+			return fmt.Errorf("schemaspec: currently on ref slice values supported, got %s", typ)
+		}
+		for i := 0; i < field.Len(); i++ {
+			item := field.Index(i).Interface().(*Ref)
+			lst.V = append(lst.V, item)
+		}
+	default:
+		return fmt.Errorf("unsupported kind %q for %q", typ.Kind(), key)
 	}
 	r.SetAttr(&Attr{
 		K: key,
diff --git a/sql/internal/specutil/convert.go b/sql/internal/specutil/convert.go
index 2332131b..5f9bb1c2 100644
--- a/sql/internal/specutil/convert.go
+++ b/sql/internal/specutil/convert.go
@@ -212,6 +212,7 @@ func FromSchema(s *schema.Schema, fn TableSpecFunc) (*sqlspec.Schema, []*sqlspec
 		if err != nil {
 			return nil, nil, err
 		}
+		table.SchemaName = s.Name
 		tables = append(tables, table)
 	}
 	return spec, tables, nil
diff --git a/sql/internal/sqlx/diff.go b/sql/internal/sqlx/diff.go
index 3a1d414e..f0d3e4f4 100644
--- a/sql/internal/sqlx/diff.go
+++ b/sql/internal/sqlx/diff.go
@@ -49,15 +49,14 @@ type (
 		ReferenceChanged(from, to schema.ReferenceOption) bool
 	}
 
-	// A Normalizer wraps the Normalize method for normalizing table
-	// elements that were inspected from the database, or were defined
-	// by the users to a standard form.
+	// A Normalizer wraps the Normalize method for normalizing the from and to tables before
+	// running diffing. The "from" usually represents the inspected database state (current),
+	// and the second represents the desired state.
 	//
-	// If the DiffDriver implements the Normalizer interface, TableDiff
-	// normalizes its table inputs before starting the diff process.
+	// If the DiffDriver implements the Normalizer interface, TableDiff normalizes its table
+	// inputs before starting the diff process.
 	Normalizer interface {
-		// Normalize normalizes a list of tables.
-		Normalize(...*schema.Table)
+		Normalize(from, to *schema.Table)
 	}
 )
 
diff --git a/sql/mysql/diff.go b/sql/mysql/diff.go
index b492cc1e..27c81986 100644
--- a/sql/mysql/diff.go
+++ b/sql/mysql/diff.go
@@ -124,6 +124,24 @@ func (*diff) ReferenceChanged(from, to schema.ReferenceOption) bool {
 	return from != to
 }
 
+// Normalize implements the sqlx.Normalizer interface.
+func (d *diff) Normalize(from, to *schema.Table) {
+	for i, idx := range from.Indexes {
+		if _, ok := to.Index(idx.Name); ok {
+			continue
+		}
+		// MySQL requires that foreign key columns be indexed; Therefore, if the child
+		// table is defined on non-indexed columns, an index is automatically created
+		// to satisfy the constraint.
+		// Therefore, if no such key was defined on the desired state, the diff will
+		// recommend to drop it on migration. Therefore, we fix it by dropping it from
+		// the current state manually.
+		if keySupportsFK(from, idx) {
+			from.Indexes = append(from.Indexes[:i], from.Indexes[i+1:]...)
+		}
+	}
+}
+
 // collationChange returns the schema change for migrating the collation if
 // it was changed and its not the default attribute inherited from its parent.
 func (d *diff) collationChange(from, top, to []schema.Attr) schema.Change {
@@ -360,3 +378,24 @@ func displayWidth(attr []schema.Attr) *DisplayWidth {
 	}
 	return d
 }
+
+// keySupportsFK reports if the index key was created automatically by MySQL
+// to support the constraint. See sql/sql_table.cc#find_fk_supporting_key.
+func keySupportsFK(t *schema.Table, idx *schema.Index) bool {
+	if _, ok := t.ForeignKey(idx.Name); ok {
+		return true
+	}
+search:
+	for _, fk := range t.ForeignKeys {
+		if len(fk.Columns) != len(idx.Parts) {
+			continue
+		}
+		for i, c := range fk.Columns {
+			if idx.Parts[i].C == nil || idx.Parts[i].C.Name != c.Name {
+				continue search
+			}
+		}
+		return true
+	}
+	return false
+}
diff --git a/sql/mysql/migrate.go b/sql/mysql/migrate.go
index 6a4b84ce..727319fb 100644
--- a/sql/mysql/migrate.go
+++ b/sql/mysql/migrate.go
@@ -92,7 +92,7 @@ func (m *migrate) dropTable(ctx context.Context, drop *schema.DropTable) error {
 // modifyTable builds and executes the queries for bringing the table into its modified state.
 func (m *migrate) modifyTable(ctx context.Context, modify *schema.ModifyTable) error {
 	var changes [2][]schema.Change
-	for _, change := range modify.Changes {
+	for _, change := range skipAutoChanges(modify.Changes) {
 		switch change := change.(type) {
 		// Constraints should be dropped before dropping columns, because if a column
 		// is a part of multi-column constraints (like, unique index), ALTER TABLE
@@ -290,6 +290,33 @@ func Build(phrase string) *sqlx.Builder {
 	return b.P(phrase)
 }
 
+// skipAutoChanges filters unnecessary changes that are automatically
+// happened by the database when ALTER TABLE is executed.
+func skipAutoChanges(changes []schema.Change) []schema.Change {
+	dropC := make(map[string]bool)
+	for _, c := range changes {
+		if c, ok := c.(*schema.DropColumn); ok {
+			dropC[c.C.Name] = true
+		}
+	}
+search:
+	for i, c := range changes {
+		// Simple case for skipping key dropping, if its columns are dropped.
+		// https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-add-drop-column
+		c, ok := c.(*schema.DropIndex)
+		if !ok {
+			continue
+		}
+		for _, p := range c.I.Parts {
+			if p.C == nil || !dropC[p.C.Name] {
+				continue search
+			}
+		}
+		changes = append(changes[:i], changes[i+1:]...)
+	}
+	return changes
+}
+
 func quote(s string) string {
 	if strings.HasPrefix(s, "'") && strings.HasSuffix(s, "'") ||
 		strings.HasPrefix(s, "\"") && strings.HasSuffix(s, "\"") {
diff --git a/sql/mysql/sqlspec.go b/sql/mysql/sqlspec.go
index 9e486945..2f1107cb 100644
--- a/sql/mysql/sqlspec.go
+++ b/sql/mysql/sqlspec.go
@@ -301,16 +301,16 @@ func binarySpec(t *schema.BinaryType) (*sqlspec.Column, error) {
 		size := specutil.LitAttr("size", strconv.Itoa(t.Size))
 		return specutil.NewCol("", "binary", size), nil
 	}
-	return nil, errors.New("mysql: schema binary failed to convert")
+	return nil, fmt.Errorf("mysql: schema binary failed to convert %q", t.T)
 }
 
 func stringSpec(t *schema.StringType) (*sqlspec.Column, error) {
 	switch t.T {
-	case tVarchar, tMediumText, tLongText:
+	case tVarchar, tMediumText, tLongText, tTinyText, tText, tChar:
 		s := strconv.Itoa(t.Size)
 		return specutil.NewCol("", "string", specutil.LitAttr("size", s)), nil
 	}
-	return nil, errors.New("mysql: schema string failed to convert")
+	return nil, fmt.Errorf("mysql: schema string failed to convert %q", t.T)
 }
 
 func integerSpec(t *schema.IntegerType) (*sqlspec.Column, error) {
@@ -322,13 +322,17 @@ func integerSpec(t *schema.IntegerType) (*sqlspec.Column, error) {
 		return &sqlspec.Column{TypeName: "int"}, nil
 	case tTinyInt:
 		return &sqlspec.Column{TypeName: "int8"}, nil
+	case tMediumInt:
+		return &sqlspec.Column{TypeName: tMediumInt}, nil
+	case tSmallInt:
+		return &sqlspec.Column{TypeName: tSmallInt}, nil
 	case tBigInt:
 		if t.Unsigned {
 			return specutil.NewCol("", "uint64"), nil
 		}
 		return &sqlspec.Column{TypeName: "int64"}, nil
 	}
-	return nil, errors.New("mysql: schema integer failed to convert")
+	return nil, fmt.Errorf("mysql: schema integer failed to convert %q", t.T)
 }
 
 func enumSpec(t *schema.EnumType) (*sqlspec.Column, error) {
diff --git a/sql/postgres/diff.go b/sql/postgres/diff.go
index 1f4c7b2b..fa5a4936 100644
--- a/sql/postgres/diff.go
+++ b/sql/postgres/diff.go
@@ -155,37 +155,19 @@ func (d *diff) typeChanged(from, to *schema.Column) (bool, error) {
 	}
 	var changed bool
 	switch fromT := fromT.(type) {
-	case *schema.BinaryType:
-		toT := toT.(*schema.BinaryType)
-		changed = fromT.T != toT.T
-	case *schema.DecimalType:
-		toT := toT.(*schema.DecimalType)
-		changed = fromT.T != toT.T || fromT.Scale != toT.Scale || fromT.Precision != toT.Precision
+	case *schema.BinaryType, *schema.BoolType, *schema.DecimalType, *schema.FloatType,
+		*schema.IntegerType, *schema.JSONType, *schema.SpatialType, *schema.StringType,
+		*schema.TimeType, *BitType, *SerialType:
+		changed = d.mustFormat(toT) != d.mustFormat(fromT)
 	case *EnumType:
 		toT := toT.(*schema.EnumType)
 		changed = fromT.T != toT.T || !sqlx.ValuesEqual(fromT.Values, toT.Values)
 	case *schema.EnumType:
 		toT := toT.(*schema.EnumType)
 		changed = fromT.T != toT.T || !sqlx.ValuesEqual(fromT.Values, toT.Values)
-	case *schema.FloatType:
-		toT := toT.(*schema.FloatType)
-		changed = fromT.T != toT.T || fromT.Precision != toT.Precision
-	case *schema.IntegerType:
-		toT := toT.(*schema.IntegerType)
-		// Unsigned integers are not supported.
-		changed = fromT.T != toT.T
 	case *NetworkType:
 		toT := toT.(*NetworkType)
 		changed = fromT.T != toT.T
-	case *SerialType:
-		toT := toT.(*SerialType)
-		changed = fromT.T != toT.T || fromT.Precision != toT.Precision
-	case *schema.StringType:
-		toT := toT.(*schema.StringType)
-		changed = fromT.T != toT.T || fromT.Size != toT.Size
-	case *BitType:
-		toT := toT.(*BitType)
-		changed = fromT.T != toT.T || fromT.Len != toT.Len
 	case *CurrencyType:
 		toT := toT.(*CurrencyType)
 		changed = fromT.T != toT.T
@@ -207,18 +189,6 @@ func (d *diff) typeChanged(from, to *schema.Column) (bool, error) {
 	case *UserDefinedType:
 		toT := toT.(*UserDefinedType)
 		changed = fromT.T != toT.T
-	case *schema.BoolType:
-		toT := toT.(*schema.BoolType)
-		changed = fromT.T != toT.T
-	case *schema.JSONType:
-		toT := toT.(*schema.JSONType)
-		changed = fromT.T != toT.T
-	case *schema.SpatialType:
-		toT := toT.(*schema.SpatialType)
-		changed = fromT.T != toT.T
-	case *schema.TimeType:
-		toT := toT.(*schema.TimeType)
-		changed = fromT.T != toT.T
 	default:
 		return false, &sqlx.UnsupportedTypeError{Type: fromT}
 	}
@@ -226,10 +196,9 @@ func (d *diff) typeChanged(from, to *schema.Column) (bool, error) {
 }
 
 // Normalize implements the sqlx.Normalizer interface.
-func (d *diff) Normalize(tables ...*schema.Table) {
-	for _, t := range tables {
-		d.normalize(t)
-	}
+func (d *diff) Normalize(from, to *schema.Table) {
+	d.normalize(from)
+	d.normalize(to)
 }
 
 func (d *diff) normalize(table *schema.Table) {
diff --git a/sql/postgres/migrate.go b/sql/postgres/migrate.go
index 102168b2..81f3f947 100644
--- a/sql/postgres/migrate.go
+++ b/sql/postgres/migrate.go
@@ -86,7 +86,7 @@ func (m *migrate) modifyTable(ctx context.Context, modify *schema.ModifyTable) e
 		changes     []schema.Change
 		addI, dropI []*schema.Index
 	)
-	for _, change := range modify.Changes {
+	for _, change := range skipAutoChanges(modify.Changes) {
 		switch change := change.(type) {
 		case *schema.DropAttr:
 			return fmt.Errorf("unsupported change type: %T", change)
@@ -160,7 +160,7 @@ func (m *migrate) alterTable(ctx context.Context, t *schema.Table, changes []sch
 			b.P("ADD")
 			m.fks(b, change.F)
 		case *schema.DropForeignKey:
-			b.P("DROP FOREIGN KEY").Ident(change.F.Symbol)
+			b.P("DROP CONSTRAINT").Ident(change.F.Symbol)
 		}
 	})
 	if _, err := m.ExecContext(ctx, b.String()); err != nil {
@@ -426,3 +426,38 @@ func Build(phrase string) *sqlx.Builder {
 	b := &sqlx.Builder{QuoteChar: '"'}
 	return b.P(phrase)
 }
+
+// skipAutoChanges filters unnecessary changes that are automatically
+// happened by the database when ALTER TABLE is executed.
+func skipAutoChanges(changes []schema.Change) []schema.Change {
+	dropC := make(map[string]bool)
+	for _, c := range changes {
+		if c, ok := c.(*schema.DropColumn); ok {
+			dropC[c.C.Name] = true
+		}
+	}
+	for i, c := range changes {
+		switch c := c.(type) {
+		// Indexes involving the column are automatically dropped
+		// with it. This true for multi-columns indexes as well.
+		// See https://www.postgresql.org/docs/current/sql-altertable.html
+		case *schema.DropIndex:
+			for _, p := range c.I.Parts {
+				if p.C == nil && dropC[p.C.Name] {
+					changes = append(changes[:i], changes[i+1:]...)
+					break
+				}
+			}
+		// Simple case for skipping constraint dropping,
+		// if the child table columns were dropped.
+		case *schema.DropForeignKey:
+			for _, c := range c.F.Columns {
+				if dropC[c.Name] {
+					changes = append(changes[:i], changes[i+1:]...)
+					break
+				}
+			}
+		}
+	}
+	return changes
+}
diff --git a/sql/postgres/sqlspec.go b/sql/postgres/sqlspec.go
index a7c74d7f..f0b343f1 100644
--- a/sql/postgres/sqlspec.go
+++ b/sql/postgres/sqlspec.go
@@ -274,7 +274,7 @@ func stringSpec(t *schema.StringType) (*sqlspec.Column, error) {
 		s := strconv.Itoa(t.Size)
 		return specutil.NewCol("", "string", specutil.LitAttr("size", s)), nil
 	default:
-		return nil, errors.New("schema string failed to convert")
+		return nil, fmt.Errorf("schema string failed to convert type %T", t)
 	}
 }
 


Test patch:
diff --git a/cmd/atlas/diff_test.go b/cmd/atlas/diff_test.go
index 0ad5533c..2172b8bf 100644
--- a/cmd/atlas/diff_test.go
+++ b/cmd/atlas/diff_test.go
@@ -53,14 +53,14 @@ table "groups" {
 	}
 	require.EqualValues(t, []*changeDesc{
 		{
-			typ:     "ModifyTable",
+			typ:     "Modify Table",
 			subject: "users",
 			queries: []string{
 				"ALTER TABLE `default`.`users` MODIFY COLUMN `id` varchar(255) NOT NULL, ADD COLUMN `name` varchar(255) NOT NULL",
 			},
 		},
 		{
-			typ:     "AddTable",
+			typ:     "Add Table",
 			subject: "groups",
 			queries: []string{
 				"CREATE TABLE `default`.`groups` (`id` int NOT NULL)",
diff --git a/internal/integration/mysql_test.go b/internal/integration/mysql_test.go
index 70593f59..a37b70ee 100644
--- a/internal/integration/mysql_test.go
+++ b/internal/integration/mysql_test.go
@@ -88,7 +88,10 @@ func TestMySQL_Relation(t *testing.T) {
 func TestMySQL_AddIndexedColumns(t *testing.T) {
 	myRun(t, func(t *myTest) {
 		ctx := context.Background()
-		usersT := t.users()
+		usersT := &schema.Table{
+			Name:    "users",
+			Columns: []*schema.Column{{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "int"}}}},
+		}
 		err := t.drv.Migrate().Exec(ctx, []schema.Change{
 			&schema.AddTable{T: usersT},
 		})
@@ -102,11 +105,16 @@ func TestMySQL_AddIndexedColumns(t *testing.T) {
 			Name:    "b",
 			Type:    &schema.ColumnType{Raw: "bigint", Type: &schema.IntegerType{T: "bigint"}, Null: true},
 			Default: &schema.RawExpr{X: "10"},
+		}, &schema.Column{
+			Name:    "c",
+			Type:    &schema.ColumnType{Raw: "bigint", Type: &schema.IntegerType{T: "bigint"}, Null: true},
+			Default: &schema.RawExpr{X: "10"},
 		})
+		parts := usersT.Columns[len(usersT.Columns)-3:]
 		usersT.Indexes = append(usersT.Indexes, &schema.Index{
 			Unique: true,
-			Name:   "a_b_unique",
-			Parts:  []*schema.IndexPart{{C: usersT.Columns[1]}, {C: usersT.Columns[2]}},
+			Name:   "a_b_c_unique",
+			Parts:  []*schema.IndexPart{{C: parts[0]}, {C: parts[1]}, {C: parts[2]}},
 		})
 		changes, err := t.drv.Diff().TableDiff(t.loadUsers(), usersT)
 		require.NoError(t, err)
@@ -114,6 +122,45 @@ func TestMySQL_AddIndexedColumns(t *testing.T) {
 		err = t.drv.Migrate().Exec(ctx, []schema.Change{&schema.ModifyTable{T: usersT, Changes: changes}})
 		require.NoError(t, err)
 		t.ensureNoChange(usersT)
+
+		// Dropping a column should remove
+		// it from the key.
+		usersT.Columns = usersT.Columns[:len(usersT.Columns)-1]
+		changes, err = t.drv.Diff().TableDiff(t.loadUsers(), usersT)
+		require.NoError(t, err)
+		err = t.drv.Migrate().Exec(ctx, []schema.Change{&schema.ModifyTable{T: usersT, Changes: changes}})
+		require.NoError(t, err)
+		t.ensureNoChange(t.loadUsers())
+
+		// Dropping a column from both table and index.
+		usersT = t.loadUsers()
+		idx, ok := usersT.Index("a_b_c_unique")
+		require.True(t, ok)
+		require.Len(t, idx.Parts, 2)
+		usersT.Columns = usersT.Columns[:len(usersT.Columns)-1]
+		idx.Parts = idx.Parts[:len(idx.Parts)-1]
+		changes, err = t.drv.Diff().TableDiff(t.loadUsers(), usersT)
+		require.NoError(t, err)
+		require.Len(t, changes, 2)
+		err = t.drv.Migrate().Exec(ctx, []schema.Change{&schema.ModifyTable{T: usersT, Changes: changes}})
+		require.NoError(t, err)
+		t.ensureNoChange(t.loadUsers())
+
+		// Dropping a column should remove
+		// single-column keys as well.
+		usersT = t.loadUsers()
+		idx, ok = usersT.Index("a_b_c_unique")
+		require.True(t, ok)
+		require.Len(t, idx.Parts, 1)
+		usersT.Columns = usersT.Columns[:len(usersT.Columns)-1]
+		changes, err = t.drv.Diff().TableDiff(t.loadUsers(), usersT)
+		require.NoError(t, err)
+		require.Len(t, changes, 1)
+		err = t.drv.Migrate().Exec(ctx, []schema.Change{&schema.ModifyTable{T: usersT, Changes: changes}})
+		require.NoError(t, err)
+		t.ensureNoChange(t.loadUsers())
+		idx, ok = t.loadUsers().Index("a_b_c_unique")
+		require.False(t, ok)
 	})
 }
 
@@ -424,6 +471,55 @@ func TestMySQL_ForeignKey(t *testing.T) {
 			t.ensureNoChange(postsT, usersT)
 		})
 	})
+
+	t.Run("AddDrop", func(t *testing.T) {
+		myRun(t, func(t *myTest) {
+			usersT := t.users()
+			t.dropTables(usersT.Name)
+			err := t.drv.Migrate().Exec(ctx, []schema.Change{
+				&schema.AddTable{T: usersT},
+			})
+			require.NoError(t, err)
+			t.ensureNoChange(usersT)
+
+			// Add foreign key.
+			usersT.Columns = append(usersT.Columns, &schema.Column{
+				Name: "spouse_id",
+				Type: &schema.ColumnType{Raw: "bigint", Type: &schema.IntegerType{T: "bigint"}, Null: true},
+			})
+			usersT.ForeignKeys = append(usersT.ForeignKeys, &schema.ForeignKey{
+				Symbol:     "spouse_id",
+				Table:      usersT,
+				Columns:    usersT.Columns[len(usersT.Columns)-1:],
+				RefTable:   usersT,
+				RefColumns: usersT.Columns[:1],
+				OnDelete:   schema.NoAction,
+			})
+
+			changes, err := t.drv.Diff().TableDiff(t.loadUsers(), usersT)
+			require.NoError(t, err)
+			require.Len(t, changes, 2)
+			addC, ok := changes[0].(*schema.AddColumn)
+			require.True(t, ok)
+			require.Equal(t, "spouse_id", addC.C.Name)
+			addF, ok := changes[1].(*schema.AddForeignKey)
+			require.True(t, ok)
+			require.Equal(t, "spouse_id", addF.F.Symbol)
+			err = t.drv.Migrate().Exec(ctx, []schema.Change{&schema.ModifyTable{T: usersT, Changes: changes}})
+			require.NoError(t, err)
+			t.ensureNoChange(usersT)
+
+			// Drop foreign keys.
+			usersT.Columns = usersT.Columns[:len(usersT.Columns)-1]
+			usersT.ForeignKeys = usersT.ForeignKeys[:len(usersT.ForeignKeys)-1]
+			changes, err = t.drv.Diff().TableDiff(t.loadUsers(), usersT)
+			require.NoError(t, err)
+			require.Len(t, changes, 2)
+			err = t.drv.Migrate().Exec(ctx, []schema.Change{&schema.ModifyTable{T: usersT, Changes: changes}})
+			require.NoError(t, err)
+			t.ensureNoChange(usersT)
+		})
+	})
 }
 
 func TestMySQL_HCL(t *testing.T) {
@@ -578,9 +674,9 @@ func (t *myTest) loadUsers() *schema.Table {
 func (t *myTest) loadPosts() *schema.Table {
 	realm := t.loadRealm()
 	require.Len(t, realm.Schemas, 1)
-	users, ok := realm.Schemas[0].Table("posts")
+	posts, ok := realm.Schemas[0].Table("posts")
 	require.True(t, ok)
-	return users
+	return posts
 }
 
 // defaultConfig returns the default charset and
diff --git a/internal/integration/postgres_test.go b/internal/integration/postgres_test.go
index 858973f2..d035f49c 100644
--- a/internal/integration/postgres_test.go
+++ b/internal/integration/postgres_test.go
@@ -34,7 +34,7 @@ var pgTests struct {
 func pgRun(t *testing.T, fn func(*pgTest)) {
 	pgTests.Do(func() {
 		pgTests.drivers = make(map[string]*pgTest)
-		for version, port := range map[string]int{"10": 5430, "11": 5431, "12": 5433, "13": 5434} {
+		for version, port := range map[string]int{"10": 5430, "11": 5431, "12": 5432, "13": 5433, "14": 5434} {
 			db, err := sql.Open("postgres", fmt.Sprintf("host=localhost port=%d user=postgres dbname=test password=pass sslmode=disable", port))
 			require.NoError(t, err)
 			drv, err := postgres.Open(db)
@@ -88,7 +88,10 @@ func TestPostgres_Relation(t *testing.T) {
 func TestPostgres_AddIndexedColumns(t *testing.T) {
 	pgRun(t, func(t *pgTest) {
 		ctx := context.Background()
-		usersT := t.users()
+		usersT := &schema.Table{
+			Name:    "users",
+			Columns: []*schema.Column{{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "int"}}}},
+		}
 		err := t.drv.Migrate().Exec(ctx, []schema.Change{
 			&schema.AddTable{T: usersT},
 		})
@@ -102,20 +105,35 @@ func TestPostgres_AddIndexedColumns(t *testing.T) {
 			Name:    "b",
 			Type:    &schema.ColumnType{Raw: "bigint", Type: &schema.IntegerType{T: "bigint"}, Null: true},
 			Default: &schema.RawExpr{X: "10"},
+		}, &schema.Column{
+			Name:    "c",
+			Type:    &schema.ColumnType{Raw: "bigint", Type: &schema.IntegerType{T: "bigint"}, Null: true},
+			Default: &schema.RawExpr{X: "10"},
 		})
+		parts := usersT.Columns[len(usersT.Columns)-3:]
 		usersT.Indexes = append(usersT.Indexes, &schema.Index{
 			Unique: true,
-			Name:   "a_b_unique",
-			Parts:  []*schema.IndexPart{{C: usersT.Columns[1]}, {C: usersT.Columns[2]}},
+			Name:   "a_b_c_unique",
+			Parts:  []*schema.IndexPart{{C: parts[0]}, {C: parts[1]}, {C: parts[2]}},
 		})
-		realm := t.loadRealm()
-		changes, err := t.drv.Diff().TableDiff(realm.Schemas[0].Tables[0], usersT)
+		changes, err := t.drv.Diff().TableDiff(t.loadUsers(), usersT)
 		require.NoError(t, err)
-		require.NotEmpty(t, changes, "usersT contains 2 new columns and 1 new index")
-
+		require.NotEmpty(t, changes, "usersT contains 3 new columns and 1 new index")
 		err = t.drv.Migrate().Exec(ctx, []schema.Change{&schema.ModifyTable{T: usersT, Changes: changes}})
 		require.NoError(t, err)
 		t.ensureNoChange(usersT)
+
+		// Dropping a column involves in a multi-column
+		// index causes the index to be dropped as well.
+		usersT.Columns = usersT.Columns[:len(usersT.Columns)-1]
+		changes, err = t.drv.Diff().TableDiff(t.loadUsers(), usersT)
+		require.NoError(t, err)
+		err = t.drv.Migrate().Exec(ctx, []schema.Change{&schema.ModifyTable{T: usersT, Changes: changes}})
+		require.NoError(t, err)
+		t.ensureNoChange(t.loadUsers())
+		usersT = t.loadUsers()
+		_, ok := usersT.Index("a_b_c_unique")
+		require.False(t, ok)
 	})
 }
 
@@ -283,6 +301,126 @@ func TestPostgres_Enums(t *testing.T) {
 	})
 }
 
+func TestPostgres_ForeignKey(t *testing.T) {
+	ctx := context.Background()
+	t.Run("ChangeAction", func(t *testing.T) {
+		pgRun(t, func(t *pgTest) {
+			usersT, postsT := t.users(), t.posts()
+			t.dropTables(postsT.Name, usersT.Name)
+			err := t.drv.Migrate().Exec(ctx, []schema.Change{
+				&schema.AddTable{T: usersT},
+				&schema.AddTable{T: postsT},
+			})
+			require.NoError(t, err)
+			t.ensureNoChange(postsT, usersT)
+
+			postsT = t.loadPosts()
+			fk, ok := postsT.ForeignKey("author_id")
+			require.True(t, ok)
+			fk.OnUpdate = schema.SetNull
+			fk.OnDelete = schema.Cascade
+			changes, err := t.drv.Diff().TableDiff(t.loadPosts(), postsT)
+			require.NoError(t, err)
+			require.Len(t, changes, 1)
+			modifyF, ok := changes[0].(*schema.ModifyForeignKey)
+			require.True(t, ok)
+			require.True(t, modifyF.Change == schema.ChangeUpdateAction|schema.ChangeDeleteAction)
+
+			err = t.drv.Migrate().Exec(ctx, []schema.Change{&schema.ModifyTable{T: postsT, Changes: changes}})
+			require.NoError(t, err)
+			t.ensureNoChange(postsT, usersT)
+		})
+	})
+
+	t.Run("UnsetNull", func(t *testing.T) {
+		pgRun(t, func(t *pgTest) {
+			usersT, postsT := t.users(), t.posts()
+			t.dropTables(postsT.Name, usersT.Name)
+			fk, ok := postsT.ForeignKey("author_id")
+			require.True(t, ok)
+			fk.OnDelete = schema.SetNull
+			fk.OnUpdate = schema.SetNull
+			err := t.drv.Migrate().Exec(ctx, []schema.Change{
+				&schema.AddTable{T: usersT},
+				&schema.AddTable{T: postsT},
+			})
+			require.NoError(t, err)
+			t.ensureNoChange(postsT, usersT)
+
+			postsT = t.loadPosts()
+			c, ok := postsT.Column("author_id")
+			require.True(t, ok)
+			c.Type.Null = false
+			fk, ok = postsT.ForeignKey("author_id")
+			require.True(t, ok)
+			fk.OnUpdate = schema.NoAction
+			fk.OnDelete = schema.NoAction
+			changes, err := t.drv.Diff().TableDiff(t.loadPosts(), postsT)
+			require.NoError(t, err)
+			require.Len(t, changes, 2)
+			modifyC, ok := changes[0].(*schema.ModifyColumn)
+			require.True(t, ok)
+			require.True(t, modifyC.Change == schema.ChangeNull)
+			modifyF, ok := changes[1].(*schema.ModifyForeignKey)
+			require.True(t, ok)
+			require.True(t, modifyF.Change == schema.ChangeUpdateAction|schema.ChangeDeleteAction)
+
+			err = t.drv.Migrate().Exec(ctx, []schema.Change{&schema.ModifyTable{T: postsT, Changes: changes}})
+			require.NoError(t, err)
+			t.ensureNoChange(postsT, usersT)
+		})
+	})
+
+	t.Run("AddDrop", func(t *testing.T) {
+		pgRun(t, func(t *pgTest) {
+			usersT := t.users()
+			t.dropTables(usersT.Name)
+			err := t.drv.Migrate().Exec(ctx, []schema.Change{
+				&schema.AddTable{T: usersT},
+			})
+			require.NoError(t, err)
+			t.ensureNoChange(usersT)
+
+			// Add foreign key.
+			usersT.Columns = append(usersT.Columns, &schema.Column{
+				Name: "spouse_id",
+				Type: &schema.ColumnType{Raw: "bigint", Type: &schema.IntegerType{T: "bigint"}, Null: true},
+			})
+			usersT.ForeignKeys = append(usersT.ForeignKeys, &schema.ForeignKey{
+				Symbol:     "spouse_id",
+				Table:      usersT,
+				Columns:    usersT.Columns[len(usersT.Columns)-1:],
+				RefTable:   usersT,
+				RefColumns: usersT.Columns[:1],
+				OnDelete:   schema.NoAction,
+			})
+
+			changes, err := t.drv.Diff().TableDiff(t.loadUsers(), usersT)
+			require.NoError(t, err)
+			require.Len(t, changes, 2)
+			addC, ok := changes[0].(*schema.AddColumn)
+			require.True(t, ok)
+			require.Equal(t, "spouse_id", addC.C.Name)
+			addF, ok := changes[1].(*schema.AddForeignKey)
+			require.True(t, ok)
+			require.Equal(t, "spouse_id", addF.F.Symbol)
+			err = t.drv.Migrate().Exec(ctx, []schema.Change{&schema.ModifyTable{T: usersT, Changes: changes}})
+			require.NoError(t, err)
+			t.ensureNoChange(usersT)
+
+			// Drop foreign keys.
+			usersT.Columns = usersT.Columns[:len(usersT.Columns)-1]
+			usersT.ForeignKeys = usersT.ForeignKeys[:len(usersT.ForeignKeys)-1]
+			changes, err = t.drv.Diff().TableDiff(t.loadUsers(), usersT)
+			require.NoError(t, err)
+			require.Len(t, changes, 2)
+			err = t.drv.Migrate().Exec(ctx, []schema.Change{&schema.ModifyTable{T: usersT, Changes: changes}})
+			require.NoError(t, err)
+			t.ensureNoChange(usersT)
+		})
+	})
+}
+
 func (t *pgTest) loadRealm() *schema.Realm {
 	r, err := t.drv.InspectRealm(context.Background(), &schema.InspectRealmOption{
 		Schemas: []string{"public"},
@@ -299,6 +437,14 @@ func (t *pgTest) loadUsers() *schema.Table {
 	return users
 }
 
+func (t *pgTest) loadPosts() *schema.Table {
+	realm := t.loadRealm()
+	require.Len(t, realm.Schemas, 1)
+	posts, ok := realm.Schemas[0].Table("posts")
+	require.True(t, ok)
+	return posts
+}
+
 func (t *pgTest) users() *schema.Table {
 	usersT := &schema.Table{
 		Name:   "users",
diff --git a/schema/schemaspec/schemahcl/context_test.go b/schema/schemaspec/schemahcl/context_test.go
index 51a93fff..6eea3f0f 100644
--- a/schema/schemaspec/schemahcl/context_test.go
+++ b/schema/schemaspec/schemahcl/context_test.go
@@ -303,3 +303,28 @@ person "jane" {
 	}
 	require.EqualValues(t, jane, test.People[1])
 }
+
+func TestSchemaRefParse(t *testing.T) {
+	type Point struct {
+		Z []*schemaspec.Ref `spec:"z"`
+	}
+	var test = struct {
+		Points []*Point `spec:"point"`
+	}{
+		Points: []*Point{
+			{Z: []*schemaspec.Ref{{V: "$a"}}},
+			{Z: []*schemaspec.Ref{{V: "b"}}},
+		},
+	}
+	b, err := Marshal(&test)
+	require.NoError(t, err)
+	expected :=
+		`point {
+  z = [a, ]
+}
+point {
+  z = [b, ]
+}
+`
+	require.Equal(t, expected, string(b))
+}
diff --git a/schema/schemaspec/schemahcl/hcl_test.go b/schema/schemaspec/schemahcl/hcl_test.go
index 41342c99..a7cd75bf 100644
--- a/schema/schemaspec/schemahcl/hcl_test.go
+++ b/schema/schemaspec/schemahcl/hcl_test.go
@@ -9,20 +9,26 @@ import (
 )
 
 func TestAttributes(t *testing.T) {
-	f := `i = 1
-b = true
-s = "hello, world"
+	f := `i  = 1
+b  = true
+s  = "hello, world"
+sl = ["hello", "world", ]
+bl = [true, false, ]
 `
 	var test struct {
-		Int  int    `spec:"i"`
-		Bool bool   `spec:"b"`
-		Str  string `spec:"s"`
+		Int        int      `spec:"i"`
+		Bool       bool     `spec:"b"`
+		Str        string   `spec:"s"`
+		StringList []string `spec:"sl"`
+		BoolList   []bool   `spec:"bl"`
 	}
 	err := Unmarshal([]byte(f), &test)
 	require.NoError(t, err)
 	require.EqualValues(t, 1, test.Int)
 	require.EqualValues(t, true, test.Bool)
 	require.EqualValues(t, "hello, world", test.Str)
+	require.EqualValues(t, []string{"hello", "world"}, test.StringList)
+	require.EqualValues(t, []bool{true, false}, test.BoolList)
 	marshal, err := Marshal(&test)
 	require.NoError(t, err)
 	require.EqualValues(t, f, string(marshal))
diff --git a/sql/internal/specutil/convert_test.go b/sql/internal/specutil/convert_test.go
index de4ca6ee..0f50e6be 100644
--- a/sql/internal/specutil/convert_test.go
+++ b/sql/internal/specutil/convert_test.go
@@ -4,6 +4,8 @@ import (
 	"testing"
 
 	"ariga.io/atlas/schema/schemaspec"
+	"ariga.io/atlas/sql/schema"
+	"ariga.io/atlas/sql/sqlspec"
 	"github.com/stretchr/testify/require"
 )
 
@@ -20,3 +22,19 @@ func TestRef_TableName(t *testing.T) {
 	require.NoError(t, err)
 	require.Equal(t, "accounts", c)
 }
+
+func TestFromSpec_SchemaName(t *testing.T) {
+	sc := &schema.Schema{
+		Name: "schema",
+		Tables: []*schema.Table{
+			{},
+		},
+	}
+	sc.Tables[0].Schema = sc
+	s, ta, err := FromSchema(sc, func(table *schema.Table) (*sqlspec.Table, error) {
+		return &sqlspec.Table{}, nil
+	})
+	require.NoError(t, err)
+	require.Equal(t, sc.Name, s.Name)
+	require.Equal(t, sc.Name, ta[0].SchemaName)
+}
diff --git a/sql/mysql/sqlspec_test.go b/sql/mysql/sqlspec_test.go
index d23141fc..2fe77dba 100644
--- a/sql/mysql/sqlspec_test.go
+++ b/sql/mysql/sqlspec_test.go
@@ -331,6 +331,20 @@ func TestMarshalSpecColumnType(t *testing.T) {
 			},
 			expected: specutil.NewCol("column", "int8"),
 		},
+		{
+			schem: &schema.IntegerType{
+				T:        tMediumInt,
+				Unsigned: false,
+			},
+			expected: specutil.NewCol("column", tMediumInt),
+		},
+		{
+			schem: &schema.IntegerType{
+				T:        tSmallInt,
+				Unsigned: false,
+			},
+			expected: specutil.NewCol("column", tSmallInt),
+		},
 		{
 			schem: &schema.IntegerType{
 				T:        tBigInt,
@@ -352,6 +366,27 @@ func TestMarshalSpecColumnType(t *testing.T) {
 			},
 			expected: specutil.NewCol("column", "string", specutil.LitAttr("size", "255")),
 		},
+		{
+			schem: &schema.StringType{
+				T:    tTinyText,
+				Size: 255,
+			},
+			expected: specutil.NewCol("column", "string", specutil.LitAttr("size", "255")),
+		},
+		{
+			schem: &schema.StringType{
+				T:    tText,
+				Size: 255,
+			},
+			expected: specutil.NewCol("column", "string", specutil.LitAttr("size", "255")),
+		},
+		{
+			schem: &schema.StringType{
+				T:    tChar,
+				Size: 255,
+			},
+			expected: specutil.NewCol("column", "string", specutil.LitAttr("size", "255")),
+		},
 		{
 			schem: &schema.StringType{
 				T:    tMediumText,