Re: Thoughts on "Love Your Database" - Mailing list pgsql-general

Good morning

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Guyren Howe
> Sent: Mittwoch, 4. Mai 2016 06:11
> To: pgsql-general <pgsql-general@postgresql.org>
> Subject: [GENERAL] Thoughts on "Love Your Database"
>
> 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.
>
>
> 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?

If you have a complex design or if the processes require the modification of various tables within a transaction you
mayprobably prefer to expose functions as the application interface. Advantages of this approach: 

- Hide complexity: You don't need to explain all the details, dependencies and implications to all web developers. Just
makesure that your documentation is up-to-date for those who want to learn about it. 
- Transactions are controlled by the database: You may have doubts if application developers do handle this correctly.
- Minimize the impact on application development: If changes to requirements force changes in the database, these would
betransparent to the application. Even if the interface changes, that may mean only an additional argument to a
function.
- Security: You can grant execute on (security definer) functions instead of granting privileges for each object. The
lattercan become quite complex. 
- Separation of concerns: Application developers don't need to (but can if they want) learn SQL. They should focus
insteadon the presentation layer, which at the end is what customers see and sells. 

Bye
Charles

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