Re:Pgmigrate: Write migrations using plain SQL
by paulryanrogers on Tuesday December 14, 2021 @05:03PM
(#29558621)
I did something similar leveraging SQL table and DB comments. In hindsight a migrations table would've been easier to understand and maintain. Still I could see an incrementing counter working for resource constrained projects.
Re:Pgmigrate: Write migrations using plain SQL
by DenisM on Tuesday December 14, 2021 @05:03PM
(#29558625)
Do you prepare your rollback snippets in advance? If not, how do you deal with urgent rollbacks? If yes, how do you make sure those snippets work in advance?
Re:Pgmigrate: Write migrations using plain SQL
by bob1029 on Tuesday December 14, 2021 @05:10PM
(#29558738)
The SQLite usage is very tightly integrated with the software. Any urgent rollback is effectively a roll-forward fix of whatever broken software is in production.
Re:Pgmigrate: Write migrations using plain SQL
by DenisM on Wednesday December 15, 2021 @08:53AM
(#29565914)
When I say urgent rollback I mean rollback within 30 seconds of realizing something went wrong. Do you have this capability?
Re:Pgmigrate: Write migrations using plain SQL
by megous on Tuesday December 14, 2021 @05:18PM
(#29558849)
Shouldn't you do DB updates in production in a way that N backend always works at least with N+1 schema? So you just rollback the backend...
Re:Pgmigrate: Write migrations using plain SQL
by DenisM on Wednesday December 15, 2021 @10:49AM
(#29567610)
You could do it that way, but I don't have the need, so my way leads to faster dev cycle: https://news.ycombinator.com/item?id=29566574
Doing as you say requires two deployment for each change with a pause in between, and likely two separate merges (does it?). This reduces the deployment velocity. I might have to do as you describe when I start scaling out - there is no hope of updating many servers in sync so might as well embrace multi-version.
Re:Pgmigrate: Write migrations using plain SQL
by developuh on Tuesday December 14, 2021 @09:14PM
(#29561208)
Can you please explain this like I'm 5. I am still learning and I would like to understand how to do upgrades in a better way.
Re:Pgmigrate: Write migrations using plain SQL
by Gadiguibou on Tuesday December 14, 2021 @09:37PM
(#29561393)
I'm not the original commenter, nor am I experienced with upgrading in general, but what I understand from the comment is that you always make sure that your current schema is compatible with the previous backend.
For example, this could mean that you don't delete any column that were still in use in the previous backend version.
This would have the advantage of letting you rollback your backend alone if anything wrong happens.
Fun fact - you don't even need to maintain a special unicorn "_migrations" table or any other external state to keep track of things with SQLite migration. You can simply utilize the user_version pragma:
https://sqlite.org/pragma.html#pragma_user_version
We have a DatabaseVersion constant in the classes that own each type of SQLite database, and all they have to do at ctor time is query the database for current version and run a for loop over the difference to execute the required migration scripts.
Our migrators are one-way (we don't define a matching 'down.sql'). We would push new code to back something out of a SQL schema and then increment our counter just like if we added something new. Decrementing/skipping is disallowed since this would cause information loss on the migration path. Having a monotonic version number per type of database makes it super easy to keep everything on rails for us.
For database access, we also use raw SQL via Dapper.