ActiveRecord basics and Postgres features Dax Huiberts CTO/developer@ Salonized.com 

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 

Things I don't like • Caching • Inconsistent data • Silent failures 

Basics 

save vs save! 

@user = User.new(params[:user]) if @user.save redirect_to user_path(@user) else render :new end 

csv_data.each do |line| @user = User.new(line.to_h) @user.save! end 

ActiveRecord::Base.transaction do csv_data.each do |line| @user = User.new(line.to_h) @user.save! end end 

Uniqueness validations 

class User < ActiveRecord::Base validates :email, uniqueness: true end 

No content

class EmailUniqIndex < ActiveRecord::Migration def up add_index :users, :email, unique: true end end 

No content

Associations 

class Invoice < ActiveRecord::Base has_many :line_items end class LineItem < ActiveRecord::Base belongs_to :invoice validates :invoice, presence: true end 

class Invoice < ActiveRecord::Base has_many :line_items, dependent: :destroy end class LineItem < ActiveRecord::Base belongs_to :invoice validates :invoice, presence: true end 

class AddInvoiceIdToLineItems < ActiveRecord::Migration def up add_reference :line_items, :invoice, foreign_key: true, index: true, null: false end end 

N+1 queries 

Invoice.limit(100).each do |invoice| invoice.line_items.each do |line_item| # do something with line_item end end 

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 

Missing indexes 

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 

Advanced stuff 

Polymorphic associations which are evil 

class LineItem < ActiveRecord::Base belongs_to :invoice belongs_to :invoicable, polymorphic: true validates :invoice, presence: true validates :invoicable, presence: true end 

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 

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 

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) ); 

Database views as rails models 

Search over multiple tables 

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; 

class Invoicable < ActiveRecord::Base self.primary_key = :id belongs_to :product belongs_to :service # attribute :kind # attribute :name # attribute :price end 

Aggregated results for reporting 

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; 

class DerivedInvoice < ActiveRecord::Base belongs_to :invoice # attribute :item_count # attribute :total_price end 

One last trick just for showing off 

class RosterEntry < ActiveRecord::Base # attribute :start_date # attribute :end_date end 

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 

Learnings • Breathe SQL • Use The Index, Luke • Look out for slow queries • `explain analyze` is your friend • Postgres is awesome! 

Thank you PS: We're hiring! :-)