Use your database for… Validations! Caching! Logic!

Transcript

1. Validations!
   Caching!
   Logic!
   Use your database for…
   

   View Slide

2. vote = Vote.where(
   user_id: current_user.id,
   link_id: params[:id]
   ).first_or_create
   

   View Slide

3. INSERT INTO votes (user_id, link_id)
   VALUES (1, 2)
   ON CONFLICT DO NOTHING;
   Simple upsert
   

   View Slide

4. INSERT INTO profiles (user_id, name, age)
   VALUES (27, "Arjan", 32)
   ON CONFLICT
   ON CONSTRAINT profiles_users_fk
   ON UPDATE SET name = EXCLUDED.name,
   age = EXCLUDED.age;
   Upsert with update
   

   View Slide

5. class User < ActiveRecord::Base
   default_scope
   -> { where(deleted_at: nil) }
   scope :account_managers,
   -> { where(role: 'account_manager') }
   end
   

   View Slide

6. CREATE VIEW account_managers
   AS SELECT * FROM users
   WHERE role = 'account_manager'
   AND deleted_at IS NULL
   WITH CASCADED CHECK OPTION;
   class AccountManager < ActiveRecord::Base
   end
   Views
   

   View Slide

7. CREATE MATERIALIZED VIEW invoices
   AS SELECT number, SUM(lines.amount)
   FROM offers
   LEFT JOIN lines ON lines.offer_id = offers.id
   GROUP BY number;
   Materialized views
   

   View Slide

8. CREATE FUNCTION refresh_invoices()
   RETURNS trigger AS $$
   BEGIN
   REFRESH MATERIALIZED VIEW invoices;
   RETURN NULL;
   END;
   $$ LANGUAGE plpgsql;
   CREATE TRIGGER trigger_refresh_invoices
   AFTER TRUNCATE OR INSERT OR UPDATE OR DELETE
   ON offers FOR EACH STATEMENT
   EXECUTE PROCEDURE refresh_invoices();
   Refreshing materialised views
   

   View Slide

9. CREATE TRIGGER soft_delete_user
   BEFORE DELETE ON users
   FOR EACH ROW EXECUTE PROCEDURE soft_delete();
   CREATE FUNCTION soft_delete()
   RETURNS trigger AS $$
   DECLARE
   command text := ' SET deleted_at =
   current_timestamp WHERE id = $1';
   BEGIN
   EXECUTE 'UPDATE ' || TG_TABLE_NAME || command
   USING OLD.id;
   RETURN NULL;
   END;
   $$ LANGUAGE plpgsql;
   Triggers per row
   

   View Slide

10. class User < ActiveRecord::Base
    validates :email,
    presence: true,
    length: { maximum: 250 }
    end
    

    View Slide

11. ALTER TABLE users
    ADD CONSTRAINT email_presence
    CHECK (char_length(email) > 0);
    Constraints
    

    View Slide

12. ALTER TABLE users
    ADD CONSTRAINT age_numericality
    CHECK (age >= 18);
    Constraints
    

    View Slide

13. ALTER TABLE users
    ADD CONSTRAINT approved_role
    CHECK (role IN ("public", "admin"));
    Constraints
    

    View Slide

14. ALTER TABLE users
    ADD CONSTRAINT email_format
    CHECK (email ~* '\A([^@\s]+)@((?:[-a-
    z0-9]+\.)+[a-z]{2,})\Z');
    Constraints
    

    View Slide

15. create_table :articles do |t|
    t.string :title
    t.text :body
    t.string :tags, array: true
    t.jsonb :props, default: '{}'
    end
    Complex datatypes
    

    View Slide

16. Use it today*
    • Data validation
    • Decouple storage from
    usage
    • Referential integrity
    • Simplify complex queries
    • Cache expensive queries
    • Data audit trail
    • Optimistic locking
    • Counter cache
    • Updated/created
    timestamps
    • Multi-table constraints
    • Schema-less data
    • Multiple-table
    inheritance
    

    View Slide

17. your database is

    probably not

    an implementation detail

    of your application
    

    View Slide

18. View Slide