Repository: ariga/atlas
Pull request #2206 (2023-10-23 11:31:13)

doc/website/blog: v0.15 announcement


Patch:
diff --git a/doc/md/atlas-schema/hcl.mdx b/doc/md/atlas-schema/hcl.mdx
index abe096ab..63edc259 100644
--- a/doc/md/atlas-schema/hcl.mdx
+++ b/doc/md/atlas-schema/hcl.mdx
@@ -670,7 +670,7 @@ function "positive" {
   arg "v" {
     type = integer
   }
-  return = integer
+  return = boolean
   as     = "SELECT v > 0"
 }
 
diff --git a/doc/website/blog/2023-10-19-atlas-v-0-15.mdx b/doc/website/blog/2023-10-19-atlas-v-0-15.mdx
new file mode 100644
index 00000000..3ceecc12
--- /dev/null
+++ b/doc/website/blog/2023-10-19-atlas-v-0-15.mdx
@@ -0,0 +1,473 @@
+---
+title: "Announcing v0.15: Interactive Declarative Migrations, Functions, Procedures and Domains"
+authors: rotemtam
+tags: [announcement, atlas, declarative, functions, stored-procedures]
+---
+
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+Hi everyone!
+
+It's been a few weeks since our last version announcement and today I'm happy to share with you  
+[v0.15](https://github.com/ariga/atlas/releases/tag/v0.15.0), which includes some
+very exciting improvements for Atlas:
+
+* **Interactive Declarative Migrations** - Atlas supports a Terraform-like workflow for managing your database schema
+  using the `schema apply` command. In this release we have added a new "Lint and Edit" mode to this command, which
+  will analyze your schema changes for issues and will allow you to edit them interactively before applying them to your
+  database.
+* **Functions and Stored Procedures** - Atlas now supports creating and managing functions and stored procedures in
+  your database schema.
+* **Postgres Domains** - In addition, Atlas now supports [Postgres Domains](https://www.postgresql.org/docs/16/sql-createdomain.html)
+. A domain is essentially a data type with optional constraints (restrictions on the allowed set of values).
+* **TypeORM Integration** - TypeORM is a popular ORM for Node.js. In this release, we are happy to announce the TypeORM
+  integration, which allows you to automatically generate your database schema from your TypeORM entities, create visualizations
+  and more.
+
+Let's dive right in!
+
+### Interactive Declarative Migrations
+
+Atlas supports a Terraform-like workflow for managing your database schema using the `schema apply` command.
+This workflow, which we call "Declarative Migrations", is a modern alternative to the traditional "versioned migrations"
+workflow. In declarative migrations, you define your desired schema in one of the formats supported by Atlas and supply
+a connection string to your database. Atlas compares the current and desired schema of your database and generates a
+plan to migrate your database to the desired state.
+
+Similar to Terraform, until today, Atlas would prompt you to confirm the migration plan before applying it to your
+database. This is a great way to ensure that you don't accidentally apply a migration that you didn't intend to. However,
+this flow suffers from a few drawbacks:
+
+1. *Ensuring Safety* - you can count on Atlas to generate a *correct* migration plan to your desired state,
+   but it's still possible that this migration will have unintended side effects. For example, adding a `UNIQUE`
+   constraint to a column might fail if there are duplicate values in the column.
+2. *Editing* - users often want to make changes to their migration plan before applying it. In the current flow, this
+   requires running `schema apply` with the `--dry-run` flag, saving the output to a file, editing it, and then manually
+   applying the edited migration plan to the database.
+
+#### Enter: Interactive Declarative Migrations
+
+In this release, we are introducing a new "Lint and Edit" mode to the `schema apply` command. This mode is
+available to logged-in users only, as it uses Atlas Cloud to provide a neat UI and rich analysis capabilities.
+Let's see it in action.
+
+Start by downloading the latest version of Atlas:
+
+```
+curl -sSf https://atlasgo.sh | sh
+```
+For installation instructions on other platforms, see the [installation guide](https://atlasgo.io/getting-started#installation).
+
+After installing Atlas, make sure to log in using the `atlas login` command:
+
+```
+atlas login
+```
+
+Next, create a new file named `schema.hcl` that will contain your desired schema:
+
+```hcl title="schema.hcl"
+schema "main" {
+}
+
+table "hello" {
+  schema = schema.main
+  column "name" {
+    type = varchar(100)
+    default = "Anonymous"
+  }
+}
+```
+
+Now, let's apply this schema to a local SQLite database named "sqlite.db":
+
+```
+atlas schema apply -u sqlite://sqlite.db --dev-url sqlite://?mode=memory -f schema.hcl
+```
+Atlas will calculate the diff between the current (empty) state of the database and
+our desired state and prompt us to confirm the migration plan:
+
+```text
+-- Planned Changes:
+-- Create "hello" table
+CREATE TABLE `hello` (`name` varchar NOT NULL DEFAULT 'Anonymous');
+Use the arrow keys to navigate: ↓ ↑ → ←
+? Are you sure?:
+    Apply
+// highlight-next-line
+  ▸ Lint and edit # <-- Brand new!
+    Abort
+```
+
+Notice the new "Lint and edit" option. Select it and press Enter. Atlas will now analyze the migration plan and
+open your browser in the new, interactive migration plan screen. The screen contains three important sections:
+
+* **Migration Plan** - the migration plan generated by Atlas. You can click the "Edit" button to make changes to it.
+* **Checks** - a summary of the checks that Atlas ran against the generated plan. In this case, our plan is completely
+safe, so all checks passed.
+* **ERD** - A visual representation of the change we are planning.
+
+![](https://atlasgo.io/uploads/blog/v0.15/plan.png)
+
+![](https://atlasgo.io/uploads/blog/v0.15/erd.png)
+
+Once we are content with the migration plan, let's go ahead and click the "Approve and Apply" button. Atlas will
+apply the migration plan to the database and scroll down to the execution logs section:
+
+![](https://atlasgo.io/uploads/blog/v0.15/logs-new.png)
+
+Let's edit our desired state a bit to delete the `hello` table and add a new `users` table:
+
+```diff title="schema.hcl"
+schema "main" {
+}
+-table "hello" {
+-  schema = schema.main
+-  column "name" {
+-    type = varchar(100)
+-    default = "Anonymous"
+-  }
+-}
++table "users" {
++  schema = schema.main
++  column "id" {
++    type = int
++  }
++  column "email" {
++    type = text
++  }
++  primary_key {
++    columns = [column.id]
++  }
++  index "unique_email" {
++    columns = [
++      column.email
++    ]
++    unique = true
++  }
++}
+```
+
+Once again, let's run `atlas schema apply` to apply the changes to the database and select the "Lint and Edit" option.
+
+This time, Atlas will warn us that the migration plan is not safe:
+
+![](https://atlasgo.io/uploads/blog/v0.15/interactive-destructive.png)
+
+In this case, we decide to abort the migration in order to not lose the precious data on the
+`hello` table. Good thing we have automatic migration linting on our side!
+
+### Functions and Stored Procedures
+
+:::info
+
+Functions and stored procedures are currently in beta and available to logged-in users only. To
+use this feature run:
+
+```
+atlas login
+```
+
+:::
+
+Over the past few months, we have received numerous requests to support management of [functions](https://www.postgresql.org/docs/current/functions.html)
+and [stored procedures](https://www.postgresql.org/docs/current/sql-createprocedure.html) in popular databases such as
+[PostgreSQL](https://postgresql.org) and [MySQL](https://mysql.com). Functions and stored procedures are a way to encapsulate
+reusable logic in your database and are often used to improve performance by reducing the number of round-trips to the database.
+
+Atlas now supports creating and managing functions and stored procedures in your database schema.
+Let's see how we can use this feature to create a simple function. In our example, we will implement the `leet_speak`
+function for PostgreSQL, which transforms a regular string into its [Leet](https://en.wikipedia.org/wiki/Leet) equivalent!
+
+We can define the desired state of our database in either HCL or SQL:
+
+<Tabs>
+<TabItem value="hcl" label="HCL" default>
+
+```hcl title="schema.hcl"
+function "leet_speak" {
+  schema = schema.public
+  lang   = PLpgSQL
+  arg "input_text" {
+    type = character_varying
+  }
+  return = character_varying
+  as     = <<-SQL
+  DECLARE
+      output_text VARCHAR := '';
+      i INT := 1;
+  BEGIN
+      WHILE i <= LENGTH(input_text) LOOP
+          output_text := output_text ||
+              CASE SUBSTRING(input_text, i, 1)
+                  WHEN 'a' THEN '4'
+                  WHEN 'e' THEN '3'
+                  WHEN 'i' THEN '1'
+                  WHEN 'o' THEN '0'
+                  WHEN 's' THEN '5'
+                  WHEN 't' THEN '7'
+                  ELSE SUBSTRING(input_text, i, 1)
+              END;
+          i := i + 1;
+      END LOOP;
+
+      RETURN output_text;
+  END;
+  SQL
+}
+schema "public" {
+  comment = "standard public schema"
+}
+```
+
+</TabItem>
+<TabItem value="sql" label="SQL">
+
+```sql title="schema.sql"
+CREATE OR REPLACE FUNCTION leet_speak(input_text VARCHAR)
+RETURNS VARCHAR
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    output_text VARCHAR := '';
+    i INT := 1;
+BEGIN
+    WHILE i <= LENGTH(input_text) LOOP
+        output_text := output_text ||
+            CASE SUBSTRING(input_text, i, 1)
+                WHEN 'a' THEN '4'
+                WHEN 'e' THEN '3'
+                WHEN 'i' THEN '1'
+                WHEN 'o' THEN '0'
+                WHEN 's' THEN '5'
+                WHEN 't' THEN '7'
+                ELSE SUBSTRING(input_text, i, 1)
+            END;
+        i := i + 1;
+    END LOOP;
+
+    RETURN output_text;
+END;
+$$;
+```
+
+</TabItem>
+</Tabs>
+
+For the purpose of this demo, we will run a local MySQL Docker container:
+
+```
+docker run --name db -e POSTGRES_PASSWORD=pass -d -p 5432:5432 postgres:16
+```
+
+Now, let's apply our schema to the database:
+
+```
+atlas schema apply -u 'postgres://postgres:pass@localhost:5432/postgres?sslmode=disable&search_path=public' --to file://schema.hcl
+```
+
+Atlas will calculate the diff between the current (empty) state of the database and
+our desired state and prompt us to confirm the migration plan:
+
+```text
+-- Planned Changes:
+-- Create "leet_speak" function
+CREATE FUNCTION "leet_speak" ("input_text" character varying) RETURNS character varying LANGUAGE PLpgSQL AS $$
+DECLARE
+    output_text VARCHAR := '';
+    i INT := 1;
+BEGIN
+    WHILE i <= LENGTH(input_text) LOOP
+        output_text := output_text ||
+            CASE SUBSTRING(input_text, i, 1)
+                WHEN 'a' THEN '4'
+                WHEN 'e' THEN '3'
+                WHEN 'i' THEN '1'
+                WHEN 'o' THEN '0'
+                WHEN 's' THEN '5'
+                WHEN 't' THEN '7'
+                ELSE SUBSTRING(input_text, i, 1)
+            END;
+        i := i + 1;
+    END LOOP;
+
+    RETURN output_text;
+END;
+$$;
+Use the arrow keys to navigate: ↓ ↑ → ←
+? Are you sure?:
+  ▸ Apply
+    Abort
+```
+
+Let's go ahead and select the "Apply" option. Atlas will apply the migration plan to the database and print the
+following output:
+
+```text
+✔ Apply
+```
+
+We can now verify that the function was created successfully by running:
+
+```
+docker exec -it db psql -U postgres -c "SELECT leet_speak('hello leet world')"
+```
+
+And the result indeed is:
+
+```text
+    leet_speak
+------------------
+ h3ll0 l337 w0rld
+(1 row)
+```
+
+To learn more about functions and stored procedures in Atlas, check out the [documentation](https://atlasgo.io/atlas-schema/hcl#function).
+
+### Postgres Domains
+
+:::info
+
+Support for domains is currently in beta and available to logged-in users only. To
+use this feature run:
+
+```
+atlas login
+```
+
+:::
+
+Another highly requested feature was support for [Postgres Domains](https://www.postgresql.org/docs/16/sql-createdomain.html).
+A domain is essentially a data type with optional constraints (restrictions on the allowed set of values). For example,
+you might want to define an `email_address` domain which would be a `varchar` column with a `CHECK` constraint to ensure
+that the value is a valid email address.
+
+Starting with v0.15, Atlas can now manage domains in your database schema, as well as use them as types for table columns.
+Let's see an example schema that uses domains:
+
+```hcl
+domain "us_postal_code" {
+  schema = schema.public
+  type   = text
+  null   = true
+  check "us_postal_code_check" {
+    expr = "((VALUE ~ '^\\d{5}$'::text) OR (VALUE ~ '^\\d{5}-\\d{4}$'::text))"
+  }
+}
+
+domain "username" {
+  schema = schema.public
+  type    = text
+  null    = false
+  default = "anonymous"
+  check "username_length" {
+    expr = "(length(VALUE) > 3)"
+  }
+}
+
+table "users" {
+  schema = schema.public
+  column "name" {
+    type = domain.username
+  }
+  column "zip" {
+    type = domain.us_postal_code
+  }
+}
+
+schema "public" {
+  comment = "standard public schema"
+}
+```
+
+The above schema defines two domains: `us_postal_code` and `username`. The `us_postal_code` domain is a `text` column
+with a `CHECK` constraint to ensure that the value is a valid US postal code. The `username` domain is a `text` column
+with a `CHECK` constraint to ensure that the value is at least 4 characters long. We then define a `users` table that
+uses these domains for its columns.
+
+Let's see what happens when we apply this schema to a local Postgres database:
+
+```
+atlas schema apply -u 'postgres://postgres:pass@localhost:5432/postgres?sslmode=disable' -f schema.hcl
+```
+
+Atlas calculates the diff between the current (empty) state of the database and
+our desired state and prompts us to confirm the migration plan:
+
+```text
+-- Planned Changes:
+-- Create domain type "us_postal_code"
+CREATE DOMAIN "public"."us_postal_code" AS text CONSTRAINT "us_postal_code_check" CHECK ((VALUE ~ '^\d{5}$'::text) OR (VALUE ~ '^\d{5}-\d{4}$'::text));
+-- Create domain type "username"
+CREATE DOMAIN "public"."username" AS text DEFAULT 'anonymous' NOT NULL CONSTRAINT "username_length" CHECK (length(VALUE) > 3);
+-- Create "users" table
+CREATE TABLE "public"."users" ("name" "public"."username" NOT NULL, "zip" "public"."us_postal_code" NOT NULL);
+Use the arrow keys to navigate: ↓ ↑ → ←
+? Are you sure?:
+  ▸ Apply
+    Abort
+```
+
+After applying, let's re-run the `schema apply` command to make sure that the schema is up-to-date:
+
+```text
+atlas schema apply -u 'postgres://postgres:pass@localhost:5432/postgres?sslmode=disable' --to file://schema.hcl
+```
+Indeed, Atlas reports that the schema is up-to-date:
+
+```text
+Schema is synced, no changes to be made
+```
+
+### Support for TypeORM
+
+[TypeORM](https://typeorm.io/) is a popular ORM for Node.js. In this release, we are happy to announce the TypeORM
+integration, which allows you to automatically generate your database schema from your TypeORM entities, create visualizations,
+and more.
+
+The [TypeORM Atlas Provider](https://github.com/ariga/atlas-provider-typeorm) is a Node.js module that can extract
+the desired schema of your database directly from your TypeORM entities. To use it, first install:
+```
+npm i @ariga/atlas-provider-typeorm
+```
+
+Next, add the TypeORM schema as a data source in your `atlas.hcl` file:
+
+```hcl
+data "external_schema" "typeorm" {
+  program = [
+    "npx",
+    "@ariga/atlas-provider-typeorm",
+    "load",
+    "--path", "./path/to/entities",
+    "--dialect", "mysql", // mariadb | postgres | sqlite | mssql
+  ]
+}
+
+env "typeorm" {
+  src = data.external_schema.typeorm.url
+  dev = "docker://mysql/8/dev"
+  migration {
+    dir = "file://migrations"
+  }
+  format {
+    migrate {
+      diff = "{{ sql . \"  \" }}"
+    }
+  }
+}
+```
+
+Finally, run `atlas schema apply` to apply the schema to your database:
+
+```
+atlas schema apply -u mysql://<db credentials> --env typeorm
+```
+
+To learn more about the TypeORM integration, check out the [documentation](https://atlasgo.io/guides/orms/typeorm).
+
+### Wrapping up
+
+That's it! I hope you try out (and enjoy) all of these new features and find them useful.
+As always, we would love to hear your feedback and suggestions on our [Discord server](https://discord.gg/zZ6sWVg6NT).
diff --git a/sql/internal/sqlx/exclude.go b/sql/internal/sqlx/exclude.go
index ea24e845..da4d9df3 100644
--- a/sql/internal/sqlx/exclude.go
+++ b/sql/internal/sqlx/exclude.go
@@ -131,6 +131,24 @@ func excludeS(s *schema.Schema, glob []string) error {
 		}
 		s.Views = views
 	}
+	if globF, exclude := excludeType(typeFn, glob[0]); exclude {
+		var err error
+		s.Funcs, err = filter(s.Funcs, func(f *schema.Func) (bool, error) {
+			return filepath.Match(globF, f.Name)
+		})
+		if err != nil {
+			return err
+		}
+	}
+	if globP, exclude := excludeType(typePr, glob[0]); exclude {
+		var err error
+		s.Procs, err = filter(s.Procs, func(p *schema.Proc) (bool, error) {
+			return filepath.Match(globP, p.Name)
+		})
+		if err != nil {
+			return err
+		}
+	}
 	return nil
 }
 
@@ -198,12 +216,14 @@ func excludeV(t *schema.View, pattern string) (err error) {
 }
 
 const (
-	typeV = "view"
-	typeT = "table"
-	typeC = "column"
-	typeI = "index"
-	typeF = "fk"
-	typeK = "check"
+	typeV  = "view"
+	typeT  = "table"
+	typeC  = "column"
+	typeI  = "index"
+	typeF  = "fk"
+	typeK  = "check"
+	typeFn = "function"
+	typePr = "procedure"
 )
 
 var reTypeVT = regexp.MustCompile(`\[type=([a-z|]+)+\]$`)
diff --git a/sql/schema/schema.go b/sql/schema/schema.go
index 5eb9ce45..1d0d8f00 100644
--- a/sql/schema/schema.go
+++ b/sql/schema/schema.go
@@ -398,6 +398,7 @@ type (
 		T         string
 		Precision *int
 		Scale     *int
+		Attrs     []Attr
 	}
 
 	// JSONType represents a JSON type.


Test patch:
diff --git a/sql/internal/sqlx/exclude_test.go b/sql/internal/sqlx/exclude_test.go
index 99cd2f0a..0fd0c26b 100644
--- a/sql/internal/sqlx/exclude_test.go
+++ b/sql/internal/sqlx/exclude_test.go
@@ -102,6 +102,16 @@ func TestExcludeRealm_Selector(t *testing.T) {
 			).
 			AddViews(
 				schema.NewView("v1", "SELECT * FROM t1"),
+			).
+			AddFuncs(
+				&schema.Func{Name: "f1"},
+				&schema.Func{Name: "f2"},
+				&schema.Func{Name: "f3"},
+			).
+			AddProcs(
+				&schema.Proc{Name: "p1"},
+				&schema.Proc{Name: "p2"},
+				&schema.Proc{Name: "p3"},
 			),
 	)
 	r, err := ExcludeRealm(r, []string{"*.*[type=table]"})
@@ -109,6 +119,21 @@ func TestExcludeRealm_Selector(t *testing.T) {
 	require.Empty(t, r.Schemas[0].Tables)
 	require.Len(t, r.Schemas[0].Views, 1)
 
+	r, err = ExcludeRealm(r, []string{"*.f1[type=function]"})
+	require.NoError(t, err)
+	require.Len(t, r.Schemas[0].Funcs, 2)
+	require.Equal(t, []string{"f2", "f3"}, []string{r.Schemas[0].Funcs[0].Name, r.Schemas[0].Funcs[1].Name})
+
+	r, err = ExcludeRealm(r, []string{"*.p2[type=procedure]"})
+	require.NoError(t, err)
+	require.Len(t, r.Schemas[0].Procs, 2)
+	require.Equal(t, []string{"p1", "p3"}, []string{r.Schemas[0].Procs[0].Name, r.Schemas[0].Procs[1].Name})
+
+	r, err = ExcludeRealm(r, []string{"*.*[type=procedure|function]"})
+	require.NoError(t, err)
+	require.Empty(t, r.Schemas[0].Funcs)
+	require.Empty(t, r.Schemas[0].Procs)
+
 	r.Schemas[0].AddTables(
 		schema.NewTable("t1"),
 		schema.NewTable("t2"),
@@ -161,6 +186,7 @@ func TestExcludeRealm_Checks(t *testing.T) {
 	require.NoError(t, err)
 	require.Len(t, r.Schemas[0].Tables[0].Attrs, 0)
 }
+
 func TestExcludeRealm_Columns(t *testing.T) {
 	r := schema.NewRealm(
 		schema.New("s1").AddTables(