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)
Notice
- Drop support ActiveRecord 5.x in ridgepole v1.2.0.
- Partitioning is no longer supported in ridgepole v1.1.0.
- ActiveRecord 7.x has some incompatible changes. If you get unintended differences in
datetime, addprecision. - For ActiveRecord 7.x series, please use AcriveRecord 7.0.2 or higher / Ridgepole 1.0.3 or higher.
- 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/ridgepole/ridgepole/pull/323
DROP TABLEis skipped by default in v1.0 and later versions.- If you want to
DROP TABLE, please pass--drop-table. - cf. https://github.com/ridgepole/ridgepole/pull/363
- If you want to
- In Rails 7.0, the output of dumper is different from Rails 6.
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
--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
$ 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.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
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
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.
Generated Column (MySQL)
There should be NO extra white spaces in the expression (such as after comma). Quotes in expression may cause the operations failure with MySQL 8.0.
create_table "users", force: :cascade do |t|
t.string "last_name"
t.string "first_name"
t.virtual "full_name", type: :string, as: "concat(`last_name`,' ',`first_name`)", stored: true
end
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.size.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
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-7.0 rake
# POSTGRESQL=1 bundle exec appraisal activerecord-7.0 rake
# MYSQL57=1 bundle exec appraisal activerecord-7.0 rake
Notice: Ruby 2.6 or above/mysql-client/postgresql-client is required.