Unintuitive migration order when extra digits in timestamp

Issues
1

Hi community,

Several months ago we’ve created a migration with an extra digit in it’s timestamp. Not a big deal for prod, so we haven’t noticed it for 4 months. But recently I added another migration that changes the same stuff, and rake db:drop db:create db:migrate started failing. Investigation showed that ROM applies migrations with extra digit in the timestamp after all the rest.

I’m not sure if it is a bug or not, but it’s definitely unintuitive and may cause issues. And it is not hard at all to get one or two such migrations in your project throughout it’s lifetime. We had 3 actually. I guess some other projects that use rom-sql also have such. Would be nice if db:migrate at least produce a warning in such cases

You can reproduce this behavior with the following files:

# Gemfile
source "https://rubygems.org"

gem "rom"
gem "rom-sql"
gem "rake"
gem "sqlite3"

# Rakefile
ENV["BUNDLE_GEMFILE"] ||= File.expand_path("./Gemfile", __dir__)

require "bundler/setup"
Bundler.require

require 'rom/sql/rake_task'

namespace :db do
  task :setup do
    ROM::SQL::RakeSupport.env = ROM::Configuration.new(:sql, "sqlite://dev.db")
  end
end

# db/migrate/200011223344559_invalid_timestamp.rb
#                          ^
ROM::SQL.migration do
  change { puts "Migration with extra digit in timestamp" }
end

# db/migrate/20251122334455_valid_timestamp.rb
ROM::SQL.migration do
  change { puts "Migration with valid timestamp" }
end

rake db:migrate
# =>
#   Migration with valid timestamp
#   Migration with extra digit in timestamp
#   <= db:migrate executed
2

The first component of the migration file name is not a timestamp. It’s an order number. Using a timestamp for that is just convenience to have files ordered in the ascending order in the filesystem listing and to carry additional information.

By adding extra digits you just created much larger number, so the migration is applied last. It would work the same way in Rails, Ecto and probably other frameworks with migrations.

3

Thank you for your comment, but I believe it may be not correct. Sequel has two migratiors: IntegerMigrator (works as you’ve said) and TimestampMigrator that works with timestamps in migration names:

The TimestampMigrator will be used if any filename in the migrations directory has a version greater than 20000101

With the TimestampMigrator, the version integer should represent a timestamp, though this isn’t strictly required
[…]

# Date
20100510_create_artists.rb

# Date and Time
20100510120000_create_artists.rb

# Unix Epoch Time Integer
1273518000_create_artists.rb

(Sequel documentation on that)

Actually, after writing this I’ve realized that I should’ve open an issue in Sequel rather than here :sweat_smile:

4

I might be wrong, but I think ultimately the TimestampMigrator still treats these pre-underscore values as integers. It expects the timestamps to all have the same format:

The important thing is that all migration files should be in the same format, otherwise when you update, it’ll be difficult to make sure migrations are applied in the correct order, as well as be difficult to unapply some the affected migrations correctly.

But I understand the expectation and confusion, because the existence of TimestampMigrator would suggest something else.

5

I’ve created a discussion in Sequel’s github regarding this: Unintuitive migration order when extra digits in timestamp · jeremyevans/sequel · Discussion #2284 · GitHub

As for us, we’re going to use a pre-commit hook for now to prevent committing invalid migrations in the future