Repository: ariga/atlas
Pull request #242 (2021-11-23 07:45:29)

sql/sqlite: bug fixes and migration support


Patch:
diff --git a/internal/integration/go.mod b/internal/integration/go.mod
index 29369998..d61c30db 100644
--- a/internal/integration/go.mod
+++ b/internal/integration/go.mod
@@ -20,6 +20,7 @@ require (
 	github.com/davecgh/go-spew v1.1.1 // indirect
 	github.com/google/go-cmp v0.5.6 // indirect
 	github.com/google/uuid v1.3.0 // indirect
+	github.com/mattn/go-sqlite3 v1.14.9 // indirect
 	github.com/mitchellh/go-wordwrap v0.0.0-20150314170334-ad45545899c7 // indirect
 	github.com/pmezard/go-difflib v1.0.0 // indirect
 	golang.org/x/mod v0.4.3-0.20210512182355-6088ed88cecd // indirect
diff --git a/internal/integration/go.sum b/internal/integration/go.sum
index 0e5ae01c..c2858c77 100644
--- a/internal/integration/go.sum
+++ b/internal/integration/go.sum
@@ -283,6 +283,7 @@ github.com/mattn/go-isatty v0.0.3/go.mod h1:M+lRXTBqGeGNdLjl/ufCoiOlB5xdOkqRJdNx
 github.com/mattn/go-isatty v0.0.5/go.mod h1:Iq45c/XA43vh69/j3iqttzPXn0bhXyGjM0Hdxcsrc5s=
 github.com/mattn/go-isatty v0.0.7/go.mod h1:Iq45c/XA43vh69/j3iqttzPXn0bhXyGjM0Hdxcsrc5s=
 github.com/mattn/go-runewidth v0.0.9/go.mod h1:H031xJmbD/WCDINGzjvQ9THkh0rPKHF+m2gUSrubnMI=
+github.com/mattn/go-sqlite3 v1.14.9 h1:10HX2Td0ocZpYEjhilsuo6WWtUqttj2Kb0KtD86/KYA=
 github.com/mattn/go-sqlite3 v1.14.9/go.mod h1:NyWgC/yNuGj7Q9rpYnZvas74GogHl5/Z4A/KQRfk6bU=
 github.com/matttproud/golang_protobuf_extensions v1.0.1/go.mod h1:D8He9yQNgCq6Z5Ld7szi9bcBfOoFv/3dc6xSMkL2PC0=
 github.com/miekg/dns v1.0.14/go.mod h1:W1PPwlIAgtquWBMBEV9nkV9Cazfe8ScdGz/Lj7v3Nrg=
diff --git a/sql/sqlite/convert.go b/sql/sqlite/convert.go
new file mode 100644
index 00000000..01e325a4
--- /dev/null
+++ b/sql/sqlite/convert.go
@@ -0,0 +1,56 @@
+// 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 sqlite
+
+import (
+	"fmt"
+	"strings"
+
+	"ariga.io/atlas/sql/schema"
+)
+
+// FormatType converts types to one format. A lowered format.
+// This is due to SQLite flexibility to allow any data types
+// and use a set of rules to define the type affinity.
+// See: https://www.sqlite.org/datatype3.html
+func (d *Driver) FormatType(t schema.Type) (string, error) {
+	var f string
+	switch t := t.(type) {
+	case *schema.BoolType:
+		f = strings.ToLower(t.T)
+	case *schema.BinaryType:
+		f = strings.ToLower(t.T)
+	case *schema.EnumType:
+		f = t.T
+	case *schema.IntegerType:
+		f = strings.ToLower(t.T)
+	case *schema.StringType:
+		f = strings.ToLower(t.T)
+	case *schema.TimeType:
+		f = strings.ToLower(t.T)
+	case *schema.FloatType:
+		f = strings.ToLower(t.T)
+	case *schema.DecimalType:
+		f = strings.ToLower(t.T)
+	case *schema.JSONType:
+		f = strings.ToLower(t.T)
+	case *schema.SpatialType:
+		f = strings.ToLower(t.T)
+	case *UUIDType:
+		f = strings.ToLower(t.T)
+	default:
+		return "", fmt.Errorf("sqlite: invalid schema type: %T", t)
+	}
+	return f, nil
+}
+
+// mustFormat calls to FormatType and panics in case of error.
+func (d *Driver) mustFormat(t schema.Type) string {
+	s, err := d.FormatType(t)
+	if err != nil {
+		panic(err)
+	}
+	return s
+}
diff --git a/sql/sqlite/diff.go b/sql/sqlite/diff.go
index 64f3507b..a214cd0c 100644
--- a/sql/sqlite/diff.go
+++ b/sql/sqlite/diff.go
@@ -146,3 +146,39 @@ func (*diff) ReferenceChanged(from, to schema.ReferenceOption) bool {
 	}
 	return from != to
 }
+
+// Normalize implements the sqlx.Normalizer interface.
+func (d *diff) Normalize(from, to *schema.Table) {
+	for _, fk1 := range from.ForeignKeys {
+		if _, ok := to.ForeignKey(fk1.Symbol); ok {
+			continue
+		}
+		// In SQLite, there is no easy way to get the foreign-key constraint
+		// name, except for parsing the CREATE statement). Therefore, we check
+		// if there is a foreign-key with identical properties.
+		for _, fk2 := range to.ForeignKeys {
+			if sameFK(fk1, fk2) {
+				fk1.Symbol = fk2.Symbol
+				break
+			}
+		}
+	}
+}
+
+func sameFK(fk1, fk2 *schema.ForeignKey) bool {
+	if fk1.Table.Name != fk2.Table.Name || fk1.RefTable.Name != fk2.RefTable.Name ||
+		len(fk1.Columns) != len(fk2.Columns) || len(fk1.RefColumns) != len(fk2.RefColumns) {
+		return false
+	}
+	for i, c1 := range fk1.Columns {
+		if c1.Name != fk2.Columns[i].Name {
+			return false
+		}
+	}
+	for i, c1 := range fk1.RefColumns {
+		if c1.Name != fk2.RefColumns[i].Name {
+			return false
+		}
+	}
+	return true
+}
diff --git a/sql/sqlite/driver.go b/sql/sqlite/driver.go
index 2b120f93..e9f21d96 100644
--- a/sql/sqlite/driver.go
+++ b/sql/sqlite/driver.go
@@ -62,11 +62,11 @@ func (d *Driver) InspectRealm(ctx context.Context, opts *schema.InspectRealmOpti
 			return nil, err
 		}
 		for _, t := range tables {
+			t.Schema = s
 			t, err := d.inspectTable(ctx, t)
 			if err != nil {
 				return nil, err
 			}
-			t.Schema = s
 			s.Tables = append(s.Tables, t)
 		}
 		s.Realm = realm
@@ -76,7 +76,7 @@ func (d *Driver) InspectRealm(ctx context.Context, opts *schema.InspectRealmOpti
 }
 
 // InspectSchema returns schema descriptions of all tables in the given schema.
-func (d *Driver) InspectSchema(ctx context.Context, name string, _ *schema.InspectOptions) (*schema.Schema, error) {
+func (d *Driver) InspectSchema(ctx context.Context, name string, opts *schema.InspectOptions) (*schema.Schema, error) {
 	schemas, err := d.databases(ctx, &schema.InspectRealmOption{
 		Schemas: []string{name},
 	})
@@ -88,17 +88,17 @@ func (d *Driver) InspectSchema(ctx context.Context, name string, _ *schema.Inspe
 			Err: fmt.Errorf("sqlite: schema %q was not found", name),
 		}
 	}
-	tables, err := d.tables(ctx, nil)
+	tables, err := d.tables(ctx, opts)
 	if err != nil {
 		return nil, err
 	}
 	s := schemas[0]
 	for _, t := range tables {
+		t.Schema = s
 		t, err := d.inspectTable(ctx, t)
 		if err != nil {
 			return nil, err
 		}
-		t.Schema = s
 		s.Tables = append(s.Tables, t)
 	}
 	sqlx.LinkSchemaTables(schemas)
@@ -108,21 +108,21 @@ func (d *Driver) InspectSchema(ctx context.Context, name string, _ *schema.Inspe
 
 // InspectTable returns the schema description of the given table.
 func (d *Driver) InspectTable(ctx context.Context, name string, opts *schema.InspectTableOptions) (*schema.Table, error) {
-	if opts != nil && opts.Schema != "" {
-		return nil, fmt.Errorf("sqlite: querying custom schema is not supported. got: %q", opts.Schema)
+	if opts != nil && opts.Schema != "main" {
+		return nil, fmt.Errorf("sqlite: querying attached database is not supported. got: %q", opts.Schema)
 	}
-	tables, err := d.tables(ctx, &schema.InspectOptions{
+	s, err := d.InspectSchema(ctx, "main", &schema.InspectOptions{
 		Tables: []string{name},
 	})
 	if err != nil {
 		return nil, err
 	}
-	if len(tables) == 0 {
+	if len(s.Tables) == 0 {
 		return nil, &schema.NotExistError{
 			Err: fmt.Errorf("sqlite: table %q was not found", name),
 		}
 	}
-	return d.inspectTable(ctx, tables[0])
+	return s.Tables[0], nil
 }
 
 func (d *Driver) inspectTable(ctx context.Context, t *schema.Table) (*schema.Table, error) {
@@ -175,6 +175,11 @@ func (d *Driver) addColumn(t *schema.Table, rows *sql.Rows) error {
 	if err != nil {
 		return err
 	}
+	if sqlx.ValidString(defaults) {
+		c.Default = &schema.RawExpr{
+			X: defaults.String,
+		}
+	}
 	// TODO(a8m): extract collation from 'CREATE TABLE' statement.
 	t.Columns = append(t.Columns, c)
 	if primary {
@@ -195,6 +200,11 @@ func (d *Driver) addColumn(t *schema.Table, rows *sql.Rows) error {
 }
 
 func parseRawType(c string) (schema.Type, error) {
+	// A datatype may be zero or more names.
+	// https://www.sqlite.org/datatypes.html
+	if c == "" {
+		return &schema.UnsupportedType{}, nil
+	}
 	parts := columnParts(c)
 	switch t := parts[0]; t {
 	case "bool", "boolean":
@@ -235,8 +245,10 @@ func parseRawType(c string) (schema.Type, error) {
 		return ct, nil
 	case "json":
 		return &schema.JSONType{T: t}, nil
-	case "date", "datetime":
+	case "date", "datetime", "time", "timestamp":
 		return &schema.TimeType{T: t}, nil
+	case "uuid":
+		return &UUIDType{T: t}, nil
 	default:
 		return nil, fmt.Errorf("unknown column type %q", t)
 	}
@@ -356,7 +368,7 @@ func (d *Driver) addFKs(t *schema.Table, rows *sql.Rows) error {
 				OnUpdate: schema.ReferenceOption(updateRule),
 			}
 			if refTable != t.Name {
-				fk.RefTable = &schema.Table{Name: refTable}
+				fk.RefTable = &schema.Table{Name: refTable, Schema: &schema.Schema{Name: t.Schema.Name}}
 			}
 			ids[id] = fk
 			t.ForeignKeys = append(t.ForeignKeys, fk)
@@ -372,7 +384,7 @@ func (d *Driver) addFKs(t *schema.Table, rows *sql.Rows) error {
 			c.ForeignKeys = append(c.ForeignKeys, fk)
 		}
 
-		// Stub referenced columns or link if it's a self-reference.
+		// Stub referenced columns or link if it is a self-reference.
 		var rc *schema.Column
 		if fk.Table != fk.RefTable {
 			rc = &schema.Column{Name: refColumn}
@@ -434,7 +446,7 @@ func (d *Driver) databases(ctx context.Context, opts *schema.InspectRealmOption)
 		query = databasesQuery
 	)
 	if opts != nil && len(opts.Schemas) > 0 {
-		query += " name IN (" + strings.Repeat("?, ", len(opts.Schemas)-1) + "?)"
+		query += " WHERE name IN (" + strings.Repeat("?, ", len(opts.Schemas)-1) + "?)"
 		for _, s := range opts.Schemas {
 			args = append(args, s)
 		}
@@ -446,13 +458,18 @@ func (d *Driver) databases(ctx context.Context, opts *schema.InspectRealmOption)
 	defer rows.Close()
 	var schemas []*schema.Schema
 	for rows.Next() {
-		var name, file string
-		if err := rows.Scan(&name); err != nil {
+		var name, file sql.NullString
+		if err := rows.Scan(&name, &file); err != nil {
 			return nil, err
 		}
+		// File is missing if the database is not
+		// associated with a file (:memory: mode).
+		if file.String == "" {
+			file.String = ":memory:"
+		}
 		schemas = append(schemas, &schema.Schema{
-			Name:  name,
-			Attrs: []schema.Attr{&File{Name: file}},
+			Name:  name.String,
+			Attrs: []schema.Attr{&File{Name: file.String}},
 		})
 	}
 	return schemas, nil
@@ -483,6 +500,12 @@ type (
 		schema.Attr
 		P string
 	}
+
+	// A UUIDType defines a UUID type.
+	UUIDType struct {
+		schema.Type
+		T string
+	}
 )
 
 func columnParts(t string) []string {
diff --git a/sql/sqlite/migrate.go b/sql/sqlite/migrate.go
new file mode 100644
index 00000000..87cd52ab
--- /dev/null
+++ b/sql/sqlite/migrate.go
@@ -0,0 +1,299 @@
+// 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 sqlite
+
+import (
+	"context"
+	"fmt"
+
+	"ariga.io/atlas/sql/internal/sqlx"
+	"ariga.io/atlas/sql/schema"
+)
+
+// A migrate provides migration capabilities for schema elements.
+type migrate struct{ *Driver }
+
+// Migrate returns a SQLite schema executor.
+func (d *Driver) Migrate() schema.Execer {
+	return &migrate{Driver: d}
+}
+
+// Exec executes the changes on the database. An error is returned
+// if one of the operations fail, or a change is not supported.
+func (m *migrate) Exec(ctx context.Context, changes []schema.Change) (err error) {
+	for _, c := range changes {
+		switch c := c.(type) {
+		case *schema.AddTable:
+			err = m.addTable(ctx, c)
+		case *schema.DropTable:
+			err = m.dropTable(ctx, c)
+		case *schema.ModifyTable:
+			err = m.modifyTable(ctx, c)
+		default:
+			err = fmt.Errorf("unsupported change %T", c)
+		}
+		if err != nil {
+			return err
+		}
+	}
+	return nil
+}
+
+// addTable builds and executes the query for creating a table in a schema.
+func (m *migrate) addTable(ctx context.Context, add *schema.AddTable) error {
+	b := Build("CREATE TABLE").Ident(add.T.Name)
+	b.Wrap(func(b *sqlx.Builder) {
+		b.MapComma(add.T.Columns, func(i int, b *sqlx.Builder) {
+			m.column(b, add.T.Columns[i])
+		})
+		if pk := add.T.PrimaryKey; pk != nil {
+			b.Comma().P("PRIMARY KEY")
+			m.indexParts(b, pk.Parts)
+		}
+		if len(add.T.ForeignKeys) > 0 {
+			b.Comma()
+			m.fks(b, add.T.ForeignKeys...)
+		}
+	})
+	if p := (WithoutRowID{}); sqlx.Has(add.T.Attrs, &p) {
+		b.P("WITHOUT ROWID")
+	}
+	if _, err := m.ExecContext(ctx, b.String()); err != nil {
+		return fmt.Errorf("create table: %w", err)
+	}
+	return m.addIndexes(ctx, add.T, add.T.Indexes...)
+}
+
+// dropTable builds and executes the query for dropping a table from a schema.
+func (m *migrate) dropTable(ctx context.Context, drop *schema.DropTable) error {
+	if err := m.skipConstraints(ctx, func(ctx context.Context) error {
+		_, err := m.ExecContext(ctx, Build("DROP TABLE").Ident(drop.T.Name).String())
+		return err
+	}); err != nil {
+		return fmt.Errorf("drop table: %w", err)
+	}
+	return nil
+}
+
+// modifyTable builds and executes the queries for bringing the table into its modified state.
+// If the modification contains changes that are not index creation/deletion or a simple column
+// addition, the changes are applied using a temporary table following the procedure mentioned
+// in: https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes.
+func (m *migrate) modifyTable(ctx context.Context, modify *schema.ModifyTable) error {
+	if alterable(modify) {
+		return m.alterTable(ctx, modify)
+	}
+	if err := m.skipConstraints(ctx, func(ctx context.Context) error {
+		newT := *modify.T
+		indexes := newT.Indexes
+		newT.Indexes = nil
+		newT.Name = "new_" + newT.Name
+		// Create a new table with a temporary name, and copy the existing rows to it.
+		if err := m.addTable(ctx, &schema.AddTable{T: &newT}); err != nil {
+			return err
+		}
+		if err := m.copyRows(ctx, modify.T, &newT, modify.Changes); err != nil {
+			return err
+		}
+		// Drop the current table, and rename the new one to its real name.
+		stmt := Build("DROP TABLE").Ident(modify.T.Name).String()
+		if _, err := m.ExecContext(ctx, stmt); err != nil {
+			return err
+		}
+		stmt = Build("ALTER TABLE").Ident(newT.Name).P("RENAME TO").Ident(modify.T.Name).String()
+		if _, err := m.ExecContext(ctx, stmt); err != nil {
+			return err
+		}
+		return m.addIndexes(ctx, modify.T, indexes...)
+	}); err != nil {
+		return fmt.Errorf("modify table: %w", err)
+	}
+	return nil
+}
+
+func (m *migrate) column(b *sqlx.Builder, c *schema.Column) {
+	b.Ident(c.Name).P(m.mustFormat(c.Type.Type))
+	if !c.Type.Null {
+		b.P("NOT")
+	}
+	b.P("NULL")
+	if x, ok := c.Default.(*schema.RawExpr); ok {
+		b.P("DEFAULT", x.X)
+	}
+}
+
+func (m *migrate) addIndexes(ctx context.Context, t *schema.Table, indexes ...*schema.Index) error {
+	for _, idx := range indexes {
+		b := Build("CREATE")
+		if idx.Unique {
+			b.P("UNIQUE")
+		}
+		b.P("INDEX")
+		if idx.Name != "" {
+			b.Ident(idx.Name)
+		}
+		b.P("ON").Ident(t.Name)
+		m.indexParts(b, idx.Parts)
+		if p := (IndexPredicate{}); sqlx.Has(idx.Attrs, &p) {
+			b.P("WHERE").P(p.P)
+		}
+		if _, err := m.ExecContext(ctx, b.String()); err != nil {
+			return fmt.Errorf("create index: %w", err)
+		}
+	}
+	return nil
+}
+
+func (m *migrate) indexParts(b *sqlx.Builder, parts []*schema.IndexPart) {
+	b.Wrap(func(b *sqlx.Builder) {
+		b.MapComma(parts, func(i int, b *sqlx.Builder) {
+			switch part := parts[i]; {
+			case part.C != nil:
+				b.Ident(part.C.Name)
+			case part.X != nil:
+				b.WriteString(part.X.(*schema.RawExpr).X)
+			}
+		})
+	})
+}
+
+func (m *migrate) fks(b *sqlx.Builder, fks ...*schema.ForeignKey) {
+	b.MapComma(fks, func(i int, b *sqlx.Builder) {
+		fk := fks[i]
+		if fk.Symbol != "" {
+			b.P("CONSTRAINT").Ident(fk.Symbol)
+		}
+		b.P("FOREIGN KEY")
+		b.Wrap(func(b *sqlx.Builder) {
+			b.MapComma(fk.Columns, func(i int, b *sqlx.Builder) {
+				b.Ident(fk.Columns[i].Name)
+			})
+		})
+		b.P("REFERENCES").Ident(fk.RefTable.Name)
+		b.Wrap(func(b *sqlx.Builder) {
+			b.MapComma(fk.RefColumns, func(i int, b *sqlx.Builder) {
+				b.Ident(fk.RefColumns[i].Name)
+			})
+		})
+		if fk.OnUpdate != "" {
+			b.P("ON UPDATE", string(fk.OnUpdate))
+		}
+		if fk.OnDelete != "" {
+			b.P("ON DELETE", string(fk.OnDelete))
+		}
+	})
+}
+
+// skipConstraints runs f without enforcement on the foreign keys constraints if
+// they are enabled.
+func (m *migrate) skipConstraints(ctx context.Context, f func(context.Context) error) (err error) {
+	var enabled bool
+	if err = m.QueryRowContext(ctx, "PRAGMA foreign_keys").Scan(&enabled); err != nil {
+		return fmt.Errorf("checking foreign_keys enforcement: %w", err)
+	}
+	if !enabled {
+		return f(ctx)
+	}
+	if _, err := m.ExecContext(ctx, "PRAGMA foreign_keys = off"); err != nil {
+		return fmt.Errorf("disabling the enforcement of foreign-keys constraints: %w", err)
+	}
+	defer func() {
+		if _, rerr := m.ExecContext(ctx, "PRAGMA foreign_keys = on"); rerr != nil && err == nil {
+			err = rerr
+		}
+	}()
+	return f(ctx)
+}
+
+func (m *migrate) copyRows(ctx context.Context, from *schema.Table, to *schema.Table, changes []schema.Change) error {
+	var (
+		args       []interface{}
+		fromC, toC []string
+	)
+	for _, column := range to.Columns {
+		// Find a change that associated with this column, if exists.
+		var change schema.Change
+		for i := range changes {
+			switch changes[i].(type) {
+			case *schema.AddColumn, *schema.DropColumn, *schema.ModifyColumn:
+				if change != nil {
+					return fmt.Errorf("duplicate changes for column: %q: %T, %T", column.Name, change, changes[i])
+				}
+				change = changes[i]
+			}
+		}
+		switch change := change.(type) {
+		// We expect that new columns are added with DEFAULT values,
+		// or defined as nullable if the table is not empty.
+		case *schema.AddColumn:
+		// Column modification requires special handling if it was
+		// converted from nullable to non-nullable with default value.
+		case *schema.ModifyColumn:
+			toC = append(toC, column.Name)
+			if !column.Type.Null && column.Default != nil && change.Change.Is(schema.ChangeNull|schema.ChangeDefault) {
+				fromC = append(fromC, fmt.Sprintf("IFNULL(`%[1]s`, ?) AS `%[1]s`", column.Name))
+				args = append(args, column.Default.(*schema.RawExpr).X)
+			} else {
+				fromC = append(fromC, column.Name)
+			}
+		// Columns without changes, should transfer as-is.
+		case nil:
+			toC = append(toC, column.Name)
+			fromC = append(fromC, column.Name)
+		}
+	}
+	stmt := fmt.Sprintf("INSERT INTO %s (%s) SELECT (%s) FROM %s", to.Name, toC, fromC, from.Name)
+	if _, err := m.ExecContext(ctx, stmt, args...); err != nil {
+		return fmt.Errorf("copy rows from %q to %q: %w", from.Name, to.Name, err)
+	}
+	return nil
+}
+
+// alterTable alters the table with the given changes. Assuming the changes are "alterable".
+func (m *migrate) alterTable(ctx context.Context, modify *schema.ModifyTable) error {
+	for _, change := range modify.Changes {
+		switch change := change.(type) {
+		case *schema.AddIndex:
+			if err := m.addIndexes(ctx, modify.T, change.I); err != nil {
+				return err
+			}
+		case *schema.DropIndex:
+			b := Build("DROP INDEX").Ident(change.I.Name)
+			if _, err := m.ExecContext(ctx, b.String()); err != nil {
+				return fmt.Errorf("drop index %q to table: %q", change.I.Name, modify.T.Name)
+			}
+		case *schema.AddColumn:
+			b := Build("ALTER TABLE").Ident(modify.T.Name).P("ADD COLUMN")
+			m.column(b, change.C)
+			if _, err := m.ExecContext(ctx, b.String()); err != nil {
+				return fmt.Errorf("add column %q to table: %q", change.C.Name, modify.T.Name)
+			}
+		default:
+			return fmt.Errorf("unexpected change in alter table: %T", change)
+		}
+	}
+	return nil
+}
+
+func alterable(modify *schema.ModifyTable) bool {
+	for _, change := range modify.Changes {
+		switch change := change.(type) {
+		case *schema.DropIndex, *schema.AddIndex:
+		case *schema.AddColumn:
+			if len(change.C.Indexes) > 0 || len(change.C.ForeignKeys) > 0 || change.C.Default != nil {
+				return false
+			}
+		default:
+			return false
+		}
+	}
+	return true
+}
+
+// Build instantiates a new builder and writes the given phrase to it.
+func Build(phrase string) *sqlx.Builder {
+	b := &sqlx.Builder{QuoteChar: '`'}
+	return b.P(phrase)
+}


Test patch:
diff --git a/internal/integration/sqlite_test.go b/internal/integration/sqlite_test.go
new file mode 100644
index 00000000..609a4e19
--- /dev/null
+++ b/internal/integration/sqlite_test.go
@@ -0,0 +1,164 @@
+// 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 (
+	"context"
+	"database/sql"
+	"testing"
+
+	"ariga.io/atlas/sql/postgres"
+	"ariga.io/atlas/sql/schema"
+	"ariga.io/atlas/sql/sqlite"
+
+	_ "github.com/mattn/go-sqlite3"
+	"github.com/stretchr/testify/require"
+)
+
+type liteTest struct {
+	*testing.T
+	db  *sql.DB
+	drv *sqlite.Driver
+}
+
+func liteRun(t *testing.T, fn func(test *liteTest)) {
+	db, err := sql.Open("sqlite3", "file:atlas?mode=memory&cache=shared&_fk=1")
+	require.NoError(t, err)
+	t.Cleanup(func() {
+		require.NoError(t, db.Close())
+	})
+	drv, err := sqlite.Open(db)
+	require.NoError(t, err)
+	tt := &liteTest{T: t, db: db, drv: drv}
+	fn(tt)
+}
+
+func TestSQLite_AddDropTable(t *testing.T) {
+	liteRun(t, func(t *liteTest) {
+		testAddDrop(t)
+	})
+}
+
+func TestSQLite_Relation(t *testing.T) {
+	liteRun(t, func(t *liteTest) {
+		testRelation(t)
+	})
+}
+
+func (t *liteTest) loadRealm() *schema.Realm {
+	r, err := t.drv.InspectRealm(context.Background(), &schema.InspectRealmOption{})
+	require.NoError(t, err)
+	return r
+}
+
+func (t *liteTest) loadUsers() *schema.Table {
+	realm := t.loadRealm()
+	require.Len(t, realm.Schemas, 1)
+	users, ok := realm.Schemas[0].Table("users")
+	require.True(t, ok)
+	return users
+}
+
+func (t *liteTest) loadPosts() *schema.Table {
+	realm := t.loadRealm()
+	require.Len(t, realm.Schemas, 1)
+	posts, ok := realm.Schemas[0].Table("posts")
+	require.True(t, ok)
+	return posts
+}
+
+func (t *liteTest) users() *schema.Table {
+	usersT := &schema.Table{
+		Name:   "users",
+		Schema: t.realm().Schemas[0],
+		Columns: []*schema.Column{
+			{
+				Name:  "id",
+				Type:  &schema.ColumnType{Type: &schema.IntegerType{T: "bigint"}},
+				Attrs: []schema.Attr{&postgres.Identity{}},
+			},
+			{
+				Name: "x",
+				Type: &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}},
+			},
+		},
+	}
+	usersT.PrimaryKey = &schema.Index{Parts: []*schema.IndexPart{{C: usersT.Columns[0]}}}
+	return usersT
+}
+
+func (t *liteTest) posts() *schema.Table {
+	usersT := t.users()
+	postsT := &schema.Table{
+		Name:   "posts",
+		Schema: t.realm().Schemas[0],
+		Columns: []*schema.Column{
+			{
+				Name:  "id",
+				Type:  &schema.ColumnType{Type: &schema.IntegerType{T: "bigint"}},
+				Attrs: []schema.Attr{&postgres.Identity{}},
+			},
+			{
+				Name:    "author_id",
+				Type:    &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}, Null: true},
+				Default: &schema.RawExpr{X: "10"},
+			},
+			{
+				Name: "ctime",
+				Type: &schema.ColumnType{Raw: "timestamp", Type: &schema.TimeType{T: "timestamp"}},
+				Default: &schema.RawExpr{
+					X: "CURRENT_TIMESTAMP",
+				},
+			},
+		},
+		Attrs: []schema.Attr{
+			&schema.Comment{Text: "posts comment"},
+		},
+	}
+	postsT.PrimaryKey = &schema.Index{Parts: []*schema.IndexPart{{C: postsT.Columns[0]}}}
+	postsT.Indexes = []*schema.Index{
+		{Name: "author_id", Parts: []*schema.IndexPart{{C: postsT.Columns[1]}}},
+		{Name: "id_author_id_unique", Unique: true, Parts: []*schema.IndexPart{{C: postsT.Columns[1]}, {C: postsT.Columns[0]}}},
+	}
+	postsT.ForeignKeys = []*schema.ForeignKey{
+		{Symbol: "author_id", Table: postsT, Columns: postsT.Columns[1:2], RefTable: usersT, RefColumns: usersT.Columns[:1], OnDelete: schema.NoAction},
+	}
+	return postsT
+}
+
+func (t *liteTest) realm() *schema.Realm {
+	r := &schema.Realm{
+		Schemas: []*schema.Schema{
+			{
+				Name: "main",
+				Attrs: []schema.Attr{
+					&sqlite.File{Name: ":memory:"},
+				},
+			},
+		},
+	}
+	r.Schemas[0].Realm = r
+	return r
+}
+
+func (t *liteTest) diff(t1, t2 *schema.Table) []schema.Change {
+	changes, err := t.drv.Diff().TableDiff(t1, t2)
+	require.NoError(t, err)
+	return changes
+}
+
+func (t *liteTest) migrate(changes ...schema.Change) {
+	err := t.drv.Migrate().Exec(context.Background(), changes)
+	require.NoError(t, err)
+}
+
+func (t *liteTest) dropTables(names ...string) {
+	t.Cleanup(func() {
+		for i := range names {
+			_, err := t.db.Exec("DROP TABLE IF EXISTS " + names[i])
+			require.NoError(t.T, err, "drop tables %q", names[i])
+		}
+	})
+}
diff --git a/sql/sqlite/diff_test.go b/sql/sqlite/diff_test.go
index 1e4d9f1d..190e44e0 100644
--- a/sql/sqlite/diff_test.go
+++ b/sql/sqlite/diff_test.go
@@ -177,9 +177,12 @@ func TestDiff_TableDiff(t *testing.T) {
 			)
 			from.ForeignKeys = []*schema.ForeignKey{
 				{Table: from, Columns: from.Columns, RefTable: ref, RefColumns: ref.Columns[:1]},
+				{Symbol: "0", Table: from, Columns: from.Columns, RefTable: ref, RefColumns: ref.Columns[:1]},
 			}
 			to.ForeignKeys = []*schema.ForeignKey{
 				{Table: to, Columns: to.Columns, RefTable: ref, RefColumns: ref.Columns[1:]},
+				// The below "constraint" is identical to "0" above, therefore, the differ does not report a change.
+				{Symbol: "constraint", Table: from, Columns: from.Columns, RefTable: ref, RefColumns: ref.Columns[:1]},
 			}
 			return testcase{
 				name: "foreign-keys",
diff --git a/sql/sqlite/driver_test.go b/sql/sqlite/driver_test.go
index e59c1299..42a74bdf 100644
--- a/sql/sqlite/driver_test.go
+++ b/sql/sqlite/driver_test.go
@@ -32,7 +32,7 @@ func TestDriver_InspectTable(t *testing.T) {
 			expect: func(require *require.Assertions, t *schema.Table, err error) {
 				require.Nil(t)
 				require.Error(err)
-				require.True(schema.IsNotExistError(err), "expect not exists error")
+				require.True(schema.IsNotExistError(err), "expect not exists error: %v", err)
 			},
 		},
 		{
@@ -179,7 +179,7 @@ func TestDriver_InspectTable(t *testing.T) {
 			expect: func(require *require.Assertions, t *schema.Table, err error) {
 				require.NoError(err)
 				fks := []*schema.ForeignKey{
-					{Symbol: "0", Table: t, OnUpdate: schema.NoAction, OnDelete: schema.Cascade, RefTable: &schema.Table{Name: "t2"}, RefColumns: []*schema.Column{{Name: "id"}, {Name: "c1"}}},
+					{Symbol: "0", Table: t, OnUpdate: schema.NoAction, OnDelete: schema.Cascade, RefTable: &schema.Table{Name: "t2", Schema: &schema.Schema{Name: "main"}}, RefColumns: []*schema.Column{{Name: "id"}, {Name: "c1"}}},
 					{Symbol: "1", Table: t, OnUpdate: schema.NoAction, OnDelete: schema.Cascade, RefTable: t},
 				}
 				columns := []*schema.Column{
@@ -233,6 +233,13 @@ func (m mock) systemVars(version string) {
       RTRIM
       NOCASE
       BINARY
+`))
+	m.ExpectQuery(sqltest.Escape(databasesQuery + " WHERE name IN (?)")).
+		WithArgs("main").
+		WillReturnRows(sqltest.Rows(`
+ name |   file    
+------+-----------
+ main |   
 `))
 }