Last modified: Sat Mar 23 2019 15:47:44 GMT+0800 (Malay Peninsula Standard Time)

Chapter 4. Rails JSONB

Can you take the advantage of NoSQL in a Relational Database? The answer is yes. I was working on a project a while back and I accidentally discovered that you can store a JSONB in a relational database. You can read more about the JSONB available in PostgreSQL

It is reasonable to take advantage of JSON column type in the table. For example, if you would like to store a config in the database, it is a good choice to take advantage of it, which we are going through in the next section.

Note: The tutorial below shows how to setup and use JSONB as a column type in PostgreSQL (PG) Database. The data type is available in MySQL, but I have not tested it.

4.1 Initial Setup

For this example, I used scaffold command to create a Post page.

rails g scaffold Post name:string

Figure below shows my Post table that contains a name column after I perform a database migration for the new changes.

Figure 4.1.1: Picture of my Post table.

4.2 Add a JSONB Column To Existing Table

Now, let's create a migration file and add a new JSONB column.

rails g migration AddConfigToPost

A file named YYYYMMDDSSSSSS_add_config_to_post.rb is then created and can be found in the app/db/migrate folder. Figure below shows the content of the folder.

Figure 4.2.1: Content of YYYYMMDDSSSSSS_add_config_to_post.rb.

Modify the file by adding the code below to the file.

# app/db/migrate/YYYYMMDDSSSSSS_add_config_to_post.rb

#Code to remove
class AddConfigToPost < ActiveRecord::Migration[5.2]
  def change

#Code to add
class AddConfigToPost < ActiveRecord::Migration[5.2]
  def change
    add_column :posts, :config, :jsonb, :null => false, :default => {}
    add_index  :posts, :config, using: :gin

The code above will add a column named config to the Post table. The default value of the column is an empty JSON: {}. Then, an index type GIN is added.

After a database migration is performed, our Post table is illustrated as the figure below.

Figure 4.2.2: config column is added to Post table successfully.

By inspecting the Structure Tab, we can see that the config column is having JSONB column type with default value set to {}

Figure 4.2.3: Table structure for Post.

4.3 Use JSONB Column To In Form

A few changes need to be made before we can store and retrieve from database. The JSON below is the data that I would like to store in the config field.

  "enable" : "true",
  "type" : "value",

The JSON above contains two keys: enable and type. Now, we will need to add the line below to our Post model. It provide information to the model that the config column contains two accessor enable and disable.

# app/models/post.rb

# Code to remove
class Post < ApplicationRecord

# Code to add
class Post < ApplicationRecord
  store_accessor :config, :enable, :value

Note: You may find more information regarding the store_accessor here

In our Post controller, we have to add the keys enable and disable to the post_params method

# app/controllers/posts_controller.rb

# Code to remove
  def post_params

# Code to add
  def post_params
    params.require(:post).permit(:name, :enable, :value)

Now, we are able to access the enable and value keys easily. In the form, add two input fields.

# app/views/posts/_form.rb

# Code to add to form


  <div class="form-inputs">
    <%= f.input :name %>
    <%= f.input :enable %> <!-- add this line -->
    <%= f.input :value %> <!-- add this line -->


Figure 4.3.1: The final look of the form.

4.4 Testing and Validation

Input values to the form as shown in Figure 4.3.1.

Figure 4.4.1: Input values are shown up correctly

To access the value of enable and value keys from config column, it can be access normally by access it's object.

results matching ""

    No results matching ""