Ridgepole

Ridgepole is a tool to manage DB schema.

It defines DB schema using Rails DSL, and updates DB schema according to DSL. (like Chef/Puppet)

ChangeLog

>= 0.4.8
- activerecord-mysql-unsigned is now optional. Please pass --enable-mysql-unsigned after you install activerecord-mysql-unsigned if you want to use.
- Please pass --enable-foreigner after you install 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 is required)
- Fix rails version < 4.2.0
>= 0.5.2
- Add --enable-mysql-awesome option (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
>= 0.6.3
- Fix default option (pull#48)
- Add --enable-migration-comments option (pull#50)
- Disable rename_table_indexes
>= 0.6.4
- Execute sql using external script (pull#56)
- Add --mysql-use-alter option
- Add --alter-extra option
- Add --dump-with-default-fk-name option
- Support t.index (pull#64)
- Remove migration_comments
- Fix foreign key apply order
>= 0.6.5
- Fix rails version '>= 4.2', '< 6'
- Support new types (pull#84)
- Support default: -> { ... } (pull#85)
- Support DDL Comment (Rails5 only)
- Output schema diff when pass --verbose
- Support composite primary key (Rails5 only / pull#97)
>= 0.6.6
- Use t.column for migration (pull#114)
- Support DATABASE_URL format (pull#118)
- Add Ruby2.4 CI (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)
- Refactoring with RuboCop
- Support primary key adding/dropping (issue#246)
>= 0.7.4
- Fix add_foreign_key options (issue#250)
>= 0.7.5
- Fix polymorphic options (pull#263)
- Fix --mysql-use-alter option (pull#246)
- Fix Database URI parsing (pull#265)
>= 0.7.6
- Fix database url check (pull#266)
- Add ignore option (pull#267)
>= 0.7.7
- Support URI query string (pull#273)
>= 0.7.8
- Fix for add_foreign_key(..., column: ,,,) (pull#278)
>= 0.8.0
- Support Rails 6.0
>= 0.8.1
- Drop tables in an order considering foreign key constraints (pull#284)
>= 0.8.2
- Support postgres:// schema (pull#285)
>= 0.8.3
- Fix "topological sort failed" error (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)
>= 0.8.5
- Improve warning message on table options (pull#291)
>= 0.8.6
- Support multiple databases feature (pull#297)
>= 0.8.7
- Support require_relative (pull#298)

Installation

Add this line to your application's Gemfile:

gem 'ridgepole'

And then execute:

$ bundle

Or install it yourself as:

$ gem install ridgepole

Omnibus Package (deb/rpm)

see https://github.com/winebarrel/ridgepole/releases.

Install from deb

sudo dpkg -i ridgepole_x.x.x+xxx-x_amd64.deb
sudo apt install build-essential libmysqlclient-dev
sudo /opt/ridgepole/embedded/bin/gem install mysql2

Install from rpm

sudo yum install ridgepole-x.x.x+xxx-x.el7.x86_64.rpm
sudo yum install make gcc mariadb-devel
sudo /opt/ridgepole/embedded/bin/gem install mysql2

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
        --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
        --mysql-use-alter
        --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
        --mysql-dump-auto-increment
    -r, --require LIBS
        --log-file LOG_FILE
        --verbose
        --debug
        --[no-]color
    -v, --version

Usage

$ 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:[email protected]: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

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

create_table "parent", force: :cascade do |t|
end

create_table "child", id: false, force: :cascade do |t|
  t.integer "id"
  t.integer "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

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

You can use the column collation by passing --enable-mysql-awesome (activerecord-mysql-awesome is required)

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

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.length.zero?
    SELECT 1 FROM information_schema.key_column_usage
     WHERE TABLE_SCHEMA = 'bookshelf'
       AND CONSTRAINT_NAME = 'fk_author' LIMIT 1
  SQL
end

Diff

$ 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.

$ ridgepole --diff config.yml file1.schema
remove_column("articles", "author")

Execute SQL using external script

$ 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

$ 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

Use ALTER instead of CREATE/DROP INDEX

$ ridgepole -a -c database.yml --mysql-use-alter --debug
Apply `Schemafile`
...
-- remove_index("dept_manager", {:name=>"emp_no"})
   (19.2ms)  ALTER TABLE `dept_manager` DROP INDEX `emp_no`
   -> 0.0200s
-- add_index("dept_manager", ["emp_no"], {:name=>"emp_no2", :using=>:btree})
   (23.4ms)  ALTER TABLE `dept_manager` ADD  INDEX `emp_no2` USING btree (`emp_no`)
   -> 0.0243s

Relation column type check

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

$ 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

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

Example project

https://github.com/winebarrel/ridgepole-example
- winebarrel/ridgepole-example#1
- winebarrel/ridgepole-example#2

Similar tools

Codenize.tools

hashedhyphen / ridgepole Goto Github PK

ridgepole's Introduction