# Ridgepole Ridgepole is a tool to manage DB schema. It defines DB schema using [Rails DSL](http://guides.rubyonrails.org/migrations.html#types-of-schema-dumps), and updates DB schema according to DSL. (like Chef/Puppet) [![Gem Version](https://badge.fury.io/rb/ridgepole.svg)](http://badge.fury.io/rb/ridgepole) [![Build Status](https://github.com/winebarrel/ridgepole/workflows/test/badge.svg?branch=0.9)](https://github.com/winebarrel/ridgepole/actions) [![Coverage Status](https://coveralls.io/repos/github/winebarrel/ridgepole/badge.svg?branch=0.9)](https://coveralls.io/github/winebarrel/ridgepole?branch=0.9)
ChangeLog * `>= 0.4.8` * `activerecord-mysql-unsigned` is now optional. Please pass `--enable-mysql-unsigned` after you install [activerecord-mysql-unsigned](https://github.com/waka/activerecord-mysql-unsigned) if you want to use. * Please pass `--enable-foreigner` after you install [foreigner](https://github.com/matthuhiggins/foreigner) if you want to use the foreign key. * `>= 0.4.11` * Add `--enable-mysql-pkdump` option. * `>= 0.4.12` * Fix `activerecord-mysql-unsigned` version: `~> 0.2.0` * `>= 0.5.0` * Fix `activerecord-mysql-unsigned` version: `~> 0.3.1` * `>= 0.5.1` * Add `--enable-migration-comments` option ([migration_comments](https://github.com/pinnymz/migration_comments) is required) * Fix rails version `< 4.2.0` * `>= 0.5.2` * Add `--enable-mysql-awesome` option ([activerecord-mysql-awesome](https://github.com/kamipo/activerecord-mysql-awesome) is required `>= 0.0.3`) * It is not possible to enable both `--enable-mysql-awesome` and `--enable-migration-comments`, `--enable-mysql-awesome` and `--enable-mysql-unsigned`, `--enable-mysql-awesome` and `--enable-mysql-pkdump` * Fix foreigner version `<= 1.7.1` * `>= 0.6.0` * Fix rails version `~> 4.2.1` * Disable following libraries support: * activerecord-mysql-unsigned * migration_comments * foreigner * Disable sqlite support * Add PostgreSQL test * Remove `--mysql-awesome-unsigned-pk` option * `>= 0.6.1` * Support [PostgreSQL columns](https://github.com/winebarrel/rails/blob/v4.2.1/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L79) * `>= 0.6.3` * Fix `default` option ([pull#48](https://github.com/winebarrel/ridgepole/pull/48)) * Add `--enable-migration-comments` option ([pull#50](https://github.com/winebarrel/ridgepole/pull/50)) * Disable `rename_table_indexes` * `>= 0.6.4` * Execute sql using external script ([pull#56](https://github.com/winebarrel/ridgepole/pull/56)) * Add `--mysql-use-alter` option * Add `--alter-extra` option * Add `--dump-with-default-fk-name` option * Support `t.index` ([pull#64](https://github.com/winebarrel/ridgepole/pull/64)) * Remove migration_comments * Fix foreign key apply order * `>= 0.6.5` * Fix rails version `'>= 4.2', '< 6'` * Support new types ([pull#84](https://github.com/winebarrel/ridgepole/pull/84)) * Support `default: -> { ... }` ([pull#85](https://github.com/winebarrel/ridgepole/pull/85)) * Support DDL Comment (Rails5 only) * Output schema diff when pass `--verbose` * Support composite primary key (Rails5 only / [pull#97](https://github.com/winebarrel/ridgepole/pull/97)) * `>= 0.6.6` * Use `t.column` for migration ([pull#114](https://github.com/winebarrel/ridgepole/pull/114)) * Support DATABASE_URL format ([pull#118](https://github.com/winebarrel/ridgepole/pull/118)) * Add Ruby2.4 CI ([pull#119](https://github.com/winebarrel/ridgepole/pull/119)) * `>= 0.7.0` * Remove Rails 4.x support * Add Rails 5.1 support * Remove `--enable-mysql-awesome` option * Add `--skip-drop-table` option * Support foreign key without name * Support MySQL JSON Type and Generated Columns * Add `--mysql-change-table-options` option * Pass config from env * Fix change fk order * Add `--check-relation-type` option * Add `--skip-column-comment-change` option * Add `--default-bigint-limit` option * Add `--ignore-table-comment` option * `>= 0.7.1` * Remove `--reverse` option * Add `--allow-pk-change` option * Add `--create-table-with-index` option * Add `--mysql-dump-auto-increment` option (`rails >= 5.1`) * `>= 0.7.2` * Support Rails 5.2 * `>= 0.7.3` * Add `--mysql-change-table-comment option` ([pull#166](https://github.com/winebarrel/ridgepole/pull/166)) * Refactoring with RuboCop * Support primary key adding/dropping ([issue#246](https://github.com/winebarrel/ridgepole/issues/246)) * `>= 0.7.4` * Fix `add_foreign_key` options ([issue#250](https://github.com/winebarrel/ridgepole/issues/250)) * `>= 0.7.5` * Fix polymorphic options ([pull#263](https://github.com/winebarrel/ridgepole/pull/263)) * Fix `--mysql-use-alter` option ([pull#246](https://github.com/winebarrel/ridgepole/pull/264)) * Fix Database URI parsing ([pull#265](https://github.com/winebarrel/ridgepole/pull/265)) * `>= 0.7.6` * Fix database url check ([pull#266](https://github.com/winebarrel/ridgepole/pull/266)) * Add ignore option ([pull#267](https://github.com/winebarrel/ridgepole/pull/267)) * `>= 0.7.7` * Support URI query string ([pull#273](https://github.com/winebarrel/ridgepole/pull/273)) * `>= 0.7.8` * Fix for `add_foreign_key(..., column: ,,,)` ([pull#278](https://github.com/winebarrel/ridgepole/pull/278)) * `>= 0.8.0` * Support Rails 6.0 * `>= 0.8.1` * Drop tables in an order considering foreign key constraints ([pull#284](https://github.com/winebarrel/ridgepole/pull/284)) * `>= 0.8.2` * Support `postgres://` schema ([pull#285](https://github.com/winebarrel/ridgepole/pull/285)) * `>= 0.8.3` * Fix "topological sort failed" error ([pull#287](https://github.com/winebarrel/ridgepole/pull/287)) * `>= 0.8.4` * Display a warning if an InnoDB table doesn't have any indexes on a column where it has a foreign key ([pull#290](https://github.com/winebarrel/ridgepole/pull/290)) * `>= 0.8.5` * Improve warning message on table options ([pull#291](https://github.com/winebarrel/ridgepole/pull/291)) * `>= 0.8.6` * Support multiple databases feature ([pull#297](https://github.com/winebarrel/ridgepole/pull/297)) * `>= 0.8.7` * Support `require_relative` ([pull#298](https://github.com/winebarrel/ridgepole/pull/298)) * `>= 0.8.8` * Fix keyword arguments warnings in Ruby 2.7 ([pull#303](https://github.com/winebarrel/ridgepole/pull/303)) * `>= 0.8.9` * Fix unexpected differences on text types and blob types on Rails 6 ([pull#306](https://github.com/winebarrel/ridgepole/pull/306)) * Fix unexpected warning when a foreign key is added on the primary key ([pull#307](https://github.com/winebarrel/ridgepole/pull/307)) * `>= 0.8.10` * Raise an error if an InnoDB column has a foreign key but no index ([pull#310](https://github.com/winebarrel/ridgepole/pull/310)) * `>= 0.8.11` * Fix FK index check support multiple PK ([pull#315](https://github.com/winebarrel/ridgepole/pull/315)) * Support t.reference() foreign_key option ([pull#316](https://github.com/winebarrel/ridgepole/pull/316)) * `>= 0.8.12` * Pluralize column specified by `references` ([pull#317](https://github.com/winebarrel/ridgepole/pull/317)) * `>= 0.8.13` * Support `serial` and `bigserial` column types ([pull#321](https://github.com/winebarrel/ridgepole/pull/321)) * `>= 0.9.0` * Remove `--mysql-use-alter` option ([pull#330](https://github.com/winebarrel/ridgepole/pull/330)) * Add `--table-hash-options` option ([pull#331](https://github.com/winebarrel/ridgepole/pull/331)) * Support Rails 6.1 ([pull#323](https://github.com/winebarrel/ridgepole/pull/323)) * Disable Rails 5.0 support ([pull#335](https://github.com/winebarrel/ridgepole/pull/335)) * Fix PK AUTO_INCREMENT change bug ([pull#334](https://github.com/winebarrel/ridgepole/pull/334)) * `>= 0.9.1` * Support `t.foreign_key` ([pull#348](https://github.com/winebarrel/ridgepole/pull/348)) * `>= 0.9.2` * Support `t.column index option` ([pull#353](https://github.com/winebarrel/ridgepole/pull/353)) * `>= 0.9.3` * Fix `limit` option for `t.integer` ([pull#354](https://github.com/winebarrel/ridgepole/pull/354)) * `>= 0.9.4` * Fix `--alter-extra` option for unique index ([pull#356](https://github.com/winebarrel/ridgepole/pull/356)) * `>= 0.9.5` * Call `super` in `disable_table_options.rb` ([pull#357](https://github.com/winebarrel/ridgepole/pull/357)) * `>= 0.9.6` * Fix malformed error ([pull#362](https://github.com/winebarrel/ridgepole/pull/362))
**Notice** ActiveRecord 6.1 is supported in ridgepole v0.9, but the ActiveRecord dump has been changed, so there is a difference between ActiveRecord 5.x/6.0 format. **If you use ActiveRecord 6.1, please modify Schemafile format**. cf. https://github.com/winebarrel/ridgepole/pull/323 ## Installation Add this line to your application's Gemfile: gem 'ridgepole' And then execute: $ bundle Or install it yourself as: $ gem install ridgepole ## Help ``` Usage: ridgepole [options] -c, --config CONF_OR_FILE -E, --env ENVIRONMENT -s, --spec-name SPEC_NAME -a, --apply -m, --merge -f, --file SCHEMAFILE --dry-run --table-options OPTIONS --table-hash-options OPTIONS --alter-extra ALTER_SPEC --external-script SCRIPT --bulk-change --default-bool-limit LIMIT --default-int-limit LIMIT --default-bigint-limit LIMIT --default-float-limit LIMIT --default-string-limit LIMIT --default-text-limit LIMIT --default-binary-limit LIMIT --pre-query QUERY --post-query QUERY -e, --export --split --split-with-dir -d, --diff DSL1 DSL2 --with-apply -o, --output SCHEMAFILE -t, --tables TABLES --ignore-tables REGEX_LIST --dump-without-table-options --dump-with-default-fk-name --index-removed-drop-column --skip-drop-table --mysql-change-table-options --mysql-change-table-comment --check-relation-type DEF_PK --ignore-table-comment --skip-column-comment-change --create-table-with-index --allow-pk-change --mysql-dump-auto-increment -r, --require LIBS --log-file LOG_FILE --verbose --debug --[no-]color -v, --version ``` ## Usage ```sh $ git init Initialized empty Git repository in ... $ cat config.yml adapter: mysql2 encoding: utf8 database: blog username: root $ ridgepole -c config.yml --export -o Schemafile # or `ridgepole -c '{adapter: mysql2, database: blog}' ...` # or `ridgepole -c 'mysql2://root:pass@127.0.0.1:3306/blog' ...` # or `export DB_URL='mysql2://...'; ridgepole -c env:DB_URL ...` Export Schema to `Schemafile` $ cat Schemafile create_table "articles", force: :cascade do |t| t.string "title" t.text "text" t.datetime "created_at" t.datetime "updated_at" end $ git add . $ git commit -m 'first commit' -a [master (root-commit) a6c2d31] first commit 2 files changed, 10 insertions(+) create mode 100644 Schemafile create mode 100644 config.yml $ vi Schemafile $ git diff diff --git a/Schemafile b/Schemafile index f5848b9..c266fed 100644 --- a/Schemafile +++ b/Schemafile @@ -1,6 +1,7 @@ create_table "articles", force: :cascade do |t| t.string "title" t.text "text" + t.text "author" t.datetime "created_at" t.datetime "updated_at" end $ ridgepole -c config.yml --apply --dry-run Apply `Schemafile` (dry-run) add_column("articles", "author", :text, {:after=>"text"}) # ALTER TABLE `articles` ADD `author` text AFTER `text` $ ridgepole -c config.yml --apply Apply `Schemafile` -- add_column("articles", "author", :text, {:after=>"text"}) -> 0.0202s ``` ## Rename ```ruby create_table "articles", force: :cascade do |t| t.string "title" t.text "desc", renamed_from: "text" t.text "author" t.datetime "created_at" t.datetime "updated_at" end create_table "user_comments", force: :cascade, renamed_from: "comments" do |t| t.string "commenter" t.text "body" t.integer "article_id" t.datetime "created_at" t.datetime "updated_at" end ``` ## Foreign Key ```ruby create_table "parent", force: :cascade do |t| end create_table "child", id: false, force: :cascade do |t| t.bigint "id" t.bigint "parent_id" end add_index "child", ["parent_id"], name: "par_ind", using: :btree add_foreign_key "child", "parent", name: "child_ibfk_1" ``` ## Ignore Column/Index/FK ```ruby create_table "articles", force: :cascade do |t| t.string "title", ignore: true # All changes are ignored t.text "desc", renamed_from: "text" t.text "author" t.datetime "created_at" t.datetime "updated_at" end ``` ## Collation/Charset ```ruby create_table "articles", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t| t.string "title", collation: "ascii_bin" t.text "text", null: false, collation: "utf8mb4_bin" t.datetime "created_at" t.datetime "updated_at" end ``` Charset: activerecord 5.0.0 and activerecord-mysql-awesome dumps a collation rather than charset because it does not determine the default collation for charset. Specifying a collation for each column would work if it is possible. See `mysql> show character set;` to find charset / collation pair for your system. ## Execute ```ruby create_table "authors", force: :cascade do |t| t.string "name", null: false end create_table "books", force: :cascade do |t| t.string "title", null: false t.integer "author_id", null: false end add_index "books", ["author_id"], name: "idx_author_id", using: :btree execute("ALTER TABLE books ADD CONSTRAINT fk_author FOREIGN KEY (author_id) REFERENCES authors (id)") do |c| # Execute SQL only if there is no foreign key c.raw_connection.query(<<-SQL).each.size.zero? SELECT 1 FROM information_schema.key_column_usage WHERE TABLE_SCHEMA = 'bookshelf' AND CONSTRAINT_NAME = 'fk_author' LIMIT 1 SQL end ``` ## Diff ```sh $ ridgepole --diff file1.schema file2.schema add_column("articles", "author", :text, {:after=>"title"}) rename_column("articles", "text", "desc") # You can apply to the database the difference: # $ ridgepole -c config.yml --diff file1.schema file2.schema --with-apply ``` You can also compare databases and files. ```sh $ ridgepole --diff config.yml file1.schema remove_column("articles", "author") ``` ## Execute SQL using external script ```sh $ cat test.sh #!/bin/sh SQL="$1" CONFIG_JSON="$2" echo "$SQL" | mysql -u root my_db $ ridgepole -c config.yml --apply --external-script ./test.sh ``` ## Add extra statement to ALTER ```sh $ ridgepole -a -c database.yml --alter-extra="LOCK=NONE" --debug Apply `Schemafile` ... -- add_column("dept_manager", "to_date2", :date, {:null=>false, :after=>"from_date"}) (42.2ms) ALTER TABLE `dept_manager` ADD `to_date2` date NOT NULL AFTER `from_date`,LOCK=NONE -> 0.0428s -- remove_column("dept_manager", "to_date") (46.9ms) ALTER TABLE `dept_manager` DROP `to_date`,LOCK=NONE -> 0.0471s ``` ## Relation column type check ```ruby create_table "employees", force: :cascade do |t| t.integer "emp_no", null: false t.string "first_name", limit: 14, null: false t.string "last_name", limit: 16, null: false end create_table "dept_manager", force: :cascade do |t| t.integer "employee_id" t.string "dept_no", limit: 4, null: false end ``` ```sh $ ridgepole -a -c database.yml --check-relation-type bigint # default primary key type (e.g. `<5.1`: integer, `>=5.1`: bigint for MySQL) Apply `Schemafile` ... [WARNING] Relation column type is different. employees.id: bigint dept_manager.employee_id: integer ... ``` ## Run tests ```sh docker-compose up -d bundle install bundle exec appraisal install bundle exec appraisal activerecord-5.1 rake # POSTGRESQL=1 bundle exec appraisal activerecord-5.1 rake # MYSQL57=1 bundle exec appraisal activerecord-5.1 rake ``` **Notice:** mysql-client/postgresql-client is required. ## Demo * [asciinema.org/a/9349](https://asciinema.org/a/9349) * [asciinema.org/a/11788](https://asciinema.org/a/11788) ## Example project * https://github.com/winebarrel/ridgepole-example * https://github.com/winebarrel/ridgepole-example/pull/1 * https://github.com/winebarrel/ridgepole-example/pull/2