Repository: ariga/atlas
Pull request #1117 (2022-09-01 09:03:20)

cmd/atlas/internal/cmdapi: add migrate clean command
As a safety guard this command requires the `--local-url` flag as well as a confirmation from the user, unless the `--auto-approve` flag is given.


Patch:
diff --git a/README.md b/README.md
index 2137757a..6aa26ea3 100644
--- a/README.md
+++ b/README.md
@@ -1,19 +1,25 @@
-
-# Atlas -  A Database Toolkit
-[![Discord](https://img.shields.io/discord/930720389120794674?label=discord&logo=discord&style=flat-square&logoColor=white)](https://discord.gg/zZ6sWVg6NT)
-
-<img width="50%" align="right" style="display: block; margin:40px auto;"
-     src="https://atlasgo.io/uploads/images/gopher.png"/>
+<div align="center">
+     <img width="50%" align="center" style="display: block; "
+     src="https://blog.ariga.io/uploads/images/atlas_logo_text.png"/>
+</div>
+<h2 align="center">Manage your database schemas with Atlas CLI</h2>
+
+<div align="center">
+     <p align="center">
+         <a href="https://atlasgo.io/getting-started/" rel="dofollow"><strong>Explore the docs »</strong></a>
+         <br/><br/>
+          <a href="https://discord.com/invite/zZ6sWVg6NT">Discord</a>
+    ·
+          <a href="https://twitter.com/ariga_io/">Twitter</a>
+     ·
+          <a href="https://github.com/ariga/atlas/issues">Report a Bug</a>
+    ·
+          <a href="https://github.com/ariga/atlas/discussions">Request a Feature</a>
+     </p>
+</div>
 
 Atlas CLI is an open source tool that helps developers manage their database schemas by applying modern DevOps principles. Contrary to existing tools, Atlas intelligently plans schema migrations for you. Atlas users can use the [Atlas DDL](https://atlasgo.io/concepts/ddl#hcl) (data definition language) to describe their desired database schema and use the command-line tool to plan and apply the migrations to their systems.
 
-### Supported databases:
-* MySQL
-* MariaDB
-* PostgresSQL
-* SQLite
-* TiDB
-
 ## Quick Installation
 
 On macOS:
@@ -41,3 +47,6 @@ atlas schema apply -u "mysql://root:pass@localhost:3306/example" -f atlas.hcl
 
 ### About the Project
 Read more about the motivation of the project [here](https://atlasgo.io/blog/2021/11/25/meet-atlas).
+
+### Supported Databses
+MySQL, MariaDB, PostgresSQL, SQLite, TiDB, CockroachDB
diff --git a/cmd/atlas/internal/cmdapi/schema.go b/cmd/atlas/internal/cmdapi/schema.go
index 16f3da23..d6ff70e8 100644
--- a/cmd/atlas/internal/cmdapi/schema.go
+++ b/cmd/atlas/internal/cmdapi/schema.go
@@ -9,11 +9,11 @@ import (
 	"errors"
 	"fmt"
 	"io/fs"
-	"io/ioutil"
 	"os"
 	"path/filepath"
 	"strings"
 
+	"ariga.io/atlas/sql/migrate"
 	"ariga.io/atlas/sql/schema"
 	"ariga.io/atlas/sql/sqlclient"
 	"github.com/hashicorp/hcl/v2/hclwrite"
@@ -22,13 +22,14 @@ import (
 )
 
 const (
-	urlFlag     = "url"
-	schemaFlag  = "schema"
-	excludeFlag = "exclude"
-	devURLFlag  = "dev-url"
-	fileFlag    = "file"
-	dsnFlag     = "dsn"
-	varFlag     = "var"
+	urlFlag         = "url"
+	schemaFlag      = "schema"
+	excludeFlag     = "exclude"
+	devURLFlag      = "dev-url"
+	fileFlag        = "file"
+	dsnFlag         = "dsn"
+	varFlag         = "var"
+	autoApproveFlag = "auto-approve"
 )
 
 var (
@@ -56,6 +57,13 @@ var (
 		DryRun      bool
 		AutoApprove bool
 	}
+
+	// CleanFlags are the flags used in SchemaClean command.
+	CleanFlags struct {
+		URL         string
+		AutoApprove bool
+	}
+
 	// SchemaApply represents the 'atlas schema apply' subcommand command.
 	SchemaApply = &cobra.Command{
 		Use:   "apply",
@@ -84,6 +92,18 @@ migration.`,
   atlas schema apply -u "sqlite://file:ex1.db?_fk=1" -f schema.hcl`,
 	}
 
+	// SchemaClean represents the 'atlas schema clean' subcommand.
+	SchemaClean = &cobra.Command{
+		Use:   "clean [flags]",
+		Short: "Removes all objects from the connected database.",
+		Long: `'atlas schema clean' drops all objects in the connected database and leaves it in an empty state.
+As a safety feature, 'atlas schema clean' will ask for confirmation before attempting to execute any SQL.`,
+		Example: `  atlas schema clean -u mysql://user:pass@localhost:3306/dbname
+  atlas schema clean -u mysql://user:pass@localhost:3306/`,
+		PreRunE: schemaFlagsFromEnv,
+		RunE:    CmdCleanRun,
+	}
+
 	// SchemaInspect represents the 'atlas schema inspect' subcommand.
 	SchemaInspect = &cobra.Command{
 		Use:   "inspect",
@@ -142,12 +162,17 @@ func init() {
 	SchemaApply.Flags().StringSliceVarP(&SchemaFlags.Schemas, schemaFlag, "s", nil, "Set schema names.")
 	SchemaApply.Flags().StringVarP(&ApplyFlags.DevURL, devURLFlag, "", "", "URL for the dev database. Used to validate schemas and calculate diffs\nbefore running migration.")
 	SchemaApply.Flags().BoolVarP(&ApplyFlags.DryRun, "dry-run", "", false, "Dry-run. Print SQL plan without prompting for execution.")
-	SchemaApply.Flags().BoolVarP(&ApplyFlags.AutoApprove, "auto-approve", "", false, "Auto approve. Apply the schema changes without prompting for approval.")
+	SchemaApply.Flags().BoolVarP(&ApplyFlags.AutoApprove, autoApproveFlag, "", false, "Auto approve. Apply the schema changes without prompting for approval.")
 	SchemaApply.Flags().StringVarP(&SchemaFlags.DSN, dsnFlag, "d", "", "")
 	cobra.CheckErr(SchemaApply.Flags().MarkHidden(dsnFlag))
 	cobra.CheckErr(SchemaApply.MarkFlagRequired(urlFlag))
 	cobra.CheckErr(SchemaApply.MarkFlagRequired(fileFlag))
 
+	// Schema clean flags.
+	SchemaClean.Flags().StringVarP(&CleanFlags.URL, urlFlag, "u", "", "URL to the database using the format:\n[driver://username:password@address/dbname?param=value]")
+	SchemaClean.Flags().BoolVarP(&CleanFlags.AutoApprove, autoApproveFlag, "", false, "Auto approve. Apply the schema changes without prompting for approval.")
+	cobra.CheckErr(SchemaClean.MarkFlagRequired(urlFlag))
+
 	// Schema inspect flags.
 	schemaCmd.AddCommand(SchemaInspect)
 	SchemaInspect.Flags().StringVarP(&SchemaFlags.URL, urlFlag, "u", "", "[driver://username:password@protocol(address)/dbname?param=value] select a database using the URL format")
@@ -159,6 +184,7 @@ func init() {
 
 	// Schema fmt.
 	schemaCmd.AddCommand(SchemaFmt)
+	schemaCmd.AddCommand(SchemaClean)
 }
 
 // selectEnv returns the Env from the current project file based on the selected
@@ -234,6 +260,7 @@ func maySetFlag(cmd *cobra.Command, name, envVal string) error {
 func dsn2url(cmd *cobra.Command) error {
 	dsnF, urlF := cmd.Flag(dsnFlag), cmd.Flag(urlFlag)
 	switch {
+	case dsnF == nil:
 	case dsnF.Changed && urlF.Changed:
 		return errors.New(`both flags "url" and "dsn" were set`)
 	case dsnF.Changed && !urlF.Changed:
@@ -279,6 +306,66 @@ func CmdApplyRun(cmd *cobra.Command, _ []string) error {
 	return applyRun(cmd, c, ApplyFlags.DevURL, ApplyFlags.Paths, ApplyFlags.DryRun, ApplyFlags.AutoApprove, GlobalFlags.Vars)
 }
 
+// CmdCleanRun is the command executed when running the CLI with 'schema clean' args.
+func CmdCleanRun(cmd *cobra.Command, _ []string) error {
+	// Open a client to the database.
+	c, err := sqlclient.Open(cmd.Context(), CleanFlags.URL)
+	if err != nil {
+		return err
+	}
+	defer c.Close()
+	var drop []schema.Change
+	// If the connection is bound to a schema, only drop the resources inside the schema.
+	switch c.URL.Schema {
+	case "":
+		r, err := c.InspectRealm(cmd.Context(), nil)
+		if err != nil {
+			return err
+		}
+		drop, err = c.RealmDiff(r, nil)
+		if err != nil {
+			return err
+		}
+	default:
+		s, err := c.InspectSchema(cmd.Context(), c.URL.Schema, nil)
+		if err != nil {
+			return err
+		}
+		drop, err = c.SchemaDiff(s, schema.New(s.Name))
+		if err != nil {
+			return err
+		}
+	}
+	if len(drop) == 0 {
+		cmd.Println("Nothing to drop")
+		return nil
+	}
+	if err := summary(cmd, c, drop); err != nil {
+		return err
+	}
+	if CleanFlags.AutoApprove || promptUser() {
+		if err := c.ApplyChanges(cmd.Context(), drop); err != nil {
+			return err
+		}
+	}
+	return nil
+}
+
+func summary(cmd *cobra.Command, drv migrate.Driver, changes []schema.Change) error {
+	p, err := drv.PlanChanges(cmd.Context(), "", changes)
+	if err != nil {
+		return err
+	}
+	cmd.Println("-- Planned Changes:")
+	for _, c := range p.Changes {
+		if c.Comment != "" {
+			cmd.Println("--", strings.ToUpper(c.Comment[:1])+c.Comment[1:])
+		}
+		cmd.Println(c.Cmd)
+	}
+	return nil
+}
+
 // CmdFmtRun formats all HCL files in a given directory using canonical HCL formatting
 // rules.
 func CmdFmtRun(cmd *cobra.Command, args []string) {
@@ -341,21 +428,10 @@ func applyRun(cmd *cobra.Command, client *sqlclient.Client, devURL string, paths
 		cmd.Println("Schema is synced, no changes to be made")
 		return nil
 	}
-	p, err := client.PlanChanges(ctx, "plan", changes)
-	if err != nil {
+	if err := summary(cmd, client, changes); err != nil {
 		return err
 	}
-	cmd.Println("-- Planned Changes:")
-	for _, c := range p.Changes {
-		if c.Comment != "" {
-			cmd.Println("--", strings.ToUpper(c.Comment[:1])+c.Comment[1:])
-		}
-		cmd.Println(c.Cmd)
-	}
-	if dryRun {
-		return nil
-	}
-	if autoApprove || promptUser() {
+	if !dryRun && (autoApprove || promptUser()) {
 		if err := client.ApplyChanges(ctx, changes); err != nil {
 			return err
 		}
@@ -400,7 +476,7 @@ func tasks(path string) ([]fmttask, error) {
 		}
 		return tasks, nil
 	}
-	all, err := ioutil.ReadDir(path)
+	all, err := os.ReadDir(path)
 	if err != nil {
 		return nil, err
 	}
@@ -409,9 +485,13 @@ func tasks(path string) ([]fmttask, error) {
 			continue
 		}
 		if strings.HasSuffix(f.Name(), ".hcl") {
+			i, err := f.Info()
+			if err != nil {
+				return nil, err
+			}
 			tasks = append(tasks, fmttask{
 				path: filepath.Join(path, f.Name()),
-				info: f,
+				info: i,
 			})
 		}
 	}
diff --git a/cmd/atlas/internal/lint/lint.go b/cmd/atlas/internal/lint/lint.go
index c53ce952..c2405a00 100644
--- a/cmd/atlas/internal/lint/lint.go
+++ b/cmd/atlas/internal/lint/lint.go
@@ -221,13 +221,13 @@ func (d *DevLoader) LoadChanges(ctx context.Context, base, files []migrate.File)
 		diff.Files[i] = &sqlcheck.File{
 			File: f,
 		}
-		stmts, err := f.Stmts()
+		stmts, err := stmts(f)
 		if err != nil {
 			return nil, &FileError{File: f.Name(), Err: fmt.Errorf("scanning statements: %w", err)}
 		}
 		start := current
 		for _, s := range stmts {
-			if _, err := d.Dev.ExecContext(ctx, s); err != nil {
+			if _, err := d.Dev.ExecContext(ctx, s.Text); err != nil {
 				return nil, &FileError{File: f.Name(), Err: fmt.Errorf("executing statement: %w", err)}
 			}
 			target, err := d.inspect(ctx)
@@ -239,14 +239,9 @@ func (d *DevLoader) LoadChanges(ctx context.Context, base, files []migrate.File)
 				return nil, err
 			}
 			current = target
-			p, err := pos(f, s)
-			if err != nil {
-				return nil, err
-			}
 			diff.Files[i].Changes = append(diff.Files[i].Changes, &sqlcheck.Change{
-				Pos:     p,
 				Stmt:    s,
-				Changes: d.mayFix(s, changes),
+				Changes: d.mayFix(s.Text, changes),
 			})
 		}
 		if diff.Files[i].Sum, err = d.Dev.RealmDiff(start, current); err != nil {
@@ -274,6 +269,27 @@ func (d *DevLoader) inspect(ctx context.Context) (*schema.Realm, error) {
 	return d.Dev.InspectRealm(ctx, opts)
 }
 
+func stmts(f migrate.File) ([]*migrate.Stmt, error) {
+	if s, ok := f.(interface {
+		StmtDecls() ([]*migrate.Stmt, error)
+	}); ok {
+		return s.StmtDecls()
+	}
+	s1, err := f.Stmts()
+	if err != nil {
+		return nil, err
+	}
+	s2 := make([]*migrate.Stmt, len(s1))
+	for i := range s1 {
+		p, err := pos(f, s1[i])
+		if err != nil {
+			return nil, err
+		}
+		s2[i] = &migrate.Stmt{Pos: p, Text: s1[i]}
+	}
+	return s2, nil
+}
+
 // pos returns the position of a statement in migration file.
 func pos(f migrate.File, stmt string) (int, error) {
 	i := bytes.Index(f.Bytes(), []byte(stmt))
diff --git a/doc/md/reference.md b/doc/md/reference.md
index 6ee4d67b..90e6d544 100644
--- a/doc/md/reference.md
+++ b/doc/md/reference.md
@@ -339,6 +339,34 @@ migration.
 ```
 
 
+### atlas schema clean
+
+Removes all objects from the connected database.
+
+#### Usage
+```
+atlas schema clean [flags]
+```
+
+#### Details
+'atlas schema clean' drops all objects in the connected database and leaves it in an empty state.
+As a safety feature, 'atlas schema clean' will ask for confirmation before attempting to execute any SQL.
+
+#### Example
+
+```
+  atlas schema clean -u mysql://user:pass@localhost:3306/dbname
+  atlas schema clean -u mysql://user:pass@localhost:3306/
+```
+#### Flags
+```
+      --auto-approve   Auto approve. Apply the schema changes without prompting for approval.
+  -u, --url string     URL to the database using the format:
+                       [driver://username:password@address/dbname?param=value]
+
+```
+
+
 ### atlas schema diff
 
 Calculate and print the diff between two schemas.
diff --git a/sql/migrate/lex.go b/sql/migrate/lex.go
index 6842e07e..c5e7dc35 100644
--- a/sql/migrate/lex.go
+++ b/sql/migrate/lex.go
@@ -16,6 +16,7 @@ import (
 // Stmt represents a scanned statement text along with its
 // position in the file and associated comments group.
 type Stmt struct {
+	Pos      int      // statement position
 	Text     string   // statement text
 	Comments []string // associated comments
 }
@@ -62,7 +63,8 @@ func stmts(input string) ([]*Stmt, error) {
 
 type lex struct {
 	input    string
-	pos      int      // current position
+	pos      int      // current and real position
+	total    int      // total bytes scanned so far
 	width    int      // size of latest rune
 	depth    int      // depth of parentheses
 	delim    string   // configured delimiter
@@ -121,7 +123,7 @@ Scan:
 			}
 		// Delimiters take precedence over comments.
 		case strings.HasPrefix(l.input[l.pos-l.width:], l.delim) && l.depth == 0:
-			l.pos += len(l.delim) - l.width
+			l.addPos(len(l.delim) - l.width)
 			text = l.input[:l.pos]
 			break Scan
 		case r == '#':
@@ -140,16 +142,14 @@ func (l *lex) next() rune {
 		return eos
 	}
 	r, w := utf8.DecodeRuneInString(l.input[l.pos:])
-	l.pos += w
 	l.width = w
+	l.addPos(w)
 	return r
 }
 
-func (l *lex) pick() rune {
-	p, w := l.pos, l.width
-	r := l.next()
-	l.pos, l.width = p, w
-	return r
+func (l *lex) addPos(p int) {
+	l.pos += p
+	l.total += p
 }
 
 func (l *lex) skipQuote(quote rune) error {
@@ -173,13 +173,14 @@ func (l *lex) comment(left, right string) {
 	}
 	// If the comment reside inside a statement, collect it.
 	if l.pos != len(left) {
-		l.pos += i + len(right)
+		l.addPos(i + len(right))
 		return
 	}
+	l.addPos(i + len(right))
 	// If we did not scan any statement characters, it
 	// can be skipped and stored in the comments group.
-	l.comments = append(l.comments, l.input[:l.pos+i+len(right)])
-	l.input = l.input[l.pos+i+len(right):]
+	l.comments = append(l.comments, l.input[:l.pos])
+	l.input = l.input[l.pos:]
 	l.pos = 0
 	// Double \n separate the comments group from the statement.
 	if strings.HasPrefix(l.input, "\n\n") || right == "\n" && strings.HasPrefix(l.input, "\n") {
@@ -189,11 +190,13 @@ func (l *lex) comment(left, right string) {
 }
 
 func (l *lex) skipSpaces() {
+	n := len(l.input)
 	l.input = strings.TrimLeftFunc(l.input, unicode.IsSpace)
+	l.total += n - len(l.input)
 }
 
 func (l *lex) emit(text string) *Stmt {
-	s := &Stmt{Text: text, Comments: l.comments}
+	s := &Stmt{Pos: l.total - len(text), Text: text, Comments: l.comments}
 	l.input = l.input[l.pos:]
 	l.pos = 0
 	l.comments = nil
diff --git a/sql/mysql/mysqlcheck/mysqlcheck.go b/sql/mysql/mysqlcheck/mysqlcheck.go
index 8a1934de..15cd58c4 100644
--- a/sql/mysql/mysqlcheck/mysqlcheck.go
+++ b/sql/mysql/mysqlcheck/mysqlcheck.go
@@ -25,7 +25,7 @@ func addNotNull(p *datadepend.ColumnPass) (diags []sqlcheck.Diagnostic, err erro
 	// changes that may cause the migration to fail.
 	mightFail := func(tt string) {
 		diags = append(diags, sqlcheck.Diagnostic{
-			Pos: p.Change.Pos,
+			Pos: p.Change.Stmt.Pos,
 			Text: fmt.Sprintf(
 				"Adding a non-nullable %q column %q will fail in case table %q is not empty",
 				tt, p.Column.Name, p.Table.Name,
@@ -35,7 +35,7 @@ func addNotNull(p *datadepend.ColumnPass) (diags []sqlcheck.Diagnostic, err erro
 	implicitUpdate := func(tt, v string) {
 		diags = append(diags, sqlcheck.Diagnostic{
 			Code: codeImplicitUpdate,
-			Pos:  p.Change.Pos,
+			Pos:  p.Change.Stmt.Pos,
 			Text: fmt.Sprintf(
 				"Adding a non-nullable %q column %q on table %q without a default value implicitly sets existing rows with %s",
 				tt, p.Column.Name, p.Table.Name, v,
diff --git a/sql/postgres/postgrescheck/postgrescheck.go b/sql/postgres/postgrescheck/postgrescheck.go
index b9592359..e9dcf48f 100644
--- a/sql/postgres/postgrescheck/postgrescheck.go
+++ b/sql/postgres/postgrescheck/postgrescheck.go
@@ -21,7 +21,7 @@ func addNotNull(p *datadepend.ColumnPass) (diags []sqlcheck.Diagnostic, err erro
 	}
 	return []sqlcheck.Diagnostic{
 		{
-			Pos: p.Change.Pos,
+			Pos: p.Change.Stmt.Pos,
 			Text: fmt.Sprintf(
 				"Adding a non-nullable %q column %q will fail in case table %q is not empty",
 				tt, p.Column.Name, p.Table.Name,
diff --git a/sql/sqlcheck/datadepend/datadepend.go b/sql/sqlcheck/datadepend/datadepend.go
index 5e7a6a95..decc9028 100644
--- a/sql/sqlcheck/datadepend/datadepend.go
+++ b/sql/sqlcheck/datadepend/datadepend.go
@@ -90,7 +90,7 @@ func (a *Analyzer) Diagnostics(_ context.Context, p *sqlcheck.Pass) (diags []sql
 					if c.I.Unique && column != nil {
 						diags = append(diags, sqlcheck.Diagnostic{
 							Code: codeAddUniqueI,
-							Pos:  sc.Pos,
+							Pos:  sc.Stmt.Pos,
 							Text: fmt.Sprintf("Adding a unique index %q on table %q might fail in case column %q contains duplicate entries", c.I.Name, m.T.Name, column.Name),
 						})
 					}
@@ -98,7 +98,7 @@ func (a *Analyzer) Diagnostics(_ context.Context, p *sqlcheck.Pass) (diags []sql
 					if c.Change.Is(schema.ChangeUnique) && c.To.Unique && p.File.IndexSpan(m.T, c.To)&sqlcheck.SpanAdded == 0 {
 						diags = append(diags, sqlcheck.Diagnostic{
 							Code: codeModUniqueI,
-							Pos:  sc.Pos,
+							Pos:  sc.Stmt.Pos,
 							Text: fmt.Sprintf("Modifying an index %q on table %q might fail in case of duplicate entries", c.To.Name, m.T.Name),
 						})
 					}
diff --git a/sql/sqlcheck/destructive/destructive.go b/sql/sqlcheck/destructive/destructive.go
index 12457128..75c1265c 100644
--- a/sql/sqlcheck/destructive/destructive.go
+++ b/sql/sqlcheck/destructive/destructive.go
@@ -59,7 +59,7 @@ func (a *Analyzer) Analyze(_ context.Context, p *sqlcheck.Pass) error {
 					}
 					diags = append(diags, sqlcheck.Diagnostic{
 						Code: codeDropS,
-						Pos:  sc.Pos,
+						Pos:  sc.Stmt.Pos,
 						Text: text,
 					})
 				}
@@ -67,7 +67,7 @@ func (a *Analyzer) Analyze(_ context.Context, p *sqlcheck.Pass) error {
 				if p.File.SchemaSpan(c.T.Schema) != sqlcheck.SpanDropped && p.File.TableSpan(c.T) != sqlcheck.SpanTemporary {
 					diags = append(diags, sqlcheck.Diagnostic{
 						Code: codeDropT,
-						Pos:  sc.Pos,
+						Pos:  sc.Stmt.Pos,
 						Text: fmt.Sprintf("Dropping table %q", c.T.Name),
 					})
 				}
@@ -80,7 +80,7 @@ func (a *Analyzer) Analyze(_ context.Context, p *sqlcheck.Pass) error {
 					if g := (schema.GeneratedExpr{}); !sqlx.Has(d.C.Attrs, &g) || strings.ToUpper(g.Type) != "VIRTUAL" {
 						diags = append(diags, sqlcheck.Diagnostic{
 							Code: codeDropC,
-							Pos:  sc.Pos,
+							Pos:  sc.Stmt.Pos,
 							Text: fmt.Sprintf("Dropping non-virtual column %q", d.C.Name),
 						})
 					}
diff --git a/sql/sqlcheck/sqlcheck.go b/sql/sqlcheck/sqlcheck.go
index f779512c..e71b09d5 100644
--- a/sql/sqlcheck/sqlcheck.go
+++ b/sql/sqlcheck/sqlcheck.go
@@ -58,9 +58,8 @@ type (
 
 	// A Change in a migration file.
 	Change struct {
-		schema.Changes        // The actual changes.
-		Stmt           string // The SQL statement generated this change.
-		Pos            int    // The position of the statement in the file.
+		schema.Changes               // The actual changes.
+		Stmt           *migrate.Stmt // The SQL statement generated this change.
 	}
 
 	// A Report describes an analysis report with an optional specific diagnostic.
diff --git a/sql/sqlite/sqlitecheck/sqlitecheck.go b/sql/sqlite/sqlitecheck/sqlitecheck.go
index 48fc7ecc..fd8e6fa0 100644
--- a/sql/sqlite/sqlitecheck/sqlitecheck.go
+++ b/sql/sqlite/sqlitecheck/sqlitecheck.go
@@ -9,6 +9,8 @@ import (
 	"fmt"
 	"strings"
 
+	"ariga.io/atlas/sql/migrate"
+
 	"ariga.io/atlas/schemahcl"
 	"ariga.io/atlas/sql/schema"
 	"ariga.io/atlas/sql/sqlcheck"
@@ -24,7 +26,7 @@ func addNotNull(p *datadepend.ColumnPass) (diags []sqlcheck.Diagnostic, err erro
 	}
 	return []sqlcheck.Diagnostic{
 		{
-			Pos: p.Change.Pos,
+			Pos: p.Change.Stmt.Pos,
 			Text: fmt.Sprintf(
 				"Adding a non-nullable %q column %q will fail in case table %q is not empty",
 				tt, p.Column.Name, p.Table.Name,
@@ -61,14 +63,16 @@ func init() {
 						return nil
 					}
 					changes = append(changes, &sqlcheck.Change{
-						// Use the position of the first statement.
-						Pos: p.File.Changes[i].Pos,
-						// A combined statement.
-						Stmt: strings.Join([]string{
-							p.File.Changes[i].Stmt,
-							p.File.Changes[i+2].Stmt,
-							p.File.Changes[i+3].Stmt,
-						}, "\n"),
+						Stmt: &migrate.Stmt{
+							// Use the position of the first statement.
+							Pos: p.File.Changes[i].Stmt.Pos,
+							// A combined statement.
+							Text: strings.Join([]string{
+								p.File.Changes[i].Stmt.Text,
+								p.File.Changes[i+2].Stmt.Text,
+								p.File.Changes[i+3].Stmt.Text,
+							}, "\n"),
+						},
 						Changes: schema.Changes{
 							&schema.ModifyTable{
 								T:       currT,


Test patch:
diff --git a/cmd/atlas/internal/cmdapi/migrate_test.go b/cmd/atlas/internal/cmdapi/migrate_test.go
index e4aac93e..c1772056 100644
--- a/cmd/atlas/internal/cmdapi/migrate_test.go
+++ b/cmd/atlas/internal/cmdapi/migrate_test.go
@@ -26,7 +26,6 @@ import (
 	"ariga.io/atlas/sql/sqlite"
 	_ "ariga.io/atlas/sql/sqlite"
 	_ "ariga.io/atlas/sql/sqlite/sqlitecheck"
-
 	"github.com/fatih/color"
 	_ "github.com/mattn/go-sqlite3"
 	"github.com/stretchr/testify/require"
diff --git a/cmd/atlas/internal/cmdapi/schema_test.go b/cmd/atlas/internal/cmdapi/schema_test.go
index 8286b38d..31b7b652 100644
--- a/cmd/atlas/internal/cmdapi/schema_test.go
+++ b/cmd/atlas/internal/cmdapi/schema_test.go
@@ -6,12 +6,16 @@ package cmdapi
 
 import (
 	"bytes"
+	"context"
+	"fmt"
 	"io/ioutil"
 	"os"
 	"path"
 	"path/filepath"
 	"testing"
 
+	"ariga.io/atlas/sql/migrate"
+	"ariga.io/atlas/sql/sqlclient"
 	"github.com/stretchr/testify/require"
 )
 
@@ -103,6 +107,26 @@ func TestFmt(t *testing.T) {
 	}
 }
 
+func TestSchema_Clean(t *testing.T) {
+	var (
+		u      = fmt.Sprintf("sqlite://file:%s?cache=shared&_fk=1", filepath.Join(t.TempDir(), "test.db"))
+		c, err = sqlclient.Open(context.Background(), u)
+	)
+	require.NoError(t, err)
+
+	// Apply migrations onto database.
+	_, err = runCmd(Root, "migrate", "apply", "--dir", "file://testdata/sqlite", "--url", u)
+	require.NoError(t, err)
+
+	// Run clean and expect to be clean.
+	_, err = runCmd(Root, "migrate", "apply", "--dir", "file://testdata/sqlite", "--url", u)
+	require.NoError(t, err)
+	s, err := runCmd(Root, "schema", "clean", "--url", u, "--auto-approve")
+	require.NoError(t, err)
+	require.NotZero(t, s)
+	require.NoError(t, c.Driver.(migrate.CleanChecker).CheckClean(context.Background(), nil))
+}
+
 func runFmt(t *testing.T, args []string) string {
 	var out bytes.Buffer
 	SchemaFmt.ResetCommands() // Detach from sub-commands and parents, needed to skip input validation done by them.
diff --git a/cmd/atlas/internal/lint/lint_test.go b/cmd/atlas/internal/lint/lint_test.go
index 59eb640f..8726f5cb 100644
--- a/cmd/atlas/internal/lint/lint_test.go
+++ b/cmd/atlas/internal/lint/lint_test.go
@@ -141,20 +141,20 @@ func TestDevLoader_LoadChanges(t *testing.T) {
 	// File 1.
 	require.Equal(t, files[0], diff.Files[0].File)
 	require.Len(t, diff.Files[0].Changes, 2)
-	require.Zero(t, diff.Files[0].Changes[0].Pos)
-	require.Equal(t, "CREATE TABLE t1 (id INT)", diff.Files[0].Changes[0].Stmt)
+	require.Zero(t, diff.Files[0].Changes[0].Stmt.Pos)
+	require.Equal(t, "CREATE TABLE t1 (id INT)", diff.Files[0].Changes[0].Stmt.Text)
 	require.IsType(t, (*schema.AddTable)(nil), diff.Files[0].Changes[0].Changes[0])
-	require.Equal(t, "INSERT INTO t1 (id) VALUES (1)", diff.Files[0].Changes[1].Stmt)
+	require.Equal(t, "INSERT INTO t1 (id) VALUES (1)", diff.Files[0].Changes[1].Stmt.Text)
 	require.Empty(t, diff.Files[0].Changes[1].Changes)
 
 	// File 2.
 	require.Equal(t, files[1], diff.Files[1].File)
 	require.Len(t, diff.Files[1].Changes, 2)
-	require.Zero(t, diff.Files[1].Changes[0].Pos)
-	require.Equal(t, "CREATE TABLE t2 (id INT)", diff.Files[1].Changes[0].Stmt)
+	require.Zero(t, diff.Files[1].Changes[0].Stmt.Pos)
+	require.Equal(t, "CREATE TABLE t2 (id INT)", diff.Files[1].Changes[0].Stmt.Text)
 	require.IsType(t, (*schema.AddTable)(nil), diff.Files[1].Changes[0].Changes[0])
-	require.Zero(t, diff.Files[1].Changes[0].Pos)
-	require.Equal(t, "DROP TABLE users", diff.Files[1].Changes[1].Stmt)
+	require.Zero(t, diff.Files[1].Changes[0].Stmt.Pos)
+	require.Equal(t, "DROP TABLE users", diff.Files[1].Changes[1].Stmt.Text)
 	require.IsType(t, (*schema.DropTable)(nil), diff.Files[1].Changes[1].Changes[0])
 
 	// File 3.
diff --git a/cmd/atlas/internal/lint/run_test.go b/cmd/atlas/internal/lint/run_test.go
index 18e84361..f719e2e5 100644
--- a/cmd/atlas/internal/lint/run_test.go
+++ b/cmd/atlas/internal/lint/run_test.go
@@ -48,8 +48,8 @@ func TestRunner_Run(t *testing.T) {
 	require.Len(t, passes, 1)
 	changes := passes[0].File.Changes
 	require.Len(t, changes, 2)
-	require.Equal(t, "CREATE TABLE pets (id INT)", changes[0].Stmt)
-	require.Equal(t, "DROP TABLE users", changes[1].Stmt)
+	require.Equal(t, "CREATE TABLE pets (id INT)", changes[0].Stmt.Text)
+	require.Equal(t, "DROP TABLE users", changes[1].Stmt.Text)
 	require.Equal(t, `2.sql: Report 1:
 
 	L1: Diagnostic 1
diff --git a/sql/migrate/lex_test.go b/sql/migrate/lex_test.go
index ececa4f5..195d5f29 100644
--- a/sql/migrate/lex_test.go
+++ b/sql/migrate/lex_test.go
@@ -29,7 +29,7 @@ func TestLocalFile_Stmts(t *testing.T) {
 }
 
 func TestLocalFile_StmtDecls(t *testing.T) {
-	f := NewLocalFile("f", []byte(`
+	f := `cmd0;
 -- test
 cmd1;
 
@@ -64,23 +64,38 @@ cmd5;
 atlas:lint not a directive
 */
 cmd6;
-`))
-	stmts, err := f.StmtDecls()
+`
+	stmts, err := NewLocalFile("f", []byte(f)).StmtDecls()
 	require.NoError(t, err)
-	require.Len(t, stmts, 6)
-	require.Equal(t, "cmd1;", stmts[0].Text)
-	require.Equal(t, []string{"-- test\n"}, stmts[0].Comments)
-	require.Equal(t, "cmd2;", stmts[1].Text)
-	require.Equal(t, []string{"-- hello\n", "-- world\n"}, stmts[1].Comments)
-	require.Equal(t, "cmd3;", stmts[2].Text)
-	require.Equal(t, []string{"# command\n"}, stmts[2].Comments)
-	require.Equal(t, "cmd4;", stmts[3].Text)
-	require.Equal(t, []string{"/* comment1 */", "/* comment2 */"}, stmts[3].Comments)
-	require.Equal(t, "cmd5;", stmts[4].Text)
-	require.Equal(t, []string{"--atlas:nolint\n", "-- atlas:nolint destructive\n"}, stmts[4].Comments)
-	require.Equal(t, []string{"", "destructive"}, stmts[4].Directive("nolint"))
-	require.Equal(t, "cmd6;", stmts[5].Text)
-	require.Equal(t, []string{"#atlas:lint error\n", "/*atlas:nolint DS101*/", "/* atlas:lint not a directive */", "/*\natlas:lint not a directive\n*/"}, stmts[5].Comments)
-	require.Equal(t, []string{"error"}, stmts[5].Directive("lint"))
-	require.Equal(t, []string{"DS101"}, stmts[5].Directive("nolint"))
+	require.Len(t, stmts, 7)
+
+	require.Equal(t, "cmd0;", stmts[0].Text)
+	require.Equal(t, 0, stmts[0].Pos, "start of the file")
+
+	require.Equal(t, "cmd1;", stmts[1].Text)
+	require.Equal(t, strings.Index(f, "cmd1;"), stmts[1].Pos)
+	require.Equal(t, []string{"-- test\n"}, stmts[1].Comments)
+
+	require.Equal(t, "cmd2;", stmts[2].Text)
+	require.Equal(t, strings.Index(f, "cmd2;"), stmts[2].Pos)
+	require.Equal(t, []string{"-- hello\n", "-- world\n"}, stmts[2].Comments)
+
+	require.Equal(t, "cmd3;", stmts[3].Text)
+	require.Equal(t, strings.Index(f, "cmd3;"), stmts[3].Pos)
+	require.Equal(t, []string{"# command\n"}, stmts[3].Comments)
+
+	require.Equal(t, "cmd4;", stmts[4].Text)
+	require.Equal(t, strings.Index(f, "cmd4;"), stmts[4].Pos)
+	require.Equal(t, []string{"/* comment1 */", "/* comment2 */"}, stmts[4].Comments)
+
+	require.Equal(t, "cmd5;", stmts[5].Text)
+	require.Equal(t, strings.Index(f, "cmd5;"), stmts[5].Pos)
+	require.Equal(t, []string{"--atlas:nolint\n", "-- atlas:nolint destructive\n"}, stmts[5].Comments)
+	require.Equal(t, []string{"", "destructive"}, stmts[5].Directive("nolint"))
+
+	require.Equal(t, "cmd6;", stmts[6].Text)
+	require.Equal(t, strings.Index(f, "cmd6;"), stmts[6].Pos)
+	require.Equal(t, []string{"#atlas:lint error\n", "/*atlas:nolint DS101*/", "/* atlas:lint not a directive */", "/*\natlas:lint not a directive\n*/"}, stmts[6].Comments)
+	require.Equal(t, []string{"error"}, stmts[6].Directive("lint"))
+	require.Equal(t, []string{"DS101"}, stmts[6].Directive("nolint"))
 }
diff --git a/sql/mysql/mysqlcheck/mysqlcheck_test.go b/sql/mysql/mysqlcheck/mysqlcheck_test.go
index f606fea1..32b681cb 100644
--- a/sql/mysql/mysqlcheck/mysqlcheck_test.go
+++ b/sql/mysql/mysqlcheck/mysqlcheck_test.go
@@ -30,7 +30,9 @@ func TestDataDepend_MySQL_ImplicitUpdate(t *testing.T) {
 				File: testFile{name: "1.sql"},
 				Changes: []*sqlcheck.Change{
 					{
-						Stmt: "ALTER TABLE users",
+						Stmt: &migrate.Stmt{
+							Text: "ALTER TABLE users",
+						},
 						Changes: schema.Changes{
 							&schema.ModifyTable{
 								T: schema.NewTable("users").
@@ -84,7 +86,9 @@ func TestDataDepend_MySQL8_ImplicitUpdate(t *testing.T) {
 				File: testFile{name: "1.sql"},
 				Changes: []*sqlcheck.Change{
 					{
-						Stmt: "ALTER TABLE users",
+						Stmt: &migrate.Stmt{
+							Text: "ALTER TABLE users",
+						},
 						Changes: schema.Changes{
 							&schema.ModifyTable{
 								T: schema.NewTable("users").
@@ -128,7 +132,9 @@ func TestDataDepend_MySQL_MightFail(t *testing.T) {
 				File: testFile{name: "1.sql"},
 				Changes: []*sqlcheck.Change{
 					{
-						Stmt: "ALTER TABLE users",
+						Stmt: &migrate.Stmt{
+							Text: "ALTER TABLE users",
+						},
 						Changes: schema.Changes{
 							&schema.ModifyTable{
 								T: schema.NewTable("users").
@@ -176,7 +182,9 @@ func TestDataDepend_Maria_ImplicitUpdate(t *testing.T) {
 				File: testFile{name: "1.sql"},
 				Changes: []*sqlcheck.Change{
 					{
-						Stmt: "ALTER TABLE users",
+						Stmt: &migrate.Stmt{
+							Text: "ALTER TABLE users",
+						},
 						Changes: schema.Changes{
 							&schema.ModifyTable{
 								T: schema.NewTable("users").
diff --git a/sql/postgres/postgrescheck/postgrescheck_test.go b/sql/postgres/postgrescheck/postgrescheck_test.go
index 5c1541d6..b5fac283 100644
--- a/sql/postgres/postgrescheck/postgrescheck_test.go
+++ b/sql/postgres/postgrescheck/postgrescheck_test.go
@@ -25,7 +25,9 @@ func TestDataDepend_MightFail(t *testing.T) {
 				File: testFile{name: "1.sql"},
 				Changes: []*sqlcheck.Change{
 					{
-						Stmt: "ALTER TABLE users",
+						Stmt: &migrate.Stmt{
+							Text: "ALTER TABLE users",
+						},
 						Changes: schema.Changes{
 							&schema.ModifyTable{
 								T: schema.NewTable("users").
diff --git a/sql/sqlcheck/datadepend/datadepend_test.go b/sql/sqlcheck/datadepend/datadepend_test.go
index e349a640..90c30776 100644
--- a/sql/sqlcheck/datadepend/datadepend_test.go
+++ b/sql/sqlcheck/datadepend/datadepend_test.go
@@ -29,7 +29,9 @@ func TestAnalyzer_AddUniqueIndex(t *testing.T) {
 				File: testFile{name: "1.sql"},
 				Changes: []*sqlcheck.Change{
 					{
-						Stmt: "ALTER TABLE users",
+						Stmt: &migrate.Stmt{
+							Text: "ALTER TABLE users",
+						},
 						Changes: schema.Changes{
 							&schema.ModifyTable{
 								T: schema.NewTable("users").
@@ -81,7 +83,9 @@ func TestAnalyzer_ModifyUniqueIndex(t *testing.T) {
 				File: testFile{name: "1.sql"},
 				Changes: []*sqlcheck.Change{
 					{
-						Stmt: "ALTER TABLE users",
+						Stmt: &migrate.Stmt{
+							Text: "ALTER TABLE users",
+						},
 						Changes: schema.Changes{
 							&schema.ModifyTable{
 								T: schema.NewTable("users").
@@ -138,7 +142,9 @@ func TestAnalyzer_Options(t *testing.T) {
 				File: testFile{name: "1.sql"},
 				Changes: []*sqlcheck.Change{
 					{
-						Stmt: "ALTER TABLE users",
+						Stmt: &migrate.Stmt{
+							Text: "ALTER TABLE users",
+						},
 						Changes: schema.Changes{
 							&schema.ModifyTable{
 								T: schema.NewTable("users").
diff --git a/sql/sqlcheck/destructive/destructive_test.go b/sql/sqlcheck/destructive/destructive_test.go
index 5270abe3..e013668e 100644
--- a/sql/sqlcheck/destructive/destructive_test.go
+++ b/sql/sqlcheck/destructive/destructive_test.go
@@ -28,7 +28,9 @@ func TestAnalyzer_DropTable(t *testing.T) {
 				File: testFile{name: "1.sql"},
 				Changes: []*sqlcheck.Change{
 					{
-						Stmt: "DROP TABLE `users`",
+						Stmt: &migrate.Stmt{
+							Text: "DROP TABLE `users`",
+						},
 						Changes: schema.Changes{
 							&schema.DropTable{
 								T: schema.NewTable("users").
@@ -37,7 +39,9 @@ func TestAnalyzer_DropTable(t *testing.T) {
 						},
 					},
 					{
-						Stmt: "DROP TABLE `posts`",
+						Stmt: &migrate.Stmt{
+							Text: "DROP TABLE `posts`",
+						},
 						Changes: schema.Changes{
 							&schema.DropTable{
 								T: schema.NewTable("posts").
@@ -46,7 +50,9 @@ func TestAnalyzer_DropTable(t *testing.T) {
 						},
 					},
 					{
-						Stmt: "CREATE TABLE `posts`",
+						Stmt: &migrate.Stmt{
+							Text: "CREATE TABLE `posts`",
+						},
 						Changes: schema.Changes{
 							&schema.AddTable{
 								T: schema.NewTable("posts").
@@ -80,7 +86,9 @@ func TestAnalyzer_SkipTemporaryTable(t *testing.T) {
 				File: testFile{name: "1.sql"},
 				Changes: []*sqlcheck.Change{
 					{
-						Stmt: "DROP TABLE `users`",
+						Stmt: &migrate.Stmt{
+							Text: "DROP TABLE `users`",
+						},
 						Changes: schema.Changes{
 							&schema.AddTable{
 								T: schema.NewTable("users").
@@ -115,7 +123,9 @@ func TestAnalyzer_DropSchema(t *testing.T) {
 				File: testFile{name: "1.sql"},
 				Changes: []*sqlcheck.Change{
 					{
-						Stmt: "DROP SCHEMA `test`",
+						Stmt: &migrate.Stmt{
+							Text: "DROP SCHEMA `test`",
+						},
 						Changes: schema.Changes{
 							&schema.DropSchema{
 								S: schema.New("test").
@@ -127,7 +137,9 @@ func TestAnalyzer_DropSchema(t *testing.T) {
 						},
 					},
 					{
-						Stmt: "DROP SCHEMA `market`",
+						Stmt: &migrate.Stmt{
+							Text: "DROP SCHEMA `market`",
+						},
 						Changes: schema.Changes{
 							&schema.DropSchema{
 								S: schema.New("market"),
@@ -135,7 +147,9 @@ func TestAnalyzer_DropSchema(t *testing.T) {
 						},
 					},
 					{
-						Stmt: "CREATE DATABASE `market`",
+						Stmt: &migrate.Stmt{
+							Text: "CREATE DATABASE `market`",
+						},
 						Changes: schema.Changes{
 							&schema.AddSchema{
 								S: schema.New("market"),
@@ -177,7 +191,9 @@ func TestAnalyzer_DropColumn(t *testing.T) {
 				File: testFile{name: "1.sql"},
 				Changes: []*sqlcheck.Change{
 					{
-						Stmt: "ALTER TABLE `pets`",
+						Stmt: &migrate.Stmt{
+							Text: "ALTER TABLE `pets`",
+						},
 						Changes: []schema.Change{
 							&schema.ModifyTable{
 								T: schema.NewTable("pets").
@@ -192,7 +208,9 @@ func TestAnalyzer_DropColumn(t *testing.T) {
 						},
 					},
 					{
-						Stmt: "ALTER TABLE `pets`",
+						Stmt: &migrate.Stmt{
+							Text: "ALTER TABLE `pets`",
+						},
 						Changes: []schema.Change{
 							&schema.ModifySchema{
 								S: schema.New("test"),
diff --git a/sql/sqlite/sqlitecheck/sqlitecheck_test.go b/sql/sqlite/sqlitecheck/sqlitecheck_test.go
index da3e04ee..46ffb6e0 100644
--- a/sql/sqlite/sqlitecheck/sqlitecheck_test.go
+++ b/sql/sqlite/sqlitecheck/sqlitecheck_test.go
@@ -35,7 +35,9 @@ func TestDetectModifyTable(t *testing.T) {
 				Changes: []*sqlcheck.Change{
 					// A real drop.
 					{
-						Stmt: "DROP TABLE `users`",
+						Stmt: &migrate.Stmt{
+							Text: "DROP TABLE `users`",
+						},
 						Changes: schema.Changes{
 							&schema.DropTable{
 								T: schema.NewTable("users").
@@ -45,10 +47,14 @@ func TestDetectModifyTable(t *testing.T) {
 					},
 					// Table modification using a temporary table.
 					{
-						Stmt: "PRAGMA foreign_keys = off;",
+						Stmt: &migrate.Stmt{
+							Text: "PRAGMA foreign_keys = off;",
+						},
 					},
 					{
-						Stmt: "CREATE TABLE `new_posts` (`text` text NOT NULL);",
+						Stmt: &migrate.Stmt{
+							Text: "CREATE TABLE `new_posts` (`text` text NOT NULL);",
+						},
 						Changes: schema.Changes{
 							&schema.AddTable{
 								T: schema.NewTable("new_posts").
@@ -58,10 +64,14 @@ func TestDetectModifyTable(t *testing.T) {
 						},
 					},
 					{
-						Stmt: "INSERT INTO `new_posts` (`text`) SELECT `text` FROM `posts`;",
+						Stmt: &migrate.Stmt{
+							Text: "INSERT INTO `new_posts` (`text`) SELECT `text` FROM `posts`;",
+						},
 					},
 					{
-						Stmt: "DROP TABLE `posts`",
+						Stmt: &migrate.Stmt{
+							Text: "DROP TABLE `posts`",
+						},
 						Changes: schema.Changes{
 							&schema.DropTable{
 								T: schema.NewTable("posts").
@@ -74,7 +84,9 @@ func TestDetectModifyTable(t *testing.T) {
 						},
 					},
 					{
-						Stmt: "ALTER TABLE `new_posts` RENAME TO `posts`;",
+						Stmt: &migrate.Stmt{
+							Text: "ALTER TABLE `new_posts` RENAME TO `posts`;",
+						},
 						Changes: schema.Changes{
 							&schema.DropTable{
 								T: schema.NewTable("new_posts").
@@ -89,11 +101,15 @@ func TestDetectModifyTable(t *testing.T) {
 						},
 					},
 					{
-						Stmt: "PRAGMA foreign_keys = on;",
+						Stmt: &migrate.Stmt{
+							Text: "PRAGMA foreign_keys = on;",
+						},
 					},
 					// Another real drop.
 					{
-						Stmt: "DROP TABLE `pets`",
+						Stmt: &migrate.Stmt{
+							Text: "DROP TABLE `pets`",
+						},
 						Changes: schema.Changes{
 							&schema.DropTable{
 								T: schema.NewTable("pets").