Views keep your SQL queries DRY

A SQL view is a SELECT statement stored in the database which can be used like a table. So... what? Well, some of the benefits include:

1. Syntax verified before deployment -- avoids errors from queries built at runtime.
2. The RDBMS knows to expect it and could prepare an execution plan.
3. Force users to use a view which excludes sensitive table data.
4. Centralize important, common logic.

Number 4 is the strongest argument. It is the Don't Repeat Yourself argument for data. Duplicate code stinks! The fix is defining and calling "a single, unambiguous, authoritative representation within a system" (src). So why not treat data that well? Here's a silly example (in PostgreSQLish):

CREATE TABLE pantry (food TEXT, course TEXT, expiry DATE);
INSERT INTO pantry (food, course, expiry)
    VALUES ('crumpets', 'breakfast', now() + interval '2 weeks');
INSERT INTO pantry (food, course, expiry)
    VALUES ('canned ravioli', 'lunch', now() + interval '2 years');
INSERT INTO pantry (food, course, expiry)
    VALUES ('instant noodles', 'dinner', now() + interval '2 years');
INSERT INTO pantry (food, course, expiry)
    VALUES ('fruitcake', 'snack', now() + interval '2000 years');

Imagine these meal planning queries:

• SELECT food FROM pantry WHERE course = 'breakfast' AND expiry > now();
• SELECT food FROM pantry WHERE course = 'lunch' AND expiry > now();
• SELECT food FROM pantry WHERE course = 'dinner' AND expiry > now();
• SELECT food FROM pantry WHERE course = 'snack' AND expiry > now();

Well, that's pretty gory. No abstraction at all -- mystery meat queries which need unnecessary work to be understood. Two things need to be abstracted: course and expiry. No one wants stale food, so build that logic first:

CREATE VIEW fresh_food AS
    SELECT *
    FROM pantry
    WHERE expiry < now();

Voici! The per-course queries look like: SELECT food FROM fresh_food where course = 'foo'. But, why stop abstracting now? Why not this?

CREATE VIEW breakfast_menu AS
    SELECT *
    FROM fresh_food
    WHERE course = 'breakfast';

This example is almost too silly, but one more good point can be squeezed out yet. A key benefit of centralized logic is having one place to make bug fixes. For example, the typo in fresh_food which returns stale food. Fix the view DML and all client code immediately benefits without change.

Unfortunately in practice performance can easily suffer when using (and especially layering) views.

Coming soon -- part 2: Views made my queries SLOW!