Ridgepole

https://rubydoc.info/gems/ridgepole/3.1.5
Admin
Ridgepole is a tool for managing database schemas using Rails DSL that automatically updates the...
Tokens:4,335
Snippets:23
Trust Score:10
Update:6 months ago
Show doc for...
Tokens:
Raw
### Schemafile Content Example

Source: https://rubydoc.info/gems/ridgepole

Example content of a Schemafile generated by Ridgepole, defining a table and its columns.

```ruby
create_table "articles", force: :cascade do |t|
  t.string   "title"
  t.text     "text"
  t.datetime "created_at"
  t.datetime "updated_at"
end

```

--------------------------------

### Install Ridgepole

Source: https://rubydoc.info/gems/ridgepole

Methods to add Ridgepole to your project environment.

```ruby
gem 'ridgepole'
```

```bash
$ bundle install
```

```bash
$ gem install ridgepole
```

--------------------------------

### Run Ridgepole Tests

Source: https://rubydoc.info/gems/ridgepole

Execute tests for Ridgepole using Docker Compose and Bundler. Ensure mysql-client or postgresql-client is installed.

```shell
docker compose up -d
bundle install
bundle exec appraisal install
bundle exec appraisal activerecord-8.1 rake
# POSTGRESQL=1 bundle exec appraisal activerecord-8.1 rake
# MYSQL80=1 bundle exec appraisal activerecord-8.1 rake


```

--------------------------------

### Ridgepole CLI Help

Source: https://rubydoc.info/gems/ridgepole

Displays the available command-line options for Ridgepole.

```bash
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
        --drop-table-only
        --mysql-change-table-options
        --mysql-change-table-comment
        --check-relation-type DEF_PK
        --ignore-table-comment
        --skip-column-comment-change
        --allow-pk-change
        --create-table-with-index
        --disable-sort-columns
        --mysql-dump-auto-increment
    -r, --require LIBS
        --log-file LOG_FILE
        --verbose
        --debug
        --[no-]color
    -v, --version
    -h, --help

```

--------------------------------

### Export Schema to Schemafile

Source: https://rubydoc.info/gems/ridgepole

Exports the current database schema to a Schemafile using a configuration file. Ensure the config.yml is correctly set up.

```bash
$ 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`

```

--------------------------------

### Diff Schemafile Changes

Source: https://rubydoc.info/gems/ridgepole

Shows the difference between the current Schemafile and the version in the repository after manual edits.

```bash
$ 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

```

--------------------------------

### Perform Schema Diffs

Source: https://rubydoc.info/gems/ridgepole

Compare schema files or databases to identify differences.

```bash
$ 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
```

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

--------------------------------

### Apply Schema Changes

Source: https://rubydoc.info/gems/ridgepole

Applies the schema changes defined in the Schemafile to the database.

```bash
$ ridgepole -c config.yml --apply
Apply `Schemafile`
-- add_column("articles", "author", :text, {:after=>"text"})
   -> 0.0202s

```

--------------------------------

### Manage Database Views

Source: https://rubydoc.info/gems/ridgepole

Use the execute method to manage views by comparing the current definition against the desired state.

```ruby
# NOTE: view_select must match the normalized form stored in information_schema.views.VIEW_DEFINITION
view_select = <<-EOS.strip
  select `mydb`.`users`.`id` AS `id`,`mydb`.`users`.`name` AS `name`,`mydb`.`users`.`active` AS `active` from `mydb`.`users` where (`mydb`.`users`.`active` = 1)
EOS

execute("CREATE OR REPLACE VIEW `active_users` AS #{view_select}") do |c|
  definition = c.raw_connection.query(<<-SQL).first&.first
    SELECT SHA(VIEW_DEFINITION) FROM information_schema.views
     WHERE TABLE_SCHEMA = 'mydb'
       AND TABLE_NAME = 'active_users';
  SQL
  definition != view_select
end
```

```ruby
# NOTE: view_select must match the normalized form stored in pg_views.definition
view_select = <<~EOS.strip
  SELECT users.name
     FROM users
    WHERE (users.active = false);
EOS

execute("CREATE OR REPLACE VIEW active_users AS #{view_select}") do |c|
  definition = c.raw_connection.query(<<-SQL).first&.dig("definition")
    SELECT definition FROM pg_views
      WHERE schemaname = 'public'
        AND viewname = 'active_users';
  SQL
  definition&.strip != view_select
end
```

--------------------------------

### Dry Run Schema Apply

Source: https://rubydoc.info/gems/ridgepole

Performs a dry run of applying schema changes from the Schemafile, showing the SQL that would be executed without making changes.

```bash
$ 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`

```

--------------------------------

### Execute Custom SQL

Source: https://rubydoc.info/gems/ridgepole

Use the execute method to run arbitrary SQL commands, optionally with conditional logic.

```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
```

--------------------------------

### Ridgepole Dry-Run Output Comparison

Source: https://rubydoc.info/gems/ridgepole

This output shows a comparison during a Ridgepole dry-run, highlighting a difference in the WHERE clause normalization for a PostgreSQL index.

```shell
% ridgepole -a -c database.yml --dry-run --verbose
Apply `Schemafile` (dry-run)
# Parse DSL
# ...
# Compare definitions
#   users
      :options=>
       {:name=>"idx_users_email",
        :unique=>true,
-       :where=>"(email IS NOT NULL)"}}}, 
+       :where=>"email is not null"}}}, 
  :options=>{:id=>:serial}}


```

--------------------------------

### Configure Collation and Charset

Source: https://rubydoc.info/gems/ridgepole

Specify collation and charset options at the table or column level to match database requirements.

```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
```

--------------------------------

### Execute SQL via External Script

Source: https://rubydoc.info/gems/ridgepole

Run external scripts during the apply process to execute custom SQL.

```bash
$ 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
```

--------------------------------

### Commit Schemafile Changes

Source: https://rubydoc.info/gems/ridgepole

Adds and commits the generated Schemafile and configuration to Git.

```bash
$ 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

```

--------------------------------

### Define PostgreSQL Partial Index

Source: https://rubydoc.info/gems/ridgepole

Use the `where` option in `t.index` to define a partial index. Ensure the WHERE clause is normalized to avoid detection differences.

```ruby
create_table "users", id: :serial, force: :cascade do |t|
  t.text "email"
  t.text "name"
  t.index ["email"], name: "idx_users_email", unique: true, where: "email is not null"
end

```

```ruby
#t.index ["email"], name: "idx_users_email", unique: true, where: "email is not null"
t.index ["email"], name: "idx_users_email", unique: true, where: "(email IS NOT NULL)"

```

--------------------------------

### Define MySQL Generated Columns

Source: https://rubydoc.info/gems/ridgepole

Use the virtual method to define generated columns. Avoid extra whitespace in the expression to prevent syntax errors.

```ruby
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
```

--------------------------------

### Check Constraint Definition

Source: https://rubydoc.info/gems/ridgepole

Defines a table with check constraints on the 'price' and 'quantity' columns. Ensure the constraint expressions in the Schemafile exactly match the database's exported format to avoid migration issues.

```ruby
create_table "products", force: :cascade do |t|
  t.string  "name", null: false
  t.decimal "price", precision: 10, scale: 2
  t.integer "quantity"

  t.check_constraint "price > 0", name: "price_check"
  t.check_constraint "quantity >= 0", name: "quantity_check"
end

```

--------------------------------

### Add Extra Statements to ALTER

Source: https://rubydoc.info/gems/ridgepole

Inject additional SQL clauses into ALTER statements using the --alter-extra flag.

```bash
$ 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
```

--------------------------------

### Foreign Key Definition

Source: https://rubydoc.info/gems/ridgepole

Defines a parent and child table, adds an index on the foreign key column, and then explicitly adds the foreign key constraint.

```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"

```

--------------------------------

### Table Rename DSL

Source: https://rubydoc.info/gems/ridgepole

Defines a table with a `renamed_from` option, indicating it was previously named differently. Use this in a separate migration step before modifying other table attributes.

```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

```

--------------------------------

### Check Relation Column Types

Source: https://rubydoc.info/gems/ridgepole

Validate that foreign key columns match the type of the primary key column.

```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
```

```bash
$ ridgepole -a -c database.yml --check-relation-type bigint # default primary key type (e.g. bigint for MySQL)
Apply `Schemafile`
...
[WARNING] Relation column type is different.
              employees.id: bigint
  dept_manager.employee_id: integer
...
```

--------------------------------

### Ignore Column, Index, or Foreign Key

Source: https://rubydoc.info/gems/ridgepole

Use the ignore: true option to prevent Ridgepole from tracking changes to specific columns.

```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
```
Tokens:
Raw