PostgreSQL data-types in Rails

PostgreSQL comes jam-packed with exceptionally useful data types that Rails developers can take advantage of. Let’s have a look at some of our favorites.

JSON/JSONB

The JSON/JSONB data type allows us to store and query JSON structures. Though both JSON/JSONB look the same, we recommend developers to use JSONB, as it’s the upgraded version of JSON.

The key difference between both is that JSON data is stored just as text, whereas JSONB stores it in a decomposed binary form. This makes querying more efficient and faster. It also allows JSON data to be directly indexed!

This datatype opens up the door to integrate relational and non-relational data without losing out on performance.

Migration

The column data type can either be json or jsonb.

# db/migrate/*_create_events.rb
# ... for json datatype:
create_table :events do |t|
  t.json 'payload'
end

# ... or for jsonb datatype:
create_table :events do |t|
  t.jsonb 'payload'
end

Usage

Let’s have a look at how to store and retrieve data.

# creating a jsonb structure
irb> Event.create(payload: { kind: "user_renamed", change: ["jack", "john"]})

# reading a jsonb structure
irb> event = Event.first
irb> event.payload
=> {"kind"=>"user_renamed", "change"=>["jack", "john"]}

irb> event.payload["kind"]
=> "user_renamed"

The best part about these structures is that we can query them like any non-relational data.

## Query based on JSON document
# The -> operator returns the original JSON type (which might be an object), whereas ->> returns text
irb> Event.where("payload->>'kind' = ?", "user_renamed")

However, Rails does not provide validations for these data types out of the box. activerecord_json_validator is a great alternative.

Hstore

Before using this data type, one must enable the hstore extension. Though it is not too different from JSON/JSONB structures, it is useful in certain scenarios.

Migration

The column data type here is hstore.

# db/migrate/*_create_profiles.rb

ActiveRecord::Schema.define do
  enable_extension 'hstore' unless extension_enabled?('hstore')
  create_table :profiles do |t|
    t.hstore 'settings'
  end
end

Usage

Hstore provides a more intuitive retrieval.

# creating a hstore structure
irb> Profile.create(settings: { "color" => "blue", "resolution" => "800x600" })

# reading a hstore structure
irb> profile = Profile.first
irb> profile.settings
=> {"color"=>"blue", "resolution"=>"800x600"}

These data types can also be queried with ease.

irb> Profile.where("settings->'color' = ?", "blue")
=> #<ActiveRecord::Relation [#<Profile id: 1, settings: {"color"=>"blue", "resolution"=>"800x600"}>]>

Array

Sometimes one might just need a simple datatype to store additional information in. If that’s the case, try using the array datatype! It’s pretty much self-explanatory.

Not only is one able to store data effectively, but one can also perform complex queries using this datatype.

Migration

# db/migrate/*_create_books.rb
create_table :books do |t|
  t.string 'title'
  t.string 'tags', array: true
end

Usage

# creating an array structure
irb> Book.create title: "Brave New World",
            tags: ["fantasy", "fiction"],
            ratings: [4, 5]

# reading an array structure
irb> Book.last.tags
=> ["fantasy", "fiction"]
irb> Book.last.tags.last
=> "fiction"

Let’s dive into how to query this data.

# Search for books with a single tag
irb> Book.where("'fantasy' = ANY (tags)")

# Search for books with muliple tags
irb> Book.where("tags @> ARRAY[?]::varchar[]", ["fantasy", "fiction"])

# Search for books with more than 2 tags
irb> Book.where("array_length(tags, 1) >= 2")

Bytea

bytea is PostgreSQL’s version of a BLOB, which stands for Binary Large Object. It can be a quick solution to store files directly in our database. However, use this as sparingly as possible.

The bytea format is just a sequence of bytes — however, it is terrible for large amounts of binary data. To store a file in text in the database, we must escape all non-printable types. This means that a single null byte would need to be encoded as “\000” in the ASCII format. It causes an unnecessary expansion of data.

Further, it is near impossible to stream data directly from the database. Even while using an unescape filter to do so, it becomes increasingly inefficient. So much so that streaming a 2MB file would need somewhere around 6MB of data to run through the filter.

Couple this with the naïve method that runs all of the data through Ruby strings, this datatype balloons tremendously in memory.

Migration

# db/migrate/*_create_documents.rb
create_table :documents do |t|
  t.binary 'payload'
end

Usage

# storing a file
irb> data = File.read(Rails.root + "tmp/output.pdf")
irb> Document.create payload: data

# reading a file
irb> Document.last.payload

Range

The range datatype is a quick and simple way to store date, number, or any other set of data.

Migration

# db/migrate/*_create_events.rb
create_table :events do |t|
  t.daterange 'duration'
end

Usage

# creating a range structure
irb> Event.create(duration: Date.new(2022, 1, 1)..Date.new(2022, 1, 7))

# reading a range structure
irb> event = Event.first
irb> event.duration
=> Sat, 01 Jan 2022...Fri, 07 Jan 2022

As always, querying this data is a piece of cake.

## All Events on a given date
irb> Event.where("duration @> ?::date", Date.new(2020, 12, 1))

Interval

Similar to the range, this data type can store a Ruby representation of data. One can use this to store any ActiveSupport::Duration objects.

Migration

# db/migrate/*_create_events.rb
create_table :events do |t|
  t.interval 'duration'
end

Usage

# creating an interval structure
irb> Event.create(duration: 2.days)

# reading an interval structure
irb> Event.first.duration
=> 2 days

Apart from these major data types, PostgreSQL and Rails also provide additional data types built atop text columns like Composites and Enums. If one needs to store network-related information like IP address, mac address, and so on, it is useful to look into the Network Address data-types.