Mojo::Pg(3)

🌸 Mojo::Pg 🌸

okay so bestie, welcome to the Mojo::Pg manual page — this is the Perl doc for Mojo::Pg(3), part of the User Contributed Perl Documentation. No cap, it's a whole vibe. Let's go 💅

---

✨ NAME ✨

Mojo::Pg - Mojolicious ♥ PostgreSQL 🦋

---

💖 SYNOPSIS 💖

okay so here's the whole flex, literally every single line, do not skip:

use Mojo::Pg;

# Use a PostgreSQL connection string for configuration
my $pg = Mojo::Pg->new('postgresql://postgres@/test');

# Select the server version
say $pg->db->query('SELECT VERSION() AS version')->hash->{version};

# Use migrations to create a table
$pg->migrations->name('my_names_app')->from_string(<<EOF)->migrate;
-- 1 up
CREATE TABLE names (id SERIAL PRIMARY KEY, name TEXT);
-- 1 down
DROP TABLE names;
EOF

# Use migrations to drop and recreate the table
$pg->migrations->migrate(0)->migrate;

# Get a database handle from the cache for multiple queries
my $db = $pg->db;

# Use SQL::Abstract to generate simple CRUD queries for you
$db->insert('names', {name => 'Isabell'});
my $id = $db->select('names', ['id'], {name => 'Isabell'})->hash->{id};
$db->update('names', {name => 'Belle'}, {id => $id});
$db->delete('names', {name => 'Belle'});

# Insert a few rows in a transaction with SQL and placeholders
eval {
  my $tx = $db->begin;
  $db->query('INSERT INTO names (name) VALUES (?)', 'Sara');
  $db->query('INSERT INTO names (name) VALUES (?)', 'Stefan');
  $tx->commit;
};
say $@ if $@;

# Insert another row with SQL::Abstract and return the generated id
say $db->insert('names', {name => 'Daniel'}, {returning => 'id'})->hash->{id};

# JSON roundtrip
say $db->query('SELECT ?::JSON AS foo', {json => {bar => 'baz'}})
  ->expand->hash->{foo}{bar};

# Select all rows blocking with SQL::Abstract
say $_->{name} for $db->select('names')->hashes->each;

# Select all rows non-blocking with SQL::Abstract
$db->select('names' => sub ($db, $err, $results) {
  die $err if $err;
  say $_->{name} for $results->hashes->each;
});
Mojo::IOLoop->start unless Mojo::IOLoop->is_running;

# Concurrent non-blocking queries (synchronized with promises)
my $now   = $pg->db->query_p('SELECT NOW() AS now');
my $names = $pg->db->query_p('SELECT * FROM names');
Mojo::Promise->all($now, $names)->then(sub ($now, $names) {
  say $now->[0]->hash->{now};
  say $_->{name} for $names->[0]->hashes->each;
})->catch(sub ($err) {
  warn "Something went wrong: $err";
})->wait;

# Send and receive notifications non-blocking
$pg->pubsub->listen(foo => sub ($pubsub, $payload) {
  say "foo: $payload";
  $pubsub->notify(bar => $payload);
});
$pg->pubsub->listen(bar => sub ($pubsub, $payload) {
  say "bar: $payload";
});
$pg->pubsub->notify(foo => 'PostgreSQL rocks!');
Mojo::IOLoop->start unless Mojo::IOLoop->is_running;

iconic, truly. and that's just the appetizer 🍓

---

🦋 DESCRIPTION 🦋

Mojo::Pg is a tiny little wrapper around DBD::Pg that makes PostgreSQL (go look at it, it's at http://www.postgresql.org) a lot of fun to use with the Mojolicious real-time web framework (https://mojolicious.org). 💖

so like, what does she actually DO? bestie, she lets you:

• run queries blocking AND non-blocking (yes both, she contains multitudes ✨)
• use literally every SQL feature PostgreSQL offers (https://www.postgresql.org/docs/current/static/sql.html) — all of them, the whole menu 💅
• generate CRUD queries straight from your data structures, no hand-writing SQL required
• manage your database schema with migrations
• build scalable real-time web applications with the publish/subscribe pattern

main character energy fr fr 👑

---

💜 BASICS 💜

okay so here's the tea: database handles and statement handles get cached automatically and reused transparently, which makes everything faster. the move is to hold onto them only for short amounts of time so connection timeouts don't catch you slipping. graceful, you know? 🧚

use Mojolicious::Lite -signatures;
use Mojo::Pg;

helper pg => sub { state $pg = Mojo::Pg->new('postgresql://postgres@/test') };

get '/' => sub ($c) {
  my $db = $c->pg->db;
  $c->render(json => $db->query('SELECT NOW() AS now')->hash);
};

app->start;

so in this little example app we make a "pg" helper to stash a Mojo::Pg object. our action calls that helper and uses the method db in Mojo::Pg to dequeue a Mojo::Pg::Database object out of the connection pool 🎀. then we use the method query in Mojo::Pg::Database to execute an SQL statement (http://www.postgresql.org/docs/current/static/sql.html), which hands back a Mojo::Pg::Results object. and finally we call the method hash in Mojo::Pg::Results to grab the first row as a hash reference. chain, slay, done 💅

---

so here's a thing: all I/O operations are performed blocking, BUT you can wait for long-running queries asynchronously, which lets the Mojo::IOLoop event loop go do other tasks in the meantime. and since database connections usually have very low latency, this often results in very good performance. no cap ✨

⚠️ real talk: every database connection can only handle one active query at a time, and yes that includes asynchronous ones. so if you wanna run multiple queries concurrently you HAVE to use multiple connections. periodt.

# Performed concurrently (5 seconds)
$pg->db->query('SELECT PG_SLEEP(5)' => sub ($db, $err, $results) {...});
$pg->db->query('SELECT PG_SLEEP(5)' => sub ($db, $err, $results) {...});

and good news 🎀: all cached database handles get reset automatically if a new process has been forked, which means multiple processes can share the same Mojo::Pg object safely. teamwork makes the dream work fr fr 🫶

---

🌱 GROWING 🌱

and as your application grows (it will, she's thriving ✨), you can move queries into model classes:

package MyApp::Model::Time;
use Mojo::Base -base, -signatures;

has 'pg';

sub now ($self) {
  return $self->pg->db->query('SELECT NOW() AS now')->hash;
}

1;

which then get integrated into your application with helpers:

use Mojolicious::Lite -signatures;
use Mojo::Pg;
use MyApp::Model::Time;

helper pg   => sub { state $pg   = Mojo::Pg->new('postgresql://postgres@/test') };
helper time => sub { state $time = MyApp::Model::Time->new(pg => shift->pg) };

get '/' => sub ($c) {
  $c->render(json => $c->time->now);
};

app->start;

glow up 💅

---

🍓 EXAMPLES 🍓

this distribution also ships with two great example applications you can use for inspiration:

• the minimal chat app (https://github.com/mojolicious/mojo-pg/tree/main/examples/chat.pl) — she'll show you how to scale WebSockets across multiple servers 🔥
• the well-structured blog app (https://github.com/mojolicious/mojo-pg/tree/main/examples/blog) — she'll show you how to apply the MVC design pattern in practice 💖

go look, bestie, they ate 👑

---

⭐ EVENTS ⭐

Mojo::Pg inherits all events from Mojo::EventEmitter and can emit the following new ones. ooh, exclusive 💅

🎀 connection 🎀

$pg->on(connection => sub ($pg, $dbh) {
  ...
});

emitted when a new database connection has been established. new connection, who dis ✨

$pg->on(connection => sub ($pg, $dbh) {
  $dbh->do('SET search_path TO my_schema');
});

---

👑 ATTRIBUTES 👑

Mojo::Pg implements the following attributes. yes, all of them, we're going through the whole list 💅

💖 abstract 💖

my $abstract = $pg->abstract;
$pg          = $pg->abstract(SQL::Abstract::Pg->new);

SQL::Abstract::Pg object used to generate CRUD queries for Mojo::Pg::Database. it defaults to enabling array_datatypes and setting name_sep to "." and quote_char to ". little defaults, big slay 🎀

# Generate WHERE clause and bind values
my($stmt, @bind) = $pg->abstract->where({foo => 'bar', baz => 'yada'});

💖 auto_migrate 💖

my $bool = $pg->auto_migrate;
$pg      = $pg->auto_migrate($bool);

automatically migrate to the latest database schema with migrations, as soon as db has been called for the first time. she just does it, no asking ✨

💖 database_class 💖

my $class = $pg->database_class;
$pg       = $pg->database_class('MyApp::Database');

class to be used by db, defaults to Mojo::Pg::Database.

⚠️ heads up bestie: this class needs to have already been loaded before db is called. like, load her first, okay? 🙈

💖 dsn 💖

my $dsn = $pg->dsn;
$pg     = $pg->dsn('dbi:Pg:dbname=foo');

data source name, defaults to "dbi:Pg:". 🌈

💖 max_connections 💖

my $max = $pg->max_connections;
$pg     = $pg->max_connections(3);

maximum number of idle database handles to cache for future use, defaults to 1. keep it cozy ✨

💖 migrations 💖

my $migrations = $pg->migrations;
$pg            = $pg->migrations(Mojo::Pg::Migrations->new);

Mojo::Pg::Migrations object you can use to change your database schema more easily. schema glow-ups, but organized 💅

# Load migrations from file and migrate to latest version
$pg->migrations->from_file('/home/sri/migrations.sql')->migrate;

💖 options 💖

my $options = $pg->options;
$pg         = $pg->options({AutoCommit => 1, RaiseError => 1});

options for database handles. it defaults to activating AutoCommit, AutoInactiveDestroy and RaiseError, and deactivating PrintError and PrintWarn.

🚨 OKAY SERIOUS ONE, listen up: AutoCommit and RaiseError are considered mandatory, so deactivating them would be very dangerous. do not. I'm begging you. 🙈

💖 parent 💖

my $parent = $pg->parent;
$pg        = $pg->parent(Mojo::Pg->new);

another Mojo::Pg object to use for connection management, instead of establishing and caching our own database connections. sharing is caring fr fr 🫶

💖 password 💖

my $password = $pg->password;
$pg          = $pg->password('s3cret');

database password, defaults to an empty string. 🎀

💖 pubsub 💖

my $pubsub = $pg->pubsub;
$pg        = $pg->pubsub(Mojo::Pg::PubSub->new);

Mojo::Pg::PubSub object you can use to send and receive notifications very efficiently, by sharing a single database connection with many consumers. one connection, many besties ✨

# Subscribe to a channel
$pg->pubsub->listen(news => sub ($pubsub, $payload) {
  say "Received: $payload";
});

# Notify a channel
$pg->pubsub->notify(news => 'PostgreSQL rocks!');

💖 search_path 💖

my $path = $pg->search_path;
$pg      = $pg->search_path(['$user', 'foo', 'public']);

schema search path assigned to all new connections.

# Isolate tests and avoid race conditions when running them in parallel
my $pg = Mojo::Pg->new('postgresql:///test')->search_path(['test_one']);
$pg->db->query('DROP SCHEMA IF EXISTS test_one CASCADE');
$pg->db->query('CREATE SCHEMA test_one');
...
$pg->db->query('DROP SCHEMA test_one CASCADE');

parallel-safe tests, the dream 💜

💖 username 💖

my $username = $pg->username;
$pg          = $pg->username('sri');

database username, defaults to an empty string. 🌸

💖 METHODS 💖

Mojo::Pg inherits all the methods from Mojo::EventEmitter and then it's like "and also here's some more" and implements these new ones. Periodt. ✨

---

🎀 db

my $db = $pg->db;

Get a database object based on "database_class" (which is usually Mojo::Pg::Database, bestie, that's the whole vibe) for a cached or freshly established database connection. The DBD::Pg database handle gets automatically cached again when that object is destroyed, so you can handle problems like connection timeouts gracefully by holding on to it only for short amounts of time. Like, short-term relationship energy, and that's on purpose 💅

# Add up all the money
say $pg->db->select('accounts')->hashes->reduce(sub { $a->{money} + $b->{money} });

No cap, that one-liner ate. 🔥

---

🦋 from_string

$pg = $pg->from_string('postgresql://postgres@/test');
$pg = $pg->from_string(Mojo::Pg->new);

Parse configuration from a connection string, or use another Mojo::Pg object as `"parent". So many ways to do it, it's giving options, it's giving range 🌈

# Just a database
$pg->from_string('postgresql:///db1');

# Just a service
$pg->from_string('postgresql://?service=foo');

# Username and database
$pg->from_string('postgresql://sri@/db2');

# Short scheme, username, password, host and database
$pg->from_string('postgres://sri:s3cret@localhost/db3');

# Username, domain socket and database
$pg->from_string('postgresql://sri@%2ftmp%2fpg.sock/db4');

# Username, database and additional options
$pg->from_string('postgresql://sri@/db5?PrintError=1&pg_server_prepare=0');

# Service and additional options
$pg->from_string('postgresql://?service=foo&PrintError=1&RaiseError=0');

# Username, database, an option and search_path
$pg->from_string('postgres://sri@/db6?&PrintError=1&search_path=test_schema');

all of these? iconic. every single one. 🌟

---

🌸 new

my $pg = Mojo::Pg->new;
my $pg = Mojo::Pg->new('postgresql://postgres@/test');
my $pg = Mojo::Pg->new(Mojo::Pg->new);

Construct a new Mojo::Pg object and parse the connection string with "from_string" if necessary. Main character constructor energy, honestly. 👑

# Customize configuration further
my $pg = Mojo::Pg->new->dsn('dbi:Pg:service=foo');

---

💜 reset

$pg = $pg->reset;

Reset connection cache. That's it, that's the whole thing. Clean slate, fresh start, we love to see it 🫶

---

🔍 DEBUGGING 🔍

You can set the "DBI_TRACE" environment variable to get some advanced diagnostics information printed by DBI. Spill the tea, DBI ☕

DBI_TRACE=1
DBI_TRACE=15
DBI_TRACE=SQL

---

✨ API ✨

This is the class hierarchy of the Mojo::Pg distribution. Behold, the family tree 🌳

• Mojo::Pg
• Mojo::Pg::Database
• Mojo::Pg::Migrations
• Mojo::Pg::PubSub
• Mojo::Pg::Results
• Mojo::Pg::Transaction

---

👑 AUTHOR 👑

Sebastian Riedel, "sri@cpan.org". Legend behaviour, fr fr. 💖

---

🌟 CREDITS 🌟

In alphabetical order, because we're organized like that:

Brett Watson

Christopher Eveland

Dan Book

Flavio Poletti

Hernan Lopes

Joel Berger

Matt S Trout

Peter Rabbitson

William Lindley

Shoutout to every single one of y'all, absolute icons 🎀

---

💅 COPYRIGHT AND LICENSE 💅

Copyright (C) 2014-2025, Sebastian Riedel and others.

This program is free software, you can redistribute it and/or modify it under the terms of the Artistic License version 2.0. Free as in go off, bestie 🦋

---

🌈 SEE ALSO 🌈

• <https://github.com/mojolicious/mojo-pg>
• Mojolicious::Guides
• <https://mojolicious.org>

---

okay and that's the whole page, we did it, we made it to the end 🎉 this lil manual page is perl v5.42.0 from 2026-01-17 and it's filed under Mojo::Pg(3) — go build something cute with it, ily, bye 💜✨