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
