Repository: ariga/atlas
Pull request #560 (2022-02-10 09:24:38)

atlas/doc: add getting started with the UI


Patch:
diff --git a/cmd/action/apply.go b/cmd/action/apply.go
index f53a67f5..8e849b83 100644
--- a/cmd/action/apply.go
+++ b/cmd/action/apply.go
@@ -18,19 +18,19 @@ import (
 var (
 	// ApplyFlags are the flags used in Apply command.
 	ApplyFlags struct {
-		DSN    string
-		File   string
-		Web    bool
-		Addr   string
-		DryRun bool
-		Schema []string
+		DSN         string
+		File        string
+		Web         bool
+		Addr        string
+		DryRun      bool
+		Schema      []string
 		AutoApprove bool
 	}
 	// ApplyCmd represents the apply command.
 	ApplyCmd = &cobra.Command{
 		Use:   "apply",
 		Short: "Apply an atlas schema to a target database.",
-		Long: "`atlas schema apply`" + ` plans and executes a database migration to be bring a given database
+		Long: "`atlas schema apply`" + ` plans and executes a database migration to bring a given database
 to the state described in the Atlas schema file. Before running the migration, Atlas will print the migration
 plan and prompt the user for approval.
 
diff --git a/doc/md/cli/reference.md b/doc/md/cli/reference.md
index f8c50f77..282a6cbd 100644
--- a/doc/md/cli/reference.md
+++ b/doc/md/cli/reference.md
@@ -69,7 +69,7 @@ atlas schema apply [flags]
 ```
 
 #### Details
-`atlas schema apply` plans and executes a database migration to be bring a given database
+`atlas schema apply` plans and executes a database migration to bring a given database
 to the state described in the Atlas schema file. Before running the migration, Atlas will print the migration
 plan and prompt the user for approval.
 
diff --git a/doc/md/ddl/sql.md b/doc/md/ddl/sql.md
index b679ca15..628423f2 100644
--- a/doc/md/ddl/sql.md
+++ b/doc/md/ddl/sql.md
@@ -14,18 +14,23 @@ state of a SQL database.
 ### Schema
 
 ```hcl
-schema "default" {}
+# Empty schema.
+schema "public" {}
+
+# Schema with attributes.
+schema "main" {
+  charset   = "utf8mb4"
+  collation = "utf8mb4_0900_ai_ci"
+}
 ```
 
 ### Table
 
-A `table` describes a table in a SQL database. 
-
-#### Example
+A `table` describes a table in a SQL database.
 
 ```hcl
 table "users" {
-  schema = schema.default
+  schema = schema.public
   column "id" {
     type = int
   }
@@ -175,12 +180,18 @@ index "idx_name" {
     }
     unique = true
 }
+
+index "idx_name" {
+  type = GIN
+  columns = [column.data]
+}
 ```
 
 #### Properties
 
-| Name      | Kind      | Type                    | Description                                                  |
-|-----------|-----------|-------------------------|--------------------------------------------------------------|
-| unique    | attribute | boolean                 | Defines whether a uniqueness constraint is set on the index. |
-| columns   | attribute | reference (list)        | The columns that comprise the index.                         |
-| on        | attribute | schema.IndexPart (list) | The index parts that comprise the index.                     |
+| Name      | Kind      | Type                    | Description                                                    |
+|-----------|-----------|-------------------------|----------------------------------------------------------------|
+| unique    | attribute | boolean                 | Defines whether a uniqueness constraint is set on the index.   |
+| type      | attribute | IndexType (enum)        | Defines the index type. e.g. `HASH`, `GIN`, `FULLTEXT`.        |
+| columns   | attribute | reference (list)        | The columns that comprise the index.                           |
+| on        | resource  | schema.IndexPart (list) | The index parts that comprise the index.                       |
diff --git a/doc/md/getting-started/04-ui.md b/doc/md/getting-started/04-ui.md
new file mode 100644
index 00000000..f9c59d53
--- /dev/null
+++ b/doc/md/getting-started/04-ui.md
@@ -0,0 +1,111 @@
+---
+id: getting-started-management-ui
+title: Using the Management UI
+slug: /cli/getting-started/using-the-management-ui
+---
+
+### Using the Management UI
+Aside from being a command line tool, Atlas also has a user interface that allows us to visually manage our schemas. 
+
+After being introduced to the CLI’s capabilities, let’s see what we can do with the Management UI.
+
+###Inspecting
+We will start off by inspecting our database with the `-w` flag. To do so, run:
+
+`atlas schema inspect -d "mysql://root:pass@tcp(localhost:3306)/example" -w`
+
+Our browser will open automatically, and we should see a similar output in the CLI:
+```
+Atlas UI available at: http://127.0.0.1:5800/projects/25769803777/schemas/1 
+Press Ctrl+C to stop
+```
+
+When the UI opens, we are taken to the schema page. This screen displays a high-level view of our Atlas schema.
+
+![Atlas Schema Synced](https://atlasgo.io/uploads/images/docs/synced-atlas-schema.png)
+
+
+On the top-left (1) we can see a box with information about our schema. The "Activity & History" panel (2) holds an audit history for all changes made to the schema. Each log is clickable, allowing us to view the diff between the schema and its previous state. On the bottom-right (3), the schema is described using the Atlas DDL.  On the bottom-left part of the screen (4), the UI displays an ERD (Entity-relation diagram) showing the different tables and the connections between them (via foreign-keys). By clicking the "expand" icon on the top-right corner of the ERD panel, we can open a more detailed view of our schema.
+
+![Schema ERD open](https://atlasgo.io/uploads/images/docs/schema-erd-open.png)
+
+### Running a migration
+
+Now that we have seen how Atlas can visualize our database schema in the UI, let’s see how to initiate a migration.
+
+First, we will make changes to our schema by clicking on the "Edit Schema" button on the top right-hand corner.
+
+![Edit Schema Button](https://atlasgo.io/uploads/images/docs/edit-schema-button.png)
+
+Next, add the table `likes` to the schema:
+```
+table "likes" {
+ schema = schema.example
+ column "id" {
+   null = false
+   type = int
+ }
+ column "user_id" {
+   null = false
+   type = int
+ }
+ column "blog_post_id" {
+   null = false
+   type = int
+ }
+ primary_key {
+   columns = [table.likes.column.id]
+ }
+ foreign_key "like_blog_post" {
+   columns     = [table.likes.column.blog_post_id]
+   ref_columns = [table.blog_posts.column.id]
+   on_update   = "RESTRICT"
+   on_delete   = "RESTRICT"
+ }
+ foreign_key "like_user" {
+   columns     = [table.likes.column.user_id]
+   ref_columns = [table.users.column.id]
+   on_update   = "RESTRICT"
+   on_delete   = "RESTRICT"
+ }
+ index "like_blog_post" {
+   columns = [table.likes.column.blog_post_id]
+ }
+ index "like_user" {
+   columns = [table.likes.column.user_id]
+ }
+}
+```
+
+Click "save" on the top right-hand corner.
+
+![Save Schema Button](https://atlasgo.io/uploads/images/docs/save-schema.png)
+
+Going back to our schema page, we can notice a few changes. First, our schema is now out of sync. This means we have created a change in the desired state that isn't yet reflected in the database. When a schema is out of sync, two separate schemas appear: desired and inspected.
+The _desired_ schema is the one being edited in the UI. The _inspected_ schema is the
+true, current state of the database. By clicking the toggle and changing the view to "unified", the schema is shown as one with a highlighted diff.
+
+Looking at the ERD, we can see that our table `likes` is green and has a plus-sign (+) next to it, showing that it needs to be added. This means the change has been recorded, but not yet applied.
+
+![Atlas Schema Not Synced Split](https://atlasgo.io/uploads/images/docs/schema-out-of-sync-unified.png)
+
+Now, let’s run a migration!
+Click "Migrate Schema" in the Schema Status box to apply the changes we want to make to our schema.
+
+The migration screen will open up, running the setup automatically. After the setup succeeds, we will see the migration plan.
+The diff in the schema in HCL on the left pane, and the planned SQL statements on the right.
+
+![Atlas Schema Not Synced Split](https://atlasgo.io/uploads/images/docs/migrate-plan-step.png)
+
+To start the migration, click "Apply". If you ever wish to not go through with a migration, you can click "Abort".
+
+![Atlas Schema Not Synced Split](https://atlasgo.io/uploads/images/docs/migrate-apply-step.png)
+
+Congrats, we have successfully completed a migration!
+
+As expected, after executing our migration plan, our database and desired schema are now synced.
+
+![Atlas Schema Not Synced Split](https://atlasgo.io/uploads/images/docs/synced-schema-after-migration.png)
+
+### Conclusion
+In this section we learned how to use the Management UI to inspect our database and run migrations.
diff --git a/schema/schemaspec/schemahcl/hcl.go b/schema/schemaspec/schemahcl/hcl.go
index 0085ba4d..9083bc72 100644
--- a/schema/schemaspec/schemahcl/hcl.go
+++ b/schema/schemaspec/schemahcl/hcl.go
@@ -52,7 +52,7 @@ func (s *state) MarshalSpec(v interface{}) ([]byte, error) {
 // UnmarshalSpec implements schemaspec.Unmarshaler.
 func (s *state) UnmarshalSpec(data []byte, v interface{}) error {
 	ctx := s.config.newCtx()
-	spec, err := decode(ctx, data)
+	spec, err := s.decode(ctx, data)
 	if err != nil {
 		return fmt.Errorf("schemahcl: failed decoding: %w", err)
 	}
@@ -63,7 +63,7 @@ func (s *state) UnmarshalSpec(data []byte, v interface{}) error {
 }
 
 // decode decodes the input Atlas HCL document and returns a *schemaspec.Resource representing it.
-func decode(ctx *hcl.EvalContext, body []byte) (*schemaspec.Resource, error) {
+func (s *state) decode(ctx *hcl.EvalContext, body []byte) (*schemaspec.Resource, error) {
 	parser := hclparse.NewParser()
 	srcHCL, diag := parser.ParseHCL(body, "")
 	if diag.HasErrors() {
@@ -80,15 +80,15 @@ func decode(ctx *hcl.EvalContext, body []byte) (*schemaspec.Resource, error) {
 	if diag := gohcl.DecodeBody(srcHCL.Body, ctx, c); diag.HasErrors() {
 		return nil, diag
 	}
-	return extract(ctx, c.Body)
+	return s.extract(ctx, c.Body)
 }
 
-func extract(ctx *hcl.EvalContext, remain hcl.Body) (*schemaspec.Resource, error) {
+func (s *state) extract(ctx *hcl.EvalContext, remain hcl.Body) (*schemaspec.Resource, error) {
 	body, ok := remain.(*hclsyntax.Body)
 	if !ok {
 		return nil, fmt.Errorf("schemahcl: expected remainder to be of type *hclsyntax.Body")
 	}
-	attrs, err := toAttrs(ctx, body.Attributes)
+	attrs, err := s.toAttrs(ctx, body.Attributes, nil)
 	if err != nil {
 		return nil, err
 	}
@@ -100,7 +100,7 @@ func extract(ctx *hcl.EvalContext, remain hcl.Body) (*schemaspec.Resource, error
 		if err != nil {
 			return nil, err
 		}
-		resource, err := toResource(ctx, blk)
+		resource, err := s.toResource(ctx, blk, []string{blk.Type})
 		if err != nil {
 			return nil, err
 		}
@@ -109,9 +109,22 @@ func extract(ctx *hcl.EvalContext, remain hcl.Body) (*schemaspec.Resource, error
 	return res, nil
 }
 
-func toAttrs(ctx *hcl.EvalContext, hclAttrs hclsyntax.Attributes) ([]*schemaspec.Attr, error) {
+// mayExtendVars gets the current scope context, and extend it with additional
+// variables if it was configured this way using WithScopedEnums.
+func (s *state) mayExtendVars(ctx *hcl.EvalContext, scope []string) *hcl.EvalContext {
+	vars, ok := s.config.pathVars[strings.Join(scope, ".")]
+	if !ok {
+		return ctx
+	}
+	ctx = ctx.NewChild()
+	ctx.Variables = vars
+	return ctx
+}
+
+func (s *state) toAttrs(ctx *hcl.EvalContext, hclAttrs hclsyntax.Attributes, scope []string) ([]*schemaspec.Attr, error) {
 	var attrs []*schemaspec.Attr
 	for _, hclAttr := range hclAttrs {
+		ctx := s.mayExtendVars(ctx, append(scope, hclAttr.Name))
 		at := &schemaspec.Attr{K: hclAttr.Name}
 		value, diag := hclAttr.Expr.Value(ctx)
 		if diag.HasErrors() {
@@ -181,20 +194,21 @@ func extractLiteralValue(value cty.Value) (*schemaspec.LiteralValue, error) {
 	}
 }
 
-func toResource(ctx *hcl.EvalContext, block *hclsyntax.Block) (*schemaspec.Resource, error) {
+func (s *state) toResource(ctx *hcl.EvalContext, block *hclsyntax.Block, scope []string) (*schemaspec.Resource, error) {
 	spec := &schemaspec.Resource{
 		Type: block.Type,
 	}
 	if len(block.Labels) > 0 {
 		spec.Name = block.Labels[0]
 	}
-	attrs, err := toAttrs(ctx, block.Body.Attributes)
+	ctx = s.mayExtendVars(ctx, scope)
+	attrs, err := s.toAttrs(ctx, block.Body.Attributes, scope)
 	if err != nil {
 		return nil, err
 	}
 	spec.Attrs = attrs
 	for _, blk := range block.Body.Blocks {
-		res, err := toResource(ctx, blk)
+		res, err := s.toResource(ctx, blk, append(scope, blk.Type))
 		if err != nil {
 			return nil, err
 		}
diff --git a/schema/schemaspec/schemahcl/opts.go b/schema/schemaspec/schemahcl/opts.go
index b391a85e..96db0a96 100644
--- a/schema/schemaspec/schemahcl/opts.go
+++ b/schema/schemaspec/schemahcl/opts.go
@@ -14,8 +14,9 @@ import (
 type (
 	// Config configures an unmarshaling.
 	Config struct {
-		types  []*schemaspec.TypeSpec
-		newCtx func() *hcl.EvalContext
+		types    []*schemaspec.TypeSpec
+		newCtx   func() *hcl.EvalContext
+		pathVars map[string]map[string]cty.Value
 	}
 
 	// Option configures a Config.
@@ -25,6 +26,7 @@ type (
 // New returns a state configured with options.
 func New(opts ...Option) *state {
 	cfg := &Config{
+		pathVars: make(map[string]map[string]cty.Value),
 		newCtx: func() *hcl.EvalContext {
 			return &hcl.EvalContext{
 				Variables: make(map[string]cty.Value),
@@ -38,6 +40,32 @@ func New(opts ...Option) *state {
 	return &state{config: cfg}
 }
 
+// WithScopedEnums configured a list of allowed ENUMs to be used in
+// the given context, block or attribute. For example, the following
+// option allows setting HASH or BTREE to the "using" attribute in
+// "index" block.
+//
+//	WithScopedEnums("table.index.type", "HASH", "BTREE")
+//
+//	table "t" {
+//		...
+//		index "i" {
+//			type = HASH     // Allowed.
+//			type = INVALID  // Not Allowed.
+//		}
+//	}
+//
+//
+func WithScopedEnums(path string, enums ...string) Option {
+	return func(c *Config) {
+		vars := make(map[string]cty.Value, len(enums))
+		for i := range enums {
+			vars[enums[i]] = cty.StringVal(enums[i])
+		}
+		c.pathVars[path] = vars
+	}
+}
+
 // WithTypes configures the list of given types as identifiers in the unmarshaling context.
 func WithTypes(typeSpecs []*schemaspec.TypeSpec) Option {
 	newCtx := func() *hcl.EvalContext {
diff --git a/schema/schemaspec/spec.go b/schema/schemaspec/spec.go
index c58a70cf..f7fe00de 100644
--- a/schema/schemaspec/spec.go
+++ b/schema/schemaspec/spec.go
@@ -103,18 +103,28 @@ type (
 	}
 )
 
-// Int returns an integer from the Value of the Attr. If The value is not a LiteralValue or the value
+// Int returns an int from the Value of the Attr. If The value is not a LiteralValue or the value
 // cannot be converted to an integer an error is returned.
 func (a *Attr) Int() (int, error) {
+	i, err := a.Int64()
+	if err != nil {
+		return 0, err
+	}
+	return int(i), nil
+}
+
+// Int64 returns an int64 from the Value of the Attr. If The value is not a LiteralValue or the value
+// cannot be converted to an integer an error is returned.
+func (a *Attr) Int64() (int64, error) {
 	lit, ok := a.V.(*LiteralValue)
 	if !ok {
 		return 0, fmt.Errorf("schema: cannot read attribute %q as literal", a.K)
 	}
-	s, err := strconv.Atoi(lit.V)
+	i, err := strconv.ParseInt(lit.V, 10, 64)
 	if err != nil {
-		return 0, fmt.Errorf("schema: cannot read attribute %q as integer", a.K)
+		return 0, fmt.Errorf("schema: cannot read attribute %q as integer: %w", a.K, err)
 	}
-	return s, nil
+	return i, nil
 }
 
 // String returns a string from the Value of the Attr. If The value is not a LiteralValue
@@ -131,7 +141,11 @@ func (a *Attr) Bool() (bool, error) {
 	if !ok {
 		return false, fmt.Errorf("schema: cannot read attribute %q as literal", a.K)
 	}
-	return strconv.ParseBool(lit.V)
+	b, err := strconv.ParseBool(lit.V)
+	if err != nil {
+		return false, fmt.Errorf("schema: cannot read attribute %q as bool: %w", a.K, err)
+	}
+	return b, nil
 }
 
 // Ref returns the string representation of the Attr. If the value is not a Ref or the value
diff --git a/sql/internal/specutil/convert.go b/sql/internal/specutil/convert.go
index 63506fa6..9dcfa97a 100644
--- a/sql/internal/specutil/convert.go
+++ b/sql/internal/specutil/convert.go
@@ -491,7 +491,7 @@ 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")
+		return "", errors.New("missing 'schema' attribute")
 	}
 	parts := strings.Split(ref.V, ".")
 	if len(parts) < 2 || parts[0] != "$schema" {
diff --git a/sql/internal/specutil/spec.go b/sql/internal/specutil/spec.go
index 9845b891..1fda8080 100644
--- a/sql/internal/specutil/spec.go
+++ b/sql/internal/specutil/spec.go
@@ -41,6 +41,14 @@ func RawAttr(k, v string) *schemaspec.Attr {
 	}
 }
 
+// VarAttr is a helper method for constructing *schemaspec.Attr instances that contain a variable reference.
+func VarAttr(k, v string) *schemaspec.Attr {
+	return &schemaspec.Attr{
+		K: k,
+		V: &schemaspec.Ref{V: v},
+	}
+}
+
 // ListAttr is a helper method for constructing *schemaspec.Attr instances that contain list values.
 func ListAttr(k string, litValues ...string) *schemaspec.Attr {
 	lv := &schemaspec.ListValue{}
diff --git a/sql/migrate/migrate.go b/sql/migrate/migrate.go
index ca469874..366254bb 100644
--- a/sql/migrate/migrate.go
+++ b/sql/migrate/migrate.go
@@ -9,10 +9,9 @@ import (
 	"context"
 	"errors"
 	"fmt"
-	"io/fs"
+	"io"
 	"os"
 	"path/filepath"
-	"sort"
 	"strings"
 	"text/template"
 	"time"
@@ -127,182 +126,47 @@ func Schema(s *schema.Schema) StateReader {
 }
 
 type (
-	// Dir represents a versioned migration directory.
-	Dir struct {
-		fs        fs.FS
-		conn      Driver
-		pattern   string
-		templates []struct{ N, T *template.Template }
+	// Dir describes the methods needed to work with the Planner.
+	Dir interface {
+		// Open opens the named file.
+		Open(name string) (io.ReadWriteCloser, error)
 	}
 
-	// DirOption allows configuring the Dir
-	// using functional options.
-	DirOption func(*Dir) error
-)
-
-// NewDir creates a new workspace directory based
-// on the given configuration options.
-func NewDir(opts ...DirOption) (*Dir, error) {
-	d := &Dir{
-		fs: dirFS{
-			dir: "migrations",
-			FS:  os.DirFS("migrations"),
-		},
-		pattern: "*.sql",
-	}
-	for _, opt := range opts {
-		if err := opt(d); err != nil {
-			return nil, err
-		}
-	}
-	if len(d.templates) == 0 {
-		d.templates = []struct{ N, T *template.Template }{defaultTemplate}
+	// Formatter wraps the Format method for naming and dumping a migration plan to one or more Files.
+	Formatter interface {
+		// Format formats the given Plan.
+		// The returned map contains the contents for each file to dump.
+		Format(*Plan) ([]File, error)
 	}
-	return d, nil
-}
 
-type (
-	// dirFS wraps the os.DirFS with additional writing capabilities.
-	dirFS struct {
-		fs.FS
-		dir string
-	}
-	// FileRemoveWriter wraps the WriteFile and RemoveFile methods
-	// to allow editing the migration directory on development.
-	FileRemoveWriter interface {
-		RemoveFile(name string) error
-		WriteFile(name string, data []byte, perm fs.FileMode) error
+	// File represents a migration file.
+	File interface {
+		io.Reader
+		Name() string
 	}
-)
-
-// WriteFile implements the FileWriter interface.
-func (d *dirFS) WriteFile(name string, data []byte, perm fs.FileMode) error {
-	return os.WriteFile(filepath.Join(d.dir, name), data, perm)
-}
 
-// RemoveFile implements the FileWriter interface.
-func (d *dirFS) RemoveFile(name string) error {
-	return os.Remove(filepath.Join(d.dir, name))
-}
-
-// DirFS configures the FS used by the migration directory.
-func DirFS(fs fs.FS) DirOption {
-	return func(d *Dir) error {
-		d.fs = fs
-		return nil
-	}
-}
-
-// DirPath configures the FS used by the migration directory
-// to point the given OS directory.
-func DirPath(path string) DirOption {
-	return func(d *Dir) error {
-		fi, err := os.Stat(path)
-		if err != nil {
-			return err
-		}
-		if !fi.IsDir() {
-			return fmt.Errorf("sql/migrate: %q not a dir", path)
-		}
-		d.fs = &dirFS{FS: os.DirFS(path), dir: path}
-		return nil
-	}
-}
-
-// DirGlob configures the glob/pattern for reading
-// migration files from the directory. For example:
-//
-//	migrate.NewDir(
-//		migrate.DirPath("migrations"),
-//		migrate.DirGlob("*.up.sql"),
-//	)
-//
-func DirGlob(pattern string) DirOption {
-	return func(d *Dir) error {
-		d.pattern = pattern
-		return nil
-	}
-}
-
-// DirConn provides a Driver connection to a database. It is usually connected to
-// an ephemeral database for emulating migration changes on it and calculating the
-// "current state" to be compared with the "desired state".
-func DirConn(conn Driver) DirOption {
-	return func(d *Dir) error {
-		d.conn = conn
-		return nil
-	}
-}
-
-var (
-	// TemplateFuncs defines the global functions available for the templates.
-	TemplateFuncs = template.FuncMap{
-		"hasSuffix": strings.HasSuffix,
-		"timestamp": func() int64 {
-			return time.Now().Unix()
-		},
-	}
-	defaultTemplate = struct {
-		N, T *template.Template
-	}{
-		N: template.Must(template.New("name").
-			Funcs(TemplateFuncs).
-			Parse("{{timestamp}}_{{.Name}}.sql")),
-		T: template.Must(template.New("name").
-			Funcs(TemplateFuncs).
-			Parse(`
-{{- range $c := .Changes }}
-	{{- $cmd := $c.Cmd }}
-	{{- if not (hasSuffix $c.Cmd ";") }}
-		{{- $cmd = print $cmd ";" }}
-	{{- end }}
-	{{- println $cmd }}
-{{- end }}`)),
+	// Planner can plan the steps to take to migrate from one state to another. It uses the enclosed FS to write
+	// those changes to versioned migration files.
+	Planner struct {
+		drv Driver
+		dir Dir
+		fmt Formatter
 	}
 )
 
-// DirTemplates configures template files for writing
-// the Plan object to the migration directory.
-//
-//	migrate.NewDir(
-//		migrate.DirPath("migrations"),
-//		migrate.DirTemplates("{{timestamp}}{{.Name}}.up.sql", "{{range c := .Changes}}{{println c.Cmd}}{{end}}"),
-//	)
-//
-//	migrate.NewDir(
-//		migrate.DirPath("migrations"),
-//		migrate.DirTemplates(
-//			"{{timestamp}}{{.Name}}.up.sql", "{{range $c := .Changes}}{{println $c.Cmd}}{{end}}",
-//			"{{timestamp}}{{.Name}}.down.sql", "{{range $c := .Changes}}{{println $c.Reverse}}{{end}}",
-//		),
-//	)
-//
-func DirTemplates(nameFileTmpl ...string) DirOption {
-	return func(d *Dir) error {
-		if n := len(nameFileTmpl); n == 0 || n%2 == 1 {
-			return fmt.Errorf("odd or zero argument count")
-		}
-		for i := 0; i < len(nameFileTmpl); i += 2 {
-			if err := d.addTemplate(nameFileTmpl[i], nameFileTmpl[i+1]); err != nil {
-				return err
-			}
-		}
-		return nil
+// New creates a new Planner.
+func New(drv Driver, dir Dir, fmt Formatter) *Planner {
+	return &Planner{
+		drv: drv,
+		dir: dir,
+		fmt: fmt,
 	}
 }
 
-// ReadState reads the current database/realm state that is stored in the migration directory.
-// The given emulator driver is used for playing all migration files against to it.
-func (d *Dir) ReadState(ctx context.Context) (*schema.Realm, error) {
-	realm, _, err := d.readStateOf(ctx, -1)
-	return realm, err
-}
-
-// Plan calculates the migration Plan required for moving from the directory state to
-// the next state (to). A StateReader, can be another directory, static schema elements
-// or a Driver connection.
-func (d *Dir) Plan(ctx context.Context, name string, to StateReader) (*Plan, error) {
-	current, err := d.ReadState(ctx)
+// Plan calculates the migration Plan required for moving the current state (from) state to
+// the next state (to). A StateReader can be a directory, static schema elements or a Driver connection.
+func (p *Planner) Plan(ctx context.Context, name string, from StateReader, to StateReader) (*Plan, error) {
+	current, err := from.ReadState(ctx)
 	if err != nil {
 		return nil, err
 	}
@@ -310,120 +174,151 @@ func (d *Dir) Plan(ctx context.Context, name string, to StateReader) (*Plan, err
 	if err != nil {
 		return nil, err
 	}
-	changes, err := d.conn.RealmDiff(current, desired)
+	changes, err := p.drv.RealmDiff(current, desired)
 	if err != nil {
 		return nil, err
 	}
 	if len(changes) == 0 {
 		return nil, ErrNoPlan
 	}
-	return d.conn.PlanChanges(ctx, name, changes)
+	return p.drv.PlanChanges(ctx, name, changes)
 }
 
-// WritePlan writes the given plan to the directory
-// based on the given Write configuration.
-func (d *Dir) WritePlan(p *Plan) error {
-	rw, ok := d.fs.(FileRemoveWriter)
-	if !ok {
-		return fmt.Errorf("fs.FS does not support editing: %T", d.fs)
+// WritePlan writes the given plan to the Dir based on the given Formatter.
+func (p *Planner) WritePlan(plan *Plan) error {
+	fs, err := p.fmt.Format(plan)
+	if err != nil {
+		return err
 	}
-	for _, t := range d.templates {
-		var b bytes.Buffer
-		if err := t.N.Execute(&b, p); err != nil {
-			return err
-		}
-		if b.String() == "" {
-			return errors.New("file name cannot be empty")
-		}
-		name := b.String()
-		b.Reset()
-		if err := t.T.Execute(&b, p); err != nil {
+	for _, f := range fs {
+		fh, err := p.dir.Open(f.Name())
+		if err != nil {
 			return err
 		}
-		if err := rw.WriteFile(name, b.Bytes(), 0644); err != nil {
+		_, err = io.Copy(fh, f)
+		fh.Close()
+		if err != nil {
 			return err
 		}
 	}
 	return nil
 }
 
-// Compact compacts the first n migration files into one. If n < 0, all files are selected.
-func (d *Dir) Compact(ctx context.Context, name string, n int) error {
-	rw, ok := d.fs.(FileRemoveWriter)
-	if !ok {
-		return fmt.Errorf("fs.FS does not support editing: %T", d.fs)
-	}
-	desired, files, err := d.readStateOf(ctx, n)
-	if err != nil {
-		return err
-	}
-	changes, err := d.conn.RealmDiff(&schema.Realm{}, desired)
+// LocalDir implements Dir for a local path.
+type LocalDir struct {
+	dir string
+}
+
+// Open implements Dir.Open.
+func (d *LocalDir) Open(name string) (File, error) {
+	return os.Create(filepath.Join(d.dir, name))
+}
+
+// NewLocalDir returns a new the Dir used by a Planner to work on the given local path.
+func NewLocalDir(path string) (*LocalDir, error) {
+	fi, err := os.Stat(path)
 	if err != nil {
-		return err
+		return nil, err
 	}
-	if len(changes) == 0 {
-		return nil
+	if !fi.IsDir() {
+		return nil, fmt.Errorf("sql/migrate: %q is not a dir", path)
 	}
-	for _, c := range changes {
-		// Add the "IF NOT EXISTS" clause to the schema
-		// creation as it is usually created manually.
-		if c, ok := c.(*schema.AddSchema); ok {
-			c.Extra = append(c.Extra, &schema.IfNotExists{})
-		}
+	return &LocalDir{dir: path}, nil
+}
+
+// TemplateFormatter implements Formatter by using templates.
+type TemplateFormatter struct {
+	templates []struct{ N, C *template.Template }
+}
+
+var (
+	// templateFuncs contains the template.FuncMap for the DefaultFormatter.
+	templateFuncs = template.FuncMap{
+		"now": time.Now,
+		"sem": ensureSemicolonSuffix,
+		"rev": reverse,
+	}
+	// DefaultFormatter is a default implementation for Formatter. Compatible with golang-migrate/migrate.
+	DefaultFormatter = &TemplateFormatter{
+		templates: []struct{ N, C *template.Template }{
+			{
+				N: template.Must(template.New("").Funcs(templateFuncs).Parse(
+					"{{ now.Unix }}{{ with .Name }}_{{ . }}{{ end }}.up.sql",
+				)),
+				C: template.Must(template.New("").Funcs(templateFuncs).Parse(
+					"{{ range .Changes }}{{ println (sem .Cmd) }}{{ end }}",
+				)),
+			},
+			{
+				N: template.Must(template.New("").Funcs(templateFuncs).Parse(
+					"{{ now.Unix }}{{ with .Name }}_{{ . }}{{ end }}.down.sql",
+				)),
+				C: template.Must(template.New("").Funcs(templateFuncs).Parse(
+					"{{ range rev .Changes }}{{ println (sem .Reverse) }}{{ end }}",
+				)),
+			},
+		},
 	}
-	plan, err := d.conn.PlanChanges(ctx, name, changes)
-	if err != nil {
-		return err
+)
+
+// NewTemplateFormatter creates a new Formatter working with the given templates.
+//
+//	migrate.NewTemplateFormatter(
+//		template.Must(template.New("").Parse("{{now.Unix}}{{.Name}}.sql")),                 // name template
+//		template.Must(template.New("").Parse("{{range .Changes}}{{println .Cmd}}{{end}}")), // content template
+//	)
+//
+func NewTemplateFormatter(templates ...*template.Template) (*TemplateFormatter, error) {
+	if n := len(templates); n == 0 || n%2 == 1 {
+		return nil, fmt.Errorf("zero or odd number of templates given")
 	}
-	for _, f := range files {
-		if err := rw.RemoveFile(f); err != nil {
-			return fmt.Errorf("remove file: %q: %w", f, err)
-		}
+	t := new(TemplateFormatter)
+	for i := 0; i < len(templates); i += 2 {
+		t.templates = append(t.templates, struct{ N, C *template.Template }{templates[i], templates[i+1]})
 	}
-	return d.WritePlan(plan)
+	return t, nil
 }
 
-// readStateOf of first n files. If n < 0, all files are selected.
-func (d *Dir) readStateOf(ctx context.Context, n int) (*schema.Realm, []string, error) {
-	files, err := fs.Glob(d.fs, d.pattern)
-	if err != nil {
-		return nil, nil, err
-	}
-	switch {
-	case n == 0 || n >= len(files):
-		return nil, nil, fmt.Errorf("sql/migrate: invalid number for selected files: %d", n)
-	case n > 0:
-		files = files[:n]
-	}
-	// Files are expected to be sorted lexicographically.
-	sort.Slice(files, func(i, j int) bool {
-		return files[i] < files[j]
-	})
-	for _, f := range files {
-		buf, err := fs.ReadFile(d.fs, f)
-		if err != nil {
-			return nil, nil, fmt.Errorf("sql/migrate: scan migration script %q: %w", f, err)
+// Format implements the Formatter interface.
+func (t *TemplateFormatter) Format(plan *Plan) ([]File, error) {
+	fs := make([]File, 0, len(t.templates))
+	for _, tpl := range t.templates {
+		var n, c bytes.Buffer
+		if err := tpl.N.Execute(&n, plan); err != nil {
+			return nil, err
 		}
-		if _, err := d.conn.ExecContext(ctx, string(buf)); err != nil {
-			return nil, nil, fmt.Errorf("sql/migrate: execute migration script %q: %w", f, err)
+		if err := tpl.C.Execute(&c, plan); err != nil {
+			return nil, err
 		}
+		fs = append(fs, &templateFile{
+			Buffer: &c,
+			n:      n.String(),
+		})
 	}
-	realm, err := d.conn.InspectRealm(ctx, nil)
-	if err != nil {
-		return nil, nil, err
-	}
-	return realm, files, nil
+	return fs, nil
 }
 
-func (d *Dir) addTemplate(nameTmpl, fileTmpl string) error {
-	nameT, err := template.New("name").Funcs(TemplateFuncs).Parse(nameTmpl)
-	if err != nil {
-		return err
+type templateFile struct {
+	*bytes.Buffer
+	n string
+}
+
+// Name implements the File interface.
+func (f *templateFile) Name() string { return f.n }
+
+// reverse changes for the down migration.
+func reverse(changes []*Change) []*Change {
+	rev := make([]*Change, len(changes))
+	for i, j := 0, len(changes)-1; i < j; i, j = i+1, j-1 {
+		rev[i], rev[j] = changes[j], changes[i]
 	}
-	fileT, err := template.New("file").Funcs(TemplateFuncs).Parse(fileTmpl)
-	if err != nil {
-		return err
+	return rev
+}
+
+// ensure an SQL statement has a trailing ";".
+func ensureSemicolonSuffix(s string) string {
+	if !strings.HasSuffix(s, ";") {
+		return s + ";"
 	}
-	d.templates = append(d.templates, struct{ N, T *template.Template }{N: nameT, T: fileT})
-	return nil
+	return s
 }
diff --git a/sql/mysql/diff.go b/sql/mysql/diff.go
index 35ca90de..1defb247 100644
--- a/sql/mysql/diff.go
+++ b/sql/mysql/diff.go
@@ -228,10 +228,16 @@ func (*diff) charsetChange(from, top, to []schema.Attr) schema.Change {
 // attribute in case it is not the default.
 func (*diff) autoIncChange(from, to []schema.Attr) schema.Change {
 	var fromA, toA AutoIncrement
-	// The table is empty and AUTO_INCREMENT was not configured. This can happen
-	// because older versions of MySQL (< 8.0) stored the AUTO_INCREMENT counter
-	// in main memory (not persistent), and the value is reset on process restart.
-	if sqlx.Has(from, &fromA) && sqlx.Has(to, &toA) && fromA.V <= 1 && toA.V > 1 {
+	switch fromHas, toHas := sqlx.Has(from, &fromA), sqlx.Has(to, &toA); {
+	// Ignore if the AUTO_INCREMENT attribute was dropped from the desired schema.
+	case fromHas && !toHas:
+	// The AUTO_INCREMENT exists in the desired schema, and may not exists in the inspected one.
+	// This can happen because older versions of MySQL (< 8.0) stored the AUTO_INCREMENT counter
+	// in main memory (not persistent), and the value is reset on process restart for empty tables.
+	case toA.V > 1 && toA.V > fromA.V:
+		// Suggest a diff only if the desired value is greater than the inspected one,
+		// because this attribute cannot be maintained in users schema and used to set
+		// up only the initial value.
 		return &schema.ModifyAttr{
 			From: &fromA,
 			To:   &toA,
@@ -243,7 +249,7 @@ func (*diff) autoIncChange(from, to []schema.Attr) schema.Change {
 // indexType returns the index type from its attribute.
 // The default type is BTREE if no type was specified.
 func indexType(attr []schema.Attr) *IndexType {
-	t := &IndexType{T: "BTREE"}
+	t := &IndexType{T: IndexTypeBTree}
 	if sqlx.Has(attr, t) {
 		t.T = strings.ToUpper(t.T)
 	}
diff --git a/sql/mysql/driver.go b/sql/mysql/driver.go
index ce7ed624..90d33006 100644
--- a/sql/mysql/driver.go
+++ b/sql/mysql/driver.go
@@ -188,6 +188,11 @@ const (
 
 // Additional common constants in MySQL.
 const (
+	IndexTypeBTree    = "BTREE"
+	IndexTypeHash     = "HASH"
+	IndexTypeFullText = "FULLTEXT"
+	IndexTypeSpatial  = "SPATIAL"
+
 	currentTS     = "current_timestamp"
 	defaultGen    = "default_generated"
 	autoIncrement = "auto_increment"
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index 605dce4b..f90da736 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -28,6 +28,9 @@ func (i *inspect) InspectRealm(ctx context.Context, opts *schema.InspectRealmOpt
 		return nil, err
 	}
 	r := schema.NewRealm(schemas...).SetCharset(i.charset).SetCollation(i.collate)
+	if len(schemas) == 0 {
+		return r, nil
+	}
 	if err := i.inspectTables(ctx, r, nil); err != nil {
 		return nil, err
 	}
@@ -772,7 +775,7 @@ type (
 	// IndexType represents an index type.
 	IndexType struct {
 		schema.Attr
-		T string // BTREE, FULLTEXT, HASH, RTREE
+		T string // BTREE, HASH, FULLTEXT, SPATIAL, RTREE
 	}
 
 	// BitType represents a bit type.
diff --git a/sql/mysql/migrate.go b/sql/mysql/migrate.go
index 26342cc9..4377edff 100644
--- a/sql/mysql/migrate.go
+++ b/sql/mysql/migrate.go
@@ -213,20 +213,14 @@ func (s *state) addTable(add *schema.AddTable) error {
 		})
 		if pk := add.T.PrimaryKey; pk != nil {
 			b.Comma().P("PRIMARY KEY")
-			s.indexParts(b, pk.Parts)
-			s.attr(b, pk.Attrs...)
+			indexParts(b, pk.Parts)
 		}
 		if len(add.T.Indexes) > 0 {
 			b.Comma()
 		}
 		b.MapComma(add.T.Indexes, func(i int, b *sqlx.Builder) {
 			idx := add.T.Indexes[i]
-			if idx.Unique {
-				b.P("UNIQUE")
-			}
-			b.P("INDEX").Ident(idx.Name)
-			s.indexParts(b, idx.Parts)
-			s.attr(b, idx.Attrs...)
+			index(b, idx)
 		})
 		if len(add.T.ForeignKeys) > 0 {
 			b.Comma()
@@ -352,22 +346,12 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 			reversible = false
 		case *schema.AddIndex:
 			b.P("ADD")
-			if change.I.Unique {
-				b.P("UNIQUE")
-			}
-			b.P("INDEX").Ident(change.I.Name)
-			s.indexParts(b, change.I.Parts)
-			s.attr(b, change.I.Attrs...)
+			index(b, change.I)
 			reverse.Comma().P("DROP INDEX").Ident(change.I.Name)
 		case *schema.DropIndex:
 			b.P("DROP INDEX").Ident(change.I.Name)
 			reverse.Comma().P("ADD")
-			if change.I.Unique {
-				reverse.P("UNIQUE")
-			}
-			reverse.P("INDEX").Ident(change.I.Name)
-			s.indexParts(reverse, change.I.Parts)
-			s.attr(reverse, change.I.Attrs...)
+			index(reverse, change.I)
 			reversible = true
 		case *schema.AddForeignKey:
 			b.P("ADD")
@@ -498,7 +482,29 @@ func (s *state) column(b *sqlx.Builder, t *schema.Table, c *schema.Column) error
 	return nil
 }
 
-func (s *state) indexParts(b *sqlx.Builder, parts []*schema.IndexPart) {
+func index(b *sqlx.Builder, idx *schema.Index) {
+	var t IndexType
+	if sqlx.Has(idx.Attrs, &t) {
+		t.T = strings.ToUpper(t.T)
+	}
+	switch {
+	case idx.Unique:
+		b.P("UNIQUE")
+	case t.T == IndexTypeFullText || t.T == IndexTypeSpatial:
+		b.P(t.T)
+	}
+	b.P("INDEX").Ident(idx.Name)
+	// Skip BTREE as it is the default type.
+	if t.T == IndexTypeHash {
+		b.P("USING", t.T)
+	}
+	indexParts(b, idx.Parts)
+	if c := (schema.Comment{}); sqlx.Has(idx.Attrs, &c) {
+		b.P("COMMENT", quote(c.Text))
+	}
+}
+
+func indexParts(b *sqlx.Builder, parts []*schema.IndexPart) {
 	b.Wrap(func(b *sqlx.Builder) {
 		b.MapComma(parts, func(i int, b *sqlx.Builder) {
 			switch part := parts[i]; {
diff --git a/sql/mysql/sqlspec.go b/sql/mysql/sqlspec.go
index 4e594245..ee39f96b 100644
--- a/sql/mysql/sqlspec.go
+++ b/sql/mysql/sqlspec.go
@@ -3,6 +3,7 @@ package mysql
 import (
 	"fmt"
 	"reflect"
+	"strings"
 
 	"ariga.io/atlas/schema/schemaspec"
 	"ariga.io/atlas/schema/schemaspec/schemahcl"
@@ -63,7 +64,10 @@ func MarshalSpec(v interface{}, marshaler schemaspec.Marshaler) ([]byte, error)
 }
 
 var (
-	hclState = schemahcl.New(schemahcl.WithTypes(TypeRegistry.Specs()))
+	hclState = schemahcl.New(
+		schemahcl.WithTypes(TypeRegistry.Specs()),
+		schemahcl.WithScopedEnums("table.index.type", IndexTypeBTree, IndexTypeHash, IndexTypeFullText, IndexTypeSpatial),
+	)
 	// UnmarshalHCL unmarshals an Atlas HCL DDL document into v.
 	UnmarshalHCL = schemaspec.UnmarshalerFunc(func(bytes []byte, i interface{}) error {
 		return UnmarshalSpec(bytes, hclState, i)
@@ -78,16 +82,41 @@ var (
 // ForeignKeySpecs into ForeignKeys, as the target tables do not necessarily exist in the schema
 // at this point. Instead, the linking is done by the convertSchema function.
 func convertTable(spec *sqlspec.Table, parent *schema.Schema) (*schema.Table, error) {
-	t, err := specutil.Table(spec, parent, convertColumn, specutil.PrimaryKey, specutil.Index, convertCheck)
+	t, err := specutil.Table(spec, parent, convertColumn, specutil.PrimaryKey, convertIndex, convertCheck)
 	if err != nil {
 		return nil, err
 	}
 	if err := convertCharset(spec, &t.Attrs); err != nil {
 		return nil, err
 	}
+	// MySQL allows setting the initial AUTO_INCREMENT value
+	// on the table definition.
+	if attr, ok := spec.Attr("auto_increment"); ok {
+		v, err := attr.Int64()
+		if err != nil {
+			return nil, err
+		}
+		t.AddAttrs(&AutoIncrement{V: v})
+	}
 	return t, err
 }
 
+// convertIndex converts a sqlspec.Index into a schema.Index.
+func convertIndex(spec *sqlspec.Index, parent *schema.Table) (*schema.Index, error) {
+	idx, err := specutil.Index(spec, parent)
+	if err != nil {
+		return nil, err
+	}
+	if attr, ok := spec.Attr("type"); ok {
+		t, err := attr.String()
+		if err != nil {
+			return nil, err
+		}
+		idx.Attrs = append(idx.Attrs, &IndexType{T: t})
+	}
+	return idx, nil
+}
+
 // convertCheck converts a sqlspec.Check into a schema.Check.
 func convertCheck(spec *sqlspec.Check) (*schema.Check, error) {
 	c, err := specutil.Check(spec)
@@ -120,6 +149,15 @@ func convertColumn(spec *sqlspec.Column, _ *schema.Table) (*schema.Column, error
 		}
 		c.AddAttrs(&OnUpdate{A: exp.X})
 	}
+	if attr, ok := spec.Attr("auto_increment"); ok {
+		b, err := attr.Bool()
+		if err != nil {
+			return nil, err
+		}
+		if b {
+			c.AddAttrs(&AutoIncrement{})
+		}
+	}
 	return c, err
 }
 
@@ -149,7 +187,7 @@ func tableSpec(t *schema.Table) (*sqlspec.Table, error) {
 		t,
 		columnSpec,
 		specutil.FromPrimaryKey,
-		specutil.FromIndex,
+		indexSpec,
 		specutil.FromForeignKey,
 		checkSpec,
 	)
@@ -165,6 +203,17 @@ func tableSpec(t *schema.Table) (*sqlspec.Table, error) {
 	return ts, nil
 }
 
+func indexSpec(idx *schema.Index) (*sqlspec.Index, error) {
+	spec, err := specutil.FromIndex(idx)
+	if err != nil {
+		return nil, err
+	}
+	if i := (IndexType{}); sqlx.Has(idx.Attrs, &i) {
+		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.VarAttr("type", strings.ToUpper(i.T)))
+	}
+	return spec, nil
+}
+
 // columnSpec converts from a concrete MySQL schema.Column into a sqlspec.Column.
 func columnSpec(c *schema.Column, t *schema.Table) (*sqlspec.Column, error) {
 	col, err := specutil.FromColumn(c, columnTypeSpec)
diff --git a/sql/postgres/diff.go b/sql/postgres/diff.go
index 01168673..00aabd80 100644
--- a/sql/postgres/diff.go
+++ b/sql/postgres/diff.go
@@ -103,11 +103,11 @@ func (d *diff) IsGeneratedIndexName(t *schema.Table, idx *schema.Index) bool {
 // IndexAttrChanged reports if the index attributes were changed.
 // The default type is BTREE if no type was specified.
 func (*diff) IndexAttrChanged(from, to []schema.Attr) bool {
-	t1 := &IndexType{T: "BTREE"}
+	t1 := &IndexType{T: IndexTypeBTree}
 	if sqlx.Has(from, t1) {
 		t1.T = strings.ToUpper(t1.T)
 	}
-	t2 := &IndexType{T: "BTREE"}
+	t2 := &IndexType{T: IndexTypeBTree}
 	if sqlx.Has(to, t2) {
 		t2.T = strings.ToUpper(t2.T)
 	}
diff --git a/sql/postgres/driver.go b/sql/postgres/driver.go
index 5586c482..a4baa483 100644
--- a/sql/postgres/driver.go
+++ b/sql/postgres/driver.go
@@ -134,3 +134,11 @@ const (
 	TypeInterval    = "interval"
 	TypeUserDefined = "user-defined"
 )
+
+// List of supported index types.
+const (
+	IndexTypeBTree = "BTREE"
+	IndexTypeHash  = "HASH"
+	IndexTypeGIN   = "GIN"
+	IndexTypeGiST  = "GIST"
+)
diff --git a/sql/postgres/migrate.go b/sql/postgres/migrate.go
index 5540094b..8e37d9d2 100644
--- a/sql/postgres/migrate.go
+++ b/sql/postgres/migrate.go
@@ -478,8 +478,7 @@ func (s *state) addIndexes(t *schema.Table, indexes ...*schema.Index) {
 			b.Ident(idx.Name)
 		}
 		b.P("ON").Table(t)
-		s.indexParts(b, idx.Parts)
-		s.indexAttrs(b, idx.Attrs)
+		s.index(b, idx)
 		s.append(&migrate.Change{
 			Cmd:     b.String(),
 			Comment: fmt.Sprintf("Create index %q to table: %q", idx.Name, t.Name),
@@ -639,15 +638,16 @@ func (s *state) partAttrs(b *sqlx.Builder, p *schema.IndexPart) {
 	}
 }
 
-func (s *state) indexAttrs(b *sqlx.Builder, attrs []schema.Attr) {
+func (s *state) index(b *sqlx.Builder, idx *schema.Index) {
 	// Avoid appending the default method.
-	if t := (IndexType{}); sqlx.Has(attrs, &t) && strings.ToLower(t.T) != "btree" {
-		b.P("USING").P(t.T)
+	if t := (IndexType{}); sqlx.Has(idx.Attrs, &t) && strings.ToUpper(t.T) != IndexTypeBTree {
+		b.P("USING", t.T)
 	}
-	if p := (IndexPredicate{}); sqlx.Has(attrs, &p) {
+	s.indexParts(b, idx.Parts)
+	if p := (IndexPredicate{}); sqlx.Has(idx.Attrs, &p) {
 		b.P("WHERE").P(p.P)
 	}
-	for _, attr := range attrs {
+	for _, attr := range idx.Attrs {
 		switch attr.(type) {
 		case *schema.Comment, *ConType, *IndexType, *IndexPredicate:
 		default:
diff --git a/sql/postgres/sqlspec.go b/sql/postgres/sqlspec.go
index e4f05efc..43c589e6 100644
--- a/sql/postgres/sqlspec.go
+++ b/sql/postgres/sqlspec.go
@@ -91,7 +91,10 @@ func MarshalSpec(v interface{}, marshaler schemaspec.Marshaler) ([]byte, error)
 }
 
 var (
-	hclState = schemahcl.New(schemahcl.WithTypes(TypeRegistry.Specs()))
+	hclState = schemahcl.New(
+		schemahcl.WithTypes(TypeRegistry.Specs()),
+		schemahcl.WithScopedEnums("table.index.type", IndexTypeBTree, IndexTypeHash, IndexTypeGIN, IndexTypeGiST),
+	)
 	// UnmarshalHCL unmarshals an Atlas HCL DDL document into v.
 	UnmarshalHCL = schemaspec.UnmarshalerFunc(func(bytes []byte, i interface{}) error {
 		return UnmarshalSpec(bytes, hclState, i)
@@ -168,7 +171,7 @@ func Schema(spec *sqlspec.Schema, tables []*sqlspec.Table, enums []*Enum) (*sche
 // ForeignKeySpecs into ForeignKeys, as the target tables do not necessarily exist in the schema
 // at this point. Instead, the linking is done by the convertSchema function.
 func convertTable(spec *sqlspec.Table, parent *schema.Schema) (*schema.Table, error) {
-	return specutil.Table(spec, parent, convertColumn, specutil.PrimaryKey, specutil.Index, specutil.Check)
+	return specutil.Table(spec, parent, convertColumn, specutil.PrimaryKey, convertIndex, specutil.Check)
 }
 
 // convertColumn converts a sqlspec.Column into a schema.Column.
@@ -196,6 +199,22 @@ func fixDefaultQuotes(value schemaspec.Value) error {
 	return nil
 }
 
+// convertIndex converts a sqlspec.Index into a schema.Index.
+func convertIndex(spec *sqlspec.Index, parent *schema.Table) (*schema.Index, error) {
+	idx, err := specutil.Index(spec, parent)
+	if err != nil {
+		return nil, err
+	}
+	if attr, ok := spec.Attr("type"); ok {
+		t, err := attr.String()
+		if err != nil {
+			return nil, err
+		}
+		idx.Attrs = append(idx.Attrs, &IndexType{T: t})
+	}
+	return idx, nil
+}
+
 const defaultTimePrecision = 6
 
 // convertColumnType converts a sqlspec.Column into a concrete Postgres schema.Type.
@@ -300,12 +319,23 @@ func tableSpec(tab *schema.Table) (*sqlspec.Table, error) {
 		tab,
 		columnSpec,
 		specutil.FromPrimaryKey,
-		specutil.FromIndex,
+		indexSpec,
 		specutil.FromForeignKey,
 		specutil.FromCheck,
 	)
 }
 
+func indexSpec(idx *schema.Index) (*sqlspec.Index, error) {
+	spec, err := specutil.FromIndex(idx)
+	if err != nil {
+		return nil, err
+	}
+	if i := (IndexType{}); sqlx.Has(idx.Attrs, &i) {
+		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.VarAttr("type", strings.ToUpper(i.T)))
+	}
+	return spec, nil
+}
+
 // columnSpec converts from a concrete Postgres schema.Column into a sqlspec.Column.
 func columnSpec(col *schema.Column, _ *schema.Table) (*sqlspec.Column, error) {
 	return specutil.FromColumn(col, columnTypeSpec)


Test patch:
diff --git a/internal/integration/script_test.go b/internal/integration/script_test.go
index 038344f2..7064048b 100644
--- a/internal/integration/script_test.go
+++ b/internal/integration/script_test.go
@@ -154,8 +154,16 @@ func (t *myTest) cmdCmpShow(ts *testscript.TestScript, _ bool, args []string) {
 		if err := t.db.QueryRow(fmt.Sprintf("SHOW CREATE TABLE `%s`.`%s`", schema, name)).Scan(&name, &create); err != nil {
 			return "", err
 		}
-		// Trim the "table_options" if it was not requested explicitly.
-		return create[:strings.LastIndexByte(create, ')')+1], nil
+		i := strings.LastIndexByte(create, ')')
+		create, opts := create[:i+1], strings.Fields(create[i+1:])
+		for _, opt := range opts {
+			switch strings.Split(opt, "=")[0] {
+			// Keep only options that are relevant for the tests.
+			case "AUTO_INCREMENT", "COMMENT":
+				create += " " + opt
+			}
+		}
+		return create, nil
 	})
 }
 
diff --git a/internal/integration/testdata/mysql/autoincrement.txt b/internal/integration/testdata/mysql/autoincrement.txt
new file mode 100644
index 00000000..8f1f7279
--- /dev/null
+++ b/internal/integration/testdata/mysql/autoincrement.txt
@@ -0,0 +1,93 @@
+apply 1.hcl
+cmpshow users 1.sql
+
+# Setup a custom AUTO_INCREMENT initial value.
+apply 2.hcl
+cmpshow users 2.sql
+
+# Increase the AUTO_INCREMENT value.
+apply 3.hcl
+cmpshow users 3.sql
+
+-- 1.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "id" {
+    null = false
+    type = bigint
+    auto_increment = true
+  }
+  primary_key  {
+    columns = [column.id]
+  }
+}
+
+-- 1.sql --
+CREATE TABLE `users` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+)
+
+-- 8/1.sql --
+CREATE TABLE `users` (
+  `id` bigint NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+)
+
+-- 2.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "id" {
+    null = false
+    type = bigint
+    auto_increment = true
+  }
+  primary_key  {
+    columns = [column.id]
+  }
+  auto_increment = 1000
+}
+
+-- 2.sql --
+CREATE TABLE `users` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) AUTO_INCREMENT=1000
+
+-- 8/2.sql --
+CREATE TABLE `users` (
+  `id` bigint NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) AUTO_INCREMENT=1000
+
+-- 3.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "id" {
+    null = false
+    type = bigint
+    auto_increment = true
+  }
+  primary_key  {
+    columns = [column.id]
+  }
+  auto_increment = 2000
+}
+
+-- 3.sql --
+CREATE TABLE `users` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) AUTO_INCREMENT=2000
+
+-- 8/3.sql --
+CREATE TABLE `users` (
+  `id` bigint NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) AUTO_INCREMENT=2000
\ No newline at end of file
diff --git a/internal/integration/testdata/mysql/index-type.txt b/internal/integration/testdata/mysql/index-type.txt
new file mode 100644
index 00000000..c18e6bf0
--- /dev/null
+++ b/internal/integration/testdata/mysql/index-type.txt
@@ -0,0 +1,44 @@
+apply 1.hcl
+cmpshow users 1.sql
+
+# Drop an index.
+apply 2.hcl
+cmpshow users 2.sql
+
+-- 1.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "text" {
+    null = false
+    type = text
+  }
+  index "users_text" {
+    type = FULLTEXT
+    columns = [column.text]
+  }
+}
+
+-- 1.sql --
+CREATE TABLE `users` (
+  `text` text NOT NULL,
+  FULLTEXT KEY `users_text` (`text`)
+)
+
+-- 2.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "text" {
+    null = false
+    type = text
+  }
+}
+
+-- 2.sql --
+CREATE TABLE `users` (
+  `text` text NOT NULL
+)
+
diff --git a/internal/integration/testdata/postgres/index-type.txt b/internal/integration/testdata/postgres/index-type.txt
new file mode 100644
index 00000000..ff036122
--- /dev/null
+++ b/internal/integration/testdata/postgres/index-type.txt
@@ -0,0 +1,73 @@
+apply 1.hcl
+cmpshow users 1.sql
+
+# Change index type.
+apply 2.hcl
+cmpshow users 2.sql
+
+-- 1.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "name" {
+    null = false
+    type = text
+  }
+  column "data" {
+    null = true
+    type = jsonb
+  }
+  index "users_name" {
+    type = HASH
+    columns = [column.name]
+  }
+  index "users_data" {
+    type = GIN
+    columns = [column.data]
+  }
+}
+
+-- 1.sql --
+         Table "script_index_type.users"
+ Column | Type  | Collation | Nullable | Default
+--------+-------+-----------+----------+---------
+ name   | text  |           | not null |
+ data   | jsonb |           |          |
+Indexes:
+    "users_data" gin (data)
+    "users_name" hash (name)
+
+-- 2.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "name" {
+    null = false
+    type = text
+  }
+  column "data" {
+    null = true
+    type = jsonb
+  }
+  index "users_name" {
+    columns = [column.name]
+    # Index without "using" defaults to BTREE.
+  }
+  index "users_data" {
+    type = BTREE
+    columns = [column.data]
+  }
+}
+
+-- 2.sql --
+         Table "script_index_type.users"
+ Column | Type  | Collation | Nullable | Default
+--------+-------+-----------+----------+---------
+ name   | text  |           | not null |
+ data   | jsonb |           |          |
+Indexes:
+    "users_data" btree (data)
+    "users_name" btree (name)
+
diff --git a/schema/schemaspec/schemahcl/hcl_test.go b/schema/schemaspec/schemahcl/hcl_test.go
index a086d6c9..9fd9ff39 100644
--- a/schema/schemaspec/schemahcl/hcl_test.go
+++ b/schema/schemaspec/schemahcl/hcl_test.go
@@ -84,6 +84,7 @@ func TestResource(t *testing.T) {
 func ExampleUnmarshal() {
 	f := `
 show "seinfeld" {
+	day = SUN
 	writer "jerry" {
 		full_name = "Jerry Seinfeld"	
 	}
@@ -99,19 +100,25 @@ show "seinfeld" {
 		}
 		Show struct {
 			Name    string    `spec:",name"`
+			Day     string    `spec:"day"`
 			Writers []*Writer `spec:"writer"`
 		}
 	)
-	var test struct {
-		Shows []*Show `spec:"show"`
-	}
-	err := Unmarshal([]byte(f), &test)
+	var (
+		test struct {
+			Shows []*Show `spec:"show"`
+		}
+		opts = []Option{
+			WithScopedEnums("show.day", "SUN", "MON", "TUE"),
+		}
+	)
+	err := New(opts...).UnmarshalSpec([]byte(f), &test)
 	if err != nil {
 		panic(err)
 	}
 	seinfeld := test.Shows[0]
-	fmt.Printf("the show %q has %d writers.", seinfeld.Name, len(seinfeld.Writers))
-	// Output: the show "seinfeld" has 2 writers.
+	fmt.Printf("the show %q at day %s has %d writers.", seinfeld.Name, seinfeld.Day, len(seinfeld.Writers))
+	// Output: the show "seinfeld" at day SUN has 2 writers.
 }
 
 func ExampleMarshal() {
diff --git a/sql/migrate/migrate_test.go b/sql/migrate/migrate_test.go
index 0dcdfc9f..6873c36b 100644
--- a/sql/migrate/migrate_test.go
+++ b/sql/migrate/migrate_test.go
@@ -5,180 +5,89 @@
 package migrate_test
 
 import (
-	"context"
-	"database/sql"
-	"io/fs"
-	"os"
-	"path/filepath"
+	"bytes"
+	"io"
+	"strconv"
 	"testing"
+	"text/template"
+	"time"
 
 	"ariga.io/atlas/sql/migrate"
-	"ariga.io/atlas/sql/schema"
-
 	"github.com/stretchr/testify/require"
 )
 
-func TestDir_Plan(t *testing.T) {
-	var (
-		m   = &mockDriver{}
-		ctx = context.Background()
-	)
-	dir, err := migrate.NewDir(
-		migrate.DirPath("testdata"),
-		migrate.DirGlob("*.up.sql"),
-		migrate.DirConn(m),
-	)
-	require.NoError(t, err)
-	plan, err := dir.Plan(ctx, "plan_name", migrate.Realm(nil))
-	require.Equal(t, migrate.ErrNoPlan, err)
-	require.Nil(t, plan)
-	require.Equal(t, []string{"CREATE TABLE t(c int);"}, m.executed)
-
-	m.executed = nil
-	dir, err = migrate.NewDir(
-		migrate.DirPath("testdata"),
-		migrate.DirGlob("*.sql"),
-		migrate.DirConn(m),
-	)
-	require.NoError(t, err)
-	plan, err = dir.Plan(ctx, "plan_name", migrate.Realm(nil))
-	require.Equal(t, migrate.ErrNoPlan, err)
-	require.Nil(t, plan)
-	require.Equal(t, []string{"DROP TABLE IF EXISTS t;", "CREATE TABLE t(c int);", "CREATE TABLE t(c int);"}, m.executed)
-
-	m.changes = append(m.changes, &schema.AddTable{T: &schema.Table{Name: "t1"}}, &schema.AddTable{T: &schema.Table{Name: "t2"}})
-	m.plan = &migrate.Plan{
-		Name: "add_t1_t2",
+func TestPlanner_WritePlan(t *testing.T) {
+	var mfs = &mockFS{}
+	plan := &migrate.Plan{
+		Name: "add_t1_and_t2",
 		Changes: []*migrate.Change{
-			{Cmd: "CREATE TABLE t1(c int);"},
-			{Cmd: "CREATE TABLE t2(c int);"},
+			{Cmd: "CREATE TABLE t1(c int);", Reverse: "DROP TABLE t1 IF EXISTS"},
+			{Cmd: "CREATE TABLE t2(c int)", Reverse: "DROP TABLE t2"},
 		},
 	}
-	plan, err = dir.Plan(ctx, "plan_name", migrate.Realm(nil))
-	require.NoError(t, err)
-	require.NotNil(t, plan)
-}
 
-func TestDir_WritePlan_Compact(t *testing.T) {
-	var (
-		f   = &mockFS{}
-		m   = &mockDriver{}
-		ctx = context.Background()
-	)
-	dir, err := migrate.NewDir(
-		migrate.DirFS(f),
-		migrate.DirConn(m),
+	// DefaultFormatter
+	pl := migrate.New(nil, mfs, migrate.DefaultFormatter)
+	require.NotNil(t, pl)
+	require.NoError(t, pl.WritePlan(plan))
+	v := strconv.FormatInt(time.Now().Unix(), 10)
+	require.Len(t, mfs.files, 2)
+	require.Equal(t, &file{
+		n: v + "_add_t1_and_t2.up.sql",
+		b: bytes.NewBufferString("CREATE TABLE t1(c int);\nCREATE TABLE t2(c int);\n"),
+	}, mfs.files[0])
+	require.Equal(t, &file{
+		n: v + "_add_t1_and_t2.down.sql",
+		b: bytes.NewBufferString("DROP TABLE t2;\nDROP TABLE t1 IF EXISTS;\n"),
+	}, mfs.files[1])
+
+	// Custom formatter (creates only "up" migration files).
+	fmt, err := migrate.NewTemplateFormatter(
+		template.Must(template.New("").Parse("{{.Name}}.sql")),
+		template.Must(template.New("").Parse("{{range .Changes}}{{println .Cmd}}{{end}}")),
 	)
 	require.NoError(t, err)
-	err = dir.WritePlan(&migrate.Plan{
-		Name: "add_t1_t2",
-		Changes: []*migrate.Change{
-			{Cmd: "CREATE TABLE t1 (c int)"},
-			{Cmd: "CREATE TABLE t2 (c int);"},
-		},
-	})
-	require.NoError(t, err)
-	require.Equal(t, "CREATE TABLE t1 (c int);\nCREATE TABLE t2 (c int);\n", f.files[0].F)
-
-	f.files = nil
-	dir, err = migrate.NewDir(
-		migrate.DirFS(f),
-		migrate.DirConn(m),
-		migrate.DirTemplates("{{.Name}}.sql", `{{range $c := .Changes}}{{printf "--%s\n%s;\n" $c.Comment $c.Cmd}}{{end}}`),
-	)
-	require.NoError(t, err)
-	err = dir.WritePlan(&migrate.Plan{
-		Name: "add_t1_t2",
-		Changes: []*migrate.Change{
-			{Cmd: "CREATE TABLE t1 (c int)", Comment: "Create a new table named t1."},
-			{Cmd: "CREATE TABLE t2 (c int)", Comment: "Create a new table named t2."},
-		},
-	})
-	require.NoError(t, err)
-	require.Equal(t, "add_t1_t2.sql", f.files[0].N)
-	require.Equal(t, "--Create a new table named t1.\nCREATE TABLE t1 (c int);\n--Create a new table named t2.\nCREATE TABLE t2 (c int);\n", f.files[0].F)
-
-	err = dir.WritePlan(&migrate.Plan{
-		Name: "add_t3",
-		Changes: []*migrate.Change{
-			{Cmd: "CREATE TABLE t3 (c int)", Comment: "Create a new table named t3."},
-		},
-	})
-	require.NoError(t, err)
-	require.Equal(t, "add_t3.sql", f.files[1].N)
-	require.Equal(t, "--Create a new table named t3.\nCREATE TABLE t3 (c int);\n", f.files[1].F)
-
-	m.changes = append(m.changes, &schema.AddTable{T: &schema.Table{Name: "t1"}}, &schema.AddTable{T: &schema.Table{Name: "t2"}})
-	m.plan = &migrate.Plan{Name: "schema", Changes: []*migrate.Change{{Cmd: "CREATE TABLE t1 (c int)"}, {Cmd: "CREATE TABLE t2 (c int)"}, {Cmd: "CREATE TABLE t3 (c int)"}}}
-	err = dir.Compact(ctx, "schema", -1)
-	require.NoError(t, err)
-	require.Len(t, f.files, 1)
-	require.Equal(t, "schema.sql", f.files[0].N)
-	require.Equal(t, "--\nCREATE TABLE t1 (c int);\n--\nCREATE TABLE t2 (c int);\n--\nCREATE TABLE t3 (c int);\n", f.files[0].F)
-
-}
-
-type mockFS struct {
-	fs.GlobFS
-	files []struct{ N, F string }
+	pl = migrate.New(nil, mfs, fmt)
+	require.NotNil(t, pl)
+	require.NoError(t, pl.WritePlan(plan))
+	require.Len(t, mfs.files, 3)
+	require.Equal(t, &file{
+		n: "add_t1_and_t2.sql",
+		b: bytes.NewBufferString("CREATE TABLE t1(c int);\nCREATE TABLE t2(c int)\n"),
+	}, mfs.files[2])
 }
 
-func (f *mockFS) Glob(pattern string) ([]string, error) {
-	var matches []string
-	for i := range f.files {
-		match, err := filepath.Match(pattern, f.files[i].N)
-		if err != nil {
-			return nil, err
-		}
-		if match {
-			matches = append(matches, f.files[i].N)
-		}
+type (
+	mockFS struct {
+		files []*file
 	}
-	return matches, nil
-}
-
-func (f *mockFS) ReadFile(name string) ([]byte, error) {
-	for i := range f.files {
-		if f.files[i].N == name {
-			return []byte(f.files[i].F), nil
-		}
+	file struct {
+		n string
+		b *bytes.Buffer
 	}
-	return nil, os.ErrNotExist
-}
+)
 
-func (f *mockFS) WriteFile(name string, data []byte, _ fs.FileMode) error {
-	f.files = append(f.files, struct{ N, F string }{N: name, F: string(data)})
-	return nil
+func (f *file) Read(b []byte) (int, error) {
+	return f.b.Read(b)
 }
 
-func (f *mockFS) RemoveFile(name string) error {
-	for i := range f.files {
-		if f.files[i].N == name {
-			f.files = append(f.files[:i], f.files[i+1:]...)
-			return nil
-		}
-	}
-	return os.ErrNotExist
+func (f *file) Write(b []byte) (int, error) {
+	return f.b.Write(b)
 }
 
-type mockDriver struct {
-	migrate.Driver
-	plan     *migrate.Plan
-	changes  []schema.Change
-	executed []string
-}
+func (f *file) Close() error { return nil }
 
-func (m *mockDriver) ExecContext(_ context.Context, query string, _ ...interface{}) (sql.Result, error) {
-	m.executed = append(m.executed, query)
-	return nil, nil
-}
+func (f *file) Name() string { return f.n }
 
-func (m mockDriver) InspectRealm(context.Context, *schema.InspectRealmOption) (*schema.Realm, error) {
-	return &schema.Realm{}, nil
-}
-func (m mockDriver) RealmDiff(_, _ *schema.Realm) ([]schema.Change, error) {
-	return m.changes, nil
-}
-func (m mockDriver) PlanChanges(context.Context, string, []schema.Change) (*migrate.Plan, error) {
-	return m.plan, nil
+func (fs *mockFS) Open(name string) (io.ReadWriteCloser, error) {
+	for _, f := range fs.files {
+		if f.n == name {
+			return f, nil
+		}
+	}
+	f := &file{n: name, b: new(bytes.Buffer)}
+	fs.files = append(fs.files, f)
+	return f, nil
 }
+
+var _ io.ReadWriteCloser = (*file)(nil)
diff --git a/sql/mysql/migrate_test.go b/sql/mysql/migrate_test.go
index a33778dd..b24db365 100644
--- a/sql/mysql/migrate_test.go
+++ b/sql/mysql/migrate_test.go
@@ -441,6 +441,7 @@ func TestPlanChanges(t *testing.T) {
 									},
 									Attrs: []schema.Attr{
 										&schema.Comment{Text: "comment"},
+										&IndexType{T: IndexTypeHash},
 									},
 								},
 							},
@@ -463,7 +464,7 @@ func TestPlanChanges(t *testing.T) {
 				Reversible: true,
 				Changes: []*migrate.Change{
 					{
-						Cmd:     "ALTER TABLE `users` ADD COLUMN `name` varchar(255) NOT NULL, ADD INDEX `id_key` (`id`) COMMENT \"comment\", ADD CONSTRAINT `id_nonzero` CHECK (id > 0) ENFORCED, AUTO_INCREMENT 1000",
+						Cmd:     "ALTER TABLE `users` ADD COLUMN `name` varchar(255) NOT NULL, ADD INDEX `id_key` USING HASH (`id`) COMMENT \"comment\", ADD CONSTRAINT `id_nonzero` CHECK (id > 0) ENFORCED, AUTO_INCREMENT 1000",
 						Reverse: "ALTER TABLE `users` DROP COLUMN `name`, DROP INDEX `id_key`, DROP CONSTRAINT `id_nonzero`, AUTO_INCREMENT 1",
 					},
 				},
diff --git a/sql/mysql/sqlspec_test.go b/sql/mysql/sqlspec_test.go
index fe6cff41..8e9d9b8a 100644
--- a/sql/mysql/sqlspec_test.go
+++ b/sql/mysql/sqlspec_test.go
@@ -23,9 +23,11 @@ table "table" {
 	}
 	column "price1" {
 		type = int
+		auto_increment = false
 	}
 	column "price2" {
 		type = int
+		auto_increment = true
 	}
 	column "account_name" {
 		type = varchar(32)
@@ -71,6 +73,7 @@ table "table" {
 		enforced = false
 	}
 	comment = "table comment"
+	auto_increment = 1000
 }
 
 table "accounts" {
@@ -136,6 +139,7 @@ table "accounts" {
 							T: TypeInt,
 						},
 					},
+					Attrs: []schema.Attr{&AutoIncrement{}},
 				},
 				{
 					Name: "account_name",
@@ -182,6 +186,7 @@ table "accounts" {
 					Attrs: []schema.Attr{&Enforced{V: false}},
 				},
 				&schema.Comment{Text: "table comment"},
+				&AutoIncrement{V: 1000},
 			},
 		},
 		{
diff --git a/sql/postgres/sqlspec_test.go b/sql/postgres/sqlspec_test.go
index 8187c596..d31538e7 100644
--- a/sql/postgres/sqlspec_test.go
+++ b/sql/postgres/sqlspec_test.go
@@ -50,6 +50,7 @@ table "table" {
 		columns = [table.table.column.col]
 	}
 	index "index" {
+		type = HASH
 		unique = true
 		columns = [
 			table.table.column.col,
@@ -232,6 +233,7 @@ enum "account_type" {
 			},
 			Attrs: []schema.Attr{
 				&schema.Comment{Text: "index comment"},
+				&IndexType{T: IndexTypeHash},
 			},
 		},
 	}
@@ -254,6 +256,37 @@ enum "account_type" {
 	require.EqualValues(t, exp, &s)
 }
 
+func TestUnmarshalSpec_IndexType(t *testing.T) {
+	f := `
+schema "s" {}
+table "t" {
+	schema = schema.s
+	column "c" {
+		type = int
+	}
+	index "i" {
+		type = %s
+		columns = [column.c]
+	}
+}
+`
+	t.Run("Invalid", func(t *testing.T) {
+		f := fmt.Sprintf(f, "UNK")
+		err := UnmarshalHCL([]byte(f), &schema.Schema{})
+		require.Error(t, err)
+	})
+	t.Run("Valid", func(t *testing.T) {
+		var (
+			s schema.Schema
+			f = fmt.Sprintf(f, "HASH")
+		)
+		err := UnmarshalHCL([]byte(f), &s)
+		require.NoError(t, err)
+		idx := s.Tables[0].Indexes[0]
+		require.Equal(t, IndexTypeHash, idx.Attrs[0].(*IndexType).T)
+	})
+}
+
 func TestMarshalSpec_Enum(t *testing.T) {
 	s := schema.New("test").
 		AddTables(