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.

Transcript

1. Up & Down Again A Migration's Tale
2. Derek Prior @derekprior
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