Repository: ariga/atlas
Pull request #2669 (2024-04-09 09:59:40)

doc/clickhouse: add guide to connect clickhouse cloud

datdao: > Also add this to the ClickHouse getting started guide

Added to Clickhouse's guide https://atlasgo.io/guides/clickhouse

Patch:
diff --git a/cmd/atlas/internal/cmdapi/cmdapi_oss.go b/cmd/atlas/internal/cmdapi/cmdapi_oss.go
index d06a6eae..6510b185 100644
--- a/cmd/atlas/internal/cmdapi/cmdapi_oss.go
+++ b/cmd/atlas/internal/cmdapi/cmdapi_oss.go
@@ -17,6 +17,7 @@ import (
 	"ariga.io/atlas/cmd/atlas/internal/migratelint"
 	"ariga.io/atlas/schemahcl"
 	"ariga.io/atlas/sql/migrate"
+	"ariga.io/atlas/sql/schema"
 	"ariga.io/atlas/sql/sqlcheck"
 	"ariga.io/atlas/sql/sqlclient"
 
@@ -135,3 +136,8 @@ func setEnvs(context.Context, []*Env) {}
 
 // specOptions are the options for the schema spec.
 var specOptions []schemahcl.Option
+
+// diffOptions returns environment-aware diff options.
+func diffOptions(_ *cobra.Command, env *Env) []schema.DiffOption {
+	return env.DiffOptions()
+}
diff --git a/cmd/atlas/internal/cmdapi/migrate.go b/cmd/atlas/internal/cmdapi/migrate.go
index f7af1862..ee68e3fa 100644
--- a/cmd/atlas/internal/cmdapi/migrate.go
+++ b/cmd/atlas/internal/cmdapi/migrate.go
@@ -679,7 +679,7 @@ func migrateDiffRun(cmd *cobra.Command, args []string, flags migrateDiffFlags, e
 			Indent:  indent,
 			Dir:     dir,
 			Dev:     dev,
-			Options: env.DiffOptions(),
+			Options: diffOptions(cmd, env),
 		})
 		return maskNoPlan(cmd, err)
 	}
@@ -698,7 +698,7 @@ func migrateDiffRun(cmd *cobra.Command, args []string, flags migrateDiffFlags, e
 	opts := []migrate.PlannerOption{
 		migrate.PlanFormat(f),
 		migrate.PlanWithIndent(indent),
-		migrate.PlanWithDiffOptions(env.DiffOptions()...),
+		migrate.PlanWithDiffOptions(diffOptions(cmd, env)...),
 	}
 	if dev.URL.Schema != "" {
 		// Disable tables qualifier in schema-mode.
diff --git a/cmd/atlas/internal/cmdapi/schema.go b/cmd/atlas/internal/cmdapi/schema.go
index 70071ba5..72aea8cb 100644
--- a/cmd/atlas/internal/cmdapi/schema.go
+++ b/cmd/atlas/internal/cmdapi/schema.go
@@ -181,7 +181,7 @@ func schemaApplyRun(cmd *cobra.Command, flags schemaApplyFlags, env *Env) error
 		return err
 	}
 	defer to.Close()
-	diff, err := computeDiff(ctx, client, from, to, env.DiffOptions()...)
+	diff, err := computeDiff(ctx, client, from, to, diffOptions(cmd, env)...)
 	if err != nil {
 		return err
 	}
@@ -433,7 +433,7 @@ func schemaDiffRun(cmd *cobra.Command, _ []string, flags schemaDiffFlags, env *E
 			return fmt.Errorf("parse log format: %w", err)
 		}
 	}
-	diff, err := computeDiff(ctx, c, from, to, env.DiffOptions()...)
+	diff, err := computeDiff(ctx, c, from, to, diffOptions(cmd, env)...)
 	if err != nil {
 		return err
 	}
diff --git a/doc/md/concepts/url.mdx b/doc/md/concepts/url.mdx
index 834a8971..4c0d6f33 100644
--- a/doc/md/concepts/url.mdx
+++ b/doc/md/concepts/url.mdx
@@ -146,6 +146,17 @@ Connecting to a specific ClickHouse schema (database) with a username and passwo
 clickhouse://user:pass@localhost:9000/schema
 ```
 
+Connecting to a specific ClickHouse schema with SSL enabled:
+```shell
+clickhouse://user:pass@localhost:9000/schema?secure=true
+```
+
+To connect [ClickHouse Cloud](https://clickhouse.com/cloud),
+we need to use native protocol port [`9440`](https://clickhouse.com/docs/en/guides/sre/network-ports) with SSL enabled:
+```shell
+clickhouse://user:pass@CLICKHOUSE-CLOUD-HOST:9440/schema?secure=true
+```
+
 </TabItem>
 <TabItem value="docker">
 
diff --git a/doc/md/guides/clickhouse.mdx b/doc/md/guides/clickhouse.mdx
index 7aac9a0f..2a7b8d1a 100644
--- a/doc/md/guides/clickhouse.mdx
+++ b/doc/md/guides/clickhouse.mdx
@@ -68,6 +68,12 @@ Let's start off by spinning up a database using Docker:
 docker run --rm -d --name atlas-demo -e CLICKHOUSE_DB=demo -p 9000:9000 clickhouse/clickhouse-server:latest
 ```
 
+If you are already using ClickHouse Cloud and want to inspect it, 
+you can work with Atlas through [Url](../concepts/url) and utilize it throughout the guide.
+```
+clickhouse://user:pass@CLICKHOUSE-CLOUD-HOST:9440/schema?secure=true
+```
+
 For this example we will begin with a minimal database with a `users` table and an `id` as the primary key.
 
 ```sql
diff --git a/sql/internal/sqlx/diff.go b/sql/internal/sqlx/diff.go
index edde2ac9..a01ebb48 100644
--- a/sql/internal/sqlx/diff.go
+++ b/sql/internal/sqlx/diff.go
@@ -232,6 +232,7 @@ func (d *Diff) schemaDiff(from, to *schema.Schema, opts *schema.DiffOptions) ([]
 			return nil, err
 		}
 	}
+
 	// Drop or modify views.
 	for _, v1 := range from.Views {
 		v2, ok := findView(to, v1)
@@ -241,7 +242,9 @@ func (d *Diff) schemaDiff(from, to *schema.Schema, opts *schema.DiffOptions) ([]
 			changes = opts.AddOrSkip(changes, &schema.DropView{V: v1})
 			continue
 		}
-		if change := d.indexDiffV(v1, v2, opts); d.viewDefChanged(v1, v2) || d.ViewAttrChanged(v1, v2) || len(change) > 0 {
+		if change, err := d.indexDiffV(v1, v2, opts); err != nil {
+			return nil, err
+		} else if len(change) > 0 || d.viewDefChanged(v1, v2) || d.ViewAttrChanged(v1, v2) {
 			changes = opts.AddOrSkip(changes, &schema.ModifyView{From: v1, To: v2, Changes: change})
 		}
 		if change, err := d.triggerDiff(v1, v2, v1.Triggers, v2.Triggers, opts); err != nil {
@@ -308,37 +311,18 @@ func (d *Diff) tableDiff(from, to *schema.Table, opts *schema.DiffOptions) ([]sc
 	}
 	changes = append(changes, change...)
 
-	// Drop or modify columns.
-	for _, c1 := range from.Columns {
-		c2, ok := to.Column(c1.Name)
-		if !ok {
-			changes = opts.AddOrSkip(changes, &schema.DropColumn{C: c1})
-			continue
-		}
-		change, err := d.ColumnChange(from, c1, c2)
-		if err != nil {
-			return nil, err
-		}
-		if change != schema.NoChange {
-			changes = opts.AddOrSkip(changes, &schema.ModifyColumn{
-				From:   c1,
-				To:     c2,
-				Change: change,
-			})
-		}
-	}
-	// Add columns.
-	for _, c1 := range to.Columns {
-		if _, ok := from.Column(c1.Name); !ok {
-			changes = opts.AddOrSkip(changes, &schema.AddColumn{
-				C: c1,
-			})
-		}
+	// Drop, add or modify columns.
+	if change, err = d.columnDiff(from, to, opts); err != nil {
+		return nil, err
 	}
+	changes = append(changes, change...)
 
 	// Primary-key and index changes.
 	changes = append(changes, d.pkDiff(from, to, opts)...)
-	changes = append(changes, d.indexDiffT(from, to, opts)...)
+	if change, err = d.indexDiffT(from, to, opts); err != nil {
+		return nil, err
+	}
+	changes = append(changes, change...)
 
 	// Drop or modify foreign-keys.
 	for _, fk1 := range from.ForeignKeys {
@@ -394,6 +378,39 @@ func addViewChange(v *schema.View) []schema.Change {
 	return changes
 }
 
+// columnDiff returns the schema changes (if any) for migrating table columns.
+func (d *Diff) columnDiff(from, to *schema.Table, opts *schema.DiffOptions) ([]schema.Change, error) {
+	var changes []schema.Change
+	// Drop or modify columns.
+	for _, c1 := range from.Columns {
+		c2, ok := to.Column(c1.Name)
+		if !ok {
+			changes = opts.AddOrSkip(changes, &schema.DropColumn{C: c1})
+			continue
+		}
+		change, err := d.ColumnChange(from, c1, c2)
+		if err != nil {
+			return nil, err
+		}
+		if change != schema.NoChange {
+			changes = opts.AddOrSkip(changes, &schema.ModifyColumn{
+				From:   c1,
+				To:     c2,
+				Change: change,
+			})
+		}
+	}
+	// Add columns.
+	for _, c1 := range to.Columns {
+		if _, ok := from.Column(c1.Name); !ok {
+			changes = opts.AddOrSkip(changes, &schema.AddColumn{
+				C: c1,
+			})
+		}
+	}
+	return d.askForColumns(from, changes, opts)
+}
+
 // pkDiff returns the schema changes (if any) for migrating table
 // primary-key from current state to the desired state.
 func (d *Diff) pkDiff(from, to *schema.Table, opts *schema.DiffOptions) (changes []schema.Change) {
@@ -418,7 +435,7 @@ func (d *Diff) pkDiff(from, to *schema.Table, opts *schema.DiffOptions) (changes
 
 // indexDiffT returns the schema changes (if any) for migrating table
 // indexes from current state to the desired state.
-func (d *Diff) indexDiffT(from, to *schema.Table, opts *schema.DiffOptions) []schema.Change {
+func (d *Diff) indexDiffT(from, to *schema.Table, opts *schema.DiffOptions) ([]schema.Change, error) {
 	var (
 		changes []schema.Change
 		exists  = make(map[*schema.Index]bool)
@@ -457,7 +474,7 @@ func (d *Diff) indexDiffT(from, to *schema.Table, opts *schema.DiffOptions) []sc
 			changes = opts.AddOrSkip(changes, &schema.AddIndex{I: idx})
 		}
 	}
-	return changes
+	return d.askForIndexes(from.Name, changes, opts)
 }
 
 // viewDefChanged checks if the view definition has changed.
@@ -473,7 +490,7 @@ func (d *Diff) viewDefChanged(v1 *schema.View, v2 *schema.View) bool {
 
 // indexDiffV returns the schema changes (if any) for migrating view
 // indexes from current state to the desired state.
-func (d *Diff) indexDiffV(from, to *schema.View, opts *schema.DiffOptions) []schema.Change {
+func (d *Diff) indexDiffV(from, to *schema.View, opts *schema.DiffOptions) ([]schema.Change, error) {
 	var (
 		changes []schema.Change
 		exists  = make(map[*schema.Index]bool)
@@ -504,7 +521,7 @@ func (d *Diff) indexDiffV(from, to *schema.View, opts *schema.DiffOptions) []sch
 			changes = opts.AddOrSkip(changes, &schema.AddIndex{I: idx})
 		}
 	}
-	return changes
+	return d.askForIndexes(from.Name, changes, opts)
 }
 
 // indexChange returns the schema changes (if any) for migrating one index to the other.
diff --git a/sql/internal/sqlx/sqlx_oss.go b/sql/internal/sqlx/sqlx_oss.go
index 4a3aebfb..75c84525 100644
--- a/sql/internal/sqlx/sqlx_oss.go
+++ b/sql/internal/sqlx/sqlx_oss.go
@@ -30,3 +30,11 @@ func funcDep(_, _ *schema.Func) bool {
 func procDep(_, _ *schema.Proc) bool {
 	return false // unimplemented.
 }
+
+func (*Diff) askForColumns(_ *schema.Table, changes []schema.Change, _ *schema.DiffOptions) ([]schema.Change, error) {
+	return changes, nil // unimplemented.
+}
+
+func (*Diff) askForIndexes(_ string, changes []schema.Change, _ *schema.DiffOptions) ([]schema.Change, error) {
+	return changes, nil // unimplemented.
+}
diff --git a/sql/postgres/inspect.go b/sql/postgres/inspect.go
index cb094763..1df32566 100644
--- a/sql/postgres/inspect.go
+++ b/sql/postgres/inspect.go
@@ -1066,6 +1066,12 @@ type (
 		schema.Clause
 	}
 
+	// NotValid describes the NOT VALID clause for the creation
+	// of check and foreign-key constraints.
+	NotValid struct {
+		schema.Clause
+	}
+
 	// NoInherit attribute defines the NO INHERIT flag for CHECK constraint.
 	// https://postgresql.org/docs/current/catalog-pg-constraint.html
 	NoInherit struct {
diff --git a/sql/postgres/migrate.go b/sql/postgres/migrate.go
index 0250632c..f72ce261 100644
--- a/sql/postgres/migrate.go
+++ b/sql/postgres/migrate.go
@@ -551,14 +551,19 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 				b.P("DROP CONSTRAINT").Ident(pkName(t, change.P))
 				reverse = append(reverse, &schema.AddPrimaryKey{P: change.P})
 			case *schema.AddForeignKey:
-				b.P("ADD")
-				s.fks(b, change.F)
+				s.fks(b.P("ADD"), change.F)
+				if sqlx.Has(change.Extra, &NotValid{}) {
+					b.P("NOT VALID")
+				}
 				reverse = append(reverse, &schema.DropForeignKey{F: change.F})
 			case *schema.DropForeignKey:
 				b.P("DROP CONSTRAINT").Ident(change.F.Symbol)
 				reverse = append(reverse, &schema.AddForeignKey{F: change.F})
 			case *schema.AddCheck:
 				check(b.P("ADD"), change.C)
+				if sqlx.Has(change.Extra, &NotValid{}) {
+					b.P("NOT VALID")
+				}
 				// Reverse operation is supported if
 				// the constraint name is not generated.
 				if reversible = reversible && change.C.Name != ""; reversible {
diff --git a/sql/schema/migrate.go b/sql/schema/migrate.go
index 7fb3685c..f91eafbf 100644
--- a/sql/schema/migrate.go
+++ b/sql/schema/migrate.go
@@ -273,7 +273,8 @@ type (
 
 	// AddForeignKey describes a foreign-key creation change.
 	AddForeignKey struct {
-		F *ForeignKey
+		F     *ForeignKey
+		Extra []Clause // Extra clauses and options.
 	}
 
 	// DropForeignKey describes a foreign-key removal change.
@@ -289,7 +290,8 @@ type (
 
 	// AddCheck describes a CHECK constraint creation change.
 	AddCheck struct {
-		C *Check
+		C     *Check
+		Extra []Clause // Extra clauses and options.
 	}
 
 	// DropCheck describes a CHECK constraint removal change.
@@ -418,6 +420,10 @@ type (
 		// Extra defines per-driver configuration. If not
 		// nil, should be set to schemahcl.Extension.
 		Extra any // avoid circular dependency with schemahcl.
+
+		// AskFunc can be implemented by the caller to
+		// make diff process interactive.
+		AskFunc func(string, []string) (string, error)
 	}
 
 	// DiffOption allows configuring the DiffOptions using functional options.
diff --git a/sql/sqlite/migrate.go b/sql/sqlite/migrate.go
index db9ab878..fc838f0e 100644
--- a/sql/sqlite/migrate.go
+++ b/sql/sqlite/migrate.go
@@ -416,6 +416,14 @@ func (s *state) copyRows(from *schema.Table, to *schema.Table, changes []schema.
 					return false, fmt.Errorf("duplicate changes for column: %q: %T, %T", column.Name, change, c)
 				}
 				change = changes[i]
+			case *schema.RenameColumn:
+				if c.To.Name != column.Name {
+					break
+				}
+				if change != nil {
+					return false, fmt.Errorf("duplicate changes for column: %q: %T, %T", column.Name, change, c)
+				}
+				change = changes[i]
 			case *schema.DropColumn:
 				if c.C.Name == column.Name {
 					return false, fmt.Errorf("unexpected drop column: %q", column.Name)
@@ -439,6 +447,9 @@ func (s *state) copyRows(from *schema.Table, to *schema.Table, changes []schema.
 			} else {
 				fromC = append(fromC, column.Name)
 			}
+		case *schema.RenameColumn:
+			toC = append(toC, change.To.Name)
+			fromC = append(fromC, change.From.Name)
 		// Columns without changes should be transferred as-is.
 		case nil:
 			toC = append(toC, column.Name)


Test patch:
diff --git a/sql/postgres/migrate_test.go b/sql/postgres/migrate_test.go
index 0b0f81f0..38ede5fd 100644
--- a/sql/postgres/migrate_test.go
+++ b/sql/postgres/migrate_test.go
@@ -596,6 +596,10 @@ func TestPlanChanges(t *testing.T) {
 							&schema.AddCheck{
 								C: &schema.Check{Name: "name_not_empty", Expr: `("name" <> '')`},
 							},
+							&schema.AddCheck{
+								C:     &schema.Check{Name: "positive_id", Expr: `("id" > 0)`},
+								Extra: []schema.Clause{&NotValid{}},
+							},
 							&schema.DropCheck{
 								C: &schema.Check{Name: "id_nonzero", Expr: `("id" <> 0)`},
 							},
@@ -686,8 +690,8 @@ func TestPlanChanges(t *testing.T) {
 						Reverse: `CREATE INDEX CONCURRENTLY "drop_con" ON "users" ("id")`,
 					},
 					{
-						Cmd:     `ALTER TABLE "users" DROP CONSTRAINT "id_nonzero", ADD COLUMN "name" character varying(255) NOT NULL DEFAULT 'logged_in', ADD COLUMN "last" character varying(255) NOT NULL DEFAULT 'logged_in', ADD CONSTRAINT "name_not_empty" CHECK ("name" <> ''), DROP CONSTRAINT "id_iseven", ADD CONSTRAINT "id_iseven" CHECK (("id") % 2 = 0)`,
-						Reverse: `ALTER TABLE "users" DROP CONSTRAINT "id_iseven", ADD CONSTRAINT "id_iseven" CHECK ("id" % 2 = 0), DROP CONSTRAINT "name_not_empty", DROP COLUMN "last", DROP COLUMN "name", ADD CONSTRAINT "id_nonzero" CHECK ("id" <> 0)`,
+						Cmd:     `ALTER TABLE "users" DROP CONSTRAINT "id_nonzero", ADD COLUMN "name" character varying(255) NOT NULL DEFAULT 'logged_in', ADD COLUMN "last" character varying(255) NOT NULL DEFAULT 'logged_in', ADD CONSTRAINT "name_not_empty" CHECK ("name" <> ''), ADD CONSTRAINT "positive_id" CHECK ("id" > 0) NOT VALID, DROP CONSTRAINT "id_iseven", ADD CONSTRAINT "id_iseven" CHECK (("id") % 2 = 0)`,
+						Reverse: `ALTER TABLE "users" DROP CONSTRAINT "id_iseven", ADD CONSTRAINT "id_iseven" CHECK ("id" % 2 = 0), DROP CONSTRAINT "positive_id", DROP CONSTRAINT "name_not_empty", DROP COLUMN "last", DROP COLUMN "name", ADD CONSTRAINT "id_nonzero" CHECK ("id" <> 0)`,
 					},
 					{
 						Cmd:     `CREATE INDEX "id_key" ON "users" ("id" DESC) WHERE success`,
diff --git a/sql/sqlite/migrate_test.go b/sql/sqlite/migrate_test.go
index 0dc18210..9608aedf 100644
--- a/sql/sqlite/migrate_test.go
+++ b/sql/sqlite/migrate_test.go
@@ -198,6 +198,7 @@ func TestPlanChanges(t *testing.T) {
 					users := schema.NewTable("users").
 						AddColumns(
 							schema.NewIntColumn("id", "bigint"),
+							schema.NewIntColumn("name", "text"),
 							schema.NewIntColumn("nid", "bigint").
 								SetGeneratedExpr(&schema.GeneratedExpr{Expr: "1", Type: "STORED"}),
 						)
@@ -205,7 +206,12 @@ func TestPlanChanges(t *testing.T) {
 						T: users,
 						Changes: []schema.Change{
 							&schema.AddColumn{
-								C: users.Columns[1],
+								C: users.Columns[2],
+							},
+							// Rename should be respected in INSERT command.
+							&schema.RenameColumn{
+								From: schema.NewColumn("uname"),
+								To:   users.Columns[1],
 							},
 						},
 					}
@@ -215,8 +221,8 @@ func TestPlanChanges(t *testing.T) {
 				Transactional: true,
 				Changes: []*migrate.Change{
 					{Cmd: "PRAGMA foreign_keys = off"},
-					{Cmd: "CREATE TABLE `new_users` (`id` bigint NOT NULL, `nid` bigint NOT NULL AS (1) STORED)", Reverse: "DROP TABLE `new_users`"},
-					{Cmd: "INSERT INTO `new_users` (`id`) SELECT `id` FROM `users`"},
+					{Cmd: "CREATE TABLE `new_users` (`id` bigint NOT NULL, `name` text NOT NULL, `nid` bigint NOT NULL AS (1) STORED)", Reverse: "DROP TABLE `new_users`"},
+					{Cmd: "INSERT INTO `new_users` (`id`, `name`) SELECT `id`, `uname` FROM `users`"},
 					{Cmd: "DROP TABLE `users`"},
 					{Cmd: "ALTER TABLE `new_users` RENAME TO `users`"},
 					{Cmd: "PRAGMA foreign_keys = on"},