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

11–20 of 69 posts

Re: Database Migrations

#11

Post by zzzeek »

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

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.

Re: Database Migrations

#13

Post by tudorg »

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.

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`.

Re: Database Migrations

#14

Post by Kinrany »

charcircuit wrote: post #9
Kinrany wrote: post #7

Earlier quoted context omitted.

Rather than 2, isn't it better to let a single application own the database and manage the schema?

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

I think GP was talking about a database-level API specifically

Re: Database Migrations

#15

Post by spikej »

zzzeek wrote: post #11
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,…

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.

Re: Database Migrations

#16

Post by claytonjy »

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.

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.

Re: Database Migrations

#18

Post by perrygeo »

simonw wrote: post #3

This is really good. It covers one of the most common things people miss with regards to running migrations: it isn't possible to atomically deploy both the migration and the application code that uses it. This means if you want to avoid a few seconds/minutes of errors, you need to deploy the migration first in a way that doesn't break existing code, then the application change, and then often a cleanup step to compl…

Great interview topic, I agree. Candidates should be able to identify any SQL DDL that might break an app, then decompose it into three safe steps as you've described. It's a core skill for working with an RDBMS but rarely explicitly taught.

Re: Database Migrations

#20

Post by claytonjy »

jalino23 wrote: post #10
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,…

how does this work? you have the "real" database and this "api" database makes queries to the real database? how does that work?

In postgres, every object (table, index, function, view, etc.) lives in a "schema", which is better thought of as a namespace. I put low-level objects like tables into one or more schemas, then create an "API" schema with views, functions, procedures that operate on tables in other schemas. Then I only grant access to that API schema to the application users.
Post reply on HN