Repository: ariga/atlas
Pull request #568 (2022-02-11 15:00:22)

sql/migrate: add GlobStateReader
~~Will be rebased once #566 is merged.~~


Patch:
diff --git a/doc/md/getting-started/01-introduction.mdx b/doc/md/getting-started/01-introduction.mdx
index ebab8392..92d368ce 100644
--- a/doc/md/getting-started/01-introduction.mdx
+++ b/doc/md/getting-started/01-introduction.mdx
@@ -69,6 +69,10 @@ move the atlas binary to a file location on your system PATH.
 </TabItem>
 </Tabs>
 
+:::info
+Latest release is updated twice a day and is based on the most recent tagged release.
+:::
+
 Starting with release [v0.3.0](https://github.com/ariga/atlas/releases/tag/v0.3.0),
 ֿthe distributed binaries include code for a [Management UI](../ui/intro.md) wrapping the
 core Atlas engine that is not currently released publicly. The binaries
diff --git a/doc/md/getting-started/04-ui.md b/doc/md/getting-started/04-ui.md
new file mode 100644
index 00000000..cdd45b46
--- /dev/null
+++ b/doc/md/getting-started/04-ui.md
@@ -0,0 +1,116 @@
+---
+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/doc/website/sidebars.js b/doc/website/sidebars.js
index 26329e9f..d183f371 100644
--- a/doc/website/sidebars.js
+++ b/doc/website/sidebars.js
@@ -18,7 +18,8 @@ module.exports = {
             items: [
                 'getting-started/getting-started-installation',
                 'getting-started/getting-started-inspection',
-                'getting-started/getting-started-apply'
+                'getting-started/getting-started-apply',
+                'getting-started/getting-started-management-ui',
             ]
         },
         {
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/migrate/migrate.go b/sql/migrate/migrate.go
index 366254bb..53f1204e 100644
--- a/sql/migrate/migrate.go
+++ b/sql/migrate/migrate.go
@@ -10,8 +10,10 @@ import (
 	"errors"
 	"fmt"
 	"io"
+	"io/fs"
 	"os"
 	"path/filepath"
+	"sort"
 	"strings"
 	"text/template"
 	"time"
@@ -128,20 +130,22 @@ func Schema(s *schema.Schema) StateReader {
 type (
 	// Dir describes the methods needed to work with the Planner.
 	Dir interface {
-		// Open opens the named file.
-		Open(name string) (io.ReadWriteCloser, error)
+		fs.FS
+
+		// WriteFile writes the data to the named file.
+		WriteFile(string, []byte) error
 	}
 
-	// Formatter wraps the Format method for naming and dumping a migration plan to one or more Files.
+	// Formatter wraps the Format method.
 	Formatter interface {
-		// Format formats the given Plan.
-		// The returned map contains the contents for each file to dump.
+		// Format formats the given Plan into one or more migration files.
 		Format(*Plan) ([]File, error)
 	}
 
-	// File represents a migration file.
+	// File represents a single migration file.
 	File interface {
 		io.Reader
+		// Name returns the name of the FormattedFile.
 		Name() string
 	}
 
@@ -184,20 +188,18 @@ func (p *Planner) Plan(ctx context.Context, name string, from StateReader, to St
 	return p.drv.PlanChanges(ctx, name, changes)
 }
 
-// WritePlan writes the given plan to the Dir based on the given Formatter.
+// WritePlan writes the given Plan to the Dir based on the configured Formatter.
 func (p *Planner) WritePlan(plan *Plan) error {
-	fs, err := p.fmt.Format(plan)
+	files, err := p.fmt.Format(plan)
 	if err != nil {
 		return err
 	}
-	for _, f := range fs {
-		fh, err := p.dir.Open(f.Name())
+	for _, f := range files {
+		d, err := io.ReadAll(f)
 		if err != nil {
 			return err
 		}
-		_, err = io.Copy(fh, f)
-		fh.Close()
-		if err != nil {
+		if err := p.dir.WriteFile(f.Name(), d); err != nil {
 			return err
 		}
 	}
@@ -209,11 +211,6 @@ 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)
@@ -226,9 +223,44 @@ func NewLocalDir(path string) (*LocalDir, error) {
 	return &LocalDir{dir: path}, nil
 }
 
-// TemplateFormatter implements Formatter by using templates.
-type TemplateFormatter struct {
-	templates []struct{ N, C *template.Template }
+// GlobStateReader creates a StateReader that loads all files from Dir matching
+// glob in lexicographic order and uses the Driver to create a migration state.
+func (d *LocalDir) GlobStateReader(drv Driver, glob string) StateReaderFunc {
+	return func(ctx context.Context) (*schema.Realm, error) {
+		names, err := fs.Glob(d, glob)
+		if err != nil {
+			return nil, err
+		}
+		// Sort files lexicographically.
+		sort.Slice(names, func(i, j int) bool {
+			return names[i] < names[j]
+		})
+		for _, n := range names {
+			f, err := d.Open(n)
+			if err != nil {
+				return nil, err
+			}
+			b, err := io.ReadAll(f)
+			f.Close()
+			if err != nil {
+				return nil, err
+			}
+			if _, err := drv.ExecContext(ctx, string(b)); err != nil {
+				return nil, err
+			}
+		}
+		return drv.InspectRealm(ctx, nil)
+	}
+}
+
+// Open implements fs.FS.
+func (d *LocalDir) Open(name string) (fs.File, error) {
+	return os.Open(filepath.Join(d.dir, name))
+}
+
+// WriteFile implements Dir.WriteFile.
+func (d *LocalDir) WriteFile(name string, b []byte) error {
+	return os.WriteFile(name, b, 0644)
 }
 
 var (
@@ -261,6 +293,11 @@ var (
 	}
 )
 
+// TemplateFormatter implements Formatter by using templates.
+type TemplateFormatter struct {
+	templates []struct{ N, C *template.Template }
+}
+
 // NewTemplateFormatter creates a new Formatter working with the given templates.
 //
 //	migrate.NewTemplateFormatter(
diff --git a/sql/mysql/diff.go b/sql/mysql/diff.go
index b140727a..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,
diff --git a/sql/mysql/sqlspec.go b/sql/mysql/sqlspec.go
index bcdafacc..cc15d6f6 100644
--- a/sql/mysql/sqlspec.go
+++ b/sql/mysql/sqlspec.go
@@ -89,6 +89,15 @@ func convertTable(spec *sqlspec.Table, parent *schema.Schema) (*schema.Table, er
 	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
 }
 
@@ -140,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
 }
 
@@ -211,6 +229,9 @@ func columnSpec(c *schema.Column, t *schema.Table) (*sqlspec.Column, error) {
 	if o := (OnUpdate{}); sqlx.Has(c.Attrs, &o) {
 		col.Extra.Attrs = append(col.Extra.Attrs, specutil.RawAttr("on_update", o.A))
 	}
+	if sqlx.Has(c.Attrs, &AutoIncrement{}) {
+		col.Extra.Attrs = append(col.Extra.Attrs, specutil.BoolAttr("auto_increment", true))
+	}
 	return col, nil
 }
 
diff --git a/sql/sqlite/sqlspec.go b/sql/sqlite/sqlspec.go
index 0a3fa3a2..8e364ff2 100644
--- a/sql/sqlite/sqlspec.go
+++ b/sql/sqlite/sqlspec.go
@@ -6,6 +6,7 @@ import (
 	"ariga.io/atlas/schema/schemaspec"
 	"ariga.io/atlas/schema/schemaspec/schemahcl"
 	"ariga.io/atlas/sql/internal/specutil"
+	"ariga.io/atlas/sql/internal/sqlx"
 	"ariga.io/atlas/sql/schema"
 	"ariga.io/atlas/sql/sqlspec"
 )
@@ -29,7 +30,20 @@ func convertTable(spec *sqlspec.Table, parent *schema.Schema) (*schema.Table, er
 
 // convertColumn converts a sqlspec.Column into a schema.Column.
 func convertColumn(spec *sqlspec.Column, _ *schema.Table) (*schema.Column, error) {
-	return specutil.Column(spec, convertColumnType)
+	c, err := specutil.Column(spec, convertColumnType)
+	if err != nil {
+		return nil, err
+	}
+	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, nil
 }
 
 // convertColumnType converts a sqlspec.Column into a concrete SQLite schema.Type.
@@ -55,8 +69,15 @@ func tableSpec(tab *schema.Table) (*sqlspec.Table, error) {
 }
 
 // columnSpec converts from a concrete SQLite schema.Column into a sqlspec.Column.
-func columnSpec(col *schema.Column, _ *schema.Table) (*sqlspec.Column, error) {
-	return specutil.FromColumn(col, columnTypeSpec)
+func columnSpec(c *schema.Column, _ *schema.Table) (*sqlspec.Column, error) {
+	s, err := specutil.FromColumn(c, columnTypeSpec)
+	if err != nil {
+		return nil, err
+	}
+	if sqlx.Has(c.Attrs, &AutoIncrement{}) {
+		s.Extra.Attrs = append(s.Extra.Attrs, specutil.BoolAttr("auto_increment", true))
+	}
+	return s, nil
 }
 
 // columnTypeSpec converts from a concrete MySQL schema.Type into sqlspec.Column Type.


Test patch:
diff --git a/cmd/action/env_test.go b/cmd/action/env_test.go
new file mode 100644
index 00000000..4a138a05
--- /dev/null
+++ b/cmd/action/env_test.go
@@ -0,0 +1,26 @@
+package action
+
+import (
+	"os"
+	"testing"
+
+	"ariga.io/atlas/cmd/action/internal/update"
+
+	"github.com/stretchr/testify/require"
+)
+
+func TestEnv(t *testing.T) {
+	cmd := newEnvCmd()
+	out, err := runCmd(cmd)
+	require.NoError(t, err)
+	require.Empty(t, out)
+}
+
+func TestEnv_Set(t *testing.T) {
+	cmd := newEnvCmd()
+	err := os.Setenv(update.AtlasNoUpdateNotifier, "test")
+	require.NoError(t, err)
+	out, err := runCmd(cmd)
+	require.NoError(t, err)
+	require.Equal(t, "ATLAS_NO_UPDATE_NOTIFIER=test\n", out)
+}
diff --git a/internal/integration/script_test.go b/internal/integration/script_test.go
index 038344f2..8527dbf3 100644
--- a/internal/integration/script_test.go
+++ b/internal/integration/script_test.go
@@ -113,19 +113,24 @@ func setupScript(t *testing.T, env *testscript.Env, db *sql.DB, dropCmd string)
 	return nil
 }
 
+var (
+	keyDB  *sql.DB
+	keyDrv *sqlite.Driver
+)
+
 func (t *liteTest) setupScript(env *testscript.Env) error {
-	db, err := sql.Open("sqlite3", "file:atlas?mode=memory&cache=shared&_fk=1")
+	db, err := sql.Open("sqlite3", fmt.Sprintf("file:%s?mode=memory&cache=shared&_fk=1", filepath.Base(env.WorkDir)))
 	require.NoError(t, err)
-	t.db = db
-	env.Setenv("db", "main")
 	env.Defer(func() {
 		require.NoError(t, db.Close())
 	})
-	t.Cleanup(func() {
-		require.NoError(t, db.Close())
-	})
-	t.drv, err = sqlite.Open(db)
+	drv, err := sqlite.Open(db)
 	require.NoError(t, err)
+	env.Setenv("db", "main")
+	// Attach connection and driver to the
+	// environment as tests run in parallel.
+	env.Values[keyDB] = db
+	env.Values[keyDrv] = drv
 	return nil
 }
 
@@ -154,8 +159,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
 	})
 }
 
@@ -181,8 +194,11 @@ func (t *pgTest) cmdCmpShow(ts *testscript.TestScript, _ bool, args []string) {
 
 func (t *liteTest) cmdCmpShow(ts *testscript.TestScript, _ bool, args []string) {
 	cmdCmpShow(ts, args, func(_, name string) (string, error) {
-		var stmts []string
-		rows, err := t.db.Query("SELECT sql FROM sqlite_schema where tbl_name = ?", name)
+		var (
+			stmts []string
+			db    = ts.Value(keyDB).(*sql.DB)
+		)
+		rows, err := db.Query("SELECT sql FROM sqlite_schema where tbl_name = ?", name)
 		if err != nil {
 			return "", fmt.Errorf("querying schema")
 		}
@@ -250,8 +266,11 @@ func (t *pgTest) cmdExist(ts *testscript.TestScript, neg bool, args []string) {
 
 func (t *liteTest) cmdExist(ts *testscript.TestScript, neg bool, args []string) {
 	cmdExist(ts, neg, args, func(_, name string) (bool, error) {
-		var b bool
-		if err := t.db.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE `type`='table' AND `name` = ?", name).Scan(&b); err != nil {
+		var (
+			b  bool
+			db = ts.Value(keyDB).(*sql.DB)
+		)
+		if err := db.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE `type`='table' AND `name` = ?", name).Scan(&b); err != nil {
 			return false, err
 		}
 		return b, nil
@@ -318,18 +337,19 @@ func (t *liteTest) cmdSynced(ts *testscript.TestScript, neg bool, args []string)
 }
 
 func (t *liteTest) cmdApply(ts *testscript.TestScript, neg bool, args []string) {
-	cmdApply(ts, neg, args, t.drv.ApplyChanges, t.hclDiff)
+	cmdApply(ts, neg, args, ts.Value(keyDrv).(*sqlite.Driver).ApplyChanges, t.hclDiff)
 }
 
 func (t *liteTest) hclDiff(ts *testscript.TestScript, name string) []schema.Change {
 	var (
 		desired schema.Schema
 		f       = ts.ReadFile(name)
+		drv     = ts.Value(keyDrv).(*sqlite.Driver)
 	)
 	ts.Check(sqlite.UnmarshalHCL([]byte(f), &desired))
-	current, err := t.drv.InspectSchema(context.Background(), desired.Name, nil)
+	current, err := drv.InspectSchema(context.Background(), desired.Name, nil)
 	ts.Check(err)
-	changes, err := t.drv.SchemaDiff(current, &desired)
+	changes, err := drv.SchemaDiff(current, &desired)
 	ts.Check(err)
 	return changes
 }
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/sqlite/autoincrement.txt b/internal/integration/testdata/sqlite/autoincrement.txt
new file mode 100644
index 00000000..3a616ba6
--- /dev/null
+++ b/internal/integration/testdata/sqlite/autoincrement.txt
@@ -0,0 +1,20 @@
+apply 1.hcl
+cmpshow users 1.sql
+
+-- 1.hcl --
+schema "main" {}
+
+table "users" {
+  schema = schema.main
+  column "id" {
+    null = false
+    type = integer
+    auto_increment = true
+  }
+  primary_key  {
+    columns = [column.id]
+  }
+}
+
+-- 1.sql --
+CREATE TABLE `users` (`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT)
\ No newline at end of file
diff --git a/sql/migrate/migrate_test.go b/sql/migrate/migrate_test.go
index 6873c36b..31b6455e 100644
--- a/sql/migrate/migrate_test.go
+++ b/sql/migrate/migrate_test.go
@@ -6,18 +6,22 @@ package migrate_test
 
 import (
 	"bytes"
-	"io"
+	"context"
+	"database/sql"
+	"io/fs"
 	"strconv"
 	"testing"
 	"text/template"
 	"time"
 
 	"ariga.io/atlas/sql/migrate"
+	"ariga.io/atlas/sql/schema"
+
 	"github.com/stretchr/testify/require"
 )
 
 func TestPlanner_WritePlan(t *testing.T) {
-	var mfs = &mockFS{}
+	mfs := &mockFS{}
 	plan := &migrate.Plan{
 		Name: "add_t1_and_t2",
 		Changes: []*migrate.Change{
@@ -57,6 +61,52 @@ func TestPlanner_WritePlan(t *testing.T) {
 	}, mfs.files[2])
 }
 
+func TestPlanner_Plan(t *testing.T) {
+	var (
+		mfs = &mockFS{}
+		drv = &mockDriver{}
+		ctx = context.Background()
+	)
+
+	// nothing to do
+	pl := migrate.New(drv, mfs, migrate.DefaultFormatter)
+	plan, err := pl.Plan(ctx, "empty", migrate.Realm(nil), migrate.Realm(nil))
+	require.ErrorIs(t, err, migrate.ErrNoPlan)
+	require.Nil(t, plan)
+
+	// there are changes
+	drv.changes = []schema.Change{
+		&schema.AddTable{T: schema.NewTable("t1").AddColumns(schema.NewIntColumn("c", "int"))},
+		&schema.AddTable{T: schema.NewTable("t2").AddColumns(schema.NewIntColumn("c", "int"))},
+	}
+	drv.plan = &migrate.Plan{
+		Changes: []*migrate.Change{
+			{Cmd: "CREATE TABLE t1(c int);"},
+			{Cmd: "CREATE TABLE t2(c int);"},
+		},
+	}
+	plan, err = pl.Plan(ctx, "", migrate.Realm(nil), migrate.Realm(nil))
+	require.NoError(t, err)
+	require.Equal(t, drv.plan, plan)
+}
+
+func TestGlobStateReader(t *testing.T) {
+	var (
+		drv = &mockDriver{}
+		ctx = context.Background()
+	)
+	localFS, err := migrate.NewLocalDir("testdata")
+	require.NoError(t, err)
+
+	_, err = localFS.GlobStateReader(drv, "*.up.sql").ReadState(ctx)
+	require.NoError(t, err)
+	require.Equal(t, drv.executed, []string{"CREATE TABLE t(c int);"})
+
+	_, err = localFS.GlobStateReader(drv, "*.down.sql").ReadState(ctx)
+	require.NoError(t, err)
+	require.Equal(t, drv.executed, []string{"CREATE TABLE t(c int);", "DROP TABLE IF EXISTS t;"})
+}
+
 type (
 	mockFS struct {
 		files []*file
@@ -65,21 +115,23 @@ type (
 		n string
 		b *bytes.Buffer
 	}
+	fileInfo struct {
+		n string
+	}
 )
 
-func (f *file) Read(b []byte) (int, error) {
-	return f.b.Read(b)
-}
+func (f *file) Read(b []byte) (int, error) { return f.b.Read(b) }
+func (f *file) Close() error               { return nil }
+func (f *file) Stat() (fs.FileInfo, error) { return fileInfo{f.n}, nil }
 
-func (f *file) Write(b []byte) (int, error) {
-	return f.b.Write(b)
-}
-
-func (f *file) Close() error { return nil }
+func (i fileInfo) Name() string       { return i.n }
+func (i fileInfo) Size() int64        { return 0 }
+func (i fileInfo) Mode() fs.FileMode  { return 0 }
+func (i fileInfo) ModTime() time.Time { return time.Time{} }
+func (i fileInfo) IsDir() bool        { return false }
+func (i fileInfo) Sys() interface{}   { return nil }
 
-func (f *file) Name() string { return f.n }
-
-func (fs *mockFS) Open(name string) (io.ReadWriteCloser, error) {
+func (fs *mockFS) Open(name string) (fs.File, error) {
 	for _, f := range fs.files {
 		if f.n == name {
 			return f, nil
@@ -90,4 +142,29 @@ func (fs *mockFS) Open(name string) (io.ReadWriteCloser, error) {
 	return f, nil
 }
 
-var _ io.ReadWriteCloser = (*file)(nil)
+func (fs *mockFS) WriteFile(name string, d []byte) error {
+	fs.files = append(fs.files, &file{n: name, b: bytes.NewBuffer(d)})
+	return nil
+}
+
+type mockDriver struct {
+	migrate.Driver
+	plan     *migrate.Plan
+	changes  []schema.Change
+	executed []string
+}
+
+func (m *mockDriver) ExecContext(_ context.Context, query string, _ ...interface{}) (sql.Result, error) {
+	m.executed = append(m.executed, query)
+	return nil, nil
+}
+
+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
+}
diff --git a/sql/migrate/testdata/1.sql b/sql/migrate/testdata/1.sql
deleted file mode 100644
index 5a3f49dc..00000000
--- a/sql/migrate/testdata/1.sql
+++ /dev/null
@@ -1,1 +0,0 @@
-CREATE TABLE t(c int);
\ No newline at end of file
diff --git a/sql/mysql/sqlspec_test.go b/sql/mysql/sqlspec_test.go
index fe6cff41..90e4dc61 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},
 			},
 		},
 		{
@@ -461,6 +466,41 @@ schema "test" {
 	require.EqualValues(t, expected, string(buf))
 }
 
+func TestMarshalSpec_AutoIncrement(t *testing.T) {
+	s := &schema.Schema{
+		Name: "test",
+		Tables: []*schema.Table{
+			{
+				Name: "users",
+				Columns: []*schema.Column{
+					{
+						Name: "id",
+						Type: &schema.ColumnType{Type: &schema.IntegerType{T: "bigint"}},
+						Attrs: []schema.Attr{
+							&AutoIncrement{},
+						},
+					},
+				},
+			},
+		},
+	}
+	s.Tables[0].Schema = s
+	buf, err := MarshalSpec(s, hclState)
+	require.NoError(t, err)
+	const expected = `table "users" {
+  schema = schema.test
+  column "id" {
+    null           = false
+    type           = bigint
+    auto_increment = true
+  }
+}
+schema "test" {
+}
+`
+	require.EqualValues(t, expected, string(buf))
+}
+
 func TestMarshalSpec_Check(t *testing.T) {
 	s := schema.New("test").
 		AddTables(
diff --git a/sql/sqlite/sqlspec_test.go b/sql/sqlite/sqlspec_test.go
index a581a400..045ff6ca 100644
--- a/sql/sqlite/sqlspec_test.go
+++ b/sql/sqlite/sqlspec_test.go
@@ -19,8 +19,9 @@ schema "schema" {
 }
 
 table "table" {
-	column "col" {
+	column "id" {
 		type = integer
+		auto_increment = true
 	}
 	column "age" {
 		type = integer
@@ -32,12 +33,12 @@ table "table" {
 		type = varchar(32)
 	}
 	primary_key {
-		columns = [table.table.column.col]
+		columns = [table.table.column.id]
 	}
 	index "index" {
 		unique = true
 		columns = [
-			table.table.column.col,
+			table.table.column.id,
 			table.table.column.age,
 		]
 	}
@@ -73,12 +74,15 @@ table "accounts" {
 			Schema: exp,
 			Columns: []*schema.Column{
 				{
-					Name: "col",
+					Name: "id",
 					Type: &schema.ColumnType{
 						Type: &schema.IntegerType{
 							T: "integer",
 						},
 					},
+					Attrs: []schema.Attr{
+						&AutoIncrement{},
+					},
 				},
 				{
 					Name: "age",
@@ -169,6 +173,41 @@ table "accounts" {
 	require.EqualValues(t, exp, &s)
 }
 
+func TestMarshalSpec_AutoIncrement(t *testing.T) {
+	s := &schema.Schema{
+		Name: "test",
+		Tables: []*schema.Table{
+			{
+				Name: "users",
+				Columns: []*schema.Column{
+					{
+						Name: "id",
+						Type: &schema.ColumnType{Type: &schema.IntegerType{T: "int"}},
+						Attrs: []schema.Attr{
+							&AutoIncrement{},
+						},
+					},
+				},
+			},
+		},
+	}
+	s.Tables[0].Schema = s
+	buf, err := MarshalSpec(s, hclState)
+	require.NoError(t, err)
+	const expected = `table "users" {
+  schema = schema.test
+  column "id" {
+    null           = false
+    type           = int
+    auto_increment = true
+  }
+}
+schema "test" {
+}
+`
+	require.EqualValues(t, expected, string(buf))
+}
+
 func TestTypes(t *testing.T) {
 	for _, tt := range []struct {
 		typeExpr string