Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Database Migrations

vadimkravcenko.com

Post reply on HN
Print view View original

21–30 of 69 posts

Re: Database Migrations

#21

Post by paulddraper »

claytonjy wrote: post #4

Two 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,…

#1 is true. #2 is extremely tedious. I have a hard time imagining this is less work than an extra release.

Re: Database Migrations

#22

Post by tudorg »

This is a fantastic article! It shows that even simple migrations (like adding or removing a column) can be quite tricky to deploy in concert with the application deployement.

We (at Xata) have tried for a while to come up with a generic schema migration system for PostgreSQL that makes this easier. We ended up using views and temporary columns in such a way that we can provide both the "old" and the "new" schema simultaneously. Up/down triggers convert newly inserted data from old to new and the other way around. This also has the advantage the it can do rollbacks instantly by just dropping the "new" view.

We were just planning to announce this as an open source project this week, but actually it is already public, so if you are curious: https://github.com/xataio/pgroll

Re: Database Migrations

#24

Post by claytonjy »

klysm wrote: post #5
claytonjy wrote: post #4

Two 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’d really like to implement 2. but it’s quite difficult to make the switch to that approach when you already have a ton of tables.

It is difficult, but it's possible, and I've had to take that hard road.

At a high level it looked like

1. create API schema and start filling it out with useful stuff

2. one by one, migrate applications to the new schema. You'll find stuff missing from the API schema, and you'll add it, to support each application. Ideally you'll find commonality across applications, so you end up with a cohesive API schema and not a jumble of application-specific stuff. But this takes discipline!

3. as each application moves over, remove their access from the low-level schema(s)

Re: Database Migrations

#25

Post by claytonjy »

tudorg wrote: post #13
klysm wrote: post #5

Earlier quoted context omitted.

I’d really like to implement 2. but it’s quite difficult to make the switch to that approach when you already have a ton of tables.

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.

Re: Database Migrations

#26

Post by tianzhou »

Nice writeup. It's one of those things not taught in school and always learn in the hard way.

We at Bytebase also recognize this and have spent over 2 years to build a solution for team to coordinate the database migrations better.

Re: Database Migrations

#27

Post by throw__away7391 »

claytonjy wrote: post #4

Two 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 scale.

Re: Database Migrations

#28

Post by claytonjy »

Kinrany wrote: post #14
charcircuit wrote: post #9

Earlier quoted context omitted.

That is the point of 2. The single application is the API.

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 those tools, and then you have a single service that is more what users might expect.

I actually discovered this API-schema approach from the docs of one of those tool...I just can't remember which one.

Re: Database Migrations

#29

Post by zzzeek »

spikej wrote: post #15
zzzeek wrote: post #11

Earlier quoted context omitted.

agree with 1, hard pass on 2, I worked in a shop like that for a long time and as you end up doing joins and subqueries with the views, query performance goes to shit. the stored procedure part, (edit: on the other hand,) is a whole other level that I wont even get into.

I don't think that's what's meant by #2... About 12 years ago I worked at a place where all the crud operations were stored procs (calling tables/views/functions as needed). The application was only granted execute on the specific schemas. That meant no direct access for crud. A number of Schema changes and query optimizations could be handled by updating the stored procs without having to recompile the application.

joins and subqueries are part of SELECT statements, not crud. the purpose of using views is for the SELECT side of the application, not the CRUD side.

Re: Database Migrations

#30

Post by spikej »

claytonjy wrote: post #16
spikej wrote: post #15

Earlier quoted context omitted.

I don't think that's what's meant by #2... About 12 years ago I worked at a place where all the crud operations were stored procs (calling tables/views/functions as needed). The application was only granted execute on the specific schemas. That meant no direct access for crud. A number of Schema changes and query optimizations could be handled by updating the stored procs without having to recompile the application.

You're right, but I think GP has a point as well. Anything you expose to the application has a potential for misuse, and a lot of "misuse" of a DB means queries with bad performance. But, I'd argue the API schema gives you better control over this by pushing what might otherwise be application-level logic into the database. I've solved a lot of bad ORM behavior this way.

Both decent approaches and we reduced a lot of friction by using code generation.

Unfortunately, modern development favors having all logic on the application side. There are a lot of benefits that brings including better testing (stored proc testing frameworks never really caught on) however I find now a lot of people I work with rely too heavily on the ORM and don't know how to work with the databases (writing sub optimal queries, not properly indexing, etc)

Post reply on HN