DEV Community

K Putra
K Putra

Posted on

Rails + PostgreSQL Array

#rails #postgres

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

Top comments (6)

Collapse
 
luis_azcuaga profile image
Luis Azcuaga • • Edited

For the multiple tags in the query I'd rather go with a simpler join:

tags_array = ["management", "startup"] Book.where("tags @> #{tags_array.join(',')})
Enter fullscreen mode Exit fullscreen mode

It might not be fancy, but casting an empty array leads to a NULL and that might not be desirable 👀

Collapse
 
marcoscannabrava profile image
Marcos Cannabrava • • Edited

This seems vulnerable to SQL injection with a payload like tags_array = ["'management'};", "malicious code here", "another fake tag query"]

Collapse
 
timkozak profile image
Timothy Kozak • • Edited

here is my scope examples

scope :tagged_one_of, -> (tags) { tags ? where("tags && ARRAY[?]::varchar[]", tags) : all } scope :tagged_all_of, -> (tags) { tags ? where("tags @> ARRAY[?]::varchar[]", tags) : all }
Enter fullscreen mode Exit fullscreen mode

example

Product.where(filter).where(sub_filter).tagged_one_of(tags_array)
Enter fullscreen mode Exit fullscreen mode
Collapse
 
hombre2014 profile image
Yuriy Chamkoriyski •

Thanks for your article. How about when you have a form to create the record? What should be the syntax? I am struggling to make it. The form should submit an array element. Do you know how to do it? Thanks.

Collapse
 
josimarcamargo profile image
Josimar Camargo •

Thanks for the article, you did a great job.
I'm saving to use as quick reference.

Collapse
 
koenhandekyn profile image
koen handekyn •

this pairs well with citext type (tags typically are case insenstive)