4 ms·

Ask HN: What's wrong/right with Postgres migrations?

• ianpurton 3y ago
  I use dbmate and for a deployment I package up the migration files into a docker container which runs and then applies the changes. Firstly I use a devcontainer for development so I know my versions line up. dbmate uses .sql files which also makes things a lot easier. You can see my setup here https://github.com/bionic-gpt/bionic-gpt https://github.com/bionic-gpt/bionic-gpt Have a look at the CONTRIBUTING.md to get an idea of the dev workflow.
• sethammons 3y ago
  > because we rewrote the history Don't rewrite shared history. As for how migrations are ran. Last place, each team/service had their own data store. Numbered sql files and forward compatible changes only. Sometimes this meant application code would have to write to two locations and in a later update change the read location. Current gig, everything is managed by the django orm. Great when you have a single db; sucks to scale out to sharded tables, sharded db instances, and is a pain for migrating to new data stores.
• sergiotapia 3y ago
  I never experienced anything like this in Rails or Phoenix (Ecto) land. The only time I had problems with database migrations were in javascript with Prisma. But that was because we had a local dev dataset, and the CEO had gone into past migration files and edited them manually. Prisma exploded every time and it was a pain to fix. It sounds like your pains are also coming from people manually editing past migration files and committing them. Don't do that!
• konha 3y ago
  > How are teams of size >2 handling this? Directory with .sql files starting with a number. Each file contains a set of changes to the db. The db has a table with the number that was applied last. To migrate your db you check if you have a file with a number that is higher than the one in the db. Then you apply that file to your db. That’s it. Sounds like you are working in a way that is not intended by your tool / framework.
  • ozozozd 3y ago
    Assume you and your teammate made db changes and generated migrations in your respective topic branches. Both migrations refer to the latest revision, as it should be. How do they merge? How does the migration tool know which migration to apply first? Edit: You are right about us possibly not using the tools in the intended manner. But having to check the latest migration and check that with my local db seems a little bit error-prone and cumbersome, no?
    • konha 3y ago
      > How do they merge? Manually. When you merge your code back into a common branch you look at the existing migrations and the new migrations and make sure the numbers make sense (after the merge). You can avoid stepping on each other’s toes by using a timestamp for the number in your filename. You still have to make sure (when you merge) the order makes sense. > How does the migration tool know which migration to apply first? Migrations are ordered by the (ascending) number in the filename. (Or some other scheme, but this is common) > But having to check the latest migration and check that with my local db seems a little bit error-prone and cumbersome, no? The number in the database should never be higher than the highest numbered file in source control. So you can increment without looking.
    • ponyous 3y ago
      We use timestamps in file names. Before you merge you have to catch up your branch (or it gets automatically updated). CI runs the migrations, so if something doesn't work you will know it.
  • foobarbaz33 3y ago
• ddorian43 3y ago
  You can merge heads on alembic, nothing special. You just need tests for running migrations and its "just" like code conflict when merging.