Repository: ariga/atlas
Pull request #1397 (2023-01-10 15:28:16)

doc/md/guides/mysql: Add article about prefix indexes


Patch:
diff --git a/doc/md/guides/frameworks/sqlc-declarative.md b/doc/md/guides/frameworks/sqlc-declarative.md
new file mode 100644
index 00000000..24cd52ee
--- /dev/null
+++ b/doc/md/guides/frameworks/sqlc-declarative.md
@@ -0,0 +1,230 @@
+---
+id: sqlc-declarative
+title: Declarative migrations for sqlc
+slug: /guides/frameworks/sqlc-declarative
+---
+
+[sqlc](https://sqlc.dev/) is a tool that generates type-safe idiomatic Go code from SQL queries. It's like a transpiler,
+where you provide the required queries along with the database schema, and sqlc generates code that implements type-safe
+interfaces for these queries.
+
+sqlc does not impose any requirements for handling migrations. While it has [support for
+basic DDL commands](https://docs.sqlc.dev/en/latest/howto/ddl.html), it's not designed for handling the migration
+process.
+
+In this guide we will show you how Atlas can be used with sqlc in a declarative way, filling the gaps and providing a
+complete solution for building applications with sqlc.
+
+For this guide we will assume that you already have a project using sqlc. If you are new to sqlc or don't have a project yet,
+[check the getting started guide](https://docs.sqlc.dev/en/latest/tutorials/getting-started-postgresql.html).
+
+## Schema
+
+To generate code, sqlc depends on the database schema and the queries to be generated against it. We need both the
+schema and queries to ensure sqlc knows what types and queries it needs to generate.
+
+As your application’s schema evolves, drift between the desired schema and database can cause many issues with sqlc,
+since the most common time to detect these errors is during query execution.
+
+In this guide, we will show how sqlc users can utilize Atlas’s declarative schema migration workflow to ensure their
+database schemas are always in sync with their desired state.
+
+## Desired state
+
+Since both Atlas and sqlc can accept a single SQL file to describe the desired state of the database schema, this file
+can be the source of truth (or desired state) for both tools, letting each tool handle its part of the job.
+
+This is a great example of the power of
+the [declarative concept](https://atlasgo.io/concepts/declarative-vs-versioned#declarative-migrations), where we declare
+what we expect and let the tooling figure out how to reach the desired state.
+
+## Migrating the initial schema
+
+If you followed the sqlc tutorial, you may end up with a `schema.sql` file that looks like this:
+
+```sql title="schema.sql"
+CREATE TABLE authors
+(
+  id   BIGSERIAL PRIMARY KEY,
+  name text NOT NULL,
+  bio  text
+);
+```
+
+And a `query.sql` file that looks like this:
+
+```sql title="query.sql"
+-- name: GetAuthor :one
+SELECT *
+FROM authors
+WHERE id = $1
+LIMIT 1;
+
+-- name: ListAuthors :many
+SELECT *
+FROM authors
+ORDER BY name;
+
+-- name: CreateAuthor :one
+INSERT INTO authors (name, bio)
+VALUES ($1, $2)
+RETURNING *;
+
+-- name: DeleteAuthor :exec
+DELETE
+FROM authors
+WHERE id = $1;
+```
+
+You may already have executed the `sqlc generate` command, if not, make sure to execute it.
+
+The next step is migrating the database, for the purpose of this guide, we will assume that our application is backed by
+a PostgreSQL database running in a local Docker container. You can start such a container by running:
+
+```shell
+docker run --rm -d --name atlas-sqlc -p 5432:5432 -e POSTGRES_PASSWORD=pass -e POSTGRES_DB=sqlc postgres
+```
+
+:::info
+While in this example we are using Postgres, this is not a requirement by sqlc or Atlas. You can switch to any other
+database as long the `schema.sql` is compatible.
+:::
+
+The first thing we do is initialize the database schema. This can be accomplished with a simple
+command:
+
+```shell
+atlas schema apply \
+--url "postgres://postgres:pass@localhost:5432/sqlc?sslmode=disable" \
+--dev-url "docker://postgres" \
+--to "file://schema.sql"
+```
+
+After executing the command, you should see the planned changes, similar to the example below:
+
+```text {2-5}
+-- Planned Changes:
+-- Add new schema named "public"
+CREATE SCHEMA "public";
+-- Create "authors" table
+CREATE TABLE "public"."authors" ("id" bigserial NOT NULL, "name" text NOT NULL, "bio" text NULL, PRIMARY KEY ("id"));
+Use the arrow keys to navigate: ↓ ↑ → ←
+? Are you sure?:
+  ▸ Apply
+    Abort
+```
+
+:::info
+Atlas CLI will ask for approval during the command execution. If you want to skip this check, you can use the
+flag `--auto-approve`.
+:::
+
+Let's break this command down: first we are telling Atlas to connect to the database using the `url` flag, then compare
+to the desired state defined by the `to` flag and apply all the changes required to ensure we get to the desired state.
+
+There is one more flag used on the command: `dev-url`. Atlas uses a temporary database to check, simulate and validate
+the generated queries. For more information about the dev database, [read about it here](https://atlasgo.io/concepts/dev-database). Atlas has
+support for running database containers, which is what we are using in this example.
+
+After running the command above and confirming the changes, your database should be in sync with the `schema.sql` file.
+
+## Evolving the schema
+
+As your application evolves, it is very common to have the database schema evolve as well. Suppose that to support a new
+feature in your application, you need to add a new `age` column. With Atlas, the process can be as simple as updating
+the `schema.sql` to the desired schema, updating the `query.sql` file, executing the `sqlc generate` comamnd
+and running `schema apply` again.
+
+First, let's update our `schema.sql` file, adding the new column.
+
+```sql title="schema.sql" {5-6}
+CREATE TABLE authors
+(
+  id   BIGSERIAL PRIMARY KEY,
+  name text NOT NULL,
+  bio  text,
+  age  integer
+);
+```
+
+Let's add a new `GetAuthorsByAge` query and update our `CreateAuthor` query.
+
+```sql title="query.sql" {7-11,19-20}
+-- name: GetAuthor :one
+SELECT *
+FROM authors
+WHERE id = $1
+LIMIT 1;
+
+-- name: GetAuthorsByAge :many
+SELECT *
+FROM authors
+WHERE age = $1
+ORDER BY age;
+
+-- name: ListAuthors :many
+SELECT *
+FROM authors
+ORDER BY name;
+
+-- name: CreateAuthor :one
+INSERT INTO authors (name, bio, age)
+VALUES ($1, $2, $3)
+RETURNING *;
+
+-- name: DeleteAuthor :exec
+DELETE
+FROM authors
+WHERE id = $1;
+```
+
+After changes to the `schema.sql` or `query.sql` file, we must run the sqlc generate command again:
+
+```shell
+sqlc generate
+```
+
+:::note
+It's important to run `sqlc generate` even for changes that don't change the `query.sql` file, since sqlc
+will replace the `*` with explicit column names, to ensure the query never returns unexpected data.
+:::
+
+As the last step, we run the `atlas schema apply` command to ensure the database (whether production or development) is
+in sync with the desired state.
+
+```shell
+atlas schema apply \
+--url "postgres://postgres:pass@localhost:5432/sqlc?sslmode=disable" \
+--dev-url "docker://postgres" \
+--to "file://schema.sql"
+```
+
+This time Atlas should show a different plan for executing these changes:
+
+```text {2-3}
+-- Planned Changes:
+-- Modify "authors" table
+ALTER TABLE "public"."authors" ADD COLUMN "age" integer NULL;
+Use the arrow keys to navigate: ↓ ↑ → ←
+? Are you sure?:
+  ▸ Apply
+    Abort
+```
+
+That’s it! After making changes to your schema, all you have to do is apply these changes again and Atlas
+will handle the rest.
+
+## Complete workflow
+
+With the declarative strategy, one can visualize the complete workflow of using Atlas with sqlc as follows:
+
+- Update the schema (`schema.sql`)
+  - Optionally update the queries (`query.sql`)
+- Run sqlc to generate the updated code
+- Run Atlas during the development and migration process, referencing the `schema.sql` as the desired state.
+
+## Wrapping up
+
+In this guide we saw how Atlas can be used with sqlc in a declarative way, making the schema management process a
+breeze. If you don’t like the approach of handling the migration in a declarative way, Atlas has support for [versioned
+migrations](https://atlasgo.io/concepts/declarative-vs-versioned#versioned-migrations) as well.
\ No newline at end of file
diff --git a/doc/md/guides/guides.md b/doc/md/guides/guides.md
index 0af6cf45..2c26d7fd 100644
--- a/doc/md/guides/guides.md
+++ b/doc/md/guides/guides.md
@@ -28,6 +28,8 @@ Welcome to the Atlas Guides.
 
 [Functional Indexes](mysql/functional-indexes.md)
 
+[Prefix Indexes](mysql/prefix-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/prefix-indexes.md b/doc/md/guides/mysql/prefix-indexes.md
new file mode 100644
index 00000000..59149ed0
--- /dev/null
+++ b/doc/md/guides/mysql/prefix-indexes.md
@@ -0,0 +1,457 @@
+---
+id: prefix-indexes
+title: Prefix Indexes in MySQL
+slug: /guides/mysql/prefix-indexes
+---
+
+### What are prefix indexes?
+
+With MySQL, users may create secondary indexes for string columns which use the first N characters of the values stored in the column, instead of using the entire value. If used correctly, prefix indexes improve performance and reduce costs, all while minimizing the amount of storage space they take up on the disk.
+When do we need them?
+
+Let’s assume you have a lengthy column. If you have many records in an indexed table, the number of records the index needs to track also grows. If the index grows in size, the disk space needed to store the index itself increases as well. In many tables, lengthy records are not accessed with uniform search queries. One might prefer to use the `LIKE` operator to fetch content from a lengthy column, instead of writing the full value itself in the query. 
+
+Some data types (such as `BLOB` and `TEXT`) are not allowed to be indexed (unless the prefix value is specified). Also, The maximum length of the indexed value is 767 bytes. If the indexed value exceeds this length, it will be truncated.
+
+In such cases, the prefix index can become useful to filter unsearched parts of values and give you, as an engineer, a tool to index only the parts of the values which are important.
+
+### Syntax
+
+Here is how you can create a prefix index:
+
+```sql
+CREATE INDEX 
+    index_name 
+ON 
+    table_name(column_name(length));
+```
+
+Here is how you can define a prefix index in a table definition:
+
+```sql
+CREATE TABLE 
+    table_name(column_name BLOB, index_name(column_name(length)));
+```
+
+Remember that the `length` is interpreted as the number of characters in non-binary string types such as `CHAR`, `VARCHAR` and `TEXT`. For binary string types such as `BINARY`, `VARBINARY` and `BLOB`, the `length` is interpreted as the number of bytes in the string.
+
+Also, while indexing `BLOB` and `TEXT` types, you *must* specify the length
+
+## Examples
+
+### 1. Using prefix indexes with a TEXT type column
+
+Let's see this in action by creating a table with the following command:
+
+```sql
+CREATE TABLE `comm_database` (
+    `id` mediumint(8) unsigned NOT NULL auto_increment,
+    `sender` varchar(255),
+    `receiver` varchar(255),
+    `sender_address` varchar(255),
+    `email` varchar(255),
+    `message` TEXT,
+    `suspicious` BOOLEAN,
+    `sent_date` varchar(255),
+    PRIMARY KEY (`id`)
+);
+```
+
+Here is how a portion of the table might look like after inserting values:
+
+```console title="Output"
++----+------------------+-----------------+-------------------------------+---------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------------------------+
+| id | sender           | receiver        | sender_address                | email                     | message                                                                                                                                               | suspicious | sent_date               |
++----+------------------+-----------------+-------------------------------+---------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------------------------+
+|  1 | Abel Warren      | Latoya Spencer  | 575 North Rocky Fabien Avenue | xufh.vuho@example.com     | Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed nec dapibu                                                                               |          0 | 2008-09-24 04:57:35.552 |
+|  2 | Erick Valentine  | Casey Leonard   | 243 West Green Nobel Parkway  | kmku.dmfn@example.com     | cus. Maecenas vitae odio sagittis lectus auctor varius. In ultricies facilisis sem, vel scelerisque nulla gravida vitae. Quisque volutpat com         |          0 | 2008-08-21 11:24:52.16  |
+|  3 | Janice Payne     | Lakeisha Montes | 36 North Rocky Nobel St.      | kevr7@example.com         | Pellentesque porttitor dolor rhoncus ex tempus pulvinar. Aenean mattis malesuada tincidunt.                                                           |          0 | 2008-09-30 23:12:21.056 |
+|  4 | Gretchen Mason   | Colleen Proctor | 241 North Green Second Way    | bymd1@example.com         | In hac habitasse platea dictumst. Nam gravida sem vitae hendrerit mattis. Maecenas vestibulum ante sed interdum accumsan.                             |          1 | 2008-01-04 07:04:50.56  |
+|  5 | Lawanda Noble    | Alice Velazquez | 255 West White Hague Avenue   | xfvh.icqt@example.com     | lentesque sed dictum diam, non sodales ligula. Etiam lacinia augue et mattis sodales. Cr                                                              |          0 | 2008-05-07 06:03:08.736 |
+|  6 | Robbie Baird     | Bernard Wilkins | 508 South Rocky Milton Blvd.  | monlt963@example.com      | mollis dui. Morbi neque mauris, convallis id enim eget, efficitur tincidunt nisi. Vestibulum congue nisi vel egestas condimentu                       |          0 | 2008-06-28 07:58:27.904 |
+|  7 | Carla Compton    | Howard Randall  | 448 South Rocky Fabien Street | ffnjia@example.com        | aretra mauris elementum, efficitur nibh. In urna risus, scelerisque ut fringilla quis, suscipit nec dolor. Proin leo l                                |          0 | 2008-11-24 04:10:54.912 |
+|  8 | Heath Stafford   | Hector Dickson  | 266 South Rocky New Road      | xlxnq6@example.com        | a vehicula tellus tempor. Suspendisse potenti. In                                                                                                     |          0 | 2008-09-28 13:23:49.44  |
+|  9 | Kendra Stevenson | Vernon Rodgers  | 451 East White Milton Blvd.   | hnguyso.qcbat@example.com | arcu. Integer fringilla vulputate lectus, et faucibus mauris fermentum et. Quisque malesuada justo quam, vehicu                                       |          1 | 2008-09-15 23:07:41.504 |
+| 10 | Brandie Chase    | Mike Finley     | 188 North Rocky Old Way       | xhqx@example.com          | mi. Donec non vehicula tortor. Nunc ut semper nibh, in accumsan metus. Cras eu pellentesque lacus, sit amet                                           |          0 | 2008-07-27 01:54:37.248 |
+```
+
+We do not have any indexes other than the primary index on the `id` column. Now, suppose we want information about senders and receivers of a message with a particular beginning. Let's query that data with the following command:
+
+```sql
+SELECT
+    sender,
+    sender_address,
+    receiver,
+    email
+FROM
+    comm_database
+WHERE
+    message LIKE 'Lorem ipsum dolor sit ame%';
+```
+
+```console title="Output"
++-----------------+-----------------------------------+-----------------+-------------------------------+
+| sender          | sender_address                    | receiver        | email                         |
++-----------------+-----------------------------------+-----------------+-------------------------------+
+| Bethany Velez   | 340 South Green Hague Blvd.       | Yvette Odonnell | ohhr2@example.com             |
+| Clarence Simon  | 42 East Green Nobel Road          | Jay Mayo        | gonfy.qvwqum@example.com      |
+| Vicky Bishop    | 64 West White Second Drive        | Kevin Petty     | wheo6@example.com             |
+| Jami Lam        | 545 North Rocky Milton St.        | Justin Cameron  | dsec4@example.com             |
+| Ernest Lynch    | 183 West Green Old Road           | Franklin Medina | knoph5@example.com            |
+| Sam Freeman     | 324 West Green Fabien Road        | Jose Shelton    | ewqa@example.com              |
+| Derek Copeland  | 88 South Rocky Cowley Blvd.       | Nichole Powell  | tzkr3@example.com             |
+| Lynn Lynch      | 72 South Rocky New Drive          | Carolyn Levine  | rrew@example.com              |
+| Olivia Meyer    | 384 South Green Hague Freeway     | Angie Whitaker  | yvft01@example.com            |
+| Shaun Harper    | 886 North Rocky Hague Parkway     | Gregory Irwin   | hhro6@example.com             |
+| Darrin Trujillo | 88 North White Nobel Freeway      | Gail Blevins    | ddwsw@example.com             |
+| Stanley Mata    | 758 South Green Cowley Blvd.      | Alma Cole       | ksxx.viza@example.com         |
+| Cory Howard     | 517 West Rocky Milton Boulevard   | Eugene Ortiz    | faut.hkvn@example.com         |
+| Joyce Owen      | 24 North Green Hague Way          | Leah Wilkerson  | jupv.wypo@example.com         |
+| Kristina Beard  | 68 East White New Street          | Thomas Faulkner | mrixv@example.com             |
+| Connie Glass    | 874 South White Clarendon Freeway | Sharon Lamb     | qgesbi.whcqendylg@example.com |
++-----------------+-----------------------------------+-----------------+-------------------------------+
+16 rows in set (0.62 sec)
+```
+
+Now, let's see how much the query cost by running  the following command:
+
+```sql
+SHOW STATUS LIKE 'Last_query_cost';
+```
+
+```console title="Output"
++-----------------+---------------+
+| Variable_name   | Value         |
++-----------------+---------------+
+| Last_query_cost | 108430.088807 |
++-----------------+---------------+
+1 row in set (0.01 sec)
+```
+
+Notice that the query cost 108430.088807 units.
+
+Now, suppose we want to optimize this query but we do not know about prefix indexes yet. In this case, we will make an index on column `message` with the following command:
+
+```sql
+CREATE INDEX 
+    message_idx 
+ON 
+    comm_database(message);
+```
+
+```console title="Output"
+ERROR 1170 (42000): BLOB/TEXT column 'message' used in key specification without a key length
+```
+
+That didn’t work! As mentioned in the [Syntax](#syntax) section above and the error message, you must specify length when indexing `BLOB` and `TEXT` type columns. Let’s specify the length and try to check the performance for the same query again:
+
+```sql
+CREATE INDEX 
+    message_idx 
+ON 
+    comm_database(message(30));
+```
+
+```console title="Output"
+Query OK, 0 rows affected (3.55 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+```
+
+```sql
+SELECT
+    sender,
+    sender_address,
+    receiver,
+    email
+FROM
+    comm_database
+WHERE
+    message LIKE 'Lorem ipsum dolor sit ame%';
+.
+.
+.
+SHOW STATUS LIKE 'Last_query_cost';
+```
+
+```console title="Output"
++-----------------+-----------+
+| Variable_name   | Value     |
++-----------------+-----------+
+| Last_query_cost | 16.461134 |
++-----------------+-----------+
+```
+
+Awesome! Now our query performs far better and has a significantly lower cost, (16.46 units now as compared to 108430.08 units before creating the index)
+Additionally, we can also confirm by using `EXPLAIN` with the query that the index we created is indeed being used:
+
+```console title="Output"
++----+-------------+--------------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
+| id | select_type | table        | partitions | type  | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra       |
++----+-------------+--------------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
+|  1 | SIMPLE      | comm_database| NULL       | range | message_idx         | message_idx         | 123     | NULL |   16 |   100.00 | Using where |
++----+-------------+--------------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
+```
+
+In the above example we saw how we can improve performance for a `TEXT` type column by specifying appropriate `length` while creating the index.
+
+Now in the next example, let’s see how we can optimize space used by the index with  a prefix index.
+
+### 2. Using prefix indexes to optimize space usage and increase query performance
+
+Using the same table, let’s assume that we want to fetch data of senders by using their email address. Here is a sample query and its cost without any index:
+
+```sql
+SELECT
+    id,
+    sender,
+    sender_address
+FROM
+    comm_database
+WHERE
+    email = 'dsec4@example.com';
+```
+
+```console title="Output"
++--------+----------+----------------------------+
+| id     | sender   | sender_address             |
++--------+----------+----------------------------+
+| 210829 | Jami Lam | 545 North Rocky Milton St. |
++--------+----------+----------------------------+
+```
+
+```console title="Cost"
++-----------------+---------------+
+| Variable_name   | Value         |
++-----------------+---------------+
+| Last_query_cost | 108453.391718 |
++-----------------+---------------+
+```
+
+To optimize this query, usually we would create the following index:
+
+```sql
+CREATE INDEX 
+    email_idx 
+ON 
+    comm_database(email);
+```
+
+```console title="Output"
+Query OK, 0 rows affected (1.57 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+```
+
+The cost for the query after creating this index is as follows:
+
+```console title="Cost"
++-----------------+----------+
+| Variable_name   | Value    |
++-----------------+----------+
+| Last_query_cost | 0.870951 |
++-----------------+----------+
+```
+
+The storage space used by the index is as following:
+
+```sql
+SELECT 
+    stat_value
+FROM 
+    mysql.innodb_index_stats 
+WHERE 
+    index_name = 'email_idx' AND stat_name= 'size';
+```
+
+```console title="Output"
++------------+
+| stat_value |
++------------+
+|       2085 |
++------------+
+```
+
+:::info
+The `innodb_page_size` variable specifies the size of the pages used by the InnoDB storage engine, and the `stat_value` column contains statistics about the index, such as the number of pages used by the index. When `stat_name` is 'size', the `stat_value` column contains the size of the index in terms of number of pages. 
+
+For example, if the `innodb_page_size` system variable is set to 16 KB and the `stat_value` column contains the value 10, this means that the index uses 10 pages, or 160KB of disk space.
+:::
+
+For our example, keeping `innodb_page_size` as 16KB in mind, our index uses ~32MB disk space. Now, we can further improve the query performance as well as reduce the storage used by the index by using a prefix index on the email column. Let’s create a prefix index with the following command:
+
+```sql
+ALTER TABLE 
+    comm_database 
+DROP INDEX 
+    email_idx,
+ADD INDEX 
+    email_prefix_idx (email(5));
+```
+
+```console title="Output"
+Query OK, 0 rows affected (1.59 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+```
+
+The cost for the query after creating this index is as follows:
+
+```console title="Cost"
++-----------------+----------+
+| Variable_name   | Value    |
++-----------------+----------+
+| Last_query_cost | 0.832643 |
++-----------------+----------+
+```
+
+Our cost has reduced by 5% by using the prefix index.
+
+And the storage space used by the index is as follows:
+
+```sql
+SELECT 
+    stat_value
+FROM 
+    mysql.innodb_index_stats 
+WHERE 
+    index_name = 'email_prefix_idx' AND stat_name= 'size';
+```
+
+```console title="Output"
++------------+
+| stat_value |
++------------+
+|       1123 |
++------------+
+```
+
+Based on the calculation using the `innodb_page_size` of 16KB, the index now occupies 17.55MB of storage space, a reduction of 46% from the previous size of 32.58MB.
+
+## Managing Prefix 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/getting-started/).
+:::
+
+We will first use the `atlas schema inspect` command to get an [HCL](https://atlasgo.io/guides/ddl#hcl) representation of the table we created earlier (without any indexes) by using the Atlas CLI:
+
+```console title="Terminal"
+atlas schema inspect -u "mysql://root:@localhost:3306/comm_database" > schema.hcl
+```
+
+```hcl title="schema.hcl"
+table "comm_database" {
+  schema = schema.comm_database
+  column "id" {
+    null           = false
+    type           = mediumint
+    unsigned       = true
+    auto_increment = true
+  }
+  column "sender" {
+    null = true
+    type = varchar(255)
+  }
+  column "receiver" {
+    null = true
+    type = varchar(255)
+  }
+  column "sender_address" {
+    null = true
+    type = varchar(255)
+  }
+  column "email" {
+    null = true
+    type = varchar(255)
+  }
+  column "message" {
+    null = true
+    type = text
+  }
+  column "suspicious" {
+    null = true
+    type = bool
+  }
+  column "sent_date" {
+    null = true
+    type = varchar(255)
+  }
+  primary_key {
+    columns = [column.id]
+  }
+}
+schema "comm_database" {
+  charset = "utf8mb4"
+  collate = "utf8mb4_0900_ai_ci"
+}
+```
+
+Now, lets add the two following index definitions to the file:
+
+```hcl
+  index "message_idx" {
+    on {
+      column = column.message
+      prefix = 30
+    }
+  }
+  index "email_prefix_idx" {
+    on {
+      column = column.email
+      prefix = 5
+    }
+  }
+```
+
+Save the file and apply the schema changes on the database by using the following command:
+
+```console title="Terminal"
+atlas schema apply -u "mysql://root:password@localhost:3306/comm_database" -f schema.hcl --dev-url docker://mysql/8/comm_database
+```
+
+:::info
+If you get `Error: pulling image: exit status 1` error, ensure that Docker Desktop is up and running.
+:::
+
+Atlas generates the necessary SQL statements to add the new index to the database schema. 
+
+```console
+-- Planned Changes:
+-- Modify "comm_database" table
+ALTER TABLE `comm_database`.`comm_database` ADD INDEX `message_idx` (`message` (30)), ADD INDEX `email_prefix_idx` (`email` (5))
+Use the arrow keys to navigate: ↓ ↑ → ← 
+? Are you sure?: 
+  ▸ Apply
+    Abort
+```
+
+To verify that our new index was created, run the following command:
+
+```console title="Terminal"
+atlas schema inspect -u "mysql://root:password@localhost:3306/comm_database" | grep -A5 index
+```
+
+```console title="Output"
+  index "message_idx" {
+    on {
+      column = column.message
+      prefix = 30
+    }
+  }
+  index "email_prefix_idx" {
+    on {
+      column = column.email
+      prefix = 5
+    }
+  }
+```
+
+Amazing! Our new indexes `message_idx` and `email_prefix_idx` are now created!
+
+### Wrapping up​
+
+In this guide, we have demonstrated that creating a prefix index is inevitable when improving performance for queries accessing `TEXT` or `BLOB` type columns. More importantly, it is a smart choice when trying to improve performance for queries accessing `CHAR`, `VARCHAR`, `BINARY` and `VARBINARY` type columns, if used wisely.
+
+## Need More Help?​​
+[Join](https://discord.com/invite/zZ6sWVg6NT) the Ariga Discord Server 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/docusaurus.config.js b/doc/website/docusaurus.config.js
index fdea14e9..243c5067 100644
--- a/doc/website/docusaurus.config.js
+++ b/doc/website/docusaurus.config.js
@@ -53,7 +53,12 @@ module.exports = {
           href: 'https://twitter.com/ariga_io',
           className: 'header-twitter-link',
           position: 'right',
+        },{
+          href: 'https://atlasnewsletter.substack.com/',
+          className: 'header-newsletter-link',
+          position: 'right',
         },
+        
         {
           to: 'getting-started',
           label: 'Docs',
@@ -91,7 +96,8 @@ module.exports = {
           "items": [
             {"label": "GitHub", "to": "https://github.com/ariga/atlas"},
             {"label": "Discord", "to": "https://discord.gg/zZ6sWVg6NT"},
-            {"label": "Twitter", "to": "https://twitter.com/ariga_io"}
+            {"label": "Twitter", "to": "https://twitter.com/ariga_io"},
+            {"label": "Newsletter", "to": "https://atlasnewsletter.substack.com/"}
           ]
         },
         {
diff --git a/doc/website/sidebars.js b/doc/website/sidebars.js
index 90c41568..27c6c5bb 100644
--- a/doc/website/sidebars.js
+++ b/doc/website/sidebars.js
@@ -149,12 +149,17 @@ module.exports = {
                     type: 'doc',
                     id: 'guides/mysql/descending-indexes',
                     label: 'Descending Indexes'
-                  },
-                  {
+                },
+                {
                     type: 'doc',
                     id: 'guides/mysql/functional-indexes',
                     label: 'Functional Indexes'
-                  },
+                },
+                {
+                    type: 'doc',
+                    id: 'guides/mysql/prefix-indexes',
+                    label: 'Prefix Indexes'
+                },
                 {
                   type: 'doc',
                   id: 'guides/mysql/generated-columns',
@@ -240,13 +245,18 @@ module.exports = {
         },
         {
             type: 'category',
-            label: 'ORMs',
+            label: 'Frameworks',
             collapsed: false,
             items: [
                 {
                     type: 'doc',
                     id:'guides/orms/gorm',
                     label: 'GORM'
+                },
+                {
+                    type: 'doc',
+                    id:'guides/frameworks/sqlc-declarative',
+                    label: 'Declarative migrations for sqlc'
                 }
             ]
         },
diff --git a/doc/website/src/assets/icons/newsletter.svg b/doc/website/src/assets/icons/newsletter.svg
new file mode 100644
index 00000000..d9e0b363
--- /dev/null
+++ b/doc/website/src/assets/icons/newsletter.svg
@@ -0,0 +1,3 @@
+<svg width="24" height="24" viewBox="0 0 24 24" fill="none" xmlns="http://www.w3.org/2000/svg">
+<path d="M20 4H4C2.9 4 2.01 4.9 2.01 6L2 18C2 19.1 2.9 20 4 20H20C21.1 20 22 19.1 22 18V6C22 4.9 21.1 4 20 4ZM20 8L12 13L4 8V6L12 11L20 6V8Z" fill="#82C7FF"/>
+</svg>
diff --git a/doc/website/src/css/custom.css b/doc/website/src/css/custom.css
index 9d8f8253..03113597 100644
--- a/doc/website/src/css/custom.css
+++ b/doc/website/src/css/custom.css
@@ -108,6 +108,29 @@ html[data-theme='dark'] .header-github-link:before {
   no-repeat;
 }
 
+/* newsletter */
+.header-newsletter-link:hover {
+  opacity: 0.6;
+}
+
+.header-newsletter-link:before {
+  content: '';
+  width: 28px;
+  height: 28px;
+  display: flex;
+  background: url("data:image/svg+xml,%3Csvg viewBox='0 0 24 24' xmlns='http://www.w3.org/2000/svg'%3E%3Cpath d='M20 4H4c-1.1 0-1.99.9-1.99 2L2 18c0 1.1.9 2 2 2h16c1.1 0 2-.9 2-2V6c0-1.1-.9-2-2-2zm0 4l-8 5-8-5V6l8 5 8-5v2z'/%3E%3C/svg%3E")
+  no-repeat;
+}
+
+html[data-theme='dark'] .header-newsletter-link:before {
+  content: '';
+  width: 28px;
+  height: 28px;
+  display: flex;
+  background: url("data:image/svg+xml,%3Csvg viewBox='0 0 24 24' xmlns='http://www.w3.org/2000/svg'%3E%3Cpath fill='white' d='M20 4H4c-1.1 0-1.99.9-1.99 2L2 18c0 1.1.9 2 2 2h16c1.1 0 2-.9 2-2V6c0-1.1-.9-2-2-2zm0 4l-8 5-8-5V6l8 5 8-5v2z'/%3E%3C/svg%3E")
+  no-repeat;
+}
+
 @media only screen
 and (max-width: 525px) {
   .menu__link {
diff --git a/doc/website/src/pages/index.js b/doc/website/src/pages/index.js
index e074a8b4..abc2e74f 100644
--- a/doc/website/src/pages/index.js
+++ b/doc/website/src/pages/index.js
@@ -6,6 +6,7 @@ import index from './index.module.css';
 import GithubIcon from '../assets/icons/github.svg';
 import DiscordIcon from '../assets/icons/discord.svg';
 import TwitterIcon from '../assets/icons/twitter.svg';
+import NewsletterIcon from '../assets/icons/newsletter.svg';
 import BrowserOnly from "@docusaurus/core/lib/client/exports/BrowserOnly";
 
 function Nav() {
@@ -40,6 +41,9 @@ function SocialLinks() {
         <a href="https://twitter.com/ariga_io" target="_blank">
             <TwitterIcon />
         </a>
+        <a href="https://atlasnewsletter.substack.com/" target="_blank">
+            <NewsletterIcon />
+        </a>
     </div>
 }
 
diff --git a/schemahcl/stdlib.go b/schemahcl/stdlib.go
index 0064f4ae..fdf990b5 100644
--- a/schemahcl/stdlib.go
+++ b/schemahcl/stdlib.go
@@ -156,6 +156,7 @@ func stdFuncs() map[string]function.Function {
 		"trimsuffix":      stdlib.TrimSuffixFunc,
 		"try":             tryfunc.TryFunc,
 		"upper":           stdlib.UpperFunc,
+		"urlescape":       urlEscape,
 		"urlqueryset":     urlQuerySetFunc,
 		"urlsetpath":      urlSetPathFunc,
 		"values":          stdlib.ValuesFunc,
@@ -288,3 +289,17 @@ var urlSetPathFunc = function.New(&function.Spec{
 		return cty.StringVal(u.String()), nil
 	},
 })
+
+var urlEscape = function.New(&function.Spec{
+	Params: []function.Parameter{
+		{
+			Name: "string",
+			Type: cty.String,
+		},
+	},
+	Type: function.StaticReturnType(cty.String),
+	Impl: func(args []cty.Value, retType cty.Type) (cty.Value, error) {
+		u := url.QueryEscape(args[0].AsString())
+		return cty.StringVal(u), nil
+	},
+})
diff --git a/sql/internal/sqlx/diff.go b/sql/internal/sqlx/diff.go
index 39f70a3d..8281f552 100644
--- a/sql/internal/sqlx/diff.go
+++ b/sql/internal/sqlx/diff.go
@@ -156,10 +156,6 @@ func (d *Diff) TableDiff(from, to *schema.Table) ([]schema.Change, error) {
 	if from.Name != to.Name {
 		return nil, fmt.Errorf("mismatched table names: %q != %q", from.Name, to.Name)
 	}
-	// PK modification is not supported.
-	if pk1, pk2 := from.PrimaryKey, to.PrimaryKey; (pk1 != nil) != (pk2 != nil) || (pk1 != nil) && d.pkChange(pk1, pk2) != schema.NoChange {
-		return nil, fmt.Errorf("changing %q table primary key is not supported", to.Name)
-	}
 
 	// Drop or modify attributes (collations, checks, etc).
 	change, err := d.TableAttrDiff(from, to)
@@ -194,7 +190,8 @@ func (d *Diff) TableDiff(from, to *schema.Table) ([]schema.Change, error) {
 		}
 	}
 
-	// Index changes.
+	// Primary-key and index changes.
+	changes = append(changes, d.pkDiff(from, to)...)
 	changes = append(changes, d.indexDiff(from, to)...)
 
 	// Drop or modify foreign-keys.
@@ -221,6 +218,28 @@ func (d *Diff) TableDiff(from, to *schema.Table) ([]schema.Change, error) {
 	return changes, nil
 }
 
+// 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) (changes []schema.Change) {
+	switch pk1, pk2 := from.PrimaryKey, to.PrimaryKey; {
+	case pk1 == nil && pk2 != nil:
+		changes = append(changes, &schema.AddPrimaryKey{P: pk2})
+	case pk1 != nil && pk2 == nil:
+		changes = append(changes, &schema.DropPrimaryKey{P: pk1})
+	case pk1 != nil && pk2 != nil:
+		change := d.indexChange(pk1, pk2)
+		change &= ^schema.ChangeUnique
+		if change != schema.NoChange {
+			changes = append(changes, &schema.ModifyPrimaryKey{
+				From:   pk1,
+				To:     pk2,
+				Change: change,
+			})
+		}
+	}
+	return
+}
+
 // indexDiff returns the schema changes (if any) for migrating table
 // indexes from current state to the desired state.
 func (d *Diff) indexDiff(from, to *schema.Table) []schema.Change {
@@ -265,12 +284,6 @@ func (d *Diff) indexDiff(from, to *schema.Table) []schema.Change {
 	return changes
 }
 
-// pkChange returns the schema changes (if any) for migrating one primary key to the other.
-func (d *Diff) pkChange(from, to *schema.Index) schema.ChangeKind {
-	change := d.indexChange(from, to)
-	return change & ^schema.ChangeUnique
-}
-
 // indexChange returns the schema changes (if any) for migrating one index to the other.
 func (d *Diff) indexChange(from, to *schema.Index) schema.ChangeKind {
 	var change schema.ChangeKind
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index c22864d4..38ac2fbb 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -835,7 +835,7 @@ type (
 		T string // BTREE, HASH, FULLTEXT, SPATIAL, RTREE
 	}
 
-	// BitType represents a bit type.
+	// BitType represents the type bit.
 	BitType struct {
 		schema.Type
 		T    string
diff --git a/sql/mysql/migrate.go b/sql/mysql/migrate.go
index 7bcf56f1..f9d1b8d1 100644
--- a/sql/mysql/migrate.go
+++ b/sql/mysql/migrate.go
@@ -232,7 +232,7 @@ func (s *state) addTable(add *schema.AddTable) error {
 		})
 		if pk := add.T.PrimaryKey; pk != nil {
 			b.Comma().P("PRIMARY KEY")
-			indexParts(b, pk.Parts)
+			indexTypeParts(b, pk)
 		}
 		if len(add.T.Indexes) > 0 {
 			b.Comma()
@@ -394,6 +394,17 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 			case *schema.DropIndex:
 				b.P("DROP INDEX").Ident(change.I.Name)
 				reverse = append(reverse, &schema.AddIndex{I: change.I})
+			case *schema.AddPrimaryKey:
+				b.P("ADD PRIMARY KEY")
+				indexTypeParts(b, change.P)
+				reverse = append(reverse, &schema.DropPrimaryKey{P: change.P})
+			case *schema.DropPrimaryKey:
+				b.P("DROP PRIMARY KEY")
+				reverse = append(reverse, &schema.AddPrimaryKey{P: change.P})
+			case *schema.ModifyPrimaryKey:
+				b.P("DROP PRIMARY KEY, ADD PRIMARY KEY")
+				indexTypeParts(b, change.To)
+				reverse = append(reverse, &schema.ModifyPrimaryKey{From: change.To, To: change.From, Change: change.Change})
 			case *schema.AddForeignKey:
 				b.P("ADD")
 				if err := s.fks(b, change.F); err != nil {
@@ -555,41 +566,37 @@ func (s *state) column(b *sqlx.Builder, t *schema.Table, c *schema.Column) error
 }
 
 func index(b *sqlx.Builder, idx *schema.Index) {
-	var t IndexType
-	if sqlx.Has(idx.Attrs, &t) {
-		t.T = strings.ToUpper(t.T)
-	}
-	switch {
+	switch t := indexType(idx.Attrs); {
 	case idx.Unique:
 		b.P("UNIQUE")
 	case t.T == IndexTypeFullText || t.T == IndexTypeSpatial:
 		b.P(t.T)
 	}
 	b.P("INDEX").Ident(idx.Name)
-	// Skip BTREE as it is the default type.
-	if t.T == IndexTypeHash {
-		b.P("USING", t.T)
-	}
-	indexParts(b, idx.Parts)
+	indexTypeParts(b, idx)
 	if c := (schema.Comment{}); sqlx.Has(idx.Attrs, &c) {
 		b.P("COMMENT", quote(c.Text))
 	}
 }
 
-func indexParts(b *sqlx.Builder, parts []*schema.IndexPart) {
+func indexTypeParts(b *sqlx.Builder, idx *schema.Index) {
+	// Skip BTREE as it is the default type.
+	if t := indexType(idx.Attrs); t.T == IndexTypeHash {
+		b.P("USING", t.T)
+	}
 	b.Wrap(func(b *sqlx.Builder) {
-		b.MapComma(parts, func(i int, b *sqlx.Builder) {
-			switch part := parts[i]; {
+		b.MapComma(idx.Parts, func(i int, b *sqlx.Builder) {
+			switch part := idx.Parts[i]; {
 			case part.C != nil:
 				b.Ident(part.C.Name)
 			case part.X != nil:
 				b.WriteString(sqlx.MayWrap(part.X.(*schema.RawExpr).X))
 			}
-			if s := (&SubPart{}); sqlx.Has(parts[i].Attrs, s) {
+			if s := (&SubPart{}); sqlx.Has(idx.Parts[i].Attrs, s) {
 				b.WriteString(fmt.Sprintf("(%d)", s.Len))
 			}
 			// Ignore default collation (i.e. "ASC")
-			if parts[i].Desc {
+			if idx.Parts[i].Desc {
 				b.P("DESC")
 			}
 		})
diff --git a/sql/mysql/sqlspec.go b/sql/mysql/sqlspec.go
index 1f327b19..8d91b951 100644
--- a/sql/mysql/sqlspec.go
+++ b/sql/mysql/sqlspec.go
@@ -80,6 +80,7 @@ var (
 	hclState = schemahcl.New(
 		schemahcl.WithTypes(TypeRegistry.Specs()),
 		schemahcl.WithScopedEnums("table.index.type", IndexTypeBTree, IndexTypeHash, IndexTypeFullText, IndexTypeSpatial),
+		schemahcl.WithScopedEnums("table.primary_key.type", IndexTypeBTree, IndexTypeHash, IndexTypeFullText, IndexTypeSpatial),
 		schemahcl.WithScopedEnums("table.column.as.type", stored, persistent, virtual),
 		schemahcl.WithScopedEnums("table.foreign_key.on_update", specutil.ReferenceVars...),
 		schemahcl.WithScopedEnums("table.foreign_key.on_delete", specutil.ReferenceVars...),
@@ -100,7 +101,7 @@ var (
 // ForeignKeySpecs into ForeignKeys, as the target tables do not necessarily exist in the schema
 // at this point. Instead, the linking is done by the convertSchema function.
 func convertTable(spec *sqlspec.Table, parent *schema.Schema) (*schema.Table, error) {
-	t, err := specutil.Table(spec, parent, convertColumn, specutil.PrimaryKey, convertIndex, convertCheck)
+	t, err := specutil.Table(spec, parent, convertColumn, convertPK, convertIndex, convertCheck)
 	if err != nil {
 		return nil, err
 	}
@@ -119,20 +120,39 @@ func convertTable(spec *sqlspec.Table, parent *schema.Schema) (*schema.Table, er
 	return t, err
 }
 
+// convertPK converts a sqlspec.PrimaryKey into a schema.Index.
+func convertPK(spec *sqlspec.PrimaryKey, parent *schema.Table) (*schema.Index, error) {
+	idx, err := specutil.PrimaryKey(spec, parent)
+	if err != nil {
+		return nil, err
+	}
+	if err := convertIndexType(idx, spec); err != nil {
+		return nil, err
+	}
+	return idx, nil
+}
+
 // convertIndex converts a sqlspec.Index into a schema.Index.
 func convertIndex(spec *sqlspec.Index, parent *schema.Table) (*schema.Index, error) {
 	idx, err := specutil.Index(spec, parent, convertPart)
 	if err != nil {
 		return nil, err
 	}
+	if err := convertIndexType(idx, spec); err != nil {
+		return nil, err
+	}
+	return idx, nil
+}
+
+func convertIndexType(idx *schema.Index, spec specutil.Attrer) error {
 	if attr, ok := spec.Attr("type"); ok {
 		t, err := attr.String()
 		if err != nil {
-			return nil, err
+			return err
 		}
 		idx.AddAttrs(&IndexType{T: t})
 	}
-	return idx, nil
+	return nil
 }
 
 func convertPart(spec *sqlspec.IndexPart, part *schema.IndexPart) error {
@@ -221,7 +241,7 @@ func tableSpec(t *schema.Table) (*sqlspec.Table, error) {
 	ts, err := specutil.FromTable(
 		t,
 		columnSpec,
-		specutil.FromPrimaryKey,
+		pkSpec,
 		indexSpec,
 		specutil.FromForeignKey,
 		checkSpec,
@@ -238,16 +258,30 @@ func tableSpec(t *schema.Table) (*sqlspec.Table, error) {
 	return ts, nil
 }
 
+func pkSpec(idx *schema.Index) (*sqlspec.PrimaryKey, error) {
+	spec, err := specutil.FromPrimaryKey(idx)
+	if err != nil {
+		return nil, err
+	}
+	spec.Extra.Attrs = indexTypeSpec(idx, spec.Extra.Attrs)
+	return spec, nil
+}
+
 func indexSpec(idx *schema.Index) (*sqlspec.Index, error) {
 	spec, err := specutil.FromIndex(idx, partAttr)
 	if err != nil {
 		return nil, err
 	}
+	spec.Extra.Attrs = indexTypeSpec(idx, spec.Extra.Attrs)
+	return spec, nil
+}
+
+func indexTypeSpec(idx *schema.Index, attrs []*schemahcl.Attr) []*schemahcl.Attr {
 	// Avoid printing the index type if it is the default.
 	if i := (IndexType{}); sqlx.Has(idx.Attrs, &i) && i.T != IndexTypeBTree {
-		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.VarAttr("type", strings.ToUpper(i.T)))
+		attrs = append(attrs, specutil.VarAttr("type", strings.ToUpper(i.T)))
 	}
-	return spec, nil
+	return attrs
 }
 
 func partAttr(_ *schema.Index, part *schema.IndexPart, spec *sqlspec.IndexPart) error {
diff --git a/sql/schema/migrate.go b/sql/schema/migrate.go
index d41ffeb9..7bc19dd4 100644
--- a/sql/schema/migrate.go
+++ b/sql/schema/migrate.go
@@ -128,6 +128,22 @@ type (
 		From, To *Index
 	}
 
+	// AddPrimaryKey describes a primary-key creation change.
+	AddPrimaryKey struct {
+		P *Index
+	}
+
+	// DropPrimaryKey describes a primary-key removal change.
+	DropPrimaryKey struct {
+		P *Index
+	}
+
+	// ModifyPrimaryKey describes a primary-key modification.
+	ModifyPrimaryKey struct {
+		From, To *Index
+		Change   ChangeKind
+	}
+
 	// AddForeignKey describes a foreign-key creation change.
 	AddForeignKey struct {
 		F *ForeignKey
@@ -382,6 +398,9 @@ func (*AddIndex) change()         {}
 func (*DropIndex) change()        {}
 func (*ModifyIndex) change()      {}
 func (*RenameIndex) change()      {}
+func (*AddPrimaryKey) change()    {}
+func (*DropPrimaryKey) change()   {}
+func (*ModifyPrimaryKey) change() {}
 func (*AddCheck) change()         {}
 func (*DropCheck) change()        {}
 func (*ModifyCheck) change()      {}


Test patch:
diff --git a/internal/integration/testdata/mysql/cli-project-url-escape.txt b/internal/integration/testdata/mysql/cli-project-url-escape.txt
new file mode 100644
index 00000000..9ec7b048
--- /dev/null
+++ b/internal/integration/testdata/mysql/cli-project-url-escape.txt
@@ -0,0 +1,36 @@
+only mysql8
+
+execsql 'CREATE USER IF NOT EXISTS "a8m"@"%" IDENTIFIED BY "&pass?"'
+execsql 'GRANT ALL PRIVILEGES ON *.* TO "a8m"@"%" WITH GRANT OPTION'
+
+atlas schema inspect --env local > got.txt
+cmp got.txt want.txt
+
+! atlas schema inspect --env failed
+stderr 'invalid port ":&pass" after host'
+
+execsql 'DROP USER "a8m"@"%"'
+
+-- atlas.hcl --
+variable "pass" {
+  type    = string
+  default = "&pass?"
+}
+
+locals {
+  escaped_pass = urlescape(var.pass)
+}
+
+env "local" {
+    url = "mysql://a8m:${local.escaped_pass}@localhost:3308/script_cli_project_url_escape"
+}
+
+env "failed" {
+    url = "mysql://a8m:${var.pass}@localhost:3308/script_cli_project_url_escape"
+}
+
+-- want.txt --
+schema "script_cli_project_url_escape" {
+  charset = "utf8mb4"
+  collate = "utf8mb4_0900_ai_ci"
+}
\ No newline at end of file
diff --git a/internal/integration/testdata/mysql/primary-key.txt b/internal/integration/testdata/mysql/primary-key.txt
new file mode 100644
index 00000000..1f054b29
--- /dev/null
+++ b/internal/integration/testdata/mysql/primary-key.txt
@@ -0,0 +1,99 @@
+only mysql8
+
+# Create table.
+apply 1.hcl
+cmpshow users 1.sql
+
+# Add a primary-key.
+apply 2.hcl
+cmpshow users 2.sql
+
+# Modify the primary-key column and type.
+apply 3.hcl
+cmpshow users 3.sql
+
+# Drop the primary-key.
+apply 1.hcl
+cmpshow users 1.sql
+
+-- 1.hcl --
+schema "$db" {
+  charset = "utf8mb4"
+  collate = "utf8mb4_general_ci"
+}
+
+table "users" {
+  schema = schema.$db
+  column "id" {
+    null = false
+    type = varchar(128)
+  }
+  column "oid" {
+    null = false
+    type = varchar(128)
+  }
+}
+
+-- 1.sql --
+CREATE TABLE `users` (
+  `id` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
+  `oid` varchar(128) COLLATE utf8mb4_general_ci NOT NULL
+)
+
+-- 2.hcl --
+schema "$db" {
+  charset = "utf8mb4"
+  collate = "utf8mb4_general_ci"
+}
+
+table "users" {
+  schema = schema.$db
+  column "id" {
+    null = false
+    type = varchar(128)
+  }
+  column "oid" {
+    null = false
+    type = varchar(128)
+  }
+  primary_key {
+    columns = [column.id]
+  }
+}
+
+-- 2.sql --
+CREATE TABLE `users` (
+  `id` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
+  `oid` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
+  PRIMARY KEY (`id`)
+)
+
+
+-- 3.hcl --
+schema "$db" {
+  charset = "utf8mb4"
+  collate = "utf8mb4_general_ci"
+}
+
+table "users" {
+  schema = schema.$db
+  column "id" {
+    null = false
+    type = varchar(128)
+  }
+  column "oid" {
+    null = false
+    type = varchar(128)
+  }
+  primary_key {
+    columns = [column.oid]
+    type    = HASH
+  }
+}
+
+-- 3.sql --
+CREATE TABLE `users` (
+  `id` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
+  `oid` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
+  PRIMARY KEY (`oid`)
+)
diff --git a/schemahcl/stdlib_test.go b/schemahcl/stdlib_test.go
index 0baaaa02..299b3ba2 100644
--- a/schemahcl/stdlib_test.go
+++ b/schemahcl/stdlib_test.go
@@ -6,6 +6,7 @@ package schemahcl
 
 import (
 	"fmt"
+	"net/url"
 	"testing"
 
 	"github.com/zclconf/go-cty/cty"
@@ -93,3 +94,17 @@ func TestURLQuerySetFunc(t *testing.T) {
 		})
 	}
 }
+
+func TestURLEscapeFunc(t *testing.T) {
+	for _, tt := range []string{"foo", "foo?", "foo&"} {
+		t.Run(tt, func(t *testing.T) {
+			got, err := urlEscape.Call([]cty.Value{cty.StringVal(tt)})
+			if err != nil {
+				t.Fatalf("unexpected error: %s", err)
+			}
+			if want := url.QueryEscape(tt); got.AsString() != want {
+				t.Errorf("wrong result\ngot:  %#v\nwant: %#v", got, want)
+			}
+		})
+	}
+}
diff --git a/sql/mysql/diff_test.go b/sql/mysql/diff_test.go
index 14e6e50a..495ff385 100644
--- a/sql/mysql/diff_test.go
+++ b/sql/mysql/diff_test.go
@@ -37,18 +37,6 @@ func TestDiff_TableDiff(t *testing.T) {
 			from: &schema.Table{Name: "users", Schema: &schema.Schema{Name: "public"}, Columns: []*schema.Column{{Name: "enum", Default: &schema.RawExpr{X: "'A'"}, Type: &schema.ColumnType{Type: &schema.EnumType{Values: []string{"A"}}}}}},
 			to:   &schema.Table{Name: "users", Columns: []*schema.Column{{Name: "enum", Default: &schema.RawExpr{X: `"A"`}, Type: &schema.ColumnType{Type: &schema.EnumType{Values: []string{"A"}}}}}},
 		},
-		{
-			name: "change primary key",
-			from: func() *schema.Table {
-				t := &schema.Table{Name: "users", Schema: &schema.Schema{Name: "public"}, Columns: []*schema.Column{{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}}}}
-				t.PrimaryKey = &schema.Index{
-					Parts: []*schema.IndexPart{{C: t.Columns[0]}},
-				}
-				return t
-			}(),
-			to:      &schema.Table{Name: "users"},
-			wantErr: true,
-		},
 		{
 			name: "modify counter",
 			from: &schema.Table{Name: "users", Schema: &schema.Schema{Name: "public"}, Attrs: []schema.Attr{&AutoIncrement{V: 1}}},
@@ -426,6 +414,65 @@ func TestDiff_TableDiff(t *testing.T) {
 				},
 			}
 		}(),
+		func() testcase {
+			from := schema.NewTable("t1").
+				SetSchema(schema.New("test")).
+				AddColumns(schema.NewIntColumn("id", "int"))
+			to := schema.NewTable("t1").
+				SetSchema(schema.New("test")).
+				AddColumns(schema.NewIntColumn("id", "int"))
+			to.SetPrimaryKey(schema.NewPrimaryKey(to.Columns...))
+			return testcase{
+				name: "add primary-key",
+				from: from,
+				to:   to,
+				wantChanges: []schema.Change{
+					&schema.AddPrimaryKey{P: to.PrimaryKey},
+				},
+			}
+		}(),
+		func() testcase {
+			from := schema.NewTable("t1").
+				SetSchema(schema.New("test")).
+				AddColumns(schema.NewIntColumn("id", "int"))
+			from.SetPrimaryKey(schema.NewPrimaryKey(from.Columns...))
+			to := schema.NewTable("t1").
+				SetSchema(schema.New("test")).
+				AddColumns(schema.NewIntColumn("id", "int"))
+			return testcase{
+				name: "drop primary-key",
+				from: from,
+				to:   to,
+				wantChanges: []schema.Change{
+					&schema.DropPrimaryKey{P: from.PrimaryKey},
+				},
+			}
+		}(),
+		func() testcase {
+			from := schema.NewTable("t1").
+				SetSchema(schema.New("test")).
+				AddColumns(schema.NewStringColumn("id", "varchar(255)"))
+			from.SetPrimaryKey(schema.NewPrimaryKey(from.Columns...))
+			to := schema.NewTable("t1").
+				SetSchema(schema.New("test")).
+				AddColumns(schema.NewStringColumn("id", "varchar(255)"))
+			to.SetPrimaryKey(
+				schema.NewPrimaryKey(from.Columns...).
+					AddAttrs(&IndexType{T: IndexTypeHash}),
+			)
+			return testcase{
+				name: "modify primary-key",
+				from: from,
+				to:   to,
+				wantChanges: []schema.Change{
+					&schema.ModifyPrimaryKey{
+						From:   from.PrimaryKey,
+						To:     to.PrimaryKey,
+						Change: schema.ChangeAttr,
+					},
+				},
+			}
+		}(),
 		func() testcase {
 			var (
 				ref = &schema.Table{
diff --git a/sql/mysql/migrate_test.go b/sql/mysql/migrate_test.go
index 8e2c9533..64d93ee1 100644
--- a/sql/mysql/migrate_test.go
+++ b/sql/mysql/migrate_test.go
@@ -264,6 +264,105 @@ func TestPlanChanges(t *testing.T) {
 				},
 			},
 		},
+		// Drop a primary key.
+		{
+			changes: []schema.Change{
+				func() schema.Change {
+					users := schema.NewTable("users").
+						SetSchema(schema.New("test")).
+						AddColumns(
+							schema.NewIntColumn("id", "bigint"),
+						)
+					return &schema.ModifyTable{
+						T: users,
+						Changes: []schema.Change{
+							&schema.DropPrimaryKey{
+								P: schema.NewPrimaryKey(users.Columns...),
+							},
+						},
+					}
+				}(),
+			},
+			wantPlan: &migrate.Plan{
+				Reversible: true,
+				Changes: []*migrate.Change{
+					{
+						Cmd:     "ALTER TABLE `users` DROP PRIMARY KEY",
+						Reverse: "ALTER TABLE `users` ADD PRIMARY KEY (`id`)",
+					},
+				},
+			},
+			options: []migrate.PlanOption{
+				func(o *migrate.PlanOptions) { o.SchemaQualifier = new(string) },
+			},
+		},
+		// Add a primary key.
+		{
+			changes: []schema.Change{
+				func() schema.Change {
+					users := schema.NewTable("users").
+						SetSchema(schema.New("test")).
+						AddColumns(
+							schema.NewIntColumn("id", "bigint"),
+						)
+					users.SetPrimaryKey(schema.NewPrimaryKey(users.Columns...))
+					return &schema.ModifyTable{
+						T: users,
+						Changes: []schema.Change{
+							&schema.AddPrimaryKey{
+								P: users.PrimaryKey,
+							},
+						},
+					}
+				}(),
+			},
+			wantPlan: &migrate.Plan{
+				Reversible: true,
+				Changes: []*migrate.Change{
+					{
+						Cmd:     "ALTER TABLE `dev`.`users` ADD PRIMARY KEY (`id`)",
+						Reverse: "ALTER TABLE `dev`.`users` DROP PRIMARY KEY",
+					},
+				},
+			},
+			options: []migrate.PlanOption{
+				func(o *migrate.PlanOptions) {
+					dev := "dev"
+					o.SchemaQualifier = &dev
+				},
+			},
+		},
+		// Modify a primary key.
+		{
+			changes: []schema.Change{
+				func() schema.Change {
+					users := schema.NewTable("users").
+						AddColumns(
+							schema.NewStringColumn("id", "varchar(255)"),
+						)
+					users.SetPrimaryKey(schema.NewPrimaryKey(users.Columns...))
+					return &schema.ModifyTable{
+						T: users,
+						Changes: []schema.Change{
+							&schema.ModifyPrimaryKey{
+								From: schema.NewPrimaryKey(users.Columns...).
+									AddAttrs(&IndexType{T: IndexTypeHash}),
+								To: users.PrimaryKey,
+							},
+						},
+					}
+				}(),
+			},
+			wantPlan: &migrate.Plan{
+				Reversible: true,
+				Changes: []*migrate.Change{
+					{
+						Cmd:     "ALTER TABLE `users` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`)",
+						Reverse: "ALTER TABLE `users` DROP PRIMARY KEY, ADD PRIMARY KEY USING HASH (`id`)",
+					},
+				},
+			},
+		},
 		{
 			changes: []schema.Change{
 				func() schema.Change {
diff --git a/sql/mysql/sqlspec_test.go b/sql/mysql/sqlspec_test.go
index 5451f79f..c5effdeb 100644
--- a/sql/mysql/sqlspec_test.go
+++ b/sql/mysql/sqlspec_test.go
@@ -583,6 +583,73 @@ table "users" {
 	require.EqualValues(t, exp, &s)
 }
 
+func TestMarshalSpec_PrimaryKeyType(t *testing.T) {
+	s := schema.New("test").
+		AddTables(
+			schema.NewTable("users").
+				AddColumns(
+					schema.NewStringColumn("id", "varchar(255)"),
+				),
+		)
+	s.Tables[0].SetPrimaryKey(
+		schema.NewPrimaryKey(s.Tables[0].Columns...).
+			AddAttrs(&IndexType{T: IndexTypeHash}),
+	)
+	buf, err := MarshalHCL(s)
+	require.NoError(t, err)
+	exp := `table "users" {
+  schema = schema.test
+  column "id" {
+    null = false
+    type = sql("varchar(255)")
+  }
+  primary_key {
+    columns = [column.id]
+    type    = HASH
+  }
+}
+schema "test" {
+}
+`
+	require.EqualValues(t, exp, string(buf))
+}
+
+func TestUnmarshalSpec_PrimaryKeyType(t *testing.T) {
+	var s schema.Schema
+	err := EvalHCLBytes([]byte(`table "users" {
+  schema = schema.test
+  column "id" {
+    null = false
+    type = sql("varchar(255)")
+  }
+  primary_key {
+    columns = [column.id]
+    type    = HASH
+  }
+}
+schema "test" {
+}`), &s, nil)
+	require.NoError(t, err)
+	exp := schema.New("test").
+		AddTables(
+			schema.NewTable("users").
+				AddColumns(&schema.Column{
+					Name: "id",
+					Type: &schema.ColumnType{
+						Type: &schema.StringType{
+							T:    "varchar",
+							Size: 255,
+						},
+					},
+				}),
+		)
+	exp.Tables[0].SetPrimaryKey(&schema.Index{
+		Parts: []*schema.IndexPart{{C: exp.Tables[0].Columns[0]}},
+		Attrs: []schema.Attr{&IndexType{T: IndexTypeHash}},
+	})
+	require.EqualValues(t, exp, &s)
+}
+
 func TestMarshalSpec_IndexParts(t *testing.T) {
 	c := schema.NewStringColumn("name", "text")
 	c2 := schema.NewStringColumn("Full Name", "text")
diff --git a/sql/postgres/diff_test.go b/sql/postgres/diff_test.go
index d438b7a7..dd320b54 100644
--- a/sql/postgres/diff_test.go
+++ b/sql/postgres/diff_test.go
@@ -27,18 +27,6 @@ func TestDiff_TableDiff(t *testing.T) {
 			from: &schema.Table{Name: "users", Schema: &schema.Schema{Name: "public"}},
 			to:   &schema.Table{Name: "users"},
 		},
-		{
-			name: "change primary key columns",
-			from: func() *schema.Table {
-				t := &schema.Table{Name: "users", Schema: &schema.Schema{Name: "public"}, Columns: []*schema.Column{{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}}}}
-				t.PrimaryKey = &schema.Index{
-					Parts: []*schema.IndexPart{{C: t.Columns[0]}},
-				}
-				return t
-			}(),
-			to:      &schema.Table{Name: "users"},
-			wantErr: true,
-		},
 		{
 			name: "change identity attributes",
 			from: func() *schema.Table {
@@ -412,6 +400,31 @@ func TestDiff_TableDiff(t *testing.T) {
 				},
 			}
 		}(),
+		func() testcase {
+			from := schema.NewTable("t1").
+				SetSchema(schema.New("test")).
+				AddColumns(schema.NewStringColumn("id", "varchar"), schema.NewBoolColumn("active", "bool"))
+			from.SetPrimaryKey(schema.NewPrimaryKey(from.Columns...))
+			to := schema.NewTable("t1").
+				SetSchema(schema.New("test")).
+				AddColumns(schema.NewStringColumn("id", "varchar"), schema.NewBoolColumn("active", "bool"))
+			to.SetPrimaryKey(
+				schema.NewPrimaryKey(from.Columns...).
+					AddAttrs(&IndexPredicate{P: "active"}),
+			)
+			return testcase{
+				name: "modify primary-key",
+				from: from,
+				to:   to,
+				wantChanges: []schema.Change{
+					&schema.ModifyPrimaryKey{
+						From:   from.PrimaryKey,
+						To:     to.PrimaryKey,
+						Change: schema.ChangeAttr,
+					},
+				},
+			}
+		}(),
 		func() testcase {
 			var (
 				ref = &schema.Table{
diff --git a/sql/sqlite/diff_test.go b/sql/sqlite/diff_test.go
index dc8e8462..ef5b1863 100644
--- a/sql/sqlite/diff_test.go
+++ b/sql/sqlite/diff_test.go
@@ -26,18 +26,6 @@ func TestDiff_TableDiff(t *testing.T) {
 			from: &schema.Table{Name: "users", Schema: &schema.Schema{Name: "public"}},
 			to:   &schema.Table{Name: "users"},
 		},
-		{
-			name: "change primary key",
-			from: func() *schema.Table {
-				t := &schema.Table{Name: "users", Schema: &schema.Schema{Name: "public"}, Columns: []*schema.Column{{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}}}}
-				t.PrimaryKey = &schema.Index{
-					Parts: []*schema.IndexPart{{C: t.Columns[0]}},
-				}
-				return t
-			}(),
-			to:      &schema.Table{Name: "users"},
-			wantErr: true,
-		},
 		{
 			name: "add attr",
 			from: &schema.Table{Name: "t1", Schema: &schema.Schema{Name: "public"}},
@@ -228,6 +216,31 @@ func TestDiff_TableDiff(t *testing.T) {
 				},
 			}
 		}(),
+		func() testcase {
+			from := schema.NewTable("t1").
+				SetSchema(schema.New("test")).
+				AddColumns(schema.NewStringColumn("id", "varchar"), schema.NewBoolColumn("active", "bool"))
+			from.SetPrimaryKey(schema.NewPrimaryKey(from.Columns...))
+			to := schema.NewTable("t1").
+				SetSchema(schema.New("test")).
+				AddColumns(schema.NewStringColumn("id", "varchar"), schema.NewBoolColumn("active", "bool"))
+			to.SetPrimaryKey(
+				schema.NewPrimaryKey(from.Columns...).
+					AddAttrs(&IndexPredicate{P: "active"}),
+			)
+			return testcase{
+				name: "modify primary-key",
+				from: from,
+				to:   to,
+				wantChanges: []schema.Change{
+					&schema.ModifyPrimaryKey{
+						From:   from.PrimaryKey,
+						To:     to.PrimaryKey,
+						Change: schema.ChangeAttr,
+					},
+				},
+			}
+		}(),
 		func() testcase {
 			var (
 				ref = &schema.Table{