CODEWITHSUNDEEP
ruby-on-railspostgresqlhstore
Lee Eather
Lee Eather

Reputation: 355

How to generate right sql for using hstore in rails migration postgres using change_column

I need to get hstore working in a rails migration. I have enabled hstore through a migration, now, I am trying to generate the correct sql to get it working but when I run db:migrate I get this error;

C:\Sites\Peoples_Profiles>rails db:migrate
rails aborted!
SyntaxError: C:/Sites/Peoples_Profiles/db/migrate/20180407073155_add_hstore_hash_to_urls.rb:4: syntax error, unexpected '\n', expecting =>
C:/Sites/Peoples_Profiles/db/migrate/20180407073155_add_hstore_hash_to_urls.rb:10: syntax error, unexpected end-of-input, expecting keyword_end
bin/rails:4:in `require'
bin/rails:4:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

The migration looks like this.

class AddHstoreHashToUrls < ActiveRecord::Migration[5.0]

  def self.up
    change_column :users, :urls, default: {}, "hstore USING urls::hstore" 
  end

  def self.down
    change_column :users, :urls, :text
  end
end

What is the correct sql to get the type set for that column and why am I get a newline error?

UPDATE My urls are getting stored like this in db

 urls: {"url1"=>"", "url2"=>"", "url3"=>"", "url4"=>"", "url5"=>""}

so I think postgres is complaining because it is trying to convert this code..

So after check how rails actually brings in paramaters it looks like this

 ["urls", "--- !ruby/hash:ActiveSupport::HashWithIndifferentAccess\nurl1: http://hello\nurl2: http://jack\nurl3: http://boo\nurl4: ''\nurl5: ''\n"]

Not sure if it looks like this in the db unfortunetly but I say there is a \n in there some where causing this error. Will have to pursue this further.

... and I realise now the \n will be coming from the conversion of serializing a hash to YAML.

Upvotes: 3

Views: 516

Answers (2)

mu is too short
mu is too short

Reputation: 434665

Presumably you started with:

serialize :urls

in your model so your hashes are being stored in the database as YAML. There is no type cast from YAML text to hstore so your USING:

USING urls::hstore

won't work. There's no easy way to reliably parse YAML inside PostgreSQL so your best bet is to:

  1. Rename the users.urls column to users.yaml_urls.
  2. Add a new urls column with (preferably) jsonb type but you can use hstore if necessary. jsonb is the future and will be better supported going forward.
  3. Read each yaml_urls value from the database into Ruby.
  4. Convert that YAML string to hash with h = YAML.load(yaml_from_db).
  5. Encode the hash as JSON (h.to_json) and put it back in the database.
  6. Make urls NOT NULL if desired and drop the yaml_urls column.

I'd recommend using the low level pg interface for this as you don't want ActiveRecord stuff in the way, can you find such a connection in ApplicationRecord.connection.raw_connection. I say this because you don't want data migrations interacting with models in any way and the low level interface supports placeholders so you can avoid escaping and quoting issues.

That process assumes that you don't have that much data or, equivalently, your app can be offline while you fix the database. If that's not the case then you'd need to maintain both formats (add a new jsonb column, write to and read from both inside your model when urls and urls= are called) while you fix the data, and then clean up later.

Upvotes: 2

Yotam Sofer
Yotam Sofer

Reputation: 1

Try to change the SELF.DOWN to SELF.ANNUAL I think its will solve

Upvotes: -1

Related Questions