Workaround for Storing Arrays in Databases With No Array Type
Tags: ruby, webdev, rails
Published: 2021-02-25 21:53:58

I was recently working on building my first web app with Sinatra and ran into the issue that I needed to store an array/hash in my database. I should mention that there are certainly databases (like Postgres) that support an Array datatype; unfortunately, I was using SQLite3, which does not. That led me down a rabbit hole to find the simplest workaround, and I found that it was actually a pretty common question. 

Here's what I want to be able to do:
```ruby
class CreatePandas < ActiveRecord::Migration
  def change
    create_table :pandas do |t|
      t.string :name
      t.integer :age
      t.array :favorite_foods   # obviously can't do this because there's no array datatype
    end 
  end
end
```


## Serialize the data

The most prominent answer by far was to serialize using the ActiveRecord method [serialize](https://api.rubyonrails.org/classes/ActiveRecord/AttributeMethods/Serialization/ClassMethods.html). The idea here is that `#serialize` uses YAML to turn our array or hash into a string, so that it can be stored in the database and reconstructed later. For an idea of how that's working behind the scenes, we can take a look at this totally unbiased list of vegetables: 

```ruby
veggies = ["carrots", "peas", "mushrooms"].to_yaml 
#=> "---\n- carrots\n- peas\n- mushrooms\n"
preferences = {great: ["carrots"], gross: ["peas", "mushrooms"]}.to_yaml
#=> "---\n:great:\n- carrots\n:gross:\n- peas\n- mushrooms\n"

YAML.load(veggies) #=> ["carrots", "peas", "mushrooms"]
YAML.load(preferences) #=> {:great=>["carrots"], :gross=>["peas", "mushrooms"]}
```

So that's how `serialize` works, but how do I use it? Well, now that we know our list of favorite foods will actually be stored as a string, we can change our datatype in the migration.  
```ruby
...
create_table :pandas do |t|
  t.string :name
  t.integer :age
  t.text :favorite_foods 
end 
```

We also need to tell our model to encode and decode `favorite_foods` so that we can interact with it as an array in our program.
```ruby
class Panda < ActiveRecord::Base 
  serialize :favorite_foods, Array  
end
``` 
The second argument is optional, but helps to specify what type of coder you'd like to use or what type of object to expect when entering or retrieving data in the database. Here we're using `Array` as our object type to expect, but we could also use `Hash` (if we were working with hashes instead of arrays) or `JSON` (if we wanted to use JSON instead of YAML as our coder).  

Now we're off to the races. We can create and edit Pandas to our hearts' content, without worrying about the `favorite_foods` attribute being an array. 

```ruby
fluf = Panda.create(name: "Fluffy", age: 13, favorite_foods: ["pizza", "bamboo"]) 
#=> #<Panda:0x00007fc39e0d7960 id: 1, name: "Fluffy", age: 13, favorite_foods: ["pizza", "bamboo"]>

fluf.favorite_foods.first #=> "pizza"
fluf.favorite_foods << "pasta" #=> ["pizza", "bamboo", "pasta"]
fluf.save #=> true 
``` 
## Drawbacks to using serialize

For a lot of situations, this is the perfect simple solution, and we don't need to go any further. But if you play around with `serialize` you'll notice there are a couple of limitations that keep it from being the one-size-fits-all, perfect tool.

The biggest drawback is querying. If I want to find Fluffy at any time, I can simply...
```ruby
fluf = Panda.find_by(name: "Fluffy")
#=> #<Panda:0x00007fc39e0d7960 id: 1, name: "Fluffy", age: 13, favorite_foods: ["pizza", "bamboo", "pasta"]>
```      
And if we want to see what food Fluffy likes...
```ruby
fluf.favorite_foods.include?("pizza") #=> true
fluf.favorite_foods.include?("gyros") #=> false
```

But what if we want to find all the pandas in my database that like pizza? Even assuming that my array is ordered by preference, and we just want to know who has pizza as their top choice, we would need something like this:
```ruby
Panda.find_by(:favorite_foods.first => "pizza") # or
Panda.find_by(:favorite_foods[0] => "pizza") # or maybe even
Panda.find_by(:favorite_foods.include?("pizza"))
```
None of that's going to work though, and the reason is pretty simple. If we remember back to the beginning, the whole point of using `serialize` was to enable us to turn our arrays into strings. Now our database is very confused (and going to send all kinds of errors) because we're trying to call Array methods on something that we've explicitly asked to be stored as text. For pandas and pizza that may not matter very much, especially at a small scale (we can simply request all the pandas from our database – turning our `favorite_foods` back into an array and allowing us to iterate over it with no problems). But what if instead of pandas we had a dataset of thousands of social media users... that method quickly becomes impractical.  

That takes us to the other potential drawback: performance. The reason the above situation isn't practical is because for every user we pull from the database, Ruby will create a new object. We can see that even when we retrieve the same database entry more than once. 
```ruby
Panda.find_by(name: "Fluffy")
#=> #<Panda:0x00007fbb8b0881c8 id: 1, name: "Fluffy", age: 13, favorite_foods: ["pizza", "bamboo", "pasta"]>
Panda.find_by(name: "Fluffy")
#=> #<Panda:0x00007fbb877f69e0 id: 1, name: "Fluffy", age: 13, favorite_foods: ["pizza", "bamboo", "pasta"]>
```

Also, because of the serialization process, if we update a user's name (or any other attribute), the serialized column will also be updated, even if we didn't change anything about it. That's a lot of extra work for our program.      

## What if I need to query the data?

If you do need to query based on the information in an array-like structure (and it's not practical to switch the database you're using), an easy option is to create another table and model for the data previously held in your array/hash. Let's update our simple example, starting with the migrations. 

```ruby
# /db/migrate/001_create_pandas.rb
...
create_table :pandas do |t|
  t.string :name
  t.integer :age
  # t.text :favorite_foods  # no longer necessary
end

# /db/migrate/002_create_favorites.rb
...
create_table :favorites do |t|
  t.string :food
  t.belongs_to :panda   
end
```
Now we can create a standard `has_many`/`belongs_to` relationship between the two models. I'm assuming a familiarity with ActiveRecord Associations here, but if this is new or not making sense, check out the [Rails Guide] (https://guides.rubyonrails.org/association_basics.html) for a good explanation. 

```ruby
# /app/models/pandas.rb
class Panda < ActiveRecord::Base 
  has_many :favorites
end

# /app/models/favorites.rb
class Favorites < ActiveRecord::Base 
  belongs_to :panda
end
``` 
And that about does it. We wanted to be able to search for pizza, and now we can. 
```ruby
pizza_luvers = Favorite.where("food = 'pizza'")
#=> [#<Favorite:0x00007fbb8aa5b1b0 id: 1, food: "pizza", panda_id: 1>, 
     #<Favorite:0x00007fbb8aa5afd0 id: 5, food: "pizza", panda_id: 3>]

  ## To get the pandas' names ##
pizza_luvers.collect{|p| p.panda.name}   
#=> ["Fluffy", "Mei Xiang"]

  ## To see a panda's favorite foods ##
Panda.find_by(name: "Fluffy").favorites   
#=> [#<Favorite:0x00007fbb8771e018 id: 1, food: "pizza", panda_id: 1>, 
     #<Favorite:0x00007fbb8771ded8 id: 3, food: "bamboo", panda_id: 1>,
     #<Favorite:0x00007fbb875f7c98 id: 4, food: "pasta", panda_id: 1>]
```
Obviously our array of favorite panda foods has a limited utility, but even with an overly-simplistic example, I like the added functionality of an additional table over serialized data. For my real-life use case, this was a much better solution, because I needed to be able to query and sort by hash data. I was actually surprised to see that `serialize` was recommended so much, so if I'm missing something here, definitely let me know. 

Hopefully this is helpful for anyone who finds themself in the same position I did working on my project!