$30 off During Our Annual Pro Sale. View Details »

Use your database for… Validations! Caching! Logic!

Use your database for… Validations! Caching! Logic!

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…

    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