Repository: ariga/atlas
Pull request #1274 (2022-11-07 22:58:20)

doc/md/guides/mysql: Add doc about descending indexes in MySQL


Patch:
diff --git a/.github/workflows/ci.yml b/.github/workflows/ci.yml
index e0dfff0a..a8c0ad84 100644
--- a/.github/workflows/ci.yml
+++ b/.github/workflows/ci.yml
@@ -37,6 +37,8 @@ jobs:
           key: ${{ runner.os }}-go-${{ hashFiles('**/go.sum') }}
           restore-keys: |
             ${{ runner.os }}-go-
+      - name: Install stringer
+        run: go install golang.org/x/tools/cmd/stringer@latest
       - name: run "go generate ./..."
         run: go generate ./...
       - name: go generate cmd/atlas
@@ -591,3 +593,4 @@ jobs:
       - name: Run integration tests for cockroach
         working-directory: internal/integration
         run: go test -race -count=2 -v -run="Cockroach" -version="cockroach" ./...
+
diff --git a/cmd/atlas/internal/cmdapi/schema.go b/cmd/atlas/internal/cmdapi/schema.go
index d8d5a671..137ca562 100644
--- a/cmd/atlas/internal/cmdapi/schema.go
+++ b/cmd/atlas/internal/cmdapi/schema.go
@@ -272,6 +272,7 @@ The database states can be read from a connected database, an HCL project or a m
 			Example: `  atlas schema diff --from mysql://user:pass@localhost:3306/test --to file://schema.hcl
   atlas schema diff --from mysql://user:pass@localhost:3306 --to file://schema_1.hcl --to file://schema_2.hcl
   atlas schema diff --from mysql://user:pass@localhost:3306 --to file://migrations`,
+			PreRunE: schemaFlagsFromEnv,
 			RunE: func(cmd *cobra.Command, args []string) error {
 				return schemaDiffRun(cmd, args, flags)
 			},
diff --git a/doc/md/guides/deploying/init.md b/doc/md/guides/deploying/init.md
new file mode 100644
index 00000000..09110f2d
--- /dev/null
+++ b/doc/md/guides/deploying/init.md
@@ -0,0 +1,160 @@
+---
+id: k8s-init-containers
+slug: /guides/deploying/k8s-init-container
+title: Deploying schema migrations to Kubernetes with Init Containers
+---
+
+In [Kubernetes](https://kubernetes.io), [Init Containers](https://kubernetes.io/docs/concepts/workloads/pods/init-containers/)
+are specialized containers that run before app containers in a Pod. Init containers
+can contain utilities or setup scripts not present in an app image. 
+
+Init containers can be utilized to run schema migrations with Atlas before the
+application loads. Because init containers can use a container image different
+from the application, developers can use a [purpose-built image](image.md) that
+only contains Atlas and the migration scripts to run them.  This way, less 
+can be included in the application runtime environment, which reduces
+the attack surface from a security perspective. 
+
+Depending on an application's [deployment strategy](https://kubernetes.io/docs/concepts/workloads/controllers/deployment/#strategy),
+multiple replicas of an init container may run concurrently.  In the case of
+schema migrations, this can cause a dangerous race condition with unknown outcomes.
+To prevent this, in databases that support advisory locking, Atlas will acquire
+a lock on the migration operation before running migrations, making the
+operation mutually exclusive.
+
+In this guide, we demonstrate how schema migrations can be integrated into
+a Kubernetes deployment using an init container.
+
+Prerequisites to the guide:
+1. [A migrations docker image](/guides/deploying/image)
+2. [A Kubernetes Deployment manifest](https://kubernetes.io/docs/concepts/workloads/controllers/deployment/) defining
+   your application.
+3. A running Kubernetes cluster to work against.
+
+## Adding an init container
+
+Suppose our deployment manifest looks similar to this:
+
+```yaml
+apiVersion: apps/v1
+kind: Deployment
+metadata:
+  name: nginx-deployment
+  labels:
+    app: nginx
+spec:
+  replicas: 1
+  selector:
+    matchLabels:
+      app: nginx
+  template:
+    metadata:
+      labels:
+        app: nginx
+    spec:
+      containers:
+      - name: nginx
+        image: nginx:1.14.2
+        ports:
+        - containerPort: 80
+```
+
+Now, let's say our [migration container image](image.md) which contains the Atlas binary
+and our migration scripts is available at `ghcr.io/repo/migrations:v0.1.2`. We would like
+to run `migrate apply` against our target database residing at `mysql://root:s3cr37p455@dbhostname.io:3306/db`. 
+
+We will use a Kubernetes [Secret](https://kubernetes.io/docs/concepts/configuration/secret/) to
+store a [config file](/atlas-schema/projects) containing the credentials to our database. Create the file:
+
+```hcl title=atlas.hcl
+env "k8s" {
+  url = "mysql://root:s3cr37p455@dbhostname.io:3306/db"
+}
+```
+
+Kubernetes accepts secrets encoded as base64 strings. Let's calculate the
+base64 string representing our project file:
+
+```text
+cat atlas.hcl | base64
+```
+Copy the result:
+```text
+ZW52ICJrOHMiIHsKICB1cmwgPSAibXlzcWw6Ly9yb290OnMzY3IzN3A0NTVAZGJob3N0bmFtZS5pbzozMzA2L2RiIgp9Cg==
+```
+
+Create the secret manifest:
+
+```yaml
+apiVersion: v1
+kind: Secret
+metadata:
+  name: atlas-project
+type: Opaque
+data:
+  atlas.hcl: ZW52ICJrOHMiIHsKICB1cmwgPSAibXlzcWw6Ly9yb290OnMzY3IzN3A0NTVAZGJob3N0bmFtZS5pbzozMzA2L2RiIgp9Cg==
+```
+
+Apply the secret on the cluster:
+
+```yaml
+kubectl apply -f secret.yaml
+```
+
+The secret is created:
+```text
+secret/atlas-project created
+```
+
+Next, add a volume to mount the config file and an init container using it to
+the deployment manifest: 
+
+```yaml
+apiVersion: apps/v1
+kind: Deployment
+metadata:
+  name: nginx-deployment
+  labels:
+    app: nginx
+spec:
+  replicas: 1
+  selector:
+    matchLabels:
+      app: nginx
+  template:
+    metadata:
+      labels:
+        app: nginx
+    spec:
+      // highlight-start
+      volumes:
+      - name: atlas-project
+        secret:
+          secretName: atlas-project
+      initContainers:
+      - name: migrate
+        image: ghcr.io/repo/migrations:v0.1.2
+        imagePullPolicy: Always
+        args: ["migrate", "apply", "-c", "file:///etc/atlas/atlas.hcl", "--env", "k8s"]
+        volumeMounts:
+        - name: atlas-project
+          mountPath: "/etc/atlas"
+      // highlight-end
+      containers:
+      - name: nginx
+        image: nginx:1.14.2
+        ports:
+        - containerPort: 80
+```
+
+Notice the new configuration blocks we added to our deployment manifest:
+* We added our secret `atlas-project` [as a volume](https://kubernetes.io/docs/tasks/configure-pod-container/configure-volume-storage/#configure-a-volume-for-a-pod) to the
+  the deployment's PodSpec.
+* We add an `initContainer` named `migrate` that runs the `ghcr.io/repo/migrations:v0.1.2` image.
+* We mounted the `atlas-project` volume at `/etc/atlas` in our init container.
+* We configured our init container to run with these flags: `["migrate", "apply", "-c", "file:///etc/atlas/atlas.hcl", "--env", "k8s"]`
+
+## Wrapping up
+
+That's it! After we apply our new deployment manifest, Kubernetes will first run 
+the init container and only then run the application containers.  
\ No newline at end of file
diff --git a/doc/md/guides/guides.md b/doc/md/guides/guides.md
index 6b94d45d..ab04bf1b 100644
--- a/doc/md/guides/guides.md
+++ b/doc/md/guides/guides.md
@@ -16,10 +16,14 @@ Welcome to the Atlas Guides.
 
 [Deploying schema migrations to Kubernetes with Helm](deploying/helm.md)
 
+[Deploying schema migrations to Kubernetes with Init Containers](deploying/init.md)
+
 ## Databases
 
 ### MySQL
 
+[Descending Indexes](mysql/descending-indexes.md)
+
 [Generated Columns](mysql/generated-columns.md)
 
 [Managing MySQL schemas on RDS with Terraform](mysql/terraform.md)
diff --git a/doc/md/guides/mysql/descending-indexes.md b/doc/md/guides/mysql/descending-indexes.md
new file mode 100644
index 00000000..22328c72
--- /dev/null
+++ b/doc/md/guides/mysql/descending-indexes.md
@@ -0,0 +1,364 @@
+---
+id: descending-indexes
+title: Descending Indexes in MySQL
+slug: /guides/mysql/descending-indexes
+---
+
+### What are the descending indexes?
+In general, indexes with ascending or descending order help increase the performance of queries with the “ORDER BY” clause. Descending indexes are indexes where key values are stored in descending order.
+
+#### When are descending indexes helpful?
+In versions prior to MySQL 8.0, scanning an index in reverse order had a very high cost, which resulted in reduced performance for certain queries. 
+Since the release of MySQL version 8.0, users can now create descending indexes, which can be scanned in forward order, thus increasing efficiency of scanning for certain queries with the ORDER BY clause.
+
+### Syntax
+Here is how you can define descending indexes by using DESC in a table definition:
+
+```sql
+CREATE TABLE t (
+  c1 INT, c2 INT,
+  INDEX idx1 (c1, c2 DESC),
+  INDEX idx2 (c1 DESC, c2),
+  INDEX idx3 (c1 DESC, c2 DESC)
+);
+```
+Or, you can add a descending index to an existing table with the following syntax:
+```sql
+CREATE INDEX 
+    index_name_idx 
+ON 
+    table_name(column_name DESC);
+```
+:::info
+ASC or DESC specifier is used to specify whether index values are stored in ascending or descending order.
+
+By default, the index values are stored in ascending order if no specifier is given (e.g. Column `c1` in the index `idx1` and column `c2` in the index `idx2` from the table definition above)`
+:::
+
+### Example
+Let’s create a table which represents data of an ISP’s subscribers along with their email addresses and broadband data usage with the following command:
+
+```sql
+CREATE TABLE `telecom_data` (
+  `id` bigint(8) unsigned NOT NULL auto_increment,
+  `email_address` varchar(255) default NULL,
+  `user_name` varchar(255) default NULL,
+  `megabytes_used` bigint default NULL,
+  PRIMARY KEY (`id`)
+) AUTO_INCREMENT=1;
+```
+Here is how a portion of the table might look like after inserting values:
+
+```sql
+SELECT * FROM telecom_data
+```
+```console title="Output"
++----+---------------------------------------+--------------------+----------------+
+| id | email_address                         | user_name          | megabytes_used |
++----+---------------------------------------+--------------------+----------------+
+|  1 | erat.Etiam.vestibulum@inhendrerit.edu | Chancellor Puckett |           1359 |
+|  2 | Cras.eu@nectempusmauris.edu           | Joel Nunez         |          83495 |
+|  3 | Etiam@eleifendvitae.com               | Rogan Wright       |          16104 |
+|  4 | gravida.mauris@ac.org                 | William Conrad     |          71934 |
+|  5 | convallis@Nullatincidunt.ca           | Magee Ayers        |          62180 |
+|  6 | tortor.Nunc@neque.edu                 | Demetrius Hanson   |          94045 |
+|  7 | Curabitur@luctusaliquet.ca            | Adrian Mccall      |           3337 |
+|  8 | purus@Phasellusdolor.org              | Evan Moreno        |          88898 |
+|  9 | mattis.semper.dui@magnisdis.edu       | Theodore Russo     |            374 |
+| 10 | enim@dolordolortempus.net             | Walter Knight      |          44358 |
+.
+.
+.
+| 98452 | volutpat.Nulla@tempor.ca                    | Jared Gardner  |          47607 |
+| 98451 | id.erat@temporaugueac.edu                   | Lars Moreno    |          93351 |
+| 98450 | est.tempor.bibendum@enimcondimentumeget.net | Len Harmon     |          71194 |
+| 98449 | dis@idmagna.ca                              | Dylan Foreman  |          20388 |
+| 98448 | eu.augue.porttitor@Aliquamerat.edu          | Samuel Lott    |          24077 |
+| 98447 | erat@dolorNullasemper.edu                   | Graiden Peters |           7781 |
+| 98446 | tincidunt.congue@nuncinterdum.org           | Ulric Atkins   |          31194 |
+| 98445 | a@utnisia.co.uk                             | Cedric Larson  |          35201 |
+| 98444 | gravida.non.sollicitudin@Curabitur.edu      | Cain Odonnell  |          85089 |
+| 98443 | turpis@enimEtiam.org                        | Kasper Payne   |          72018 |
++-------+---------------------------------------------+----------------+----------------+
+```
+We do not have any indexes other than the primary index on the `id` column. Now, suppose we want information about all subscribers who have used less than 100 MB data, but in descending order. Let's query that data with the following command:
+
+```sql
+SELECT * FROM telecom_data WHERE megabytes_used < 100 ORDER BY megabytes_used DESC;
+```
+```console title="Output"
++-------+------------------------------------------------+-------------------+----------------+
+| id    | email_address                                  | user_name         | megabytes_used |
++-------+------------------------------------------------+-------------------+----------------+
+| 27383 | augue.Sed@malesuadaiderat.edu                  | Marsden Hurst     |             98 |
+| 66371 | sem.elit@nislsem.ca                            | Finn Hayden       |             98 |
+| 14929 | Nam.nulla.magna@aliquet.edu                    | Merritt Potts     |             97 |
+| 18789 | aliquet.diam.Sed@acurnaUt.ca                   | Camden Cash       |             97 |
+| 43361 | tincidunt.pede.ac@molestie.ca                  | Kareem Bond       |             96 |
+| 85812 | aliquet@id.org                                 | Nash Crawford     |             90 |
+| 86340 | ac@magnaPhasellus.ca                           | Cedric Patrick    |             89 |
+| 93004 | Integer.tincidunt.aliquam@mauris.ca            | George Crosby     |             89 |
+| 85158 | ante.Nunc.mauris@arcu.co.uk                    | Victor Willis     |             88 |
+|  3036 | gravida.Praesent@cursusdiamat.ca               | Tiger Dale        |             87 |
+|  9784 | nec.cursus@euismodenimEtiam.ca                 | Yuli Alford       |             86 |
+| 43017 | Sed.nec@ornareegestas.net                      | Jesse Thornton    |             86 |
+| 73056 | Donec.at.arcu@lectusa.org                      | Quinn Orr         |             86 |
+.
+.
+.
+| 19932 | ligula.tortor@cursus.co.uk                     | Thane Estrada     |              5 |
+| 63591 | sapien.imperdiet@velit.edu                     | Adrian Zimmerman  |              5 |
+| 69586 | rutrum.non@Nulla.com                           | Byron Daniels     |              5 |
+| 58926 | mi.ac.mattis@Nullatinciduntneque.org           | Aladdin Gomez     |              4 |
+|  4782 | nunc.sed.libero@Sed.edu                        | Preston Hernandez |              2 |
+|  8134 | Sed@molestietellusAenean.net                   | Ali Horn          |              2 |
++-------+------------------------------------------------+-------------------+----------------+
+```
+Now, let's see how much the query cost, with the following command:
+
+```sql
+SHOW STATUS LIKE 'Last_query_cost';
+```
+```console title="Output"
++-----------------+---------------+
+| Variable_name   | Value         |
++-----------------+---------------+
+| Last_query_cost | 108034.649000 |
++-----------------+---------------+
+```
+
+Notice that the query cost 108034.65 units.
+
+Let's check how MySQL resolves the query by running the following command and observing the EXTRA section:
+
+:::info
+You can learn more about information from `EXPLAIN EXTRA` at the official documentation [here](https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information).
+:::
+
+
+```sql
+EXPLAIN SELECT * FROM telecom_data WHERE megabytes_used < 100 ORDER BY megabytes_used DESC\G
+```
+:::info
+The client command `\G` is used in order to display the results vertically. To know more about client commands, visit the [documentation](https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html).
+:::
+```console title="Output"
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: telecom_data
+   partitions: NULL
+         type: ALL
+possible_keys: NULL
+          key: NULL
+      key_len: NULL
+          ref: NULL
+         rows: 98104
+     filtered: 33.33
+        Extra: Using where; Using filesort
+1 row in set, 1 warning (0.00 sec)
+```
+Observe that MySQL is using the `filesort` operation in order to resolve the query.
+
+:::info
+A `filesort` operation uses temporary disk files as necessary if the result set is too large to fit in memory. To learn more about how filesort is used to satisfy `ORDER BY` clause in MySQL, visit [here](https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html#order-by-filesort)
+:::
+
+Now, let's try to optimize the query by using a descending index. Let's create a descending index on the `megabytes_used` column with the following command:
+
+```sql
+CREATE INDEX fastscan_idx ON telecom_data(megabytes_used DESC);
+```
+```console title="Output"
+Query OK, 0 rows affected (0.25 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+```
+Now, let's run the following query again and check the cost:
+```sql
+SELECT * FROM telecom_data WHERE megabytes_used < 100 ORDER BY megabytes_used DESC;
+```
+```console title="Output"
++-------+------------------------------------------------+-------------------+----------------+
+| id    | email_address                                  | user_name         | megabytes_used |
++-------+------------------------------------------------+-------------------+----------------+
+| 27383 | augue.Sed@malesuadaiderat.edu                  | Marsden Hurst     |             98 |
+| 66371 | sem.elit@nislsem.ca                            | Finn Hayden       |             98 |
+| 14929 | Nam.nulla.magna@aliquet.edu                    | Merritt Potts     |             97 |
+| 18789 | aliquet.diam.Sed@acurnaUt.ca                   | Camden Cash       |             97 |
+| 43361 | tincidunt.pede.ac@molestie.ca                  | Kareem Bond       |             96 |
+.
+.
+|  8134 | Sed@molestietellusAenean.net                   | Ali Horn          |              2 |
++-------+------------------------------------------------+-------------------+----------------+
+```
+```sql
+SHOW STATUS LIKE 'Last_query_cost';
+```
+```console title="Output"
++-----------------+-----------+
+| Variable_name   | Value     |
++-----------------+-----------+
+| Last_query_cost | 52.009000 |
++-----------------+-----------+
+```
+(Note: The results will vary, ​​depending on the data that is stored in the database)
+
+Amazing! Now our query cost only 52.01 units, compared to 108034.65 units earlier when the descending index was not used. 
+
+Let's check how MySQL resolves the query in this case:
+
+```sql
+EXPLAIN SELECT * FROM telecom_data WHERE megabytes_used < 100 ORDER BY megabytes_used DESC\G
+```
+```console title="Output"
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: telecom_data
+   partitions: NULL
+         type: range
+possible_keys: fastscan_idx
+          key: fastscan_idx
+      key_len: 4
+          ref: NULL
+         rows: 115
+     filtered: 100.00
+        Extra: Using index condition
+1 row in set, 1 warning (0.00 sec)
+```
+
+Observe that MySQL has used the index we created in order to resolve the query this time.
+
+Additionally, you can check whether a descending index is being used in a query or not by checking `(reverse)` along the name of the index, while running the `EXPLAIN` command with `FORMAT=TREE` option. Here is an example:
+
+
+```sql
+EXPLAIN FORMAT=TREE SELECT * FROM telecom_data WHERE megabytes_used < 100 ORDER BY megabytes_used ASC\G
+```
+```console title="Output"
+*************************** 1. row ***************************
+EXPLAIN: -> Index range scan on telecom_data using fastscan_idx over (100 < megabytes_used < NULL) (reverse), with index condition: (telecom_data.megabytes_used < 100)  (cost=52.01 rows=115)
+1 row in set (0.00 sec)
+```
+
+:::info
+Descending indexes are supported only for the InnoDB storage engine. You can learn more about Descending Indexes and their limitations in MySQL from the [official documentation](https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html).
+:::
+
+We have seen that creating a descending index is a smart choice when using queries with `ORDER BY` clauses. Now, let's see how we can easily manage descending indexes in MySQL using Atlas.
+
+### Managing Descending Indexes is easy with Atlas
+
+Atlas is an open-source project which allows us to manage our database using a simple and easy-to-understand declarative syntax (similar to Terraform).
+
+:::info
+If you are just getting started, install the latest version of Atlas using the guide to [setting up Atlas](https://atlasgo.io/cli/getting-started/setting-up).
+:::
+
+#### Managing Descending Indexes with Atlas
+
+We will first use the `atlas schema inspect` command to get an HCL representation of the table we created earlier (without any indexes) by using the Atlas CLI:
+
+```console
+atlas schema inspect -u "mysql://root:@localhost:3306/telecom_data" > schema.hcl
+```
+```hcl title="schema.hcl"
+table "telecom_data" {
+  schema = schema.telecom_data
+  column "id" {
+    null           = false
+    type           = bigint
+    unsigned       = true
+    auto_increment = true
+  }
+  column "email_address" {
+    null = true
+    type = varchar(255)
+  }
+  column "user_name" {
+    null = true
+    type = varchar(255)
+  }
+  column "megabytes_used" {
+    null = true
+    type = bigint
+  }
+  primary_key {
+    columns = [column.id]
+  }
+}
+schema "telecom_data" {
+  charset = "utf8mb4"
+  collate = "utf8mb4_0900_ai_ci"
+}
+```
+Now, lets add the following index definition to the file:
+```hcl
+  index "fastscan_idx" {
+    on {
+      desc   = true
+      column = column.megabytes_used
+    }
+  }
+```
+
+Save the file and apply the schema changes on the database by using the following command:
+
+```console
+atlas schema apply --url "mysql://root:@localhost:3306/telecom_data" -f schema.hcl
+```
+
+Atlas generates the necessary SQL statements to add the new descending index to the database schema. Press Enter while the `Apply` option is highlighted to apply the changes:
+
+```console title="Output"
+-- Planned Changes:
+-- Modify "telecom_data" table
+ALTER TABLE `telecom_data`.`telecom_data` ADD INDEX `fastscan_idx` (`megabytes_used` DESC)
+Use the arrow keys to navigate: ↓ ↑ → ← 
+? Are you sure?: 
+  ▸ Apply
+    Abort
+```
+
+To verify that our new index was created, open the database command line tool from the previous step and run:
+
+```sql
+show index from telecom_data\G
+```
+```console title="Output"
+*************************** 1. row ***************************
+        Table: telecom_data
+   Non_unique: 0
+     Key_name: PRIMARY
+.
+.
+.
+*************************** 2. row ***************************
+        Table: telecom_data
+   Non_unique: 1
+     Key_name: fastscan_idx
+ Seq_in_index: 1
+  Column_name: megabytes_used
+    Collation: D
+  Cardinality: 62544
+     Sub_part: NULL
+       Packed: NULL
+         Null: YES
+   Index_type: BTREE
+      Comment: 
+Index_comment: 
+      Visible: YES
+   Expression: NULL
+2 rows in set (0.00 sec)
+```
+Amazing! Our new descending index is now created as seen on row no. 2!
+
+### Wrapping up
+
+In this guide, we demonstrated how to create and use descending indexes in MySQL to optimize queries with `ORDER BY` clause, and how we can use Atlas to easily manage descending indexes in MySQL database.
+
+## Need More Help?​
+
+[Join the Ariga Discord Server](https://discord.com/invite/zZ6sWVg6NT) for early access to features and the ability to provide exclusive feedback that improves your Database Management Tooling. 
+
+[Sign up](https://www.getrevue.co/profile/ariga) to our newsletter to stay up to date about [Atlas](https://atlasgo.io), our OSS database schema management tool, and our cloud platform [Ariga Cloud](https://ariga.cloud).
diff --git a/doc/website/sidebars.js b/doc/website/sidebars.js
index d49e65cf..057a1ee9 100644
--- a/doc/website/sidebars.js
+++ b/doc/website/sidebars.js
@@ -116,6 +116,11 @@ module.exports = {
                     id: 'guides/deploying/helm',
                     label: 'Kubernetes (Helm)'
                 },
+                {
+                    type: 'doc',
+                    id: 'guides/deploying/k8s-init-containers',
+                    label: 'Kubernetes (Init Container)'
+                },
             ]
         },
         {
@@ -123,6 +128,11 @@ module.exports = {
             label: 'MySQL',
             collapsed: false,
             items: [
+                {
+                    type: 'doc',
+                    id: 'guides/mysql/descending-indexes',
+                    label: 'Descending Indexes'
+                  },
                 {
                   type: 'doc',
                   id: 'guides/mysql/generated-columns',
diff --git a/internal/ci/ci.tmpl b/internal/ci/ci.tmpl
index 44d9a963..7860b101 100644
--- a/internal/ci/ci.tmpl
+++ b/internal/ci/ci.tmpl
@@ -37,6 +37,8 @@ jobs:
           key: {{ "${{ runner.os }}-go-${{ hashFiles('**/go.sum') }}" }}
           restore-keys: |
             {{ "${{ runner.os }}-go-" }}
+      - name: Install stringer
+        run: go install golang.org/x/tools/cmd/stringer@latest
       - name: run "go generate ./..."
         run: go generate ./...
       - name: go generate cmd/atlas
@@ -166,4 +168,4 @@ jobs:
       - name: Run integration tests for {{ .Version }}
         working-directory: internal/integration
         run: go test -race -count=2 -v -run="{{ .Regex }}" -version="{{ .Version }}" ./...
-{{ end }}
\ No newline at end of file
+{{ end }}
diff --git a/sql/mysql/diff.go b/sql/mysql/diff.go
index 758c23bd..92c44346 100644
--- a/sql/mysql/diff.go
+++ b/sql/mysql/diff.go
@@ -16,6 +16,11 @@ import (
 	"ariga.io/atlas/sql/schema"
 )
 
+// DefaultDiff provides basic diffing capabilities for MySQL dialects.
+// Note, it is recommended to call Open, create a new Driver and use its
+// Differ when a database connection is available.
+var DefaultDiff schema.Differ = &sqlx.Diff{DiffDriver: &diff{}}
+
 // A diff provides a MySQL implementation for sqlx.DiffDriver.
 type diff struct {
 	conn
diff --git a/sql/postgres/diff.go b/sql/postgres/diff.go
index 8cf6fd0e..71059fb3 100644
--- a/sql/postgres/diff.go
+++ b/sql/postgres/diff.go
@@ -17,6 +17,11 @@ import (
 	"ariga.io/atlas/sql/schema"
 )
 
+// DefaultDiff provides basic diffing capabilities for PostgreSQL dialects.
+// Note, it is recommended to call Open, create a new Driver and use its Differ
+// when a database connection is available.
+var DefaultDiff schema.Differ = &sqlx.Diff{DiffDriver: &diff{}}
+
 // A diff provides a PostgreSQL implementation for sqlx.DiffDriver.
 type diff struct{ conn }
 
@@ -81,12 +86,16 @@ func (d *diff) defaultChanged(from, to *schema.Column) (bool, error) {
 	if trimCast(d1) == trimCast(d2) || quote(d1) == quote(d2) {
 		return false, nil
 	}
-	// Use database comparison in case of mismatch (e.g. `SELECT ARRAY[1] = '{1}'::int[]`).
-	equals, err := d.valuesEqual(d1, d2)
-	if err != nil {
-		return false, err
+	var (
+		err    error
+		equals bool
+	)
+	// In case a database connection is available (not the DefaultDiff), we use the
+	// database comparison in case of mismatch (e.g. `SELECT ARRAY[1] = '{1}'::int[]`).
+	if d.conn.ExecQuerier != nil {
+		equals, err = d.valuesEqual(d1, d2)
 	}
-	return !equals, nil
+	return !equals, err
 }
 
 // generatedChanged reports if the generated expression of a column was changed.
diff --git a/sql/postgres/inspect.go b/sql/postgres/inspect.go
index 8ece5326..59536d53 100644
--- a/sql/postgres/inspect.go
+++ b/sql/postgres/inspect.go
@@ -5,6 +5,7 @@
 package postgres
 
 import (
+	"bytes"
 	"context"
 	"database/sql"
 	"fmt"
@@ -413,16 +414,8 @@ func mayAppendOps(part *schema.IndexPart, name string, params string, defaults b
 		return nil
 	}
 	op := &IndexOpClass{Name: name, Default: defaults}
-	switch {
-	case len(params) == 0:
-	case len(params) < 2, params[0] != '{', params[len(params)-1] != '}':
-		return fmt.Errorf("postgres: unexpected operator class parameters format: %q", params)
-	default:
-		for _, e := range strings.Split(params[1:len(params)-1], ",") {
-			if kv := strings.Split(strings.TrimSpace(e), "="); len(kv) == 2 {
-				op.Params = append(op.Params, struct{ N, V string }{N: kv[0], V: kv[1]})
-			}
-		}
+	if err := op.parseParams(params); err != nil {
+		return err
 	}
 	part.Attrs = append(part.Attrs, op)
 	return nil
@@ -975,6 +968,34 @@ func (o *IndexOpClass) String() string {
 	return b.String()
 }
 
+// UnmarshalText parses the operator class from its string representation.
+func (o *IndexOpClass) UnmarshalText(text []byte) error {
+	i := bytes.IndexByte(text, '(')
+	if i == -1 {
+		o.Name = string(text)
+		return nil
+	}
+	o.Name = string(text[:i])
+	return o.parseParams(string(text[i:]))
+}
+
+// parseParams parses index class parameters defined in HCL or returned
+// from the database. For example: '{k=v}', '(k1=v1,k2=v2)'.
+func (o *IndexOpClass) parseParams(kv string) error {
+	switch {
+	case kv == "":
+	case strings.HasPrefix(kv, "(") && strings.HasSuffix(kv, ")"), strings.HasPrefix(kv, "{") && strings.HasSuffix(kv, "}"):
+		for _, e := range strings.Split(kv[1:len(kv)-1], ",") {
+			if kv := strings.Split(strings.TrimSpace(e), "="); len(kv) == 2 {
+				o.Params = append(o.Params, struct{ N, V string }{N: kv[0], V: kv[1]})
+			}
+		}
+	default:
+		return fmt.Errorf("postgres: unexpected operator class parameters format: %q", kv)
+	}
+	return nil
+}
+
 // newIndexStorage parses and returns the index storage parameters.
 func newIndexStorage(opts string) (*IndexStorageParams, error) {
 	params := &IndexStorageParams{}
diff --git a/sql/postgres/sqlspec.go b/sql/postgres/sqlspec.go
index 0b27f0e6..9ad89ac0 100644
--- a/sql/postgres/sqlspec.go
+++ b/sql/postgres/sqlspec.go
@@ -354,13 +354,12 @@ func convertPart(spec *sqlspec.IndexPart, part *schema.IndexPart) error {
 		if err != nil {
 			return err
 		}
-		switch parts := strings.Split(expr.X, "("); {
-		case len(parts) == 1 && parts[0] != "":
-			part.Attrs = append(part.Attrs, &IndexOpClass{Name: parts[0]})
-		case len(parts) == 2 && strings.HasSuffix(parts[1], ")"):
-			return mayAppendOps(part, parts[0], fmt.Sprintf("{%s}", parts[1][:len(parts[1])-1]), false)
-		default:
-			return fmt.Errorf("unexpected index.on.ops expression %q", expr.X)
+		var op IndexOpClass
+		if err := op.UnmarshalText([]byte(expr.X)); err != nil {
+			return fmt.Errorf("unexpected index.on.ops expression %q: %w", expr.X, err)
+		}
+		if op.Name != "" {
+			part.Attrs = append(part.Attrs, &op)
 		}
 	case opc.IsRef():
 		name, err := opc.Ref()
diff --git a/sql/schema/changekind_string.go b/sql/schema/changekind_string.go
new file mode 100644
index 00000000..eec1ef9b
--- /dev/null
+++ b/sql/schema/changekind_string.go
@@ -0,0 +1,55 @@
+// Code generated by "stringer -type ChangeKind"; DO NOT EDIT.
+
+package schema
+
+import "strconv"
+
+func _() {
+	// An "invalid array index" compiler error signifies that the constant values have changed.
+	// Re-run the stringer command to generate them again.
+	var x [1]struct{}
+	_ = x[NoChange-0]
+	_ = x[ChangeAttr-1]
+	_ = x[ChangeCharset-2]
+	_ = x[ChangeCollate-4]
+	_ = x[ChangeComment-8]
+	_ = x[ChangeNull-16]
+	_ = x[ChangeType-32]
+	_ = x[ChangeDefault-64]
+	_ = x[ChangeGenerated-128]
+	_ = x[ChangeUnique-256]
+	_ = x[ChangeParts-512]
+	_ = x[ChangeColumn-1024]
+	_ = x[ChangeRefColumn-2048]
+	_ = x[ChangeRefTable-4096]
+	_ = x[ChangeUpdateAction-8192]
+	_ = x[ChangeDeleteAction-16384]
+}
+
+const _ChangeKind_name = "NoChangeChangeAttrChangeCharsetChangeCollateChangeCommentChangeNullChangeTypeChangeDefaultChangeGeneratedChangeUniqueChangePartsChangeColumnChangeRefColumnChangeRefTableChangeUpdateActionChangeDeleteAction"
+
+var _ChangeKind_map = map[ChangeKind]string{
+	0:     _ChangeKind_name[0:8],
+	1:     _ChangeKind_name[8:18],
+	2:     _ChangeKind_name[18:31],
+	4:     _ChangeKind_name[31:44],
+	8:     _ChangeKind_name[44:57],
+	16:    _ChangeKind_name[57:67],
+	32:    _ChangeKind_name[67:77],
+	64:    _ChangeKind_name[77:90],
+	128:   _ChangeKind_name[90:105],
+	256:   _ChangeKind_name[105:117],
+	512:   _ChangeKind_name[117:128],
+	1024:  _ChangeKind_name[128:140],
+	2048:  _ChangeKind_name[140:155],
+	4096:  _ChangeKind_name[155:169],
+	8192:  _ChangeKind_name[169:187],
+	16384: _ChangeKind_name[187:205],
+}
+
+func (i ChangeKind) String() string {
+	if str, ok := _ChangeKind_map[i]; ok {
+		return str
+	}
+	return "ChangeKind(" + strconv.FormatInt(int64(i), 10) + ")"
+}
diff --git a/sql/schema/migrate.go b/sql/schema/migrate.go
index 5067ae14..d41ffeb9 100644
--- a/sql/schema/migrate.go
+++ b/sql/schema/migrate.go
@@ -186,6 +186,8 @@ type (
 
 // A ChangeKind describes a change kind that can be combined
 // using a set of flags. The zero kind is no change.
+//
+//go:generate stringer -type ChangeKind
 type ChangeKind uint
 
 const (
diff --git a/sql/sqlite/diff.go b/sql/sqlite/diff.go
index 17072064..5d55bb37 100644
--- a/sql/sqlite/diff.go
+++ b/sql/sqlite/diff.go
@@ -14,17 +14,22 @@ import (
 	"ariga.io/atlas/sql/schema"
 )
 
-// A Diff provides a SQLite implementation for sqlx.DiffDriver.
-type Diff struct{}
+// DefaultDiff provides basic diffing capabilities for MySQL dialects.
+// Note, it is recommended to call Open, create a new Driver and use its
+// Differ when a database connection is available.
+var DefaultDiff schema.Differ = &sqlx.Diff{DiffDriver: &diff{}}
+
+// A diff provides a SQLite implementation for sqlx.DiffDriver.
+type diff struct{}
 
 // SchemaAttrDiff returns a changeset for migrating schema attributes from one state to the other.
-func (d *Diff) SchemaAttrDiff(_, _ *schema.Schema) []schema.Change {
+func (d *diff) SchemaAttrDiff(_, _ *schema.Schema) []schema.Change {
 	// No special schema attribute diffing for SQLite.
 	return nil
 }
 
 // TableAttrDiff returns a changeset for migrating table attributes from one state to the other.
-func (d *Diff) TableAttrDiff(from, to *schema.Table) ([]schema.Change, error) {
+func (d *diff) TableAttrDiff(from, to *schema.Table) ([]schema.Change, error) {
 	var changes []schema.Change
 	switch {
 	case sqlx.Has(from.Attrs, &WithoutRowID{}) && !sqlx.Has(to.Attrs, &WithoutRowID{}):
@@ -40,7 +45,7 @@ func (d *Diff) TableAttrDiff(from, to *schema.Table) ([]schema.Change, error) {
 }
 
 // ColumnChange returns the schema changes (if any) for migrating one column to the other.
-func (d *Diff) ColumnChange(_ *schema.Table, from, to *schema.Column) (schema.ChangeKind, error) {
+func (d *diff) ColumnChange(_ *schema.Table, from, to *schema.Column) (schema.ChangeKind, error) {
 	change := sqlx.CommentChange(from.Attrs, to.Attrs)
 	if from.Type.Null != to.Type.Null {
 		change |= schema.ChangeNull
@@ -62,7 +67,7 @@ func (d *Diff) ColumnChange(_ *schema.Table, from, to *schema.Column) (schema.Ch
 }
 
 // typeChanged reports if the column type was changed.
-func (d *Diff) typeChanged(from, to *schema.Column) (bool, error) {
+func (d *diff) typeChanged(from, to *schema.Column) (bool, error) {
 	fromT, toT := from.Type.Type, to.Type.Type
 	if fromT == nil || toT == nil {
 		return false, fmt.Errorf("sqlite: missing type information for column %q", from.Name)
@@ -72,7 +77,7 @@ func (d *Diff) typeChanged(from, to *schema.Column) (bool, error) {
 }
 
 // defaultChanged reports if the default value of a column was changed.
-func (d *Diff) defaultChanged(from, to *schema.Column) bool {
+func (d *diff) defaultChanged(from, to *schema.Column) bool {
 	d1, ok1 := sqlx.DefaultValue(from)
 	d2, ok2 := sqlx.DefaultValue(to)
 	if ok1 != ok2 {
@@ -87,7 +92,7 @@ func (d *Diff) defaultChanged(from, to *schema.Column) bool {
 }
 
 // generatedChanged reports if the generated expression of a column was changed.
-func (*Diff) generatedChanged(from, to *schema.Column) bool {
+func (*diff) generatedChanged(from, to *schema.Column) bool {
 	var (
 		fromX, toX     schema.GeneratedExpr
 		fromHas, toHas = sqlx.Has(from.Attrs, &fromX), sqlx.Has(to.Attrs, &toX)
@@ -97,7 +102,7 @@ func (*Diff) generatedChanged(from, to *schema.Column) bool {
 
 // IsGeneratedIndexName reports if the index name was generated by the database.
 // See: https://github.com/sqlite/sqlite/blob/e937df8/src/build.c#L3583.
-func (d *Diff) IsGeneratedIndexName(t *schema.Table, idx *schema.Index) bool {
+func (d *diff) IsGeneratedIndexName(t *schema.Table, idx *schema.Index) bool {
 	p := fmt.Sprintf("sqlite_autoindex_%s_", t.Name)
 	if !strings.HasPrefix(idx.Name, p) {
 		return false
@@ -107,18 +112,18 @@ func (d *Diff) IsGeneratedIndexName(t *schema.Table, idx *schema.Index) bool {
 }
 
 // IndexAttrChanged reports if the index attributes were changed.
-func (*Diff) IndexAttrChanged(from, to []schema.Attr) bool {
+func (*diff) IndexAttrChanged(from, to []schema.Attr) bool {
 	var p1, p2 IndexPredicate
 	return sqlx.Has(from, &p1) != sqlx.Has(to, &p2) || (p1.P != p2.P && p1.P != sqlx.MayWrap(p2.P))
 }
 
 // IndexPartAttrChanged reports if the index-part attributes were changed.
-func (*Diff) IndexPartAttrChanged(_, _ *schema.Index, _ int) bool {
+func (*diff) IndexPartAttrChanged(_, _ *schema.Index, _ int) bool {
 	return false
 }
 
 // ReferenceChanged reports if the foreign key referential action was changed.
-func (*Diff) ReferenceChanged(from, to schema.ReferenceOption) bool {
+func (*diff) ReferenceChanged(from, to schema.ReferenceOption) bool {
 	// According to SQLite, if an action is not explicitly
 	// specified, it defaults to "NO ACTION".
 	if from == "" {
@@ -131,7 +136,7 @@ func (*Diff) ReferenceChanged(from, to schema.ReferenceOption) bool {
 }
 
 // Normalize implements the sqlx.Normalizer interface.
-func (d *Diff) Normalize(from, to *schema.Table) error {
+func (d *diff) Normalize(from, to *schema.Table) error {
 	used := make([]bool, len(to.ForeignKeys))
 	// In SQLite, there is no easy way to get the foreign-key constraint
 	// name, except for parsing the CREATE statement. Therefore, we check
diff --git a/sql/sqlite/driver.go b/sql/sqlite/driver.go
index cbbb2bb6..5d800645 100644
--- a/sql/sqlite/driver.go
+++ b/sql/sqlite/driver.go
@@ -83,7 +83,7 @@ func Open(db schema.ExecQuerier) (migrate.Driver, error) {
 	}
 	return &Driver{
 		conn:        c,
-		Differ:      &sqlx.Diff{DiffDriver: &Diff{}},
+		Differ:      &sqlx.Diff{DiffDriver: &diff{}},
 		Inspector:   &inspect{c},
 		PlanApplier: &planApply{c},
 	}, nil


Test patch:
diff --git a/cmd/atlas/internal/cmdapi/schema_test.go b/cmd/atlas/internal/cmdapi/schema_test.go
index 74618491..e44e2bad 100644
--- a/cmd/atlas/internal/cmdapi/schema_test.go
+++ b/cmd/atlas/internal/cmdapi/schema_test.go
@@ -192,6 +192,37 @@ table "tbl" {
 		"-- Add column \"col_3\" to table: \"tbl\"\nALTER TABLE `tbl` ADD COLUMN `col_3` text NOT NULL\n",
 		s,
 	)
+
+	t.Run("FromConfig", func(t *testing.T) {
+		var (
+			p   = t.TempDir()
+			cp  = filepath.Join(p, "atlas.hcl")
+			sp  = filepath.Join(p, "schema.hcl")
+			cfg = fmt.Sprintf(`env "local" { dev = "%s" }`, openSQLite(t, ""))
+		)
+		require.NoError(t, os.WriteFile(cp, []byte(cfg), 0600))
+		require.NoError(t, os.WriteFile(sp, []byte(`
+schema "main" {}
+table "users" {
+  schema = schema.main
+  column "id" {
+    type = int
+  }
+}
+`), 0600))
+
+		cmd := schemaCmd()
+		cmd.AddCommand(schemaDiffCmd())
+		s, err := runCmd(
+			cmd, "diff",
+			"-c", "file://"+cp,
+			"--env", "local",
+			"--to", "file://"+sp,
+			"--from", openSQLite(t, ""),
+		)
+		require.NoError(t, err)
+		require.Equal(t, "-- Create \"users\" table\nCREATE TABLE `users` (`id` int NOT NULL)\n", s)
+	})
 }
 
 func TestCmdSchemaApply(t *testing.T) {
diff --git a/sql/mysql/diff_test.go b/sql/mysql/diff_test.go
index c279a37a..14e6e50a 100644
--- a/sql/mysql/diff_test.go
+++ b/sql/mysql/diff_test.go
@@ -623,3 +623,16 @@ func TestDiff_RealmDiff(t *testing.T) {
 		&schema.AddTable{T: to.Schemas[1].Tables[0]},
 	}, changes)
 }
+
+func TestDefaultDiff(t *testing.T) {
+	changes, err := DefaultDiff.SchemaDiff(
+		schema.New("public").
+			AddTables(
+				schema.NewTable("users").AddColumns(schema.NewIntColumn("id", "int")),
+			),
+		schema.New("public"),
+	)
+	require.NoError(t, err)
+	require.Len(t, changes, 1)
+	require.IsType(t, &schema.DropTable{}, changes[0])
+}
diff --git a/sql/postgres/diff_test.go b/sql/postgres/diff_test.go
index 73424613..d438b7a7 100644
--- a/sql/postgres/diff_test.go
+++ b/sql/postgres/diff_test.go
@@ -510,3 +510,16 @@ func TestDiff_SchemaDiff(t *testing.T) {
 		&schema.AddTable{T: to.Tables[1]},
 	}, changes)
 }
+
+func TestDefaultDiff(t *testing.T) {
+	changes, err := DefaultDiff.SchemaDiff(
+		schema.New("public").
+			AddTables(
+				schema.NewTable("users").AddColumns(schema.NewIntColumn("id", "int")),
+			),
+		schema.New("public"),
+	)
+	require.NoError(t, err)
+	require.Len(t, changes, 1)
+	require.IsType(t, &schema.DropTable{}, changes[0])
+}
diff --git a/sql/postgres/inspect_test.go b/sql/postgres/inspect_test.go
index 0bedd59f..94da2c39 100644
--- a/sql/postgres/inspect_test.go
+++ b/sql/postgres/inspect_test.go
@@ -674,6 +674,19 @@ public
 	}(), realm)
 }
 
+func TestIndexOpClass_UnmarshalText(t *testing.T) {
+	var op IndexOpClass
+	require.NoError(t, op.UnmarshalText([]byte("int4_ops")))
+	require.EqualValues(t, IndexOpClass{Name: "int4_ops"}, op)
+
+	op = IndexOpClass{}
+	require.NoError(t, op.UnmarshalText([]byte("")))
+	require.EqualValues(t, IndexOpClass{}, op)
+
+	require.NoError(t, op.UnmarshalText([]byte("int4_ops(siglen=1)")))
+	require.EqualValues(t, IndexOpClass{Name: "int4_ops", Params: []struct{ N, V string }{{"siglen", "1"}}}, op)
+}
+
 type mock struct {
 	sqlmock.Sqlmock
 }
diff --git a/sql/sqlite/diff_test.go b/sql/sqlite/diff_test.go
index aa326fab..dc8e8462 100644
--- a/sql/sqlite/diff_test.go
+++ b/sql/sqlite/diff_test.go
@@ -329,3 +329,16 @@ func TestDiff_SchemaDiff(t *testing.T) {
 		&schema.AddTable{T: to.Tables[1]},
 	}, changes)
 }
+
+func TestDefaultDiff(t *testing.T) {
+	changes, err := DefaultDiff.SchemaDiff(
+		schema.New("main").
+			AddTables(
+				schema.NewTable("users").AddColumns(schema.NewIntColumn("id", "int")),
+			),
+		schema.New("main"),
+	)
+	require.NoError(t, err)
+	require.Len(t, changes, 1)
+	require.IsType(t, &schema.DropTable{}, changes[0])
+}
diff --git a/sql/sqlite/sqlitecheck/sqlitecheck_test.go b/sql/sqlite/sqlitecheck/sqlitecheck_test.go
index b7fd50f5..e2591e5e 100644
--- a/sql/sqlite/sqlitecheck/sqlitecheck_test.go
+++ b/sql/sqlite/sqlitecheck/sqlitecheck_test.go
@@ -8,7 +8,6 @@ import (
 	"context"
 	"testing"
 
-	"ariga.io/atlas/sql/internal/sqlx"
 	"ariga.io/atlas/sql/migrate"
 	"ariga.io/atlas/sql/schema"
 	"ariga.io/atlas/sql/sqlcheck"
@@ -26,7 +25,7 @@ func TestDetectModifyTable(t *testing.T) {
 			Dev: &sqlclient.Client{
 				Driver: func() migrate.Driver {
 					drv := &sqlite.Driver{}
-					drv.Differ = &sqlx.Diff{DiffDriver: &sqlite.Diff{}}
+					drv.Differ = sqlite.DefaultDiff
 					return drv
 				}(),
 			},