Repository: ariga/atlas
Pull request #488 (2022-01-25 11:20:44)

specutil/convert: fix panic on missing schema ref in table block
When creating a "valid" HCL schema, and running it through Unmarshal, everything works, but if I add another schema block, and the `table` block doesn't have reference to to the schema, it panics.

For example, before the fix this is valid and works
```HCL
schema "test" {
}

table "accounts" {
  column "name" {
    type = varchar(32)
  }
  primary_key {
    columns = [table.accounts.column.name]
  }
}
```
But this panics:

```HCL
schema "test" {
}

schema "test2" {
}

table "accounts" {
  column "name" {
    type = varchar(32)
  }
  primary_key {
    columns = [table.accounts.column.name]
  }
}
```

**This fix will also enforce the usage of `schema = ` attribute in the `table` block and make it more consistent on all cases.**




Patch:
diff --git a/cmd/action/diff.go b/cmd/action/diff.go
new file mode 100644
index 00000000..e4345b18
--- /dev/null
+++ b/cmd/action/diff.go
@@ -0,0 +1,69 @@
+// 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 action
+
+import (
+	"context"
+	"strings"
+
+	"github.com/spf13/cobra"
+)
+
+var (
+	// DiffFlags are the flags used in the Diff command.
+	DiffFlags struct {
+		FromDSN string
+		ToDSN   string
+	}
+	// DiffCmd represents the diff command.
+	DiffCmd = &cobra.Command{
+		Use:   "diff",
+		Short: "Calculate and print the diff between two schemas.",
+		Long: "`atlas schema diff`" + ` connects to two given databases, inspects
+them, calculates the difference in their schemas, and prints a plan of
+SQL queries to bring the "from" database to the schema of the "to" database.`,
+		Run: CmdDiffRun,
+	}
+)
+
+func init() {
+	schemaCmd.AddCommand(DiffCmd)
+	DiffCmd.Flags().StringVarP(&DiffFlags.FromDSN, "from", "", "", "[driver://username:password@protocol(address)/dbname?param=value] Select data source using the dsn format")
+	DiffCmd.Flags().StringVarP(&DiffFlags.ToDSN, "to", "", "", "[driver://username:password@protocol(address)/dbname?param=value] Select data source using the dsn format")
+	cobra.CheckErr(DiffCmd.MarkFlagRequired("from"))
+	cobra.CheckErr(DiffCmd.MarkFlagRequired("to"))
+}
+
+// CmdDiffRun connects to the given databases, and prints an SQL plan to get from
+// the "from" schema to the "to" schema.
+func CmdDiffRun(cmd *cobra.Command, args []string) {
+	fromDriver, err := defaultMux.OpenAtlas(DiffFlags.FromDSN)
+	cobra.CheckErr(err)
+	toDriver, err := defaultMux.OpenAtlas(DiffFlags.ToDSN)
+	cobra.CheckErr(err)
+	fromName, err := SchemaNameFromDSN(DiffFlags.FromDSN)
+	cobra.CheckErr(err)
+	toName, err := SchemaNameFromDSN(DiffFlags.ToDSN)
+	cobra.CheckErr(err)
+	ctx := context.Background()
+	fromSchema, err := fromDriver.InspectSchema(ctx, fromName, nil)
+	cobra.CheckErr(err)
+	toSchema, err := toDriver.InspectSchema(ctx, toName, nil)
+	cobra.CheckErr(err)
+	diff, err := toDriver.SchemaDiff(fromSchema, toSchema)
+	cobra.CheckErr(err)
+	p, err := toDriver.PlanChanges(ctx, "plan", diff)
+	cobra.CheckErr(err)
+	if len(p.Changes) == 0 {
+		schemaCmd.Println("Schemas are synced, no changes to be made.")
+		return
+	}
+	for _, c := range p.Changes {
+		if c.Comment != "" {
+			cmd.Println("--", strings.ToUpper(c.Comment[:1])+c.Comment[1:])
+		}
+		cmd.Println(c.Cmd)
+	}
+}
diff --git a/cmd/action/env.go b/cmd/action/env.go
index 9a6d981c..537afe41 100644
--- a/cmd/action/env.go
+++ b/cmd/action/env.go
@@ -5,6 +5,7 @@ import (
 	"os"
 
 	"ariga.io/atlas/cmd/action/internal/update"
+
 	"github.com/spf13/cobra"
 )
 
diff --git a/cmd/action/root.go b/cmd/action/root.go
index 807a9f17..754f5721 100644
--- a/cmd/action/root.go
+++ b/cmd/action/root.go
@@ -2,6 +2,7 @@ package action
 
 import (
 	"ariga.io/atlas/cmd/action/internal/update"
+
 	"github.com/spf13/cobra"
 )
 
diff --git a/cmd/atlas/doc.go b/cmd/atlas/doc.go
index 2d8781a7..d45146bf 100644
--- a/cmd/atlas/doc.go
+++ b/cmd/atlas/doc.go
@@ -11,6 +11,7 @@ import (
 	"text/template"
 
 	"ariga.io/atlas/cmd/action"
+
 	"github.com/spf13/cobra"
 	"github.com/spf13/cobra/doc"
 )
diff --git a/internal/integration/go.mod b/internal/integration/go.mod
index 6b32c6e0..f9b7aebd 100644
--- a/internal/integration/go.mod
+++ b/internal/integration/go.mod
@@ -15,6 +15,11 @@ require (
 	github.com/zclconf/go-cty v1.10.0 // indirect
 )
 
+require (
+	github.com/pkg/diff v0.0.0-20210226163009-20ebb0f2a09e
+	github.com/rogpeppe/go-internal v1.8.2-0.20220112175052-f3cb5c2c6412
+)
+
 require (
 	github.com/agext/levenshtein v1.2.3 // indirect
 	github.com/apparentlymart/go-textseg/v13 v13.0.0 // indirect
@@ -24,9 +29,7 @@ require (
 	github.com/google/uuid v1.3.0 // indirect
 	github.com/kr/pretty v0.2.0 // indirect
 	github.com/mitchellh/go-wordwrap v1.0.1 // indirect
-	github.com/pkg/diff v0.0.0-20210226163009-20ebb0f2a09e // indirect
 	github.com/pmezard/go-difflib v1.0.0 // indirect
-	github.com/rogpeppe/go-internal v1.8.2-0.20220112175052-f3cb5c2c6412 // indirect
 	golang.org/x/mod v0.5.0 // indirect
 	golang.org/x/text v0.3.7 // indirect
 	gopkg.in/check.v1 v1.0.0-20190902080502-41f04d3bba15 // indirect
diff --git a/schema/schemaspec/schemahcl/context.go b/schema/schemaspec/schemahcl/context.go
index 4cb6a813..a94c9c64 100644
--- a/schema/schemaspec/schemahcl/context.go
+++ b/schema/schemaspec/schemahcl/context.go
@@ -6,6 +6,7 @@ import (
 	"strconv"
 
 	"ariga.io/atlas/schema/schemaspec"
+
 	"github.com/hashicorp/hcl/v2"
 	"github.com/hashicorp/hcl/v2/gohcl"
 	"github.com/hashicorp/hcl/v2/hclsyntax"
diff --git a/schema/schemaspec/schemahcl/opts.go b/schema/schemaspec/schemahcl/opts.go
index b0e116fa..b391a85e 100644
--- a/schema/schemaspec/schemahcl/opts.go
+++ b/schema/schemaspec/schemahcl/opts.go
@@ -5,6 +5,7 @@ import (
 	"reflect"
 
 	"ariga.io/atlas/schema/schemaspec"
+
 	"github.com/hashicorp/hcl/v2"
 	"github.com/zclconf/go-cty/cty"
 	"github.com/zclconf/go-cty/cty/function"
diff --git a/sql/internal/specutil/convert.go b/sql/internal/specutil/convert.go
index 6e3727a5..15016d2d 100644
--- a/sql/internal/specutil/convert.go
+++ b/sql/internal/specutil/convert.go
@@ -466,9 +466,12 @@ func FromCheck(s *schema.Check) *sqlspec.Check {
 
 // SchemaName returns the name from a ref to a schema.
 func SchemaName(ref *schemaspec.Ref) (string, error) {
+	if ref == nil {
+		return "", errors.New("unexpected nil reference")
+	}
 	parts := strings.Split(ref.V, ".")
 	if len(parts) < 2 || parts[0] != "$schema" {
-		return "", fmt.Errorf("expected ref format of $schema.name")
+		return "", errors.New("expected ref format of $schema.name")
 	}
 	return parts[1], nil
 }
diff --git a/sql/internal/specutil/types.go b/sql/internal/specutil/types.go
index e5fc210c..d21dd4b1 100644
--- a/sql/internal/specutil/types.go
+++ b/sql/internal/specutil/types.go
@@ -10,6 +10,7 @@ import (
 
 	"ariga.io/atlas/schema/schemaspec"
 	"ariga.io/atlas/sql/schema"
+
 	"github.com/go-openapi/inflect"
 )
 
diff --git a/sql/internal/sqlx/diff.go b/sql/internal/sqlx/diff.go
index 5a1e26c6..9f6e948c 100644
--- a/sql/internal/sqlx/diff.go
+++ b/sql/internal/sqlx/diff.go
@@ -291,7 +291,7 @@ func (d *Diff) partsChange(from, to []*schema.IndexPart) schema.ChangeKind {
 	sort.Slice(from, func(i, j int) bool { return from[i].SeqNo < from[j].SeqNo })
 	for i := range from {
 		switch {
-		case d.IndexPartAttrChanged(from[i].Attrs, to[i].Attrs):
+		case from[i].Desc != to[i].Desc || d.IndexPartAttrChanged(from[i].Attrs, to[i].Attrs):
 			return schema.ChangeParts
 		case from[i].C != nil && to[i].C != nil:
 			if from[i].C.Name != to[i].C.Name {
diff --git a/sql/mysql/diff.go b/sql/mysql/diff.go
index 0866c62e..6358cbf8 100644
--- a/sql/mysql/diff.go
+++ b/sql/mysql/diff.go
@@ -131,10 +131,7 @@ func (*diff) IndexAttrChanged(from, to []schema.Attr) bool {
 // IndexPartAttrChanged reports if the index-part attributes (collation or prefix) were changed.
 func (*diff) IndexPartAttrChanged(from, to []schema.Attr) bool {
 	var s1, s2 SubPart
-	if sqlx.Has(from, &s1) != sqlx.Has(to, &s2) || s1.Len != s2.Len {
-		return true
-	}
-	return indexCollation(from).V != indexCollation(to).V
+	return sqlx.Has(from, &s1) != sqlx.Has(to, &s2) || s1.Len != s2.Len
 }
 
 // ReferenceChanged reports if the foreign key referential action was changed.
@@ -243,16 +240,6 @@ func (*diff) autoIncChange(from, to []schema.Attr) schema.Change {
 	return noChange
 }
 
-// indexCollation returns the index collation from its attribute.
-// The default collation is ascending if no order was specified.
-func indexCollation(attr []schema.Attr) *schema.Collation {
-	c := &schema.Collation{V: "A"}
-	if sqlx.Has(attr, c) {
-		c.V = strings.ToUpper(c.V)
-	}
-	return c
-}
-
 // indexType returns the index type from its attribute.
 // The default type is BTREE if no type was specified.
 func indexType(attr []schema.Attr) *IndexType {
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index d98fe4b2..ab7381d3 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -306,12 +306,12 @@ func (i *inspect) addIndexes(t *schema.Table, rows *sql.Rows) error {
 	)
 	for rows.Next() {
 		var (
-			nonuniq                                 bool
-			seqno                                   int
-			name, indexType                         string
-			column, subPart, expr, comment, collate sql.NullString
+			nonuniq, desc                  bool
+			seqno                          int
+			name, indexType                string
+			column, subPart, expr, comment sql.NullString
 		)
-		if err := rows.Scan(&name, &column, &nonuniq, &seqno, &indexType, &collate, &comment, &subPart, &expr); err != nil {
+		if err := rows.Scan(&name, &column, &nonuniq, &seqno, &indexType, &desc, &comment, &subPart, &expr); err != nil {
 			return fmt.Errorf("mysql: scanning index: %w", err)
 		}
 		// Ignore primary keys.
@@ -339,10 +339,7 @@ func (i *inspect) addIndexes(t *schema.Table, rows *sql.Rows) error {
 		}
 		// Rows are ordered by SEQ_IN_INDEX that specifies the
 		// position of the column in the index definition.
-		part := &schema.IndexPart{
-			SeqNo: seqno,
-			Attrs: []schema.Attr{&schema.Collation{V: collate.String}},
-		}
+		part := &schema.IndexPart{SeqNo: seqno, Desc: desc}
 		switch {
 		case sqlx.ValidString(expr):
 			part.X = &schema.RawExpr{
@@ -450,18 +447,18 @@ func (i *inspect) tableNames(ctx context.Context, schema string, opts *schema.In
 	return names, nil
 }
 
-var reTimeOnUpdate = regexp.MustCompile(`^(?:default_generated )?on update current_timestamp(?:\(\d?\))?$`)
+var reTimeOnUpdate = regexp.MustCompile(`(?i)^(?:default_generated )?on update (current_timestamp(?:\(\d?\))?)$`)
 
 // extraAttr parses the EXTRA column from the INFORMATION_SCHEMA.COLUMNS table
 // and appends its parsed representation to the column.
 func (i *inspect) extraAttr(t *schema.Table, c *schema.Column, extra string) error {
-	extra = strings.ToLower(extra)
+	el := strings.ToLower(extra)
 	switch {
-	case extra == "", extra == "null": // ignore.
-	case extra == defaultGen:
+	case el == "", el == "null": // ignore.
+	case el == defaultGen:
 		// The column has an expression default value
 		// and it is handled in Driver.addColumn.
-	case extra == autoIncrement:
+	case el == autoIncrement:
 		a := &AutoIncrement{}
 		// A table can have only one AUTO_INCREMENT column. If it was returned as NULL
 		// from INFORMATION_SCHEMA, it is due to information_schema_stats_expiry and we
@@ -476,7 +473,7 @@ func (i *inspect) extraAttr(t *schema.Table, c *schema.Column, extra string) err
 		}
 		c.Attrs = append(c.Attrs, a)
 	case reTimeOnUpdate.MatchString(extra):
-		c.Attrs = append(c.Attrs, &OnUpdate{A: extra})
+		c.Attrs = append(c.Attrs, &OnUpdate{A: reTimeOnUpdate.FindStringSubmatch(extra)[1]})
 	default:
 		return fmt.Errorf("unknown attribute %q", extra)
 	}
@@ -624,8 +621,8 @@ const (
 	columnsQuery = "SELECT `COLUMN_NAME`, `COLUMN_TYPE`, `COLUMN_COMMENT`, `IS_NULLABLE`, `COLUMN_KEY`, `COLUMN_DEFAULT`, `EXTRA`, `CHARACTER_SET_NAME`, `COLLATION_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? ORDER BY `ORDINAL_POSITION`"
 
 	// Query to list table indexes.
-	indexesQuery     = "SELECT `INDEX_NAME`, `COLUMN_NAME`, `NON_UNIQUE`, `SEQ_IN_INDEX`, `INDEX_TYPE`, `COLLATION`, `INDEX_COMMENT`, `SUB_PART`, NULL AS `EXPRESSION` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? ORDER BY `index_name`, `seq_in_index`"
-	indexesExprQuery = "SELECT `INDEX_NAME`, `COLUMN_NAME`, `NON_UNIQUE`, `SEQ_IN_INDEX`, `INDEX_TYPE`, `COLLATION`, `INDEX_COMMENT`, `SUB_PART`, `EXPRESSION` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? ORDER BY `index_name`, `seq_in_index`"
+	indexesQuery     = "SELECT `INDEX_NAME`, `COLUMN_NAME`, `NON_UNIQUE`, `SEQ_IN_INDEX`, `INDEX_TYPE`, UPPER(`COLLATION`) = 'D' AS `DESC`, `INDEX_COMMENT`, `SUB_PART`, NULL AS `EXPRESSION` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? ORDER BY `index_name`, `seq_in_index`"
+	indexesExprQuery = "SELECT `INDEX_NAME`, `COLUMN_NAME`, `NON_UNIQUE`, `SEQ_IN_INDEX`, `INDEX_TYPE`, UPPER(`COLLATION`) = 'D' AS `DESC`, `INDEX_COMMENT`, `SUB_PART`, `EXPRESSION` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? ORDER BY `index_name`, `seq_in_index`"
 
 	// Query to list table information.
 	tableQuery = `
diff --git a/sql/mysql/migrate.go b/sql/mysql/migrate.go
index 9889ab60..6eaf10f4 100644
--- a/sql/mysql/migrate.go
+++ b/sql/mysql/migrate.go
@@ -500,7 +500,7 @@ func (s *state) indexParts(b *sqlx.Builder, parts []*schema.IndexPart) {
 				b.WriteString(fmt.Sprintf("(%d)", s.Len))
 			}
 			// Ignore default collation (i.e. "ASC")
-			if c := (&schema.Collation{}); sqlx.Has(parts[i].Attrs, c) && c.V == "D" {
+			if parts[i].Desc {
 				b.P("DESC")
 			}
 		})
diff --git a/sql/mysql/sqlspec.go b/sql/mysql/sqlspec.go
index f89eaf3b..ff2445ba 100644
--- a/sql/mysql/sqlspec.go
+++ b/sql/mysql/sqlspec.go
@@ -114,11 +114,11 @@ func convertColumn(spec *sqlspec.Column, _ *schema.Table) (*schema.Column, error
 		return nil, err
 	}
 	if attr, ok := spec.Attr("on_update"); ok {
-		s, err := attr.String()
-		if err != nil {
-			return nil, err
+		exp, ok := attr.V.(*schemaspec.RawExpr)
+		if !ok {
+			return nil, fmt.Errorf(`unexpected type %T for atrribute "on_update"`, attr.V)
 		}
-		c.AddAttrs(&OnUpdate{A: s})
+		c.AddAttrs(&OnUpdate{A: exp.X})
 	}
 	return c, err
 }
diff --git a/sql/postgres/diff.go b/sql/postgres/diff.go
index f8301d5d..d45cc6ea 100644
--- a/sql/postgres/diff.go
+++ b/sql/postgres/diff.go
@@ -123,13 +123,11 @@ func (*diff) IndexAttrChanged(from, to []schema.Attr) bool {
 
 // IndexPartAttrChanged reports if the index-part attributes were changed.
 func (*diff) IndexPartAttrChanged(from, to []schema.Attr) bool {
-	// By default, B-tree indexes store rows
-	// in ascending order with nulls last.
-	p1 := &IndexColumnProperty{Asc: true, NullsLast: true}
+	p1 := &IndexColumnProperty{NullsLast: true}
 	sqlx.Has(from, p1)
-	p2 := &IndexColumnProperty{Asc: true, NullsLast: true}
+	p2 := &IndexColumnProperty{NullsLast: true}
 	sqlx.Has(to, p2)
-	return p1.Asc != p2.Asc || p1.Desc != p2.Desc || p1.NullsFirst != p2.NullsFirst || p1.NullsLast != p2.NullsLast
+	return p1.NullsFirst != p2.NullsFirst || p1.NullsLast != p2.NullsLast
 }
 
 // ReferenceChanged reports if the foreign key referential action was changed.
diff --git a/sql/postgres/inspect.go b/sql/postgres/inspect.go
index 94655bd1..aee5ec30 100644
--- a/sql/postgres/inspect.go
+++ b/sql/postgres/inspect.go
@@ -356,10 +356,10 @@ func (i *inspect) addIndexes(t *schema.Table, rows *sql.Rows) error {
 		var (
 			name, typ                            string
 			uniq, primary                        bool
-			asc, desc, nullsfirst, nullslast     sql.NullBool
+			desc, nullsfirst, nullslast          sql.NullBool
 			column, contype, pred, expr, comment sql.NullString
 		)
-		if err := rows.Scan(&name, &typ, &column, &primary, &uniq, &contype, &pred, &expr, &asc, &desc, &nullsfirst, &nullslast, &comment); err != nil {
+		if err := rows.Scan(&name, &typ, &column, &primary, &uniq, &contype, &pred, &expr, &desc, &nullsfirst, &nullslast, &comment); err != nil {
 			return fmt.Errorf("postgres: scanning index: %w", err)
 		}
 		idx, ok := names[name]
@@ -388,16 +388,12 @@ func (i *inspect) addIndexes(t *schema.Table, rows *sql.Rows) error {
 				t.Indexes = append(t.Indexes, idx)
 			}
 		}
-		part := &schema.IndexPart{
-			SeqNo: len(idx.Parts) + 1,
-			Attrs: []schema.Attr{
-				&IndexColumnProperty{
-					Asc:        asc.Bool,
-					Desc:       desc.Bool,
-					NullsFirst: nullsfirst.Bool,
-					NullsLast:  nullslast.Bool,
-				},
-			},
+		part := &schema.IndexPart{SeqNo: len(idx.Parts) + 1, Desc: desc.Bool}
+		if nullsfirst.Bool || nullslast.Bool {
+			part.Attrs = append(part.Attrs, &IndexColumnProperty{
+				NullsFirst: nullsfirst.Bool,
+				NullsLast:  nullslast.Bool,
+			})
 		}
 		switch {
 		case sqlx.ValidString(expr):
@@ -686,8 +682,6 @@ type (
 	// https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-INDEX-COLUMN-PROPS
 	IndexColumnProperty struct {
 		schema.Attr
-		Asc        bool
-		Desc       bool
 		NullsFirst bool
 		NullsLast  bool
 	}
@@ -790,7 +784,6 @@ SELECT
 	c.contype AS constraint_type,
 	pg_get_expr(idx.indpred, idx.indrelid) AS predicate,
 	pg_get_expr(idx.indexprs, idx.indrelid) AS expression,
-	pg_index_column_has_property(idx.indexrelid, a.attnum, 'asc') AS asc,
 	pg_index_column_has_property(idx.indexrelid, a.attnum, 'desc') AS desc,
 	pg_index_column_has_property(idx.indexrelid, a.attnum, 'nulls_first') AS nulls_first,
 	pg_index_column_has_property(idx.indexrelid, a.attnum, 'nulls_last') AS nulls_last,
diff --git a/sql/postgres/migrate.go b/sql/postgres/migrate.go
index 840fc0a4..67db315f 100644
--- a/sql/postgres/migrate.go
+++ b/sql/postgres/migrate.go
@@ -591,33 +591,33 @@ func (s *state) indexParts(b *sqlx.Builder, parts []*schema.IndexPart) {
 			case part.X != nil:
 				b.WriteString(part.X.(*schema.RawExpr).X)
 			}
-			for _, attr := range parts[i].Attrs {
-				s.partAttr(b, attr)
-			}
+			s.partAttrs(b, parts[i])
 		})
 	})
 }
 
-func (s *state) partAttr(b *sqlx.Builder, attr schema.Attr) {
-	switch attr := attr.(type) {
-	case *IndexColumnProperty:
-		switch {
-		case attr.Desc && attr.NullsLast:
-			b.P("DESC NULL LAST")
-		case attr.Desc:
-			// Rows in descending order are stored
-			// with nulls first by default.
-			b.P("DESC")
-		case attr.Asc && attr.NullsFirst:
-			b.P("NULL FIRST")
-		case attr.Asc && attr.NullsLast:
-			// Do nothing, since B-tree indexes store
-			// rows in ascending order with nulls last.
-		}
-	case *schema.Collation:
-		b.P("COLLATE").Ident(attr.V)
-	default:
-		panic(fmt.Sprintf("unexpected index part attribute: %T", attr))
+func (s *state) partAttrs(b *sqlx.Builder, p *schema.IndexPart) {
+	if p.Desc {
+		b.P("DESC")
+	}
+	for _, attr := range p.Attrs {
+		switch attr := attr.(type) {
+		case *IndexColumnProperty:
+			switch {
+			// Defaults when DESC is specified.
+			case p.Desc && attr.NullsFirst:
+			case p.Desc && attr.NullsLast:
+				b.P("NULL LAST")
+			// Defaults when DESC is not specified.
+			case !p.Desc && attr.NullsLast:
+			case !p.Desc && attr.NullsFirst:
+				b.P("NULL FIRST")
+			}
+		case *schema.Collation:
+			b.P("COLLATE").Ident(attr.V)
+		default:
+			panic(fmt.Sprintf("unexpected index part attribute: %T", attr))
+		}
 	}
 }
 
diff --git a/sql/schema/schema.go b/sql/schema/schema.go
index 14451cb2..15ce189c 100644
--- a/sql/schema/schema.go
+++ b/sql/schema/schema.go
@@ -62,10 +62,15 @@ type (
 	// An IndexPart represents an index part that
 	// can be either an expression or a column.
 	IndexPart struct {
+		// SeqNo represents the sequence number of the key part
+		// in the index.
 		SeqNo int
-		Attrs []Attr
+		// Desc indicates if the key part is stored in descending
+		// order. All databases use ascending order as default.
+		Desc  bool
 		X     Expr
 		C     *Column
+		Attrs []Attr
 	}
 
 	// A ForeignKey represents an index definition.
diff --git a/sql/sqlite/inspect.go b/sql/sqlite/inspect.go
index 9207e032..6a9817ec 100644
--- a/sql/sqlite/inspect.go
+++ b/sql/sqlite/inspect.go
@@ -247,7 +247,10 @@ func (i *inspect) indexInfo(ctx context.Context, t *schema.Table, idx *schema.In
 		if err := rows.Scan(&name, &desc); err != nil {
 			return fmt.Errorf("sqlite: scanning index names: %w", err)
 		}
-		part := &schema.IndexPart{SeqNo: len(idx.Parts) + 1}
+		part := &schema.IndexPart{
+			SeqNo: len(idx.Parts) + 1,
+			Desc:  desc.Bool,
+		}
 		switch c, ok := t.Column(name.String); {
 		case ok:
 			part.C = c
@@ -258,10 +261,6 @@ func (i *inspect) indexInfo(ctx context.Context, t *schema.Table, idx *schema.In
 		default:
 			return fmt.Errorf("sqlite: column %q was not found for index %q", name.String, idx.Name)
 		}
-		if desc.Bool {
-			// The default sort order is "ASC".
-			part.Attrs = append(part.Attrs, &IndexOrder{Desc: desc.Bool})
-		}
 		idx.Parts = append(idx.Parts, part)
 	}
 	return nil
@@ -444,13 +443,6 @@ type (
 		P string
 	}
 
-	// IndexOrder describes the sort order of an index.
-	// See: https://www.sqlite.org/lang_createindex.html#descending_indexes
-	IndexOrder struct {
-		schema.Attr
-		Desc bool
-	}
-
 	// IndexOrigin describes how the index was created.
 	// See: https://www.sqlite.org/pragma.html#pragma_index_list
 	IndexOrigin struct {
diff --git a/sql/sqlite/migrate.go b/sql/sqlite/migrate.go
index f5992323..682e741c 100644
--- a/sql/sqlite/migrate.go
+++ b/sql/sqlite/migrate.go
@@ -254,6 +254,9 @@ func (s *state) indexParts(b *sqlx.Builder, parts []*schema.IndexPart) {
 			case part.X != nil:
 				b.WriteString(part.X.(*schema.RawExpr).X)
 			}
+			if parts[i].Desc {
+				b.P("DESC")
+			}
 		})
 	})
 }


Test patch:
diff --git a/internal/integration/hclsqlspec/hclsqlspec_test.go b/internal/integration/hclsqlspec/hclsqlspec_test.go
index d9d37fa4..89138ed5 100644
--- a/internal/integration/hclsqlspec/hclsqlspec_test.go
+++ b/internal/integration/hclsqlspec/hclsqlspec_test.go
@@ -10,6 +10,7 @@ import (
 	"ariga.io/atlas/sql/schema"
 	"ariga.io/atlas/sql/sqlite"
 	"ariga.io/atlas/sql/sqlspec"
+
 	"github.com/stretchr/testify/require"
 )
 
diff --git a/internal/integration/hclsqlspec/migrate_test.go b/internal/integration/hclsqlspec/migrate_test.go
index 734a6744..cc7e8355 100644
--- a/internal/integration/hclsqlspec/migrate_test.go
+++ b/internal/integration/hclsqlspec/migrate_test.go
@@ -7,6 +7,7 @@ import (
 	"ariga.io/atlas/schema/schemaspec/schemahcl"
 	"ariga.io/atlas/sql/postgres"
 	"ariga.io/atlas/sql/sqlspec"
+
 	"github.com/stretchr/testify/require"
 )
 
diff --git a/internal/integration/integration_test.go b/internal/integration/integration_test.go
index bd870160..816e904d 100644
--- a/internal/integration/integration_test.go
+++ b/internal/integration/integration_test.go
@@ -236,6 +236,28 @@ func testCLISchemaApplyDry(t T, h string, dsn string) {
 	require.False(t, ok, "expected users table not to be created")
 }
 
+func testCLISchemaDiff(t T, dsn string) {
+	// Required to have a clean "stderr" while running first time.
+	err := exec.Command("go", "run", "-mod=mod", "ariga.io/atlas/cmd/atlas").Run()
+
+	require.NoError(t, err)
+	t.dropTables("users")
+	cmd := exec.Command("go", "run", "ariga.io/atlas/cmd/atlas",
+		"schema",
+		"diff",
+		"--from",
+		dsn,
+		"--to",
+		dsn,
+	)
+	stdout, stderr := bytes.NewBuffer(nil), bytes.NewBuffer(nil)
+	cmd.Stderr = stderr
+	cmd.Stdout = stdout
+	require.NoError(t, cmd.Run(), stderr.String(), stdout.String())
+	require.Empty(t, stderr.String(), stderr.String())
+	require.Contains(t, stdout.String(), "Schemas are synced, no changes to be made.")
+}
+
 func TestCLI_Version(t *testing.T) {
 	// Required to have a clean "stderr" while running first time.
 	require.NoError(t, exec.Command("go", "run", "-mod=mod", "ariga.io/atlas/cmd/atlas").Run())
diff --git a/internal/integration/mysql_test.go b/internal/integration/mysql_test.go
index da2254c5..35539b42 100644
--- a/internal/integration/mysql_test.go
+++ b/internal/integration/mysql_test.go
@@ -544,6 +544,11 @@ func TestMySQL_CLI(t *testing.T) {
 			testCLISchemaApplyDry(t, fmt.Sprintf(h, charset.V, collate.V), t.dsn())
 		})
 	})
+	t.Run("SchemaDiffRun", func(t *testing.T) {
+		myRun(t, func(t *myTest) {
+			testCLISchemaDiff(t, t.dsn())
+		})
+	})
 }
 
 func TestMySQL_HCL_Realm(t *testing.T) {
@@ -600,45 +605,47 @@ func TestMySQL_Sanity(t *testing.T) {
 		ddl := `
 create table atlas_types_sanity
 (
-    tBit                 bit(10)              default b'100'               null,
-    tInt                 int(10)              default 4                not null,
-    tTinyInt             tinyint(10)          default 8                    null,
-    tSmallInt            smallint(10)         default 2                    null,
-    tMediumInt           mediumint(10)        default 11                   null,
-    tBigInt              bigint(10)           default 4                    null,
-    tDecimal             decimal              default 4                    null,
-    tNumeric             numeric              default 4                not null,
-    tFloat               float(10, 0)         default 4                    null,
-    tDouble              double(10, 0)        default 4                    null,
-    tReal                double(10, 0)        default 4                    null,
-    tTimestamp           timestamp            default CURRENT_TIMESTAMP    null,
-    tTimestampFraction   timestamp(6)         default CURRENT_TIMESTAMP(6) null,
-    tDate                date                                              null,
-    tTime                time                                              null,
-    tDateTime            datetime                                          null,
-    tYear                year                                              null,
-    tVarchar             varchar(10)          default 'Titan'              null,
-    tChar                char(25)             default 'Olimpia'        not null,
-    tVarBinary           varbinary(30)        default 'Titan'              null,
-    tBinary              binary(5)            default 'Titan'              null,
-    tBlob                blob(5)              default                      null,
-    tTinyBlob            tinyblob                                          null,
-    tMediumBlob          mediumblob           default                      null,
-    tLongBlob            longblob             default                      null,
-    tText                text(13)             default                      null,
-    tTinyText            tinytext             default                      null,
-    tMediumText          mediumtext           default                      null,
-    tLongText            longtext             default                      null,
-    tEnum                enum('a','b')        default                      null,
-    tSet                 set('a','b')         default                      null,
-    tGeometry            geometry             default                      null,
-    tPoint               point                default                      null,
-    tMultiPoint          multipoint           default                      null,
-    tLineString          linestring           default                      null,
-    tMultiLineString     multilinestring      default                      null,
-    tPolygon             polygon              default                      null,
-    tMultiPolygon        multipolygon         default                      null,
-    tGeometryCollection  geometrycollection   default                      null
+    tBit                        bit(10)              default b'100'                                              null,
+    tInt                        int(10)              default 4                                               not null,
+    tTinyInt                    tinyint(10)          default 8                                                   null,
+    tSmallInt                   smallint(10)         default 2                                                   null,
+    tMediumInt                  mediumint(10)        default 11                                                  null,
+    tBigInt                     bigint(10)           default 4                                                   null,
+    tDecimal                    decimal              default 4                                                   null,
+    tNumeric                    numeric              default 4                                               not null,
+    tFloat                      float(10, 0)         default 4                                                   null,
+    tDouble                     double(10, 0)        default 4                                                   null,
+    tReal                       double(10, 0)        default 4                                                   null,
+    tTimestamp                  timestamp            default CURRENT_TIMESTAMP                                   null,
+    tTimestampFraction          timestamp(6)         default CURRENT_TIMESTAMP(6)                                null,
+    tTimestampOnUpdate          timestamp            default CURRENT_TIMESTAMP    ON UPDATE CURRENT_TIMESTAMP    null,
+    tTimestampFractionOnUpdate  timestamp(6)         default CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) null,
+    tDate                       date                                                                             null,
+    tTime                       time                                                                             null,
+    tDateTime                   datetime                                                                         null,
+    tYear                       year                                                                             null,
+    tVarchar                    varchar(10)          default 'Titan'                                             null,
+    tChar                       char(25)             default 'Olimpia'                                       not null,
+    tVarBinary                  varbinary(30)        default 'Titan'                                             null,
+    tBinary                     binary(5)            default 'Titan'                                             null,
+    tBlob                       blob(5)              default                                                     null,
+    tTinyBlob                   tinyblob                                                                         null,
+    tMediumBlob                 mediumblob           default                                                     null,
+    tLongBlob                   longblob             default                                                     null,
+    tText                       text(13)             default                                                     null,
+    tTinyText                   tinytext             default                                                     null,
+    tMediumText                 mediumtext           default                                                     null,
+    tLongText                   longtext             default                                                     null,
+    tEnum                       enum('a','b')        default                                                     null,
+    tSet                        set('a','b')         default                                                     null,
+    tGeometry                   geometry             default                                                     null,
+    tPoint                      point                default                                                     null,
+    tMultiPoint                 multipoint           default                                                     null,
+    tLineString                 linestring           default                                                     null,
+    tMultiLineString            multilinestring      default                                                     null,
+    tPolygon                    polygon              default                                                     null,
+    tMultiPolygon               multipolygon         default                                                     null,
+    tGeometryCollection         geometrycollection   default                                                     null
 ) CHARSET = latin1 COLLATE latin1_swedish_ci;
 `
 		myRun(t, func(t *myTest) {
@@ -748,6 +755,52 @@ create table atlas_types_sanity
 							}(),
 						},
 					},
+					{
+						Name: "tTimestampOnUpdate",
+						Type: &schema.ColumnType{Type: &schema.TimeType{T: "timestamp"},
+							Raw: "timestamp", Null: true},
+						Default: &schema.RawExpr{
+							X: func() string {
+								if t.mariadb() {
+									return "current_timestamp()"
+								}
+								return "CURRENT_TIMESTAMP"
+							}(),
+						},
+						Attrs: []schema.Attr{
+							&mysql.OnUpdate{
+								A: func() string {
+									if t.mariadb() {
+										return "current_timestamp()"
+									}
+									return "CURRENT_TIMESTAMP"
+								}(),
+							},
+						},
+					},
+					{
+						Name: "tTimestampFractionOnUpdate",
+						Type: &schema.ColumnType{Type: &schema.TimeType{T: "timestamp", Precision: 6},
+							Raw: "timestamp(6)", Null: true},
+						Default: &schema.RawExpr{
+							X: func() string {
+								if t.mariadb() {
+									return "current_timestamp(6)"
+								}
+								return "CURRENT_TIMESTAMP(6)"
+							}(),
+						},
+						Attrs: []schema.Attr{
+							&mysql.OnUpdate{
+								A: func() string {
+									if t.mariadb() {
+										return "current_timestamp(6)"
+									}
+									return "CURRENT_TIMESTAMP(6)"
+								}(),
+							},
+						},
+					},
 					{
 						Name: "tDate",
 						Type: &schema.ColumnType{Type: &schema.TimeType{T: "date"},
diff --git a/internal/integration/postgres_test.go b/internal/integration/postgres_test.go
index f9fa5013..ea776bf0 100644
--- a/internal/integration/postgres_test.go
+++ b/internal/integration/postgres_test.go
@@ -473,6 +473,11 @@ func TestPostgres_CLI(t *testing.T) {
 			testCLISchemaApplyDry(t, h, t.dsn())
 		})
 	})
+	t.Run("SchemaDiffRun", func(t *testing.T) {
+		pgRun(t, func(t *pgTest) {
+			testCLISchemaDiff(t, t.dsn())
+		})
+	})
 }
 
 func TestPostgres_DefaultsHCL(t *testing.T) {
diff --git a/internal/integration/sqlite_test.go b/internal/integration/sqlite_test.go
index 9dc330ff..fbfc48b4 100644
--- a/internal/integration/sqlite_test.go
+++ b/internal/integration/sqlite_test.go
@@ -452,6 +452,11 @@ func TestSQLite_CLI(t *testing.T) {
 			testCLISchemaApplyDry(t, h, t.dsn())
 		})
 	})
+	t.Run("SchemaDiffRun", func(t *testing.T) {
+		liteRun(t, func(t *liteTest) {
+			testCLISchemaDiff(t, t.dsn())
+		})
+	})
 }
 
 func TestSQLite_Sanity(t *testing.T) {
diff --git a/schema/schemaspec/schemahcl/hcl_test.go b/schema/schemaspec/schemahcl/hcl_test.go
index 06271770..49cb3c95 100644
--- a/schema/schemaspec/schemahcl/hcl_test.go
+++ b/schema/schemaspec/schemahcl/hcl_test.go
@@ -6,6 +6,7 @@ import (
 	"testing"
 
 	"ariga.io/atlas/schema/schemaspec"
+
 	"github.com/stretchr/testify/require"
 )
 
diff --git a/sql/internal/spectest/spectest.go b/sql/internal/spectest/spectest.go
index ee41fd81..3bd8a7f9 100644
--- a/sql/internal/spectest/spectest.go
+++ b/sql/internal/spectest/spectest.go
@@ -10,6 +10,7 @@ import (
 
 	"ariga.io/atlas/schema/schemaspec"
 	"ariga.io/atlas/sql/internal/specutil"
+
 	"github.com/stretchr/testify/require"
 )
 
diff --git a/sql/internal/specutil/convert_test.go b/sql/internal/specutil/convert_test.go
index 9cac1a16..80c78602 100644
--- a/sql/internal/specutil/convert_test.go
+++ b/sql/internal/specutil/convert_test.go
@@ -6,6 +6,7 @@ import (
 	"ariga.io/atlas/schema/schemaspec"
 	"ariga.io/atlas/sql/schema"
 	"ariga.io/atlas/sql/sqlspec"
+
 	"github.com/stretchr/testify/require"
 )
 
diff --git a/sql/internal/specutil/types_test.go b/sql/internal/specutil/types_test.go
index b6655c53..1f808529 100644
--- a/sql/internal/specutil/types_test.go
+++ b/sql/internal/specutil/types_test.go
@@ -6,6 +6,7 @@ import (
 
 	"ariga.io/atlas/schema/schemaspec"
 	"ariga.io/atlas/sql/schema"
+
 	"github.com/stretchr/testify/require"
 )
 
diff --git a/sql/mysql/diff_test.go b/sql/mysql/diff_test.go
index c3cf8c40..458ffbb7 100644
--- a/sql/mysql/diff_test.go
+++ b/sql/mysql/diff_test.go
@@ -256,11 +256,13 @@ func TestDiff_TableDiff(t *testing.T) {
 				{Name: "c1_index", Unique: true, Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[0]}}},
 				{Name: "c2_unique", Unique: true, Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}},
 				{Name: "c1_prefix", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1], Attrs: []schema.Attr{&SubPart{Len: 50}}}}},
+				{Name: "c1_desc", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}},
 			}
 			to.Indexes = []*schema.Index{
 				{Name: "c1_index", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[0]}}},
 				{Name: "c3_unique", Unique: true, Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: to.Columns[1]}}},
 				{Name: "c1_prefix", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[0], Attrs: []schema.Attr{&SubPart{Len: 100}}}}},
+				{Name: "c1_desc", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1], Desc: true}}},
 			}
 			return testcase{
 				name: "indexes",
@@ -270,6 +272,7 @@ func TestDiff_TableDiff(t *testing.T) {
 					&schema.ModifyIndex{From: from.Indexes[0], To: to.Indexes[0], Change: schema.ChangeUnique},
 					&schema.DropIndex{I: from.Indexes[1]},
 					&schema.ModifyIndex{From: from.Indexes[2], To: to.Indexes[2], Change: schema.ChangeParts},
+					&schema.ModifyIndex{From: from.Indexes[3], To: to.Indexes[3], Change: schema.ChangeParts},
 					&schema.AddIndex{I: to.Indexes[1]},
 				},
 			}
diff --git a/sql/mysql/inspect_test.go b/sql/mysql/inspect_test.go
index 2158c7c0..0dabbb9a 100644
--- a/sql/mysql/inspect_test.go
+++ b/sql/mysql/inspect_test.go
@@ -84,11 +84,11 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(sqltest.Escape(indexesExprQuery)).
 					WithArgs("test", "users").
 					WillReturnRows(sqltest.Rows(`
-+--------------+-------------+------------+--------------+--------------+--------------+--------------+------------+------------------+
-| INDEX_NAME   | COLUMN_NAME | NON_UNIQUE | SEQ_IN_INDEX | INDEX_TYPE   | COLLATION    | COMMENT      | SUB_PART   | EXPRESSION       |
-+--------------+-------------+------------+--------------+--------------+--------------+--------------+------------+------------------+
-| PRIMARY      | id          |          0 |            1 | BTREE        | A            |              |       NULL |      NULL        |
-+--------------+-------------+------------+--------------+--------------+--------------+--------------+------------+------------------+
++--------------+-------------+------------+--------------+--------------+----------+--------------+------------+------------------+
+| INDEX_NAME   | COLUMN_NAME | NON_UNIQUE | SEQ_IN_INDEX | INDEX_TYPE   | DESC     | COMMENT      | SUB_PART   | EXPRESSION       |
++--------------+-------------+------------+--------------+--------------+----------+--------------+------------+------------------+
+| PRIMARY      | id          |          0 |            1 | BTREE        | 0        |              |       NULL |      NULL        |
++--------------+-------------+------------+--------------+--------------+----------+--------------+------------+------------------+
 `))
 				m.noFKs()
 			},
@@ -383,10 +383,10 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c1", Type: &schema.ColumnType{Raw: "date", Type: &schema.TimeType{T: "date"}}},
 					{Name: "c2", Type: &schema.ColumnType{Raw: "datetime", Type: &schema.TimeType{T: "datetime"}}},
 					{Name: "c3", Type: &schema.ColumnType{Raw: "time", Type: &schema.TimeType{T: "time"}}},
-					{Name: "c4", Type: &schema.ColumnType{Raw: "timestamp", Type: &schema.TimeType{T: "timestamp"}}, Default: &schema.RawExpr{X: "CURRENT_TIMESTAMP"}, Attrs: []schema.Attr{&OnUpdate{A: "on update current_timestamp"}}},
+					{Name: "c4", Type: &schema.ColumnType{Raw: "timestamp", Type: &schema.TimeType{T: "timestamp"}}, Default: &schema.RawExpr{X: "CURRENT_TIMESTAMP"}, Attrs: []schema.Attr{&OnUpdate{A: "CURRENT_TIMESTAMP"}}},
 					{Name: "c5", Type: &schema.ColumnType{Raw: "year(4)", Type: &schema.TimeType{T: "year", Precision: 4}}},
 					{Name: "c6", Type: &schema.ColumnType{Raw: "year", Type: &schema.TimeType{T: "year"}}},
-					{Name: "c7", Type: &schema.ColumnType{Raw: "timestamp(6)", Type: &schema.TimeType{T: "timestamp", Precision: 6}}, Default: &schema.RawExpr{X: "CURRENT_TIMESTAMP(6)"}, Attrs: []schema.Attr{&OnUpdate{A: "on update current_timestamp(6)"}}},
+					{Name: "c7", Type: &schema.ColumnType{Raw: "timestamp(6)", Type: &schema.TimeType{T: "timestamp", Precision: 6}}, Default: &schema.RawExpr{X: "CURRENT_TIMESTAMP(6)"}, Attrs: []schema.Attr{&OnUpdate{A: "CURRENT_TIMESTAMP(6)"}}},
 				}, t.Columns)
 			},
 		},
@@ -476,17 +476,17 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(sqltest.Escape(indexesExprQuery)).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+--------------+-------------+------------+--------------+--------------+--------------+--------------+------------+------------------+
-| INDEX_NAME   | COLUMN_NAME | NON_UNIQUE | SEQ_IN_INDEX | INDEX_TYPE   | COLLATION    | COMMENT      | SUB_PART   | EXPRESSION       |
-+--------------+-------------+------------+--------------+--------------+--------------+--------------+------------+------------------+
-| nickname     | nickname    |          0 |            1 | BTREE        | A            |              |        255 |      NULL        |
-| lower_nick   | NULL        |          1 |            1 | HASH         | A            |              |       NULL | lower(nickname)  |
-| non_unique   | oid         |          1 |            1 | BTREE        | A            |              |       NULL |      NULL        |
-| non_unique   | uid         |          1 |            2 | BTREE        | A            |              |       NULL |      NULL        |
-| PRIMARY      | id          |          0 |            1 | BTREE        | A            |              |       NULL |      NULL        |
-| unique_index | uid         |          0 |            1 | BTREE        | A            |              |       NULL |      NULL        |
-| unique_index | oid         |          0 |            2 | BTREE        | A            |              |       NULL |      NULL        |
-+--------------+-------------+------------+--------------+--------------+--------------+--------------+------------+------------------+
++--------------+-------------+------------+--------------+--------------+---------+--------------+------------+------------------+
+| INDEX_NAME   | COLUMN_NAME | NON_UNIQUE | SEQ_IN_INDEX | INDEX_TYPE   | DESC    | COMMENT      | SUB_PART   | EXPRESSION       |
++--------------+-------------+------------+--------------+--------------+---------+--------------+------------+------------------+
+| nickname     | nickname    |          0 |            1 | BTREE        | 0       |              |        255 |      NULL        |
+| lower_nick   | NULL        |          1 |            1 | HASH         | 0       |              |       NULL | lower(nickname)  |
+| non_unique   | oid         |          1 |            1 | BTREE        | 0       |              |       NULL |      NULL        |
+| non_unique   | uid         |          1 |            2 | BTREE        | 0       |              |       NULL |      NULL        |
+| PRIMARY      | id          |          0 |            1 | BTREE        | 0       |              |       NULL |      NULL        |
+| unique_index | uid         |          0 |            1 | BTREE        | 1       |              |       NULL |      NULL        |
+| unique_index | oid         |          0 |            2 | BTREE        | 1       |              |       NULL |      NULL        |
++--------------+-------------+------------+--------------+--------------+---------+--------------+------------+------------------+
 `))
 				m.noFKs()
 			},
@@ -507,21 +507,21 @@ func TestDriver_InspectTable(t *testing.T) {
 				}
 				// nickname
 				indexes[0].Parts = []*schema.IndexPart{
-					{SeqNo: 1, C: columns[1], Attrs: []schema.Attr{&schema.Collation{V: "A"}, &SubPart{Len: 255}}},
+					{SeqNo: 1, C: columns[1], Attrs: []schema.Attr{&SubPart{Len: 255}}},
 				}
 				// lower(nickname)
 				indexes[1].Parts = []*schema.IndexPart{
-					{SeqNo: 1, X: &schema.RawExpr{X: "lower(nickname)"}, Attrs: []schema.Attr{&schema.Collation{V: "A"}}},
+					{SeqNo: 1, X: &schema.RawExpr{X: "lower(nickname)"}},
 				}
 				// oid, uid
 				indexes[2].Parts = []*schema.IndexPart{
-					{SeqNo: 1, C: columns[2], Attrs: []schema.Attr{&schema.Collation{V: "A"}}},
-					{SeqNo: 2, C: columns[3], Attrs: []schema.Attr{&schema.Collation{V: "A"}}},
+					{SeqNo: 1, C: columns[2]},
+					{SeqNo: 2, C: columns[3]},
 				}
 				// uid, oid
 				indexes[3].Parts = []*schema.IndexPart{
-					{SeqNo: 1, C: columns[3], Attrs: []schema.Attr{&schema.Collation{V: "A"}}},
-					{SeqNo: 2, C: columns[2], Attrs: []schema.Attr{&schema.Collation{V: "A"}}},
+					{SeqNo: 1, C: columns[3], Desc: true},
+					{SeqNo: 2, C: columns[2], Desc: true},
 				}
 				require.EqualValues(columns, t.Columns)
 				require.EqualValues(indexes, t.Indexes)
diff --git a/sql/mysql/migrate_test.go b/sql/mysql/migrate_test.go
index c4725519..d9d18637 100644
--- a/sql/mysql/migrate_test.go
+++ b/sql/mysql/migrate_test.go
@@ -116,7 +116,7 @@ func TestMigrate_ApplyChanges(t *testing.T) {
 							Name: "id_spouse_id",
 							Parts: []*schema.IndexPart{
 								{C: users.Columns[1]},
-								{C: users.Columns[0], Attrs: []schema.Attr{&schema.Collation{V: "D"}}},
+								{C: users.Columns[0], Desc: true},
 							},
 							Attrs: []schema.Attr{
 								&schema.Comment{Text: "comment"},
@@ -288,7 +288,7 @@ func TestPlanChanges(t *testing.T) {
 							{
 								Name: "text_prefix",
 								Parts: []*schema.IndexPart{
-									{Attrs: []schema.Attr{&SubPart{Len: 100}, &schema.Collation{V: "D"}}},
+									{Desc: true, Attrs: []schema.Attr{&SubPart{Len: 100}}},
 								},
 							},
 						},
diff --git a/sql/mysql/sqlspec_test.go b/sql/mysql/sqlspec_test.go
index e886283a..4085c9c9 100644
--- a/sql/mysql/sqlspec_test.go
+++ b/sql/mysql/sqlspec_test.go
@@ -30,6 +30,15 @@ table "table" {
 	column "account_name" {
 		type = varchar(32)
 	}
+	column "created_at" {
+		type    = datetime(4)
+		default = sql("now(4)")
+	}
+	column "updated_at" {
+		type      = timestamp(6)
+		default   = sql("current_timestamp(6)")
+		on_update = sql("current_timestamp(6)")
+	}
 	primary_key {
 		columns = [table.table.column.col]
 	}
@@ -129,6 +138,27 @@ table "accounts" {
 						},
 					},
 				},
+				{
+					Name: "created_at",
+					Type: &schema.ColumnType{
+						Type: &schema.TimeType{
+							T:         TypeDateTime,
+							Precision: 4,
+						},
+					},
+					Default: &schema.RawExpr{X: "now(4)"},
+				},
+				{
+					Name: "updated_at",
+					Type: &schema.ColumnType{
+						Type: &schema.TimeType{
+							T:         TypeTimestamp,
+							Precision: 6,
+						},
+					},
+					Default: &schema.RawExpr{X: "current_timestamp(6)"},
+					Attrs:   []schema.Attr{&OnUpdate{A: "current_timestamp(6)"}},
+				},
 			},
 			Attrs: []schema.Attr{
 				&schema.Check{
diff --git a/sql/postgres/inspect_test.go b/sql/postgres/inspect_test.go
index b76e1063..f82ec768 100644
--- a/sql/postgres/inspect_test.go
+++ b/sql/postgres/inspect_test.go
@@ -154,14 +154,14 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(sqltest.Escape(indexesQuery)).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-    index_name   | index_type  | column_name | primary | unique | constraint_type | predicate             |   expression              | asc | desc | nulls_first | nulls_last | comment
------------------+-------------+-------------+---------+--------+-----------------+-----------------------+---------------------------+-----+------+-------------+------------+-----------
- idx             | hash        | left        | f       | f      |                 |                       | "left"((c11)::text, 100)  | f   | t    | t           | f          | boring
- idx1            | btree       | left        | f       | f      |                 | (id <> NULL::integer) | "left"((c11)::text, 100)  | f   | t    | t           | f          |
- t1_c1_key       | btree       | c1          | f       | t      | u               |                       |                           | f   | t    | t           | f          |
- t1_pkey         | btree       | id          | t       | t      | p               |                       |                           | f   | t    | f           | f          |
- idx4            | btree       | c1          | f       | t      |                 |                       |                           | t   | f    | f           | f          |
- idx4            | btree       | id          | f       | t      |                 |                       |                           | t   | f    | f           | t          |
+    index_name   | index_type  | column_name | primary | unique | constraint_type | predicate             |   expression              | desc | nulls_first | nulls_last | comment
+-----------------+-------------+-------------+---------+--------+-----------------+-----------------------+---------------------------+------+-------------+------------+-----------
+ idx             | hash        | left        | f       | f      |                 |                       | "left"((c11)::text, 100)  | t    | t           | f          | boring
+ idx1            | btree       | left        | f       | f      |                 | (id <> NULL::integer) | "left"((c11)::text, 100)  | t    | t           | f          |
+ t1_c1_key       | btree       | c1          | f       | t      | u               |                       |                           | t    | t           | f          |
+ t1_pkey         | btree       | id          | t       | t      | p               |                       |                           | t    | f           | f          |
+ idx4            | btree       | c1          | f       | t      |                 |                       |                           | f    | f           | f          |
+ idx4            | btree       | id          | f       | t      |                 |                       |                           | f    | f           | t          |
 
 `))
 				m.noFKs()
@@ -175,17 +175,17 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c1", Type: &schema.ColumnType{Raw: "smallint", Type: &schema.IntegerType{T: "smallint"}}},
 				}
 				indexes := []*schema.Index{
-					{Name: "idx", Table: t, Attrs: []schema.Attr{&IndexType{T: "hash"}, &schema.Comment{Text: "boring"}}, Parts: []*schema.IndexPart{{SeqNo: 1, X: &schema.RawExpr{X: `"left"((c11)::text, 100)`}, Attrs: []schema.Attr{&IndexColumnProperty{Desc: true, NullsFirst: true}}}}},
-					{Name: "idx1", Table: t, Attrs: []schema.Attr{&IndexType{T: "btree"}, &IndexPredicate{P: `(id <> NULL::integer)`}}, Parts: []*schema.IndexPart{{SeqNo: 1, X: &schema.RawExpr{X: `"left"((c11)::text, 100)`}, Attrs: []schema.Attr{&IndexColumnProperty{Desc: true, NullsFirst: true}}}}},
-					{Name: "t1_c1_key", Unique: true, Table: t, Attrs: []schema.Attr{&IndexType{T: "btree"}, &ConType{T: "u"}}, Parts: []*schema.IndexPart{{SeqNo: 1, C: columns[1], Attrs: []schema.Attr{&IndexColumnProperty{Desc: true, NullsFirst: true}}}}},
-					{Name: "idx4", Unique: true, Table: t, Attrs: []schema.Attr{&IndexType{T: "btree"}}, Parts: []*schema.IndexPart{{SeqNo: 1, C: columns[1], Attrs: []schema.Attr{&IndexColumnProperty{Asc: true}}}, {SeqNo: 2, C: columns[0], Attrs: []schema.Attr{&IndexColumnProperty{Asc: true, NullsLast: true}}}}},
+					{Name: "idx", Table: t, Attrs: []schema.Attr{&IndexType{T: "hash"}, &schema.Comment{Text: "boring"}}, Parts: []*schema.IndexPart{{SeqNo: 1, X: &schema.RawExpr{X: `"left"((c11)::text, 100)`}, Desc: true, Attrs: []schema.Attr{&IndexColumnProperty{NullsFirst: true}}}}},
+					{Name: "idx1", Table: t, Attrs: []schema.Attr{&IndexType{T: "btree"}, &IndexPredicate{P: `(id <> NULL::integer)`}}, Parts: []*schema.IndexPart{{SeqNo: 1, X: &schema.RawExpr{X: `"left"((c11)::text, 100)`}, Desc: true, Attrs: []schema.Attr{&IndexColumnProperty{NullsFirst: true}}}}},
+					{Name: "t1_c1_key", Unique: true, Table: t, Attrs: []schema.Attr{&IndexType{T: "btree"}, &ConType{T: "u"}}, Parts: []*schema.IndexPart{{SeqNo: 1, C: columns[1], Desc: true, Attrs: []schema.Attr{&IndexColumnProperty{NullsFirst: true}}}}},
+					{Name: "idx4", Unique: true, Table: t, Attrs: []schema.Attr{&IndexType{T: "btree"}}, Parts: []*schema.IndexPart{{SeqNo: 1, C: columns[1]}, {SeqNo: 2, C: columns[0], Attrs: []schema.Attr{&IndexColumnProperty{NullsLast: true}}}}},
 				}
 				pk := &schema.Index{
 					Name:   "t1_pkey",
 					Unique: true,
 					Table:  t,
 					Attrs:  []schema.Attr{&IndexType{T: "btree"}, &ConType{T: "p"}},
-					Parts:  []*schema.IndexPart{{SeqNo: 1, C: columns[0], Attrs: []schema.Attr{&IndexColumnProperty{Desc: true}}}},
+					Parts:  []*schema.IndexPart{{SeqNo: 1, C: columns[0], Desc: true}},
 				}
 				columns[0].Indexes = append(columns[0].Indexes, pk, indexes[3])
 				columns[1].Indexes = indexes[2:]
diff --git a/sql/postgres/migrate_test.go b/sql/postgres/migrate_test.go
index 552db583..6e6ef1b5 100644
--- a/sql/postgres/migrate_test.go
+++ b/sql/postgres/migrate_test.go
@@ -145,7 +145,7 @@ func TestPlanChanges(t *testing.T) {
 								I: &schema.Index{
 									Name: "id_key",
 									Parts: []*schema.IndexPart{
-										{C: users.Columns[0]},
+										{C: users.Columns[0], Desc: true},
 									},
 									Attrs: []schema.Attr{
 										&schema.Comment{Text: "comment"},
@@ -175,7 +175,7 @@ func TestPlanChanges(t *testing.T) {
 						Reverse: `ALTER TABLE "users" DROP COLUMN "name", DROP CONSTRAINT "name_not_empty", ADD CONSTRAINT "id_nonzero" CHECK ("id" <> 0), DROP CONSTRAINT "id_iseven", ADD CONSTRAINT "id_iseven" CHECK ("id" % 2 = 0)`,
 					},
 					{
-						Cmd:     `CREATE INDEX "id_key" ON "users" ("id")`,
+						Cmd:     `CREATE INDEX "id_key" ON "users" ("id" DESC)`,
 						Reverse: `DROP INDEX "id_key"`,
 					},
 					{
diff --git a/sql/postgres/sqlspec_test.go b/sql/postgres/sqlspec_test.go
index 65c9f4e8..b93e696f 100644
--- a/sql/postgres/sqlspec_test.go
+++ b/sql/postgres/sqlspec_test.go
@@ -6,6 +6,7 @@ import (
 
 	"ariga.io/atlas/sql/internal/spectest"
 	"ariga.io/atlas/sql/schema"
+
 	"github.com/stretchr/testify/require"
 )
 
diff --git a/sql/sqlite/diff_test.go b/sql/sqlite/diff_test.go
index 90841504..0a735cbf 100644
--- a/sql/sqlite/diff_test.go
+++ b/sql/sqlite/diff_test.go
@@ -164,11 +164,13 @@ func TestDiff_TableDiff(t *testing.T) {
 				{Name: "c1_index", Unique: true, Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[0]}}},
 				{Name: "c2_unique", Unique: true, Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}},
 				{Name: "c3_predicate", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}},
+				{Name: "c3_desc", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: to.Columns[1]}}},
 			}
 			to.Indexes = []*schema.Index{
 				{Name: "c1_index", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[0]}}},
 				{Name: "c3_unique", Unique: true, Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: to.Columns[1]}}},
 				{Name: "c3_predicate", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}, Attrs: []schema.Attr{&IndexPredicate{P: "c3 <> NULL"}}},
+				{Name: "c3_desc", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, Desc: true, C: to.Columns[1]}}},
 			}
 			return testcase{
 				name: "indexes",
@@ -178,6 +180,7 @@ func TestDiff_TableDiff(t *testing.T) {
 					&schema.ModifyIndex{From: from.Indexes[0], To: to.Indexes[0], Change: schema.ChangeUnique},
 					&schema.DropIndex{I: from.Indexes[1]},
 					&schema.ModifyIndex{From: from.Indexes[2], To: to.Indexes[2], Change: schema.ChangeAttr},
+					&schema.ModifyIndex{From: from.Indexes[3], To: to.Indexes[3], Change: schema.ChangeParts},
 					&schema.AddIndex{I: to.Indexes[1]},
 				},
 			}
diff --git a/sql/sqlite/inspect_test.go b/sql/sqlite/inspect_test.go
index a516cf73..9e520eda 100644
--- a/sql/sqlite/inspect_test.go
+++ b/sql/sqlite/inspect_test.go
@@ -131,7 +131,7 @@ func TestDriver_InspectTable(t *testing.T) {
 						Unique: true,
 						Table:  t,
 						Parts: []*schema.IndexPart{
-							{SeqNo: 1, C: columns[0], Attrs: []schema.Attr{&IndexOrder{Desc: true}}},
+							{SeqNo: 1, C: columns[0], Desc: true},
 							{SeqNo: 2, C: columns[1]},
 						},
 						Attrs: []schema.Attr{
diff --git a/sql/sqlite/migrate_test.go b/sql/sqlite/migrate_test.go
index 61b20ba7..073cb3ff 100644
--- a/sql/sqlite/migrate_test.go
+++ b/sql/sqlite/migrate_test.go
@@ -13,6 +13,7 @@ import (
 
 	"ariga.io/atlas/sql/migrate"
 	"ariga.io/atlas/sql/schema"
+
 	"github.com/DATA-DOG/go-sqlmock"
 	"github.com/stretchr/testify/require"
 )
diff --git a/sql/sqlite/sqlspec_test.go b/sql/sqlite/sqlspec_test.go
index e7b721a9..a581a400 100644
--- a/sql/sqlite/sqlspec_test.go
+++ b/sql/sqlite/sqlspec_test.go
@@ -9,6 +9,7 @@ import (
 	"testing"
 
 	"ariga.io/atlas/sql/schema"
+
 	"github.com/stretchr/testify/require"
 )