[smac8]

At my last job we finally got our front-end devs to move all client side db work to calls to stored procedures or views. After doing so, nearly all of them fell completely in favor of this method. This is escpecially true with large, complex DBs or data warehouses, where the model is subject to change and reliance on back-end devs better knowledge of the database is more important.

That said, despite some of the tradeoffs - for example with testing, or migrating to a new db (which is extremely rare anyway) - it is ultimately far better for client code not to have to know back-end implementation. I think things like graphql are finally making this abundantly clear.

What I have been dying for is a real time DB BAAS that is ACID compliant, preferably relational, has a simple rest api, and allows me to write stored procedures that I can call from my client code. Horizon is probably the closest thing out there. Right now using firebase, and sick of the absurd amount of client side code I have to commmit to to pull data from various parts of the database. Requires huge amounts of overfetching, unecessary coupling of client-server code, horrible consistency support, overly loose type structuring, etc.

If somebody writes a postgres version of horizon I will pay big money to use it :)

[oomkiller]

Doesn't include everything that Horizon does, but it provides a good foundation for building it http://postgrest.com/

[smac8]

Cool, hadn't seen that. There is also postgraphql, which I have become a big fan of and is a great foundation:

https://github.com/calebmer/postgraphql

That said, the real-time aspect is what I'm really dying for, plus a company supporting a BAAS. The ease of firebase is so nice, and you can make really cool real time apps with it very fast. I'm not sure if switching to a relational model would make impose some technical limitation that json stores don't (besides obvious complexity). Postgraphql + socket.io is kind of what i'm thinking is the start

[ianbicking]

Some clever use of NOTIFY might help with real-time? It's basically publish/subscribe directly in Postgres itself, but I've never actually used it.

[smac8]

I had forgotten about this actually. Thanks for the reminder :) Curious if anyone has tried using this for real time apps and what their experience was

[oomkiller]

Unfortunately NOTIFY doesn't work across nodes.

[sheraz]

You might want to take a look at dreamfactory[1]. They have a nice app that wraps all manner of services to become a self-hosted MBaaS. I've used it to put a REST interface on my postgres database, views, and stores procs.

[1] - Dreamfactory.com

[quink]

> where the model is subject to change

You cite this as the primary reason, but how much easier would this have been without stored procedures and especially views? I would think harder if anything.

[smac8]

It can be the primary reason, but it is generally one of a few very good reasons (performance is often another big one, as this article points out).

I will say its importance is somewhat contextual though. The 3 principal contextual factors that make this reason significant are: (1) A large, complex database/model, (2) A rapidly evolving data model (such as during development or prototyping and fast business requirement changes, and (3) a clear division between back-end and front-end guys on your team. Those are the most important factors, but they are not the only ones. When these factors are significant, you really want to encapsulate DB access from client work. The client should simply declaratively say what they want, and this enforced contract is maintained by the back-end team while the back-end team is free to implement the details, presumably better than the front-end team could. For example, if a query initially involves just joining a fact to a dimension, a front-end user might think they could simply write this in their ORM. But, if the model changes and this later requires 4 or 5 other joins to the get the needed data (because the modeler or business or whatever decided it to be so), then the client code can remain as is while the back-end team can rewrite the implementation details, maintaining performance and correctness of implementation as needed.