One of the reasons we chose to build durable workflows on Postgres was because of the richness of its ecosystem. Postgres is the most popular database in the world, but it isn’t just a database: it can process incoming data with triggers, schedule tasks with pg_cron, integrate other data sources via foreign data wrappers, and much more.
To make it easier to integrate workflows with the Postgres ecosystem, we just released a PostgreSQL workflows client. This lets you enqueue workflows (or send messages to them) directly from Postgres by calling a SQL function. Here’s what that looks like:
One important use case is to enqueue a workflow from a database trigger. For example, let’s say you want to use a workflow to process each new row in a table. You can create a Postgres trigger that runs on each insert into the table and starts a workflow processing the newly inserted row. This guarantees that exactly one workflow is launched to process each new row of the table:
Alternatively, you can enqueue a workflow as part of a larger database transaction, for example as part of a transactional outbox pattern. This way, you guarantee that the workflow enqueue is done atomically with your other database updates:
How to Enqueue Durable Workflows via Postgres UDFs
Enqueueing workflows from Postgres is powered by a user-defined function that’s created as part of your application’s system database. Here’s what it looks like, in all its gory detail:
This looks complex, so let’s break it down. Essentially, this defines an enqueue_workflow function you can call from a SQL statement. The function takes in two required arguments (workflow name and queue name) and a number of optional arguments, such as workflow inputs or workflow ID. When it’s called, the function:
- Validates and normalizes its arguments
- Coerces the workflow inputs into a JSON format that the workflow execution library can parse
- Enqueues the workflow by inserting a new row (containing all the input arguments) into the workflow_status table
This works because workflows are just database rows. Once a new row defining a new workflow is inserted, the workflow is ready to go: a server can dequeue it, parse its inputs from JSON, and execute it.
A similar function allows sending messages to workflows from Postgres:
This function takes in a workflow ID and a message (and optionally a topic and idempotency key) and inserts the message as a new row into the notifications table for that workflow and topic. The workflow can later receive and process the message by consuming it from the notifications table.
Learn More
To learn more about how to manage workflows from Postgres, check out the documentation.
If you like making systems reliable, we’d love to hear from you. At DBOS, our goal is to make durable workflows as easy to work with as possible. Check it out:
- Quickstart: https://docs.dbos.dev/quickstart
- GitHub: https://github.com/dbos-inc
- Discord community: https://discord.gg/eMUHrvbu67
