Repository: ariga/atlas
Pull request #1389 (2023-01-05 16:12:10)

doc/md/guides: Add article about functional indexes in MySQL


Patch:
diff --git a/cmd/atlas/go.mod b/cmd/atlas/go.mod
index a218e36f..f0931951 100644
--- a/cmd/atlas/go.mod
+++ b/cmd/atlas/go.mod
@@ -3,7 +3,7 @@ module ariga.io/atlas/cmd/atlas
 go 1.18
 
 require (
-	ariga.io/atlas v0.8.4-0.20221128070800-1ce9c6ac2c02
+	ariga.io/atlas v0.9.0
 	entgo.io/ent v0.11.5-0.20221123165602-b6a475f066c4
 	github.com/antlr/antlr4/runtime/Go/antlr v0.0.0-20220816024939-bc8df83d7b9d
 	github.com/auxten/postgresql-parser v1.0.1
diff --git a/cmd/atlas/go.sum b/cmd/atlas/go.sum
index 0aecc01d..73bf2dce 100644
--- a/cmd/atlas/go.sum
+++ b/cmd/atlas/go.sum
@@ -1,5 +1,9 @@
+ariga.io/atlas v0.8.3 h1:nddOywkhr/62Cwa+UsGgO35lAhUYh52XGVsbFwGzWZM=
+ariga.io/atlas v0.8.3/go.mod h1:T230JFcENj4ZZzMkZrXFDSkv+2kXkUgpJ5FQQ5hMcKU=
 ariga.io/atlas v0.8.4-0.20221128070800-1ce9c6ac2c02 h1:OvAZ24R0xneE3I8UR3bqCyiMAR/yETEbhI1XNzpkrUY=
 ariga.io/atlas v0.8.4-0.20221128070800-1ce9c6ac2c02/go.mod h1:T230JFcENj4ZZzMkZrXFDSkv+2kXkUgpJ5FQQ5hMcKU=
+ariga.io/atlas v0.9.0 h1:q0JMtqyA3X1YWtPcn+E/kVPwLDslb+jAC8Ejl/vW6d0=
+ariga.io/atlas v0.9.0/go.mod h1:T230JFcENj4ZZzMkZrXFDSkv+2kXkUgpJ5FQQ5hMcKU=
 bazil.org/fuse v0.0.0-20160811212531-371fbbdaa898/go.mod h1:Xbm+BRKSBEpa4q4hTSxohYNQpsxXPbPry4JJWOB3LB8=
 bazil.org/fuse v0.0.0-20200407214033-5883e5a4b512/go.mod h1:FbcW6z/2VytnFDhZfumh8Ss8zxHE6qpMP5sHTRe0EaM=
 cloud.google.com/go v0.26.0/go.mod h1:aQUYkXzVsufM+DwF1aE+0xfcU+56JwCaLick0ClmMTw=
diff --git a/cmd/atlas/internal/cmdapi/migrate.go b/cmd/atlas/internal/cmdapi/migrate.go
index a519bc32..2d0b947c 100644
--- a/cmd/atlas/internal/cmdapi/migrate.go
+++ b/cmd/atlas/internal/cmdapi/migrate.go
@@ -24,6 +24,7 @@ import (
 	"ariga.io/atlas/cmd/atlas/internal/cmdlog"
 	"ariga.io/atlas/cmd/atlas/internal/lint"
 	cmdmigrate "ariga.io/atlas/cmd/atlas/internal/migrate"
+	"ariga.io/atlas/cmd/atlas/internal/migrate/ent"
 	"ariga.io/atlas/cmd/atlas/internal/migrate/ent/revision"
 	"ariga.io/atlas/sql/migrate"
 	"ariga.io/atlas/sql/schema"
@@ -735,14 +736,13 @@ func migrateSetCmd() *cobra.Command {
 	var (
 		flags migrateSetFlags
 		cmd   = &cobra.Command{
-			Use:   "set [flags] <version>",
+			Use:   "set [flags] [version]",
 			Short: "Set the current version of the migration history table.",
 			Long: `'atlas migrate set' edits the revision table to consider all migrations up to and including the given version
 to be applied. This command is usually used after manually making changes to the managed database.`,
 			Example: `  atlas migrate set 3 --url mysql://user:pass@localhost:3306/
-  atlas migrate set 4 --env local
+  atlas migrate set --env local
   atlas migrate set 1.2.4 --url mysql://user:pass@localhost:3306/my_db --revision-schema my_revisions`,
-			Args: cobra.ExactArgs(1),
 			PreRunE: func(cmd *cobra.Command, _ []string) error {
 				if err := migrateFlagsFromEnv(cmd); err != nil {
 					return err
@@ -765,29 +765,24 @@ to be applied. This command is usually used after manually making changes to the
 	return cmd
 }
 
-func migrateSetRun(cmd *cobra.Command, args []string, flags migrateSetFlags) error {
+func migrateSetRun(cmd *cobra.Command, args []string, flags migrateSetFlags) (rerr error) {
+	ctx := cmd.Context()
 	dir, err := dir(flags.dirURL, false)
 	if err != nil {
 		return err
 	}
-	avail, err := dir.Files()
+	files, err := dir.Files()
 	if err != nil {
 		return err
 	}
-	// Check if the target version does exist in the migration directory.
-	if idx := migrate.FilesLastIndex(avail, func(f migrate.File) bool {
-		return f.Version() == args[0]
-	}); idx == -1 {
-		return fmt.Errorf("migration with version %q not found", args[0])
-	}
-	client, err := sqlclient.Open(cmd.Context(), flags.url)
+	client, err := sqlclient.Open(ctx, flags.url)
 	if err != nil {
 		return err
 	}
 	defer client.Close()
 	// Acquire a lock.
 	if l, ok := client.Driver.(schema.Locker); ok {
-		unlock, err := l.Lock(cmd.Context(), applyLockValue, 0)
+		unlock, err := l.Lock(ctx, applyLockValue, 0)
 		if err != nil {
 			return fmt.Errorf("acquiring database lock: %w", err)
 		}
@@ -798,104 +793,132 @@ func migrateSetRun(cmd *cobra.Command, args []string, flags migrateSetFlags) err
 		return err
 	}
 	// Ensure revision table exists.
-	rrw, err := entRevisions(cmd.Context(), client, flags.revisionSchema)
+	rrw, err := entRevisions(ctx, client, flags.revisionSchema)
 	if err != nil {
 		return err
 	}
-	if err := rrw.Migrate(cmd.Context()); err != nil {
+	if err := rrw.Migrate(ctx); err != nil {
 		return err
 	}
 	// Wrap manipulation in a transaction.
-	tx, err := client.Tx(cmd.Context(), nil)
+	tx, err := client.Tx(ctx, nil)
 	if err != nil {
 		return err
 	}
-	rrw, err = entRevisions(cmd.Context(), tx.Client, flags.revisionSchema)
+	defer func() {
+		if rerr == nil {
+			rerr = tx.Commit()
+		} else if err2 := tx.Rollback(); err2 != nil {
+			rerr = fmt.Errorf("%v: %w", err2, err)
+		}
+	}()
+	rrw, err = entRevisions(ctx, tx.Client, flags.revisionSchema)
 	if err != nil {
 		return err
 	}
-	revs, err := rrw.ReadRevisions(cmd.Context())
+	revs, err := rrw.ReadRevisions(ctx)
 	if err != nil {
 		return err
 	}
-	if err := func() error {
-		for _, r := range revs {
-			// Check all existing revisions and ensure they precede the given version. If we encounter a partially
-			// applied revision, or one with errors, mark them "fixed".
-			switch {
-			// remove revision to keep linear history
-			case r.Version > args[0]:
-				if err := rrw.DeleteRevision(cmd.Context(), r.Version); err != nil {
-					return err
-				}
-			// keep, but if with error mark "fixed"
-			case r.Version == args[0] && (r.Error != "" || r.Total != r.Applied):
-				r.Type = migrate.RevisionTypeExecute | migrate.RevisionTypeResolved
-				if err := rrw.WriteRevision(cmd.Context(), r); err != nil {
-					return err
-				}
+	var version string
+	switch n := len(args); {
+	// Prevent the case where 'migrate set' is called without a version on
+	// a clean database. i.e., we allow only removing or syncing revisions.
+	case n == 0 && len(revs) > 0:
+		// Calling set without a version and an empty
+		// migration directory purges the revision table.
+		if len(files) > 0 {
+			version = files[len(files)-1].Version()
+		}
+	case n == 1:
+		// Check if the target version does exist in the migration directory.
+		if idx := migrate.FilesLastIndex(files, func(f migrate.File) bool {
+			return f.Version() == args[0]
+		}); idx == -1 {
+			return fmt.Errorf("migration with version %q not found", args[0])
+		}
+		version = args[0]
+	default:
+		return fmt.Errorf("accepts 1 arg(s), received %d", n)
+	}
+	log := &cmdlog.MigrateSet{}
+	for _, r := range revs {
+		// Check all existing revisions and ensure they precede the given version. If we encounter a partially
+		// applied revision, or one with errors, mark them "fixed".
+		switch {
+		// remove revision to keep linear history
+		case r.Version > version:
+			log.Removed(r)
+			if err := rrw.DeleteRevision(ctx, r.Version); err != nil {
+				return err
+			}
+		// keep, but if with error mark "fixed"
+		case r.Version == version && (r.Error != "" || r.Total != r.Applied):
+			log.Set(r)
+			r.Type = migrate.RevisionTypeExecute | migrate.RevisionTypeResolved
+			if err := rrw.WriteRevision(ctx, r); err != nil {
+				return err
 			}
 		}
-		revs, err = rrw.ReadRevisions(cmd.Context())
-		if err != nil {
-			return err
+	}
+	revs, err = rrw.ReadRevisions(ctx)
+	if err != nil {
+		return err
+	}
+	// If the target version succeeds the last revision, mark
+	// migrations applied, until we reach the target version.
+	var pending []migrate.File
+	switch {
+	case len(revs) == 0:
+		// Take every file until we reach target version.
+		for _, f := range files {
+			if f.Version() > version {
+				break
+			}
+			pending = append(pending, f)
 		}
-		// If the target version succeeds the last revision, mark
-		// migrations applied, until we reach the target version.
-		var pending []migrate.File
-		switch {
-		case len(revs) == 0:
-			// Take every file until we reach target version.
-			for _, f := range avail {
-				if f.Version() > args[0] {
-					break
-				}
+	case version > revs[len(revs)-1].Version:
+	loop:
+		// Take every file succeeding the last revision until we reach target version.
+		for _, f := range files {
+			switch {
+			case f.Version() <= revs[len(revs)-1].Version:
+				// Migration precedes last revision.
+			case f.Version() > version:
+				// Migration succeeds target revision.
+				break loop
+			default: // between last revision and target
 				pending = append(pending, f)
 			}
-		case args[0] > revs[len(revs)-1].Version:
-		loop:
-			// Take every file succeeding the last revision until we reach target version.
-			for _, f := range avail {
-				switch {
-				case f.Version() <= revs[len(revs)-1].Version:
-					// Migration precedes last revision.
-				case f.Version() > args[0]:
-					// Migration succeeds target revision.
-					break loop
-				default: // between last revision and target
-					pending = append(pending, f)
-				}
-			}
 		}
-		// Mark every pending file as applied.
-		sum, err := dir.Checksum()
+	}
+	// Mark every pending file as applied.
+	sum, err := dir.Checksum()
+	if err != nil {
+		return err
+	}
+	for _, f := range pending {
+		h, err := sum.SumByName(f.Name())
 		if err != nil {
 			return err
 		}
-		for _, f := range pending {
-			h, err := sum.SumByName(f.Name())
-			if err != nil {
-				return err
-			}
-			if err := rrw.WriteRevision(cmd.Context(), &migrate.Revision{
-				Version:         f.Version(),
-				Description:     f.Desc(),
-				Type:            migrate.RevisionTypeResolved,
-				ExecutedAt:      time.Now(),
-				Hash:            h,
-				OperatorVersion: operatorVersion(),
-			}); err != nil {
-				return err
-			}
+		rev := &migrate.Revision{
+			Version:         f.Version(),
+			Description:     f.Desc(),
+			Type:            migrate.RevisionTypeResolved,
+			ExecutedAt:      time.Now(),
+			Hash:            h,
+			OperatorVersion: operatorVersion(),
 		}
-		return nil
-	}(); err != nil {
-		if err2 := tx.Rollback(); err2 != nil {
-			err = fmt.Errorf("%v: %w", err2, err)
+		log.Set(rev)
+		if err := rrw.WriteRevision(ctx, rev); err != nil {
+			return err
 		}
+	}
+	if log.Current, err = rrw.CurrentRevision(ctx); err != nil && !ent.IsNotFound(err) {
 		return err
 	}
-	return tx.Commit()
+	return cmdlog.MigrateSetTemplate.Execute(cmd.OutOrStdout(), log)
 }
 
 type migrateStatusFlags struct {
diff --git a/cmd/atlas/internal/cmdlog/cmdlog.go b/cmd/atlas/internal/cmdlog/cmdlog.go
index 65b8888a..d3e76407 100644
--- a/cmd/atlas/internal/cmdlog/cmdlog.go
+++ b/cmd/atlas/internal/cmdlog/cmdlog.go
@@ -9,6 +9,7 @@ import (
 	"context"
 	"encoding/json"
 	"fmt"
+	"sort"
 	"strings"
 	"text/template"
 	"time"
@@ -198,6 +199,85 @@ func table(report *MigrateStatus) (string, error) {
 	return buf.String(), nil
 }
 
+// MigrateSetTemplate holds the default template of the 'migrate set' command.
+var MigrateSetTemplate = template.Must(template.New("set").
+	Funcs(ColorTemplateFuncs).Parse(`
+{{- if and (not .Current) .Revisions -}}
+All revisions deleted ({{ len .Revisions }} in total):
+{{ else if and .Current .Revisions -}}
+Current version is {{ cyan .Current.Version }} ({{ .Summary }}):
+{{ end }}
+{{- if .Revisions }}
+{{ range .ByVersion }}
+  {{- $text := .ColoredVersion }}{{ with .Description }}{{ $text = printf "%s (%s)" $text . }}{{ end }}
+  {{- printf "  %s\n" $text }}
+{{- end }}
+{{ end -}}
+`))
+
+type (
+	// MigrateSet contains a summary of the migrate set command.
+	MigrateSet struct {
+		// Revisions that were added, removed or updated.
+		Revisions []RevisionOp `json:"Revisions,omitempty"`
+		// Current version in the revisions table.
+		Current *migrate.Revision `json:"Latest,omitempty"`
+	}
+	// RevisionOp represents an operation done on a revision.
+	RevisionOp struct {
+		*migrate.Revision
+		Op string `json:"Op,omitempty"`
+	}
+)
+
+// ByVersion returns all revisions sorted by version.
+func (r *MigrateSet) ByVersion() []RevisionOp {
+	sort.Slice(r.Revisions, func(i, j int) bool {
+		return r.Revisions[i].Version < r.Revisions[j].Version
+	})
+	return r.Revisions
+}
+
+// Set records revision that was added.
+func (r *MigrateSet) Set(rev *migrate.Revision) {
+	r.Revisions = append(r.Revisions, RevisionOp{Revision: rev, Op: "set"})
+}
+
+// Removed records revision that was added.
+func (r *MigrateSet) Removed(rev *migrate.Revision) {
+	r.Revisions = append(r.Revisions, RevisionOp{Revision: rev, Op: "remove"})
+}
+
+// Summary returns a summary of the set operation.
+func (r *MigrateSet) Summary() string {
+	var s, d int
+	for i := range r.Revisions {
+		switch r.Revisions[i].Op {
+		case "set":
+			s++
+		default:
+			d++
+		}
+	}
+	var sum []string
+	if s > 0 {
+		sum = append(sum, fmt.Sprintf("%d set", s))
+	}
+	if d > 0 {
+		sum = append(sum, fmt.Sprintf("%d removed", d))
+	}
+	return strings.Join(sum, ", ")
+}
+
+// ColoredVersion returns the version of the revision with a color.
+func (r *RevisionOp) ColoredVersion() string {
+	c := color.HiGreenString("+")
+	if r.Op != "set" {
+		c = color.HiRedString("-")
+	}
+	return c + " " + r.Version
+}
+
 var (
 	// ApplyTemplateFuncs are global functions available in apply report templates.
 	ApplyTemplateFuncs = merge(ColorTemplateFuncs, template.FuncMap{
diff --git a/cmd/atlas/internal/migrate/migrate.go b/cmd/atlas/internal/migrate/migrate.go
index 82e18a3b..e4c4d335 100644
--- a/cmd/atlas/internal/migrate/migrate.go
+++ b/cmd/atlas/internal/migrate/migrate.go
@@ -106,6 +106,15 @@ func (r *EntRevisions) ReadRevisions(ctx context.Context) ([]*migrate.Revision,
 	return ret, nil
 }
 
+// CurrentRevision returns the current (latest) revision in the revisions table.
+func (r *EntRevisions) CurrentRevision(ctx context.Context) (*migrate.Revision, error) {
+	rev, err := r.ec.Revision.Query().Order(ent.Desc(revision.FieldID)).First(ctx)
+	if err != nil {
+		return nil, err
+	}
+	return rev.AtlasRevision(), nil
+}
+
 // WriteRevision writes a revision to the revisions table.
 func (r *EntRevisions) WriteRevision(ctx context.Context, rev *migrate.Revision) error {
 	return r.ec.Revision.Create().
diff --git a/doc/md/declarative/apply.mdx b/doc/md/declarative/apply.mdx
index cf6ba015..4be9f6e1 100644
--- a/doc/md/declarative/apply.mdx
+++ b/doc/md/declarative/apply.mdx
@@ -169,6 +169,12 @@ atlas schema apply \
 </TabItem>
 </Tabs>
 
+:::info The role of the [Dev Database](/concepts/dev-database)
+Atlas loads the **desired state** by executing the SQL files onto the provided [dev database](/concepts/dev-database),
+compares it against the database **current state** by inspecting its schema and writes a migration plan for moving
+from the current state to the desired state.
+:::
+
 ### Migration directory
 
 The following example demonstrates how to use the migration directory as the desired state and update the database
@@ -226,52 +232,6 @@ atlas schema apply \
 </TabItem>
 </Tabs>
 
-### Down migrations
-
-Migrations that "roll back" or reverse changes made to the database schema are called "down migrations". These are often
-used during local development to undo the changes made by corresponding "up migrations". Atlas follows a linear
-migration history model, in which all migration files are "roll-forward". However, it is still possible to clean or
-revert schema changes made by specific migration files using the `schema clean` or `schema apply` commands. For example:
-
-1\. If a migration file(s) was already deleted, you can use the [command above](#migration-directory) to migrate the
-database state to the state defined by the migration directory. For example:
-
-```shell
-atlas schema apply \
-  --url "mysql://root:pass@localhost:3306/example" \
-  --to "file://migrations" \
-  --dev-url "docker://mysql/8/example"
-```
-
-2\. If the file is still present, the `version` parameter can be used to specify that the database should be migrated
-to the state defined in a specific version of the migration directory. For example:
-
-```shell
-atlas schema apply \
-  --url "mysql://root:pass@localhost:3306/example" \
-  --to "file://migrations?version=20220925094437" \
-  --dev-url "docker://mysql/8/example"
-```
-
-```shell title="tree ." {6}
-.
-└── migrations
-    ├── 20220925092817_initial.sql
-    ├── 20220925094021_second.sql
-    ├── 20220925094437_third.sql
-    ├── 20220925094438_fourth.sql # changes made by this file are reverted.
-    └── atlas.sum
-```
-
-3\. In order to clean the entire schema, use the `schema clean` command, and then use
-[`atlas migrate apply`](../versioned/apply) to replay the migration files. For example:
-
-```shell
-atlas schema clean -u "mysql://root:pass@:3306/example"
-atlas migrate apply -u "mysql://root:pass@:3306/example"
-```
-
-
 ### Multi-Tenant environments
 
 The Atlas configuration language provides built-in support for executing declarative workflows in multi-tenant
diff --git a/doc/md/guides/guides.md b/doc/md/guides/guides.md
index 49c7458d..0af6cf45 100644
--- a/doc/md/guides/guides.md
+++ b/doc/md/guides/guides.md
@@ -26,6 +26,8 @@ Welcome to the Atlas Guides.
 
 [Descending Indexes](mysql/descending-indexes.md)
 
+[Functional Indexes](mysql/functional-indexes.md)
+
 [Generated Columns](mysql/generated-columns.md)
 
 [Managing MySQL schemas on RDS with Terraform](mysql/terraform.md)
diff --git a/doc/md/guides/mysql/functional-indexes.md b/doc/md/guides/mysql/functional-indexes.md
new file mode 100644
index 00000000..6d734a76
--- /dev/null
+++ b/doc/md/guides/mysql/functional-indexes.md
@@ -0,0 +1,410 @@
+---
+id: functional-indexes
+title: Functional Indexes in MySQL
+slug: /guides/mysql/functional-indexes
+---
+
+### What are functional key parts?​
+
+In contrast to a "normal" index which indexes column values, functional key parts can index expression values. Hence, functional key parts enable indexing values which are not stored directly in the table itself. MySQL 8.0.13 and higher have added support for functional key parts.
+
+### When are functional indexes helpful?
+
+In cases where MySQL users may want to optimize their queries containing expressions, indexing column values will not achieve the desired result. In such cases, users should create a functional index for the expressions in their query in order to optimize the performance of the query.
+
+### Syntax​
+
+Here is how you can define functional indexes in a table:
+
+```sql
+CREATE TABLE table_name (column_1 INT, column_2 INT, INDEX functional_index ((ABS(column_1))));
+```
+```sql
+CREATE INDEX functional_index ON table_name ((column_1 + column_2));
+```
+```sql
+CREATE INDEX functional_index ON table_name ((column_1 + column_2), (column_1 - column_2), column_1);
+```
+```sql
+ALTER TABLE table_name ADD INDEX ((column_1 * 90) DESC);
+```
+
+:::info
+Expressions must be enclosed within parentheses in order to create a functional index. If you do not enclose expressions within parentheses in the index definition, you will get the following error:
+
+`ERROR 1064 (42000): You have an error in your SQL syntax`
+:::
+
+### Example​
+
+Let’s create a table containing a list of students and the marks they received in each subject with the following command:
+
+```sql
+CREATE TABLE `scorecard` (
+  `id` mediumint(8) unsigned NOT NULL auto_increment,
+  `name` varchar(255),
+  `science` mediumint,
+  `mathematics` mediumint,
+  `language` mediumint,
+  `social_science` mediumint,
+  `arts` mediumint,
+  PRIMARY KEY (`id`)
+) AUTO_INCREMENT=1;
+```
+
+Here is what a portion of the table might look like after inserting values:
+
+```sql
+SELECT * FROM scorecard
+```
+```console title="Output"
++----+------------------+---------+-------------+----------+----------------+------+
+| id | name             | science | mathematics | language | social_science | arts |
++----+------------------+---------+-------------+----------+----------------+------+
+|  1 | Abel Warren      |      73 |          73 |       73 |             73 |   73 |
+|  2 | Erick Valentine  |      90 |           7 |       91 |              8 |   57 |
+|  3 | Janice Payne     |      49 |           6 |       91 |             48 |   77 |
+|  4 | Gretchen Mason   |      98 |          76 |       67 |             46 |   11 |
+|  5 | Lawanda Noble    |      85 |          22 |        7 |             44 |   33 |
+|  6 | Robbie Baird     |      98 |          66 |        2 |             69 |   67 |
+|  7 | Carla Compton    |      22 |          80 |       69 |             27 |   54 |
+|  8 | Heath Stafford   |       7 |           2 |       80 |             75 |   89 |
+|  9 | Kendra Stevenson |      74 |          81 |       15 |             21 |    1 |
+| 10 | Brandie Chase    |      94 |          94 |       92 |             92 |   93 |
+.
+.
+| 1000000 | James Strong     |      47 |          78 |       36 |             67 |   73 |
+```
+
+We do not have any indexes other than the primary index on the `id` column. Now, suppose we want information about the top ten students who scored the best average in math and science combined. Let's query that data with the following command:
+
+```sql
+SELECT
+    id, name, ((science + mathematics)/2) as average_score
+FROM
+    scorecard
+ORDER BY
+    average_score DESC
+LIMIT 10
+```
+
+```console title="Output"
++-------+------------------+-------------------+
+| id    | name             | average_score     |
++-------+------------------+-------------------+
+| 31593 | Rusty Mercer     | 99.0000           |
+| 24451 | Sara Fowler      | 99.0000           |
+|  8190 | Cecil Glass      | 99.0000           |
+| 47193 | Nelson Horn      | 99.0000           |
+|  7032 | Calvin Freeman   | 99.0000           |
+| 99198 | Mathew Rowland   | 99.0000           |
+| 50054 | Josephine Forbes | 99.0000           |
+| 52280 | Krystal Yoder    | 99.0000           |
+| 12639 | Maribel Carroll  | 99.0000           |
+| 53245 | Ramona Fischer   | 99.0000           |
++-------+------------------+-------------------+
+10 rows in set (0.47 sec)
+```
+
+Now, let's see how much the query cost with the following command:
+
+```sql
+EXPLAIN FORMAT=JSON
+SELECT
+    id, name, ((science + mathematics)/2) as average_score
+FROM
+    scorecard
+ORDER BY
+    average_score DESC
+LIMIT 10;
+```
+
+```console title="Output"
+| {
+  "query_block": {
+    "select_id": 1,
+    "cost_info": {
+      "query_cost": "100567.25"
+    },
+    "ordering_operation": {
+      "using_filesort": true,
+      "table": {
+        "table_name": "scorecard",
+        "access_type": "ALL",
+        "rows_examined_per_scan": 997100,
+        "rows_produced_per_join": 997100,
+        "filtered": "100.00",
+        "cost_info": {
+          "read_cost": "857.25",
+          "eval_cost": "99710.00",
+          "prefix_cost": "100567.25",
+          "data_read_per_join": "996M"
+        },
+        "used_columns": [
+          "id",
+          "name",
+          "science",
+          "mathematics"
+        ]
+      }
+    }
+  }
+} |
+```
+
+The cost is huge (100567.25) because MySQL uses the filesort operation when it is unable to utilize an index. As we are making use of columns `science` and `mathematics`, let’s try to optimize the query performance by indexing these columns with the following command:
+
+```sql
+CREATE INDEX
+    normal_index
+ON
+    scorecard (science, mathematics);
+```
+
+```console title="Output"
+Query OK, 0 rows affected (1.40 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+```
+
+Awesome, our index is now created! Let's check again how much our previous query cost, with the same command:
+
+```sql
+EXPLAIN ANALYZE
+SELECT
+    id, name, ((science + mathematics)/2) as average_score
+FROM
+    scorecard
+ORDER BY
+    average_score DESC
+LIMIT 10;
+```
+
+```console title="Output"
+| -> Limit: 10 row(s)  (cost=100448.95 rows=10) (actual time=552.143..552.145 rows=10 loops=1)
+    -> Sort: average_score DESC, limit input to 10 row(s) per chunk  (cost=100448.95 rows=995917) (actual time=552.142..552.143 rows=10 loops=1)
+        -> Table scan on scorecard  (cost=100448.95 rows=995917) (actual time=0.076..263.811 rows=1000000 loops=1)
+ |
+```
+
+:::info
+You can learn more about EXPLAIN EXTRA in the official documentation [here](https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information).
+:::
+
+The cost is still the same, our query hasn’t used the index we created which can be seen in the output of the following command (key = NULL):
+
+```sql
+EXPLAIN
+SELECT
+    id, name, ((science + mathematics)/2) as average_score
+FROM
+    scorecard
+ORDER BY
+    average_score DESC
+LIMIT 10\G;
+```
+
+```console title="Output"
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: scorecard
+   partitions: NULL
+         type: ALL
+possible_keys: NULL
+          key: NULL
+      key_len: NULL
+          ref: NULL
+         rows: 997269
+     filtered: 100.00
+        Extra: Using filesort
+1 row in set, 1 warning (0.00 sec)
+```
+
+:::info
+A filesort operation uses temporary disk files as necessary if the result set is too large to fit in memory. To learn more about how filesort is used to satisfy the ORDER BY clause in MySQL,  click [here](https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html#order-by-filesort).
+:::
+
+This is where having knowledge about functional indexes becomes essential. Now, let's try to optimize the query by creating a functional index with the expression `((science + mathematics)/2)` from our query, with the following command:
+
+```sql
+CREATE INDEX
+    functional_idx
+ON
+    scorecard((science + mathamatics)/2);
+```
+
+```console title="Output"
+ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/2)' at line 4
+```
+
+Oops, that didn’t work! As we mentioned in the [syntax](#syntax) section above, expressions must be enclosed within parentheses in order to create a functional index. Let’s try this again with the correct syntax:
+
+```sql
+CREATE INDEX
+    functional_idx
+ON
+    scorecard(((science + mathamatics)/2));
+```
+
+```console title="Output"
+Query OK, 0 rows affected (2.13 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+```
+
+Let's check again how much our previous query cost, with the same command:
+
+```sql
+EXPLAIN
+SELECT
+    id, name, ((science + mathematics)/2) as average_score
+FROM
+    scorecard
+ORDER BY
+    average_score DESC
+LIMIT 10;
+```
+
+```console title="Output"
+| -> Limit: 10 row(s)  (cost=0.01 rows=10) (actual time=0.149..0.160 rows=10 loops=1)
+    -> Index scan on scorecard using functional_idx (reverse)  (cost=0.01 rows=10) (actual time=0.148..0.157 rows=10 loops=1)
+|
+```
+
+The cost has reduced down to 0.01! The index scan has used the index "functional_idx" in order to execute the query. This is a significant improvement in performance compared to the scenarios above with an index on a column (unused) or no index at all (both of which cost 100000+ units).
+
+### Limitations
+1. Functional indexes cannot be defined as primary keys.
+2. Any conditions, functions or expressions in the query other than the ones defined in the functional index will not make use of the functional index. Thus, one needs to create a functional index with the exact conditions, functions or expressions which are used by the query.
+
+## Managing Functional Indexes is easy with Atlas​
+Atlas is an open-source project which allows us to manage our database using a simple and easy-to-understand declarative syntax.
+
+:::info
+If you are just getting started, install the latest version of Atlas using the guide to [setting up Atlas](https://atlasgo.io/cli/getting-started/setting-up).
+:::
+
+We will first use the `atlas schema inspect` command to get an [HCL](https://atlasgo.io/guides/ddl#hcl) representation of the table we created earlier (without any indexes other than primary index on id column) by using the Atlas CLI:
+
+```console
+atlas schema inspect -u "mysql://root:password@localhost:3306/scorecard" > schema.hcl
+```
+
+```hcl title="schema.hcl"
+table "scorecard" {
+  schema = schema.scorecard
+  column "id" {
+    type           = int
+    auto_increment = true
+  }
+  column "name" {
+    type = varchar(255)
+  }
+  column "science" {
+    type = mediumint
+  }
+  column "mathematics" {
+    type = mediumint
+  }
+  column "language" {
+    type = mediumint
+  }
+  column "social_science" {
+    type = mediumint
+  }
+  column "arts" {
+    type = mediumint
+  }
+  primary_key {
+    columns = [column.id]
+  }
+}
+schema "scorecard" {
+  charset = "utf8mb4"
+  collate = "utf8mb4_0900_ai_ci"
+}
+```
+
+Now, let's add the following index definition to the file:
+
+```hcl
+  index "sci_math_avg_idx" {
+    on {
+      expr = "((`science` + `mathematics`) / 2)"
+    }
+  }
+```
+
+Save and apply the schema changes on the database by using the apply command:
+
+```console
+atlas schema apply -u "mysql://root:password@localhost:3306/scorecard" -f schema.hcl --dev-url docker://mysql/8/scorecard
+```
+
+:::info
+If you get `Error: pulling image: exit status 1` error, ensure that Docker Desktop is up and running.
+:::
+
+Atlas generates the necessary SQL statements to add the new index to the database schema.
+Press `Enter` key while the Apply option is highlighted to apply the changes:
+
+```console
+-- Planned Changes:
+-- Modify "scorecard" table
+ALTER TABLE `scorecard`.`scorecard` ADD INDEX `sci_math_avg_idx` (((`science` + `mathematics`) / 2))
+Use the arrow keys to navigate: ↓ ↑ → ← 
+? Are you sure?: 
+  ▸ Apply
+    Abort
+```
+
+To verify that our new index was created, open the database command line tool from the previous step and run:
+
+```sql
+SHOW INDEXES FROM scorecard;
+```
+
+```console title="Output"
+*************************** 1. row ***************************
+        Table: scorecard
+   Non_unique: 0
+     Key_name: PRIMARY
+ Seq_in_index: 1
+  Column_name: id
+    Collation: A
+  Cardinality: 997438
+     Sub_part: NULL
+       Packed: NULL
+         Null: 
+   Index_type: BTREE
+      Comment: 
+Index_comment: 
+      Visible: YES
+   Expression: NULL
+*************************** 2. row ***************************
+        Table: scorecard
+   Non_unique: 1
+     Key_name: sci_math_avg_idx
+ Seq_in_index: 1
+  Column_name: NULL
+    Collation: A
+  Cardinality: 190
+     Sub_part: NULL
+       Packed: NULL
+         Null: YES
+   Index_type: BTREE
+      Comment: 
+Index_comment: 
+      Visible: YES
+   Expression: ((`science` + `mathematics`) / 2)
+2 rows in set (0.00 sec)
+```
+
+Amazing! Our new index sci_math_avg_idx with the expression “(`science` + `mathematics`) / 2” is now created!
+
+## Wrapping up​
+In this guide, we demonstrated how using functional indexes with an appropriate expression becomes a very crucial skill in optimizing query performance with combinations of certain expressions, functions and/or conditions.
+
+# Need More Help?​​
+[Join the Ariga Discord Server](https://discord.com/invite/zZ6sWVg6NT) for early access to features and the ability to provide exclusive feedback that improves your Database Management Tooling.
+
+[Sign up](https://www.getrevue.co/profile/ariga) to our newsletter to stay up to date about [Atlas](https://atlasgo.io/), our OSS database schema management tool, and our cloud platform [Ariga Cloud](https://ariga.cloud/).
diff --git a/doc/md/reference.md b/doc/md/reference.md
index 55ccbded..1f477452 100644
--- a/doc/md/reference.md
+++ b/doc/md/reference.md
@@ -246,7 +246,7 @@ Set the current version of the migration history table.
 
 #### Usage
 ```
-atlas migrate set [flags] <version>
+atlas migrate set [flags] [version]
 ```
 
 #### Details
@@ -257,7 +257,7 @@ to be applied. This command is usually used after manually making changes to the
 
 ```
   atlas migrate set 3 --url mysql://user:pass@localhost:3306/
-  atlas migrate set 4 --env local
+  atlas migrate set --env local
   atlas migrate set 1.2.4 --url mysql://user:pass@localhost:3306/my_db --revision-schema my_revisions
 ```
 #### Flags
diff --git a/doc/md/versioned/apply.mdx b/doc/md/versioned/apply.mdx
index 342e159f..1114ac80 100644
--- a/doc/md/versioned/apply.mdx
+++ b/doc/md/versioned/apply.mdx
@@ -110,6 +110,146 @@ If you want to check what exactly Atlas would do when attempting a migration exe
 flag:
 * `--dry-run` to not execute any SQL but print it on the screen.
 
+### Down migrations
+
+Migrations that "roll back" or reverse changes made to the database schema are called "down migrations". These are often
+used during local development to undo the changes made by corresponding "up migrations". Atlas follows a linear
+migration history model, in which all migration files are "roll-forward". However, it is still possible to clean or
+revert schema changes made by specific migration files using the `schema clean` or `schema apply` commands. For example:
+
+#### Reverting deleted migration files
+
+1\. To undo the changes made by migration files that have already been deleted, you can use the [`schema apply`](../declarative/apply#migration-directory)
+command to roll back the database state to the state defined by the migration directory:
+
+```shell
+atlas schema apply \
+  --url "mysql://root:pass@localhost:3306/example" \
+  --to "file://migrations" \
+  --dev-url "docker://mysql/8/example" \
+  --exclude "atlas_schema_revisions"
+```
+
+2\. Remove the files/versions that were rolled back from the revision table:
+```shell
+atlas migrate set \
+  --url "mysql://root:pass@localhost:3306/example" \
+  --dir "file://migrations"
+```
+
+<details>
+  <summary>Example output</summary>
+  <div>
+
+  ```text
+  Current version is 20221207170547 (2 removed):
+
+    - 20221225113035 (add_users)
+    - 20221225113406 (add_blog_posts)
+
+  ```
+
+  </div>
+</details>
+
+3\. Verify the migration status by using the `atlas migrate status` command:
+```shell
+atlas migrate status \
+  --url "mysql://root:pass@localhost:3306/example" \
+  --dir "file://migrations"
+```
+
+<details>
+  <summary>Example output</summary>
+  <div>
+
+  ```text
+  Migration Status: OK
+    -- Current Version: 20221207170547
+    -- Next Version:    Already at latest version
+    -- Executed Files:  25
+    -- Pending Files:   0
+  ```
+
+  </div>
+</details>
+
+#### Reverting applied migration files
+
+1\. To undo the changes made by migration files that are still present in the migration directory, you can use the
+`version` parameter to specify that the database should be rolled back to the state defined in a specific version of
+the migration directory. For example:
+
+```shell
+atlas schema apply \
+  --url "mysql://root:pass@localhost:3306/example" \
+  --to "file://migrations?version=20220925094437" \
+  --dev-url "docker://mysql/8/example" \
+  --exclude "atlas_schema_revisions"
+```
+```shell title="tree ." {6}
+.
+└── migrations
+    ├── 20220925092817_initial.sql
+    ├── 20220925094021_second.sql
+    ├── 20220925094437_third.sql
+    ├── 20220925094438_fourth.sql # changes made by this file are reverted.
+    └── atlas.sum
+```
+
+2\. To remove the `20220925094438` version from the revision table, set the current version to the preceding one `20220925094437`:
+```shell
+atlas migrate set 20220925094437 \
+  --url "mysql://root:pass@localhost:3306/example" \
+  --dir "file://migrations"
+```
+
+<details>
+  <summary>Example output</summary>
+  <div>
+
+  ```text
+  Current version is 20220925094437 (1 removed):
+
+    - 20220925094438 (fourth)
+
+  ```
+
+  </div>
+</details>
+
+3\. Verify the migration status by using the `atlas migrate status` command:
+```shell
+atlas migrate status \
+  --url "mysql://root:pass@localhost:3306/example" \
+  --dir "file://migrations"
+```
+
+<details>
+  <summary>Example output</summary>
+  <div>
+
+  ```text
+  Migration Status: PENDING
+    -- Current Version: 20220925094437
+    -- Next Version:    20220925094438
+    -- Executed Files:  3
+    -- Pending Files:   1
+  ```
+
+  </div>
+</details>
+
+#### Reverting all schema changes
+
+3\. In order to clean the entire schema, use the [`schema clean`](../cli-reference#atlas-schema-clean) command, and then use
+[`migrate apply`](#flags) to replay the migration files. For example:
+
+```shell
+atlas schema clean -u "mysql://root:pass@:3306/example"
+atlas migrate apply -u "mysql://root:pass@:3306/example"
+```
+
 ### Migration status
 
 In addition to the `--dry-run` flag Atlas also provides the `atlas migrate status` command, that provides in-depth
diff --git a/doc/website/blog/2022-01-19-atlas-v030.md b/doc/website/blog/2022-01-19-atlas-v030.md
index 9147b525..8be3c993 100644
--- a/doc/website/blog/2022-01-19-atlas-v030.md
+++ b/doc/website/blog/2022-01-19-atlas-v030.md
@@ -154,6 +154,6 @@ that are supported in it. There's much more inside, and we invite you to [instal
 and give it a try.
 
 ### What next?
-* Follow the [Getting Started](https://atlasgo.io/cli/getting-started/setting-up) guide..
+* Follow the [Getting Started](https://atlasgo.io/cli/getting-started/setting-up) guide.
 * Join our [Discord Server](https://discord.gg/zZ6sWVg6NT).
 * Follow us [on Twitter](https://twitter.com/ariga_io).
diff --git a/doc/website/blog/2023-01-05-atlas-v090.mdx b/doc/website/blog/2023-01-05-atlas-v090.mdx
new file mode 100644
index 00000000..85f0a906
--- /dev/null
+++ b/doc/website/blog/2023-01-05-atlas-v090.mdx
@@ -0,0 +1,250 @@
+---
+title: "Announcing Atlas v0.9.0: SQL as a First-Class Citizen"
+authors: a8m
+tags: [migration, sql, erd]
+---
+
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+For a long time, one of the most common feature requests we've been getting from our users is the ability to
+manage their desired "schema state" using SQL. This is understandable, using Atlas DDL (HCL) can feel unfamiliar to
+some users, especially those who have never worked with Terraform before. For this reason, we're excited to announce the
+release of Atlas v0.9.0, which now fully supports SQL.
+
+## Schema as Code (SaC)
+
+Atlas applies the common IaC concept of declarative resource management to database schemas. With Atlas, users do not
+need to plan schema changes themselves. Instead of figuring out the correct SQL statements to update their database
+schemas, users provide to Atlas the schema definition that describe their ***desired state*** and Atlas generates a
+migration plan to move from the ***current state*** to the desired state defined by the schema.
+
+Starting from v0.9.0, users can use SQL schema files (or a directory) containing `CREATE` and `ALTER` statements
+to describe their desired state. To demonstrate this, let's use this schema example with a single `users` table:
+
+```sql title="schema.sql"
+-- create table "users
+CREATE TABLE users(
+	id int NOT NULL,
+	name varchar(100) NULL,
+	PRIMARY KEY(id)
+);
+```
+
+Given this schema file, Atlas offers two workflows to update databases:
+
+- [Declarative](/declarative/apply): Similar to Terraform, Atlas compares the current state of the database schema with
+  the desired state defined by the SQL schema, and generates a migration plan to reach that state.
+- [Versioned](/versioned/diff): Atlas compares the current state defined by the `migrations` directory to the
+  desired state defined by the SQL schema, and writes a new migration script to the directory to update the database
+  schema to the desired state.
+
+In this blog post, we'll focus on explaining how SQL schemas can be used with the declarative workflow. For the sake
+of simplicity, let's assume we have an empty database that we want to apply the schema above to:
+
+<Tabs
+defaultValue="mysql"
+values={[
+{label: 'MySQL', value: 'mysql'},
+{label: 'MariaDB', value: 'maria'},
+{label: 'PostgreSQL', value: 'postgres'},
+{label: 'SQLite', value: 'sqlite'},
+]}>
+<TabItem value="mysql">
+
+```shell
+atlas schema apply \
+  --url "mysql://root:pass@localhost:3306/example" \
+  --to "file://schema.sql" \
+  --dev-url "docker://mysql/8/example"
+```
+
+</TabItem>
+<TabItem value="maria">
+
+```shell
+atlas schema apply \
+  --url "maria://root:pass@:3306/example" \
+  --to "file://schema.sql" \
+  --dev-url "docker://maria/latest/example"
+```
+
+</TabItem>
+<TabItem value="postgres">
+
+```shell
+atlas schema apply \
+  --url "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable" \
+  --to "file://schema.sql" \
+  --dev-url "docker://postgres/15"
+```
+
+</TabItem>
+<TabItem value="sqlite">
+
+```shell
+atlas schema apply \
+  --url "sqlite://file.db" \
+  --to "file://schema.sql" \
+  --dev-url "sqlite://file?mode=memory"
+```
+
+</TabItem>
+</Tabs>
+
+:::info FLAGS
+- `--url` - the database URL to apply the schema to.
+- `--to` - URLs describe the desired state: SQL or HCL schema definition, or a database URL.
+- `--dev-url` - a URL to a [Dev Database](/concepts/dev-database) that will be used to compute the diff.
+:::
+
+Running the command above with the `--auto-approve` flag will apply the following changes:
+
+```sql
+-- Planned Changes:
+-- Create "users" table
+CREATE TABLE `users` (`id` int NOT NULL, `name` varchar NULL, PRIMARY KEY (`id`));
+```
+
+Hooray! We have successfully created the `users` table defined in our schema file. Let's inspect our database and ensure
+its schema was actually updated by the command above:
+
+
+<Tabs
+defaultValue="mysql"
+values={[
+{label: 'MySQL', value: 'mysql'},
+{label: 'MariaDB', value: 'maria'},
+{label: 'PostgreSQL', value: 'postgres'},
+{label: 'SQLite', value: 'sqlite'},
+]}>
+<TabItem value="mysql">
+
+```shell
+atlas schema inspect \
+  --url "mysql://root:pass@localhost:3306/example" \
+  --log "{{ sql . }}"
+```
+
+</TabItem>
+<TabItem value="maria">
+
+```shell
+atlas schema inspect \
+  --url "maria://root:pass@:3306/example" \
+  --log "{{ sql . }}"
+```
+
+</TabItem>
+<TabItem value="postgres">
+
+```shell
+atlas schema inspect \
+  --url "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable" \
+  --log "{{ sql . }}"
+```
+
+</TabItem>
+<TabItem value="sqlite">
+
+```shell
+atlas schema inspect \
+  --url "sqlite://file.db" \
+  --log "{{ sql . }}"
+```
+
+</TabItem>
+</Tabs>
+
+Excellent! As you can see, our database schema has been updated:
+
+```sql
+-- create "users" table
+CREATE TABLE `users` (`id` int NOT NULL, `name` varchar NULL, PRIMARY KEY (`id`));
+```
+
+Now let's make our schema more interesting by adding a column to the `users` table and creating a `blog_posts` table
+with a foreign key that references `users`:
+
+```sql title="schema.sql" {5,10-17}
+-- create table "users
+CREATE TABLE users(
+	id int NOT NULL,
+	name varchar(100) NULL,
+	email varchar(50) NULL,
+	PRIMARY KEY(id)
+);
+
+-- create table "blog_posts"
+CREATE TABLE blog_posts(
+	id int NOT NULL,
+	title varchar(100) NULL,
+	body text NULL,
+	author_id int NULL,
+	PRIMARY KEY(id),
+	CONSTRAINT author_fk FOREIGN KEY(author_id) REFERENCES users(id)
+);
+```
+
+Next, executing `atlas schema apply` again will update the database schema with the following changes:
+
+```sql title="atlas schema apply"
+-- Planned Changes:
+-- Add column "email" to table: "users"
+ALTER TABLE `users` ADD COLUMN `email` varchar NULL;
+-- Create "blog_posts" table
+CREATE TABLE `blog_posts` (`title` varchar NULL, `body` text NULL, `author_id` int NULL, `id` int NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `author_fk` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION);
+```
+
+Boom! Atlas automatically calculates the difference between the ***current state*** of our database and the ***desired state***
+defined by our schema file, and generates the necessary changes to migrate the database to the new state. We don't need to
+specify each individual migration – we simply tell Atlas what state we want the database to be in, and it handles the rest.
+
+To see a full description of this generated migration plan, check out this diagram example in [Atlas public playground](https://gh.ariga.cloud/explore/f95ffc45...20d5152c):
+
+#### Diff ERD
+
+[![](https://atlasgo.io/uploads/images/diff-erd.png)](https://gh.ariga.cloud/explore/f95ffc45...20d5152c)
+
+#### Diff SQL
+
+[![](https://atlasgo.io/uploads/images/diff-sql.png)](https://gh.ariga.cloud/explore/f95ffc45...20d5152c)
+
+## Atlas Playground
+
+As part of this version, we have released the [Atlas playground](https://gh.ariga.cloud/explore) where users can visualize their
+database schemas in an interactive way. Simply provide an SQL or HCL schema, or import one from an existing database,
+and in return get an ERD visualizing their entire data model.
+
+Users can also compare between two schemas with the *Schema Diff* button, and get the SQL statements necessary to
+migrate from one schema to the other - [give it try!](https://gh.ariga.cloud/explore/01d2aeb1)
+
+<p style={{textAlign: "center"}}><a href="https://gh.ariga.cloud/explore/01d2aeb1">
+<img src="https://atlasgo.io/uploads/images/diff-erd-short.gif" alt="Blog ERD"/>
+</a></p>
+
+A big thanks to [@solomonme](https://github.com/solomonme), [@ronenlu](https://github.com/ronenlu) and [@masseelch](https://github.com/masseelch)
+for contributing this feature to Atlas!
+
+## Schema Loaders
+
+What's next? In the near future, we plan to add an infrastructure for loading schemas from external sources. This will
+enable ORM maintainers to integrate with Atlas and provide their schema definitions as Atlas schemas. As as result, they
+can utilize the Atlas engine to diff schemas, plan and lint migrations, execute them on the databases, and more.
+
+The first ORM to integrate with Atlas will be [Ent](https://entgo.io/). Using this integration, Ent users will be able
+to generate Atlas schemas or migrations for their Ent projects with a single command:
+
+```shell {3}
+atlas migrate diff create_users \
+  --dir "file://migrations" \
+  --to "ent://path/to/schema" \
+  --dev-url "docker://<driver-name>"
+```
+
+Would you like to see other ORMs integrated with Atlas? Please, join our [Discord server](https://discord.gg/zZ6sWVg6NT)
+and let me know.
+
+## What next?
+
+Have questions or feedback? Feel free to reach out [on our Discord server](https://discord.gg/zZ6sWVg6NT).
diff --git a/doc/website/docusaurus.config.js b/doc/website/docusaurus.config.js
index 56a05104..fdea14e9 100644
--- a/doc/website/docusaurus.config.js
+++ b/doc/website/docusaurus.config.js
@@ -194,6 +194,10 @@ module.exports = {
             to: '/guides/ddl',
             from: '/knowledge/ddl',
           },
+          {
+            to: '/concepts/url',
+            from: '/url',
+          },
         ],
       },
     ],
diff --git a/doc/website/sidebars.js b/doc/website/sidebars.js
index 8a64b5de..90c41568 100644
--- a/doc/website/sidebars.js
+++ b/doc/website/sidebars.js
@@ -150,6 +150,11 @@ module.exports = {
                     id: 'guides/mysql/descending-indexes',
                     label: 'Descending Indexes'
                   },
+                  {
+                    type: 'doc',
+                    id: 'guides/mysql/functional-indexes',
+                    label: 'Functional Indexes'
+                  },
                 {
                   type: 'doc',
                   id: 'guides/mysql/generated-columns',
diff --git a/internal/integration/go.mod b/internal/integration/go.mod
index 7de39144..3a7e6bcc 100644
--- a/internal/integration/go.mod
+++ b/internal/integration/go.mod
@@ -7,7 +7,7 @@ replace ariga.io/atlas => ../../
 replace ariga.io/atlas/cmd/atlas => ../../cmd/atlas
 
 require (
-	ariga.io/atlas v0.8.4-0.20221128070800-1ce9c6ac2c02
+	ariga.io/atlas v0.9.0
 	entgo.io/ent v0.11.5-0.20221123165602-b6a475f066c4
 	github.com/go-sql-driver/mysql v1.6.0
 	github.com/hashicorp/hcl/v2 v2.13.0
@@ -20,7 +20,7 @@ require (
 )
 
 require (
-	ariga.io/atlas/cmd/atlas v0.8.4-0.20221129182314-0f380f502419 // indirect
+	ariga.io/atlas/cmd/atlas v0.8.4-0.20230106092735-c67ba2a50b37 // indirect
 	cloud.google.com/go v0.103.0 // indirect
 	cloud.google.com/go/compute v1.7.0 // indirect
 	cloud.google.com/go/iam v0.3.0 // indirect
diff --git a/sql/postgres/migrate.go b/sql/postgres/migrate.go
index 9c37f9af..071fc792 100644
--- a/sql/postgres/migrate.go
+++ b/sql/postgres/migrate.go
@@ -200,7 +200,21 @@ func (s *state) addTable(ctx context.Context, add *schema.AddTable) error {
 
 // dropTable builds and executes the query for dropping a table from a schema.
 func (s *state) dropTable(ctx context.Context, drop *schema.DropTable) error {
-	rs := &state{conn: s.conn, PlanOptions: s.PlanOptions}
+	cmd := &changeGroup{}
+	for _, e := range s.enumTypes(drop.T) {
+		if err := s.mayDropEnum(cmd, drop.T.Schema, e, drop.T); err != nil {
+			return err
+		}
+	}
+	rs := &state{
+		conn:        s.conn,
+		PlanOptions: s.PlanOptions,
+		altered:     s.altered,
+		// Enums that were dropped above, were
+		// also created in the reverse commands.
+		created: s.dropped,
+		dropped: s.created,
+	}
 	if err := rs.addTable(ctx, &schema.AddTable{T: drop.T}); err != nil {
 		return fmt.Errorf("calculate reverse for drop table %q: %w", drop.T.Name, err)
 	}
@@ -209,7 +223,7 @@ func (s *state) dropTable(ctx context.Context, drop *schema.DropTable) error {
 		b.P("IF EXISTS")
 	}
 	b.Table(drop.T)
-	s.append(&migrate.Change{
+	cmd.main = &migrate.Change{
 		Cmd:     b.String(),
 		Source:  drop,
 		Comment: fmt.Sprintf("drop %q table", drop.T.Name),
@@ -225,7 +239,8 @@ func (s *state) dropTable(ctx context.Context, drop *schema.DropTable) error {
 			}
 			return cmd
 		}(),
-	})
+	}
+	cmd.append(s)
 	return nil
 }
 
@@ -367,7 +382,7 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 		reverse    []schema.Change
 		reversible = true
 	)
-	build := func(alter *alterChange, changes []schema.Change) (string, error) {
+	build := func(alter *changeGroup, changes []schema.Change) (string, error) {
 		b := s.Build("ALTER TABLE").Table(t)
 		err := b.MapCommaErr(changes, func(i int, b *sqlx.Builder) error {
 			switch change := changes[i].(type) {
@@ -458,7 +473,7 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 		}
 		return b.String(), nil
 	}
-	cmd := &alterChange{}
+	cmd := &changeGroup{}
 	stmt, err := build(cmd, changes)
 	if err != nil {
 		return fmt.Errorf("alter table %q: %v", t.Name, err)
@@ -475,7 +490,7 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 		// Changes should be reverted in
 		// a reversed order they were created.
 		sqlx.ReverseChanges(reverse)
-		if cmd.main.Reverse, err = build(&alterChange{}, reverse); err != nil {
+		if cmd.main.Reverse, err = build(&changeGroup{}, reverse); err != nil {
 			return fmt.Errorf("reverse alter table %q: %v", t.Name, err)
 		}
 	}
@@ -483,20 +498,20 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 	return nil
 }
 
-// alterChange describes an alter table migrate.Change where its main command
+// changeGroup describes an alter table migrate.Change where its main command
 // can be supported by additional statements before and after it is executed.
-type alterChange struct {
+type changeGroup struct {
 	main          *migrate.Change
 	before, after []*migrate.Change
 }
 
-func (a *alterChange) append(s *state) {
+func (a *changeGroup) append(s *state) {
 	s.append(a.before...)
 	s.append(a.main)
 	s.append(a.after...)
 }
 
-func (s *state) alterColumn(b *sqlx.Builder, alter *alterChange, t *schema.Table, c *schema.ModifyColumn) error {
+func (s *state) alterColumn(b *sqlx.Builder, alter *changeGroup, t *schema.Table, c *schema.ModifyColumn) error {
 	for k := c.Change; !k.Is(schema.NoChange); {
 		b.P("ALTER COLUMN").Ident(c.To.Name)
 		switch {
@@ -552,7 +567,7 @@ func (s *state) alterColumn(b *sqlx.Builder, alter *alterChange, t *schema.Table
 
 // alterType appends the clause(s) to alter the column type and assuming the
 // "ALTER COLUMN <Name>" was called before by the alterColumn function.
-func (s *state) alterType(b *sqlx.Builder, alter *alterChange, t *schema.Table, c *schema.ModifyColumn) error {
+func (s *state) alterType(b *sqlx.Builder, alter *changeGroup, t *schema.Table, c *schema.ModifyColumn) error {
 	// Commands for creating and dropping serial sequences.
 	createDropSeq := func(st *SerialType) (string, string, string) {
 		seq := fmt.Sprintf(`%s%q`, s.schemaPrefix(t.Schema), st.sequence(t, c.To))
@@ -782,8 +797,9 @@ func (s *state) enumExists(ctx context.Context, ns *schema.Schema, e *schema.Enu
 	return rows.Next(), rows.Err()
 }
 
-// mayDropEnum drops dangling enum types form the schema.
-func (s *state) mayDropEnum(alter *alterChange, ns *schema.Schema, e *schema.EnumType) error {
+// mayDropEnum drops dangling enum types from the schema. An optional
+// "dropped" list can be provided to skip while searching for usage.
+func (s *state) mayDropEnum(cmd *changeGroup, ns *schema.Schema, e *schema.EnumType, dropped ...*schema.Table) error {
 	name := s.enumIdent(ns, e)
 	if _, ok := s.dropped[name]; ok {
 		return nil
@@ -795,6 +811,10 @@ func (s *state) mayDropEnum(alter *alterChange, ns *schema.Schema, e *schema.Enu
 	}
 	for i := range schemas {
 		for _, t := range schemas[i].Tables {
+			// Skip dropped tables.
+			if containsT(dropped, t) {
+				continue
+			}
 			for _, c := range t.Columns {
 				e1, ok := hasEnumType(c)
 				// Although we search in siblings schemas, use the
@@ -807,7 +827,7 @@ func (s *state) mayDropEnum(alter *alterChange, ns *schema.Schema, e *schema.Enu
 	}
 	s.dropped[name] = e
 	create, drop := s.createDropEnum(ns, e)
-	alter.after = append(alter.after, &migrate.Change{
+	cmd.after = append(cmd.after, &migrate.Change{
 		Cmd:     drop,
 		Reverse: create,
 		Comment: fmt.Sprintf("drop enum type %q", e.T),
@@ -1205,6 +1225,21 @@ func (s *state) enumSchema(ns *schema.Schema, e *schema.EnumType) (es string) {
 	return
 }
 
+// enumType returns all the enum types used by the given table.
+func (s *state) enumTypes(t *schema.Table) []*schema.EnumType {
+	var (
+		es   []*schema.EnumType
+		seen = make(map[string]bool)
+	)
+	for _, c := range t.Columns {
+		if e, ok := hasEnumType(c); ok && !seen[s.enumIdent(t.Schema, e)] {
+			seen[s.enumIdent(t.Schema, e)] = true
+			es = append(es, e)
+		}
+	}
+	return es
+}
+
 // schemaPrefix returns the schema prefix based on the planner config.
 func (s *state) schemaPrefix(ns *schema.Schema) string {
 	switch {
@@ -1243,3 +1278,12 @@ func hasEnumType(c *schema.Column) (*schema.EnumType, bool) {
 	}
 	return nil, false
 }
+
+func containsT(ts []*schema.Table, t *schema.Table) bool {
+	for _, t1 := range ts {
+		if t1.Schema.Name == t.Schema.Name && t1.Name == t.Name {
+			return true
+		}
+	}
+	return false
+}
diff --git a/sql/sqlclient/client.go b/sql/sqlclient/client.go
index 35cc0dda..8f677ceb 100644
--- a/sql/sqlclient/client.go
+++ b/sql/sqlclient/client.go
@@ -199,9 +199,12 @@ func OpenURL(ctx context.Context, u *url.URL, opts ...OpenOption) (*Client, erro
 			return nil, err
 		}
 	}
+	if u.Scheme == "" {
+		return nil, errors.New("sql/sqlclient: missing driver. See: https://atlasgo.io/url")
+	}
 	v, ok := drivers.Load(u.Scheme)
 	if !ok {
-		return nil, fmt.Errorf("sql/sqlclient: no opener was registered with name %q", u.Scheme)
+		return nil, fmt.Errorf("sql/sqlclient: unknown driver %q. See: https://atlasgo.io/url", u.Scheme)
 	}
 	drv := v.(*driver)
 	// If there is a schema given and the driver allows to change the schema for the url, do it.


Test patch:
diff --git a/cmd/atlas/internal/cmdapi/migrate_test.go b/cmd/atlas/internal/cmdapi/migrate_test.go
index 8e75bc59..fd78110c 100644
--- a/cmd/atlas/internal/cmdapi/migrate_test.go
+++ b/cmd/atlas/internal/cmdapi/migrate_test.go
@@ -792,6 +792,89 @@ func TestMigrate_StatusJSON(t *testing.T) {
 	require.Equal(t, `"sqlite3"`, s)
 }
 
+func TestMigrate_Set(t *testing.T) {
+	u := fmt.Sprintf("sqlite://file:%s?_fk=1", filepath.Join(t.TempDir(), "test.db"))
+	_, err := runCmd(
+		migrateApplyCmd(),
+		"--dir", "file://testdata/sqlite",
+		"--url", u,
+	)
+	require.NoError(t, err)
+
+	s, err := runCmd(
+		migrateSetCmd(),
+		"--dir", "file://testdata/sqlite",
+		"-u", u,
+		"20220318104614",
+	)
+	require.NoError(t, err)
+	require.Equal(t, `Current version is 20220318104614 (1 removed):
+
+  - 20220318104615 (second)
+
+`, s)
+	s, err = runCmd(
+		migrateSetCmd(),
+		"--dir", "file://testdata/sqlite",
+		"-u", u,
+		"20220318104615",
+	)
+	require.NoError(t, err)
+	require.Equal(t, `Current version is 20220318104615 (1 set):
+
+  + 20220318104615 (second)
+
+`, s)
+
+	s, err = runCmd(
+		migrateSetCmd(),
+		"--dir", "file://testdata/baseline1",
+		"-u", u,
+	)
+	require.NoError(t, err)
+	require.Equal(t, `Current version is 1 (1 set, 2 removed):
+
+  + 1 (baseline)
+  - 20220318104614 (initial)
+  - 20220318104615 (second)
+
+`, s)
+
+	s, err = runCmd(
+		migrateSetCmd(),
+		"--dir", filepath.Join("file://", t.TempDir()), // empty dir.
+		"-u", u,
+	)
+	require.NoError(t, err)
+	require.Equal(t, `All revisions deleted (1 in total):
+
+  - 1 (baseline)
+
+`, s)
+
+	// Empty database.
+	u = fmt.Sprintf("sqlite://file:%s?_fk=1", filepath.Join(t.TempDir(), "test.db"))
+	_, err = runCmd(
+		migrateSetCmd(),
+		"--dir", "file://testdata/sqlite",
+		"-u", u,
+	)
+	require.EqualError(t, err, "accepts 1 arg(s), received 0")
+
+	s, err = runCmd(
+		migrateSetCmd(),
+		"--dir", "file://testdata/sqlite",
+		"-u", u,
+		"20220318104614",
+	)
+	require.NoError(t, err)
+	require.Equal(t, `Current version is 20220318104614 (1 set):
+
+  + 20220318104614 (initial)
+
+`, s)
+}
+
 func TestMigrate_New(t *testing.T) {
 	var (
 		p = t.TempDir()
diff --git a/cmd/atlas/internal/cmdlog/cmdlog_test.go b/cmd/atlas/internal/cmdlog/cmdlog_test.go
index 233c6497..8180be06 100644
--- a/cmd/atlas/internal/cmdlog/cmdlog_test.go
+++ b/cmd/atlas/internal/cmdlog/cmdlog_test.go
@@ -14,6 +14,7 @@ import (
 	"text/template"
 
 	"ariga.io/atlas/cmd/atlas/internal/cmdlog"
+	"ariga.io/atlas/sql/migrate"
 	"ariga.io/atlas/sql/schema"
 	"ariga.io/atlas/sql/sqlclient"
 	_ "ariga.io/atlas/sql/sqlite"
@@ -130,3 +131,50 @@ func TestSchemaInspect_EncodeSQL(t *testing.T) {
 	require.NoError(t, tmpl.Execute(&b, &cmdlog.SchemaInspect{Error: errors.New("failure")}))
 	require.Equal(t, "failure", b.String())
 }
+
+func TestMigrateSet(t *testing.T) {
+	var (
+		b   bytes.Buffer
+		log = &cmdlog.MigrateSet{}
+	)
+	require.NoError(t, cmdlog.MigrateSetTemplate.Execute(&b, log))
+	require.Empty(t, b.String())
+
+	log.Current = &migrate.Revision{Version: "1"}
+	require.NoError(t, cmdlog.MigrateSetTemplate.Execute(&b, log))
+	require.Empty(t, b.String())
+
+	log.Current = &migrate.Revision{Version: "1"}
+	log.Removed(&migrate.Revision{Version: "2"})
+	log.Removed(&migrate.Revision{Version: "3", Description: "desc"})
+	require.NoError(t, cmdlog.MigrateSetTemplate.Execute(&b, log))
+	require.Equal(t, `Current version is 1 (2 removed):
+
+  - 2
+  - 3 (desc)
+
+`, b.String())
+
+	b.Reset()
+	log.Set(&migrate.Revision{Version: "1.1", Description: "desc"})
+	require.NoError(t, cmdlog.MigrateSetTemplate.Execute(&b, log))
+	require.Equal(t, `Current version is 1 (1 set, 2 removed):
+
+  + 1.1 (desc)
+  - 2
+  - 3 (desc)
+
+`, b.String())
+
+	b.Reset()
+	log.Current, log.Revisions = nil, nil
+	log.Removed(&migrate.Revision{Version: "2"})
+	log.Removed(&migrate.Revision{Version: "3", Description: "desc"})
+	require.NoError(t, cmdlog.MigrateSetTemplate.Execute(&b, log))
+	require.Equal(t, `All revisions deleted (2 in total):
+
+  - 2
+  - 3 (desc)
+
+`, b.String())
+}
diff --git a/cmd/atlas/internal/migrate/migrate_test.go b/cmd/atlas/internal/migrate/migrate_test.go
index 99d6f115..9c2c8f57 100644
--- a/cmd/atlas/internal/migrate/migrate_test.go
+++ b/cmd/atlas/internal/migrate/migrate_test.go
@@ -7,8 +7,11 @@ package migrate
 import (
 	"context"
 	"testing"
+	"time"
 
+	"ariga.io/atlas/cmd/atlas/internal/migrate/ent"
 	"ariga.io/atlas/cmd/atlas/internal/migrate/ent/revision"
+	"ariga.io/atlas/sql/migrate"
 	"ariga.io/atlas/sql/sqlclient"
 
 	_ "github.com/mattn/go-sqlite3"
@@ -16,15 +19,46 @@ import (
 )
 
 func TestNewEntRevisions(t *testing.T) {
-	c, err := sqlclient.Open(context.Background(), "sqlite://?mode=memory")
+	ctx := context.Background()
+	c, err := sqlclient.Open(ctx, "sqlite://?mode=memory")
 	require.NoError(t, err)
-	r, err := NewEntRevisions(context.Background(), c)
+	r, err := NewEntRevisions(ctx, c)
 	require.NoError(t, err)
-	require.NoError(t, r.Migrate(context.Background()))
+	require.NoError(t, r.Migrate(ctx))
 
-	s, err := c.Driver.InspectSchema(context.Background(), "", nil)
+	s, err := c.Driver.InspectSchema(ctx, "", nil)
 	require.NoError(t, err)
 	require.Len(t, s.Tables, 1)
 	_, ok := s.Table(revision.Table)
 	require.True(t, ok)
+
+	cur, err := r.CurrentRevision(ctx)
+	require.True(t, ent.IsNotFound(err))
+	require.Nil(t, cur)
+
+	err = r.WriteRevision(ctx, &migrate.Revision{
+		Version:         "1",
+		Description:     "desc",
+		Type:            migrate.RevisionTypeResolved,
+		ExecutedAt:      time.Now(),
+		Hash:            "hash",
+		OperatorVersion: "0.1.0",
+	})
+	require.NoError(t, err)
+	cur, err = r.CurrentRevision(ctx)
+	require.NoError(t, err)
+	require.Equal(t, "1", cur.Version)
+
+	next := *cur
+	next.Version = "2"
+	require.NoError(t, r.WriteRevision(ctx, &next))
+	cur, err = r.CurrentRevision(ctx)
+	require.NoError(t, err)
+	require.Equal(t, "2", cur.Version)
+
+	revs, err := r.ReadRevisions(ctx)
+	require.NoError(t, err)
+	require.Len(t, revs, 2)
+	require.Equal(t, "1", revs[0].Version)
+	require.Equal(t, "2", revs[1].Version)
 }
diff --git a/internal/integration/testdata/sqlite/cli-migrate-set.txt b/internal/integration/testdata/sqlite/cli-migrate-set.txt
index 8992a278..9fd56bc8 100644
--- a/internal/integration/testdata/sqlite/cli-migrate-set.txt
+++ b/internal/integration/testdata/sqlite/cli-migrate-set.txt
@@ -1,15 +1,15 @@
-! atlas migrate set
-stderr 'Error: accepts 1 arg\(s\), received 0'
-
-! atlas migrate set foo bar
-stderr 'Error: accepts 1 arg\(s\), received 2'
-
 ! atlas migrate set 0
 stderr 'Error: checksum file not found'
 stdout 'You have a checksum error in your migration directory.'
 
 atlas migrate hash
 
+! atlas migrate set --url URL
+stderr 'Error: accepts 1 arg\(s\), received 0'
+
+! atlas migrate set --url URL foo bar
+stderr 'Error: accepts 1 arg\(s\), received 2'
+
 # Works on fresh database.
 atlas migrate set 1 --url URL
 atlas migrate apply 1 --url URL --dry-run
diff --git a/sql/postgres/migrate_test.go b/sql/postgres/migrate_test.go
index 0de9ddba..57d0a36c 100644
--- a/sql/postgres/migrate_test.go
+++ b/sql/postgres/migrate_test.go
@@ -668,6 +668,35 @@ func TestPlanChanges(t *testing.T) {
 				},
 			},
 		},
+		{
+			changes: []schema.Change{
+				&schema.DropTable{
+					T: schema.NewTable("users").
+						SetSchema(schema.New("public")).
+						AddColumns(
+							schema.NewEnumColumn("state", schema.EnumName("state"), schema.EnumValues("on", "off")),
+							schema.NewEnumColumn("status", schema.EnumName("status"), schema.EnumValues("on", "off")),
+						),
+				},
+			},
+			mock: func(m mock) {
+				m.ExpectQuery(sqltest.Escape("SELECT * FROM pg_type t JOIN pg_namespace n on t.typnamespace = n.oid WHERE t.typname = $1 AND t.typtype = 'e' AND n.nspname = $2 ")).
+					WithArgs("state", "public").
+					WillReturnRows(sqlmock.NewRows([]string{"name"}))
+				m.ExpectQuery(sqltest.Escape("SELECT * FROM pg_type t JOIN pg_namespace n on t.typnamespace = n.oid WHERE t.typname = $1 AND t.typtype = 'e' AND n.nspname = $2 ")).
+					WithArgs("status", "public").
+					WillReturnRows(sqlmock.NewRows([]string{"name"}))
+			},
+			wantPlan: &migrate.Plan{
+				Reversible:    true,
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{Cmd: `DROP TABLE "public"."users"`, Reverse: `CREATE TABLE "public"."users" ("state" "public"."state" NOT NULL, "status" "public"."status" NOT NULL)`},
+					{Cmd: `DROP TYPE "public"."state"`, Reverse: `CREATE TYPE "public"."state" AS ENUM ('on', 'off')`},
+					{Cmd: `DROP TYPE "public"."status"`, Reverse: `CREATE TYPE "public"."status" AS ENUM ('on', 'off')`},
+				},
+			},
+		},
 		{
 			changes: []schema.Change{
 				func() schema.Change {
diff --git a/sql/sqlclient/client_test.go b/sql/sqlclient/client_test.go
index d9c45192..e5409654 100644
--- a/sql/sqlclient/client_test.go
+++ b/sql/sqlclient/client_test.go
@@ -57,7 +57,16 @@ func TestRegisterOpen(t *testing.T) {
 	require.Equal(t, "schema", c.URL.Schema)
 
 	c1, err = sqlclient.Open(context.Background(), "postgres://:3306")
-	require.EqualError(t, err, `sql/sqlclient: no opener was registered with name "postgres"`)
+	require.EqualError(t, err, `sql/sqlclient: unknown driver "postgres". See: https://atlasgo.io/url`)
+}
+
+func TestOpen_Errors(t *testing.T) {
+	c, err := sqlclient.Open(context.Background(), "missing")
+	require.EqualError(t, err, `sql/sqlclient: missing driver. See: https://atlasgo.io/url`)
+	require.Nil(t, c)
+	c, err = sqlclient.Open(context.Background(), "unknown://")
+	require.EqualError(t, err, `sql/sqlclient: unknown driver "unknown". See: https://atlasgo.io/url`)
+	require.Nil(t, c)
 }
 
 func TestClient_AddClosers(t *testing.T) {