I'm designing a small system that includes an API and a Database. On the API side I'm familiar with versioning the functions (Endpoints) to allow older clients to continue working.
I'm looking for advice to achieve the same effect for the database. Is there a way to "version" the database tables (or schemas), so to speak, to allow older applications to continue working? In this case the that will be the older versions of the API service that I'm maintaining to support older clients.
One thought that occurred to me is "create a versioned view for every table", so for example if the table is called
account I would have a view called
I obviously don't have a problem with adding new tables or even adding columns to tables (as long as they have default values or allow Null)
The biggest problem comes when splitting a table into multiple tables or when dropping columns.
A simple example would be taking a user's email address out of the user table because in version 2 you want to allow the user to have multiple email addresses. I'm quite new to views and triggers but I think you can set it up so that the older application still sees the email address in the table, and writes the email address to the new separate table.
I'm finding it terribly inconvenient to maintain views for every table, but this may be because I'm new to it and it will get easier? Or are there perhaps some other rules or methods that makes it easier to upgrade and maintain older versions so that older version of the application doesn't break? Is there an easy way to generate the views and triggers automatically because the initial View would basically mirror the table at that time. Later when the table is modified, I would then have to hand-fix the view(s) associated with that table to maintain a consistent view. And it seems to me that View version N-2 should refer to View version N-1 as it's base, with View version N-1 referring to View version N. View version N refers initially to the raw table but when the table is changed that view will be re-written.
FWIW I'm using Postgres 9.5