Repository: ariga/atlas
Pull request #1000 (2022-08-02 21:03:01)

doc/md: declarative migration workflow docs


Patch:
diff --git a/.github/workflows/ci.yml b/.github/workflows/ci.yml
index 24d0c47e..07367ce6 100644
--- a/.github/workflows/ci.yml
+++ b/.github/workflows/ci.yml
@@ -14,7 +14,7 @@ jobs:
     steps:
     - uses: actions/setup-go@v2
       with:
-        go-version: 1.17
+        go-version: 1.18
     - uses: actions/checkout@v2
     - name: Run linters
       uses: golangci/golangci-lint-action@v3
@@ -51,7 +51,7 @@ jobs:
     runs-on: ubuntu-latest
     strategy:
       matrix:
-        go: [ '1.17', '1.18' ]
+        go: [ '1.18', '1.19' ]
     steps:
       - uses: actions/checkout@v2
       - uses: actions/setup-go@v2
@@ -74,7 +74,7 @@ jobs:
     runs-on: ubuntu-latest
     strategy:
       matrix:
-        go: [ '1.18' ]
+        go: [ '1.19' ]
     steps:
       - uses: actions/checkout@v2
       - uses: actions/setup-go@v2
@@ -96,7 +96,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -128,7 +128,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -159,7 +159,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -190,7 +190,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -221,7 +221,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -252,7 +252,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -283,7 +283,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -313,7 +313,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -343,7 +343,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -373,7 +373,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -403,7 +403,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -433,7 +433,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -457,7 +457,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -481,7 +481,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -499,7 +499,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -523,7 +523,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
diff --git a/doc/md/declarative/apply.md b/doc/md/declarative/apply.md
new file mode 100644
index 00000000..993bc0d4
--- /dev/null
+++ b/doc/md/declarative/apply.md
@@ -0,0 +1,44 @@
+---
+id: apply
+slug: /declarative/apply
+title: Declarative schema migrations
+---
+
+With Atlas, users do not need to plan database schema changes themselves. Instead
+of figuring out the correct SQL statements to get their database to the desired state,
+Atlas supports a kind of workflow that we call _declarative schema migration_.
+With declarative schema migrations the user provides a connection string to the target database
+and the desired schema and Atlas does all of the planning.
+
+[Read more about declarative workflows](/concepts/declarative-vs-versioned)
+
+### Auto-approval
+Before executing the migration against the target database, Atlas will print the SQL
+statements that it is going to run and prompt the user for approval. Users that wish
+to automatically approve may run the `schema apply` command with the `--auto-approve`
+flag.
+
+### Dry-runs
+In order to skip the execution of the SQL queries against the target database,
+users may provide the `--dry-run` flag. When invoked with this flag, Atlas will
+connect to the target database, inspect its current state, calculate the diff
+between the provided desired schema and print out a series of SQL statements to
+reconcile any gaps between the inspected and desired schemas.
+
+### Dev-database
+When storing schema definitions, many database engines perform some form of
+normalization. That is, despite us providing a specific definition of some
+aspect of the schema, the database will store it in another, equivalent form.
+This means in certain situations it may appear to Atlas as if some diff exists
+between the desired and inspected schemas, whereas in reality there is none.
+
+To overcome these situations, users may use the `--dev-url` flag to provide
+Atlas with a connection string to a _Dev-Database_.
+This database is used to normalize the schema prior to planning migrations and
+for simulating changes to ensure their applicability before execution.
+
+[Read more about Dev-Databases](/concepts/dev-database)
+
+### Reference
+
+[CLI Command Reference](/cli-reference#atlas-schema-apply)
\ No newline at end of file
diff --git a/doc/md/declarative/inspect.md b/doc/md/declarative/inspect.md
new file mode 100644
index 00000000..d64ea49e
--- /dev/null
+++ b/doc/md/declarative/inspect.md
@@ -0,0 +1,19 @@
+---
+id: inspect
+slug: /declarative/inspect
+title: Inspecting existing schemas with Atlas 
+---
+
+### Automatic Schema Inspection
+Many projects begin with an existing database that users wish to start managing
+with Atlas. In this case, instead of having developers learn the [Atlas Language](/atlas-schema/sql-resources)
+and reverse engineer a schema definition file that precisely describes the existing database,
+Atlas supports _automatic schema inspection_.
+
+With automatic schema inspection, users simply provide Atlas with a connection string
+to their target database and Atlas prints out a schema definition file in the Atlas
+language that they can use as the starting point for working with this database.
+
+### Reference
+
+[CLI Command Reference](/cli-reference#atlas-schema-apply)
diff --git a/doc/md/getting-started/02-inspecting.mdx b/doc/md/getting-started/02-inspecting.mdx
index ab4a0d06..5fb26608 100644
--- a/doc/md/getting-started/02-inspecting.mdx
+++ b/doc/md/getting-started/02-inspecting.mdx
@@ -184,7 +184,7 @@ table "users" {
   }
 }
 ```
-This block represents a [Table](../atlas-schema/sql.mdx#table) resource with an `id`, and `name`
+This block represents a [Table](../atlas-schema/sql.mdx#table) resource with `id`, and `name`
 columns. The `schema` field references the `example` schema that is defined elsewhere
 in this document. In addition, the `primary_key` sub-block defines the `id` column as
 the primary key for the table. In SQL databases, columns usually have a type attribute
diff --git a/doc/md/getting-started/03-applying.mdx b/doc/md/getting-started/03-applying.mdx
index c367fb5a..4cf0379c 100644
--- a/doc/md/getting-started/03-applying.mdx
+++ b/doc/md/getting-started/03-applying.mdx
@@ -178,11 +178,11 @@ table "post_categories" {
     }
     foreign_key "post_category_post" {
         columns     = [column.post_id]
-        ref_columns = [column.id]
+        ref_columns = [table.blog_posts.column.id]
     }
     foreign_key "post_category_category" {
         columns     = [column.category_id]
-        ref_columns = [column.id]
+        ref_columns = [table.categories.column.id]
     }
 }
 ```
diff --git a/doc/website/sidebars.js b/doc/website/sidebars.js
index aaf911d7..9e7cc92e 100644
--- a/doc/website/sidebars.js
+++ b/doc/website/sidebars.js
@@ -21,6 +21,15 @@ module.exports = {
                 'getting-started/getting-started-apply',
             ]
         },
+        {
+            type: "category",
+            label: "Declarative Workflows",
+            collapsed: false,
+            items: [
+                {type: 'doc', id: 'declarative/inspect', label: 'Schema Inspection'},
+                {type: 'doc', id: 'declarative/apply', label: 'Applying Changes'},
+            ]
+        },
         {
             type: 'category',
             label: 'Atlas Schemas',
diff --git a/go.mod b/go.mod
index 10556d4f..9a69e23d 100644
--- a/go.mod
+++ b/go.mod
@@ -1,6 +1,6 @@
 module ariga.io/atlas
 
-go 1.17
+go 1.18
 
 require (
 	github.com/DATA-DOG/go-sqlmock v1.5.0
diff --git a/internal/ci/ci.tmpl b/internal/ci/ci.tmpl
index 32bdb3b2..5055ab72 100644
--- a/internal/ci/ci.tmpl
+++ b/internal/ci/ci.tmpl
@@ -14,7 +14,7 @@ jobs:
     steps:
     - uses: actions/setup-go@v2
       with:
-        go-version: 1.17
+        go-version: 1.18
     - uses: actions/checkout@v2
     - name: Run linters
       uses: golangci/golangci-lint-action@v3
@@ -51,7 +51,7 @@ jobs:
     runs-on: ubuntu-latest
     strategy:
       matrix:
-        go: [ '1.17', '1.18' ]
+        go: [ '1.18', '1.19' ]
     steps:
       - uses: actions/checkout@v2
       - uses: actions/setup-go@v2
@@ -74,7 +74,7 @@ jobs:
     runs-on: ubuntu-latest
     strategy:
       matrix:
-        go: [ '1.18' ]
+        go: [ '1.19' ]
     steps:
       - uses: actions/checkout@v2
       - uses: actions/setup-go@v2
@@ -96,7 +96,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
@@ -126,7 +126,7 @@ jobs:
       - uses: actions/checkout@v2.3.4
       - uses: actions/setup-go@v2
         with:
-          go-version: 1.18
+          go-version: 1.19
       - uses: actions/cache@v2.1.6
         with:
           path: ~/go/pkg/mod
diff --git a/sql/internal/sqlx/exclude.go b/sql/internal/sqlx/exclude.go
new file mode 100644
index 00000000..910e9d67
--- /dev/null
+++ b/sql/internal/sqlx/exclude.go
@@ -0,0 +1,149 @@
+// Copyright 2021-present The Atlas Authors. All rights reserved.
+// This source code is licensed under the Apache 2.0 license found
+// in the LICENSE file in the root directory of this source tree.
+
+package sqlx
+
+import (
+	"encoding/csv"
+	"fmt"
+	"path/filepath"
+	"strings"
+
+	"ariga.io/atlas/sql/schema"
+)
+
+// ExcludeRealm filters resources in the realm based on the given patterns.
+func ExcludeRealm(r *schema.Realm, patterns []string) error {
+	var schemas []*schema.Schema
+	globs, err := split(patterns)
+	if err != nil {
+		return err
+	}
+Filter:
+	for _, s := range r.Schemas {
+		for i, g := range globs {
+			if len(g) > 3 {
+				return fmt.Errorf("too many parts in pattern: %q", patterns[i])
+			}
+			match, err := filepath.Match(g[0], s.Name)
+			if err != nil {
+				return err
+			}
+			if match {
+				// In case there is a match, and it is
+				// a single glob we exclude this schema.
+				if len(g) == 1 {
+					continue Filter
+				}
+				if err := excludeS(s, g[1:]); err != nil {
+					return err
+				}
+			}
+		}
+		schemas = append(schemas, s)
+	}
+	r.Schemas = schemas
+	return nil
+}
+
+// ExcludeSchema filters resources in the schema based on the given patterns.
+func ExcludeSchema(s *schema.Schema, patterns []string) error {
+	if s.Realm == nil {
+		return fmt.Errorf("missing realm for schema %q", s.Name)
+	}
+	for i, p := range patterns {
+		patterns[i] = fmt.Sprintf("%s.%s", s.Name, p)
+	}
+	return ExcludeRealm(s.Realm, patterns)
+}
+
+// split parses the list of patterns into chain of resource-globs.
+// For example, 's*.t.*' is split to ['s*', 't', *].
+func split(patterns []string) ([][]string, error) {
+	globs := make([][]string, len(patterns))
+	for i, p := range patterns {
+		r := csv.NewReader(strings.NewReader(p))
+		r.Comma = '.'
+		switch parts, err := r.ReadAll(); {
+		case err != nil:
+			return nil, err
+		case len(parts) != 1:
+			return nil, fmt.Errorf("unexpected pattern: %q", p)
+		case len(parts[0]) == 0:
+			return nil, fmt.Errorf("empty pattern: %q", p)
+		default:
+			globs[i] = parts[0]
+		}
+	}
+	return globs, nil
+}
+
+func excludeS(s *schema.Schema, glob []string) error {
+	var tables []*schema.Table
+	for _, t := range s.Tables {
+		match, err := filepath.Match(glob[0], t.Name)
+		if err != nil {
+			return err
+		}
+		if match {
+			// In case there is a match, and it is
+			// a single glob we exclude this table.
+			if len(glob) == 1 {
+				continue
+			}
+			if err := excludeT(t, glob[1]); err != nil {
+				return err
+			}
+		}
+		// No match or glob has more than one pattern.
+		tables = append(tables, t)
+	}
+	s.Tables = tables
+	return nil
+}
+
+func excludeT(t *schema.Table, pattern string) (err error) {
+	ex := make(map[*schema.Index]struct{})
+	ef := make(map[*schema.ForeignKey]struct{})
+	t.Columns, err = filter(t.Columns, func(c *schema.Column) (bool, error) {
+		match, err := filepath.Match(pattern, c.Name)
+		if !match || err != nil {
+			return false, err
+		}
+		for _, idx := range c.Indexes {
+			ex[idx] = struct{}{}
+		}
+		for _, fk := range c.ForeignKeys {
+			ef[fk] = struct{}{}
+		}
+		return true, nil
+	})
+	t.Indexes, err = filter(t.Indexes, func(idx *schema.Index) (bool, error) {
+		if _, ok := ex[idx]; ok {
+			return true, nil
+		}
+		return filepath.Match(pattern, idx.Name)
+	})
+	t.ForeignKeys, err = filter(t.ForeignKeys, func(fk *schema.ForeignKey) (bool, error) {
+		if _, ok := ef[fk]; ok {
+			return true, nil
+		}
+		return filepath.Match(pattern, fk.Symbol)
+	})
+	return
+}
+
+func filter[T any](s []T, f func(T) (bool, error)) ([]T, error) {
+	r := make([]T, 0, len(s))
+	for i := range s {
+		match, err := f(s[i])
+		if err != nil {
+			return nil, err
+		}
+		if !match {
+			r = append(r, s[i])
+		}
+	}
+	return r, nil
+}
diff --git a/sql/migrate/dir.go b/sql/migrate/dir.go
index 198e8aa9..e63520c0 100644
--- a/sql/migrate/dir.go
+++ b/sql/migrate/dir.go
@@ -13,7 +13,6 @@ import (
 	"fmt"
 	"io"
 	"io/fs"
-	"io/ioutil"
 	"os"
 	"path/filepath"
 	"regexp"
@@ -226,40 +225,22 @@ func HashSum(dir Dir) (HashFile, error) {
 		hs HashFile
 		h  = sha256.New()
 	)
-	err := fs.WalkDir(dir, "", func(path string, d fs.DirEntry, err error) error {
-		if err != nil {
-			return err
+	files, err := dir.Files()
+	if err != nil {
+		return nil, err
+	}
+	for _, f := range files {
+		if _, err = h.Write([]byte(f.Name())); err != nil {
+			return nil, err
 		}
-		// If this is the integrity sum file do not include it into the sum.
-		if filepath.Base(path) == HashFileName {
-			return nil
+		// Check if this file contains an "atlas:sum" directive and if so, act to it.
+		if mode, ok := directive(string(f.Bytes()), directiveSum); ok && mode == sumModeIgnore {
+			continue
 		}
-		if !d.IsDir() {
-			f, err := dir.Open(path)
-			if err != nil {
-				return err
-			}
-			defer f.Close()
-			if _, err := h.Write([]byte(path)); err != nil {
-				return err
-			}
-			c, err := ioutil.ReadAll(f)
-			if err != nil {
-				return err
-			}
-			// Check if this file contains an "atlas:sum" directive and if so, act to it.
-			if mode, ok := directive(string(c), directiveSum); ok && mode == sumModeIgnore {
-				return nil
-			}
-			if _, err := h.Write(c); err != nil {
-				return err
-			}
-			hs = append(hs, struct{ N, H string }{path, base64.StdEncoding.EncodeToString(h.Sum(nil))})
+		if _, err = h.Write(f.Bytes()); err != nil {
+			return nil, err
 		}
-		return nil
-	})
-	if err != nil {
-		return nil, err
+		hs = append(hs, struct{ N, H string }{f.Name(), base64.StdEncoding.EncodeToString(h.Sum(nil))})
 	}
 	return hs, nil
 }
diff --git a/sql/schema/inspect.go b/sql/schema/inspect.go
index cdbc1522..ea2de5f6 100644
--- a/sql/schema/inspect.go
+++ b/sql/schema/inspect.go
@@ -57,6 +57,18 @@ type (
 
 		// Tables to inspect. Empty means all tables in the schema.
 		Tables []string
+
+		// Exclude defines a list of glob patterns used to filter resources from inspection.
+		// The syntax used by the different drivers is implemented as follows:
+		//
+		//	t   // exclude table 't'.
+		//	*   // exclude all tables.
+		//	t.c // exclude column, index and foreign-key named 'c' in table 't'.
+		//	t.* // the last item defines the filtering; all resources under 't' are excluded.
+		//	*.c // the last item defines the filtering; all resourced named 'c' are excluded in all tables.
+		//	*.* // the last item defines the filtering; all resourced under all tables are excluded.
+		//
+		Exclude []string
 	}
 
 	// InspectRealmOption describes options for RealmInspector.
@@ -67,6 +79,20 @@ type (
 
 		// Schemas to inspect. Empty means all schemas in the realm.
 		Schemas []string
+
+		// Exclude defines a list of glob patterns used to filter resources from inspection.
+		// The syntax used by the different drivers is implemented as follows:
+		//
+		//	s     // exclude schema 't'.
+		//	*     // exclude all schemas.
+		//	s.t   // exclude table 't' under schema 's'.
+		//	s.*   // the last item defines the filtering; all tables under 's' are excluded.
+		//	*.t   // the last item defines the filtering; all tables named 't' are excluded in all schemas.
+		//	*.*   // the last item defines the filtering; all tables under all schemas are excluded.
+		//	*.*.c // the last item defines the filtering; all resourced named 'c' are excluded in all tables.
+		//	*.*.* // the last item defines the filtering; all resources are excluded in all tables.
+		//
+		Exclude []string
 	}
 
 	// Inspector is the interface implemented by the different database


Test patch:
diff --git a/cmd/atlas/internal/cmdapi/migrate_test.go b/cmd/atlas/internal/cmdapi/migrate_test.go
index c1fa260b..6ed4dfb8 100644
--- a/cmd/atlas/internal/cmdapi/migrate_test.go
+++ b/cmd/atlas/internal/cmdapi/migrate_test.go
@@ -328,6 +328,9 @@ func TestMigrate_New(t *testing.T) {
 	require.FileExists(t, filepath.Join(p, "atlas.sum"))
 	require.Equal(t, 3, countFiles(t, p))
 
+	_, err = runCmd(Root, "migrate", "hash", "--dir", "file://"+p, "--force", "--dir-format", formatGolangMigrate)
+	require.NoError(t, err)
+	MigrateFlags.Force = false
 	s, err = runCmd(Root, "migrate", "new", "golang-migrate", "--dir", "file://"+p, "--dir-format", formatGolangMigrate)
 	require.Zero(t, s)
 	require.NoError(t, err)
@@ -335,6 +338,9 @@ func TestMigrate_New(t *testing.T) {
 	require.FileExists(t, filepath.Join(p, v+"_golang-migrate.down.sql"))
 	require.Equal(t, 5, countFiles(t, p))
 
+	s, err = runCmd(Root, "migrate", "hash", "--dir", "file://"+p, "--force", "--dir-format", formatGoose)
+	require.NoError(t, err)
+	MigrateFlags.Force = false
 	s, err = runCmd(Root, "migrate", "new", "goose", "--dir", "file://"+p, "--dir-format", formatGoose)
 	require.Zero(t, s)
 	require.NoError(t, err)
diff --git a/sql/internal/sqlx/exclude_test.go b/sql/internal/sqlx/exclude_test.go
new file mode 100644
index 00000000..98de70fe
--- /dev/null
+++ b/sql/internal/sqlx/exclude_test.go
@@ -0,0 +1,181 @@
+// Copyright 2021-present The Atlas Authors. All rights reserved.
+// This source code is licensed under the Apache 2.0 license found
+// in the LICENSE file in the root directory of this source tree.
+
+package sqlx
+
+import (
+	"testing"
+
+	"ariga.io/atlas/sql/schema"
+
+	"github.com/stretchr/testify/require"
+)
+
+func TestExcludeRealm_Schemas(t *testing.T) {
+	r := schema.NewRealm(schema.New("s1"), schema.New("s2"), schema.New("s3"))
+	require.NoError(t, ExcludeRealm(r, []string{"s4"}))
+	require.Len(t, r.Schemas, 3)
+
+	require.NoError(t, ExcludeRealm(r, []string{"s1", "s2.t2", "s3.t3.c3"}))
+	require.Len(t, r.Schemas, 2)
+	require.Equal(t, "s2", r.Schemas[0].Name)
+	require.Equal(t, "s3", r.Schemas[1].Name)
+
+	require.NoError(t, ExcludeRealm(r, []string{"*"}))
+	require.Empty(t, r.Schemas)
+
+	r = schema.NewRealm(schema.New("s1"), schema.New("s2"), schema.New("s3"))
+	require.NoError(t, ExcludeRealm(r, []string{"s*.*", "s*.*.*"}))
+	require.Len(t, r.Schemas, 3)
+	require.NoError(t, ExcludeRealm(r, []string{"s*"}))
+	require.Empty(t, r.Schemas)
+}
+
+func TestExcludeRealm_Tables(t *testing.T) {
+	r := schema.NewRealm(
+		schema.New("s0"),
+		schema.New("s1").AddTables(
+			schema.NewTable("t1"),
+		),
+		schema.New("s2").AddTables(
+			schema.NewTable("t1"),
+			schema.NewTable("t2"),
+		),
+		schema.New("s3").AddTables(
+			schema.NewTable("t1"),
+			schema.NewTable("t2"),
+			schema.NewTable("t3"),
+		),
+	)
+	require.NoError(t, ExcludeRealm(r, []string{"s4"}))
+	require.Len(t, r.Schemas, 4)
+	require.NoError(t, ExcludeRealm(r, []string{"s0"}))
+	require.Len(t, r.Schemas, 3)
+	require.Equal(t, "s1", r.Schemas[0].Name)
+	require.Equal(t, "s2", r.Schemas[1].Name)
+	require.Equal(t, "s3", r.Schemas[2].Name)
+
+	require.NoError(t, ExcludeRealm(r, []string{"*.t1.*"}))
+	require.Len(t, r.Schemas, 3)
+	require.Len(t, r.Schemas[0].Tables, 1)
+	require.Len(t, r.Schemas[1].Tables, 2)
+	require.Len(t, r.Schemas[2].Tables, 3)
+
+	require.NoError(t, ExcludeRealm(r, []string{"*.t1"}))
+	require.Len(t, r.Schemas, 3)
+	require.Empty(t, r.Schemas[0].Tables)
+	require.Len(t, r.Schemas[1].Tables, 1)
+	require.Len(t, r.Schemas[2].Tables, 2)
+
+	require.NoError(t, ExcludeRealm(r, []string{"s[12].t2"}))
+	require.Len(t, r.Schemas, 3)
+	require.Empty(t, r.Schemas[0].Tables)
+	require.Empty(t, r.Schemas[1].Tables)
+	require.Len(t, r.Schemas[2].Tables, 2)
+
+	require.NoError(t, ExcludeRealm(r, []string{"*.t[23].*"}))
+	require.Len(t, r.Schemas, 3)
+	require.Len(t, r.Schemas[2].Tables, 2)
+	require.NoError(t, ExcludeRealm(r, []string{"*.t[23]"}))
+	require.Len(t, r.Schemas, 3)
+	require.Empty(t, r.Schemas[2].Tables)
+}
+
+func TestExcludeRealm_Columns(t *testing.T) {
+	r := schema.NewRealm(
+		schema.New("s1").AddTables(
+			func() *schema.Table {
+				t := schema.NewTable("t1").AddColumns(schema.NewColumn("c1"))
+				t.AddIndexes(schema.NewIndex("i1").AddColumns(t.Columns[0]))
+				return t
+			}(),
+		),
+		schema.New("s2").AddTables(
+			func() *schema.Table {
+				t := schema.NewTable("t1").AddColumns(schema.NewColumn("c1"))
+				t.AddIndexes(schema.NewIndex("i1").AddColumns(t.Columns[0]))
+				return t
+			}(),
+			func() *schema.Table {
+				t := schema.NewTable("t2").AddColumns(schema.NewColumn("c1"), schema.NewColumn("c2"))
+				t.AddIndexes(schema.NewIndex("i1").AddColumns(t.Columns[0]), schema.NewIndex("i2").AddColumns(t.Columns[1]))
+				return t
+			}(),
+		),
+		schema.New("s3").AddTables(
+			func() *schema.Table {
+				t := schema.NewTable("t1").AddColumns(schema.NewColumn("c1"))
+				t.AddIndexes(schema.NewIndex("i1").AddColumns(t.Columns[0]))
+				return t
+			}(),
+			func() *schema.Table {
+				t := schema.NewTable("t2").AddColumns(schema.NewColumn("c1"), schema.NewColumn("c2"))
+				t.AddIndexes(schema.NewIndex("i1").AddColumns(t.Columns[0]), schema.NewIndex("i2").AddColumns(t.Columns[1]))
+				return t
+			}(),
+			func() *schema.Table {
+				t := schema.NewTable("t3").AddColumns(schema.NewColumn("c1"), schema.NewColumn("c2"), schema.NewColumn("c3"))
+				t.AddIndexes(schema.NewIndex("i1").AddColumns(t.Columns[0]), schema.NewIndex("i2").AddColumns(t.Columns[1]))
+				return t
+			}(),
+		),
+	)
+	require.NoError(t, ExcludeRealm(r, []string{"s[23].t[23].c1"}))
+	require.Len(t, r.Schemas, 3)
+	require.Len(t, r.Schemas[0].Tables, 1)
+	require.Len(t, r.Schemas[0].Tables[0].Columns, 1)
+	require.Len(t, r.Schemas[0].Tables[0].Indexes, 1)
+
+	require.Len(t, r.Schemas[1].Tables, 2)
+	require.Len(t, r.Schemas[1].Tables[0].Columns, 1)
+	require.Len(t, r.Schemas[1].Tables[0].Indexes, 1)
+	require.Len(t, r.Schemas[1].Tables[1].Columns, 1)
+	require.Len(t, r.Schemas[1].Tables[1].Indexes, 1)
+
+	require.Len(t, r.Schemas[2].Tables, 3)
+	require.Len(t, r.Schemas[2].Tables[0].Columns, 1)
+	require.Len(t, r.Schemas[2].Tables[0].Indexes, 1)
+	require.Len(t, r.Schemas[2].Tables[1].Columns, 1)
+	require.Len(t, r.Schemas[2].Tables[1].Indexes, 1)
+	require.Len(t, r.Schemas[2].Tables[2].Columns, 2)
+	require.Len(t, r.Schemas[2].Tables[2].Indexes, 1)
+
+	require.NoError(t, ExcludeRealm(r, []string{"s[23].t*.*"}))
+	require.Len(t, r.Schemas, 3)
+	require.Len(t, r.Schemas[0].Tables, 1)
+	require.Len(t, r.Schemas[0].Tables[0].Columns, 1)
+	require.Len(t, r.Schemas[0].Tables[0].Indexes, 1)
+
+	require.Len(t, r.Schemas[1].Tables, 2)
+	require.Empty(t, r.Schemas[1].Tables[0].Columns)
+	require.Empty(t, r.Schemas[1].Tables[0].Indexes)
+	require.Empty(t, r.Schemas[1].Tables[1].Columns)
+	require.Empty(t, r.Schemas[1].Tables[1].Indexes)
+
+	require.Len(t, r.Schemas[2].Tables, 3)
+	require.Empty(t, r.Schemas[2].Tables[0].Columns)
+	require.Empty(t, r.Schemas[2].Tables[0].Indexes)
+	require.Empty(t, r.Schemas[2].Tables[1].Columns)
+	require.Empty(t, r.Schemas[2].Tables[1].Indexes)
+	require.Empty(t, r.Schemas[2].Tables[2].Columns)
+	require.Empty(t, r.Schemas[2].Tables[2].Indexes)
+
+	require.NoError(t, ExcludeRealm(r, []string{"*.*.*"}))
+	require.Len(t, r.Schemas, 3)
+	require.Len(t, r.Schemas[0].Tables, 1)
+	require.Empty(t, r.Schemas[0].Tables[0].Columns)
+	require.Empty(t, r.Schemas[0].Tables[0].Indexes)
+}
+
+func TestExcludeSchema(t *testing.T) {
+	r := schema.NewRealm(
+		schema.New("s1").AddTables(
+			schema.NewTable("t1"),
+			schema.NewTable("t2"),
+		),
+	)
+	require.NoError(t, ExcludeSchema(r.Schemas[0], []string{"t2"}))
+	require.Len(t, r.Schemas, 1)
+	require.Len(t, r.Schemas[0].Tables, 1)
+}
diff --git a/sql/migrate/dir_test.go b/sql/migrate/dir_test.go
index 405203eb..c9975415 100644
--- a/sql/migrate/dir_test.go
+++ b/sql/migrate/dir_test.go
@@ -41,25 +41,38 @@ func TestHashSum(t *testing.T) {
 	require.Equal(t, 1, countFiles(t, d))
 	requireFileEqual(t, d, v+"_plan.sql", "cmd;\n")
 
+	// Files not ending with .sql get ignored.
+	p = t.TempDir()
+	d, err = migrate.NewLocalDir(p)
+	require.NoError(t, err)
+	pl = migrate.NewPlanner(nil, d)
+	require.NotNil(t, pl)
+	require.NoError(t, os.WriteFile(filepath.Join(p, "include.sql"), nil, 0600))
+	require.NoError(t, os.WriteFile(filepath.Join(p, "exclude.txt"), nil, 0600))
+	require.NoError(t, pl.WritePlan(plan))
+	require.Equal(t, 4, countFiles(t, d))
+	c, err := os.ReadFile(filepath.Join(p, "atlas.sum"))
+	require.NoError(t, err)
+	require.Contains(t, string(c), "include.sql")
+	require.NotContains(t, string(c), "exclude.txt")
+
 	// Files with directive in first line get ignored.
 	p = t.TempDir()
 	d, err = migrate.NewLocalDir(p)
 	require.NoError(t, err)
 	pl = migrate.NewPlanner(nil, d)
 	require.NotNil(t, pl)
-	require.NoError(t, os.WriteFile(filepath.Join(p, "include"), []byte("//atlas:sum\nfoo"), 0600))
-	require.NoError(t, os.WriteFile(filepath.Join(p, "exclude_1"), []byte("//atlas:sum ignore bar"), 0600))
-	require.NoError(t, os.WriteFile(filepath.Join(p, "exclude_2"), []byte("//atlas:sum ignore\nbar"), 0600))
-	require.NoError(t, os.WriteFile(filepath.Join(p, "exclude_3"), []byte("atlas:sum ignore"), 0600))
+	require.NoError(t, os.WriteFile(filepath.Join(p, "include.sql"), []byte("//atlas:sum\nfoo"), 0600))
+	require.NoError(t, os.WriteFile(filepath.Join(p, "exclude_1.sql"), []byte("//atlas:sum ignore\nbar"), 0600))
+	require.NoError(t, os.WriteFile(filepath.Join(p, "exclude_2.sql"), []byte("atlas:sum ignore"), 0600))
 	require.NoError(t, pl.WritePlan(plan))
-	require.Equal(t, 6, countFiles(t, d))
+	require.Equal(t, 5, countFiles(t, d))
 	requireFileEqual(t, d, v+"_plan.sql", "cmd;\n")
-	c, err := os.ReadFile(filepath.Join(p, "atlas.sum"))
+	c, err = os.ReadFile(filepath.Join(p, "atlas.sum"))
 	require.NoError(t, err)
 	require.Contains(t, string(c), "include")
-	require.NotContains(t, string(c), "exclude_2")
-	require.NotContains(t, string(c), "exclude_2")
-	require.NotContains(t, string(c), "exclude_3")
+	require.NotContains(t, string(c), "exclude_1.sql")
+	require.NotContains(t, string(c), "exclude_2.sql")
 }
 
 //go:embed testdata/migrate/atlas.sum
diff --git a/sql/migrate/testdata/migrate/atlas.sum b/sql/migrate/testdata/migrate/atlas.sum
index 23c4fc95..7660de17 100644
--- a/sql/migrate/testdata/migrate/atlas.sum
+++ b/sql/migrate/testdata/migrate/atlas.sum
@@ -1,6 +1,3 @@
-h1:rXkukFq7O3f2xgfZZi97Mh5Yd6MYRy6XmMYMV+Z9S9c=
+h1:M74RrNK69S2pj6C541LR1ew5O32/i0WoyNgsJmyuiUk=
 1_initial.down.sql h1:0zypK43rgPbgvVUgVJABGN25VgM1QSeU+LJDBb8cEQI=
 1_initial.up.sql h1:hFhs5XhRml4KTWGF5td6h1s7xNqAFnaEBbC5Y/NF7i4=
-sub/1.a_sub.up.sql h1:GomhYquMSavc1O/WeukxL02orVIHO2EUK6pWsedwELc=
-sub/2.10.x-20_description.sql h1:9HvZYZZMi9S7eLQvNAR9W9BxaMRvFz0A9TUmpdEHt4o=
-sub/3_partly.sql h1:WC7hyKTXrRB3woOqTAdc2m7XSLnbctgtF+W2ozDLUVc=