Updating the data in a string column using ruby during a migration

[riddley]

Hi. I'm either super bad at searching or this isn't documented anywhere. I've just figured out a way to do this after about 4hrs of searching for a "cleaner" way to do it to no avail.

Is there a better way to do this?

Sequel.migration do
  up do
    jinkies = from(:mytable).select(:id, :text).map{|hash| hash[:text] = hash[:text].squeeze(' '); h}
    jinkies.each do |update|
      from(:mytable).where( id: update[:id]).update(text: update[:text])
    end
  end
end

Second question: if there is a better way to do it, would you like a documentation PR for Sequel::Dataset#update and/or migration.rdoc ?

[jeremyevans]

A better way would involve using a database function (e.g. regexp_replace on PostgreSQL) and issuing a single multitable UPDATE statement to do it.

I don't think it would make sense to document this in the migration documentation, since it isn't specific to migrations, or in the Sequel::Dataset#update documentation (which already gives an example of using a multitable UPDATE).

I would guess that you are correct that there is no specific example in the documentation like this. However, it seems like a simple combination of retrieving data (well documented) and updating data (also well documented).

[riddley]

Thanks for the response. I'm not sure what you mean by "a single multitable UPDATE statement" since only one table is involved, but perhaps my example was confusing. (I see now that my example did have multiple tables, I've corrected this.)

I guess as a bit of feedback, I found the existing documentation around Sequel::Dataset#update to be confusing and incomplete. For one, the ", y=0" bit, which I guess(?) is there to demonstrate that you can update multiple columns at the same time, is implicit, rather than explicit in its inclusion and calling out its inclusion in the prose can only help.

The incomplete part is that it only shows you how to update the data using database-level methods rather than Ruby-level methods, which I guess is a point of view problem. If the point of view is that Sequel exists as a thin layer between Ruby and SQL, I guess I can see not mentioning how to modify data with Ruby. From my point of view, I use an ORM so that I don't have to think about SQL and instead can focus on the Ruby side of things. I can certainly understand the former, but I don't think it would hurt to help people who share the latter.

Thanks again for your response and for all of the code you maintain.

[jeremyevans]

Thanks for the response. I'm not sure what you mean by "a single multitable UPDATE statement" since only one table is involved, but perhaps my example was confusing. (I see now that my example did have multiple tables, I've corrected this.)

Yes, your example originally showed multiple tables, which is why I assumed you wanted a multitable UPDATE.

I guess as a bit of feedback, I found the existing documentation around Sequel::Dataset#update to be confusing and incomplete. For one, the ", y=0" bit, which I guess(?) is there to demonstrate that you can update multiple columns at the same time, is implicit, rather than explicit in its inclusion and calling out its inclusion in the prose can only help.

I'm not sure it makes sense to explicitly state in the prose that the hash can have more than one entry. I think all Ruby users are aware that hashes can have multiple entries. Both the single and multiple entry cases are implicit and not explicit, as the prose does not mention either explicitly.

The incomplete part is that it only shows you how to update the data using database-level methods rather than Ruby-level methods, which I guess is a point of view problem. If the point of view is that Sequel exists as a thin layer between Ruby and SQL, I guess I can see not mentioning how to modify data with Ruby. From my point of view, I use an ORM so that I don't have to think about SQL and instead can focus on the Ruby side of things. I can certainly understand the former, but I don't think it would hurt to help people who share the latter.

Note that Sequel::Model is an ORM (designed around updating single rows/objects), but core Sequel (including Sequel::Dataset) is just a database access library, and it's designed to map Ruby concepts to SQL. Your code is basically trying to do ORM-level updates using core Sequel, which works fine, but not really an approach I would recommend. If you really did want to do this ORM-style:

Sequel.migration do
  up do
    Sequel::Model(from(:mytable)).select(:id, :text).all do |h|
      h.update(text: h.text.squeeze(' '))
    end
  end
end

[riddley]

Thanks. I was trying to adhere to the "Just don't do it." advice found in migrations.rdoc around using Models in migrations. Sequel::Model is my primary use for Sequel.

[rgalanakis]

I think Jeremy's point is, the approach of updating each row with a separate update statement is going to be very slow and non-ideal, and it's not really a thing you'd do with SQL. You'd write a single UPDATE with the squeeze logic using a regex.

If you want to update each row in the table using Ruby, then what you have is fine, though I'd suggest it's clearer as:

Sequel.migration do
  up do
    from(:mytable).select(:id, :text).each do |row|
      from(:mytable).where( id: row[:id]).update(text: row[:text].squeeze(' '))
    end
  end
end

Note you can also use a case statement (using Sequel.case), so it looks like UPDATE mytable SET text=(CASE id WHEN 1 THEN 'x' WHEN 2 THEN 'y') or whatever, and issue fewer than a single UPDATE per row, which is what many ORMs do for 'batch update' (which I personally dislike because it hides the complexity of what you're asking). But at that point I'd suggest following Jeremy's advice and using something like from(:mytbale).update(text: Sequel.function(:regex_replace, '...', '...')) and figuring out the regex (it is probably documented somewhere in ActiveSupport or a blog post).

I will say, what you're tripped up with is pretty common for people new to Sequel or who approach it like a fundamental ORM approach like ActiveRecord or Django, where the promise if you don't have to worry about the database ever. But my experience across dozens of projects is that you don't have to worry about it, until you do, and then it's a big problem. The approach you're taking, to update each row in a migration, is infeasible at a modest scale. For example, let's say it takes 300 seconds to update 1 million rows that way. Because you're in a transaction, each UPDATE then locks the row from reads. By the time you get to the end, most rows in that table will have been locked for an average of 2.5 minutes. But if you wrote the migration as a regex_replace in the DB, the update would take maybe 5 seconds, which is a reasonable time to have locked the table.

Sequel's approach is to come at things from a SQL perspective first, and make that easy, and layer an ORM on top of it. This IME makes it a lot easier to work with, and perform much better in practice. But it can trip up people who see it as a simple drop-in for ActiveRecord.

[jeremyevans]

Thanks. I was trying to adhere to the "Just don't do it." advice found in migrations.rdoc around using Models in migrations. Sequel::Model is my primary use for Sequel.

That is good general advice, but it's more like "don't use existing models in migrations", and the reason for that is that changes to models can break old migrations. Creating an anonymous model class in a migration doesn't have the same issue. Not that I would recommend that approach either, unless what you are doing is impossible to do in a single query in SQL.

If you want to update each row in the table using Ruby, then what you have is fine, though I'd suggest it's clearer as:

Sequel.migration do
  up do
    from(:mytable).select(:id, :text).each do |row|
      from(:mytable).where( id: row[:id]).update(text: row[:text].squeeze(' '))
    end
  end
end

Note that issuing queries inside a Dataset#each block is unsupported in Sequel (whether it works depends on the adapter in use). However, switching each to all and the above would be fine (though not as good as using a single query, for the reasons you mentioned).