Repository: ariga/atlas
Pull request #1398 (2023-01-11 15:07:25)

doc/md: sqlc guide (versioned)


Patch:
diff --git a/doc/md/guides/frameworks/sqlc-declarative.md b/doc/md/guides/frameworks/sqlc-declarative.md
index 23207031..24cd52ee 100644
--- a/doc/md/guides/frameworks/sqlc-declarative.md
+++ b/doc/md/guides/frameworks/sqlc-declarative.md
@@ -45,14 +45,41 @@ If you followed the sqlc tutorial, you may end up with a `schema.sql` file that
 ```sql title="schema.sql"
 CREATE TABLE authors
 (
-    id   BIGSERIAL PRIMARY KEY,
-    name text NOT NULL,
-    bio  text
+  id   BIGSERIAL PRIMARY KEY,
+  name text NOT NULL,
+  bio  text
 );
 ```
 
-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:
+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
@@ -105,18 +132,66 @@ After running the command above and confirming the changes, your database should
 
 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 and running `schema apply` again.
+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
+  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" \
@@ -144,7 +219,7 @@ will handle the rest.
 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`)
+  - 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.
 
diff --git a/doc/md/guides/frameworks/sqlc-versioned.md b/doc/md/guides/frameworks/sqlc-versioned.md
new file mode 100644
index 00000000..794a0547
--- /dev/null
+++ b/doc/md/guides/frameworks/sqlc-versioned.md
@@ -0,0 +1,285 @@
+---
+id: sqlc-versioned
+title: Versioned migrations for sqlc
+slug: /guides/frameworks/sqlc-versioned
+---
+
+In our [previous sqlc guide](/guides/frameworks/sqlc-declarative), we saw how we can use Atlas to handle the schema
+management process using the [declarative workflow](/concepts/declarative-vs-versioned#declarative-migrations). This
+works great in many situations, but some teams prefer the imperative approach where schema changes are explicitly
+checked in to source control and verified during code review
+(see [this video](https://www.youtube.com/watch?v=FCeIjPb4AYs) to learn why).
+
+To accommodate such cases, Atlas supports another kind of workflow for handling migrations,
+called [versioned migrations](/concepts/declarative-vs-versioned#versioned-migrations), where the changes are stored as
+SQL files
+and replayed on the database as needed. This ensures that the code to be executed on the database is known and approved
+beforehand and gives engineers ultimate control on exactly what code is going to be run in production.
+
+In this guide we will see how we can use sqlc with Atlas, using the versioned migrations workflow.
+
+:::note
+If you haven't already done so, it will be helpful to have read the
+previous [sqlc guide](/guides/frameworks/sqlc-declarative) where we have explained some concepts that we will expand
+on in this guide. You should also check
+the [sqlc getting started with PostgreSQL](https://docs.sqlc.dev/en/latest/tutorials/getting-started-postgresql.html)
+guide since this guide continues where it left off.
+:::
+
+## Versioned migrations
+
+[Versioned migrations](/concepts/declarative-vs-versioned#versioned-migrations) is a migration workflow where whenever
+we want to change the database schema, we create a _migration file_, which is an SQL file containing SQL statements to
+upgrade the schema from the current version to the next. Many schema management tools, such as Flyway and Liquibase,
+support this strategy and Atlas supports it as well.
+
+Atlas's versioned migrations workflow works very well with sqlc. In fact, in most cases, you can simply
+point [sqlc schema configuration](https://docs.sqlc.dev/en/latest/reference/config.html#sql) to the migration directory,
+and things should just work.
+
+Usually, teams using versioned migrations author the migration files manually. While this is a common approach, there
+are obvious downsides to it. Writing migrations by hand can be both error-prone and time-consuming for developers.
+
+## Migration Authoring
+
+Atlas has support for a strategy that combines the simplicity of the declarative workflow with the control provided by
+the versioned one. With this approach, which is
+called "[versioned migration authoring](/versioned/diff)", we still define our desired state in HCL or
+SQL but use Atlas to compute the required changes, storing them in the migration directory as SQL files.
+
+This strategy brings the best of both worlds, where we can have a single source of truth (our desired state) and have all the
+authored SQL files that will be used to reach the desired state. To learn more about this approach and see it in action,
+watch this [video](https://www.youtube.com/watch?v=L-UlkXtp3OY).
+
+Now that we have a bit of an understanding of all the concepts, let's see how we can use Atlas with sqlc, using
+the `schema.sql` as our desired state and combine versioned migrations with migration authoring.
+
+## Creating the initial migration directory
+
+The first thing we should do is create the initial migration directory. If you are following
+the [sqlc getting started with PostgreSQL](https://docs.sqlc.dev/en/latest/tutorials/getting-started-postgresql.html)
+your `schema.sql` and `query.sql` should look like this:
+
+```sql title="schema.sql"
+CREATE TABLE authors
+(
+    id   BIGSERIAL PRIMARY KEY,
+    name text NOT NULL,
+    bio  text
+);
+```
+
+```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;
+```
+
+We have to generate a diff of our desired state (`schema.sql`) and our current state (the migration directory, that
+right now doesn't even exist), we can generate the diff with a simple command:
+
+```shell
+atlas migrate diff initial_schema \
+  --dir "file://migrations" \
+  --to "file://schema.sql" \
+  --dev-url "docker://postgres?search_path=public"
+```
+
+A new `migrations` directory should be created with two files in it.
+
+```text
+.
+├── 20230102211759_initial_schema.sql
+└── atlas.sum
+```
+
+As expected, the `20230102211759_initial_schema.sql` file holds the commands to create our initial schema.
+
+```sql title="20230102211759_initial_schema.sql"
+-- create "authors" table
+CREATE TABLE "authors" ("id" bigserial NOT NULL, "name" text NOT NULL, "bio" text NULL, PRIMARY KEY ("id"));
+```
+
+:::info
+Like in the last guide, we need a temporary database to check, simulate and validate the
+generated queries. This database is provided using the `dev-url` flag. Atlas has support for running database
+containers, which is what we are using in this example. Fore more information about dev
+databases, [read about them here](/concepts/dev-database).
+:::
+
+From the sqlc side, we don't have to do anything since we have not changed our `schema.sql` or `query.sql` file.
+
+## Migrating the database
+
+In this guide, we are using a Postgres
+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
+```
+
+Now that we have our base migration directory and target database, we can migrate our schema based on it. To do so, we
+have to use a command very similar to the one used on the previous guide.
+
+```shell
+atlas migrate apply --url "postgres://postgres:pass@localhost:5432/sqlc?sslmode=disable"
+```
+
+The command `migrate apply` connects to the given database [URL](/concepts/url) provided in the `url` flag, checks the
+current state of the database and applies any pending migrations.
+
+After running the `migrate apply` command, you should be able to see an output similar to this:
+
+```text
+Migrating to version 20230102211759 (1 migrations in total):
+
+  -- migrating version 20230102211759
+	-> CREATE TABLE "authors" ("id" bigserial NOT NULL, "name" text NOT NULL, "bio" text NULL, PRIMARY KEY ("id"));
+  -- ok (3.282921ms)
+
+  -------------------------
+  -- 4.570005ms
+  -- 1 migrations
+  -- 1 sql statements
+```
+
+## Updating the schema
+
+The process of changing our schema is quite easy and similar to the declarative way. First we update our `schema.sql`
+file, let's add a new `age` 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 as well.
+
+```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;
+```
+
+When working with sqlc, after every change to the `schema.sql` or `query.sql` file, we should execute the sqlc generate
+command again. Even in the cases where the changes seem to not cause problems, like adding a new column, this is a
+requirement from sqlc, since it replaces the `*` with explicit column names.
+
+```shell
+sqlc generate
+```
+
+:::note
+sqlc only shows messages when encountering an error, if the generator process was successful, no output should be
+shown.
+:::
+
+The `models.go` and `query.sql.go` should have been updated accordingly. Now we only have to update our `migrations`
+directory, we can use the same command from before, using "add_age_field" as our migration name this time:
+
+```shell
+atlas migrate diff add_age_field \
+  --dir "file://migrations" \
+  --to "file://schema.sql" \
+  --dev-url "docker://postgres?search_path=public"
+```
+
+If you look at the migration directory, a new SQL file should be created with the statements required for adding a new
+`age` column.
+
+```text {3}
+.
+├── 20230102211759_initial_schema.sql
+├── 20230102212813_add_age_field.sql
+└── atlas.sum
+```
+
+The new `20230102212813_add_age_field.sql` should have all the changes required to reach the desired state from our
+previous state.
+
+```sql title="20230102212813_add_age_field.sql"
+-- modify "authors" table
+ALTER TABLE "authors" ADD COLUMN "age" integer NULL;
+```
+
+For applying the migrations, we only have to execute `atlas migrate apply` again:
+
+```shell
+atlas migrate apply --url "postgres://postgres:pass@localhost:5432/sqlc?sslmode=disable"
+```
+
+If everything went correctly, you should see the output similar to the one below:
+
+```text
+Migrating to version 20230102212813 from 20230102211759 (1 migrations in total):
+
+  -- migrating version 20230102212813
+	-> ALTER TABLE "authors" ADD COLUMN "age" integer NULL;
+  -- ok (1.565535ms)
+
+  -------------------------
+  -- 2.839369ms
+  -- 1 migrations
+  -- 1 sql statements
+```
+
+## Complete workflow
+
+With the versioned strategy, we 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 migrate diff` to compute and store the required changes
+- Run Atlas during the development and migration process, referencing the migration directory.
+
+## Wrapping up
+
+In this guide we saw how Atlas can be used with sqlc in a versioned way. We used Atlas to create migration files, while
+still keeping a single source of truth for our database schema. The versioned strategy requires more steps, but allows
+for more control 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 af66ea55..5969de72 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',
@@ -252,6 +257,11 @@ module.exports = {
                     type: 'doc',
                     id:'guides/frameworks/sqlc-declarative',
                     label: 'Declarative migrations for sqlc'
+                },
+                {
+                    type: 'doc',
+                    id:'guides/frameworks/sqlc-versioned',
+                    label: 'Versioned 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..37cb6365 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(spec, idx); 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(spec, idx); err != nil {
+		return nil, err
+	}
+	return idx, nil
+}
+
+func convertIndexType(spec specutil.Attrer, idx *schema.Index) 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/postgres/migrate.go b/sql/postgres/migrate.go
index 071fc792..657d8015 100644
--- a/sql/postgres/migrate.go
+++ b/sql/postgres/migrate.go
@@ -160,7 +160,7 @@ func (s *state) addTable(ctx context.Context, add *schema.AddTable) error {
 		})
 		if pk := add.T.PrimaryKey; pk != nil {
 			b.Comma().P("PRIMARY KEY")
-			if err := s.indexParts(b, pk); err != nil {
+			if err := s.index(b, pk); err != nil {
 				errs = append(errs, err.Error())
 			}
 		}
@@ -421,15 +421,31 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 					}
 				}
 			case *schema.AddIndex:
+				// Skip reversing this operation as it is the inverse of
+				// the operation above and should not be used besides this.
 				b.P("ADD CONSTRAINT").Ident(change.I.Name).P("UNIQUE")
 				if err := s.indexParts(b, change.I); err != nil {
 					return err
 				}
-				// Skip reversing this operation as it is the inverse of
-				// the operation below and should not be used besides this.
 			case *schema.DropIndex:
 				b.P("DROP CONSTRAINT").Ident(change.I.Name)
 				reverse = append(reverse, &schema.AddIndex{I: change.I})
+			case *schema.AddPrimaryKey:
+				b.P("ADD PRIMARY KEY")
+				if err := s.index(b, change.P); err != nil {
+					return err
+				}
+				reverse = append(reverse, &schema.DropPrimaryKey{P: change.P})
+			case *schema.DropPrimaryKey:
+				b.P("DROP CONSTRAINT").Ident(pkName(t, change.P))
+				reverse = append(reverse, &schema.AddPrimaryKey{P: change.P})
+			case *schema.ModifyPrimaryKey:
+				b.P("DROP CONSTRAINT").Ident(pkName(t, change.From))
+				b.P(", ADD PRIMARY KEY")
+				if err := s.index(b, change.To); err != nil {
+					return err
+				}
+				reverse = append(reverse, &schema.ModifyPrimaryKey{From: change.To, To: change.From, Change: change.Change})
 			case *schema.AddForeignKey:
 				b.P("ADD")
 				s.fks(b, change.F)
@@ -1287,3 +1303,12 @@ func containsT(ts []*schema.Table, t *schema.Table) bool {
 	}
 	return false
 }
+
+func pkName(t *schema.Table, pk *schema.Index) string {
+	if pk.Name != "" {
+		return pk.Name
+	}
+	// The default naming for primary-key constraints is <Table>_pkey.
+	// See: the ChooseIndexName function in PostgreSQL for more reference.
+	return t.Name + "_pkey"
+}
diff --git a/sql/postgres/sqlspec.go b/sql/postgres/sqlspec.go
index 01ac736b..3366fc87 100644
--- a/sql/postgres/sqlspec.go
+++ b/sql/postgres/sqlspec.go
@@ -147,7 +147,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, specutil.Check)
+	t, err := specutil.Table(spec, parent, convertColumn, convertPK, convertIndex, specutil.Check)
 	if err != nil {
 		return nil, err
 	}
@@ -306,6 +306,18 @@ func fixDefaultQuotes(spec *sqlspec.Column) error {
 	return nil
 }
 
+// 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 := convertIndexPK(spec, parent, idx); err != nil {
+		return nil, err
+	}
+	return idx, nil
+}
+
 // convertIndex converts a sqlspec.Index into a schema.Index.
 func convertIndex(spec *sqlspec.Index, t *schema.Table) (*schema.Index, error) {
 	idx, err := specutil.Index(spec, t, convertPart)
@@ -326,30 +338,38 @@ func convertIndex(spec *sqlspec.Index, t *schema.Table) (*schema.Index, error) {
 		}
 		idx.Attrs = append(idx.Attrs, &IndexPredicate{P: p})
 	}
+	if err := convertIndexPK(spec, t, idx); err != nil {
+		return nil, err
+	}
+	return idx, nil
+}
+
+// convertIndexPK converts the index parameters shared between primary and secondary indexes.
+func convertIndexPK(spec specutil.Attrer, t *schema.Table, idx *schema.Index) error {
 	if attr, ok := spec.Attr("page_per_range"); ok {
 		p, err := attr.Int64()
 		if err != nil {
-			return nil, err
+			return err
 		}
 		idx.Attrs = append(idx.Attrs, &IndexStorageParams{PagesPerRange: p})
 	}
 	if attr, ok := spec.Attr("include"); ok {
 		refs, err := attr.Refs()
 		if err != nil {
-			return nil, err
+			return err
 		}
 		if len(refs) == 0 {
-			return nil, fmt.Errorf("unexpected empty INCLUDE in index %q definition", spec.Name)
+			return fmt.Errorf("unexpected empty INCLUDE in index %q definition", idx.Name)
 		}
 		include := make([]*schema.Column, len(refs))
 		for i, r := range refs {
 			if include[i], err = specutil.ColumnByRef(t, r); err != nil {
-				return nil, err
+				return err
 			}
 		}
 		idx.Attrs = append(idx.Attrs, &IndexInclude{Columns: include})
 	}
-	return idx, nil
+	return nil
 }
 
 func convertPart(spec *sqlspec.IndexPart, part *schema.IndexPart) error {
@@ -521,7 +541,7 @@ func tableSpec(table *schema.Table) (*sqlspec.Table, error) {
 	spec, err := specutil.FromTable(
 		table,
 		columnSpec,
-		specutil.FromPrimaryKey,
+		pkSpec,
 		indexSpec,
 		specutil.FromForeignKey,
 		specutil.FromCheck,
@@ -535,6 +555,15 @@ func tableSpec(table *schema.Table) (*sqlspec.Table, error) {
 	return spec, nil
 }
 
+func pkSpec(idx *schema.Index) (*sqlspec.PrimaryKey, error) {
+	spec, err := specutil.FromPrimaryKey(idx)
+	if err != nil {
+		return nil, err
+	}
+	spec.Extra.Attrs = indexPKSpec(idx, spec.Extra.Attrs)
+	return spec, nil
+}
+
 func indexSpec(idx *schema.Index) (*sqlspec.Index, error) {
 	spec, err := specutil.FromIndex(idx, partAttr)
 	if err != nil {
@@ -544,20 +573,25 @@ func indexSpec(idx *schema.Index) (*sqlspec.Index, error) {
 	if i := (IndexType{}); sqlx.Has(idx.Attrs, &i) && strings.ToUpper(i.T) != IndexTypeBTree {
 		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.VarAttr("type", strings.ToUpper(i.T)))
 	}
+	if i := (IndexPredicate{}); sqlx.Has(idx.Attrs, &i) && i.P != "" {
+		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.VarAttr("where", strconv.Quote(i.P)))
+	}
+	spec.Extra.Attrs = indexPKSpec(idx, spec.Extra.Attrs)
+	return spec, nil
+}
+
+func indexPKSpec(idx *schema.Index, attrs []*schemahcl.Attr) []*schemahcl.Attr {
 	if i := (IndexInclude{}); sqlx.Has(idx.Attrs, &i) && len(i.Columns) > 0 {
 		refs := make([]*schemahcl.Ref, 0, len(i.Columns))
 		for _, c := range i.Columns {
 			refs = append(refs, specutil.ColumnRef(c.Name))
 		}
-		spec.Extra.Attrs = append(spec.Extra.Attrs, schemahcl.RefsAttr("include", refs...))
-	}
-	if i := (IndexPredicate{}); sqlx.Has(idx.Attrs, &i) && i.P != "" {
-		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.VarAttr("where", strconv.Quote(i.P)))
+		attrs = append(attrs, schemahcl.RefsAttr("include", refs...))
 	}
 	if p, ok := indexStorageParams(idx.Attrs); ok {
-		spec.Extra.Attrs = append(spec.Extra.Attrs, schemahcl.Int64Attr("page_per_range", p.PagesPerRange))
+		attrs = append(attrs, schemahcl.Int64Attr("page_per_range", p.PagesPerRange))
 	}
-	return spec, nil
+	return attrs
 }
 
 func partAttr(idx *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/internal/integration/testdata/postgres/primary-key.txt b/internal/integration/testdata/postgres/primary-key.txt
new file mode 100644
index 00000000..ae2f2a5c
--- /dev/null
+++ b/internal/integration/testdata/postgres/primary-key.txt
@@ -0,0 +1,97 @@
+only postgres15
+
+# Create table.
+apply 1.hcl
+cmpshow users 1.sql
+
+# Add a primary-key.
+apply 2.hcl
+cmpshow users 2.sql
+
+# Modify the primary-key include columns.
+apply 3.hcl
+cmpshow users 3.sql
+
+# Drop the primary-key.
+apply 1.hcl
+cmpshow users 1.sql
+
+-- 1.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "id" {
+    null = false
+    type = int
+  }
+  column "c" {
+    null = true
+    type = int
+  }
+}
+
+-- 1.sql --
+         Table "script_primary_key.users"
+ Column |  Type   | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id     | integer |           | not null |
+ c      | integer |           |          |
+
+-- 2.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "id" {
+    null = false
+    type = int
+  }
+  column "c" {
+    null = true
+    type = int
+  }
+  primary_key {
+    columns = [column.id]
+  }
+}
+
+-- 2.sql --
+         Table "script_primary_key.users"
+ Column |  Type   | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id     | integer |           | not null |
+ c      | integer |           |          |
+Indexes:
+    "users_pkey" PRIMARY KEY, btree (id)
+
+
+-- 3.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "id" {
+    null = false
+    type = int
+  }
+  column "c" {
+    null = true
+    type = int
+  }
+  primary_key {
+    columns = [column.id]
+    include = [column.c]
+  }
+}
+
+-- 3.sql --
+         Table "script_primary_key.users"
+ Column |  Type   | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id     | integer |           | not null |
+ c      | integer |           |          |
+Indexes:
+    "users_pkey" PRIMARY KEY, btree (id) INCLUDE (c)
+
+
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/postgres/migrate_test.go b/sql/postgres/migrate_test.go
index 57d0a36c..361891c9 100644
--- a/sql/postgres/migrate_test.go
+++ b/sql/postgres/migrate_test.go
@@ -370,6 +370,100 @@ 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", "int"),
+						)
+					return &schema.ModifyTable{
+						T: users,
+						Changes: []schema.Change{
+							&schema.DropPrimaryKey{
+								P: schema.NewPrimaryKey(users.Columns...),
+							},
+						},
+					}
+				}(),
+			},
+			wantPlan: &migrate.Plan{
+				Reversible:    true,
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{
+						Cmd:     `ALTER TABLE "test"."users" DROP CONSTRAINT "users_pkey"`,
+						Reverse: `ALTER TABLE "test"."users" ADD PRIMARY KEY ("id")`,
+					},
+				},
+			},
+		},
+		// 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,
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{
+						Cmd:     `ALTER TABLE "test"."users" ADD PRIMARY KEY ("id")`,
+						Reverse: `ALTER TABLE "test"."users" DROP CONSTRAINT "users_pkey"`,
+					},
+				},
+			},
+		},
+		// Modify a primary key.
+		{
+			changes: []schema.Change{
+				func() schema.Change {
+					users := schema.NewTable("users").
+						AddColumns(
+							schema.NewStringColumn("id", "varchar(255)"),
+							schema.NewStringColumn("name", "varchar(255)"),
+						)
+					users.SetPrimaryKey(schema.NewPrimaryKey(users.Columns[:1]...))
+					return &schema.ModifyTable{
+						T: users,
+						Changes: []schema.Change{
+							&schema.ModifyPrimaryKey{
+								From: schema.NewPrimaryKey(users.Columns[:1]...).
+									AddAttrs(&IndexInclude{Columns: users.Columns[1:]}),
+								To: users.PrimaryKey,
+							},
+						},
+					}
+				}(),
+			},
+			wantPlan: &migrate.Plan{
+				Reversible:    true,
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{
+						Cmd:     `ALTER TABLE "users" DROP CONSTRAINT "users_pkey" , ADD PRIMARY KEY ("id")`,
+						Reverse: `ALTER TABLE "users" DROP CONSTRAINT "users_pkey" , ADD PRIMARY KEY ("id") INCLUDE ("name")`,
+					},
+				},
+			},
+		},
 		{
 			changes: []schema.Change{
 				func() schema.Change {
diff --git a/sql/postgres/sqlspec_test.go b/sql/postgres/sqlspec_test.go
index d50866bb..0c0189bb 100644
--- a/sql/postgres/sqlspec_test.go
+++ b/sql/postgres/sqlspec_test.go
@@ -886,7 +886,6 @@ func TestMarshalSpec_IndexInclude(t *testing.T) {
 		},
 	}
 	s.Tables[0].Schema = s
-	s.Tables[0].Schema = s
 	s.Tables[0].Indexes = []*schema.Index{
 		{
 			Name:  "index",
@@ -922,6 +921,78 @@ schema "test" {
 	require.EqualValues(t, expected, string(buf))
 }
 
+func TestMarshalSpec_PrimaryKey(t *testing.T) {
+	s := schema.New("test").
+		AddTables(
+			schema.NewTable("users").
+				AddColumns(
+					schema.NewIntColumn("c", "int"),
+					schema.NewIntColumn("d", "int"),
+				),
+		)
+	s.Tables[0].SetPrimaryKey(
+		schema.NewPrimaryKey(s.Tables[0].Columns[:1]...).
+			AddAttrs(&IndexInclude{Columns: s.Tables[0].Columns[1:]}),
+	)
+	buf, err := MarshalSpec(s, hclState)
+	require.NoError(t, err)
+	const expected = `table "users" {
+  schema = schema.test
+  column "c" {
+    null = false
+    type = int
+  }
+  column "d" {
+    null = false
+    type = int
+  }
+  primary_key {
+    columns = [column.c]
+    include = [column.d]
+  }
+}
+schema "test" {
+}
+`
+	require.EqualValues(t, expected, string(buf))
+}
+
+func TestUnmarshalSpec_PrimaryKey(t *testing.T) {
+	f := `
+schema "s" {}
+table "t" {
+	schema = schema.s
+	column "c" {
+		type = int
+	}
+	column "d" {
+		type = int
+	}
+	primary_key {
+		columns = [
+			column.c,
+		]
+		include = [
+			column.d,
+		]
+	}
+}
+`
+	var s schema.Schema
+	err := EvalHCLBytes([]byte(f), &s, nil)
+	require.NoError(t, err)
+	require.Len(t, s.Tables, 1)
+	require.Len(t, s.Tables[0].Columns, 2)
+	pk := s.Tables[0].PrimaryKey
+	require.NotNil(t, pk)
+	require.Len(t, pk.Parts, 1)
+	require.Len(t, pk.Attrs, 1)
+	var include IndexInclude
+	require.True(t, sqlx.Has(pk.Attrs, &include))
+	require.Len(t, include.Columns, 1)
+	require.Equal(t, "d", include.Columns[0].Name)
+}
+
 func TestMarshalSpec_GeneratedColumn(t *testing.T) {
 	s := schema.New("test").
 		AddTables(
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{