Up & Down Again: A Migration's Tale

Up & Down Again: A Migration's Tale

You run rake db:migrate and rake db:schema:load regularly, but what do they actually do? How does rake db:rollback automatically reverse migrations and why can't it reverse all of them? How can you teach these tasks new tricks to support additional database constructs?

We'll answer all of this and more as we explore the world of schema management in Rails. You will leave this talk with a deep understanding of how Rails manages schema, a better idea of its pitfalls, and ready to bend it to your will.

C537a86fdc5234b3e941a84c154ba034?s=128

Derek Prior

April 19, 2018
Tweet

Transcript

  1. Up & Down Again A Migration's Tale

  2. Derek Prior @derekprior

  3. None
  4. Schema Management @derekprior

  5. 0.10.1 @derekprior

  6. Agenda @derekprior

  7. Agenda → Anatomy of a Migration @derekprior

  8. Agenda → Anatomy of a Migration → Applying and Reverting

    a Migration @derekprior
  9. Agenda → Anatomy of a Migration → Applying and Reverting

    a Migration → Schema Dumping @derekprior
  10. Agenda → Anatomy of a Migration → Applying and Reverting

    a Migration → Schema Dumping → Shortcomings and Extensions @derekprior
  11. Anatomy of a Migration @derekprior

  12. class CreatePosts < ActiveRecord::Migration[5.2] def change create_table :posts do |t|

    t.belongs_to :user, null: false, index: true, foreign_key: true t.string :title, null: false, index: { unique: true } t.text :body, null: false t.timestamps null: false, index: true end end end @derekprior
  13. class CreatePosts < ActiveRecord::Migration[5.2] def change create_table :posts do |t|

    t.belongs_to :user, null: false, index: true, foreign_key: true t.string :title, null: false, index: { unique: true } t.text :body, null: false t.timestamps null: false, index: true end end end @derekprior
  14. ActiveRecord::Migration def self.[](version) Compatibility.find(version) end module Compatibility def self.find(version) name

    = "V#{version.tr('.', '_')}" # Some error handling... const_get(name) end end @derekprior
  15. ActiveRecord::Migration def self.[](version) Compatibility.find(version) end module Compatibility def self.find(version) name

    = "V#{version.tr('.', '_')}" # Some error handling... const_get(name) end end @derekprior
  16. ActiveRecord::Migration[5.2] @derekprior

  17. ActiveRecord::Migration::Compatibility::V5_2 @derekprior

  18. Compatibility::V5_2 module ActiveRecord class Migration module Compatibility class V5_2 <

    Migration end end end end @derekprior
  19. Prevent Subclassing Migration module ActiveRecord class Migration def self.inherited(subclass) super

    if subclass.superclass == Migration raise StandardError, "Directly inheriting ... is not supported. " end end end end @derekprior
  20. class CreatePosts < ActiveRecord::Migration[5.1] def change create_table :posts do |t|

    t.belongs_to :user, null: false, index: true, foreign_key: true t.string :title, null: false, index: { unique: true } t.text :body, null: false t.timestamps null: false, index: true end end end @derekprior
  21. Compatibility::V5_1 class V5_1 < V5_2 def change_column(table_name, column_name, type, options

    = {}) if adapter_name == "PostgreSQL" #... else super end end def create_table(table_name, options = {}) if adapter_name == "Mysql2" #... else super end end end @derekprior
  22. Compatibility::V5_1 class V5_1 < V5_2 def change_column(table_name, column_name, type, options

    = {}) if adapter_name == "PostgreSQL" #... else super end end def create_table(table_name, options = {}) if adapter_name == "Mysql2" #... else super end end end @derekprior
  23. Compatibility::V5_1 class V5_1 < V5_2 def change_column(table_name, column_name, type, options

    = {}) if adapter_name == "PostgreSQL" #... else super end end def create_table(table_name, options = {}) if adapter_name == "Mysql2" #... else super end end end @derekprior
  24. Stable Migrations @derekprior

  25. ! @derekprior

  26. class CreatePosts < ActiveRecord::Migration[5.2] def change create_table :posts do |t|

    t.belongs_to :user, null: false, index: true, foreign_key: true t.string :title, null: false, index: { unique: true } t.text :body, null: false t.timestamps null: false, index: true end end end @derekprior
  27. class CreatePosts < ActiveRecord::Migration[5.2] def change create_table :posts do |t|

    t.belongs_to :user, null: false, index: true, foreign_key: true t.string :title, null: false, index: { unique: true } t.text :body, null: false t.timestamps null: false, index: true end end end @derekprior
  28. Schema Statements @derekprior

  29. ActiveRecord::ConnectionAdapters::SchemaStatements @derekprior

  30. create_table :posts do |t| t.belongs_to :user, null: false, index: true,

    foreign_key: true t.string :title, null: false, index: { unique: true } t.text :body, null: false t.timestamps null: false, index: true end @derekprior
  31. create_table :posts do |t| t.belongs_to :user, null: false, index: true,

    foreign_key: true t.string :title, null: false, index: { unique: true } t.text :body, null: false t.timestamps null: false, index: true end @derekprior
  32. Jobs To Be Done @derekprior

  33. Jobs To Be Done → Adds non nullable user_id to

    CREATE TABLE @derekprior
  34. Jobs To Be Done → Adds non nullable user_id to

    CREATE TABLE → Adds an index to the user_id column @derekprior
  35. Jobs To Be Done → Adds non nullable user_id to

    CREATE TABLE → Adds an index to the user_id column → Adds a foreign key to the users table @derekprior
  36. class CreatePosts < ActiveRecord::Migration[5.2] def change create_table :posts do |t|

    t.belongs_to :user, null: false, index: true, foreign_key: true t.string :title, null: false, index: { unique: true } t.text :body, null: false t.timestamps null: false, index: true end end end @derekprior
  37. Postgres CREATE TABLE "posts" ( "id" bigserial primary key, "title"

    character varying NOT NULL, "body" text NOT NULL, "user_id" bigint NOT NULL, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL, CONSTRAINT "fk_rails_5b5ddfd518" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ); CREATE UNIQUE INDEX "index_posts_on_title" ON "posts" ("title"); CREATE INDEX "index_posts_on_user_id" ON "posts" ("user_id"); CREATE INDEX "index_posts_on_created_at" ON "posts" ("created_at"); CREATE INDEX "index_posts_on_updated_at" ON "posts" ("updated_at"); @derekprior
  38. MySQL CREATE TABLE `posts` ( `id` bigint NOT NULL AUTO_INCREMENT

    PRIMARY KEY, `title` varchar(255) NOT NULL, `body` text NOT NULL, `user_id` bigint NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, UNIQUE INDEX `index_posts_on_title` (`title`), INDEX `index_posts_on_user_id` (`user_id`), INDEX `index_posts_on_created_at` (`created_at`), INDEX `index_posts_on_updated_at` (`updated_at`), CONSTRAINT `fk_rails_5b5ddfd518` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ); @derekprior
  39. Adatper Pattern @derekprior

  40. Adapter Pattern "Convert the interface of a class into another

    interface clients expect. Adapter lets classes work together that couldn't otherwise because of incompatible interfaces." -- Design Patterns (Gang of Four) @derekprior
  41. External Dependencies @derekprior

  42. External Dependencies → Postgres @derekprior

  43. External Dependencies → Postgres → MySQL @derekprior

  44. External Dependencies → Postgres → MySQL → SQLite @derekprior

  45. External Dependencies → Postgres → MySQL → SQLite → ...

    @derekprior
  46. Connection Adapters @derekprior

  47. Connection Adapters → AbstractAdapter @derekprior

  48. Connection Adapters → AbstractAdapter → PostgreSQLAdapter @derekprior

  49. Connection Adapters → AbstractAdapter → PostgreSQLAdapter → Mysql2Adapter @derekprior

  50. Connection Adapters → AbstractAdapter → PostgreSQLAdapter → Mysql2Adapter → SQLite3Adapter

    @derekprior
  51. Connection Adapters → AbstractAdapter → PostgreSQLAdapter → Mysql2Adapter → SQLite3Adapter

    → ... @derekprior
  52. Connection Adapter Responsibilities @derekprior

  53. Connection Adapter Responsibilities → Schema inspection @derekprior

  54. Connection Adapter Responsibilities → Schema inspection → Schema statements @derekprior

  55. Connection Adapter Responsibilities → Schema inspection → Schema statements →

    Mapping types @derekprior
  56. Connection Adapter Responsibilities → Schema inspection → Schema statements →

    Mapping types → Cataloging capabilities @derekprior
  57. Connection Adapter Responsibilities → Schema inspection → Schema statements →

    Mapping types → Cataloging capabilities → Quoting @derekprior
  58. Agenda → Anatomy of a Migration → Applying and Reverting

    a Migration → Schema Dumping → Shortcomings and Extensions @derekprior
  59. Applying a Migration @derekprior

  60. rails db:migrate @derekprior

  61. Which Migrations Will Run? @derekprior

  62. 20180327012835_create_posts.rb @derekprior

  63. What's in a Filename? 20180327012835 create_posts version name @derekprior

  64. SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC @derekprior

  65. INSERT INTO "schema_migrations" ("version") VALUES ($1) RETURNING "version" [["version", "20180327012835"]]

    @derekprior
  66. rails db:migrate:status @derekprior

  67. rails db:migrate:status Status Migration ID Migration Name up 20180327005927 Create

    users down 20180327012835 Create posts @derekprior
  68. rails db:migrate:status Status Migration ID Migration Name up 20180327005927 Create

    users up 20180327012835 ** NO FILE ** @derekprior
  69. Rolling Back a Migration @derekprior

  70. With a down method def up add_column :users, :name, :string

    remove_column :users, :first_name remove_column :users, :last_name end def down add_column :users, :first_name, :string add_column :users, :last_name, :string remove_column :users: name end @derekprior
  71. With a change method def change add_column :users, :name, :string

    remove_column :users, :first_name remove_column :users, :last_name end @derekprior
  72. Down For Free @derekprior

  73. ActiveRecord::Migration::CommandRecorder def initialize(delegate = nil) @commands = [] end def

    record(*command, &block) @commands << (command << block) end @derekprior
  74. ActiveRecord::Migration::CommandRecorder def create_table(*args, &block) record(:create_table, args, block) end def add_index(*args,

    &block) record(:add_index, args, block) end @derekprior
  75. ActiveRecord::Migration::CommandRecorder def invert_create_table(args, &block) [:drop_table, args, block] end def invert_add_index(args,

    &block) [:remove_index, args, block] end def invert_rename_table(args) [:rename_table, args.reverse] end @derekprior
  76. Is This Migration Reversible? def change add_column :users, :name, :string

    remove_column :users, :first_name remove_column :users, :last_name end @derekprior
  77. ActiveRecord::IrreversibleMigration remove_column is only reversible if given a type @derekprior

  78. def change add_column :users, :name, :string remove_column :users, :first_name remove_column

    :users, :last_name end @derekprior
  79. def change add_column :users, :name, :string remove_column :users, :first_name, :string

    remove_column :users, :last_name, :string end @derekprior
  80. def change add_column :users, :name, :string remove_column :users, :first_name, :string

    remove_column :users, :last_name, :string end @derekprior
  81. What Did We Record? [ [:add_column, [:users, :name, :string]], [:remove_column,

    [:users, :first_name, :string]], [:remove_column, [:users, :last_name, :string]], ] @derekprior
  82. Inverted [ [:add_column, [:users, :first_name, :string] [:add_column, [:users, :last_name, :string]

    [:remove_column, [:users, :name]], ] @derekprior
  83. ! @derekprior

  84. ! @derekprior

  85. What if a removed column originally had... @derekprior

  86. What if a removed column originally had... → A null:

    false constraint? @derekprior
  87. What if a removed column originally had... → A null:

    false constraint? → A default value? @derekprior
  88. What if a removed column originally had... → A null:

    false constraint? → A default value? → A foreign key? @derekprior
  89. It's Not Magic @derekprior

  90. Agenda → Anatomy of a Migration → Applying and Reverting

    a Migration → Schema Dumping → Shortcomings and Extensions @derekprior
  91. # This file is auto-generated from the current state of

    the database. # ... ActiveRecord::Schema.define(version: 2018_03_27_012835) do enable_extension "plpgsql" create_table "posts", force: :cascade do |t| t.string "title", null: false #... end create_table "users", force: :cascade do |t| t.string "email", null: false #... end add_foreign_key "posts", "users" end @derekprior
  92. How is it Generated? @derekprior

  93. ActiveRecord::SchemaDumper module ActiveRecord class SchemaDumper def self.dump(connection, stream = STDOUT)

    connection.create_schema_dumper.dump(stream) end def dump(stream) header(stream) extensions(stream) tables(stream) trailer(stream) end end end @derekprior
  94. module ActiveRecord class SchemaDumper def self.dump(connection, stream = STDOUT) connection.create_schema_dumper.dump(stream)

    end def dump(stream) header(stream) extensions(stream) tables(stream) trailer(stream) end end end @derekprior
  95. module ActiveRecord class SchemaDumper def self.dump(connection, stream = STDOUT) connection.create_schema_dumper.dump(stream)

    end def dump(stream) header(stream) extensions(stream) tables(stream) trailer(stream) end end end @derekprior
  96. # This file is auto-generated from the current state of

    the database. # ... ActiveRecord::Schema.define(version: 2018_03_27_012835) do enable_extension "plpgsql" create_table "posts", force: :cascade do |t| t.string "title", null: false #... end create_table "users", force: :cascade do |t| t.string "email", null: false #... end add_foreign_key "posts", "users" end @derekprior
  97. # This file is auto-generated from the current state of

    the database. # ... ActiveRecord::Schema.define(version: 2018_03_27_012835) do enable_extension "plpgsql" create_table "posts", force: :cascade do |t| t.string "title", null: false #... end create_table "users", force: :cascade do |t| t.string "email", null: false #... end add_foreign_key "posts", "users" end @derekprior
  98. # This file is auto-generated from the current state of

    the database. # ... ActiveRecord::Schema.define(version: 2018_03_27_012835) do enable_extension "plpgsql" create_table "posts", force: :cascade do |t| t.string "title", null: false #... end create_table "users", force: :cascade do |t| t.string "email", null: false #... end add_foreign_key "posts", "users" end @derekprior
  99. # This file is auto-generated from the current state of

    the database. # ... ActiveRecord::Schema.define(version: 2018_03_27_012835) do enable_extension "plpgsql" create_table "posts", force: :cascade do |t| t.string "title", null: false #... end create_table "users", force: :cascade do |t| t.string "email", null: false #... end add_foreign_key "posts", "users" end @derekprior
  100. # This file is auto-generated from the current state of

    the database. # ... ActiveRecord::Schema.define(version: 2018_03_27_012835) do enable_extension "plpgsql" create_table "posts", force: :cascade do |t| t.string "title", null: false #... end create_table "users", force: :cascade do |t| t.string "email", null: false #... end add_foreign_key "posts", "users" end @derekprior
  101. # This file is auto-generated from the current state of

    the database. # ... ActiveRecord::Schema.define(version: 2018_03_27_012835) do enable_extension "plpgsql" create_table "posts", force: :cascade do |t| t.string "title", null: false #... end create_table "users", force: :cascade do |t| t.string "email", null: false #... end add_foreign_key "posts", "users" end @derekprior
  102. Justifies Its Existence # This file is auto-generated from the

    current state of the database. Instead # of editing this file, please use the migrations feature of Active Record to # incrementally modify your database, and then regenerate this schema definition. # # Note that this schema.rb definition is the authoritative source for your # database schema. If you need to create the application database on another # system, you should be using db:schema:load, not running all the migrations # from scratch. The latter is a flawed and unsustainable approach (the more migrations # you'll amass, the slower it'll run and the greater likelihood for issues). # # It's strongly recommended that you check this file into your version control system. @derekprior
  103. "... the authoritative source for your database schema" @derekprior

  104. ! @derekprior

  105. "... you should be using db:schema:load, not running all migrations

    from scratch" @derekprior
  106. "the latter is a flawed and unsustainable approach" @derekprior

  107. "The more migrations you amass, the slower it'll run and

    the greater likelihood for issues" @derekprior
  108. ! @derekprior

  109. ! @derekprior

  110. ❌ @derekprior

  111. "greater likelihood for issues..." @derekprior

  112. Causes of Migration Rot @derekprior

  113. Causes of Migration Rot → Changing schema statements over time

    @derekprior
  114. Causes of Migration Rot → Changing schema statements over time

    → External dependencies in migrations @derekprior
  115. Causes of Migration Rot → Changing schema statements over time

    → External dependencies in migrations @derekprior
  116. External Dependencies in Migrations class CombineNameFields < ActiveRecord::Migration[5.2] def change

    add_column :users, :name, :string reversible do |dir| dir.up do User.find_each { |u| u.update(name: "#{u.first_name} #{u.last_name}") } end end remove_column :users, :first_name, :string remove_column :users, :last_name, :string end end @derekprior
  117. External Dependencies in Migrations class CombineNameFields < ActiveRecord::Migration[5.2] def change

    add_column :users, :name, :string reversible do |dir| dir.up do User.find_each { |u| u.update(name: "#{u.first_name} #{u.last_name}") } end end remove_column :users, :first_name, :string remove_column :users, :last_name, :string end end @derekprior
  118. External Dependencies in Migrations class CombineNameFields < ActiveRecord::Migration[5.2] def change

    add_column :users, :name, :string execute "UPDATE users SET name = CONCAT(first_name, ' ', last_name)" remove_column :users, :first_name, :string remove_column :users, :last_name, :string end end @derekprior
  119. Mind Your Dependencies @derekprior

  120. Agenda → Anatomy of a Migration → Applying and Reverting

    a Migration → Schema Dumping → Shortcomings and Extensions @derekprior
  121. Shortcomings @derekprior

  122. Shortcomings → Migration rot @derekprior

  123. Shortcomings → Migration rot → Further removed from SQL @derekprior

  124. Shortcomings → Migration rot → Further removed from SQL →

    Support for a limited subset of features @derekprior
  125. Foreign Keys @derekprior

  126. Rails 4.2 @derekprior

  127. Expression Indexes @derekprior

  128. Rails 5.0 @derekprior

  129. Functions @derekprior

  130. ! @derekprior

  131. Triggers @derekprior

  132. ☹ @derekprior

  133. Views @derekprior

  134. ! @derekprior

  135. execute "WHATEVER SQL YOU WANT" @derekprior

  136. config.active_record.schema_format = :sql @derekprior

  137. Extending Migrations @derekprior

  138. No Official API @derekprior

  139. ! @derekprior

  140. ! @derekprior

  141. Database Views @derekprior

  142. Requirements @derekprior

  143. Requirements → Schema Statements @derekprior

  144. Requirements → Schema Statements → Command Recorder @derekprior

  145. Requirements → Schema Statements → Command Recorder → Schema Dumper

    @derekprior
  146. Schema Statements module Scenic module Statements def create_view(version:, materialized: false)

    end def drop_view(version:, revert_to_version: nil, materialized: false) end def update_view(name, version:, revert_to_version: nil, materialized: false) end end end @derekprior
  147. Requirements → Schema Statements → Command Recorder → Schema Dumper

    @derekprior
  148. Command Recorder module Scenic module CommandRecorder def create_view(*args) record(:create_view, args)

    end def drop_view(*args) record(:drop_view, args) end def update_view(*args) record(:update_view, args) end end end @derekprior
  149. Command Recorder module Scenic module CommandRecorder def invert_create_view(args) [:drop_view, args]

    end def invert_drop_view(args) perform_scenic_inversion(:create_view, args) end def invert_update_view(args) perform_scenic_inversion(:update_view, args) end end end @derekprior
  150. Command Recorder module Scenic module CommandRecorder def perform_scenic_inversion(method, args) scenic_args

    = StatementArguments.new(args) if scenic_args.revert_to_version.nil? message = "#{method} is reversible only if given a revert_to_version" raise ActiveRecord::IrreversibleMigration, message end [method, scenic_args.invert_version.to_a] end end end @derekprior
  151. Requirements → Schema Statements → Command Recorder → Schema Dumper

    @derekprior
  152. Schema Dumper module Scenic module SchemaDumper def tables(stream) super views(stream)

    end def views # ... end end end @derekprior
  153. Schema Dumper module Scenic module SchemaDumper def tables(stream) super views(stream)

    end def views # ... end end end @derekprior
  154. Wiring It All Up module Scenic class Railtie < Rails::Railtie

    initializer "scenic.load" do ActiveSupport.on_load :active_record do Scenic.load end end end def self.load ActiveRecord::ConnectionAdapters::AbstractAdapter.include Scenic::Statements ActiveRecord::Migration::CommandRecorder.include Scenic::CommandRecorder ActiveRecord::SchemaDumper.prepend Scenic::SchemaDumper end end @derekprior
  155. Wiring It All Up module Scenic class Railtie < Rails::Railtie

    initializer "scenic.load" do ActiveSupport.on_load :active_record do Scenic.load end end end def self.load ActiveRecord::ConnectionAdapters::AbstractAdapter.include Scenic::Statements ActiveRecord::Migration::CommandRecorder.include Scenic::CommandRecorder ActiveRecord::SchemaDumper.prepend Scenic::SchemaDumper end end @derekprior
  156. ! @derekprior

  157. It's ALIVE! class UpdateSearchResults < ActiveRecord::Migration[5.2] def change update_view :search_results,

    version: 3, revert_to_version: 2 end end @derekprior
  158. ! @derekprior

  159. ! @derekprior

  160. ! @derekprior

  161. What if we did none of this? -- Some guy

    on a podcast @derekprior
  162. SQL Migrations @derekprior

  163. SQL Migrations db/migrate ├── 20180327005927_create_users │ ├── down.sql │ └──

    up.sql └── 20180327012835_create_posts ├── down.sql └── up.sql @derekprior
  164. @derekprior

  165. Pareto Principle @derekprior

  166. for many events, roughly 80% of the effects come from

    20% of the causes @derekprior
  167. Higher Level Abstraction @derekprior

  168. Ruby Migrations @derekprior

  169. ! @derekprior

  170. ! Maybe learn to love structure.sql

  171. Thank You Derek Prior → twitter: @derekprior → email: derek@thoughtbot.com

    → podcast: http://bikeshed.fm