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