DEV Community

loading...

Rails + PostgreSQL Array

kputra profile image K Putra ・4 min read

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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">
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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"]
Enter fullscreen mode Exit fullscreen mode

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']
Enter fullscreen mode Exit fullscreen mode

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"]
Enter fullscreen mode Exit fullscreen mode

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}")
Enter fullscreen mode Exit fullscreen mode

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

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

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.
Enter fullscreen mode Exit fullscreen mode

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")
Enter fullscreen mode Exit fullscreen mode

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%")
Enter fullscreen mode Exit fullscreen mode

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

Discussion (0)

pic
Editor guide