Sergey Potapov

Talking about Linux, Ruby and other hackers' stuff.

Pg_power - ActiveRecord Extension for PostgreSQL

I am happy to announce that TMXCredit released pg_power gem - an ActiveRecord extension which allows to use number of PostgreSQL features with Rails.

What you can do with pg_power?

  • Use PostgresSQL schemas in your Rails project.
  • Add comments to PostgreSQL database with Rails migrations.
  • Use foreign keys (we imported foreigner functionality and made it schema aware).
  • Use partial indexes.
  • Add indexes concurrently.

You’ll find enough documentation in README file.

Quick usage example

Assume you want to create tables countries and languages in demography schema.

At first we need to create demography schema:

db/migrate/create_demography_schema.rb
1
2
3
4
5

class CreateDemographySchema < < ActiveRecord::Migration
  def change
    create_schema 'demography'
  end
end

Now let’s create tables:

db/migrate/create_demography_languages.rb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

class CreateDemographyLanguages < ActiveRecord::Migration
  def change
    # Create table `languages` in schema `demography`
    create_table "languages", :schema => "demography" do |t|
      t.string :name
      t.string :code, :limit => 2
    end

    # Add PostgreSQL comments
    set_table_comment "demography.languages", "List of languages"
    set_column_comments "demography.languages",
        :name => "Full name of language in English",
        :code => "ISO 639-1 code"
  end
end
db/migrate/create_demography_countries.rb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

class CreateDemographyContries < ActiveRecord::Migration
  def change
    # Create table `countries` in schema `demography`
    create_table "countries", :schema => "demography" do |t|
      t.string :name

      # In real life you likely would have many-to-many associaton
      t.integer :language_id
    end

    # Add PostgreSQL comments
    set_table_comment "demography.countries", "List of world countries"
    set_column_comments "demography.languages",
        :name        => "Full name of country in English",
        :language_id => "Most popular language in the country"

    # Add foreign key and create index on demography.countries.language_id
    add_foreign_key("demography.countries", "demography.languages")
  end
end

Great! Now we need to set table names in models to make ActiveRecord know that these tables are located in demography schema.

app/models/language.rb
1
2
3

class Language < ActiveRecord::Base
  set_table_name "demography.languages"
end

It will work. But I would recommend you to create module Demography which would represent demography schema and move those models to it. One more benefit is that you can define schema prefix in module and models will use it build table name automatically.

app/models/demography.rb
1
2
3
4
5

module Demography
  def self.table_name_prefix
    'demography.'
  end
end
app/models/demography/language.rb
1
2
3

module Demography::Language
  # No need to use set_table_name anymore
end

I hope you will enjoy pg_power. Let us know what you think!

Thanks. Sergey Potapov.

Comments