How to save array in database correctly?

Question

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:

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.
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.
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?

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. postgresql.org/docs/9.4/static/datatype-json.html — Aleksei Matiushkin
– Aleksei Matiushkin, Commented Mar 3, 2018 at 10:21
@mudasobwa yes, but it correctly to save array to database like json? If you tell about sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/… - it is look like first variant in my question, but with json conversion instead array — Flamine
– Flamine, Commented Mar 3, 2018 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
– Aleksei Matiushkin, Commented Mar 3, 2018 at 10:29
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
– spickermann, Commented Mar 3, 2018 at 10:50

Joe · Accepted Answer · 2018-03-03 13:30:01Z

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

answered Mar 3, 2018 at 13:30

Joe

42.9k20 gold badges109 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eliav Lavi · Accepted Answer · 2018-03-03 11:05:56Z

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.

answered Mar 3, 2018 at 11:05

Eliav Lavi

666 bronze badges

Comments

Nimish Gupta · Accepted Answer · 2018-03-03 11:51:05Z

-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

answered Mar 3, 2018 at 11:51

Nimish Gupta

3,1851 gold badge17 silver badges21 bronze badges

1 Comment

Joe Over a year ago

He’s using Sequel gem not ActiveRecord.

Collectives™ on Stack Overflow

How to save array in database correctly?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related