Use your database for… Validations! Caching! Logic!

Transcript

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

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

3. INSERT INTO votes (user_id, link_id) VALUES (1, 2) ON CONFLICT

   DO NOTHING; Simple upsert

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

5. class User < ActiveRecord::Base default_scope -> { where(deleted_at: nil) }

   scope :account_managers, -> { where(role: 'account_manager') } end

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

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

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

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

10. class User < ActiveRecord::Base validates :email, presence: true, length: {

    maximum: 250 } end

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

    Constraints

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

    Constraints

13. ALTER TABLE users ADD CONSTRAINT approved_role CHECK (role IN ("public",

    "admin")); Constraints

14. ALTER TABLE users ADD CONSTRAINT email_format CHECK (email ~* '\A([^@\s]+)@((?:[-a-

    z0-9]+\.)+[a-z]{2,})\Z'); Constraints

15. create_table :articles do |t| t.string :title t.text :body t.string :tags,

    array: true t.jsonb :props, default: '{}' end Complex datatypes

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

17. your database is
 probably not
 an implementation detail
 of your

    application
18. None