2

I want to add array in database. I use ruby, sequel and postgresql, but question about true way, and not program realization. I see 3 path:

  1. Use special adapters. It help to save array like [1,2,3,4] in db after some changes: in database it will look like "{1,2,3,4}". BUT after it I don't know how to convert this entry back to [1,2,3,4] without bullshits.
  2. Convert array to json and save it to database. Array like [1,2,3,4] will be look like "[1,2,3,4]". And I can easy convert back by JSON.parse.
  3. Convert array by Marshal and save. Array [1,2,3,4] will be look like "\x04\b[\ti\x06i\ai\bi\t", but it riskily, because data can be changed after ruby update (or I am wrong) Can anybody to tell about true way?
Flamine
  • 487
  • 4
  • 21
  • PostgreSQL has a JSON field type and `sequel` has an ability to store/load to/from there in a natural way without any explicit marshalling. https://www.postgresql.org/docs/9.4/static/datatype-json.html – Aleksei Matiushkin Mar 03 '18 at 10:21
  • @mudasobwa yes, but it correctly to save array to database like json? If you tell about http://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/pg_json_rb.html - it is look like first variant in my question, but with json conversion instead array – Flamine Mar 03 '18 at 10:27
  • I am not sure I follow. You have in ruby a field of type postgres json, assign it like `user.roles = %w|admin poster commenter|` and save it within all others like `user.save!` and/or `user.update_attributes!(roles: ...)`. Everything else is done by sequel/postgres. – Aleksei Matiushkin Mar 03 '18 at 10:29
  • https://stackoverflow.com/questions/44784259/how-to-declare-postgresql-json-jsonb-field-with-sequel – Aleksei Matiushkin Mar 03 '18 at 10:31
  • If there is a small chance that you need to query your data by an array element in the future then I suggest with Postgresql's array extension or an extra table. – spickermann Mar 03 '18 at 10:50
  • @spickermann extra table is good idea, but in my case it not necessary. I will not use searching by this field – Flamine Mar 03 '18 at 10:57
  • @mudasobwa Variant with %w|admin poster commenter| in field with json type is work, but then need to JSON.parse() this field to getting array instead string ("[1,2,3,4,5]") – Flamine Mar 03 '18 at 11:04

3 Answers3

3

Sequel supports Postgres Array columns natively, assuming you don’t need to normalize yourschema further.

How do I define an ARRAY column in a Sequel Postgresql migration?

http://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/pg_array_rb.html

Joe
  • 41,484
  • 20
  • 104
  • 125
0

IMHO, The clean approach would be an extra table. Ideally, each row in the DB represent a single observation, and having an array or json column most probably contradicts that. I suggest looking at that extra table as good design, not as an overkill.

Eliav Lavi
  • 66
  • 6
-1

Make use of serialize provided by ActiveRecord

class User < ApplicationRecord

  serialize :preferences, Array

end


#In migration Add a string field
class AddSerializePreferencesToUsers < ActiveRecord::Migration[5.1]
  def change
    add_column :users, :preferences, :string
  end
end

#In rails console.

u = User.first
u.preferences # => []
u.preferences << 'keep coding' 
u.preferences # => ['keep coding']
u.save
u.reload.preferences #  ['keep coding']

Hope this helps

Nimish Gupta
  • 3,095
  • 1
  • 12
  • 20