Repository: ariga/atlas
Pull request #357 (2022-01-01 14:33:10)

sql/sqlite: support inspecting CHECK constraints


Patch:
diff --git a/sql/internal/sqlx/diff.go b/sql/internal/sqlx/diff.go
index 7d6cca72..550d78c8 100644
--- a/sql/internal/sqlx/diff.go
+++ b/sql/internal/sqlx/diff.go
@@ -343,3 +343,67 @@ type UnsupportedTypeError struct {
 func (e UnsupportedTypeError) Error() string {
 	return fmt.Sprintf("unsupported type %T", e.Type)
 }
+
+// CheckDiff computes the change diff between the 2 tables. A compare
+// function is provided to check if a Check object was modified.
+func CheckDiff(from, to *schema.Table, compare func(c1, c2 *schema.Check) bool) []schema.Change {
+	var changes []schema.Change
+	// Drop or modify checks.
+	for _, c1 := range checks(from.Attrs) {
+		switch c2, ok := similarCheck(to.Attrs, c1); {
+		case !ok:
+			changes = append(changes, &schema.DropCheck{
+				C: c1,
+			})
+		case compare(c1, c2):
+			changes = append(changes, &schema.ModifyCheck{
+				From: c1,
+				To:   c2,
+			})
+		}
+	}
+	// Add checks.
+	for _, c1 := range checks(to.Attrs) {
+		if _, ok := similarCheck(from.Attrs, c1); !ok {
+			changes = append(changes, &schema.AddCheck{
+				C: c1,
+			})
+		}
+	}
+	return changes
+}
+
+// checks extracts all constraints from table attributes.
+func checks(attr []schema.Attr) (checks []*schema.Check) {
+	for i := range attr {
+		if c, ok := attr[i].(*schema.Check); ok {
+			checks = append(checks, c)
+		}
+	}
+	return checks
+}
+
+// similarCheck returns a CHECK by its constraints name or expression.
+func similarCheck(attrs []schema.Attr, c *schema.Check) (*schema.Check, bool) {
+	var byName, byExpr *schema.Check
+	for i := 0; i < len(attrs) && (byName == nil || byExpr == nil); i++ {
+		check, ok := attrs[i].(*schema.Check)
+		if !ok {
+			continue
+		}
+		if check.Name != "" && check.Name == c.Name {
+			byName = check
+		}
+		if check.Expr == c.Expr {
+			byExpr = check
+		}
+	}
+	// Give precedence to constraint name.
+	if byName != nil {
+		return byName, true
+	}
+	if byExpr != nil {
+		return byExpr, true
+	}
+	return nil, false
+}
diff --git a/sql/internal/sqlx/sqlx.go b/sql/internal/sqlx/sqlx.go
index 8f0344e2..d18d494b 100644
--- a/sql/internal/sqlx/sqlx.go
+++ b/sql/internal/sqlx/sqlx.go
@@ -322,23 +322,3 @@ func DefaultValue(c *schema.Column) (string, bool) {
 		panic(fmt.Sprintf("unexpected default value type: %T", x))
 	}
 }
-
-// Checks constrains from table or column attributes.
-func Checks(attr []schema.Attr) (checks []*schema.Check) {
-	for i := range attr {
-		if c, ok := attr[i].(*schema.Check); ok {
-			checks = append(checks, c)
-		}
-	}
-	return checks
-}
-
-// CheckByName returns a CHECK by its constraints name.
-func CheckByName(attr []schema.Attr, name string) (*schema.Check, bool) {
-	for i := range attr {
-		if c, ok := attr[i].(*schema.Check); ok && c.Name == name {
-			return c, true
-		}
-	}
-	return nil, false
-}
diff --git a/sql/mysql/diff.go b/sql/mysql/diff.go
index 418cd2a5..0612c6d6 100644
--- a/sql/mysql/diff.go
+++ b/sql/mysql/diff.go
@@ -35,37 +35,15 @@ func (d *diff) SchemaAttrDiff(from, to *schema.Schema) []schema.Change {
 // TableAttrDiff returns a changeset for migrating table attributes from one state to the other.
 func (d *diff) TableAttrDiff(from, to *schema.Table) []schema.Change {
 	var changes []schema.Change
-	// Charset change.
 	if change := d.charsetChange(from.Attrs, from.Schema.Attrs, to.Attrs); change != noChange {
 		changes = append(changes, change)
 	}
-	// Collation change.
 	if change := d.collationChange(from.Attrs, from.Schema.Attrs, to.Attrs); change != noChange {
 		changes = append(changes, change)
 	}
-	// Drop or modify checks.
-	for _, c1 := range sqlx.Checks(from.Attrs) {
-		switch c2, ok := sqlx.CheckByName(to.Attrs, c1.Name); {
-		case !ok:
-			changes = append(changes, &schema.DropCheck{
-				C: c1,
-			})
-		case c1.Expr != c2.Expr || sqlx.Has(c1.Attrs, &Enforced{}) != sqlx.Has(c2.Attrs, &Enforced{}):
-			changes = append(changes, &schema.ModifyCheck{
-				From: c1,
-				To:   c2,
-			})
-		}
-	}
-	// Add checks.
-	for _, c1 := range sqlx.Checks(to.Attrs) {
-		if _, ok := sqlx.CheckByName(from.Attrs, c1.Name); !ok {
-			changes = append(changes, &schema.AddCheck{
-				C: c1,
-			})
-		}
-	}
-	return changes
+	return append(changes, sqlx.CheckDiff(from, to, func(c1, c2 *schema.Check) bool {
+		return c1.Expr != c2.Expr || sqlx.Has(c1.Attrs, &Enforced{}) != sqlx.Has(c2.Attrs, &Enforced{})
+	})...)
 }
 
 // ColumnChange returns the schema changes (if any) for migrating one column to the other.
diff --git a/sql/postgres/diff.go b/sql/postgres/diff.go
index bab80b7d..f5c3fa0b 100644
--- a/sql/postgres/diff.go
+++ b/sql/postgres/diff.go
@@ -25,30 +25,9 @@ func (d *diff) SchemaAttrDiff(_, _ *schema.Schema) []schema.Change {
 
 // TableAttrDiff returns a changeset for migrating table attributes from one state to the other.
 func (d *diff) TableAttrDiff(from, to *schema.Table) []schema.Change {
-	var changes []schema.Change
-	// Drop or modify checks.
-	for _, c1 := range sqlx.Checks(from.Attrs) {
-		switch c2, ok := sqlx.CheckByName(to.Attrs, c1.Name); {
-		case !ok:
-			changes = append(changes, &schema.DropAttr{
-				A: c1,
-			})
-		case c1.Expr != c2.Expr || sqlx.Has(c1.Attrs, &NoInherit{}) != sqlx.Has(c2.Attrs, &NoInherit{}):
-			changes = append(changes, &schema.ModifyAttr{
-				From: c1,
-				To:   c2,
-			})
-		}
-	}
-	// Add checks.
-	for _, c1 := range sqlx.Checks(to.Attrs) {
-		if _, ok := sqlx.CheckByName(from.Attrs, c1.Name); !ok {
-			changes = append(changes, &schema.AddAttr{
-				A: c1,
-			})
-		}
-	}
-	return changes
+	return sqlx.CheckDiff(from, to, func(c1, c2 *schema.Check) bool {
+		return c1.Expr != c2.Expr || sqlx.Has(c1.Attrs, &NoInherit{}) != sqlx.Has(c2.Attrs, &NoInherit{})
+	})
 }
 
 // ColumnChange returns the schema changes (if any) for migrating one column to the other.
diff --git a/sql/sqlite/diff.go b/sql/sqlite/diff.go
index 1e713351..ca5360fc 100644
--- a/sql/sqlite/diff.go
+++ b/sql/sqlite/diff.go
@@ -34,8 +34,9 @@ func (d *diff) TableAttrDiff(from, to *schema.Table) []schema.Change {
 			A: &WithoutRowID{},
 		})
 	}
-	// TODO: support diffing constraints after it's added on inspection.
-	return changes
+	return append(changes, sqlx.CheckDiff(from, to, func(c1, c2 *schema.Check) bool {
+		return c1.Expr != c2.Expr
+	})...)
 }
 
 // ColumnChange returns the schema changes (if any) for migrating one column to the other.
diff --git a/sql/sqlite/inspect.go b/sql/sqlite/inspect.go
index a3f5009e..5f4cc6dd 100644
--- a/sql/sqlite/inspect.go
+++ b/sql/sqlite/inspect.go
@@ -111,7 +111,9 @@ func (i *inspect) inspectTable(ctx context.Context, t *schema.Table) (*schema.Ta
 	if err := i.fks(ctx, t); err != nil {
 		return nil, err
 	}
-	// TODO(a8m): extract CHECK constraints from 'CREATE TABLE' statement.
+	if err := fillChecks(t); err != nil {
+		return nil, err
+	}
 	return t, nil
 }
 
@@ -375,7 +377,6 @@ func (i *inspect) addFKs(t *schema.Table, rows *sql.Rows) error {
 			fk.RefColumns = append(fk.RefColumns, rc)
 		}
 	}
-	// TODO(a8m): extract the foreign-key name from the `CREATE TABLE` statement.
 	return nil
 }
 
@@ -563,11 +564,12 @@ func autoinc(t *schema.Table) {
 	t.PrimaryKey.Parts[0].C.Attrs = append(t.PrimaryKey.Parts[0].C.Attrs, AutoIncrement{})
 }
 
-// The following regexes extract named foreign-key constraints defined in the table-constraints or inlined
+// The following regexes extract named FKs and CHECK constraints defined in table-constraints or inlined
 // as column-constraints. Note, we assume the SQL statements are valid as they are returned by SQLite.
 var (
-	reConstC = regexp.MustCompile("(?i)(?:[(,]\\s*)[\"`]*(\\w+)[\"`]*[^,]*\\s+CONSTRAINT\\s+[\"`]*(\\w+)[\"`]*\\s+REFERENCES\\s+[\"`]*(\\w+)[\"`]*\\s*\\(([,\"` \\w]+)\\)")
-	reConstT = regexp.MustCompile("(?i)CONSTRAINT\\s+[\"`]*(\\w+)[\"`]*\\s+FOREIGN\\s+KEY\\s*\\(([,\"` \\w]+)\\)\\s+REFERENCES\\s+[\"`]*(\\w+)[\"`]*\\s*\\(([,\"` \\w]+)\\)")
+	reFKC   = regexp.MustCompile("(?i)(?:[(,]\\s*)[\"`]*(\\w+)[\"`]*[^,]*\\s+CONSTRAINT\\s+[\"`]*(\\w+)[\"`]*\\s+REFERENCES\\s+[\"`]*(\\w+)[\"`]*\\s*\\(([,\"` \\w]+)\\)")
+	reFKT   = regexp.MustCompile("(?i)CONSTRAINT\\s+[\"`]*(\\w+)[\"`]*\\s+FOREIGN\\s+KEY\\s*\\(([,\"` \\w]+)\\)\\s+REFERENCES\\s+[\"`]*(\\w+)[\"`]*\\s*\\(([,\"` \\w]+)\\)")
+	reCheck = regexp.MustCompile("(?i)(?:CONSTRAINT\\s+[\"`]?(\\w+)[\"`]?\\s+)?CHECK\\s*\\(")
 )
 
 // fillConstName fills foreign-key constrain names from CREATE TABLE statement.
@@ -577,7 +579,7 @@ func fillConstName(t *schema.Table) error {
 		return fmt.Errorf("missing CREATE statment for table: %q", t.Name)
 	}
 	// Loop over table constraints.
-	for _, m := range reConstT.FindAllStringSubmatch(c.S, -1) {
+	for _, m := range reFKT.FindAllStringSubmatch(c.S, -1) {
 		if len(m) != 5 {
 			return fmt.Errorf("unexpected number of matches for a table constraint: %q", m)
 		}
@@ -591,7 +593,7 @@ func fillConstName(t *schema.Table) error {
 		}
 	}
 	// Loop over inlined column constraints.
-	for _, m := range reConstC.FindAllStringSubmatch(c.S, -1) {
+	for _, m := range reFKC.FindAllStringSubmatch(c.S, -1) {
 		if len(m) != 5 {
 			return fmt.Errorf("unexpected number of matches for a column constraint: %q", m)
 		}
@@ -634,6 +636,52 @@ func matchFK(fk *schema.ForeignKey, columns []string, refTable string, refColumn
 	return true
 }
 
+// fillChecks extracts the CHECK constrains from the CREATE TABLE statement,
+// and appends them to the table attributes.
+func fillChecks(t *schema.Table) error {
+	var c CreateStmt
+	if !sqlx.Has(t.Attrs, &c) {
+		return fmt.Errorf("missing CREATE statment for table: %q", t.Name)
+	}
+	for i := 0; i < len(c.S); {
+		idx := reCheck.FindStringSubmatchIndex(c.S[i:])
+		// No more matches.
+		if len(idx) != 4 {
+			break
+		}
+		var (
+			r, l int
+			expr = c.S[idx[1]-1:]
+		)
+		for i := 0; i < len(expr); i++ {
+			switch expr[i] {
+			case '(':
+				r++
+			case ')':
+				l++
+			case '\'', '"':
+				// Skip unescaped strings.
+				if j := strings.IndexByte(expr[i+1:], expr[i]); j != -1 {
+					i += j + 1
+				}
+			}
+			// Balanced parens.
+			if r == l {
+				check := &schema.Check{Expr: expr[:i+1]}
+				// Matching group for constraint name.
+				if idx[2] != -1 && idx[3] != -1 {
+					check.Name = c.S[idx[2]:idx[3]]
+				}
+				t.Attrs = append(t.Attrs, check)
+				idx[1] = idx[1] + i
+				break
+			}
+		}
+		c.S = c.S[idx[1]:]
+	}
+	return nil
+}
+
 const (
 	// Query to list attached database files.
 	databasesQuery = "SELECT `name`, `file` FROM pragma_database_list()"
diff --git a/sql/sqlite/migrate.go b/sql/sqlite/migrate.go
index 551af287..f9341b81 100644
--- a/sql/sqlite/migrate.go
+++ b/sql/sqlite/migrate.go
@@ -27,7 +27,7 @@ func (p *planApply) PlanChanges(ctx context.Context, name string, changes []sche
 			Transactional: true,
 		},
 	}
-	if err := s.plan(ctx, changes); err != nil {
+	if err := s.plan(changes); err != nil {
 		return nil, err
 	}
 	for _, c := range s.Changes {
@@ -56,15 +56,15 @@ type state struct {
 
 // Exec executes the changes on the database. An error is returned
 // if one of the operations fail, or a change is not supported.
-func (s *state) plan(ctx context.Context, changes []schema.Change) (err error) {
+func (s *state) plan(changes []schema.Change) (err error) {
 	for _, c := range changes {
 		switch c := c.(type) {
 		case *schema.AddTable:
-			err = s.addTable(ctx, c)
+			err = s.addTable(c)
 		case *schema.DropTable:
-			err = s.dropTable(ctx, c)
+			err = s.dropTable(c)
 		case *schema.ModifyTable:
-			err = s.modifyTable(ctx, c)
+			err = s.modifyTable(c)
 		default:
 			err = fmt.Errorf("unsupported change %T", c)
 		}
@@ -84,7 +84,7 @@ func (s *state) plan(ctx context.Context, changes []schema.Change) (err error) {
 }
 
 // addTable builds and executes the query for creating a table in a schema.
-func (s *state) addTable(ctx context.Context, add *schema.AddTable) error {
+func (s *state) addTable(add *schema.AddTable) error {
 	b := Build("CREATE TABLE").Ident(add.T.Name)
 	if sqlx.Has(add.Extra, &schema.IfNotExists{}) {
 		b.P("IF NOT EXISTS")
@@ -102,6 +102,12 @@ func (s *state) addTable(ctx context.Context, add *schema.AddTable) error {
 			b.Comma()
 			s.fks(b, add.T.ForeignKeys...)
 		}
+		for _, attr := range add.T.Attrs {
+			if c, ok := attr.(*schema.Check); ok {
+				b.Comma()
+				check(b, c)
+			}
+		}
 	})
 	if p := (WithoutRowID{}); sqlx.Has(add.T.Attrs, &p) {
 		b.P("WITHOUT ROWID")
@@ -112,11 +118,11 @@ func (s *state) addTable(ctx context.Context, add *schema.AddTable) error {
 		Reverse: Build("DROP TABLE").Table(add.T).String(),
 		Comment: fmt.Sprintf("create %q table", add.T.Name),
 	})
-	return s.addIndexes(ctx, add.T, add.T.Indexes...)
+	return s.addIndexes(add.T, add.T.Indexes...)
 }
 
 // dropTable builds and executes the query for dropping a table from a schema.
-func (s *state) dropTable(ctx context.Context, drop *schema.DropTable) error {
+func (s *state) dropTable(drop *schema.DropTable) error {
 	s.skipFKs = true
 	b := Build("DROP TABLE").Ident(drop.T.Name)
 	if sqlx.Has(drop.Extra, &schema.IfExists{}) {
@@ -134,9 +140,9 @@ func (s *state) dropTable(ctx context.Context, drop *schema.DropTable) error {
 // 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 (s *state) modifyTable(ctx context.Context, modify *schema.ModifyTable) error {
+func (s *state) modifyTable(modify *schema.ModifyTable) error {
 	if alterable(modify) {
-		return s.alterTable(ctx, modify)
+		return s.alterTable(modify)
 	}
 	s.skipFKs = true
 	newT := *modify.T
@@ -144,7 +150,7 @@ func (s *state) modifyTable(ctx context.Context, modify *schema.ModifyTable) err
 	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 := s.addTable(ctx, &schema.AddTable{T: &newT}); err != nil {
+	if err := s.addTable(&schema.AddTable{T: &newT}); err != nil {
 		return err
 	}
 	if err := s.copyRows(modify.T, &newT, modify.Changes); err != nil {
@@ -161,7 +167,7 @@ func (s *state) modifyTable(ctx context.Context, modify *schema.ModifyTable) err
 		Source:  modify,
 		Comment: fmt.Sprintf("rename temporary table %q to %q", newT.Name, modify.T.Name),
 	})
-	return s.addIndexes(ctx, modify.T, indexes...)
+	return s.addIndexes(modify.T, indexes...)
 }
 
 func (s *state) column(b *sqlx.Builder, c *schema.Column) {
@@ -178,7 +184,7 @@ func (s *state) column(b *sqlx.Builder, c *schema.Column) {
 	}
 }
 
-func (s *state) addIndexes(_ context.Context, t *schema.Table, indexes ...*schema.Index) error {
+func (s *state) addIndexes(t *schema.Table, indexes ...*schema.Index) error {
 	for _, idx := range indexes {
 		// PRIMARY KEY or UNIQUE columns automatically create indexes with the generated name.
 		// See: sqlite/build.c#sqlite3CreateIndex. Therefore, we ignore such PKs, but create
@@ -325,11 +331,11 @@ func (s *state) copyRows(from *schema.Table, to *schema.Table, changes []schema.
 }
 
 // alterTable alters the table with the given changes. Assuming the changes are "alterable".
-func (s *state) alterTable(ctx context.Context, modify *schema.ModifyTable) error {
+func (s *state) alterTable(modify *schema.ModifyTable) error {
 	for _, change := range modify.Changes {
 		switch change := change.(type) {
 		case *schema.AddIndex:
-			if err := s.addIndexes(ctx, modify.T, change.I); err != nil {
+			if err := s.addIndexes(modify.T, change.I); err != nil {
 				return err
 			}
 		case *schema.DropIndex:
@@ -374,6 +380,19 @@ func alterable(modify *schema.ModifyTable) bool {
 	return true
 }
 
+// checks writes the CHECK constraint to the builder.
+func check(b *sqlx.Builder, c *schema.Check) {
+	expr := c.Expr
+	// Expressions should be wrapped with parens.
+	if t := strings.TrimSpace(expr); !strings.HasPrefix(t, "(") || !strings.HasSuffix(t, ")") {
+		expr = "(" + t + ")"
+	}
+	if c.Name != "" {
+		b.P("CONSTRAINT").Ident(c.Name)
+	}
+	b.P("CHECK", expr)
+}
+
 func autoincPK(pk *schema.Index) bool {
 	return sqlx.Has(pk.Attrs, &AutoIncrement{}) ||
 		len(pk.Parts) == 1 && pk.Parts[0].C != nil && sqlx.Has(pk.Parts[0].C.Attrs, &AutoIncrement{})


Test patch:
diff --git a/sql/postgres/diff_test.go b/sql/postgres/diff_test.go
index 63beebc9..4e397511 100644
--- a/sql/postgres/diff_test.go
+++ b/sql/postgres/diff_test.go
@@ -44,8 +44,8 @@ func TestDiff_TableDiff(t *testing.T) {
 			from: &schema.Table{Name: "t1", Schema: &schema.Schema{Name: "public"}},
 			to:   &schema.Table{Name: "t1", Attrs: []schema.Attr{&schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)"}}},
 			wantChanges: []schema.Change{
-				&schema.AddAttr{
-					A: &schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)"},
+				&schema.AddCheck{
+					C: &schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)"},
 				},
 			},
 		},
@@ -54,8 +54,8 @@ func TestDiff_TableDiff(t *testing.T) {
 			from: &schema.Table{Name: "t1", Attrs: []schema.Attr{&schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)"}}},
 			to:   &schema.Table{Name: "t1"},
 			wantChanges: []schema.Change{
-				&schema.DropAttr{
-					A: &schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)"},
+				&schema.DropCheck{
+					C: &schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)"},
 				},
 			},
 		},
@@ -64,7 +64,7 @@ func TestDiff_TableDiff(t *testing.T) {
 			from: &schema.Table{Name: "t1", Schema: &schema.Schema{Name: "public"}, Attrs: []schema.Attr{&schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)"}}},
 			to:   &schema.Table{Name: "t1", Attrs: []schema.Attr{&schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)", Attrs: []schema.Attr{&NoInherit{}}}}},
 			wantChanges: []schema.Change{
-				&schema.ModifyAttr{
+				&schema.ModifyCheck{
 					From: &schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)"},
 					To:   &schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)", Attrs: []schema.Attr{&NoInherit{}}},
 				},
diff --git a/sql/sqlite/diff_test.go b/sql/sqlite/diff_test.go
index ff00d2d7..8b3b9424 100644
--- a/sql/sqlite/diff_test.go
+++ b/sql/sqlite/diff_test.go
@@ -58,6 +58,54 @@ func TestDiff_TableDiff(t *testing.T) {
 				},
 			},
 		},
+		{
+			name: "add check",
+			from: &schema.Table{Name: "t1"},
+			to:   &schema.Table{Name: "t1", Attrs: []schema.Attr{&schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)"}}},
+			wantChanges: []schema.Change{
+				&schema.AddCheck{
+					C: &schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)"},
+				},
+			},
+		},
+		{
+			name: "drop check",
+			from: &schema.Table{Name: "t1", Attrs: []schema.Attr{&schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)"}}},
+			to:   &schema.Table{Name: "t1"},
+			wantChanges: []schema.Change{
+				&schema.DropCheck{
+					C: &schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)"},
+				},
+			},
+		},
+		{
+			name: "find check by expr",
+			from: &schema.Table{
+				Name: "t1",
+				Attrs: []schema.Attr{
+					&schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)"},
+					&schema.Check{Expr: "(d1 > 1)"},
+				},
+			},
+			to: &schema.Table{
+				Name: "t1",
+				Attrs: []schema.Attr{
+					&schema.Check{Expr: "(c1 > 1)"},
+					&schema.Check{Name: "add_name_to_check", Expr: "(d1 > 1)"},
+				},
+			},
+		},
+		{
+			name: "modify check",
+			from: &schema.Table{Name: "t1", Attrs: []schema.Attr{&schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)"}}},
+			to:   &schema.Table{Name: "t1", Attrs: []schema.Attr{&schema.Check{Name: "t1_c1_check", Expr: "(c1 > 2)"}}},
+			wantChanges: []schema.Change{
+				&schema.ModifyCheck{
+					From: &schema.Check{Name: "t1_c1_check", Expr: "(c1 > 1)"},
+					To:   &schema.Check{Name: "t1_c1_check", Expr: "(c1 > 2)"},
+				},
+			},
+		},
 		func() testcase {
 			var (
 				from = &schema.Table{
diff --git a/sql/sqlite/inspect_test.go b/sql/sqlite/inspect_test.go
index 76a2ed34..027d6293 100644
--- a/sql/sqlite/inspect_test.go
+++ b/sql/sqlite/inspect_test.go
@@ -158,16 +158,17 @@ func TestDriver_InspectTable(t *testing.T) {
 			},
 		},
 		{
-			name: "table fks",
+			name: "table constraints",
 			before: func(m mock) {
 				m.systemVars("3.36.0")
 				m.tableExists("users", true, `
 CREATE TABLE users(
 	id INTEGER PRIMARY KEY,
-	c1 int,
-	c2 integer NOT NULL CONSTRAINT c2_fk REFERENCES users (c1) ON DELETE SET NULL,
+	c1 int CHECK (c1 > 10),
+	c2 integer NOT NULL CONSTRAINT c2_fk REFERENCES users (c1) ON DELETE SET NULL constraint "ck1" CHECK ((c1 + c2) % 2 = 0),
 	c3 integer NOT NULL REFERENCES users (c1) ON DELETE SET NULL,
-	CONSTRAINT "c1_c2_fk" FOREIGN KEY (c1, c2) REFERENCES t2 (id, c1)
+	CONSTRAINT "c1_c2_fk" FOREIGN KEY (c1, c2) REFERENCES t2 (id, c1),
+	CONSTRAINT "id_nonzero" CHECK (id <> 0)
 )
 `)
 				m.ExpectQuery(sqltest.Escape(fmt.Sprintf(columnsQuery, "users"))).
@@ -202,8 +203,14 @@ CREATE TABLE users(
 				fks[0].Columns = columns[:2]
 				fks[1].Columns = columns[1:2]
 				fks[1].RefColumns = columns[:1]
+				checks := []schema.Attr{
+					&schema.Check{Expr: "(c1 > 10)"},
+					&schema.Check{Name: "ck1", Expr: "((c1 + c2) % 2 = 0)"},
+					&schema.Check{Name: "id_nonzero", Expr: "(id <> 0)"},
+				}
 				require.Equal(t.Columns, columns)
 				require.Equal(t.ForeignKeys, fks)
+				require.Equal(t.Attrs[1:], checks)
 			},
 		},
 	}
@@ -220,7 +227,7 @@ CREATE TABLE users(
 	}
 }
 
-func TestRegex_TableConstraint(t *testing.T) {
+func TestRegex_TableFK(t *testing.T) {
 	tests := []struct {
 		input   string
 		matches []string
@@ -268,7 +275,7 @@ CONSTRAINT "c_d_fk" FOREIGN KEY (c, d) REFERENCES "users" (a, b)
 		},
 	}
 	for _, tt := range tests {
-		m := reConstT.FindStringSubmatch(tt.input)
+		m := reFKT.FindStringSubmatch(tt.input)
 		require.Equal(t, len(m) != 0, len(tt.matches) != 0)
 		if len(m) > 0 {
 			require.Equal(t, tt.matches, m[1:])
@@ -276,7 +283,7 @@ CONSTRAINT "c_d_fk" FOREIGN KEY (c, d) REFERENCES "users" (a, b)
 	}
 }
 
-func TestRegex_ColumnConstraint(t *testing.T) {
+func TestRegex_ColumnFK(t *testing.T) {
 	tests := []struct {
 		input   string
 		matches []string
@@ -307,7 +314,7 @@ CREATE TABLE t1 (
 		},
 	}
 	for _, tt := range tests {
-		m := reConstC.FindStringSubmatch(tt.input)
+		m := reFKC.FindStringSubmatch(tt.input)
 		require.Equal(t, len(m) != 0, len(tt.matches) != 0)
 		if len(m) > 0 {
 			require.Equal(t, tt.matches, m[1:])
@@ -315,6 +322,103 @@ CREATE TABLE t1 (
 	}
 }
 
+func TestRegex_Checks(t *testing.T) {
+	tests := []struct {
+		input  string
+		checks []*schema.Check
+	}{
+		{
+			input: `CREATE TABLE pets (id int NOT NULL, owner_id int CONSTRAINT "ck1" CHECK (owner_id <> 0))`,
+			checks: []*schema.Check{
+				{Name: "ck1", Expr: "(owner_id <> 0)"},
+			},
+		},
+		{
+			input: `CREATE TABLE pets (id int NOT NULL, owner_id int CHECK (owner_id <> 0) CONSTRAINT "ck1")`,
+			checks: []*schema.Check{
+				{Expr: "(owner_id <> 0)"},
+			},
+		},
+		{
+			input: `CREATE TABLE pets (id int NOT NULL CHECK ("id" <> 0), owner_id int CONSTRAINT "ck1" CHECK ((owner_id) <> 0))`,
+			checks: []*schema.Check{
+				{Expr: `("id" <> 0)`},
+				{Name: "ck1", Expr: "((owner_id) <> 0)"},
+			},
+		},
+		{
+			input: `CREATE TABLE pets (id int NOT NULL CHECK ("(" <> ')'), owner_id int CONSTRAINT "ck1" CHECK ((owner_id) <> 0))`,
+			checks: []*schema.Check{
+				{Expr: `("(" <> ')')`},
+				{Name: "ck1", Expr: "((owner_id) <> 0)"},
+			},
+		},
+		{
+			input: "CREATE TABLE pets (\n\tid int NOT NULL CHECK (id <> 0) CHECK ((id % 2) = 0)\n,\n\towner_id int CHECK ((owner_id) <> 0)\n)",
+			checks: []*schema.Check{
+				{Expr: "(id <> 0)"},
+				{Expr: "((id % 2) = 0)"},
+				{Expr: "((owner_id) <> 0)"},
+			},
+		},
+		{
+			input: `CREATE TABLE t1(
+				x INTEGER CHECK( x<5 ),
+				y REAL CHECK( y>x ))`,
+			checks: []*schema.Check{
+				{Expr: "( x<5 )"},
+				{Expr: "( y>x )"},
+			},
+		},
+		{
+			input: `CREATE TABLE t(
+				x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=="integer" ),
+				y NUMERIC CONSTRAINT two CHECK( typeof(coalesce(y,0.1))=='real' ),
+				z TEXT CONSTRAINT three CHECK( typeof(coalesce(z,''))=='text' )
+			)`,
+			checks: []*schema.Check{
+				{Name: "one", Expr: `( typeof(coalesce(x,0))=="integer" )`},
+				{Name: "two", Expr: `( typeof(coalesce(y,0.1))=='real' )`},
+				{Name: "three", Expr: `( typeof(coalesce(z,''))=='text' )`},
+			},
+		},
+		{
+			input: `CREATE TABLE t(
+				x char check ('foo''(' <> 1)
+			)`,
+			checks: []*schema.Check{
+				{Expr: `('foo''(' <> 1)`},
+			},
+		},
+		// Invalid inputs.
+		{
+			input: "CREATE TABLE t(x char check)",
+		},
+		{
+			input: "CREATE TABLE t(x char constraint x check)",
+		},
+	}
+	for _, tt := range tests {
+		const name = "users"
+		db, m, err := sqlmock.New()
+		require.NoError(t, err)
+		mk := mock{m}
+		mk.systemVars("3.36.0")
+		mk.tableExists(name, true, tt.input)
+		mk.noColumns(name)
+		mk.noIndexes(name)
+		mk.noFKs(name)
+		drv, err := Open(db)
+		require.NoError(t, err)
+		table, err := drv.InspectTable(context.Background(), name, nil)
+		require.NoError(t, err)
+		require.Equal(t, len(table.Attrs[1:]), len(tt.checks))
+		for i := range tt.checks {
+			require.Equal(t, tt.checks[i], table.Attrs[i+1])
+		}
+	}
+}
+
 type mock struct {
 	sqlmock.Sqlmock
 }
@@ -361,6 +465,11 @@ func (m mock) tableExists(table string, exists bool, stmt ...string) {
 		WillReturnRows(rows)
 }
 
+func (m mock) noColumns(table string) {
+	m.ExpectQuery(sqltest.Escape(fmt.Sprintf(columnsQuery, table))).
+		WillReturnRows(sqlmock.NewRows([]string{"name", "type", "nullable", "dflt_value", "primary"}))
+}
+
 func (m mock) noIndexes(table string) {
 	m.ExpectQuery(sqltest.Escape(fmt.Sprintf(indexesQuery, table))).
 		WillReturnRows(sqlmock.NewRows([]string{"name", "unique", "origin", "partial", "sql"}))
diff --git a/sql/sqlite/migrate_test.go b/sql/sqlite/migrate_test.go
index f24caec8..1e140941 100644
--- a/sql/sqlite/migrate_test.go
+++ b/sql/sqlite/migrate_test.go
@@ -28,13 +28,17 @@ func TestPlanChanges(t *testing.T) {
 							{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}}},
 							{Name: "text", Type: &schema.ColumnType{Type: &schema.StringType{T: "text"}, Null: true}},
 						},
+						Attrs: []schema.Attr{
+							&schema.Check{Expr: "(text <> '')"},
+							&schema.Check{Name: "positive_id", Expr: "(id <> 0)"},
+						},
 					},
 				},
 			},
 			plan: &migrate.Plan{
 				Reversible:    true,
 				Transactional: true,
-				Changes:       []*migrate.Change{{Cmd: "CREATE TABLE `posts` (`id` integer NOT NULL, `text` text NULL)", Reverse: "DROP TABLE `posts`"}},
+				Changes:       []*migrate.Change{{Cmd: "CREATE TABLE `posts` (`id` integer NOT NULL, `text` text NULL, CHECK (text <> ''), CONSTRAINT `positive_id` CHECK (id <> 0))", Reverse: "DROP TABLE `posts`"}},
 			},
 		},
 		{
@@ -96,6 +100,9 @@ func TestPlanChanges(t *testing.T) {
 						Columns: []*schema.Column{
 							{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "bigint"}}},
 						},
+						Attrs: []schema.Attr{
+							&schema.Check{Expr: "(id <> 0)"},
+						},
 					}
 					return &schema.ModifyTable{
 						T: users,
@@ -103,6 +110,9 @@ func TestPlanChanges(t *testing.T) {
 							&schema.DropColumn{
 								C: &schema.Column{Name: "name", Type: &schema.ColumnType{Type: &schema.StringType{T: "varchar(255)"}}},
 							},
+							&schema.AddCheck{
+								C: &schema.Check{Expr: "(id <> 0)"},
+							},
 						},
 					}
 				}(),
@@ -111,7 +121,7 @@ func TestPlanChanges(t *testing.T) {
 				Transactional: true,
 				Changes: []*migrate.Change{
 					{Cmd: "PRAGMA foreign_keys = off"},
-					{Cmd: "CREATE TABLE `new_users` (`id` bigint NOT NULL)", Reverse: "DROP TABLE `new_users`"},
+					{Cmd: "CREATE TABLE `new_users` (`id` bigint NOT NULL, CHECK (id <> 0))", Reverse: "DROP TABLE `new_users`"},
 					{Cmd: "INSERT INTO new_users (id) SELECT id FROM users"},
 					{Cmd: "DROP TABLE `users`"},
 					{Cmd: "ALTER TABLE `new_users` RENAME TO `users`"},