7 ms·
Pgroll – Zero-downtime, reversible, schema changes for PostgreSQL (new website)
- colemannerd 2y agoAre migrations still specified in json?
- tudorg 2y agoYes, but we're working on a converter from DDL sql to pgroll json. The reason for JSON is because the pgroll migrations are "higher level". For example, let's say that you are adding a new unique column that should infer its data from an existing column (e.g. split `name` into `first_name` and `last_name`). The pgroll migration contains not only the info that new columns are added, but also about how to backfill the data. The sql2pgroll converter is creating the higher level migration files, but leaves placeholder for the "up" / "down" data migrations. The issue where sql2pgroll is tracked is this one: https://github.com/xataio/pgroll/issues/504 https://github.com/xataio/pgroll/issues/504
- colemannerd 2y agoThank you so much! I’ll be watching
- solatic 2y agoI completely understand the need for a higher-level language above SQL, but straight JSON is a deal-breaker. It's not just comments, it's also that editors won't understand that JSON should have syntax-highlighting to help people catch trivial typos. A configuration language that allows for importing a file as a string would allow users to write the SQL in files with .sql extensions and get syntax-highlighting. pgroll is written in Go, so if you were to accept configuration written in CUE, you would get the best of all worlds: * Besides support for comments, there's first-class support in Go for writing configuration in CUE and then importing it: https://cuelang.org/docs/concept/how-cue-works-with-go/#loading-cue-data https://cuelang.org/docs/concept/how-cue-works-with-go/#load... * SQL can written in files with .sql extensions and embedded in CUE with @embed: https://cuelang.org/docs/howto/embed-files-in-cue-evaluation/ https://cuelang.org/docs/howto/embed-files-in-cue-evaluation...
- joelhaasnoot 2y agoLol, it's funny you need a higher level language and choose... JSON.
- mdaniel 2y agohttps://github.com/xataio/pgroll/issues/281 https://github.com/xataio/pgroll/issues/281 may be worth a :+1: even though it seems from tudorg's comment that they're really wedded to .json :-(
- tudorg 2y agoHa ha, we're not wedded, I was only explaining why a .sql file is not quite enough. Using some json equivalent should be fine, thanks for pointing to the issue.
- jherskovic 2y agoThe website generally looks good, but the font in the headings is weird. What is up with the "d" in it?!
- tudorg 2y agoThis is the font, we wanted something a bit different :) https://unbounded.polkadot.network/ https://unbounded.polkadot.network/
- rtuin 2y agoThis seems like a great tool! I like how it "simply" works by putting a view on top of the actual table. This concept is widely used in the data engineering world (in dbt). There must be an easier way to write migrations for pgroll though. I mean, JSON, really?
- cpursley 2y agoHere's a few other migration tooling options for Postgres: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#migrations https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...
- brycethornton 2y agoCan you add https://github.com/shayonj/pg-osc https://github.com/shayonj/pg-osc? It's my favorite PG migration tool.
- shayonj 2y agoThank you Bryce <3
- deleted 2y ago[deleted]
- paulryanrogers 2y agoHow many have you used? At ORIS I wrote a Laravel wrapper for PTOSC and really miss it now that I'm back on PostgreSQL. Now I mostly use updatable views in front of modified tables then drop-swap things later once any transitional backfilling is done.
- brycethornton 2y agoHey Paul! Try out pg-osc if you haven't already. I use it quite a bit for this type of thing. https://github.com/shayonj/pg-osc https://github.com/shayonj/pg-osc
- konradb 2y agoCool new site! I got 'invalid invite' on the discord link from the front page, I only mention as I'd like to join it.
- tudorg 2y agoOops, fixing it now. This link should work: https://discord.com/invite/kvAcQKh7vm https://discord.com/invite/kvAcQKh7vm
- Dinux 2y agoIs this related to supabase? There seems to be a link?
- tudorg 2y agoActually no, this is a project by Xata. What link do you mean?
- ltbarcly3 2y agoThis seems nice at first glance, but lets think about it for a minute: Before: You had a .sql file and if you messed up you had to revert manually. Maybe you would pre-write the revert script, maybe your site is down if you mess up. It's super easy to understand what is happening though. Now: you use pgroll. An absolute heaping ton of magic is happening behind the scenes. Every table is replaced by a view and tables have tons of 'secret' columns holding old data. Every operation that made sense before (ALTER TABLE foo ADD COLUMN bar INTEGER NOT NULL) turns into some ugly json mess (look at the docs, it's horrible) that then drops into the magic of pgroll to get turned into who knows what operations in the db, with the PROMISE that it can be reversed safely. Since all software has bugs, pgroll has bugs. When pgroll leaves you in a broken state, which it will sooner or later, you are just FUCKED. You will have to reverse engineer all it's complicated magic and try to get it back on the rails live. You're trading something you understand and is simple, but maybe not super convenient, for something that is magically convenient and which will eventually put a bullet in your head. It's a horrific product, don't use it.
- erulabs 2y agoWhat is good for the goose is not necessarily good for the gander. Obviously for startups, you're 100% right. Just announce a brief downtime and/or do migrations after-hours. Keep it simple, no one will care if their requests timeout once every week for 30 seconds. If your company has hundreds of developers making changes across every timezone and downtime (or developers being blocked waiting for scheduled merge windows) costs real money or creates real problems other than optics, something like this or Vitess (MySQL) is definitely worth it. Engineering should not be a "one-size-fits-all" type of job, and while I do love postgres, my main gripe with the community is that the "keep it simple stupid" mentality persists well beyond its sell-by date in many cases.
- ltbarcly3 2y agoThe bigger and more important your company is, the less you should rely on a tool like this. You have more budget to invest in operations and less tolerance for being down for 3 days when a tool like this has a bug that takes your site down. You should hire DBA's and operations staff that understand how to apply db migrations in a safe way, and have them review them before and/or apply them during deployments. It's not hard to do manually, just a little bit of extra work (not that much extra). With a little bit of feedback engineers will learn the basics of how to write migrations that are safe and then the use case for this product is dramatically reduced anyway. You keep things simple because you need to be able to understand what is going on to work with it later. pgroll is inherently complex and poorly designed, but even if it wasn't it is still bad to use something that you can't reasonably correct the problems it causes when it breaks.
- rixed 2y agoPgroll shines if you are doing slow rollouts. Recently on the market for a tool to manage SQL migration patches with no need for slow rollouts, I reviewed many such tools and the one that impressed me was sqitch: https://github.com/sqitchers/sqitch https://github.com/sqitchers/sqitch So if you are interrested in this field and if Pgroll is not quite what you are looking for, I recommand you have a look at sqitch.
- lucideer 2y ago> if Pgroll is not quite what you are looking for If you don't need slow rollouts, what would you say the downsides of using Pgroll over Sqitch would be? (I've used neither, but I got the impression from the op that slow rollouts was a feature, not a requirement)
- rixed 2y agoWhat Pgroll offers does not come for free: it puts some constraints on your schema and what migrations you can perform. Most "automatic migration" tools I've seen are designed for the easy migrations ("horizontal" changes, ie adding/removing/altering columns). But a schema migration is not equivalent to a view and a pair of triggers in general, even if in practice it's often limited to that. (simplest if a bit contrived example I can come up with: imagine you have a short table with 3 columns and 4 lines, and you wish to use instead a transposition of this table with 4 columns and 3 lines) It's also why I'm dubious of "revert patches" in general. If there exist a revert patch for a migration, that's an easy migration. Sqitch can use a revert patch, like it can use a verify statement, but just for the convenience; it does not require them. Yet, 9 schema migrations out of 10 are easy ones that pgroll handle nicely. It's a bit like using an ORM : if you just need a DB to store objects manipulated only in your program, sure go ahead use an ORM; but if your DB is the core of your business then you'd better not let an ORM anywhere near your schema. At the end of the day, I'm under the impression that if one wants to handle the general case then one has to keep the whole previous DB and apps in one hand and the new DB and apps in the other, and transition customers from the former to the later. That's much less work if you don't need slow rollout.
- Arctic_fly 2y agoWe've looked into this tool a couple times, haven't adopted it yet. The main issue is compatibility with prisma, which we use for defining our db's schema and automating migrations. If there were a slick integration between the two, that would go a long way to convincing us to adopt. In the meantime, we'll go with convenience and (slightly, almost immeasurably) lower reliability. We haven't had downtime due to a migration in months!
- notpushkin 2y agoSame but with Alembic! I'm wondering how hard would it be to open pgroll up for third party migration systems.
- tudorg 2y agoYes, this is the biggest issue right now and we’re working on a solution. What we want to do is add the ability to generate the pgroll migrations based on the prisma generated migration files. Depending on the operation, you might need to add more info. This will work fairly generally, not only prisma.
- muratsu 2y agoUnrelated to the OP product but I’m curious how people are solving this issue on smaller scale with nextjs + prisma/drizzle. Do you just run the builtin migrate script with npm run?
- maxf 2y agoWe use a CI job that runs drizzle migrate whenever the schema files change. Drizzle doesn’t have rollbacks though. Replacing this process with pgroll as a sort of drop-in replacement would be nice. Then orchestrate everything with a couple of CI jobs, and done!
- polskibus 2y agoDoes anyone know of similar tool for MS SQL?
- radicalbyte 2y agoNot public.. I build something very similar a decade ago but that was part of a low-code system so had access to far more information than most databases (the full history of schema changes with a little bit of metadata over the change).
- cmcconomy 2y agoWe use liquibase for these purposes, but I welcome any tool that has people take their versioning and historical artifacts seriously!
- tianzhou 2y agoWe have been building such a tool as well, please check out https://github.com/bytebase/bytebase https://github.com/bytebase/bytebase
- deleted 2y ago[deleted]