git.iko.soy/reshape
on iko/fix help sign in
reshape/README.md
630 lines 19 kB history raw
1# Reshape
3[![Test status badge](https://github.com/fabianlindfors/Reshape/actions/workflows/test.yaml/badge.svg)](https://github.com/fabianlindfors/reshape/actions/workflows/test.yaml) [![Latest release](https://shields.io/github/v/release/fabianlindfors/reshape?display_name=tag&sort=semver&color=blue)](https://github.com/fabianlindfors/reshape/releases)
5Reshape 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/).
7Designed for Postgres 12 and later.
9*Note: Reshape is **experimental** and should not be used in production. It can (and probably will) break your application.*
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)
19 - [Basics](#basics)
20 - [Tables](#tables)
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)
26 - [Columns](#columns)
27 - [Add column](#add-column)
28 - [Alter column](#alter-column)
29 - [Remove column](#remove-column)
30 - [Indices](#indices)
31 - [Add index](#add-index)
32 - [Remove index](#remove-index)
33 - [Enums](#enums)
34 - [Create enum](#create-enum)
35 - [Remove enum](#remove-enum)
36 - [Custom](#custom)
37- [Commands and options](#commands-and-options)
38 - [`reshape migration start`](#reshape-migration-start)
39 - [`reshape migration complete`](#reshape-migration-complete)
40 - [`reshape migration abort`](#reshape-migration-abort)
41 - [`reshape schema-query`](#reshape-schema-query)
42 - [Connection options](#connection-options)
43- [License](#license)
46## How it works
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.
56## Getting started
58### Installation
60#### Binaries
62Binaries are available for macOS and Linux under [Releases](https://github.com/fabianlindfors/reshape/releases).
64#### Cargo
66Reshape can be installed using [Cargo](https://doc.rust-lang.org/cargo/) (requires Rust 1.58 or later):
68```shell
69cargo install reshape
70```
72#### Docker
74Reshape is available as a Docker image on [Docker Hub](https://hub.docker.com/repository/docker/fabianlindfors/reshape).
76```shell
77docker run -v $(pwd):/usr/share/app fabianlindfors/reshape reshape migration start
78```
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`:
86```toml
87[[actions]]
88type = "create_table"
89name = "users"
90primary_key = ["id"]
92 [[actions.columns]]
93 name = "id"
94 type = "INTEGER"
95 generated = "ALWAYS AS IDENTITY"
97 [[actions.columns]]
98 name = "name"
99 type = "TEXT"
100```
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)
112If 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:
114```python
115# Example for Python
116reshape_schema_query = os.getenv("RESHAPE_SCHEMA_QUERY")
117db.execute(reshape_schema_query)
118```
120### Running your migration
122To create your new `users` table, run:
124```bash
125reshape migration start --complete
126```
128We 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.
130If 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.
132### Using during development
134When 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`.
136## Writing migrations
138### Basics
140Every 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`:
142```toml
143[[actions]]
144type = "create_table"
145name = "customers"
146primary_key = ["id"]
148 [[actions.columns]]
149 name = "id"
150 type = "INTEGER"
151 generated = "ALWAYS AS IDENTITY"
153[[actions]]
154type = "create_table"
155name = "products"
156primary_key = ["sku"]
158 [[actions.columns]]
159 name = "sku"
160 type = "TEXT"
161```
163Every action has a `type`. The supported types are detailed below.
165### Tables
167#### Create table
169The `create_table` action will create a new table with the specified columns, indices and constraints.
171*Example: create a `customers` table with a few columns and a primary key*
173```toml
174[[actions]]
175type = "create_table"
176name = "customers"
177primary_key = ["id"]
179 [[actions.columns]]
180 name = "id"
181 type = "INTEGER"
182 generated = "ALWAYS AS IDENTITY"
184 [[actions.columns]]
185 name = "name"
186 type = "TEXT"
188 # Columns default to nullable
189 nullable = false
191 # default can be any valid SQL value, in this case a string literal
192 default = "'PLACEHOLDER'"
193```
195*Example: create `users` and `items` tables with a foreign key between them*
197```toml
198[[actions]]
199type = "create_table"
200name = "users"
201primary_key = ["id"]
203 [[actions.columns]]
204 name = "id"
205 type = "INTEGER"
206 generated = "ALWAYS AS IDENTITY"
208[[actions]]
209type = "create_table"
210name = "items"
211primary_key = ["id"]
213 [[actions.columns]]
214 name = "id"
215 type = "INTEGER"
216 generated = "ALWAYS AS IDENTITY"
218 [[actions.columns]]
219 name = "user_id"
220 type = "INTEGER"
222 [[actions.foreign_keys]]
223 columns = ["user_id"]
224 referenced_table = "users"
225 referenced_columns = ["id"]
226 on_update = "restrict"
227 on_delete = "restrict"
228```
230#### Rename table
232The `rename_table` action will change the name of an existing table.
234*Example: change name of `users` table to `customers`*
236```toml
237[[actions]]
238type = "rename_table"
239table = "users"
240new_name = "customers"
241```
243#### Remove table
245The `remove_table` action will remove an existing table.
247*Example: remove `users` table*
249```toml
250[[actions]]
251type = "remove_table"
252table = "users"
253```
255#### Add foreign key
257The `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.
259*Example: create foreign key from `items` to `users` table*
261```toml
262[[actions]]
263type = "add_foreign_key"
264table = "items"
266 [actions.foreign_key]
267 columns = ["user_id"]
268 referenced_table = "users"
269 referenced_columns = ["id"]
270 on_update = "restrict"
271 on_delete = "restrict"
272```
274#### Remove foreign key
276The `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.
278*Example: remove foreign key `items_user_id_fkey` from `users` table*
280```toml
281[[actions]]
282type = "remove_foreign_key"
283table = "items"
284foreign_key = "items_user_id_fkey"
285```
287### Columns
289#### Add column
291The `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.
293*Example: add a new column `reference` to table `products`*
295```toml
296[[actions]]
297type = "add_column"
298table = "products"
300 [actions.column]
301 name = "reference"
302 type = "INTEGER"
303 nullable = false
304 default = "10"
305```
307*Example: replace an existing `name` column with two new columns, `first_name` and `last_name`*
309```toml
310[[actions]]
311type = "add_column"
312table = "users"
314# Extract the first name from the existing name column
315up = "(STRING_TO_ARRAY(name, ' '))[1]"
317 [actions.column]
318 name = "first_name"
319 type = "TEXT"
322[[actions]]
323type = "add_column"
324table = "users"
326# Extract the last name from the existing name column
327up = "(STRING_TO_ARRAY(name, ' '))[2]"
329 [actions.column]
330 name = "last_name"
331 type = "TEXT"
334[[actions]]
335type = "remove_column"
336table = "users"
337column = "name"
339# Reconstruct name column by concatenating first and last name
340down = "first_name || ' ' || last_name"
341```
343*Example: extract nested value from unstructured JSON `data` column to new `name` column*
345```toml
346[[actions]]
347type = "add_column"
348table = "users"
350# #>> '{}' converts the JSON string value to TEXT
351up = "data['path']['to']['value'] #>> '{}'"
353 [actions.column]
354 name = "name"
355 type = "TEXT"
356```
359#### Alter column
361The `alter_column` action enables many different changes to an existing column, for example renaming, changing type and changing existing values.
363When 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.
365*Example: rename `last_name` column on `users` table to `family_name`*
367```toml
368[[actions]]
369type = "alter_column"
370table = "users"
371column = "last_name"
373 [actions.changes]
374 name = "family_name"
375```
377*Example: change the type of `reference` column from `INTEGER` to `TEXT`*
379```toml
380[[actions]]
381type = "alter_column"
382table = "users"
383column = "reference"
385up = "CAST(reference AS TEXT)" # Converts from integer value to text
386down = "CAST(reference AS INTEGER)" # Converts from text value to integer
388 [actions.changes]
389 type = "TEXT" # Previous type was 'INTEGER'
390```
392*Example: increment all values of an `index` column by one*
394```toml
395[[actions]]
396type = "alter_column"
397table = "users"
398column = "index"
400up = "index + 1" # Increment for new schema
401down = "index - 1" # Decrement to revert for old schema
403 [actions.changes]
404 name = "index"
405```
407*Example: make `name` column not nullable*
409```toml
410[[actions]]
411type = "alter_column"
412table = "users"
413column = "name"
415# Use "N/A" for any rows that currently have a NULL name
416up = "COALESCE(name, 'N/A')"
418 [actions.changes]
419 nullable = false
420```
422*Example: change default value of `created_at` column to current time*
424```toml
425[[actions]]
426type = "alter_column"
427table = "users"
428column = "created_at"
430 [actions.changes]
431 default = "NOW()"
432```
434#### Remove column
436The `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. The `down` setting must be provided when the removed column is `NOT NULL` or doesn't have a default value.
438Any indices that cover the column will be removed.
440*Example: remove column `name` from table `users`*
442```toml
443[[actions]]
444type = "remove_column"
445table = "users"
446column = "name"
448# Use a default value of "N/A" for the old schema when inserting/updating rows
449down = "'N/A'"
450```
452### Indices
454#### Add index
456The `add_index` action will add a new index to an existing table.
458*Example: create a `users` table with a unique index on the `name` column*
460```toml
461[[actions]]
462type = "create_table"
463name = "users"
464primary_key = "id"
466 [[actions.columns]]
467 name = "id"
468 type = "INTEGER"
469 generated = "ALWAYS AS IDENTITY"
471 [[actions.columns]]
472 name = "name"
473 type = "TEXT"
475[[actions]]
476type = "add_index"
477table = "users"
479 [actions.index]
480 name = "name_idx"
481 columns = ["name"]
483 # Defaults to false
484 unique = true
485```
487*Example: add GIN index to `data` column on `products` table*
489```toml
490[[actions]]
491type = "add_index"
492table = "products"
494 [actions.index]
495 name = "data_idx"
496 columns = ["data"]
498 # One of: btree (default), hash, gist, spgist, gin, brin
499 type = "gin"
500```
502#### Remove index
504The `remove_index` action will remove an existing index. The index won't actually be removed until the migration is completed.
506*Example: remove the `name_idx` index*
508```toml
509[[actions]]
510type = "remove_index"
511index = "name_idx"
512```
514### Enums
516#### Create enum
518The `create_enum` action will create a new [enum type](https://www.postgresql.org/docs/current/datatype-enum.html) with the specified values.
520*Example: add a new `mood` enum type with three possible values*
522```toml
523[[actions]]
524type = "create_enum"
525name = "mood"
526values = ["happy", "ok", "sad"]
527```
529#### Remove enum
531The `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.
533*Example: remove the `mood` enum type*
535```toml
536[[actions]]
537type = "remove_enum"
538enum = "mood"
539```
541### Custom
543The `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.
545There 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.
547- `start`: run when a migration is started using `reshape migration start`
548- `complete`: run when a migration is completed using `reshape migration complete`
549- `abort`: run when a migration is aborted using `reshape migration abort`
551*Example: enable PostGIS and pg_stat_statements extensions*
553```toml
554[[actions]]
555type = "custom"
557start = """
558 CREATE EXTENSION IF NOT EXISTS postgis;
559 CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
560"""
562abort = """
563 DROP EXTENSION IF EXISTS postgis;
564 DROP EXTENSION IF EXISTS pg_stat_statements;
565"""
566```
568## Commands and options
570### `reshape migration start`
572Starts 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`.
574#### Options
576*See also [Connection options](#connection-options)*
578| Option | Default | Description |
579| ------ | ------- | ----------- |
580| `--complete`, `-c` | `false` | Automatically complete migration after applying it. |
581| `--dirs` | `migrations/` | Directories to search for migration files. Multiple directories can be specified using `--dirs dir1 dir2 dir3`. |
583### `reshape migration complete`
585Completes migrations previously started with `reshape migration complete`.
587#### Options
589See [Connection options](#connection-options)
591### `reshape migration abort`
593Aborts any migrations which haven't yet been completed.
595#### Options
597See [Connection options](#connection-options)
599### `reshape schema-query`
601Generates 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`).
603If your application is written in Rust, we recommend using the [Rust helper library](https://github.com/fabianlindfors/reshape-helper/) instead.
605The query should look something like `SET search_path TO migration_1_initial_migration`.
607#### Options
609| Option | Default | Description |
610| ------ | ------- | ----------- |
611| `--dirs` | `migrations/` | Directories to search for migration files. Multiple directories can be specified using `--dirs dir1 dir2 dir3`. |
613### Connection options
615The 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.
617All options can also be set using environment variables instead of flags. If a `.env` file exists, then variables will be automatically loaded from there.
619| Option | Default | Environment variable | Description |
620| ------ | ------- | -------------------- | ----------- |
621| `--url` | | `DB_URL` | URL to your Postgres database |
622| `--host` | `localhost` | `DB_HOST` | Hostname to use when connecting to Postgres |
623| `--port` | `5432` | `DB_PORT` | Port which Postgres is listening on |
624| `--database` | `postgres` | `DB_NAME` | Database name |
625| `--username` | `postgres` | `DB_USERNAME` | Postgres username |
626| `--password` | `postgres` | `DB_PASSWORD` | Postgres password |
628## License
630Reshape is released under the [MIT license](https://choosealicense.com/licenses/mit/).