BigBinary Blog

Rails 6.1 adds support for PostgreSQL interval data type

What is PostgreSQL Interval Data Type?

PostgreSQL Interval Data Type allows us to store a duration/period of time in years, months, days, hours, minutes, seconds, etc. It also allows us to perform arithmetic operations on that interval.

There are two input formats for interval data. These formats are used to write interval values.

1. Verbose format:

  <quantity> <unit> [<quantity> <unit>...] [<direction>]

  # Examples:
  '2 years ago'
  '12 hours 13 minutes ago'
  '8 years 7 months 2 days 3 hours'

• quantity can be any number.
• unit can be any granular unit of time in plural or singular form like days/day, months/month, weeks/week, etc..
• direction can be ago or an empty string.

2. ISO 8601 formats:

P <quantity> <unit> [ <quantity> <unit> ...] [ T [ <quantity> <unit> ...]]

• ISO 8601 format always starts with P.
• quantity and unit before T represents years, months, weeks and days of an interval.
• quantity and unit after T represents the time-of-day unit.

# Examples
P1Y1M1D => interval of '1 year 1 month 1 day'
P3Y1DT2H => interval of '3 years 1 day 2 hours'
P5Y2MT3H2M => interval of '5 years 2 months 3 hours 2 minutes'
# NOTE: If `M` appears before `T`,
# it is month/months and if it appears after `T`, it signifies minute/minutes.

OR

P [ years-months-days ] [ T hours:minutes:seconds ]

# Examples
P0012-07-00T00:09:00 => interval of '12 years 7 months 9 minutes'
P0000-10-00T10:00:00 => interval of '10 months 10 hours'

Arithmetic operations on interval

We can easily apply addition, subtraction and multiplication operations on interval data.

'10 hours 10 minutes' + '30 minutes' => '10 hours 40 minutes'

'10 hours 10 minutes' - '10 minutes' => '10 hours'

60 * '10 minute' => '10 hours'

Before Rails 6.1

PostgreSQL interval data type can be used in Rails but Active Record treats interval as a string. In order to convert it to an ActiveSupport::Duration object, we have to manually alter the IntervalStyle of the database to iso_8601 and then parse it as shown below:

execute "ALTER DATABASE <our_database_name> SET IntervalStyle = 'iso_8601'"

ActiveSupport::Duration.parse(the_iso_8601_formatted_string)

Rails 6.1

Rails 6.1 adds built-in support for the PostgreSQL interval data type. It automatically converts interval to an ActiveSupport::Duration object when fetched from a database. When a record containing the interval field is saved, it is serialized to an ISO 8601 formatted duration string.

The following example illustrates how it can be used now:

# db/migrate/20201109111850_create_seminars.rb

class CreateSeminars < ActiveRecord::Migration[6.1]
  def change
    create_table :seminars do |t|
      t.string :name
      t.interval :duration
      t.timestamps
    end
  end
end

# app/models/seminar.rb
class Seminar < ApplicationRecord
  attribute :duration, :interval
end

>> seminar = Seminar.create!(name: 'RubyConf', duration: 5.days)
>> seminar
=> #<Event id: 1, name: "RubyConf", duration: 5 days, created_at: ...>

>> seminar.duration
=> 5 days

>> seminar.duration.class
=> ActiveSupport::Duration

>> seminar.duration.iso8601
=> "P5D"

# ISO 8601 strings can also be provided as interval's value
>> seminar = Seminar.create!(name: 'GopherConIndia', duration: 'P5DT7H6S')
>> seminar
=> #<Event id: 2, name: "GopherConIndia", duration: 5 days, 7 hours, and 6 seconds, created_at: ...>

# Invalid values to interval are written as NULL in the database.
>> seminar = Seminar.create!(name: 'JSConf', duration: '3 days')
>> seminar
=> #<Event id: 3, name: "JSConf", duration: nil, created_at: ...>

If we want to keep the old behaviour where interval is treated as a string, we need to add the following in the model.

# app/models/seminar.rb
class Seminar < ApplicationRecord
  attribute :duration, :string
end

If the attribute is not set in the model, it will throw the following deprecation warning.

DEPRECATION WARNING: The behavior of the `:interval` type will be changing in Rails 6.2
to return an `ActiveSupport::Duration` object. If you'd like to keep
the old behavior, you can add this line to Event model:

  attribute :duration, :string

If you'd like the new behavior today, you can add this line:

  attribute :duration, :interval

Check out the commit for more details.

Rails 6.1 allows per environment configuration support for Active Storage

Rails 6.1 allows environment-specific configuration files to set up Active Storage.

In development, the config/storage/development.yml file will take precedence over the config/storage.yml file. Similarly, in production, the config/storage/production.yml file will take precedence.

If an environment-specific configuration is not present, Rails will fall back to the configuration declared in config/storage.yml.

Why was it needed?

Before Rails 6.1, all storage services were defined in one file, each environment could set its preferred service in config.active_storage.service, and that service would be used for all attachments.

Now we can override the default application-wide storage service for any attachment, like this:

class User < ApplicationModel
  has_one_attached :avatar, service: :amazon_s3
end

And we can declare a custom amazon_s3 service in the config/storage.yml file:

amazon_s3:
  service: S3
  bucket: "..."
  access_key_id: "..."
  secret_access_key: "..."

But we are still using the same service for storing avatars in both production and development environments.

To use a separate service per environment, Rails allows the creation of configuration files for each.

How do we do that?

Let's change the service to something more generic in the User model:

class User < ApplicationModel
  has_one_attached :avatar, service: :store_avatars
end

And add some environment configurations:

For production we'll add config/storage/production.yml:

store_avatars:
  service: S3
  bucket: "..."
  access_key_id: "..."
  secret_access_key: "..."

And for development we'll add config/storage/development.yml:

store_avatars:
  service: Disk
  root: <%= Rails.root.join("storage") %>

This will ensure that Rails will store the avatars differently per environment.

Check out the pull request to learn more.

Authorization in REST vs Postgraphile

Postgraphile is a great tool for making instant GraphQL from a PostgreSQL database. When I started working with Postgraphile, its authorization part felt a bit different compared to the REST based backends which I had worked with before. Here I will share some differences that I noted.

First, let's see Authentication vs Authorization.

Authentication is determining whether a user is logged in or not. Authorization is then deciding what the users has permission to do or see.

Comparing the implementation of a blog application using Postgraphile vs REST

Suppose we have to build a blog application with the below schema.

Features of the blog application.

• Display published blogs with is_published = true to all users.

• Display unpublished blogs with is_published = false to its creator only.

REST Implementation

The REST implementation with JavaScript and sequelize can be like below.

The client requests the blogs using an endpoint, it also attaches the access token received from the authentication service.

const getBlogs = () => requestData({
    endpoint: `/api/blogs`,
    accessToken: '***'
});

The backend code in the server receives the request, finds the current logged in user from the access token, and requests the data based on the current logged in user from the database.

const userEmail = findEmail(accessToken)
const blogs = await models.Blogs.findAll({
where: { [Op.or]:[
    {creatorEmail: userEmail},
    {isPublished: true}
  ]},
});

res.send(blogs);

Here, the backend code finds the user’s email from the access token, then requests the database to give the list of blogs that have creatorEmail matching to the current user's email or the field isPublished is true.

The database will return whatever data the server requests.

Similarly, for creating, editing, and deleting blogs, we can have different end-points to handle the authorization logic in the backend code.

Postgraphile Implementation

The postgraphile implementation can be like below.

The client requests the blogs using a GraphQL query. It also attaches the access token received from the authentication service.

const data = requestQuery({
 query: "allBlogs {
         nodes {
            content
            creatorEmail
            visiblityType
           }
        }"
 accessToken: '***'
})

In the server, we configure Postgraphile to pass the user information to the database.

export postgraphile(DATABASE_URL, schemaName, {
  pgSettings: (req) => {
     const userEmail = findEmail(accessToken);
     return({
         'current_user_email': userEmail
     })
  }
})

We can pass a function as Postgraphile’s pg Settings property, whose return value will be accessible from the connected Postgres database by calling the current_setting function.

In the database, the row-level security policies can be defined to control the data access.

Row-level security policies are basically just SQL that either evaluates to true or false. If a policy is created and enabled for a table, that policy will be checked before doing an operation on the table.

create policy blogs_policy_select
on public.blogs for select to users
USING (
 isPublished OR
 creator_email = current_setting('current_user_email')
);
ALTER TABLE blogs ENABLE ROW LEVEL SECURITY;

Here the policy named blogs_policy_select will be checked before selecting a row in the table public.blogs. A row will be selected only if the isPublished field is true or creator_email matches with the current user's email.

Similarly, for creating, editing, and deleting blogs, we can have row level security policies for INSERT, UPDATE, and DELETE operations on the table.

Conclusion

The REST implementation does the authorization on the server level but the Postgraphile does it on the database level. Each implementation has its own advantages and disadvantages, which is a topic for another day.

Sort query data on associated table in PostGraphile

PostGraphile provides sorting on all columns of a table in a GraqhQL query by default with orderBy argument.

Although, sorting based on associated table’s columns or adding a custom sort can be acheived via plugins. In this blog we will explore two such plugins.

Using pg-order-by-related plugin

pg-order-by-related plugin allows us to sort query result based on associated table's columns. It does that by adding enums for all associated table's columns. Here's what we need to do to use this plugin.

Installation

npm i @graphile-contrib/pg-order-by-related

Adding the plugin

const express = require("express");
const { postgraphile } = require("postgraphile");
const PgOrderByRelatedPlugin = require("@graphile-contrib/pg-order-by-related");

const app = express();

app.use(
  postgraphile(process.env.DATABASE_URL, "public", {
    appendPlugins: [PgOrderByRelatedPlugin],
  })
);

Using associated table column enum with orderBy argument

query getPostsSortedByUserId {
  posts: postsList(orderBy: AUTHOR_BY_USER_ID__NAME_ASC) {
    id
    title
    description
    author: authorByUserId {
      id
      name
    }
  }
}

pg-order-by-related plugin is useful only when we want to sort data based on first level association. If we want to apply orderBy on second level table columns or so, we have to use makeAddPgTableOrderByPlugin.

Using makeAddPgTableOrderByPlugin

makeAddPgTableOrderByPlugin allows us to add custom enums that are accessible on specified table's orderBy argument. We can write our custom select queries using this plugin.

We will use a complex example to understand the use-case of custom orderBy enum.

In our posts list query, we want posts to be sorted by author's address. Address has country, state and city columns. We want list to be sorted by country, state and city in the same order.

Here's how we can achieve this using makeAddPgTableOrderByPlugin.

plugins/orderBy/orderByPostAuthorAddress.js

import { makeAddPgTableOrderByPlugin, orderByAscDesc } from "graphile-utils";

export default makeAddPgTableOrderByPlugin(
  "public",
  "post",
  ({ pgSql: sql }) => {
    const author = sql.identifier(Symbol("author"));
    const address = sql.identifier(Symbol("address"));
    return orderByAscDesc(
      "AUTHOR_BY_USER_ID__ADDRESS_ID__COUNTRY__STATE__CITY",
      ({ queryBuilder }) => sql.fragment`(
            SELECT
              CONCAT(
                ${address}.city,
                ', ',
                ${address}.state,
                ', ',
                ${address}.country
              ) AS full_address
            FROM public.user as ${author}
            JOIN public.address ${address} ON ${author}.address_id = ${address}.id
            WHERE ${author}.id = ${queryBuilder.getTableAlias()}.user_id
            ORDER BY ${address}.country DESC, ${address}.state DESC, ${address}.city DESC
            LIMIT 1
          )`
    );
  }
);

Export all custom orderBy plugins

plugins/orderBy/index.js

export { default as orderByPostAuthorAddress } from "./orderByPostAuthorAddress";

Append custom orderBy plugins to postgraphile

const express = require("express");
const { postgraphile } = require("postgraphile");
import * as OrderByPlugins from "./plugins/orderby";

const app = express();

app.use(
  postgraphile(process.env.DATABASE_URL, "public", {
    appendPlugins: [...Object.values(OrderByPlugins)],
  })
);

Using custom enum with orderBy argument

query getPostsSortedByAddress {
  posts: postsList(
    orderBy: AUTHOR_BY_USER_ID__ADDRESS_ID__COUNTRY__STATE__CITY
  ) {
    id
    title
    description
    author: authorByUserId {
      id
      name
      address {
        id
        country
        state
        city
      }
    }
  }
}

Please head to pg-order-by-related and makeAddPgTableOrderByPlugin pages for detailed documentation.

Rails 6.1 adds support for belongs_to to has_many inversing

Before Rails 6.1, we could only traverse the object chain in one direction - from has_many to belongs_to. Now we can traverse the chain bi-directionally.

The inverse_of option, both in belongs_to and has_many is used to specify the name of the inverse association.

Let's see an example.

class Author < ApplicationRecord
  has_many :books, inverse_of: :author
end

class Book < ApplicationRecord
  belongs_to :author, inverse_of: :books
end

Before Rails 6.1

has_many to belongs_to inversing

irb(main):001:0> author = Author.new
irb(main):002:0> book = author.books.build
irb(main):003:0> author == book.author
=> true

In the above code, first we created the author and then a book instance through the has_many association.

In line 3, we traverse the object chain back to the author using the belongs_to association method on the book instance.

belongs_to to has_many inversing

irb(main):001:0> book = Book.new
irb(main):002:0> author = book.build_author
irb(main):003:0> author.books
=> #<ActiveRecord::Associations::CollectionProxy []>

In the above case, we created the book instance and then we created the author instance using the method added by belongs_to association.

But when we tried to traverse the object chain through the has_many association, we got an empty collection instead of one with the book instance.

After changes in Rails 6.1

The belongs_to inversing can now be traversed in the same way as the has_many inversing.

irb(main):001:0> book = Book.new
irb(main):002:0> author = book.build_author
irb(main):003:0> author.books
=> #<ActiveRecord::Associations::CollectionProxy [#<Book id: nil, author_id: nil, created_at: nil, updated_at: nil>]>

Here we get the collection with the book instance instead of an empty collection.

We can also verify using a test.

class InverseTest < ActiveSupport::TestCase

  def test_book_inverse_of_author
    author = Author.new
    book = author.books.build

    assert_equal book.author, author
  end

  def test_author_inverse_of_book
    book = Book.new
    author = book.build_author

    assert_includes author.books, book
  end
end

In previous Rails versions, the test cases would fail.

# Running:

.F
Failure:
InverseTest#test_author_inverse_of_book

Expected #<ActiveRecord::Associations::CollectionProxy []> to include #<Book id: nil, author_id: nil, created_at: nil, updated_at: nil>.

Finished in 0.292532s, 6.8369 runs/s, 10.2553 assertions/s.
2 runs, 3 assertions, 1 failures, 0 errors, 0 skips

In Rails 6.1, both the tests will pass.

# Running:

..

Finished in 0.317668s, 6.2959 runs/s, 9.4438 assertions/s.
2 runs, 3 assertions, 0 failures, 0 errors, 0 skips

Check out this pull request for more details.