Which migrations will up and down commands run?
Closed this issue · 8 comments
I use Flyway right now. Whether part of Flyway or just the way we use it, each migration file starts with a number and only the highest number will be run. This is especially annoying in a large team scenario. If we both grab the same number the first to submit will win!
In migrondi, which migration file(s) will the up and down commands run? Any that haven’t been run previously? Where is that info stored? The newest one based on its file name? Does the caller specify?
Hi there 😁,
When you create a new migration, the file always ends in <yourname>_timestamp.sql that's used to sort the migration order
when you run up migrondi will try to reach for the database's migrations collection and find which was the last migration ran, then from there on it will try to find the same timestamp in the disk and continue with whatever migrations were created after that, if you use the option -t <number> it will try to run that amount of migrations the same happens on down
the up/down migrations is basically a for loop and each statement inside the migration file gets wrapped inside a transaction operation, if the operation everything is committed to the database except for the last transaction attempt which is the one that failed
just to be precise I'll quote your questions
which migration file(s) will the up and down commands run? Any that haven’t been run previously?
yes, but only those after the last migration stored in the database, the "older" ones should already be in there so we ignore them
Where is that info stored?
Migrondi creates a table named migration in the selected database with the following columns
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
timestamp BIGINT NOT NULLThe newest one based on its file name?
the timestamp on its name is the determining factor
Does the caller specify?
currently, there's no way to do that
since your migration files are just SQL, I'd advise to check them in the repository where the software is being worked on, for large teams I think there could be a couple of things like two persons working on the same table, for example, the lower timestamp would be run first then the other if they happen to be the same timestamp somehow... it would depend on the .NET's sorting to determine which will be run first (like sorting an array with two number 1's), but overall there's no way (currently) to specify any kind of priority besides the timestamp if these were conflicting migrations one of them would fail for sure.
And I think that's mostly it, let me know if you have further doubts 😁
Just for reference, I'll add the output of the almost done dry-run option
[😏]~> dotnet run -p src/Migrondi -- up --dry-run true
[MIGRATION: SampleMigration] - [PARAMS: seq [[Name, SampleMigration]; [Timestamp, 1609005399101]]]
BEGIN TRANSACTION;-- ---------- MIGRONDI:UP:1609005399101 --------------
-- Write your Up migrations here
INSERT INTO migration(name, timestamp) VALUES(@Name, @Timestamp);
END TRANSACTION;
[MIGRATION: SecondMigration] - [PARAMS: seq [[Name, SecondMigration]; [Timestamp, 1609005759280]]]
BEGIN TRANSACTION;-- ---------- MIGRONDI:UP:1609005759280 --------------
-- Write your Up migrations here
INSERT INTO migration(name, timestamp) VALUES(@Name, @Timestamp);
END TRANSACTION;
Total Migrations To Run: 2
[😏]~> dotnet run -p src/Migrondi -- down --dry-run true
Total Migrations To Run: 0
[😏]~> dotnet run -p src/Migrondi -- up --dry-run false
Migrations Applied: 2
[😏]~> dotnet run -p src/Migrondi -- down --dry-run true
[MIGRATION: SecondMigration] - [PARAMS: seq [[Timestamp, 1609005759280]]]
BEGIN TRANSACTION;
-- Write how to revert the migration here
DELETE FROM migration WHERE timestamp = @Timestamp;
END TRANSACTION;
[MIGRATION: SampleMigration] - [PARAMS: seq [[Timestamp, 1609005399101]]]
BEGIN TRANSACTION;
-- Write how to revert the migration here
DELETE FROM migration WHERE timestamp = @Timestamp;
END TRANSACTION;
Total Migrations To Run: 2
[😏]~>
This sounds perfect. I’ll have to try it out. We run our migrations as part of CI. Since you do everything in a transaction that should take care of any race potential conditions.
This is kind of a dumb question. What other tools inspired migrondi? What does migrondi do better? Worse? Differently? I like the simplicity and that the implementation is in F#.
Don't worry, there are no dumb questions here (except mine perhaps 😁)
What other tools inspired migrondi?
This was at the beginning just an exercise to learn more F# or at least to solidify what I already knew
but as I was working on it I felt it was worth making it usable for someone else not just my simple exercise, the last migration tool I used was db-migrate which is a nodejs tool.
What does migrondi do better? Worse? Differently?
What MIgrondi aims for is to be something useful yet super simple without any dependency on runtimes or any particular thing installed on your machine
basically, I wanted the following
- download a binary that runs everywhere, no extra installs
- create a migration
- run the migration
- done
the least configuration possible as well, that's why the config file is short as well
as long as it's simple and easy to do stuff I think it's okay to build more stuff on top of that
I'll close this, feel free to re-open if needed 😁
Can you explain what down means? Do I have to write both up and down migrations myself, or does it figure out down logic from up logic?
@xperiandri if your migrations have SQL in the "down" portion of the SQL script then those will be run.
The logic for down is somewhat like this:
- Fetch the migrations from the database
- Compare locally and find the ones that have been applied and cut at the last applied, discard the rest (which should be local)
- Order the migration list by descending timestamp and run the "down" portion of the SQL scripts
e.g.
- AddStudentsTable
- up: create students table
- down: drop table students
- AddClassroomColumnForstudents
- up: alter table students add column classroom
- down: alter table students drop column classroom
Running Up in an "empty" database would create the students table, then update and add the classroom column.
if both migrations are applied, then down would drop the classroom table first then drop the students table in that order
if only one the first migration was applied, down would drop the students table as the "alter" migration would have been pending (not applied in the database) at that point.