How to run river migrations along with the "main" app's migrations?

[ribtoks]

Hi

Thank you for developing river!

I have a "default"/"main" app (web server) that is running it's own migrations from go:embed-ed sql files using golang-migrate. Is there any best practices of integrating with river migrations in such case? I'm interested only in Go API of river's migrations. Main app is also using pgx pool.

Particular areas where I'm a bit confused:

• any possible conflicts with "latest migration" version saved to DB (as of now, since river has custom migrations, seems safe, but still)
• can I reuse the same dbxpool instance from running "my" migrations and pass it to river? (this may be a stupid question, just in case)
• I did not find an example in docs of running migrations and creating a new client in the same app. Is it somehow discouraged?

[brandur]

• any possible conflicts with "latest migration" version saved to DB (as of now, since river has custom migrations, seems safe, but still)

I'm not sure if I completely understand this question, but I don't think so. Migrating multiple times will end up with the same result because the latest migration state is read from the database before any new ones are applied.

• can I reuse the same dbxpool instance from running "my" migrations and pass it to river? (this may be a stupid question, just in case)

Yep, that's fine.

• I did not find an example in docs of running migrations and creating a new client in the same app. Is it somehow discouraged?

It won't cause any trouble straight away, but often running migrations on start up can lead to some fringe problem, which is why a lot of people don't do it. For example:

• When you have multiple running instances of an app, they'll all try to migrate on start up when the fleet is restarted. This might be okay, but it is possible that two running changes at exactly the same time will conflict with each other and one will error.
• In case a roll back is necessary due to some problem, that can be difficult since no one is familiar with how to use the migration tooling.
• This isn't the case yet, but it is possible that a "harder" migration needs to be introduced in the future. For example, say we add a new index at some point, building it on a very large existing job table will be an expensive operation. Presumably we'll add CONCURRENTLY on it and hopefully everything would be okay, but it'd still be a long lived background operation that someone may want to monitor.

[ribtoks]

Hi Brandur. Thank you so much for a fast reply!

I'm sorry for being unclear with the first question. What I meant was that golang-migrate stores a migration version in, let's say, golang-migrate table (exact name does not matter). So my question was if river used golang-migrate too (which it isn't, at this time) migration versions of the app and river could have collided. And if there was any kind of a "best practice" in eliminating such possibility "by design"? (e.g. intentionally using a different schema etc.)

As to your response to my 3rd question. I thought migrations are done in a transaction so there will be no conflict by design (only 1 worker of many will do the actual migration and others will observe the new state after the lock is released). So what is then the recommended way to approach such worker migrations, if not on a start-up? Shut down old workers, migrate manually (with river-cli) and then start the workers again? Or there's any other way?

P.S. My use-case is that the app is a monolith. I'm not planning to have separate pool of workers. Servers (plural) will be running scheduled distributed cron jobs, so I needed to execute each job only once, as they are I/O intensive on the database(s).

[bgentry]

I'm sorry for being unclear with the first question. What I meant was that golang-migrate stores a migration version in, let's say, golang-migrate table (exact name does not matter). So my question was if river used golang-migrate too (which it isn't, at this time) migration versions of the app and river could have collided. And if there was any kind of a "best practice" in eliminating such possibility "by design"? (e.g. intentionally using a different schema etc.)

Not sure if you've checked out the migration docs, but we have examples in there on how to extract the raw SQL for using with your migration framework of choice, or even run them within Go code. As for table name collisions, all tables added by River have a river_ prefix which makes collisions unlikely. And while we are hoping to add proper support for multiple Postgres schemas, we are still somewhat blocked by sqlc in this regard and so support there is a bit limited at the moment.

As to your response to my 3rd question. I thought migrations are done in a transaction so there will be no conflict by design (only 1 worker of many will do the actual migration and others will observe the new state after the lock is released).

It's true that migrations are generally run in a transaction although it's possible we will add migrations which can't be done in a transaction (canonical example of this is CREATE INDEX CONCURRENTLY). But even without that possibility, it's still not ideal to have every one of your app processes trying to run migrations at start time (assuming you have more than one). Two processes may attempt to start migrations at the same time and only one will succeed while the other will error. The erroring one could likely just try again and succeed with a no-op, but this whole process increases boot time and causes unnecessary contention.

So what is then the recommended way to approach such worker migrations, if not on a start-up? Shut down old workers, migrate manually (with river-cli) and then start the workers again? Or there's any other way?

Typically, most deployments will have a way to run a pre-deploy script just once before rolling out all new versions of that process. That's what I'd recommend if your setup allows for it. We will take all possible measures to avoid backward-incompatible migrations and will hopefully never have them, which generally means you can run a new migration while the old code is still running before then launching your app using the new code.

[ribtoks]

Thank you for taking time to write that detailed answer! It does answer my concerns.

Yes, I did read the migration docs regarding the raw SQLs, but I would want to avoid that as it seems "too intrusive" for me (e.g. going too much into taking over river's operation)

[bgentry]

I wouldn’t look at it that way. If you already have a system for managing your migrations and feel more comfortable integrating River’s migrations into it (for better control, consistency, etc) you should definitely do that. We felt we needed to provide a variety of options because the Go ecosystem does not have one overwhelmingly popular option for managing migrations.

Honestly I wish there were one popular and widespread choice here, it would enable us to spend less time dealing with a problem that is not at all unique to River!