Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

More Decks by Arjan van der Gaag

Other Decks in Programming

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