xataio · andrew-farries · Dec 6, 2024 · Dec 6, 2024 · Dec 6, 2024 · Dec 6, 2024
diff --git a/docs/operations/add_column.mdx b/docs/operations/add_column.mdx
@@ -33,23 +33,49 @@ description: An add column operation creates a new column on an existing table.
 }
 ```
 
+The `up` SQL is used when a row is added to the old version of the table (ie, without the new column). In the new version of the table, the row's value for the new column is determined by the `up` SQL expression. The `up` SQL can be omitted if the new column is nullable or has a `DEFAULT` defined.
+
 Default values are subject to the usual rules for quoting SQL expressions. In particular, string literals should be surrounded with single quotes.
 
 **NOTE:** As a special case, the `up` field can be omitted when adding `smallserial`, `serial` and `bigserial` columns.
 
 ## Examples
 
+### Add multiple columns
+
+Add three new columns to the `products` table. Each column addition is a separate operation:
+
 <ExampleSnippet example="03_add_column.json" language="json" />
 
+### Add column with `up` SQL
+
+Add a new column to the `users` table and define `up` SQL to ensure that the new column is populated with a value when a row is added to the old version of the table:
+
 <ExampleSnippet example="06_add_column_to_sql_table.json" language="json" />
 
+### Add column without `up` SQL
+
+Add a new column to the `reviews` table. There is no need for `up` SQL because the column has a default:
+
 <ExampleSnippet example="17_add_rating_column.json" language="json" />
 
+### Add column with `CHECK` constraint
+
+Add a new column to the `people` table with a `CHECK` constraint defined:
+
 <ExampleSnippet
   example="26_add_column_with_check_constraint.json"
   language="json"
 />
 
+### Add column with comment
+
+Add a new column to the `people` table with a comment defined on the new column:
+
 <ExampleSnippet example="30_add_column_simple_up.json" language="json" />
 
+### Add column with a user defined type
+
+Add a new column to the `fruits` table that has an enum type, defined in an earlier migration:
+
 <ExampleSnippet example="41_add_enum_column.json" language="json" />
diff --git a/docs/operations/alter_column/add_check_constraint.mdx b/docs/operations/alter_column/add_check_constraint.mdx
@@ -20,6 +20,14 @@ description: An add check constraint operation adds a `CHECK` constraint to a co
 }
 ```
 
+The `up` SQL expression is used to migrate values from the column in the old schema version that aren't subject to the constraint to values in the new schema version that are subject to the constraint.
+
 ## Examples
 
+### Add a `CHECK` constraint
+
+Add a `CHECK` constraint to the `title` column in the `posts` table. 
+
+The `up` SQL migrates values to ensure they meet the constraint. The `down` SQL copies values without modification from column in the new schema version to the column in the old schema version:
+
 <ExampleSnippet example="22_add_check_constraint.json" language="json" />
diff --git a/docs/operations/alter_column/add_foreign_key.mdx b/docs/operations/alter_column/add_foreign_key.mdx
@@ -1,6 +1,6 @@
 ---
 title: Add foreign key
-description: Add foreign key operations add a foreign key constraint to a column.
+description: Add a foreign key constraint to a column.
 ---
 
 ## Structure
@@ -24,4 +24,8 @@ description: Add foreign key operations add a foreign key constraint to a column
 
 ## Examples
 
+### Add a foreign key constraint
+
+Add a `FOREIGN KEY` constraint to the `user_id` column in the `posts` table:
+
 <ExampleSnippet example="21_add_foreign_key_constraint.json" language="json" />
diff --git a/docs/operations/alter_column/add_not_null_constraint.mdx b/docs/operations/alter_column/add_not_null_constraint.mdx
@@ -17,6 +17,12 @@ description: Add not null operations add a `NOT NULL` constraint to a column.
 }
 ```
 
+Use `up` to migrate values from the nullable column in the old schema view to the `NOT NULL` column in the new schema version. `down` is used to migrate values in the other direction.
+
 ## Examples
 
+### Add a `NOT NULL` constraint
+
+Add a `NOT NULL` constraint to the `review` column in the `reviews` table.
+
 <ExampleSnippet example="16_set_nullable.json" language="json" />
diff --git a/docs/operations/alter_column/add_unique_constraint.mdx b/docs/operations/alter_column/add_unique_constraint.mdx
@@ -19,6 +19,12 @@ description: Add unique operations add a `UNIQUE` constraint to a column.
 }
 ```
 
+Use the `up` SQL expression to migrate values from the old non-unique column in the old schema to the `UNIQUE` column in the new schema.
+
 ## Examples
 
+### Add a `UNIQUE` constraint
+
+Add a `UNIQUE` constraint to the `review` column in the `reviews` table. The `up` SQL appends a random suffix to ensure uniqueness:
+
 <ExampleSnippet example="15_set_column_unique.json" language="json" />
diff --git a/docs/operations/alter_column/change_comment.mdx b/docs/operations/alter_column/change_comment.mdx
@@ -17,8 +17,18 @@ description: A change comment operation changes the comment on a column.
 }
 ```
 
+The comment is added directly to the column on migration start.
+
 ## Examples
 
+### Alter many column properties
+
+An alter column migration performs many operations, including setting a comment: 
+
 <ExampleSnippet example="35_alter_column_multiple.json" language="json" />
 
+### Remove a comment
+
+To remove a comment from a column set `comment` to `NULL`:
+
 <ExampleSnippet example="36_set_comment_to_null.json" language="json" />
diff --git a/docs/operations/alter_column/change_default.mdx b/docs/operations/alter_column/change_default.mdx
@@ -17,8 +17,20 @@ description: A change default operation changes the default value of a column.
 }
 ```
 
+The `default` expression is subject to the usual SQL quoting rules. In particular, string literals should be surrounded with `''`.
+
+To remove a column default, set the `default` field to `NULL`.
+
 ## Examples
 
+### Make multiple column changes
+
+An alter column migration that makes multiple changes including setting the default:
+
 <ExampleSnippet example="35_alter_column_multiple.json" language="json" />
 
+### Drop a column default
+
+Drop a default by setting the `default` field to `null`.
+
 <ExampleSnippet example="46_alter_column_drop_default.json" language="json" />
diff --git a/docs/operations/alter_column/change_type.mdx b/docs/operations/alter_column/change_type.mdx
@@ -17,6 +17,14 @@ description: A change type operation changes the type of a column.
 }
 ```
 
+Use the `up` SQL expression to do data conversion from the old column type to the new type. In the old schema version, the column will have its old data type; in the new version the column will have its new type.
+
+Use the `down` SQL expression to do data conversion in the other direction; from the new data type back to the old.
+
 ## Examples
 
+### Change column type
+
+Change the type of the `rating` column on the `reviews` table:
+
 <ExampleSnippet example="18_change_column_type.json" language="json" />
diff --git a/docs/operations/alter_column/drop_not_null_constraint.mdx b/docs/operations/alter_column/drop_not_null_constraint.mdx
@@ -19,4 +19,8 @@ description: Drop not null operations drop a `NOT NULL` constraint from a column
 
 ## Examples
 
+### Remove `NOT NULL` from a column
+
+Remove `NOT NULL` from the `title` column in the `posts` table:
+
 <ExampleSnippet example="31_unset_not_null.json" language="json" />
diff --git a/docs/operations/alter_column/rename_column.mdx b/docs/operations/alter_column/rename_column.mdx
@@ -15,6 +15,12 @@ description: A rename column operation renames a column.
 }
 ```
 
+In the new schema version, the column will have its new name. In the old schema version the column still has its old name.
+
 ## Examples
 
+### Rename a column
+
+Rename the `role` column in the `employees` table:
+
 <ExampleSnippet example="13_rename_column.json" language="json" />
diff --git a/docs/operations/create_constraint.mdx b/docs/operations/create_constraint.mdx
@@ -35,13 +35,40 @@ Required fields: `name`, `table`, `type`, `up`, `down`.
 }
 ```
 
+An `up` and `down` SQL expression is required for each column covered by the constraint, and no other column names are permitted. For example, when adding a new constraint covering columns `a` and `b` the `up` and `down` fields should look like:
+
+```json
+{
+  "up": {
+    "a": "up SQL expression for column a",
+    "b": "up SQL expression for column b",
+  },
+  "down": {
+    "a": "down SQL expression for column a",
+    "b": "down SQL expression for column b",
+  }
+}
+```
+
 ## Examples
 
+### Add a `UNIQUE` constraint
+
+Add a multi-column unique constraint on the `tickets` table:
+
 <ExampleSnippet example="44_add_table_unique_constraint.json" language="json" />
 
+### Add a `CHECK` constraint
+
+Add a check constraint on the `sellers_name` and `sellers_zip` fields on the `ticket` table. The `up` SQL expression ensures that pairs of values not meeting the new constraint on the old columns are data migrated to values that meet the new constraint in the new columns:
+
 <ExampleSnippet example="45_add_table_check_constraint.json" language="json" />
 
+### Add a `FOREIGN KEY` constraint
+
+Add a multi-column foreign key constraint to the the `tickets` table. The `up` SQL expressions here don't do any data transformation:
+
 <ExampleSnippet
-  example="46_add_table_foreign_key_constraint.json"
+  example="47_add_table_foreign_key_constraint.json"
   language="json"
 />
diff --git a/docs/operations/create_index.mdx b/docs/operations/create_index.mdx
@@ -12,24 +12,41 @@ description: A create index operation creates a new index on a set of columns.
     "name": "index name",
     "columns": [ "names of columns on which to define the index" ]
     "predicate": "conditional expression for defining a partial index",
+    "unique": true | false,
     "method": "btree"
   }
 }
 ```
 
-The field `method` can be `btree`, `hash`, `gist`, `spgist`, `gin`, `brin`.
-You can also specify storage parameters for the index in `storage_parameters`.
-To create a unique index set `unique` to `true`.
+* The field `method` can be `btree`, `hash`, `gist`, `spgist`, `gin`, `brin`.
+* You can also specify storage parameters for the index in `storage_parameters`.
+* To create a unique index set `unique` to `true`.
 
 ## Examples
 
+### Create a `btree` index
+
+Create a `btree` index on the `name` column in the `fruits` table:
+
 <ExampleSnippet example="10_create_index.json" language="json" />
 
+### Create a partial index
+
+Create a partial index on the `id` column in the `fruits` table:
+
 <ExampleSnippet example="37_create_partial_index.json" language="json" />
 
+### Create an index with storage parameters
+
+Set storage parameters and index method:
+
 <ExampleSnippet
   example="38_create_hash_index_with_fillfactor.json"
   language="json"
 />
 
+### Create a unique index
+
+Create a unique index:
+
 <ExampleSnippet example="42_create_unique_index.json" language="json" />
diff --git a/docs/operations/create_table.mdx b/docs/operations/create_table.mdx
@@ -42,23 +42,59 @@ Default values are subject to the usual rules for quoting SQL expressions. In pa
 
 ## Examples
 
+### Create multiple tables
+
+Create multiple tables. Each table is a separate operation in the migration:
+
 <ExampleSnippet example="01_create_tables.json" language="json" />
 
+### Create one table
+
+Create one table:
+
 <ExampleSnippet example="02_create_another_table.json" language="json" />
 
+### Create one table (2)
+
+Create one table:
+
 <ExampleSnippet example="08_create_fruits_table.json" language="json" />
 
+### Create one table (3)
+
+Create one table:
+
+<ExampleSnippet example="20_create_posts_table.json" language="json" />
+
+### Create a table with a comment 
+
+Create a table with a comment on the table:
+
 <ExampleSnippet example="12_create_employees_table.json" language="json" />
 
+### Create a table with nullable and non-nullable columns
+
+Create a table with a mix of nullable and non-nullable columns:
+
 <ExampleSnippet example="14_add_reviews_table.json" language="json" />
 
+### Create a table with a foreign key
+
+Create a table with a foreign key constraint defined on a column:
+
 <ExampleSnippet example="19_create_orders_table.json" language="json" />
 
-<ExampleSnippet example="20_create_posts_table.json" language="json" />
+### Create a table with a `CHECK` constraint
+
+Create a table with a `CHECK` constraint on one column:
 
 <ExampleSnippet
   example="25_add_table_with_check_constraint.json"
   language="json"
 />
 
+### Create a table with column defaults
+
+Create a table with different `DEFAULT` values:
+
 <ExampleSnippet example="28_different_defaults.json" language="json" />
diff --git a/docs/operations/drop_column.mdx b/docs/operations/drop_column.mdx
@@ -19,8 +19,8 @@ The `down` field above is required in order to backfill the previous version of
 
 ## Examples
 
-### Drop a column with a default value
+### Drop a column
 
-If a new row is inserted against the new schema without a `price` column, the old schema `price` column will be set to `0`.
+Drop a column - if a new row is inserted against the new schema without a `price` column, the old schema `price` column will be set to `0`.
 
 <ExampleSnippet example="09_drop_column.json" language="json" />
diff --git a/docs/operations/drop_constraint.mdx b/docs/operations/drop_constraint.mdx
@@ -26,8 +26,20 @@ Only `CHECK`, `FOREIGN KEY`, and `UNIQUE` constraints can be dropped.
 
 ## Examples
 
+### Drop a `CHECK` constraint:
+
+Drop a `CHECK` constraint:
+
 <ExampleSnippet example="23_drop_check_constraint.json" language="json" />
 
+### Drop a `FOREIGN KEY` constraint:
+
+Drop a `FOREIGN KEY` constraint:
+
 <ExampleSnippet example="24_drop_foreign_key_constraint.json" language="json" />
 
+### Drop a `UNIQUE` constraint:
+
+Drop a `UNIQUE` constraint:
+
 <ExampleSnippet example="27_drop_unique_constraint.json" language="json" />
diff --git a/docs/operations/drop_index.mdx b/docs/operations/drop_index.mdx
@@ -15,4 +15,8 @@ description: A drop index operation drops an index from a table.
 
 ## Examples
 
+### Drop an index
+
+Drop an index defined on the `fruits` table:
+
 <ExampleSnippet example="11_drop_index.json" language="json" />