Create Migrations for HStore, JSON and JSONB with Rails

JSONB, JSON and things like HSTORE are good for when you can’t use a normalized data model, such as when the data model changes rapidly and is user defined.

Of course, the main rule is that if you can model them relationally, you should. And if you can choose between the options, JSON, JSONB and HSTORE, the priority should be JSONB, JSON and then HSTORE.

Here’s a comprehensive resource on the matter: When to use unstructured datatypes in Postgres–Hstore vs. JSON vs. JSONB

In most cases JSONB is likely what you want when looking for a NoSQL, schema-less, datatype. Hstore and JSON can have their place as well but it’s less common. More broadly, JSONB isn’t always a fit in every data model. Where you can normalize there are benefits, but if you do have a schema that has a large number of optional columns (such as with event data) or the schema differs based on tenant id then JSONB can be a great fit. In general you want:

* JSONB – In most cases
* JSON – If you’re just processing logs, don’t often need to query, and use as more of an audit trail
* hstore – Can work fine for text based key-value looks, but in general JSONB can still work great here

Here’s a migration sample for Rails for JSONB:

class CreateAccountSubscriptions < ActiveRecord::Migration
  def change
    create_table :account_subscriptions do |t|
      t.date :start_date
      t.date :renewal_date
      t.decimal :amount,precision: 10, scale: 2, default: 0
      t.string :billing_cycle
      t.string :name
      t.json :details
      t.string :payment_type
    end
  end
end
### Migration...
== 20180405011531 CreateAccountSubscriptions: migrating =======================
-- create_table(:account_subscriptions)
   -> 0.0408s
== 20180405011531 CreateAccountSubscriptions: migrated (0.0409s) ==============

Here’s a migration sample for Rails for JSON:

class CreateAccountSubscriptions < ActiveRecord::Migration
  def change
    create_table :account_subscriptions do |t|
      t.date :start_date
      t.date :renewal_date
      t.decimal :amount,precision: 10, scale: 2, default: 0
      t.string :billing_cycle
      t.string :name
      t.jsonb :details
      t.string :payment_type
    end
  end
end
### Migration...
== 20180405011531 CreateAccountSubscriptions: migrating =======================
-- create_table(:account_subscriptions)
   -> 0.0420s
== 20180405011531 CreateAccountSubscriptions: migrated (0.0421s) ==============

Here’s a migration sample for Rails for HSTORE:

class CreateAccountSubscriptions < ActiveRecord::Migration
  def change
    enable_extension 'hstore' unless extension_enabled?('hstore')
    create_table :account_subscriptions do |t|
      t.date :start_date
      t.date :renewal_date
      t.decimal :amount,precision: 10, scale: 2, default: 0
      t.string :billing_cycle
      t.string :name
      t.hstore :details
      t.string :payment_type
    end
  end
end
### Migration...
-- extension_enabled?("hstore")
   -> 0.1217s
-- enable_extension("hstore")
   -> 0.3896s
-- create_table(:account_subscriptions)
   -> 0.0880s
== 20180405011531 CreateAccountSubscriptions: migrated (0.5996s) ==============

 Rails ActiveRecord: Get last records

Do you want to get the last few records on a database table using the Rails ActiveRecord? Here’s a simple way to do it:

Get the last record

Grower.last
#<Grower:0x007fdf8e55de10
 id: 713,
 name: "Diamondback",
 created_at: Tue, 06 Mar 2018 03:16:09 EST -05:00,
 updated_at: Tue, 06 Mar 2018 03:16:09 EST -05:00>

Get the last few records

Grower.last(5)
[#<Grower:0x007fdf917e7ed0
 id: 709,
 name: "Crossbones",
 created_at: Tue, 06 Mar 2018 03:16:09 EST -05:00,
 updated_at: Tue, 06 Mar 2018 03:16:09 EST -05:00>,
#<Grower:0x007fdf99a50070
 id: 710,
 name: "Winter Soldier",
 created_at: Tue, 06 Mar 2018 03:16:19 EST -05:00,
 updated_at: Tue, 06 Mar 2018 03:16:19 EST -05:00>,
#<Grower:0x007fdf99a501b0
 id: 711,
 name: "Punisher",
 created_at: Tue, 06 Mar 2018 03:16:29 EST -05:00,
 updated_at: Tue, 06 Mar 2018 03:16:29 EST -05:00>,
#<Grower:0x007fdf99a502f0
 id: 712,
 name: "TaskMaster",
 created_at: Tue, 06 Mar 2018 03:16:30 EST -05:00,
 updated_at: Tue, 06 Mar 2018 03:16:30 EST -05:00>,
#<Grower:0x007fdf99a52de8
 id: 713,
 name: "Diamondback",
 created_at: Tue, 06 Mar 2018 03:16:39 EST -05:00,
 updated_at: Tue, 06 Mar 2018 03:16:39 EST -05:00>]

Easy Peezy!

 Heroku: How to Restore Database dump

Restore database to local database.

Remember that your database should already exist.

pg_restore --clean --dbname=database_name --username=postgres1 ~/Downloads/dump

Prerequites

If you can’t remember how to create the backup, here’s a simple script

Create the backup

heroku pg:backups:capture -a appname
Starting backup of postgresql-acute-52332... done

Use Ctrl-C at any time to stop monitoring progress; the backup will continue running.
Use heroku pg:backups:info to check progress.
Stop a running backup with heroku pg:backups:cancel.

Backing up DATABASE to b117... done

Download the backup

You will need to get the url of the backup you just created. Here is the script to retrieve the url of your backup. You can just paste this on to your browser address bar.

heroku pg:backups public-url b117 -a appname

Done!

 Sass Cheatsheet

Variables

$white: #FFF;

body {
  color: $white;
}

Mixins

Example 1

@mixin font-size($times) {
  font-size: $times * 1.2px;
}

.highlight {
  @include font-size(3);
}

Example 2

@mixin border-radius($radius) {
  -webkit-border-radius: $radius;
     -moz-border-radius: $radius;
      -ms-border-radius: $radius;
          border-radius: $radius;
}

.box {
  @include border-radius(10px);
}

Extends

%button {
  display: inline-block;
  margin-bottom: 0;
  font-weight: normal;
  text-align: center;
  vertical-align: middle;
  cursor: pointer;
  background-image: none;
  border: 1px solid transparent;
  padding: 8px 12px;
  font-size: 14px;
  line-height: 1.42857143;
  border-radius: 4px;
}

.white-button {
   @extend %button;
  background-color: #FFF;
}

.green-button {
   @extend %button;
  background-color: #00FF00;
}