Repository: ariga/atlas
Pull request #531 (2022-01-30 14:59:00)

internal/integration: initial testscript testing for pg
Moved parts are existing code that moved from integration_test to script_test.


Patch:
diff --git a/cmd/action/apply.go b/cmd/action/apply.go
index e9f8152a..938b4c60 100644
--- a/cmd/action/apply.go
+++ b/cmd/action/apply.go
@@ -1,3 +1,7 @@
+// 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 action
 
 import (
diff --git a/cmd/action/diff.go b/cmd/action/diff.go
index df2fb77c..ef5319d6 100644
--- a/cmd/action/diff.go
+++ b/cmd/action/diff.go
@@ -11,41 +11,46 @@ import (
 	"github.com/spf13/cobra"
 )
 
-var (
-	// DiffFlags are the flags used in the Diff command.
-	DiffFlags struct {
-		FromDSN string
-		ToDSN   string
-	}
-	// DiffCmd represents the diff command.
-	DiffCmd = &cobra.Command{
+type diffCmdOpts struct {
+	fromDSN string
+	toDSN   string
+}
+
+// newDiffCmd returns a new *cobra.Command that runs cmdDiffRun with the given flags and mux.
+func newDiffCmd() *cobra.Command {
+	var opts diffCmdOpts
+	cmd := &cobra.Command{
 		Use:   "diff",
 		Short: "Calculate and print the diff between two schemas.",
 		Long: "`atlas schema diff`" + ` connects to two given databases, inspects
 them, calculates the difference in their schemas, and prints a plan of
 SQL queries to bring the "from" database to the schema of the "to" database.`,
-		Run: CmdDiffRun,
+		Run: func(cmd *cobra.Command, args []string) {
+			cmdDiffRun(cmd, &opts)
+		},
 	}
-)
+	cmd.Flags().StringVarP(&opts.fromDSN, "from", "", "", "[driver://username:password@protocol(address)/dbname?param=value] Select data source using the dsn format")
+	cmd.Flags().StringVarP(&opts.toDSN, "to", "", "", "[driver://username:password@protocol(address)/dbname?param=value] Select data source using the dsn format")
+	cobra.CheckErr(cmd.MarkFlagRequired("from"))
+	cobra.CheckErr(cmd.MarkFlagRequired("to"))
+	return cmd
+}
 
 func init() {
-	schemaCmd.AddCommand(DiffCmd)
-	DiffCmd.Flags().StringVarP(&DiffFlags.FromDSN, "from", "", "", "[driver://username:password@protocol(address)/dbname?param=value] Select data source using the dsn format")
-	DiffCmd.Flags().StringVarP(&DiffFlags.ToDSN, "to", "", "", "[driver://username:password@protocol(address)/dbname?param=value] Select data source using the dsn format")
-	cobra.CheckErr(DiffCmd.MarkFlagRequired("from"))
-	cobra.CheckErr(DiffCmd.MarkFlagRequired("to"))
+	diffCmd := newDiffCmd()
+	schemaCmd.AddCommand(diffCmd)
 }
 
-// CmdDiffRun connects to the given databases, and prints an SQL plan to get from
+// cmdDiffRun connects to the given databases, and prints an SQL plan to get from
 // the "from" schema to the "to" schema.
-func CmdDiffRun(cmd *cobra.Command, args []string) {
-	fromDriver, err := defaultMux.OpenAtlas(DiffFlags.FromDSN)
+func cmdDiffRun(cmd *cobra.Command, flags *diffCmdOpts) {
+	fromDriver, err := defaultMux.OpenAtlas(flags.fromDSN)
 	cobra.CheckErr(err)
-	toDriver, err := defaultMux.OpenAtlas(DiffFlags.ToDSN)
+	toDriver, err := defaultMux.OpenAtlas(flags.toDSN)
 	cobra.CheckErr(err)
-	fromName, err := SchemaNameFromDSN(DiffFlags.FromDSN)
+	fromName, err := SchemaNameFromDSN(flags.fromDSN)
 	cobra.CheckErr(err)
-	toName, err := SchemaNameFromDSN(DiffFlags.ToDSN)
+	toName, err := SchemaNameFromDSN(flags.toDSN)
 	cobra.CheckErr(err)
 	ctx := context.Background()
 	fromSchema, err := fromDriver.InspectSchema(ctx, fromName, nil)
@@ -64,7 +69,7 @@ func CmdDiffRun(cmd *cobra.Command, args []string) {
 	p, err := toDriver.PlanChanges(ctx, "plan", diff)
 	cobra.CheckErr(err)
 	if len(p.Changes) == 0 {
-		schemaCmd.Println("Schemas are synced, no changes to be made.")
+		cmd.Println("Schemas are synced, no changes to be made.")
 		return
 	}
 	for _, c := range p.Changes {
diff --git a/cmd/action/env.go b/cmd/action/env.go
index 537afe41..0a1dfaf6 100644
--- a/cmd/action/env.go
+++ b/cmd/action/env.go
@@ -1,3 +1,7 @@
+// 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 action
 
 import (
diff --git a/cmd/action/inspect.go b/cmd/action/inspect.go
index 4dd48c55..76e1dbea 100644
--- a/cmd/action/inspect.go
+++ b/cmd/action/inspect.go
@@ -1,3 +1,7 @@
+// 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 action
 
 import (
diff --git a/cmd/action/mux.go b/cmd/action/mux.go
index ae33aecd..158e316f 100644
--- a/cmd/action/mux.go
+++ b/cmd/action/mux.go
@@ -1,3 +1,7 @@
+// 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 action
 
 import (
diff --git a/cmd/action/provider.go b/cmd/action/provider.go
index 619e46c1..f3290bbe 100644
--- a/cmd/action/provider.go
+++ b/cmd/action/provider.go
@@ -1,3 +1,7 @@
+// 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 action
 
 import (
diff --git a/cmd/action/root.go b/cmd/action/root.go
index 754f5721..6ce5e1e9 100644
--- a/cmd/action/root.go
+++ b/cmd/action/root.go
@@ -1,3 +1,7 @@
+// 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 action
 
 import (
diff --git a/cmd/action/schema.go b/cmd/action/schema.go
index 471a2035..b4ca2fe5 100644
--- a/cmd/action/schema.go
+++ b/cmd/action/schema.go
@@ -1,3 +1,7 @@
+// 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 action
 
 import (
diff --git a/cmd/action/version.go b/cmd/action/version.go
index edf78ec0..db7539c3 100644
--- a/cmd/action/version.go
+++ b/cmd/action/version.go
@@ -1,3 +1,7 @@
+// 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 action
 
 import (
diff --git a/sql/internal/sqlx/diff.go b/sql/internal/sqlx/diff.go
index 9f6e948c..203cc75e 100644
--- a/sql/internal/sqlx/diff.go
+++ b/sql/internal/sqlx/diff.go
@@ -44,7 +44,7 @@ type (
 
 		// IndexPartAttrChanged reports if the index-part attributes were
 		// changed. For example, an index-part collation.
-		IndexPartAttrChanged(from, to []schema.Attr) bool
+		IndexPartAttrChanged(from, to *schema.IndexPart) bool
 
 		// IsGeneratedIndexName reports if the index name was generated by the database
 		// for unnamed INDEX or UNIQUE constraints. In such cases, the differ will look
@@ -291,7 +291,7 @@ func (d *Diff) partsChange(from, to []*schema.IndexPart) schema.ChangeKind {
 	sort.Slice(from, func(i, j int) bool { return from[i].SeqNo < from[j].SeqNo })
 	for i := range from {
 		switch {
-		case from[i].Desc != to[i].Desc || d.IndexPartAttrChanged(from[i].Attrs, to[i].Attrs):
+		case from[i].Desc != to[i].Desc || d.IndexPartAttrChanged(from[i], to[i]):
 			return schema.ChangeParts
 		case from[i].C != nil && to[i].C != nil:
 			if from[i].C.Name != to[i].C.Name {
diff --git a/sql/mysql/diff.go b/sql/mysql/diff.go
index 6358cbf8..cb634ea3 100644
--- a/sql/mysql/diff.go
+++ b/sql/mysql/diff.go
@@ -129,9 +129,9 @@ func (*diff) IndexAttrChanged(from, to []schema.Attr) bool {
 }
 
 // IndexPartAttrChanged reports if the index-part attributes (collation or prefix) were changed.
-func (*diff) IndexPartAttrChanged(from, to []schema.Attr) bool {
+func (*diff) IndexPartAttrChanged(from, to *schema.IndexPart) bool {
 	var s1, s2 SubPart
-	return sqlx.Has(from, &s1) != sqlx.Has(to, &s2) || s1.Len != s2.Len
+	return sqlx.Has(from.Attrs, &s1) != sqlx.Has(to.Attrs, &s2) || s1.Len != s2.Len
 }
 
 // ReferenceChanged reports if the foreign key referential action was changed.
diff --git a/sql/postgres/diff.go b/sql/postgres/diff.go
index d45cc6ea..96dd28e4 100644
--- a/sql/postgres/diff.go
+++ b/sql/postgres/diff.go
@@ -122,11 +122,11 @@ func (*diff) IndexAttrChanged(from, to []schema.Attr) bool {
 }
 
 // IndexPartAttrChanged reports if the index-part attributes were changed.
-func (*diff) IndexPartAttrChanged(from, to []schema.Attr) bool {
-	p1 := &IndexColumnProperty{NullsLast: true}
-	sqlx.Has(from, p1)
-	p2 := &IndexColumnProperty{NullsLast: true}
-	sqlx.Has(to, p2)
+func (*diff) IndexPartAttrChanged(from, to *schema.IndexPart) bool {
+	p1 := &IndexColumnProperty{NullsFirst: from.Desc, NullsLast: !from.Desc}
+	sqlx.Has(from.Attrs, p1)
+	p2 := &IndexColumnProperty{NullsFirst: to.Desc, NullsLast: !to.Desc}
+	sqlx.Has(to.Attrs, p2)
 	return p1.NullsFirst != p2.NullsFirst || p1.NullsLast != p2.NullsLast
 }
 
diff --git a/sql/postgres/inspect.go b/sql/postgres/inspect.go
index 63c6742a..59be3b89 100644
--- a/sql/postgres/inspect.go
+++ b/sql/postgres/inspect.go
@@ -682,8 +682,10 @@ type (
 	// https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-INDEX-COLUMN-PROPS
 	IndexColumnProperty struct {
 		schema.Attr
+		// NullsFirst defaults to true for DESC indexes.
 		NullsFirst bool
-		NullsLast  bool
+		// NullsLast defaults to true for ASC indexes.
+		NullsLast bool
 	}
 
 	// NoInherit attribute defines the NO INHERIT flag for CHECK constraint.
diff --git a/sql/postgres/migrate.go b/sql/postgres/migrate.go
index b5e6a08d..a13797dd 100644
--- a/sql/postgres/migrate.go
+++ b/sql/postgres/migrate.go
@@ -482,8 +482,22 @@ func (s *state) addIndexes(t *schema.Table, indexes ...*schema.Index) {
 		s.indexAttrs(b, idx.Attrs)
 		s.append(&migrate.Change{
 			Cmd:     b.String(),
-			Reverse: Build("DROP INDEX").Ident(idx.Name).String(),
 			Comment: fmt.Sprintf("Create index %q to table: %q", idx.Name, t.Name),
+			Reverse: func() string {
+				b := Build("DROP INDEX")
+				// Unlike MySQL, the DROP command is not attached to ALTER TABLE.
+				// Therefore, we print indexes with their qualified name, because
+				// the connection that executes the statements may not be attached
+				// to the this schema.
+				if t.Schema != nil {
+					b.WriteByte(b.QuoteChar)
+					b.WriteString(t.Schema.Name)
+					b.WriteByte(b.QuoteChar)
+					b.WriteByte('.')
+				}
+				b.Ident(idx.Name)
+				return b.String()
+			}(),
 		})
 	}
 }
diff --git a/sql/sqlite/diff.go b/sql/sqlite/diff.go
index 3be6de8d..bd51110a 100644
--- a/sql/sqlite/diff.go
+++ b/sql/sqlite/diff.go
@@ -105,7 +105,7 @@ func (*diff) IndexAttrChanged(from, to []schema.Attr) bool {
 }
 
 // IndexPartAttrChanged reports if the index-part attributes were changed.
-func (*diff) IndexPartAttrChanged(_, _ []schema.Attr) bool {
+func (*diff) IndexPartAttrChanged(_, _ *schema.IndexPart) bool {
 	return false
 }
 


Test patch:
diff --git a/cmd/action/diff_test.go b/cmd/action/diff_test.go
new file mode 100644
index 00000000..d94107e6
--- /dev/null
+++ b/cmd/action/diff_test.go
@@ -0,0 +1,67 @@
+// 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 action
+
+import (
+	"bytes"
+	"context"
+	"database/sql"
+	"fmt"
+	"io/ioutil"
+	"os"
+	"testing"
+
+	"ariga.io/atlas/sql/sqlite"
+	"github.com/spf13/cobra"
+	"github.com/stretchr/testify/require"
+)
+
+func TestDiffCmd_Diff(t *testing.T) {
+	from := openSQLite(t, "")
+	to := openSQLite(t, "create table t1 (id int);")
+	cmd := newDiffCmd()
+	s, err := runCmd(cmd, "schema", "diff", "--from", from, "--to", to)
+	require.NoError(t, err)
+	require.EqualValues(t, "-- Create \"t1\" table\nCREATE TABLE `t1` (`id` int NULL)\n", s)
+}
+
+func TestDiffCmd_Synced(t *testing.T) {
+	from := openSQLite(t, "")
+	to := openSQLite(t, "")
+	cmd := newDiffCmd()
+	s, err := runCmd(cmd, "schema", "diff", "--from", from, "--to", to)
+	require.NoError(t, err)
+	require.EqualValues(t, "Schemas are synced, no changes to be made.\n", s)
+}
+
+// openSQLite creates a sqlite db, seeds it with the seed query and returns the dsn to it.
+func openSQLite(t *testing.T, seed string) string {
+	f, err := ioutil.TempFile("", "sqlite.db")
+	require.NoError(t, err)
+	t.Cleanup(func() {
+		os.Remove(f.Name())
+	})
+	dsn := fmt.Sprintf("file:%s?cache=shared&_fk=1", f.Name())
+	db, err := sql.Open("sqlite3", dsn)
+	require.NoError(t, err)
+	t.Cleanup(func() {
+		db.Close()
+	})
+	drv, err := sqlite.Open(db)
+	require.NoError(t, err)
+	if len(seed) > 0 {
+		_, err := drv.ExecContext(context.Background(), seed)
+		require.NoError(t, err)
+	}
+	return fmt.Sprintf("sqlite://%s", dsn)
+}
+
+func runCmd(cmd *cobra.Command, args... string) (string, error) {
+	var out bytes.Buffer
+	cmd.SetOut(&out)
+	cmd.SetArgs(args)
+	err := cmd.Execute()
+	return out.String(), err
+}
diff --git a/cmd/action/mux_test.go b/cmd/action/mux_test.go
index 639fa849..954183f1 100644
--- a/cmd/action/mux_test.go
+++ b/cmd/action/mux_test.go
@@ -1,3 +1,7 @@
+// 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 action_test
 
 import (
@@ -121,7 +125,7 @@ func Test_PostgresSchemaDSN(t *testing.T) {
 	var tests = []struct {
 		dsn      string
 		expected string
-		err      bool
+		wantErr  bool
 	}{
 		{
 			dsn:      "postgres://localhost:5432/dbname?search_path=foo",
@@ -134,7 +138,7 @@ func Test_PostgresSchemaDSN(t *testing.T) {
 		{
 			dsn:      "postgres://(bad:host)?search_path=foo",
 			expected: "",
-			err:      true,
+			wantErr:  true,
 		},
 		{
 			dsn:      "postgres://localhost:5432/dbname?search_path=",
@@ -144,11 +148,7 @@ func Test_PostgresSchemaDSN(t *testing.T) {
 	for _, tt := range tests {
 		t.Run(tt.dsn, func(t *testing.T) {
 			schema, err := action.SchemaNameFromDSN(tt.dsn)
-			if tt.err {
-				require.Error(t, err)
-			} else {
-				require.NoError(t, err)
-			}
+			require.Equal(t, tt.wantErr, err != nil)
 			require.Equal(t, tt.expected, schema)
 		})
 	}
diff --git a/internal/integration/mysql_test.go b/internal/integration/mysql_test.go
index be7898c5..9344b38c 100644
--- a/internal/integration/mysql_test.go
+++ b/internal/integration/mysql_test.go
@@ -8,9 +8,6 @@ import (
 	"context"
 	"database/sql"
 	"fmt"
-	"os"
-	"path/filepath"
-	"regexp"
 	"strings"
 	"sync"
 	"testing"
@@ -20,8 +17,6 @@ import (
 
 	"entgo.io/ent/dialect"
 	_ "github.com/go-sql-driver/mysql"
-	"github.com/pkg/diff"
-	"github.com/rogpeppe/go-internal/testscript"
 	"github.com/stretchr/testify/require"
 )
 
@@ -1318,161 +1313,3 @@ func rmCreateStmt(t *schema.Table) {
 		}
 	}
 }
-
-func TestMySQL_Script(t *testing.T) {
-	myRun(t, func(t *myTest) {
-		var (
-			attrs            = t.defaultAttrs()
-			charset, collate = attrs[0].(*schema.Charset).V, attrs[1].(*schema.Collation).V
-		)
-		testscript.Run(t.T, testscript.Params{
-			Dir: "testdata/mysql",
-			Setup: func(env *testscript.Env) error {
-				ctx := context.Background()
-				conn, err := t.db.Conn(ctx)
-				if err != nil {
-					return err
-				}
-				name := strings.ReplaceAll(filepath.Base(env.WorkDir), "-", "_")
-				env.Setenv("db", name)
-				env.Setenv("charset", charset)
-				env.Setenv("collate", collate)
-				if _, err := conn.ExecContext(ctx, fmt.Sprintf("CREATE DATABASE IF NOT EXISTS `%s`", name)); err != nil {
-					return err
-				}
-				if _, err := conn.ExecContext(ctx, fmt.Sprintf("USE `%s`", name)); err != nil {
-					return err
-				}
-				env.Defer(func() {
-					if _, err := conn.ExecContext(ctx, fmt.Sprintf("DROP DATABASE IF EXISTS `%s`", name)); err != nil {
-						t.Fatal(err)
-					}
-					if _, err := conn.ExecContext(ctx, "USE test"); err != nil {
-						t.Fatal(err)
-					}
-					if err := conn.Close(); err != nil {
-						t.Fatal(err)
-					}
-				})
-				// Store the testscript.T for later use.
-				// See "only" function below.
-				env.Values[keyT] = env.T()
-				return nil
-			},
-			Cmds: map[string]func(ts *testscript.TestScript, neg bool, args []string){
-				"only":    t.cmdOnly,
-				"apply":   t.cmdApply,
-				"exist":   t.cmdExist,
-				"synced":  t.cmdSynced,
-				"cmpshow": t.cmdCmpShow,
-			},
-		})
-	})
-}
-
-var keyT struct{}
-
-// cmdOnly executes only tests that their driver version matches the given pattern.
-// For example, "only 8" or "only 8 maria*"
-func (t *myTest) cmdOnly(ts *testscript.TestScript, neg bool, args []string) {
-	if neg {
-		ts.Fatalf("unsupported: ! only")
-	}
-	for i := range args {
-		re, rerr := regexp.Compile(`(?mi)` + args[i])
-		ts.Check(rerr)
-		if re.MatchString(t.version) {
-			return
-		}
-	}
-	// This is not an elegant way to get the created testing.T for the script,
-	// but we need some workaround to get it in order to skip specific tests.
-	ts.Value(keyT).(testscript.T).Skip("skip version", t.version)
-}
-
-func (t *myTest) cmdCmpShow(ts *testscript.TestScript, _ bool, args []string) {
-	if len(args) < 2 {
-		ts.Fatalf("invalid number of args to 'cmpshow': %d", len(args))
-	}
-	var (
-		fname = args[len(args)-1]
-		stmts = make([]string, 0, len(args)-1)
-	)
-	for _, name := range args[:len(args)-1] {
-		var create string
-		if err := t.db.QueryRow(fmt.Sprintf("SHOW CREATE TABLE `%s`.`%s`", ts.Getenv("db"), name)).Scan(&name, &create); err != nil {
-			ts.Fatalf("show table %q: %v", name, err)
-		}
-		// Trim the "table_options" if it was not requested explicitly.
-		stmts = append(stmts, create[:strings.LastIndexByte(create, ')')+1])
-	}
-
-	// Check if there is a file prefixed by database version (1.sql and <version>/1.sql).
-	if _, err := os.Stat(ts.MkAbs(filepath.Join(t.version, fname))); err == nil {
-		fname = filepath.Join(t.version, fname)
-	}
-	t1, t2 := strings.Join(stmts, "\n"), ts.ReadFile(fname)
-	if strings.TrimSpace(t1) == strings.TrimSpace(t2) {
-		return
-	}
-	var sb strings.Builder
-	ts.Check(diff.Text("show", fname, t1, t2, &sb))
-	ts.Fatalf(sb.String())
-}
-
-func (t *myTest) cmdExist(ts *testscript.TestScript, neg bool, args []string) {
-	for _, name := range args {
-		var b bool
-		if err := t.db.QueryRow("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?", ts.Getenv("db"), name).Scan(&b); err != nil {
-			ts.Fatalf("failed query table existence %q: %v", name, err)
-		}
-		if !b != neg {
-			ts.Fatalf("table %q existence failed", name)
-		}
-	}
-}
-
-func (t *myTest) cmdSynced(ts *testscript.TestScript, neg bool, args []string) {
-	switch changes := t.hclDiff(ts, args[0]); {
-	case len(changes) > 0 && !neg:
-		ts.Fatalf("expect no schema changes, but got: %d", len(changes))
-	case len(changes) == 0 && neg:
-		ts.Fatalf("expect schema changes, but there are none")
-	}
-}
-
-func (t *myTest) cmdApply(ts *testscript.TestScript, neg bool, args []string) {
-	changes := t.hclDiff(ts, args[0])
-	switch err := t.drv.ApplyChanges(context.Background(), changes); {
-	case err != nil && !neg:
-		ts.Fatalf("apply changes: %v", err)
-	case err == nil && neg:
-		ts.Fatalf("unexpected apply success")
-	// If we expect to fail, and there's a specific error to compare.
-	case err != nil && len(args) == 2:
-		re, rerr := regexp.Compile(`(?m)` + args[1])
-		ts.Check(rerr)
-		if !re.MatchString(err.Error()) {
-			t.Fatalf("mismatched errors: %v != %s", err, args[1])
-		}
-	// Apply passed. Make sure there is no drift.
-	case !neg:
-		if changes := t.hclDiff(ts, args[0]); len(changes) > 0 {
-			ts.Fatalf("unexpected schema changes: %d", len(changes))
-		}
-	}
-}
-
-func (t *myTest) hclDiff(ts *testscript.TestScript, name string) []schema.Change {
-	var (
-		desired schema.Schema
-		f       = ts.ReadFile(name)
-		r       = strings.NewReplacer("$charset", ts.Getenv("charset"), "$collate", ts.Getenv("collate"), "$db", ts.Getenv("db"))
-	)
-	ts.Check(mysql.UnmarshalHCL([]byte(r.Replace(f)), &desired))
-	current, err := t.drv.InspectSchema(context.Background(), desired.Name, nil)
-	ts.Check(err)
-	changes, err := t.drv.SchemaDiff(current, &desired)
-	ts.Check(err)
-	return changes
-}
diff --git a/internal/integration/script_test.go b/internal/integration/script_test.go
new file mode 100644
index 00000000..17071305
--- /dev/null
+++ b/internal/integration/script_test.go
@@ -0,0 +1,287 @@
+// 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 integration
+
+import (
+	"bytes"
+	"context"
+	"database/sql"
+	"fmt"
+	"os"
+	"os/exec"
+	"path/filepath"
+	"regexp"
+	"strings"
+	"testing"
+	"unicode"
+
+	"ariga.io/atlas/sql/mysql"
+	"ariga.io/atlas/sql/postgres"
+	"ariga.io/atlas/sql/schema"
+
+	"github.com/pkg/diff"
+	"github.com/rogpeppe/go-internal/testscript"
+)
+
+func TestMySQL_Script(t *testing.T) {
+	myRun(t, func(t *myTest) {
+		testscript.Run(t.T, testscript.Params{
+			Dir:   "testdata/mysql",
+			Setup: t.setupScript,
+			Cmds: map[string]func(ts *testscript.TestScript, neg bool, args []string){
+				"only":    cmdOnly,
+				"apply":   t.cmdApply,
+				"exist":   t.cmdExist,
+				"synced":  t.cmdSynced,
+				"cmpshow": t.cmdCmpShow,
+			},
+		})
+	})
+}
+
+func TestPostgresScript(t *testing.T) {
+	pgRun(t, func(t *pgTest) {
+		testscript.Run(t.T, testscript.Params{
+			Dir:   "testdata/postgres",
+			Setup: t.setupScript,
+			Cmds: map[string]func(ts *testscript.TestScript, neg bool, args []string){
+				"only":    cmdOnly,
+				"apply":   t.cmdApply,
+				"exist":   t.cmdExist,
+				"synced":  t.cmdSynced,
+				"cmpshow": t.cmdCmpShow,
+			},
+		})
+	})
+}
+
+var keyT struct{}
+
+func (t *myTest) setupScript(env *testscript.Env) error {
+	attrs := t.defaultAttrs()
+	env.Setenv("version", t.version)
+	env.Setenv("charset", attrs[0].(*schema.Charset).V)
+	env.Setenv("collate", attrs[1].(*schema.Collation).V)
+	return setupScript(t.T, env, t.db, "DROP SCHEMA IF EXISTS %s")
+}
+
+func (t *pgTest) setupScript(env *testscript.Env) error {
+	env.Setenv("version", t.version)
+	return setupScript(t.T, env, t.db, "DROP SCHEMA IF EXISTS %s CASCADE")
+}
+
+func setupScript(t *testing.T, env *testscript.Env, db *sql.DB, dropCmd string) error {
+	ctx := context.Background()
+	conn, err := db.Conn(ctx)
+	if err != nil {
+		return err
+	}
+	name := strings.ReplaceAll(filepath.Base(env.WorkDir), "-", "_")
+	env.Setenv("db", name)
+	if _, err := conn.ExecContext(ctx, fmt.Sprintf("CREATE SCHEMA IF NOT EXISTS %s", name)); err != nil {
+		return err
+	}
+	env.Defer(func() {
+		if _, err := conn.ExecContext(ctx, fmt.Sprintf(dropCmd, name)); err != nil {
+			t.Fatal(err)
+		}
+		if err := conn.Close(); err != nil {
+			t.Fatal(err)
+		}
+	})
+	// Store the testscript.T for later use.
+	// See "only" function below.
+	env.Values[keyT] = env.T()
+	return nil
+}
+
+// cmdOnly executes only tests that their driver version matches the given pattern.
+// For example, "only 8" or "only 8 maria*"
+func cmdOnly(ts *testscript.TestScript, neg bool, args []string) {
+	if neg {
+		ts.Fatalf("unsupported: ! only")
+	}
+	ver := ts.Getenv("version")
+	for i := range args {
+		re, rerr := regexp.Compile(`(?mi)` + args[i])
+		ts.Check(rerr)
+		if re.MatchString(ver) {
+			return
+		}
+	}
+	// This is not an elegant way to get the created testing.T for the script,
+	// but we need some workaround to get it in order to skip specific tests.
+	ts.Value(keyT).(testscript.T).Skip("skip version", ver)
+}
+
+func (t *myTest) cmdCmpShow(ts *testscript.TestScript, _ bool, args []string) {
+	cmdCmpShow(ts, args, func(schema, name string) (string, error) {
+		var create 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
+	})
+}
+
+func (t *pgTest) cmdCmpShow(ts *testscript.TestScript, _ bool, args []string) {
+	cmdCmpShow(ts, args, func(schema, name string) (string, error) {
+		buf, err := exec.Command("docker", "ps", "-qa", "-f", fmt.Sprintf("name=postgres%s", t.version)).CombinedOutput()
+		if err != nil {
+			return "", fmt.Errorf("get container id %q: %v", buf, err)
+		}
+		cmd := exec.Command("docker", "exec", string(bytes.TrimSpace(buf)), "psql", "-U", "postgres", "-d", "test", "-c", fmt.Sprintf(`\d %s.%s`, schema, name))
+		// Use "cmd.String" to debug command.
+		buf, err = cmd.CombinedOutput()
+		if err != nil {
+			return "", err
+		}
+		lines := strings.Split(string(buf), "\n")
+		for i := range lines {
+			lines[i] = strings.TrimRightFunc(lines[i], unicode.IsSpace)
+		}
+		return strings.Join(lines, "\n"), err
+	})
+}
+
+func cmdCmpShow(ts *testscript.TestScript, args []string, show func(schema, name string) (string, error)) {
+	if len(args) < 2 {
+		ts.Fatalf("invalid number of args to 'cmpshow': %d", len(args))
+	}
+	var (
+		ver   = ts.Getenv("version")
+		fname = args[len(args)-1]
+		stmts = make([]string, 0, len(args)-1)
+	)
+	for _, name := range args[:len(args)-1] {
+		create, err := show(ts.Getenv("db"), name)
+		if err != nil {
+			ts.Fatalf("show table %q: %v", name, err)
+		}
+		stmts = append(stmts, create)
+	}
+
+	// Check if there is a file prefixed by database version (1.sql and <version>/1.sql).
+	if _, err := os.Stat(ts.MkAbs(filepath.Join(ver, fname))); err == nil {
+		fname = filepath.Join(ver, fname)
+	}
+	t1, t2 := strings.Join(stmts, "\n"), ts.ReadFile(fname)
+	if strings.TrimSpace(t1) == strings.TrimSpace(t2) {
+		return
+	}
+	var sb strings.Builder
+	ts.Check(diff.Text("show", fname, t1, t2, &sb))
+	ts.Fatalf(sb.String())
+}
+
+func (t *myTest) cmdExist(ts *testscript.TestScript, neg bool, args []string) {
+	cmdExist(ts, neg, args, func(schema, name string) (bool, error) {
+		var b bool
+		if err := t.db.QueryRow("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?", schema, name).Scan(&b); err != nil {
+			return false, err
+		}
+		return b, nil
+	})
+}
+
+func (t *pgTest) cmdExist(ts *testscript.TestScript, neg bool, args []string) {
+	cmdExist(ts, neg, args, func(schema, name string) (bool, error) {
+		var b bool
+		if err := t.db.QueryRow("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = $1 AND TABLE_NAME = $2", schema, name).Scan(&b); err != nil {
+			return false, err
+		}
+		return b, nil
+	})
+}
+
+func cmdExist(ts *testscript.TestScript, neg bool, args []string, exists func(schema, name string) (bool, error)) {
+	for _, name := range args {
+		b, err := exists(ts.Getenv("db"), name)
+		if err != nil {
+			ts.Fatalf("failed query table existence %q: %v", name, err)
+		}
+		if !b != neg {
+			ts.Fatalf("table %q existence failed", name)
+		}
+	}
+}
+
+func (t *myTest) cmdSynced(ts *testscript.TestScript, neg bool, args []string) {
+	cmdSynced(ts, neg, args, t.hclDiff)
+}
+
+func (t *myTest) cmdApply(ts *testscript.TestScript, neg bool, args []string) {
+	cmdApply(ts, neg, args, t.drv.ApplyChanges, t.hclDiff)
+}
+
+func (t *myTest) hclDiff(ts *testscript.TestScript, name string) []schema.Change {
+	var (
+		desired schema.Schema
+		f       = ts.ReadFile(name)
+		r       = strings.NewReplacer("$charset", ts.Getenv("charset"), "$collate", ts.Getenv("collate"), "$db", ts.Getenv("db"))
+	)
+	ts.Check(mysql.UnmarshalHCL([]byte(r.Replace(f)), &desired))
+	current, err := t.drv.InspectSchema(context.Background(), desired.Name, nil)
+	ts.Check(err)
+	changes, err := t.drv.SchemaDiff(current, &desired)
+	ts.Check(err)
+	return changes
+}
+
+func (t *pgTest) cmdSynced(ts *testscript.TestScript, neg bool, args []string) {
+	cmdSynced(ts, neg, args, t.hclDiff)
+}
+
+func (t *pgTest) cmdApply(ts *testscript.TestScript, neg bool, args []string) {
+	cmdApply(ts, neg, args, t.drv.ApplyChanges, t.hclDiff)
+}
+
+func (t *pgTest) hclDiff(ts *testscript.TestScript, name string) []schema.Change {
+	var (
+		desired schema.Schema
+		f       = strings.ReplaceAll(ts.ReadFile(name), "$db", ts.Getenv("db"))
+	)
+	ts.Check(postgres.UnmarshalHCL([]byte(f), &desired))
+	current, err := t.drv.InspectSchema(context.Background(), desired.Name, nil)
+	ts.Check(err)
+	changes, err := t.drv.SchemaDiff(current, &desired)
+	ts.Check(err)
+	return changes
+}
+
+func cmdSynced(ts *testscript.TestScript, neg bool, args []string, diff func(*testscript.TestScript, string) []schema.Change) {
+	if len(args) != 1 {
+		ts.Fatalf("unexpected number of args to synced command: %d", len(args))
+	}
+	switch changes := diff(ts, args[0]); {
+	case len(changes) > 0 && !neg:
+		ts.Fatalf("expect no schema changes, but got: %d", len(changes))
+	case len(changes) == 0 && neg:
+		ts.Fatalf("expect schema changes, but there are none")
+	}
+}
+
+func cmdApply(ts *testscript.TestScript, neg bool, args []string, apply func(context.Context, []schema.Change) error, diff func(*testscript.TestScript, string) []schema.Change) {
+	changes := diff(ts, args[0])
+	switch err := apply(context.Background(), changes); {
+	case err != nil && !neg:
+		ts.Fatalf("apply changes: %v", err)
+	case err == nil && neg:
+		ts.Fatalf("unexpected apply success")
+	// If we expect to fail, and there's a specific error to compare.
+	case err != nil && len(args) == 2:
+		re, rerr := regexp.Compile(`(?m)` + args[1])
+		ts.Check(rerr)
+		if !re.MatchString(err.Error()) {
+			ts.Fatalf("mismatched errors: %v != %s", err, args[1])
+		}
+	// Apply passed. Make sure there is no drift.
+	case !neg:
+		if changes := diff(ts, args[0]); len(changes) > 0 {
+			ts.Fatalf("unexpected schema changes: %d", len(changes))
+		}
+	}
+}
diff --git a/internal/integration/testdata/postgres/index-desc.txt b/internal/integration/testdata/postgres/index-desc.txt
new file mode 100644
index 00000000..6a65badf
--- /dev/null
+++ b/internal/integration/testdata/postgres/index-desc.txt
@@ -0,0 +1,70 @@
+apply 1.hcl
+cmpshow users 1.sql
+
+# Drop the "DESC" option from the key part.
+apply 2.hcl
+cmpshow users 2.sql
+# Use of "columns" instead of "on" should not trigger a change.
+synced 2-no-change.hcl
+
+-- 1.hcl --
+schema "$db" {}
+
+table "users" {
+    schema = schema.$db
+    column "rank" {
+        type = int
+    }
+    index "rank_idx" {
+        on {
+            desc   = true
+            column = table.users.column.rank
+        }
+    }
+}
+
+-- 1.sql --
+          Table "script_index_desc.users"
+ Column |  Type   | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ rank   | integer |           | not null |
+Indexes:
+    "rank_idx" btree (rank DESC)
+
+-- 2.hcl --
+schema "$db" {}
+
+table "users" {
+    schema = schema.$db
+    column "rank" {
+        type = int
+    }
+    index "rank_idx" {
+        on {
+            column = table.users.column.rank
+        }
+    }
+}
+
+-- 2.sql --
+          Table "script_index_desc.users"
+ Column |  Type   | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ rank   | integer |           | not null |
+Indexes:
+    "rank_idx" btree (rank)
+
+-- 2-no-change.hcl --
+schema "$db" {}
+
+table "users" {
+    schema = schema.$db
+    column "rank" {
+        type = int
+    }
+    index "rank_idx" {
+        columns = [
+            table.users.column.rank,
+        ]
+    }
+}
\ No newline at end of file