'View Only' Database Schema Design

api
databases
graphql

#1

Does it make any sense, to limit database read-access to a bunch of views?

The views could be versionable etc. We could add fields, but never change / remove them.

We would guarantee each view remains stable.

Underneath the views, we would have some basic set of tables, which can be changed / optimised etc. as long as the top level views remain the same.

Is this a common approach?

(Unsure how to keep updates / inserts stable, but that’s another story…)


#2

If you have specific requirements why you need this done sure why not. You can handle updates/insert/deletes either via updatble views if your schema meets the limitations imposed or via triggers (in PostgreSQL). In general having db as external API is not a very good approach.


#3

ah, of course updateable views! recently I was doing funky things with a view, which meant I lost the updateability - but in general, you are totally right!

I suppose my idea was not to have the views as an external API, but more akin to an internal, reliable API for the backend…


#4

That works but I think more used by teams where DBAs are a separate function. If you want to make this really tight a good approach is have you App user only have execute permissions on stored procedures and encapsulate all the db access logic there this has security benefits and provides a flexible stable interface decoupled from your schema.


#5

Good pts.

I suppose making DB ‘APIs’ explicit cannot hurt (too much)…


#6

We did this approach for a while and having to upgrade the views in a migration when things changed was untenable. It was a lot of noise for little gain. We instead override Ecto's from and have it select only the relevant schema fields in the base query function, and do select_merges for derived fields, but you get into weird issues there with the association filtering (especially in Ecto 3) and preloads also needing the same info. It can be worked around, but you have to engineer it, you don’t get as much from Ecto for free as I would prefer there. But it IS doable. We maintain _v1, _v2, etc versions of our resources which are discrete resources, and you also need to collect these versions, and also variants (admin are users where is_admin = true, type thing), because when you say you relate to users, you often want users of all kinds, and manually updating all the places where it was referencing _vN is onerous. That puts you into compiler deadlock territory though, so you have to work around THAT problem. It’s doable, but you start getting into DSL building at this point, to tame all of this complexity which is a very far place from where you started. But you can do it.