Active record, standalone migrations, and working with Arel

A two-part presentation given to the folks at EverTrue on the benefits of using ActiveRecord for database management in non-rails projects and a comparison of ActiveRecord and Arel and how Arel can be useful in building complex and interesting queries (inspired by a talk by Cameron Dutro)

Transcript

1. ActiveRecord, Standalone Migrations, and aRel Alex Tironati
2. Overview • Part 1 - ActiveRecord/Standalone Migrations ◦ The Problem ◦ Motivation ◦ The Solution ◦ Implementation • Part 2 - Arel vs. AR ◦ ActiveRecord Basics ◦ ARel tables ◦ Select and Join queries ◦ Using a QueryBuilder ◦ Bringing it home
3. Part 1 Standalone Database Migrations in non-Rails Projects
4. The Problem
5. The Underlying Problem • We have no process in place for managing the database schemas for our Java projects • This is the best we could do before:
6. Is that good enough?
7. Motivation • We should be able to keep track of the schema changes over time • We should have a standard way to change the schema across all databases • We shouldn’t have to go through SQLPro to make those changes • We should have an easy way to recreate the current schema from scratch • We should be able to run those changes automatically, after they are defined
8. What magical wonder-framework of Poseidon does all this shit? ?
9. Oh that’s right
16. Implementation • Add a .env file • Make a project-level db folder • Create a Gemfile and add: ◦ gem 'active_record_migrations' ◦ gem 'mysql2' ◦ gem 'dotenv' • Create a migration file for each table in your database: ◦ rake db:new_migration name='create_note_tables' ◦ Remember to include index definitions ◦ Make sure all of the fields match your current schema exactly ◦ run ‘rake db:migrate’ to test a lot locally • When the migrations are written, go to new schema_migrations table
17. Implementation • Create schema_migrations on staging and prod instances • Duplicate entries (timestamps) from local schema_migrations table to remote instances so that they match (the migrations are in order) • run migrations on remote instance: ◦ rake db:migrate RAILS_ENV=staging • This will generate a file in db/schema.rb ◦ Be sure to commit and push this to Github every time you run migrations • Update your Capistrano build to automatically run migrations for the right environment on deploy
18. .env
19. Migration Example
20. Ugly way to make bigint primary key
21. Schema.rb
22. Capistrano task
23. Part 2 ActiveRecord vs. aRel
24. RailsConf 2014 - Advanced aRel: When ActiveRecord Just Isn’t Enough • Cameron Dutro • Works on Twitter’s International Engineering team • https://www.youtube.com/watch? v=ShPAxNcLm3o&index=14&list=PLE7tQUdR KcyZ5jfnbS_osIoWzK_FrwKz5
25. Rails using SQL
26. Rails ¾ Improvements
27. Issues • Just wrapping SQL in Rails methods • You have to know underlying language • No syntax checking • Who likes matching question marks? ◦ They’re confusing, man • Not object-oriented
28. Arelized
29. Benefits • Don’t have to know SQL • Ruby syntax checking • Chainable, composable • No question marks • Easier to read
30. ActiveRecord • Database abstraction • Persistence layer ◦ Database rows as Ruby objects • Domain Logic ◦ Validations • Associations
31. Arel • “Relational Algebra” for Ruby • Used to build queries, generates ASTs • Applies query optimizations • Enables Chaining (subtrees) • Not well documented • Knows nothing about models or database, does not retrieve data ◦ That’s ActiveRecord’s job ◦ Arel is all like “IDGAF” about yo models, db, or data
32. What’s an AST?
33. Arel Table
34. Arel-Helpers Gem • gem install arel-helpers
35. Active Record Relations and .to_sql
36. Arelized
37. Select
38. More ActiveRecord vs. Arel In this case AR is better/ cleaner
39. .not_eq(nil)
40. Reminder of the Data Model
41. Joins
42. Outer Joins Only Possible in Arel
43. Ugly Arel Version
44. Outer Joins Only Possible in Arel
45. Query Builder/Builder Pattern
46. Constructing a Query - Post Query Bldr
47. Constructing a Query cont.
48. Example in our own code!
49. Translated to SQL
50. The End