PgBuilder

PgBuilder is a Ruby gem for building PostgreSQL schema deployments from many small SQL files. It turns your src/ tree into Rake tasks, resolves declared dependencies, and produces a final merged build/schema.sql.

Why PgBuilder

• Keep database objects in small, reviewable SQL files.
• Control creation order with explicit dependencies.
• Build a single deployable script from source files.
• Generate Cucumber features for tables/functions to validate your schema.

Requirements

• Ruby >= 3.0
• PostgreSQL (for running generated SQL and tests)
• Docker (for running the Cucumber integration tests)

Installation

gem install pg_builder

Or in your project Gemfile:

gem "pg_builder"

Quick Start

1) Load PgBuilder tasks in your project

Create a Rakefile in your database project root:

require "pg_builder"
spec = Gem::Specification.find_by_name("pg_builder")
load "#{spec.gem_dir}/lib/pg_builder/Rakefile"

# Optional: provide this task if your project grants permissions.
task :permissions do
  # load/run permission SQL here
end

rake uses this task graph:

• default -> complete
• complete -> init, schemas:all, permissions, schema.sql
• init -> clean, tmp/, roles:all

If you do not need permissions, define an empty task :permissions.

2) Create your src/ layout

project_root/
  src/
    roles/
      app_reader.sql
      app_writer.sql
    schemas/
      accounting.sql
      accounting/
        types/
          invoice_status.sql
        sequences/
          invoice_id_seq.sql
        tables/
          invoice.sql
          invoice_line.sql
        functions/
          calculate_invoice_total.sql
        trigger_functions/
          set_updated_at.sql

3) Declare dependencies in SQL files (optional)

The first line can declare dependencies:

-- depends_on: ["::schemas:accounting:types:invoice_status", "invoice_line"]

Rules:

• Use : as path separator.
• Use :: prefix for absolute paths from src/.
• Relative paths are resolved from the current SQL file directory.
• Do not include .sql in dependency names.

PgBuilder also auto-adds the parent schema as a dependency for objects under schemas/<name>/... (except public).

4) Build

rake

This creates build/schema.sql from the ordered SQL sources.

Generators

Create a new schema skeleton:

rake generate:schema[schema_name]

This creates:

• src/schemas/schema_name.sql
• src/schemas/schema_name/functions/
• src/schemas/schema_name/sequences/
• src/schemas/schema_name/tables/
• src/schemas/schema_name/trigger_functions/
• src/schemas/schema_name/types/

Testing with Cucumber

Run:

bundle exec rake cucumber

During tests PgBuilder:

• spins up a PostgreSQL Docker container,
• generates feature files under features/schemas/generated,
• applies SQL from tmp/*.sql,
• runs Cucumber scenarios for supported object types (tables, functions).

Optional env var:

• PG_BUILDER_EXTENSION_SCHEMA: creates this schema before tests (useful for extension-related testing).

Detailed guide: docs/integration-testing.md

Common Issues

• Missing dependency file -> build fails with Cannot find dependency ...
• Missing permissions task in your project Rakefile -> Don't know how to build task 'permissions'
• Invalid dependency format -> make sure it is valid JSON in the first line after -- depends_on:

Contributing

Issues and pull requests are welcome at github.com/brunoenten/pg_builder.

License

MIT - see LICENSE.txt.