Search Postgresql Archives

Re: Thoughts on "Love Your Database"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, 3 May 2016 23:11:06 -0500, Guyren Howe <guyren@xxxxxxxxx>
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



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux