ActiveRecord basics and Postgres features

A talk I gave on the Amsterdam Ruby meetup on Tuesday November 22nd.

Transcript

1. ActiveRecord basics and Postgres features Dax Huiberts CTO/developer@ Salonized.com
2. Why postgres • It's a great tool • It gets the job done • I like having just one data source • It can handle a lot of data (+100M records) • Guarantee data consistency • It is much faster than your ruby code
3. Things I don't like • Caching • Inconsistent data • Silent failures
4. Basics
5. save vs save!
6. @user = User.new(params[:user]) if @user.save redirect_to user_path(@user) else render :new end
7. csv_data.each do |line| @user = User.new(line.to_h) @user.save! end
8. ActiveRecord::Base.transaction do csv_data.each do |line| @user = User.new(line.to_h) @user.save! end end
9. Uniqueness validations
10. class User < ActiveRecord::Base validates :email, uniqueness: true end
12. class EmailUniqIndex < ActiveRecord::Migration def up add_index :users, :email, unique: true end end
14. Associations
15. class Invoice < ActiveRecord::Base has_many :line_items end class LineItem < ActiveRecord::Base belongs_to :invoice validates :invoice, presence: true end
16. class Invoice < ActiveRecord::Base has_many :line_items, dependent: :destroy end class LineItem < ActiveRecord::Base belongs_to :invoice validates :invoice, presence: true end
17. class AddInvoiceIdToLineItems < ActiveRecord::Migration def up add_reference :line_items, :invoice, foreign_key: true, index: true, null: false end end
18. N+1 queries
19. Invoice.limit(100).each do |invoice| invoice.line_items.each do |line_item| # do something with line_item end end
20. invoices = Invoice.limit(100).includes(:line_items) invoices.each do |invoice| invoice.line_items.each do |line_item| # do something with line_item end end
21. Missing indexes
22. invoices = Invoice.limit(100).includes(:line_items) invoices.each do |invoice| invoice.line_items.each do |line_item| # do something with line_item end end
23. Advanced stuff
24. Polymorphic associations which are evil
25. class LineItem < ActiveRecord::Base belongs_to :invoice belongs_to :invoicable, polymorphic: true validates :invoice, presence: true validates :invoicable, presence: true end
26. class LineItem < ActiveRecord::Base belongs_to :invoice belongs_to :product belongs_to :service validates :invoice, presence: true validates :product, presence: true, unless: :service? validates :product, absence: true, if: :service? validates :service, presence: true, unless: :product? validates :service, absence: true, if: :product? end
27. class LineItem < ActiveRecord::Base belongs_to :invoice belongs_to :product belongs_to :service validates :invoice, presence: true validates :kind, inclusion: {in: ['product', 'service']} validates :product, presence: true, if: "kind == 'product'" validates :product, absence: true, if: "kind != 'product'" validates :service, presence: true, if: "kind == 'service'" validates :service, absence: true, if: "kind != 'service'" end
28. ALTER TABLE line_items ADD CONSTRAINT kind_values CHECK (kind IN ('product', 'service'); ALTER TABLE line_items ADD CONSTRAINT product_kind CHECK ( (kind = 'product' AND product_id IS NOT NULL) OR (kind != 'product' AND product_id IS NULL) ); ALTER TABLE line_items ADD CONSTRAINT service_kind CHECK ( (kind = 'service' AND service_id IS NOT NULL) OR (kind != 'service' AND service_id IS NULL) );
29. Database views as rails models
30. Search over multiple tables
31. CREATE VIEW invoicables AS SELECT concat('p', p.id) AS id, 'product' AS kind, p.id AS product_id, NULL AS service_id, p.name, p.price FROM products AS p UNION ALL SELECT concat('s', s.id) AS id, 'service' AS kind, NULL AS product_id, s.id AS service_id, s.name, s.price FROM services AS s;
32. class Invoicable < ActiveRecord::Base self.primary_key = :id belongs_to :product belongs_to :service # attribute :kind # attribute :name # attribute :price end
33. Aggregated results for reporting
34. CREATE VIEW derived_invoices AS SELECT i.id AS id, i.id AS invoice_id, COUNT(li.id) AS item_count, SUM(li.price * li.quantity) AS total_price FROM invoices AS i LEFT JOIN line_items AS li ON i.id = li.invoice_id GROUP BY i.id;
35. class DerivedInvoice < ActiveRecord::Base belongs_to :invoice # attribute :item_count # attribute :total_price end
36. One last trick just for showing off
37. class RosterEntry < ActiveRecord::Base # attribute :start_date # attribute :end_date end
38. CREATE VIEW roster_days AS SELECT UNNEST(ARRAY( SELECT next_date FROM GENERATE_SERIES( start_date, end_date, '1 week' ) AS next_date ))::date AS date FROM roster_entries
39. Learnings • Breathe SQL • Use The Index, Luke • Look out for slow queries • `explain analyze` is your friend • Postgres is awesome!
40. Thank you PS: We're hiring! :-)