Pro Yearly is on sale from $80 to $50! »

Use your database for… Validations! Caching! Logic!

Use your database for… Validations! Caching! Logic!

241eb3a089132c5a0c65e765558a6735?s=128

Arjan van der Gaag

January 27, 2016
Tweet

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