What if your (read) DB is just a projection?
Database Migrations
31–40 of 69 posts
Re: Database Migrations
#32Earlier quoted context omitted.
If you are using Postgres, I think you can create a view for each table and put all the views in a schema, then you switch the app all at once by using `SET search_path TO`.
You can, but I'd argue if your API schema is just 1-to-1 views for the underlying tables, you're not getting much value from this approach.
Until you change the underlying tables, or the api schema. At which point you don’t need to change the other.
Re: Database Migrations
#33Two additional rules I don't see followed often, but made a past life of mine much easier: 1. rollbacks are bullshit, stop pretending they aren't. They work fine for easy changes, but you can't rollback the hard ones (like deleting a field), and you're better off getting comfortable with forward-only migrations 2. never expose real tables to an application. Create an "API" schema which contains only views, functions,…
I think #2 is a bit dated approach by now. Certainly there's a place for this approach, when you have a DBA who is acting as gatekeeper to a large DB and the application is stable and is probably a monolith, but this adds friction and comes at a cost. It also tends to encourage a style of design where more business logic goes into the DB instead of stateless services, which eventually bottlenecks your ability to scal…
I last applied this approach a few years ago at an IIOT startup, where we had billions of rows of sensor data. We needed it all in one place, but with a fairly varied set of users, like an application team showing customers various slices of their data and a data science team applying all kinds of analytics to the data.
When I showed up we had a single Postgres database with a half-dozen different clients. One managed the migrations in addition to doing real work, and the others were a mix of "direct" users and API layers used elsewhere. Tons of overlap, tons of repeated logic, tons of inconsistency.
I used this approach to first stem the plague of direct DB access, and later to consolidate our APIs. It worked pretty well, and the folks I left it to seemed to appreciate my approach.
We did have a lot of debates about what is or isn't "business logic", what belongs in this DB or not. We tried to keep it pretty light; our raw data was many independent tables, and our API schema had functions for insertions and views to make that data usable in ways that didn't feel very application-specific.
I was pushing to replace our handwritten API service(s) with something like Hasura, though that didnt happen before I left. I think that's where this approach gets more powerful: manage your data with your database, get a web API for free. Then your data store is encapsulated behind a singular service with a well-defined API.
You're right that it requires some serious attention to the DB. We wrote all migrations in PL/pgSQL (managed with sqitch), giving us a level of control that is hard to get from a language-specific migration framework. That required a lot of upskilling, but I think it was worth it for us.
Re: Database Migrations
#34Two additional rules I don't see followed often, but made a past life of mine much easier: 1. rollbacks are bullshit, stop pretending they aren't. They work fine for easy changes, but you can't rollback the hard ones (like deleting a field), and you're better off getting comfortable with forward-only migrations 2. never expose real tables to an application. Create an "API" schema which contains only views, functions,…
Former DBA here turned Python dev (flask mostly) and now Data Engineer: 1 million percent this:
“ 1. rollbacks are bullshit, stop pretending they aren't. They work fine for easy changes, but you can't rollback the hard ones (like deleting a field), and you're better off getting comfortable with forward-only migrations”
Think of database migrations like you would versioning an API, deprecate and then drop old columns only after declaring a breaking change and giving consumers time.
Or take the Google approach where the protobufs contain all the fields from the start of time or something along those lines so that old applications can still work and the service devs can choose to migrate as needed.
But yeah re: point 1 databases are best thought of like the arrow of time, just evolve forward not backwards and you’ll be fine and not lose data or worse.
Re: Database Migrations
#35Two additional rules I don't see followed often, but made a past life of mine much easier: 1. rollbacks are bullshit, stop pretending they aren't. They work fine for easy changes, but you can't rollback the hard ones (like deleting a field), and you're better off getting comfortable with forward-only migrations 2. never expose real tables to an application. Create an "API" schema which contains only views, functions,…
Former DBA here turned Python dev (flask mostly) and now Data Engineer: 1 million percent this: “ 1. rollbacks are bullshit, stop pretending they aren't. They work fine for easy changes, but you can't rollback the hard ones (like deleting a field), and you're better off getting comfortable with forward-only migrations” Think of database migrations like you would versioning an API, deprecate and then drop old columns…
> Think of database migrations like you would versioning an API, deprecate and then drop old columns only after declaring a breaking change and giving consumers time.
This ties into my second point as I eventually made an "api_v2" database schema and migrated users as you describe.
Re: Database Migrations
#36What I want are state based migrations which are smart enough to handle things which are dependent on others.
Re: Database Migrations
#37Two additional rules I don't see followed often, but made a past life of mine much easier: 1. rollbacks are bullshit, stop pretending they aren't. They work fine for easy changes, but you can't rollback the hard ones (like deleting a field), and you're better off getting comfortable with forward-only migrations 2. never expose real tables to an application. Create an "API" schema which contains only views, functions,…
The amount of times I've been asked to treat a database like any other stateless microservice is crazy. I've been asked to support time machine like behavior where a user can deploy any version of the database (given a list of schemas and other database config variables) and expect it to work even if they were moving from v9 to v3 of the config.
Re: Database Migrations
#38Earlier quoted context omitted.
If you are using Postgres, I think you can create a view for each table and put all the views in a schema, then you switch the app all at once by using `SET search_path TO`.
You can, but I'd argue if your API schema is just 1-to-1 views for the underlying tables, you're not getting much value from this approach.
I was thinking of it as the starting point, then you evolve from there.
Tbh, views are a bit of a leaky abstraction (for example when it comes to constraints) so adopting them gradually seems like a good idea.
Re: Database Migrations
#39Two additional rules I don't see followed often, but made a past life of mine much easier: 1. rollbacks are bullshit, stop pretending they aren't. They work fine for easy changes, but you can't rollback the hard ones (like deleting a field), and you're better off getting comfortable with forward-only migrations 2. never expose real tables to an application. Create an "API" schema which contains only views, functions,…
I always wonder why this pattern is not more popular: https://sive.rs/pg
We try to put a clean API on everything, except the one place where it really matters...
Re: Database Migrations
#40Earlier quoted context omitted.
I think GP was talking about a database-level API specifically
I was, but this approach makes it function as a single application, kinda. You're not exposing your underlying data storage directly to consumers. The weird thing here is your interface is still SQL. Sometimes that's fine, but I think this API-schema approach really takes off when you use something like PostgREST, PostGraphile, or Hasura to automatically turn this API schema into a web API. Lots of nice benefits to t…
> I actually discovered this API-schema approach from the docs of one of those tool...I just can't remember which one.
Maybe from https://postgrest.org/en/v10.2/schema_structure.html