Re: Thoughts on "Love Your Database"

On Tue, 3 May 2016 23:11:06 -0500, Guyren Howe <guyren(at)gmail(dot)com>
wrote:

>I've long been frustrated with how most web developers I meet
>have no idea how to use an SQL database properly. I think I'm
>going to write a book called Love Your Database, aimed at web
>developers, that explains how to make their apps better by
>leveraging the power of SQL in general, and Postgres in particular.
>
>I'm thinking of a section on features of SQL most folks don't know
>about (CTEs are *way* to hell at the top of that list, but also
>EXCEPT/INTERSECT and window functions), but much of the book
>would be about how to do things server side. Benchmarks showing
>how much faster this can be, but mostly techniques — stored
>procedures/triggers/rules, views.
>
>I asked a colleague about the advice I often hear stated but seldom
>justified, that one shouldn't put business rules in the database. He
>offered that server-side code can be hard to debug.
>
>I'm sure many here would love to see such a book published,
>maybe some talks on the topic given.

I think such a book would be wonderful. Unfortunately, I doubt many
web coders would take the time to read it.

You might want a chapter or 3 on Model-View-Controller ... where it is
appropriate and where it isn't. I've seen some truly spectacular
backflips done by code trying to shoehorn uncooperative data models
into MVC.

>What might I cover that I haven't mentioned? What are the usual
>objections to server-side code and how can they be met? When
>*are* they justified and what should the criteria be to put code in
>Postgres? Any other thoughts? Any other websites or books on
>the topic I might consult?

FWIW: I have a master degree in data modeling. I design databases,
and when necessary write web facing middleware for them.

The usual objection to stored code is highly conditional queries. For
example, my most recent web project has a search which is ~100 lines
of SQL with 7 CTEs, 5 of which are executed conditionally depending on
user input. This kind of dynamic code is painful to write in most SQL
dialects.

I compose such queries in middleware preferentially because I can use
languages better suited to complex string manipulation. And yes, I am
aware of injection: SQL may be composed dynamically, but user input is
/never/ spliced - it always is passed via SQL parameters.

I am aware that Postgresql has other languages available as
extensions. Some of them would do the job - though I think not as
nicely as my goto language: Racket (a Scheme dialect).
[Yes, I know Guile (Scheme) is one of the extension languages.]

The code wouldn't be any less complicated for being resident in the
DBMS, and I doubt it would be much faster: my middleware is always
either co-located with the DBMS, or on the same LAN if working with a
cluster.

I draw the line at giving web clients direct access to a database -
any web facing system I design always involves mediation via
middleware. IME it is the copying/conversion of data to/from the
HTTP interface that ultimately limits performance, so where to put the
database code largely is a judgement call.

YMMV,
George