Gem Version

PgObjects

Simple manager for PostgreSQL objects like triggers and functions.

Inspired by https://github.com/neongrau/rails_db_objects

Installation

Add this line to your application's Gemfile:

gem 'pg_objects'

And then execute:

bundle install

Or install it yourself as:

gem install pg_objects

Run the installation procedure to initialize directories structure and configuration file:

bundle exec rails generate pg_objects:install

Supported object types

The following CREATE statements are recognized as manageable objects:

AGGREGATE, CONVERSION, DOMAIN, EVENT TRIGGER, EXTENSION, FUNCTION, INDEX, MATERIALIZED VIEW, OPERATOR, OPERATOR CLASS, POLICY, RULE, SEQUENCE, TABLE, TEXT SEARCH PARSER, TEXT SEARCH TEMPLATE, TRIGGER, TYPE (composite, enum, range, base, and shell forms), VIEW.

Files containing any other statement raise PgObjects::UnknownObjectTypeError during loading.

Usage

Store DB objects as CREATE (or CREATE OR UPDATE) queries in files within a directory structure (default: db/objects).

You can control the order of creation by using the directive depends_on in an SQL comment:

--!depends_on my_another_func
CREATE FUNCTION my_func()
...

The string after the directive should be the name of the file that the dependency refers to, without the file extension.

Directive syntax

  • The directive must start the line — no leading whitespace.
  • Both --! (SQL comment) and #! prefixes are supported.
  • List several dependencies on one line, separated by commas and/or whitespace, or use a separate directive line for each:
--!depends_on func_a, func_b, func_c
--!depends_on func_d
#!depends_on func_e
CREATE FUNCTION my_func()
...

Configuration

You have the option to configure the gem using either a YAML file or a Ruby initializer. The priority order for configuration is as follows:

  1. Ruby initializer
  2. YAML config
  3. Default values

YAML

Create pg_objects.yml in the application config directory.

The file is loaded on first configuration access and resolved against Rails.root in Rails applications, so it is found even when the gem is required before the process changes into the app root (e.g. under the Spring preloader). Outside Rails the path is relative to the current working directory.

# pg_objects.yml

# Specify the directories where the DB objects files are located
directories:
  before: path/to/objects/before # executed before the migrations
  after: path/to/objects/after # executed after the migrations

# Specify the file extensions of the DB objects files
extensions:
  - sql
  - txt

# Suppress non-error output to console (error messages are always printed)
silent: false

# Whether to wrap each object-creation run in a database transaction so a
# failure rolls back everything created in that run (default: true).
# Note: some PostgreSQL statements cannot run inside a transaction
# (e.g. CREATE INDEX CONCURRENTLY, VACUUM) — set to false if your
# object files contain them.
transactional: true

# Whether to install the Rake hooks that auto-create objects (default: true)
auto_hook_migrations: true

Initializer

Create file in config/initializers directory with the following content:

PgObjects.configure do |config|
  config.before_path = 'path/to/objects/before' # default: 'db/objects/before'
  config.after_path = 'path/to/objects/after' # default: 'db/objects/after'
  config.extensions = ['sql', 'txt'] # default: 'sql'
  config.silent = true # suppress non-error output to console (errors are always printed), default: false
  config.transactional = false # opt out of the wrapping transaction, default: true
end

Otherwise, the default values will be used.

Please make sure to verify that the specified directories actually exist.

[!NOTE] Object creation runs inside a single database transaction by default, so a failure mid-run rolls back every object created in that run. PostgreSQL rejects some statements inside a transaction block — for example CREATE INDEX CONCURRENTLY, VACUUM, or ALTER TYPE ... ADD VALUE on PostgreSQL versions before 12. If your object files contain such statements, set config.transactional = false.

Rake tasks that trigger object creation

Object creation is hooked into Rake tasks. By default:

Task before objects after objects
db:migrate ✓ ✓
db:schema:load ✓
db:migrate:redo ✓ ✓

db:rollback is not hooked — rolling a migration back should not recreate objects.

You can also invoke the underlying tasks directly: db:create_objects:before and db:create_objects:after.

Multiple databases

By default objects are created over the global connection (ActiveRecord::Base.connection). In a Rails multi-database setup, target a specific database by passing its connection to the manager:

PgObjects::Manager.new(connection: AnimalsRecord.connection).load_files(:before).create_objects

For the rake tasks, set PG_OBJECTS_CONNECTION_CLASS to the name of the Active Record class whose connection should be used:

PG_OBJECTS_CONNECTION_CLASS=AnimalsRecord bin/rails db:create_objects:before

To disable all hooks entirely, set auto_hook_migrations to false in an initializer; then create objects only by invoking those tasks manually:

PgObjects.configure do |config|
  config.auto_hook_migrations = false # default: true
end

Re-running and idempotency

The hooked tasks re-execute every object file on each run — the gem keeps no state about what was already created. Files must therefore contain re-runnable (idempotent) SQL, or the second db:migrate fails with errors like relation "..." already exists.

What PostgreSQL offers per object type:

Object type Idempotent form
FUNCTION, VIEW CREATE OR REPLACE
RULE CREATE OR REPLACE
AGGREGATE CREATE OR REPLACE (PostgreSQL 12+)
TRIGGER CREATE OR REPLACE (PostgreSQL 14+), otherwise drop-and-recreate
TABLE, INDEX, SEQUENCE, MATERIALIZED VIEW, EXTENSION IF NOT EXISTS
TYPE, DOMAIN, POLICY, CONVERSION, OPERATOR, OPERATOR CLASS, EVENT TRIGGER, TEXT SEARCH PARSER/TEMPLATE none — use a guard (below)

For types without an idempotent form, either drop first:

DROP POLICY IF EXISTS user_policy ON users;
CREATE POLICY user_policy ON users USING (user_id = current_user_id());

or swallow the duplicate error in a DO block:

DO $$ BEGIN
  CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

[!NOTE] Object files are classified by parsing their first statement. When that statement is a DROP or a DO block (as in the guards above), the SQL object name cannot be extracted. The file still executes normally, but it can only be referenced by its file identifiers — the extensionless file name or the file path — while --!depends_on directives referring to the SQL object name (or its schema-qualified form) will not match it. The plain IF NOT EXISTS / OR REPLACE forms keep full name resolution.

Also note that object creation runs inside a single transaction by default (see above), so one failing statement rolls back the entire run — a half-idempotent set of files either all applies or not at all.

Override hook_tasks to customize which tasks/stages are hooked (e.g. an empty hash also disables all hooks). Configure it in a Rails initializer: the hooks are installed when the gem's rake tasks load, which happens after initializers run, so an initializer value is always picked up. Changing hook_tasks later (after task loading) has no effect on which hooks are installed.

PgObjects.configure do |config|
  config.hook_tasks = {
    'db:migrate' => %i[before after],
    'db:schema:load' => %i[after]
  }
end

Development

After checking out the repo, run bin/setup to install dependencies. Then, run rake spec to run the tests. You can also run bin/console for an interactive prompt that will allow you to experiment.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and tags, and push the .gem file to rubygems.org.

Performance Benchmarking

You can measure the performance of query parsing including file I/O operations using the included benchmark tool:

# Run the benchmark
bundle exec rake benchmark

# Or run directly
bundle exec bin/benchmark

The benchmark measures:

  • File I/O Performance: Time to read SQL files from disk
  • SQL Parsing Performance: Time to parse SQL queries using pg_query
  • Dependency Extraction Performance: Time to extract --!depends_on directives from comments
  • Full Workflow Performance: Combined time for file I/O, parsing, and dependency extraction
  • Memory Usage: Memory consumption during the parsing process

The benchmark creates temporary SQL files of various sizes and complexities to provide realistic performance metrics. Results include throughput (files/objects per second) and average processing time per file.

Example output:

PG Objects Performance Benchmark
==================================================

File I/O Performance:
  Files processed: 59
  Time: 0.0005s
  Throughput: 108574.84 files/s

Parsing Performance:
  Files processed: 59
  Successful parses: 59
  Parse errors: 0
  Time: 0.0092s
  Throughput: 6411.8 files/s

Full Workflow Performance:
  Objects processed: 59
  Time: 0.0083s
  Throughput: 7105.78 objects/s

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/marinazzio/pg_objects.

Every pull request that changes gem behavior must add an entry to the [Unreleased] section of CHANGELOG.md (following the Keep a Changelog format). Pure refactorings, spec-only, and dependency-bump PRs may skip this.

Releasing

  1. Move the [Unreleased] entries in CHANGELOG.md under a new version heading with the release date, and update the comparison links at the bottom of the file.
  2. Bump PgObjects::VERSION in lib/pg_objects/version.rb accordingly (semantic versioning).
  3. Tag the commit vX.Y.Z and publish a GitHub release — the publish workflow builds and pushes the gem to RubyGems.