From 891597b708d6a5bc8ebdcb0a55c7347110a73908 Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 09:40:02 +0000 Subject: [PATCH 01/22] Expand `Add column` docs --- docs/operations/add_column.mdx | 14 ++++++++++++++ 1 file changed, 14 insertions(+) diff --git a/docs/operations/add_column.mdx b/docs/operations/add_column.mdx index 02661c6da..214277048 100644 --- a/docs/operations/add_column.mdx +++ b/docs/operations/add_column.mdx @@ -33,23 +33,37 @@ 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 three new columns to the `products` table. Each column addition is a separate operation: + +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: + +Add a new column to the `reviews` table. There is no need for `up` SQL because the column has a default: + +Add a new column to the `people` table with a `CHECK` constraint defined: + +Add a new column to the `people` table with a comment defined on the new column: + +Add a new column to the `fruits` table that has an enum type, defined in an earlier migration: + From 6ee2d2fe28c8f2093957133c3c12b1c3f35ef8ae Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 09:52:42 +0000 Subject: [PATCH 02/22] Expand `Create constraint` docs --- docs/operations/create_constraint.mdx | 23 ++++++++++++++++++++++- 1 file changed, 22 insertions(+), 1 deletion(-) diff --git a/docs/operations/create_constraint.mdx b/docs/operations/create_constraint.mdx index cc58a42e9..5632f4910 100644 --- a/docs/operations/create_constraint.mdx +++ b/docs/operations/create_constraint.mdx @@ -35,13 +35,34 @@ 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 multi-column unique constraint on the `tickets` table: + +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: + +Add a multi-column foreign key constraint to the the `tickets` table. The `up` SQL expressions here don't do any data transformation: + From 70246e1a4dcbec1d2dad896361427b2849e45976 Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 09:58:03 +0000 Subject: [PATCH 03/22] Expand `Create index` docs --- docs/operations/create_index.mdx | 15 ++++++++++++--- 1 file changed, 12 insertions(+), 3 deletions(-) diff --git a/docs/operations/create_index.mdx b/docs/operations/create_index.mdx index cd977ad62..596c71094 100644 --- a/docs/operations/create_index.mdx +++ b/docs/operations/create_index.mdx @@ -12,24 +12,33 @@ 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 on the `name` column in the `fruits` table: + +Create a partial index on the `id` column in the `fruits` table: + +Set storage parameters and index method: + +Create a unique index: + From b677bce197716f5020fc78800aba0c3f905562b4 Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 10:09:15 +0000 Subject: [PATCH 04/22] Expand `Create table` docs --- docs/operations/create_table.mdx | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) diff --git a/docs/operations/create_table.mdx b/docs/operations/create_table.mdx index 81d082c8f..401becc81 100644 --- a/docs/operations/create_table.mdx +++ b/docs/operations/create_table.mdx @@ -42,23 +42,41 @@ Default values are subject to the usual rules for quoting SQL expressions. In pa ## Examples +Create multiple tables. Each table is a separate operation in the migration: + +Create one table: + +Create one table: + +Create a table with a comment on the table: + +Create a table with a mix of nullable and non-nullable columns: + +Create a table with a foreign key constraint defined on a column: + +Create a table: + +Create a table with a `CHECK` constraint on one column: + +Create a table with different `DEFAULT` values: + From f4ac298db7b29c4ef3ce4e2932fb57b84aefaf49 Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 10:10:39 +0000 Subject: [PATCH 05/22] Expand `Drop constraint` docs --- docs/operations/drop_constraint.mdx | 6 ++++++ 1 file changed, 6 insertions(+) diff --git a/docs/operations/drop_constraint.mdx b/docs/operations/drop_constraint.mdx index 10ed8e40d..d788f2d2b 100644 --- a/docs/operations/drop_constraint.mdx +++ b/docs/operations/drop_constraint.mdx @@ -26,8 +26,14 @@ Only `CHECK`, `FOREIGN KEY`, and `UNIQUE` constraints can be dropped. ## Examples +Drop a `CHECK` constraint: + +Drop a `FOREIGN KEY` constraint: + +Drop a `UNIQUE` constraint: + From a6f1f69fdb78fe2aac806556c0f3558646231a9c Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 10:11:35 +0000 Subject: [PATCH 06/22] Expand `Drop index` docs --- docs/operations/drop_index.mdx | 2 ++ 1 file changed, 2 insertions(+) diff --git a/docs/operations/drop_index.mdx b/docs/operations/drop_index.mdx index 51df11e51..17cefd54b 100644 --- a/docs/operations/drop_index.mdx +++ b/docs/operations/drop_index.mdx @@ -15,4 +15,6 @@ description: A drop index operation drops an index from a table. ## Examples +Drop an index defined on the `fruits` table: + From f5132920c04d6d092f80c87789eeff5328d3ce72 Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 10:15:46 +0000 Subject: [PATCH 07/22] Expand `Drop multi-column constraint` docs --- .../drop_multi_column_constraint.mdx | 21 +++++++++++++++++++ 1 file changed, 21 insertions(+) diff --git a/docs/operations/drop_multi_column_constraint.mdx b/docs/operations/drop_multi_column_constraint.mdx index a93e5c0f2..6b04143df 100644 --- a/docs/operations/drop_multi_column_constraint.mdx +++ b/docs/operations/drop_multi_column_constraint.mdx @@ -24,6 +24,27 @@ Only `CHECK`, `FOREIGN KEY`, and `UNIQUE` constraints can be dropped. } ``` +This operation can also be used to drop single-column constraints and replaces the deprecated (#drop-constraint) operation. + +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 droping a 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", + } +} +``` + +The new versions of the columns will no longer have the constraint, but in the old view of the table the columns will still be covered by the constraint; the `down` expressions should therefore be used to ensure that the combination of values meets the constraint being dropped. + ## Examples +Drop a `CHECK` constraint defined on the `tickets` table. + From 7785fd92b8b0bac76a265c1697a94bb0ac73037b Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 10:17:22 +0000 Subject: [PATCH 08/22] Expand `Drop table` docs --- docs/operations/drop_table.mdx | 4 ++++ 1 file changed, 4 insertions(+) diff --git a/docs/operations/drop_table.mdx b/docs/operations/drop_table.mdx index 5eef06d11..87b63fb1e 100644 --- a/docs/operations/drop_table.mdx +++ b/docs/operations/drop_table.mdx @@ -13,6 +13,10 @@ description: A drop table operation drops a table. } ``` +The table is not visible in the new version of the schema created when the migration is started, but remains visible to the old version of the schema. The table is dropped on migration completion. + ## Examples +Drop the products table: + From 9df6a03960de5b430571d36f350baab7be79e7ed Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 10:18:58 +0000 Subject: [PATCH 09/22] Expand `Raw sql` docs --- docs/operations/raw_sql.mdx | 4 ++++ 1 file changed, 4 insertions(+) diff --git a/docs/operations/raw_sql.mdx b/docs/operations/raw_sql.mdx index 811deb153..8f5f5de25 100644 --- a/docs/operations/raw_sql.mdx +++ b/docs/operations/raw_sql.mdx @@ -38,6 +38,10 @@ The `onComplete` flag will make this operation run the `up` expression on the co ## Examples +A raw SQL migration to create a table: + +A raw SQL migration run on migration completion rather than start. + From 4465d07d55de5f8b610ec938efb85992a971f2ae Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 10:20:24 +0000 Subject: [PATCH 10/22] Expand `Rename constraint` docs --- docs/operations/rename_constraint.mdx | 4 ++++ 1 file changed, 4 insertions(+) diff --git a/docs/operations/rename_constraint.mdx b/docs/operations/rename_constraint.mdx index 94f7d5af3..05eb49aed 100644 --- a/docs/operations/rename_constraint.mdx +++ b/docs/operations/rename_constraint.mdx @@ -15,6 +15,10 @@ description: A rename constraint operation renames a constraint. } ``` +The constraint is renamed on migration completion; it retains its old name during the active migration period. + ## Examples +Rename a `CHECK` constraint: + From 839793c2e807380a1d6ebee675fcdbb62d8c5d90 Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 10:22:17 +0000 Subject: [PATCH 11/22] Expand `Rename table` docs --- docs/operations/rename_table.mdx | 6 ++++++ 1 file changed, 6 insertions(+) diff --git a/docs/operations/rename_table.mdx b/docs/operations/rename_table.mdx index b22019be3..dce07efea 100644 --- a/docs/operations/rename_table.mdx +++ b/docs/operations/rename_table.mdx @@ -14,6 +14,12 @@ description: A rename table operation renames a table. } ``` +The table is accessible by its old name in the old version of the schema, and by its new name in the new version of the schema. + +The table itself is renamed on migration completion. + ## Examples +Rename the customers table: + From eab82df55cfd772aca6c0012839ad375018346b1 Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 10:23:16 +0000 Subject: [PATCH 12/22] Expand `Drop column` docs --- docs/operations/drop_column.mdx | 4 +--- 1 file changed, 1 insertion(+), 3 deletions(-) diff --git a/docs/operations/drop_column.mdx b/docs/operations/drop_column.mdx index e555c4b1b..2bad2e69b 100644 --- a/docs/operations/drop_column.mdx +++ b/docs/operations/drop_column.mdx @@ -19,8 +19,6 @@ The `down` field above is required in order to backfill the previous version of ## Examples -### Drop a column with a default value - -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 with a default value - if a new row is inserted against the new schema without a `price` column, the old schema `price` column will be set to `0`. From 0d15c70b0bf544180b0c8aa63b105097c22f638b Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 11:30:06 +0000 Subject: [PATCH 13/22] Expand `Add check constraint` docs --- docs/operations/alter_column/add_check_constraint.mdx | 6 ++++++ 1 file changed, 6 insertions(+) diff --git a/docs/operations/alter_column/add_check_constraint.mdx b/docs/operations/alter_column/add_check_constraint.mdx index 783ae4658..f317b0db4 100644 --- a/docs/operations/alter_column/add_check_constraint.mdx +++ b/docs/operations/alter_column/add_check_constraint.mdx @@ -20,6 +20,12 @@ 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 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: + From a0de61256d9339c10c3fe68f8e7c88f5722ac46f Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 11:31:45 +0000 Subject: [PATCH 14/22] Expand `Add foreign key` docs --- docs/operations/alter_column/add_foreign_key.mdx | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) diff --git a/docs/operations/alter_column/add_foreign_key.mdx b/docs/operations/alter_column/add_foreign_key.mdx index 5705b1dce..df7c1cf4d 100644 --- 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,6 @@ description: Add foreign key operations add a foreign key constraint to a column ## Examples +Add a `FOREIGN KEY` constraint to the `user_id` column in the `posts` table: + From 2a03539f711d754e5530af72d37039cac5a58b88 Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 11:33:29 +0000 Subject: [PATCH 15/22] Expand `Add not null constraint` docs --- docs/operations/alter_column/add_not_null_constraint.mdx | 4 ++++ 1 file changed, 4 insertions(+) diff --git a/docs/operations/alter_column/add_not_null_constraint.mdx b/docs/operations/alter_column/add_not_null_constraint.mdx index 6eedf73a4..7774edc65 100644 --- a/docs/operations/alter_column/add_not_null_constraint.mdx +++ b/docs/operations/alter_column/add_not_null_constraint.mdx @@ -17,6 +17,10 @@ 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 to the `review` column in the `reviews` table. + From 4d1743e8fca2b9eca29c0cb5b3f3bb604be71561 Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 11:35:43 +0000 Subject: [PATCH 16/22] Expand `Add unique constraint` docs --- docs/operations/alter_column/add_unique_constraint.mdx | 4 ++++ 1 file changed, 4 insertions(+) diff --git a/docs/operations/alter_column/add_unique_constraint.mdx b/docs/operations/alter_column/add_unique_constraint.mdx index 51b40a693..87b11c3e9 100644 --- a/docs/operations/alter_column/add_unique_constraint.mdx +++ b/docs/operations/alter_column/add_unique_constraint.mdx @@ -19,6 +19,10 @@ 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 to the `review` column in the `reviews` table. The `up` SQL appends a random suffix to ensure uniqueness: + From e574b14a6376e8e764c6e684991306943e1b43eb Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 11:37:51 +0000 Subject: [PATCH 17/22] Expand `Change comment` docs --- docs/operations/alter_column/change_comment.mdx | 6 ++++++ 1 file changed, 6 insertions(+) diff --git a/docs/operations/alter_column/change_comment.mdx b/docs/operations/alter_column/change_comment.mdx index d268f84f5..c4de859b8 100644 --- a/docs/operations/alter_column/change_comment.mdx +++ b/docs/operations/alter_column/change_comment.mdx @@ -17,8 +17,14 @@ description: A change comment operation changes the comment on a column. } ``` +The comment is added directly to the column on migration start. + ## Examples +An alter column migration performs many operations, including setting a comment: + +To remove a comment from a column set `comment` to `NULL`: + From 3f37af7246b170424a1ff5f64a19a041e5f1153d Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 11:40:15 +0000 Subject: [PATCH 18/22] Expand `Change default` docs --- docs/operations/alter_column/change_default.mdx | 8 ++++++++ 1 file changed, 8 insertions(+) diff --git a/docs/operations/alter_column/change_default.mdx b/docs/operations/alter_column/change_default.mdx index 92478dd34..cd9c9ac48 100644 --- a/docs/operations/alter_column/change_default.mdx +++ b/docs/operations/alter_column/change_default.mdx @@ -17,8 +17,16 @@ 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 +An alter column migration that makes multiple changes including setting the default: + +Drop a default by setting the `default` field to `null`. + From 747418738f5189694fb3b0cae0ff657d50a8ec8c Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 11:43:06 +0000 Subject: [PATCH 19/22] Expand `Change type` docs --- docs/operations/alter_column/change_type.mdx | 6 ++++++ 1 file changed, 6 insertions(+) diff --git a/docs/operations/alter_column/change_type.mdx b/docs/operations/alter_column/change_type.mdx index ab14eaf48..80672a8bb 100644 --- a/docs/operations/alter_column/change_type.mdx +++ b/docs/operations/alter_column/change_type.mdx @@ -17,6 +17,12 @@ 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 the type of the `rating` column on the `reviews` table: + From faad73da5681c3859cbbc205f1167ee17affb8ee Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 11:44:30 +0000 Subject: [PATCH 20/22] Expand `Drop not null constraint` docs --- docs/operations/alter_column/drop_not_null_constraint.mdx | 2 ++ 1 file changed, 2 insertions(+) diff --git a/docs/operations/alter_column/drop_not_null_constraint.mdx b/docs/operations/alter_column/drop_not_null_constraint.mdx index 6cc471cfb..df5f034a7 100644 --- a/docs/operations/alter_column/drop_not_null_constraint.mdx +++ b/docs/operations/alter_column/drop_not_null_constraint.mdx @@ -19,4 +19,6 @@ description: Drop not null operations drop a `NOT NULL` constraint from a column ## Examples +Remove `NOT NULL` from the `title` column in the `posts` table: + From 6d2d5e33b8fbc7bf60b50f4c1c1155541f4e8be6 Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 11:45:45 +0000 Subject: [PATCH 21/22] Expand `Rename column` docs --- docs/operations/alter_column/rename_column.mdx | 4 ++++ 1 file changed, 4 insertions(+) diff --git a/docs/operations/alter_column/rename_column.mdx b/docs/operations/alter_column/rename_column.mdx index cf353be35..d4244ad8b 100644 --- a/docs/operations/alter_column/rename_column.mdx +++ b/docs/operations/alter_column/rename_column.mdx @@ -15,6 +15,10 @@ 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 the `role` column in the `employees` table: + From 4eff0621b91164518269531c565be4f4faa8d059 Mon Sep 17 00:00:00 2001 From: Andrew Farries Date: Fri, 6 Dec 2024 13:18:23 +0000 Subject: [PATCH 22/22] Add headers to examples --- docs/operations/add_column.mdx | 12 ++++++++++ .../alter_column/add_check_constraint.mdx | 2 ++ .../alter_column/add_foreign_key.mdx | 2 ++ .../alter_column/add_not_null_constraint.mdx | 2 ++ .../alter_column/add_unique_constraint.mdx | 2 ++ .../alter_column/change_comment.mdx | 4 ++++ .../alter_column/change_default.mdx | 4 ++++ docs/operations/alter_column/change_type.mdx | 2 ++ .../alter_column/drop_not_null_constraint.mdx | 2 ++ .../operations/alter_column/rename_column.mdx | 2 ++ docs/operations/create_constraint.mdx | 6 +++++ docs/operations/create_index.mdx | 8 +++++++ docs/operations/create_table.mdx | 24 ++++++++++++++++--- docs/operations/drop_column.mdx | 4 +++- docs/operations/drop_constraint.mdx | 6 +++++ docs/operations/drop_index.mdx | 2 ++ .../drop_multi_column_constraint.mdx | 2 ++ docs/operations/drop_table.mdx | 2 ++ docs/operations/raw_sql.mdx | 4 ++++ docs/operations/rename_constraint.mdx | 2 ++ docs/operations/rename_table.mdx | 2 ++ docs/operations/set_replica_identity.mdx | 2 ++ 22 files changed, 94 insertions(+), 4 deletions(-) diff --git a/docs/operations/add_column.mdx b/docs/operations/add_column.mdx index 214277048..5eeb8b4be 100644 --- a/docs/operations/add_column.mdx +++ b/docs/operations/add_column.mdx @@ -41,18 +41,26 @@ Default values are subject to the usual rules for quoting SQL expressions. In pa ## Examples +### Add multiple columns + Add three new columns to the `products` table. Each column addition is a separate operation: +### 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: +### 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: +### Add column with `CHECK` constraint + Add a new column to the `people` table with a `CHECK` constraint defined: +### Add column with comment + Add a new column to the `people` table with a comment defined on the new column: +### 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: diff --git a/docs/operations/alter_column/add_check_constraint.mdx b/docs/operations/alter_column/add_check_constraint.mdx index f317b0db4..4391be1f1 100644 --- a/docs/operations/alter_column/add_check_constraint.mdx +++ b/docs/operations/alter_column/add_check_constraint.mdx @@ -24,6 +24,8 @@ The `up` SQL expression is used to migrate values from the column in the old sch ## 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: diff --git a/docs/operations/alter_column/add_foreign_key.mdx b/docs/operations/alter_column/add_foreign_key.mdx index df7c1cf4d..d919802b6 100644 --- a/docs/operations/alter_column/add_foreign_key.mdx +++ b/docs/operations/alter_column/add_foreign_key.mdx @@ -24,6 +24,8 @@ description: 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: diff --git a/docs/operations/alter_column/add_not_null_constraint.mdx b/docs/operations/alter_column/add_not_null_constraint.mdx index 7774edc65..ec012850d 100644 --- a/docs/operations/alter_column/add_not_null_constraint.mdx +++ b/docs/operations/alter_column/add_not_null_constraint.mdx @@ -21,6 +21,8 @@ Use `up` to migrate values from the nullable column in the old schema view to th ## Examples +### Add a `NOT NULL` constraint + Add a `NOT NULL` constraint to the `review` column in the `reviews` table. diff --git a/docs/operations/alter_column/add_unique_constraint.mdx b/docs/operations/alter_column/add_unique_constraint.mdx index 87b11c3e9..afbaa4e17 100644 --- a/docs/operations/alter_column/add_unique_constraint.mdx +++ b/docs/operations/alter_column/add_unique_constraint.mdx @@ -23,6 +23,8 @@ Use the `up` SQL expression to migrate values from the old non-unique column in ## 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: diff --git a/docs/operations/alter_column/change_comment.mdx b/docs/operations/alter_column/change_comment.mdx index c4de859b8..b713435b9 100644 --- a/docs/operations/alter_column/change_comment.mdx +++ b/docs/operations/alter_column/change_comment.mdx @@ -21,10 +21,14 @@ 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: +### Remove a comment + To remove a comment from a column set `comment` to `NULL`: diff --git a/docs/operations/alter_column/change_default.mdx b/docs/operations/alter_column/change_default.mdx index cd9c9ac48..0502a2634 100644 --- a/docs/operations/alter_column/change_default.mdx +++ b/docs/operations/alter_column/change_default.mdx @@ -23,10 +23,14 @@ 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: +### Drop a column default + Drop a default by setting the `default` field to `null`. diff --git a/docs/operations/alter_column/change_type.mdx b/docs/operations/alter_column/change_type.mdx index 80672a8bb..379e6daf1 100644 --- a/docs/operations/alter_column/change_type.mdx +++ b/docs/operations/alter_column/change_type.mdx @@ -23,6 +23,8 @@ Use the `down` SQL expression to do data conversion in the other direction; from ## Examples +### Change column type + Change the type of the `rating` column on the `reviews` table: diff --git a/docs/operations/alter_column/drop_not_null_constraint.mdx b/docs/operations/alter_column/drop_not_null_constraint.mdx index df5f034a7..9fabd8ec3 100644 --- a/docs/operations/alter_column/drop_not_null_constraint.mdx +++ b/docs/operations/alter_column/drop_not_null_constraint.mdx @@ -19,6 +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: diff --git a/docs/operations/alter_column/rename_column.mdx b/docs/operations/alter_column/rename_column.mdx index d4244ad8b..1774cdb60 100644 --- a/docs/operations/alter_column/rename_column.mdx +++ b/docs/operations/alter_column/rename_column.mdx @@ -19,6 +19,8 @@ In the new schema version, the column will have its new name. In the old schema ## Examples +### Rename a column + Rename the `role` column in the `employees` table: diff --git a/docs/operations/create_constraint.mdx b/docs/operations/create_constraint.mdx index 5632f4910..be85c55aa 100644 --- a/docs/operations/create_constraint.mdx +++ b/docs/operations/create_constraint.mdx @@ -52,14 +52,20 @@ An `up` and `down` SQL expression is required for each column covered by the con ## Examples +### Add a `UNIQUE` constraint + Add a multi-column unique constraint on the `tickets` table: +### 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: +### 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: +### Create a partial index + Create a partial index on the `id` column in the `fruits` table: +### Create an index with storage parameters + Set storage parameters and index method: +### Create a unique index + Create a unique index: diff --git a/docs/operations/create_table.mdx b/docs/operations/create_table.mdx index 401becc81..dd559c985 100644 --- a/docs/operations/create_table.mdx +++ b/docs/operations/create_table.mdx @@ -42,33 +42,49 @@ 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: +### Create one table + Create one table: +### Create one table (2) + Create one table: +### Create one table (3) + +Create one table: + + + +### Create a table with a comment + Create a table with a comment on the table: +### Create a table with nullable and non-nullable columns + Create a table with a mix of nullable and non-nullable columns: +### Create a table with a foreign key + Create a table with a foreign key constraint defined on a column: -Create a table: - - +### Create a table with a `CHECK` constraint Create a table with a `CHECK` constraint on one column: @@ -77,6 +93,8 @@ Create a table with a `CHECK` constraint on one column: language="json" /> +### Create a table with column defaults + Create a table with different `DEFAULT` values: diff --git a/docs/operations/drop_column.mdx b/docs/operations/drop_column.mdx index 2bad2e69b..01410f9bb 100644 --- a/docs/operations/drop_column.mdx +++ b/docs/operations/drop_column.mdx @@ -19,6 +19,8 @@ The `down` field above is required in order to backfill the previous version of ## Examples -Drop a column with a default value - 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 + +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`. diff --git a/docs/operations/drop_constraint.mdx b/docs/operations/drop_constraint.mdx index d788f2d2b..64f94e1ce 100644 --- a/docs/operations/drop_constraint.mdx +++ b/docs/operations/drop_constraint.mdx @@ -26,14 +26,20 @@ Only `CHECK`, `FOREIGN KEY`, and `UNIQUE` constraints can be dropped. ## Examples +### Drop a `CHECK` constraint: + Drop a `CHECK` constraint: +### Drop a `FOREIGN KEY` constraint: + Drop a `FOREIGN KEY` constraint: +### Drop a `UNIQUE` constraint: + Drop a `UNIQUE` constraint: diff --git a/docs/operations/drop_index.mdx b/docs/operations/drop_index.mdx index 17cefd54b..17f7f661a 100644 --- a/docs/operations/drop_index.mdx +++ b/docs/operations/drop_index.mdx @@ -15,6 +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: diff --git a/docs/operations/drop_multi_column_constraint.mdx b/docs/operations/drop_multi_column_constraint.mdx index 6b04143df..fca3bdc8d 100644 --- a/docs/operations/drop_multi_column_constraint.mdx +++ b/docs/operations/drop_multi_column_constraint.mdx @@ -45,6 +45,8 @@ The new versions of the columns will no longer have the constraint, but in the o ## Examples +### Drop a `CHECK` constraint + Drop a `CHECK` constraint defined on the `tickets` table. diff --git a/docs/operations/drop_table.mdx b/docs/operations/drop_table.mdx index 87b63fb1e..c47756d0d 100644 --- a/docs/operations/drop_table.mdx +++ b/docs/operations/drop_table.mdx @@ -17,6 +17,8 @@ The table is not visible in the new version of the schema created when the migra ## Examples +### Drop a table + Drop the products table: diff --git a/docs/operations/raw_sql.mdx b/docs/operations/raw_sql.mdx index 8f5f5de25..2630abe21 100644 --- a/docs/operations/raw_sql.mdx +++ b/docs/operations/raw_sql.mdx @@ -38,10 +38,14 @@ The `onComplete` flag will make this operation run the `up` expression on the co ## Examples +### Create a table with a raw SQL migration + A raw SQL migration to create a table: +### Run a SQL migration on migration complete + A raw SQL migration run on migration completion rather than start. diff --git a/docs/operations/rename_constraint.mdx b/docs/operations/rename_constraint.mdx index 05eb49aed..d1ca409f1 100644 --- a/docs/operations/rename_constraint.mdx +++ b/docs/operations/rename_constraint.mdx @@ -19,6 +19,8 @@ The constraint is renamed on migration completion; it retains its old name durin ## Examples +### Rename a `CHECK` constraint + Rename a `CHECK` constraint: diff --git a/docs/operations/rename_table.mdx b/docs/operations/rename_table.mdx index dce07efea..cbef9612d 100644 --- a/docs/operations/rename_table.mdx +++ b/docs/operations/rename_table.mdx @@ -20,6 +20,8 @@ The table itself is renamed on migration completion. ## Examples +### Rename a table + Rename the customers table: diff --git a/docs/operations/set_replica_identity.mdx b/docs/operations/set_replica_identity.mdx index 20b3fdac2..6a4b40937 100644 --- a/docs/operations/set_replica_identity.mdx +++ b/docs/operations/set_replica_identity.mdx @@ -31,4 +31,6 @@ description: A set replica identity operation sets the replica identity for a ta ## Examples +### Set replica identity +