'View Only' Database Schema Design

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…)

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.

1 Like

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…

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.

1 Like

Good pts.

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

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.

4 Likes

I can see this approach being used really well with a database that has very sensitive data in it. When I was working with some health data, security and privacy had a close eye around the database calls and I think this approach actually would have helped ensure smooth interaction between developers and security and privacy.

So I’m plus one on this, but only if you absolutely need it due to the nature of the data. If anyone executes a non-view specific query, you can easily raise red flag. You can also instill company practices with data and make an easy to manage DB call list just by the views.

This is a very odd way to use a database, but yeah if the nature of the app requires it, you use it. If you’re not aiming to be compliant with anything or your just being clever for the sake of it, it seems silly to me. To a security and privacy officer, this could be powerful, but I cannot think of anything else.