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

51–60 of 69 posts

Re: Database Migrations

#51

Post by claytonjy »

steve-chavez wrote: post #40
claytonjy wrote: post #28

Earlier quoted context omitted.

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

Yes, I think that was it! Interesting to see they have removed that page in more recent version of the docs.

Re: Database Migrations

#52

Post by claytonjy »

rewmie wrote: post #50
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,…

Great post, thanks for the insight. I have a question regarding this point: > 2. never expose real tables to an application. Create an "API" schema which contains only views, functions, procedures, and only allow applications to use this schema. Would a view-based indirection help with rollbacks? For example, in scenarios where a column is added/dropped, would it work to just join with a new relationship column? Roll…

Yes, this absolutely works. Rather than viewing it as a rollback enabler, I think of it as a way to try things while keeping an escape hatch. Instead of deleting a column from a table, you can remove it from the view, and only drop it from the table when you're really sure that's safe.

But dropping a column from the view is an API-breaking change, so rather than updating the existing view I'd make a new view, possibly in a new API schema (e.g. "api_v2"). Then you migrate clients/applications to the new view, and only when nothing uses the old view would I drop both the old view and the column from the underlying table in new migrations.

Re: Database Migrations

#53

Post by steve-chavez »

claytonjy wrote: post #51
steve-chavez wrote: post #40

Earlier quoted context omitted.

> 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

Yes, I think that was it! Interesting to see they have removed that page in more recent version of the docs.

It wasn't removed, just badly reorganized. It's here now: https://postgrest.org/en/stable/explanations/db_authz.html#s...

Re: Database Migrations

#54

Post by ivancho »

jbverschoor wrote: post #48
ivancho wrote: post #46

Earlier quoted context omitted.

SQL is the clean interface. Databases are not designed to run your extra business computations, they are there to store and give you access to your data with efficiency and some guarantees. DBMS' are impossible to debug, cannot be shut down like you would with a faulty service node, and locking/crashing the production one can grind your entire company to a halt. The above pattern seems insane to me.

SQL is specifically designed for "business" computations.

And is wildly misused. (edit: I meant "extra business logic" above, nothing to do with businesses). You commented above with the same thing I said - turns out multiuser DBs are not good for running expensive code beyond the data access stuff, because it interferes with their main job, in ways that are hard to diagnose or mitigate. Aggregation for business computations is fine, cleaning up text with regular expressions is borderline, and procedural code is a disaster waiting to happen. Putting a bunch of functions to hide the SQL in the DB itself to me seems to go against basic norms of how code is developed and deployed as application logic evolves, and not good architecture from risk standpoint either.

Re: Database Migrations

#55

Post by montroser »

klysm wrote: post #41
montroser wrote: post #8

Such is life. This perspective seems coming from an application developer who is seeing the database as an extension of the app. When you're big enough to have a database ops team, they will come with a different perspective, and most likely be highly skeptical of storing schema changes in the database. If your're not too cool for MySQL, check out skeema.io for a declarative, platform-agnostic approach to schema mana…

Your comment is written like you have a undisclosed stake in skeema.io

Nope, it just made my life better and I want others who feel the pain in TFA to know there are options.

Re: Database Migrations

#56

Post by JoBrad »

mslot wrote: post #39
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 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...

I really hate the db-as-code paradigm. It leads to magic that is invisible to the app layer, and leads to a lot of unintended consequences.

Re: Database Migrations

#57

Post by btown »

perrygeo wrote: post #18
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.

Something as simple as a field rename can result in downtime done naively, and showing a candidate a badly named field and asking them what they’d do to fix it can be quite illuminating!

Re: Database Migrations

#58

Post by Kinrany »

claytonjy wrote: post #28
Kinrany wrote: post #14

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

The problem I see is that tables and views are a poor general purpose interface: if you don't know what the indexes are, you can't guarantee reasonable performance.

Re: Database Migrations

#59

Post by evanelias »

montroser wrote: post #55
klysm wrote: post #41

Earlier quoted context omitted.

Your comment is written like you have a undisclosed stake in skeema.io

Nope, it just made my life better and I want others who feel the pain in TFA to know there are options.

Thank you for spreading the word, I greatly appreciate it. Building a bootstrapped product/business in the MySQL space has been quite challenging.

Most newer entrants in the schema management space are VC-funded, and went wide instead of deep in terms of the range of supported DBs. I personally believe in deep expert-level coverage of a specific DB, resulting in better functionality and a safer schema management toolchain. However it does make word-of-mouth more challenging, since MySQL is unpopular here. So often these blog posts don’t mention Skeema, since blog authors coming from other DBs haven’t ever encountered it.

Re: Database Migrations

#60

Post by brainbag »

montroser wrote: post #8

Such is life. This perspective seems coming from an application developer who is seeing the database as an extension of the app. When you're big enough to have a database ops team, they will come with a different perspective, and most likely be highly skeptical of storing schema changes in the database. If your're not too cool for MySQL, check out skeema.io for a declarative, platform-agnostic approach to schema mana…

I'm not too cool for MySQL, but PostgreSQL has been the default anywhere I've been in fifteen years. Is anybody aware of something equivalent for pg? I see a lot of old projects that aren't getting updates anymore.
Post reply on HN