3[](https://github.com/fabianlindfors/reshape/actions/workflows/test.yaml) [](https://github.com/fabianlindfors/reshape/releases)
5> Also check out [ReshapeDB](https://reshapedb.com), a new database built from the ground up to make zero-downtime schema and data migrations as simple and safe as possible. If you'd like to chat about it, please [reach out](contact@reshapedb.com)!
7Reshape is an easy-to-use, zero-downtime schema migration tool for Postgres. It automatically handles complex migrations that would normally require downtime or manual multi-step changes. During a migration, Reshape ensures both the old and new schema are available at the same time, allowing you to gradually roll out your application. It will also perform all changes without excessive locking, avoiding downtime caused by blocking other queries. For a more thorough introduction to Reshape, check out the [introductory blog post](https://fabianlindfors.se/blog/schema-migrations-in-postgres-using-reshape/).
9Designed for Postgres 12 and later.
11- [How it works](#how-it-works)
12- [Getting started](#getting-started)
13 - [Installation](#installation)
14 - [Creating your first migration](#creating-your-first-migration)
15 - [Preparing your application](#preparing-your-application)
16 - [Running your migration](#running-your-migration)
17 - [Using during development](#using-during-development)
18- [Writing migrations](#writing-migrations)
21 - [Create table](#create-table)
22 - [Rename table](#rename-table)
23 - [Remove table](#remove-table)
24 - [Add foreign key](#add-foreign-key)
25 - [Remove foreign key](#remove-foreign-key)
27 - [Add column](#add-column)
28 - [Alter column](#alter-column)
29 - [Remove column](#remove-column)
31 - [Add index](#add-index)
32 - [Remove index](#remove-index)
34 - [Create enum](#create-enum)
35 - [Remove enum](#remove-enum)
37 - [Complex changes across tables](#complex-changes-across-tables)
38- [Commands and options](#commands-and-options)
39 - [`reshape migration start`](#reshape-migration-start)
40 - [`reshape migration complete`](#reshape-migration-complete)
41 - [`reshape migration abort`](#reshape-migration-abort)
42 - [`reshape schema-query`](#reshape-schema-query)
43 - [Connection options](#connection-options)
48Reshape works by creating views that encapsulate the underlying tables, which your application will interact with. During a migration, Reshape will automatically create a new set of views and set up triggers to translate inserts and updates between the old and new schema. This means that every deployment is a three-phase process:
501. **Start migration** (`reshape migration start`): Sets up views and triggers to ensure both the new and old schema are usable at the same time.
512. **Roll out application**: Your application can be gradually rolled out without downtime. The existing deployment will continue using the old schema whilst the new deployment uses the new schema.
523. **Complete migration** (`reshape migration complete`): Removes the old schema and any intermediate data and triggers.
54If the application deployment fails, you should run `reshape migration abort` which will roll back any changes made by `reshape migration start` without losing data.
62Binaries are available for macOS and Linux under [Releases](https://github.com/fabianlindfors/reshape/releases).
66Reshape can be installed using [Cargo](https://doc.rust-lang.org/cargo/) (requires Rust 1.58 or later):
74Reshape is available as a Docker image on [Docker Hub](https://hub.docker.com/repository/docker/fabianlindfors/reshape).
77docker run -v $(pwd):/usr/share/app fabianlindfors/reshape reshape migration start
80### Creating your first migration
82Each migration should be stored as a separate file in a `migrations/` directory. The files can be in either JSON or TOML format and the name of the file will become the name of your migration. We recommend prefixing every migration with an incrementing number as migrations are sorted by file name.
84Let's create a simple migration to set up a new table `users` with two fields, `id` and `name`. We'll create a file called `migrations/1_create_users_table.toml`:
95 generated = "ALWAYS AS IDENTITY"
102This is the equivalent of running `CREATE TABLE users (id INTEGER GENERATED ALWAYS AS IDENTITY, name TEXT)`.
104### Preparing your application
106Reshape relies on your application using a specific schema. When establishing the connection to Postgres in your application, you need to run a query to select the most recent schema. The simplest way to do this is to use one of the helper libraries:
108- [Rust](https://github.com/fabianlindfors/reshape-helper)
109- [Ruby (and Rails)](https://github.com/fabianlindfors/reshape-ruby)
110- [Python (and Django)](https://github.com/fabianlindfors/reshape-python)
111- [Go](https://github.com/leourbina/reshape-helper)
113If your application is not using one of the languages with an available helper library, you can instead generate the query with the command: `reshape schema-query`. To pass it along to your application, you can for example use an environment variable in your run script: `RESHAPE_SCHEMA_QUERY=$(reshape schema-query)`. Then in your application:
117reshape_schema_query = os.getenv("RESHAPE_SCHEMA_QUERY")
118db.execute(reshape_schema_query)
121### Running your migration
123To create your new `users` table, run:
126reshape migration start --complete
129We use the `--complete` flag to automatically complete the migration. During a production deployment, you should first run `reshape migration start` followed by `reshape migration complete` once your application has been fully rolled out.
131If nothing else is specified, Reshape will try to connect to a Postgres database running on `localhost` using `postgres` as both username and password. See [Connection options](#connection-options) for details on how to change the connection settings.
133### Using during development
135When adding new migrations during development, we recommend running `reshape migration start` but skipping `reshape migration complete`. This way, the new migrations can be iterated on by updating the migration file and running `reshape migration abort` followed by `reshape migration start`.
141Every migration consists of one or more actions. The actions will be run sequentially. Here's an example of a migration with two actions to create two tables, `customers` and `products`:
152 generated = "ALWAYS AS IDENTITY"
164Every action has a `type`. The supported types are detailed below.
170The `create_table` action will create a new table with the specified columns, indices and constraints. You can optionally provide an `up` option to backfill values from an existing table.
172_Example: create a `customers` table with a few columns and a primary key_
183 generated = "ALWAYS AS IDENTITY"
189 # Columns default to nullable
192 # default can be any valid SQL value, in this case a string literal
193 default = "'PLACEHOLDER'"
196_Example: create `users` and `items` tables with a foreign key between them_
207 generated = "ALWAYS AS IDENTITY"
217 generated = "ALWAYS AS IDENTITY"
223 [[actions.foreign_keys]]
224 columns = ["user_id"]
225 referenced_table = "users"
226 referenced_columns = ["id"]
229_Example: create `profiles` table based on existing `users` table_
235primary_key = ["user_id"]
245 # Backfill from `users` table and copy `users.email` to `user_email` column
246 # This will perform an upsert based on the primary key to avoid duplicate rows
249 values = { user_id = "id", user_email = "email" }
254The `rename_table` action will change the name of an existing table.
256_Example: change name of `users` table to `customers`_
262new_name = "customers"
267The `remove_table` action will remove an existing table.
269_Example: remove `users` table_
279The `add_foreign_key` action will add a foreign key between two existing tables. The migration will fail if the existing column values aren't valid references.
281_Example: create foreign key from `items` to `users` table_
285type = "add_foreign_key"
288 [actions.foreign_key]
289 columns = ["user_id"]
290 referenced_table = "users"
291 referenced_columns = ["id"]
294#### Remove foreign key
296The `remove_foreign_key` action will remove an existing foreign key. The foreign key will only be removed once the migration is completed, which means that your new application must continue to adhere to the foreign key constraint.
298_Example: remove foreign key `items_user_id_fkey` from `users` table_
302type = "remove_foreign_key"
304foreign_key = "items_user_id_fkey"
311The `add_column` action will add a new column to an existing table. You can optionally provide an `up` setting. This should be an SQL expression which will be run for all existing rows to backfill the new column. `up` may also reference another table to perform cross-table migrations (see ["Complex changes across tables"](#complex-changes-across-tables)).
313_Example: add a new column `reference` to table `products`_
327_Example: replace an existing `name` column with two new columns, `first_name` and `last_name`_
334# Extract the first name from the existing name column
335up = "(STRING_TO_ARRAY(name, ' '))[1]"
346# Extract the last name from the existing name column
347up = "(STRING_TO_ARRAY(name, ' '))[2]"
355type = "remove_column"
359# Reconstruct name column by concatenating first and last name
360down = "first_name || ' ' || last_name"
363_Example: extract nested value from unstructured JSON `data` column to new `name` column_
370# #>> '{}' converts the JSON string value to TEXT
371up = "data['path']['to']['value'] #>> '{}'"
378_Example: duplicate `email` column from `users` to `profiles` table_
381# `profiles` has `user_id` column which maps to `users.id`
391 # When `users` is updated in the old schema, we write the email value to `profiles`
395 where = "user_id = id"
400The `alter_column` action enables many different changes to an existing column, for example renaming, changing type and changing existing values.
402When performing more complex changes than a rename, `up` and `down` should be provided. These should be SQL expressions which determine how to transform between the new and old version of the column. Inside those expressions, you can reference the current column value by the column name.
404_Example: rename `last_name` column on `users` table to `family_name`_
416_Example: change the type of `reference` column from `INTEGER` to `TEXT`_
424up = "CAST(reference AS TEXT)" # Converts from integer value to text
425down = "CAST(reference AS INTEGER)" # Converts from text value to integer
428 type = "TEXT" # Previous type was 'INTEGER'
431_Example: increment all values of an `index` column by one_
439up = "index + 1" # Increment for new schema
440down = "index - 1" # Decrement to revert for old schema
446_Example: make `name` column not nullable_
454# Use "N/A" for any rows that currently have a NULL name
455up = "COALESCE(name, 'N/A')"
461_Example: change default value of `created_at` column to current time_
475The `remove_column` action will remove an existing column from a table. You can optionally provide a `down` setting. This should be an SQL expression which will be used to determine values for the old schema when inserting or updating rows using the new schema. `down` may also reference another table to perform cross-table migrations (see ["Complex changes across tables"](#complex-changes-across-tables)) . The `down` setting must be provided when the removed column is `NOT NULL` or doesn't have a default value.
477Any indices that cover the column will be removed.
479_Example: remove column `name` from table `users`_
483type = "remove_column"
487# Use a default value of "N/A" for the old schema when inserting/updating rows
491_Example: remove `email` column from `users` table and use column from `profiles` table instead_
495type = "remove_column"
499 # Our application will use the `profiles.email` column instead
500 # For backwards compatibility, we will write back to the removed `email` column whenever `profiles` is changed
503 value = "profiles.email"
504 where = "users.id = profiles.user_id"
511The `add_index` action will add a new index to an existing table.
513_Example: create a `users` table with a unique index on the `name` column_
524 generated = "ALWAYS AS IDENTITY"
542_Example: add GIN index to `data` column on `products` table_
553 # One of: btree (default), hash, gist, spgist, gin, brin
559The `remove_index` action will remove an existing index. The index won't actually be removed until the migration is completed.
561_Example: remove the `name_idx` index_
573The `create_enum` action will create a new [enum type](https://www.postgresql.org/docs/current/datatype-enum.html) with the specified values.
575_Example: add a new `mood` enum type with three possible values_
581values = ["happy", "ok", "sad"]
586The `remove_enum` action will remove an existing [enum type](https://www.postgresql.org/docs/current/datatype-enum.html). Make sure all usages of the enum has been removed before running the migration. The enum will only be removed once the migration is completed.
588_Example: remove the `mood` enum type_
598The `custom` action lets you create a migration which runs custom SQL. It should be used with great care as it provides no guarantees of zero-downtime and will simply run whatever SQL is provided. Use other actions whenever possible as they are explicitly designed for zero downtime.
600There are three optional settings available which all accept SQL queries. All queries need to be idempotent, for example by using `IF NOT EXISTS` wherever available.
602- `start`: run when a migration is started using `reshape migration start`
603- `complete`: run when a migration is completed using `reshape migration complete`
604- `abort`: run when a migration is aborted using `reshape migration abort`
606_Example: enable PostGIS and pg_stat_statements extensions_
613 CREATE EXTENSION IF NOT EXISTS postgis;
614 CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
618 DROP EXTENSION IF EXISTS postgis;
619 DROP EXTENSION IF EXISTS pg_stat_statements;
623### Complex changes across tables
625The `up` and `down` options available when creating tables, adding columns and removing columns can also perform more complex changes that span tables.
627_Example: move `email` column from `users` to `profiles` table_
639 # When `users` is updated in the old schema, we write the email value to `profiles`
642 value = "users.email"
643 where = "profiles.user_id = users.id"
646type = "remove_column"
650 # When `profiles` is changed in the new schema, we write the email address back to the removed column
653 value = "profiles.email"
654 where = "users.id = profiles.user_id"
657_Example: turn a 1:N relationship between `users` and `accounts` into N:M and change the format of the associated `role`_
660# Add `user_account_connections` as a junction table
663name = "user_account_connections"
664primary_key = ["account_id", "user_id"]
674 # `role` is currently stored directly on the `users` table but is part of the relationship
680 # Backfill the new table from `users` and uppercase the `role`
683 values = { user_id = "id", account_id = "account_id", role = "UPPER(account_role)" }
684 where = "user_account_connections.user_id = users.id"
687type = "remove_column"
691 # When `user_account_connections` is updated, we write the `account_id` back to `users`
693 table = "user_account_connections"
694 value = "user_account_connections.account_id"
695 where = "users.id = user_account_connections.user_id"
698type = "remove_column"
700column = "account_role"
702 # When `user_account_connections` is updated, we write the lowercase role back to `users`
704 table = "user_account_connections"
705 value = "LOWER(user_account_connections.role)"
706 where = "users.id = user_account_connections.user_id"
709## Commands and options
711### `reshape migration start`
713Starts a new migration, applying all migrations under `migrations/` that haven't yet been applied. After the command has completed, both the old and new schema will be usable at the same time. When you have rolled out the new version of your application which uses the new schema, you should run `reshape migration complete`.
717_See also [Connection options](#connection-options)_
719| Option | Default | Description |
720| ------------------ | ------------- | --------------------------------------------------------------------------------------------------------------- |
721| `--complete`, `-c` | `false` | Automatically complete migration after applying it. |
722| `--dirs` | `migrations/` | Directories to search for migration files. Multiple directories can be specified using `--dirs dir1 dir2 dir3`. |
724### `reshape migration complete`
726Completes migrations previously started with `reshape migration complete`.
730See [Connection options](#connection-options)
732### `reshape migration abort`
734Aborts any migrations which haven't yet been completed.
738See [Connection options](#connection-options)
740### `reshape schema-query`
742Generates the SQL query you need to run in your application before using the database. This command does not require a database connection. Instead it will generate the query based on the latest migration in the `migrations/` directory (or the directories specified by `--dirs`).
744If your application is written in Rust, we recommend using the [Rust helper library](https://github.com/fabianlindfors/reshape-helper/) instead.
746The query should look something like `SET search_path TO migration_1_initial_migration`.
750| Option | Default | Description |
751| -------- | ------------- | --------------------------------------------------------------------------------------------------------------- |
752| `--dirs` | `migrations/` | Directories to search for migration files. Multiple directories can be specified using `--dirs dir1 dir2 dir3`. |
754### Connection options
756The options below can be used with all commands that communicate with Postgres. Use either a [connection URL](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING) or specify each connection option individually.
758All options can also be set using environment variables instead of flags. If a `.env` file exists, then variables will be automatically loaded from there.
760| Option | Default | Environment variable | Description |
761| ------------ | ----------- | -------------------- | ------------------------------------------- |
762| `--url` | | `DB_URL` | URL to your Postgres database |
763| `--host` | `localhost` | `DB_HOST` | Hostname to use when connecting to Postgres |
764| `--port` | `5432` | `DB_PORT` | Port which Postgres is listening on |
765| `--database` | `postgres` | `DB_NAME` | Database name |
766| `--username` | `postgres` | `DB_USERNAME` | Postgres username |
767| `--password` | `postgres` | `DB_PASSWORD` | Postgres password |
771Reshape is released under the [MIT license](https://choosealicense.com/licenses/mit/).
keysesc to close
- j k
- move down, up
- enter
- open
- esc
- back, or up a level
- 1 2 3 4
- readme, files, commits, refs
- /
- filter
- y
- copy the clone command
- c
- compare the chosen commit with another (or shift-click both)
- ?
- show or hide this list