This gem provides a way to implement the transactional outbox pattern using triggers (stored procedures) in the PostgreSQL RDBMS.
This only provides the "writing events to the outbox table" part of the outbox pattern. It does not provide the "reading and handling events from the outbox table" part. That is up to the application to implement.
There are many libraries that can be used to read and handle events from the outbox table, among them are:
PgtOutbox utilizes the ruby Sequel ORM to interact with the database, but once it’s set up, it doesn’t require any ruby nor Sequel code for the outbox population to operate. It all runs on the PostgreSQL cluster, guaranteeing no matter how the database writes occur, the outbox events will always be generated.
-
Create outbox table(s) to store events
-
Create function(s) to write events to outbox table
-
Create trigger(s) to attach to tables so their changes write events to the outbox(es)
Install the gem and add to the application’s Gemfile by executing:
bundle add sequel-pgt_outboxIf bundler is not being used to manage dependencies, install the gem by executing:
gem install sequel-pgt_outboxIf you do not want to install the gem, you can use the provided OCI Container image, available at the Rubyists Github Container Registry.
sequel -r sequel/pgt_outbox postgres:///spgt_test
Your database is stored in DB...
irb(main):000> DB.extension :pgt_output
=> #<Sequel::Postgres::Database database=spgt_test>
irb(main):001> DB.create_table(:foo) { primary_key :id; String :s; Integer :i }
=> nil
irb(main):002> function = DB.pgt_setup_outbox(:foo)
=> "pgt_outbox_foo_outbox"
irb(main):003> DB[:foo].insert(s: 'foo', i: 1)
=> 1
irb(main):004> DB[:foo_outbox].first
=> nil
irb(main):005> DB.pgt_outbox_events(:foo, function)
=>
#<Rubyists::PgtOutbox::Trigger:0x00007f1ae2fbc2b0
@db=#<Sequel::Postgres::Database database=spgt_test>,
@events=[:insert, :update, :delete],
@function="pgt_outbox_foo_outbox",
@name="pgt_outbox_foo_outbox",
@opts={when: nil},
@table=:foo,
@trigger_opts={after: true, each_row: true}>
irb(main):006> DB[:foo].insert(s: 'bar', i: 2)
=> 2
irb(main):007> DB[:foo_outbox].first
=>
{id: 1,
attempts: 0,
completed: nil,
created: 2025-02-15 18:20:30.28394 +0000,
updated: 2025-02-15 18:20:30.28394 +0000,
attempted: nil,
event_type: "foo_created",
last_error: nil,
data_before: nil,
data_after: "{\"i\": 2, \"s\": \"bar\", \"id\": 2}",
metadata: nil} % psql spgt_test
psql (16.4)
Type "help" for help.
spgt_test=# create table foo (id serial primary key, s text, i integer);
CREATE TABLE
spgt_test=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+----------+----------
public | foo | table | bougyman
public | foo_id_seq | sequence | bougyman
(2 rows)
spgt_test=# \q
bougyman@framezotz ~/rubyists/sequel-pgt_outbox
% outboxify postgres:///spgt_test foo
bougyman@framezotz ~/rubyists/sequel-pgt_outbox
% psql spgt_test
psql (16.4)
Type "help" for help.
spgt_test=# \d foo
Table "public.foo"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------------------------------
id | integer | | not null | nextval('foo_id_seq'::regclass)
s | text | | |
i | integer | | |
Indexes:
"foo_pkey" PRIMARY KEY, btree (id)
Triggers:
pgt_outbox_foo_outbox AFTER INSERT OR DELETE OR UPDATE ON foo FOR EACH ROW EXECUTE FUNCTION pgt_outbox_foo_outbox()
spgt_test=#After checking out the repo, run bundle install or bin/setup to install dependencies.
Then, either set the PGT_SPEC_DB environment variable to an existing PostgreSQL db of your choice,
or run rake createdb to create a testing one (spgt_test).
Finally 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 from source onto your local machine, run bundle exec rake install.
This project uses the Conventional Commits specification for commit messages, as well as for PR titles. This allows for automated (by release-please) release management, changelog generation, and versioning. Please follow the specification when writing commit messages.
Bug reports and pull requests are welcome on GitHub at https://github.com/rubyists/sequel-pgt_outbox. Discussions are welcome in the discussions section of the repository.
The gem is available as open source under the terms of the MIT License.
Everyone interacting in the PgtOutbox project’s codebases, issue trackers, and discussions is expected to follow the Code of Conduct.