PgSaurus
¶ ↑
An ActiveRecord
extension to get more from PostgreSQL:
-
Create/drop schemas.
-
Use existing functionality in the context of schemas.
-
Set/remove comments on columns and tables.
-
Enhancements to the Rails 4.2 foreign key support (PgSaurus 3.X).
-
Use partial indexes.
-
Create/drop views.
-
Create/drop functions.
-
Create/drop triggers.
-
Use yet more tools.
PgSaurus
is a fork of PgPower.
More information¶ ↑
Environment notes¶ ↑
PgSaurus
v4 was tested with Rails 5.2 and Ruby 2.4. For Rails 4.2, use PgSaurus
v3. For Rails 4.1, use PgSaurus
v2.5+. Older versions of Rails are not supported.
NOTE: JRuby is not supported.
Schemas¶ ↑
Create schema¶ ↑
In migrations you can use create_schema
and drop_schema
methods like this:
class ReplaceDemographySchemaWithPolitics < ActiveRecord::Migration def change drop_schema 'demography' create_schema 'politics' drop_schema_if_exists('demography') create_schema_if_not_exists('politics') end end
Create table¶ ↑
Use schema :schema
option to specify schema name:
create_table "countries", schema: "demography" do |t| # columns goes here end
Move table to another schema¶ ↑
Move table countries
from demography
schema to public
:
move_table_to_schema 'demography.countries', :public
Table and column comments¶ ↑
Provides the following methods to manage comments:
-
set_table_comment(table_name, comment)
-
remove_table_comment(table_name)
-
set_column_comment(table_name, column_name, comment)
-
remove_column_comment(table_name, column_name, comment)
-
set_column_comments(table_name, comments)
-
remove_column_comments(table_name, *comments)
Examples¶ ↑
Set a comment on the given table.
set_table_comment :phone_numbers, 'This table stores phone numbers that conform to the North American Numbering Plan.'
Sets a comment on a given column of a given table.
set_column_comment :phone_numbers, :npa, 'Numbering Plan Area Code - Allowed ranges: [2-9] for first digit, [0-9] for second and third digit.'
Removes any comment from the given table.
remove_table_comment :phone_numbers
Removes any comment from the given column of a given table.
remove_column_comment :phone_numbers, :npa
Set comments on multiple columns in the table.
set_column_comments :phone_numbers, npa: 'Numbering Plan Area Code - Allowed ranges: [2-9] for first digit, [0-9] for second and third digit.', nxx: 'Central Office Number'
Remove comments from multiple columns in the table.
remove_column_comments :phone_numbers, :npa, :nxx
PgSaurus
also adds extra table methods to the change_table
block.
Set comments:
change_table :phone_numbers do |t| t.set_table_comment 'This table stores phone numbers that conform to the North American Numbering Plan.' t.set_column_comment :npa, 'Numbering Plan Area Code - Allowed ranges: [2-9] for first digit, [0-9] for second and third digit.' end change_table :phone_numbers do |t| t.set_column_comments npa: 'Numbering Plan Area Code - Allowed ranges: [2-9] for first digit, [0-9] for second and third digit.', nxx: 'Central Office Number' end
Remove comments:
change_table :phone_numbers do |t| t.remove_table_comment t.remove_column_comment :npa end change_table :phone_numbers do |t| t.remove_column_comments :npa, :nxx end
Foreign keys¶ ↑
PgSaurus
v3 augments Rails 4.2's foreign key methods with:
-
schema support
-
index auto-generation
When you create a foreign key PgSaurus
automatically creates an index. If you do not want to generate an index, pass the exclude_index: true
option. The syntax is compatible with Rails 4.2's foreign key handling methods.
It works with schemas as expected:
add_foreign_key('blog.comments', 'blog.posts')
Adds the index 'index_comments_on_post_id'
:
add_foreign_key(:comments, :posts)
Does not add an index:
add_foreign_key(:comments, :posts, exclude_index: true)
Note that removing a foreign key does not drop the index of the foreign key column. If you want to remove the index, pass in the remove_index: true
option.
remove_foreign_key(:comments, column: :post_id, remove_index: true)
Migration notes - upgrading from Rails 4.2¶ ↑
PgSaurus
v4.X requires Rails 5. Rails 5.2 is recommended. You can use the new Rails 5 semantics to create comments and indexes inline. You also need to use the index order options using the Rails 5 semantics.
# THIS FAILS add_index :books, ["author_id DESC NULLS FIRST", "publisher_id DESC NULLS LAST"], name: "books_author_id_and_publisher_id" # DO THIS INSTEAD add_index :books, ["author_id", "publisher_id"], name: "books_author_id_and_publisher_id", order: { author_id: "DESC NULLS FIRST", publisher_id: "DESC NULLS LAST" }
Migration notes - upgrading from Rails 4.1¶ ↑
PgSaurus
v3.X now uses the Rails 4.2 semantics for add_foreign_key
and remove_foreign_key
. See api.rubyonrails.org/v4.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html
A few things have changed. The most breaking change is that the syntax remove_foreign_key :from_table, :to_table, options
no longer works.
#THIS FAILS remove_foreign_key :comments, :posts, remove_index: true #DO THIS INSTEAD remove_foreign_key :comments, column: :post_id, remove_index: true
For adding foreign keys, the :dependent
option is replaced with :on_delete
and :on_update
. The :delete
value is replaced with :cascade
.
#OLD STYLE - NO LONGER WORKS add_foreign_key :comments, :posts, dependent: :delete #NEW STYLE - DO THIS INSTEAD add_foreign_key :comments, :posts, on_delete: :cascade
Partial Indexes¶ ↑
Rails 4.x pull request was used as a starting point to patch it to be schema-aware.
Examples¶ ↑
Add a partial index to a table:
add_index(:comments, [:country_id, :user_id, :category], where: "category IN ('foo', 'bar')")
Add a partial index to a schema-qualified table:
add_index('blog.comments', :status, where: "status = 'active'")
Indexes on Expressions¶ ↑
PostgreSQL supports indexes on expressions. Right now, only basic functional expressions are supported.
Examples¶ ↑
Add an index to a column with a function:
add_index(:comments, "lower(text)")
You can also specify the index access method:
create_extension 'btree_gist' create_extension 'fuzzystrmatch' add_index(:comments, 'dmetaphone(author)', using: 'gist')
Indexes with operator classes¶ ↑
Specifying an operator class on each column of an index is supported.
Examples¶ ↑
Add an index with a custom ops class:
add_index(:books, "title varchar_pattern_ops")
Concurrent index creation¶ ↑
PostgreSQL supports concurrent index creation. PgSaurus
supports that feature by adding support to the migration DSL on index and foreign key creation.
Examples¶ ↑
Add an index concurrently to a table:
add_index :table, :column_id, concurrently: true
Add an index concurrently along with foreign key:
add_foreign_key :table1, :table2, column: :column_id, concurrent_index: true
Loading/Unloading postgresql extension modules¶ ↑
PostgreSQL ships with a number of extension modules. PgSaurus
provides some tools to load and unload such modules using migrations.
Please note: CREATE
/DROP EXTENSION
command was introduced in PostgreSQL 9.1. So this functionality is not available in previous versions.
Examples¶ ↑
Load fuzzystrmatch extension module; and create its objects in schema public:
create_extension "fuzzystrmatch"
Load version 1.0 of the btree_gist extension module; and create its objects in schema demography.
create_extension "btree_gist", schema_name: "demography", version: "1.0"
Unload an extension module:
drop_extension "fuzzystrmatch"
Views¶ ↑
PgSaurus
v1.6.0 introduced experimental support for creating views. This API should only be used with the understanding that it is preliminary 'alpha' at best.
Examples¶ ↑
create_view "demography.citizens_view", "select * from demography.citizens" drop_view "demography.citizens_view"
Roles¶ ↑
If you want to execute a migration as a specific PostgreSQL role you can use the set_role
method:
class CreateRockBands < ActiveRecord::Migration set_role "rocker" def change create_table :rock_bands do |t| # create columns end end end
Technically it is equivalent to the following:
class CreateRockBands < ActiveRecord::Migration def change execute "SET ROLE rocker" create_table :rock_bands do |t| # create columns end ensure execute "RESET ROLE" end end
You may force all migrations to have set_role
, for this, configure PgSaurus
with ensure_role_set = true
:
PgSaurus.configure do |config| config.ensure_role_set = true end
Functions¶ ↑
You can create, list, and drop functions.
Examples¶ ↑
Create a function:
pets_not_empty_function = <<-SQL BEGIN IF (SELECT COUNT(*) FROM pets) > 0 THEN RETURN true; ELSE RETURN false; END IF; END; SQL # Arguments are: function_name, return_type, function_definition, options (currently, only :schema and :volatility) create_function 'pets_not_empty()', :boolean, pets_not_empty_function, schema: 'public', volatility: 'stable'
Drop a function:
drop_function 'pets_not_empty()'
Get a list of defined functions:
ActiveRecord::Base.connection.functions
Triggers¶ ↑
You can create and remove triggers on tables and views.
Examples¶ ↑
Create a trigger:
create_trigger :pets, # Table or view name :pets_not_empty_trigger_proc, # Procedure name. Parentheses are optional if you have no arguments. 'AFTER INSERT', # Trigger event for_each: 'ROW', # Can be a row or a statement. Default is row. schema: 'public', # Optional schema name constraint: true, # Sets whether the trigger is a constraint. Default is false. deferrable: true, # Sets whether the trigger is immediate or deferrable. Default is immediate. initially_deferred: true, # Sets whether the trigger is initially deferred. Default is immediate. # Only relevant if the trigger is deferrable. condition: "new.name = 'fluffy'" # Optional when condition. Default is none.
Drop a trigger:
remove_trigger :pets, :pets_not_empty_trigger_proc
Get a list of defined triggers on a table or view:
ActiveRecord::Base.connection.triggers
Tools¶ ↑
PgSaurus::Tools
provides a number of useful methods for managing schemas, etc.:
PgSaurus::Tools.create_schema "services" # => create new PG schema "services" PgSaurus::Tools.create_schema "nets" # => create new PG schema "nets" PgSaurus::Tools.drop_schema "services" # => remove the PG schema "services" PgSaurus::Tools.create_schema_if_not_exists "nets" # => Does nothing -- schema "nets" already exists PgSaurus::Tools.drop_schema_if_exists "services" # => Does nothing -- schema "services" doesn't exist PgSaurus::Tools.schemas # => ["public", "information_schema", "nets"] PgSaurus::Tools.index_exists?(table, columns, options) # => returns true if an index exists for the given params
Running tests¶ ↑
-
Ensure your
postgresql
haspostgres-contrib
(if you're on Ubuntu) package installed. Tests depend on thebtree_gist
andfuzzystrmatch
extensions -
If you're on a Mac, see below for installing contrib packages
-
Configure
spec/dummy/config/database.yml
for development and test environments. -
Run
rake spec
. -
Make sure migrations don't raise exceptions and all specs pass.
Installing contrib packages on Mac OS X:¶ ↑
-
This assumes you are using MacPorts to install
postgresql
. If you're using Homebrew or the Postgres App, you will need to adjust the instructions accordingly (please add to this README when you do) -
Assuming you installed with default options (including auto-clean), you will need to rebuild the
postgresql
port and keep the build files -
sudo port -k -s build postgresql94
-
(adjust the version number above appropriately)
-
Now you can make and install the
btree_gist
and any other contrib modules -
cd $(port work postgresql94)/postgresql-9.4.7/contrib/btree_gist
-
(again, you may need to adjust the version number to your specific version)
-
sudo make all
-
sudo make install
-
Done!
TODO¶ ↑
Support for Rails 6+
-
Rails 6 support has not been tested as of yet.
Possible support for JRuby:
-
Jdbc driver provides its own
create_schema(schema, user)
method - solve conflicts.
Credits¶ ↑
-
Potapov Sergey - schema support, role support
-
Arthur Shagall - function and trigger support, Rails 4.2 support - and thanks for pg_comment
-
Mitya Lyubarskyy - view support, Rails 4.2 support
-
Artem Ignatyev - extension modules load/unload support
-
Marcelo Silveira - thanks for rails partial index support that was backported into this gem
Copyright and License¶ ↑
-
Copyright © 2016 HornsAndHooves.
-
Initial foreign key code taken from foreigner, Copyright © 2009 Matthew Higgins
-
pg_comment Copyright © 2011 Arthur Shagall
-
Partial index Copyright © 2012 Marcelo Silveira
-
PgPower Copyright © 2012 TMX Credit.
Released under the MIT License. See the MIT-LICENSE file for more details.
Contributing¶ ↑
Contributions are welcome. However, before issuing a pull request, please make sure of the following:
-
All specs are passing (under Ruby 2.4+)
-
Any new features have test coverage.
-
Anything that breaks backward compatibility has a very good reason for doing so.