Rails + PostgreSQL Array

If you continue to read this article, I assume that you know Ruby, OOP in Ruby, RoR, and Active Record.

Yes, Postgresql support Array types to store. Based on their documentation:

PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.

Let's start our journey! (I use Rails API-only as example, but this article can be implemented in normal Rails as well)

Table of Contents:
1. Migration
2. Create
3. Show
4. Update
5. Query
6. Final Word

1. Migration

It is simple:

# db/migrate/*_create_books.rb
class CreateBooks < ActiveRecord::Migration[6.0]
  def change
    create_table :books do |t|
      t.string :title
      t.string :tags, array: true, default: []
      t.integer :ratings, array: true, default: []

      t.timestamps
    end
    add_index :books, :tags, using: 'gin'
    add_index :books, :ratings, using: 'gin'
  end
end

If you want to add new column:

# db/migrate/*_add_subjects_to_books.rb
class AddSubjectsToBooks < ActiveRecord::Migration
  def change
    add_column :books, :subjects, :string, array:true, default: []
  end
end

Notice:
I define the column as t.string :tags, array: true not t.array :tags. Compare to jsonb, which t.jsonb :payload. This is because there is no "array" type in PostgreSQL, only "array of column type". PostgreSQL arrays aren't generic containers like Ruby arrays, they are more like arrays in C, C++, etc.

2. Create

Create a record is very simple too:

irb(main):001:0> Book.create(title: "Hacking Growth", tags: ["business", "startup"], ratings: [4, 5])
   (0.1ms)  BEGIN
  Book Create (0.6ms)  INSERT INTO "books" ("title", "tags", "ratings", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["title", "Hacking Growth"], ["tags", "{business,startup}"], ["ratings", "{4,5}"], ["created_at", "2020-06-29 08:48:42.440895"], ["updated_at", "2020-06-29 08:48:42.440895"]]
   (0.4ms)  COMMIT
=> #<Book id: 1, title: "Hacking Growth", tags: ["business", "startup"], ratings: [4, 5], created_at: "2020-06-29 08:48:42", updated_at: "2020-06-29 08:48:42">

3. Show

Both tags and ratings now an array object:

irb(main):002:0> book = Book.first
  Book Load (0.3ms)  SELECT "books".* FROM "books" ORDER BY "books"."id" ASC LIMIT $1  [["LIMIT", 1]]
irb(main):003:0> book.tags
=> ["business", "startup"]
irb(main):004:0> book.tags[0]
=> "business"

4. Update

To update, the most easiest way is:

irb(main):005:0> book.tags << 'management'
=> ["business", "startup", "management"]
irb(main):0006:0> book.save!
   (0.1ms)  BEGIN
  Book Update (1.2ms)  UPDATE "books" SET "tags" = $1, "updated_at" = $2 WHERE "books"."id" = $3  [["tags", "{business,startup,management}"], ["updated_at", "2020-06-29 08:54:36.731328"], ["id", 1]]
   (0.4ms)  COMMIT
=> true
irb(main):007:0> book.tags
=> ["business", "startup", "management"]

And any other way to add a value to an array object:

# This works
book.tags << 'management'

#This will work too
book.tags.push 'management'

# This is also will work
book.tags += ['management']

But do not do this: Book.first.tags << 'finance', it won't be saved to the database. Prove:

irb(main):008:0> Book.first.tags << "finance"
  Book Load (0.3ms)  SELECT "books".* FROM "books" ORDER BY "books"."id" ASC LIMIT $1  [["LIMIT", 1]]
=> ["business", "startup", "management", "finance"]
irb(main):009:0> Book.first.save!
  Book Load (0.3ms)  SELECT "books".* FROM "books" ORDER BY "books"."id" ASC LIMIT $1  [["LIMIT", 1]]
=> true
irb(main):010:0> Book.first.tags
  Book Load (0.3ms)  SELECT "books".* FROM "books" ORDER BY "books"."id" ASC LIMIT $1  [["LIMIT", 1]]
=> ["business", "startup", "management"]

If you want to use raw SQL, you can check to the official documentation.

5. Query

Let say we want to search every single Book that have tags management:

# This is valid
irb(main):011:0> Book.where("'management' = ANY (tags)")

# This is more secure
irb(main):012:0> Book.where(":tags = ANY (tags)", tags: 'management')

# This is also valid
irb(main):013:0> Book.where("tags @> ?", "{management}")

What if we want to search every single book that DO NOT HAVE tags management:

irb(main):013:0> Book.where.not("tags @> ?", "{management}")

You can see the operators and their description in the official documentation.

Now, what if we want to search book that contain multiple tags, like management and startup:

# This is valid
irb(main):014:0> Book.where("tags @> ARRAY[?]::varchar[]", ["management", "startup"])

# This is valid
irb(main):015:0> Book.where("tags &&  ?", "{management,startup}")

# If you use where.not, you basically search for all that do not contain the parameter given.

Now what if we want to search all book that have rating more than 3:

irb(main):016:0> Book.where("array_length(ratings, 1) >= 3")

How about making our search a little bit more robust and supporting pattern matching:

# %gem% is manaGEMent 
irb(main):017:0> Book.where("array_to_string(tags, '||') LIKE :tags", tags: "%gem%")

You can see all the operators and functions and their description in the official documentation.

6. Final Word

That's all from me. I'll update if I find something interesting.

source: myself and extract from many articles

Blog