Mojo::Pg::Database(3)

💖 Mojo::Pg::Database 💖

okay bestie, welcome to Mojo::Pg::Database(3) — from the User Contributed Perl Documentation, no less ✨. this is the page where your database handle gets its whole personality, and honestly? it's giving main character energy. let's get into it 🫶

---

✨ NAME ✨

Mojo::Pg::Database — it's a Database, bestie. that's the whole vibe. 💅

---

🌸 SYNOPSIS 🌸

like, just look at how clean this is:

use Mojo::Pg::Database;

my $db = Mojo::Pg::Database->new(pg => $pg, dbh => $dbh);
$db->query('SELECT * FROM foo') ->hashes->map(sub { $_->{bar} })->join("\n")->say;

no cap, that's the entire energy. query goes in, results come out, you .say them. iconic. 🎀

---

🦋 DESCRIPTION 🦋

Mojo::Pg::Database is a container for DBD::Pg database handles, and it's the one Mojo::Pg reaches for. think of it as the lil home where your DBD::Pg handles live rent-free 🏠💜

---

💫 EVENTS 💫

Mojo::Pg::Database inherits all the events from Mojo::EventEmitter, plus it can emit these new ones down here. 👇

💌 close

$db->on(close => sub ($db) {
  ...
});

this one fires when the database connection gets closed while it's still waiting on notifications. lowkey sad moment but okay 😭

🔔 notification

$db->on(notification => sub ($db, $name, $pid, $payload) {
  ...
});

emitted when a notification has been received. yes bestie, you get $name, $pid, AND $payload — the full trio 🔥

---

👑 ATTRIBUTES 👑

Mojo::Pg::Database implements these attributes. all of them. don't skip any, i'm watching 👀

🗄️ dbh

my $dbh = $db->dbh;
$db     = $db->dbh($dbh);

this is the DBD::Pg database handle used for all the queries. the whole squad runs through this one.

# Use DBI utility methods
my $quoted = $db->dbh->quote_identifier('foo.bar');

🐘 pg

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

the Mojo::Pg object that this database belongs to. ⚠️ heads up bestie: this attribute is weakened — important detail, don't forget it.

🎀 results_class

my $class = $db->results_class;
$db       = $db->results_class('MyApp::Results');

the class that "query" is gonna use — defaults to Mojo::Pg::Results. but here's the tea: this class needs to already be loaded before "query" gets called. no lazy-loading shenanigans here, fr fr.

---

💅 METHODS 💅

Mojo::Pg::Database inherits all the methods from Mojo::EventEmitter and implements these new ones. buckle up, there's a lot and they're all kinda slay.

🌀 begin

my $tx = $db->begin;

starts a transaction and hands you back a Mojo::Pg::Transaction object. and get this — it will automatically roll back the transaction unless "commit" in Mojo::Pg::Transaction has been called before it gets destroyed. so like, commit or it's gone, periodt.

# Insert rows in a transaction
eval {
  my $tx = $db->begin;
  $db->insert('frameworks', {name => 'Catalyst'});
  $db->insert('frameworks', {name => 'Mojolicious'});
  $tx->commit;
};
say $@ if $@;

🗑️ delete

my $results = $db->delete($table, \%where, \%options);

generates a "DELETE" statement using "abstract" in Mojo::Pg (which is usually an SQL::Abstract::Pg object) and then executes it with "query". you can also tack a callback on the end to do it non-blocking. ✨

$db->delete(some_table => sub ($db, $err, $results) {
  ...
});
Mojo::IOLoop->start unless Mojo::IOLoop->is_running;

use all the same argument variations you'd pass to the "delete" method of SQL::Abstract. all of them, bestie:

# "DELETE FROM some_table"
$db->delete('some_table');

# "DELETE FROM some_table WHERE foo = 'bar'"
$db->delete('some_table', {foo => 'bar'});

# "DELETE from some_table WHERE foo LIKE '%test%'"
$db->delete('some_table', {foo => {-like => '%test%'}});

# "DELETE FROM some_table WHERE foo = 'bar' RETURNING id"
$db->delete('some_table', {foo => 'bar'}, {returning => 'id'});

🌈 delete_p

my $promise = $db->delete_p($table, \%where, \%options);

same as "delete" but it does everything non-blocking and returns a Mojo::Promise instead of taking a callback. promises? we love promises. 💖

$db->delete_p('some_table')->then(sub ($results) {
  ...
})->catch(sub ($err) {
  ...
})->wait;

🔌 disconnect

$db->disconnect;

disconnects "dbh" and prevents it from getting reused. it's over, it's done, no take-backs. 💀

💲 dollar_only

$db = $db->dollar_only;

activates "pg_placeholder_dollaronly" for the next "query" call, and lets ? be used as an operator. sneaky but useful.

# Check for a key in a JSON document
$db->dollar_only->query('SELECT * FROM foo WHERE bar ? $1', 'baz')
  ->expand->hashes->map(sub { $_->{bar}{baz} })->join("\n")->say;

📥 insert

my $results = $db->insert($table, \@values || \%fieldvals, \%options);

generates an "INSERT" statement with "abstract" in Mojo::Pg (usually an SQL::Abstract::Pg object) and executes it with "query". and yeah, you can append a callback to go non-blocking too. 🫶

$db->insert(some_table => {foo => 'bar'} => sub ($db, $err, $results) {
  ...
});
Mojo::IOLoop->start unless Mojo::IOLoop->is_running;

use all the same argument variations you'd pass to the "insert" method of SQL::Abstract:

# "INSERT INTO some_table (foo, baz) VALUES ('bar', 'yada')"
$db->insert('some_table', {foo => 'bar', baz => 'yada'});

# "INSERT INTO some_table (foo) VALUES ({1,2,3})"
$db->insert('some_table', {foo => [1, 2, 3]});

# "INSERT INTO some_table (foo) VALUES ('bar') RETURNING id"
$db->insert('some_table', {foo => 'bar'}, {returning => 'id'});

# "INSERT INTO some_table (foo) VALUES ('bar') RETURNING id, foo"
$db->insert('some_table', {foo => 'bar'}, {returning => ['id', 'foo']});

plus some PostgreSQL specific extensions added by SQL::Abstract::Pg. spicy 🌶️

# "INSERT INTO some_table (foo) VALUES ('{"test":23}')"
$db->insert('some_table', {foo => {-json => {test => 23}}});

# "INSERT INTO some_table (foo) VALUES ('bar') ON CONFLICT DO NOTHING"
$db->insert('some_table', {foo => 'bar'}, {on_conflict => undef});

including the operations commonly referred to as "upsert". yk, the classic insert-but-maybe-update move:

# "INSERT INTO t (a) VALUES ('b') ON CONFLICT (a) DO UPDATE SET a = 'c'"
$db->insert('t', {a => 'b'}, {on_conflict => [a => {a => 'c'}]});

# "INSERT INTO t (a, b) VALUES ('c', 'd') ON CONFLICT (a, b) DO UPDATE SET a = 'e'"
$db->insert('t', {a => 'c', b => 'd'}, {on_conflict => [['a', 'b'] => {a => 'e'}]});

🌟 insert_p

my $promise = $db->insert_p($table, \@values || \%fieldvals, \%options);

same as "insert" but all non-blocking, returns a Mojo::Promise instead of taking a callback. 💜

$db->insert_p(some_table => {foo => 'bar'})->then(sub ($results) {
  ...
})->catch(sub ($err) {
  ...
})->wait;

👂 is_listening

my $bool = $db->is_listening;

checks if "dbh" is listening for notifications. simple. clean. we stan a boolean. ✅

🎧 listen

$db = $db->listen('foo');

subscribes to a channel and receives "notification" events whenever the Mojo::IOLoop event loop is running. so like, you're on the list now 💌

📣 notify

$db = $db->notify('foo');
$db = $db->notify(foo => 'bar');

notify a channel. that's it, that's the whole thing. go off 📢

🆔 pid

my $pid = $db->pid;

returns the process id of the backend server process. yes, the actual backend pid. very demure, very mindful.

🏓 ping

my $bool = $db->ping;

checks the database connection. is it alive? is it thriving? find out 💫

🔍 query

my $results = $db->query('SELECT * FROM foo');
my $results = $db->query('INSERT INTO foo VALUES (?, ?, ?)', @values);
my $results = $db->query('SELECT ?::JSON AS foo', {-json => {bar => 'baz'}});

executes a blocking SQL <http://www.postgresql.org/docs/current/static/sql.html> statement and returns a results object based on "results_class" (which is usually Mojo::Pg::Results) with the query results inside. the DBD::Pg statement handle gets automatically reused when it's not active anymore — that's the performance glow-up ✨. you can also append a callback to go non-blocking.

$db->query('INSERT INTO foo VALUES (?, ?, ?)' => @values => sub ($db, $err, $results) {
  ...
});
Mojo::IOLoop->start unless Mojo::IOLoop->is_running;

okay here's some important tea: hash reference arguments containing a value named "-json" or "json" get encoded to JSON text using "to_json" in Mojo::JSON. to do the reverse, use the method "expand" in Mojo::Pg::Results — it automatically decodes all fields of the types "json" and "jsonb" with "from_json" in Mojo::JSON into Perl values. cute, right? 🎀

# "I ♥ Mojolicious!"
$db->query('SELECT ?::JSONB AS foo', {-json => {bar => 'I ♥ Mojolicious!'}}) ->expand->hash->{foo}{bar};

and hash reference arguments containing values named "type" and "value" can be used to bind specific DBD::Pg data types to placeholders. fancy 💅

# Insert binary data
use DBD::Pg ':pg_types';
$db->query('INSERT INTO bar VALUES (?)', {type => PG_BYTEA, value => $bytes});

🌸 query_p

my $promise = $db->query_p('SELECT * FROM foo');

same as "query" but non-blocking everything, returns a Mojo::Promise instead of taking a callback. 🦋

$db->query_p('INSERT INTO foo VALUES (?, ?, ?)' => @values)->then(sub ($results) {
  ...
})->catch(sub ($err) {
  ...
})->wait;

🎯 select

my $results = $db->select($source, $fields, $where, \%options);

generates a "SELECT" statement with "abstract" in Mojo::Pg (usually an SQL::Abstract::Pg object) and executes it with "query". and yep, callback = non-blocking, same as always. 🫶

$db->select(some_table => ['foo'] => {bar => 'yada'} => sub ($db, $err, $results) {
  ...
});
Mojo::IOLoop->start unless Mojo::IOLoop->is_running;

use all the same argument variations you'd pass to the "select" method of SQL::Abstract:

# "SELECT * FROM some_table"
$db->select('some_table');

# "SELECT id, foo FROM some_table"
$db->select('some_table', ['id', 'foo']);

# "SELECT * FROM some_table WHERE foo = 'bar'"
$db->select('some_table', undef, {foo => 'bar'});

# "SELECT * FROM some_table WHERE foo LIKE '%test%'"
$db->select('some_table', undef, {foo => {-like => '%test%'}});

plus those PostgreSQL specific extensions from SQL::Abstract::Pg. the joins?? obsessed.

# "SELECT * FROM foo JOIN bar ON (bar.foo_id = foo.id)"
$db->select(['foo', ['bar', foo_id => 'id']]);

# "SELECT * FROM foo LEFT JOIN bar ON (bar.foo_id = foo.id)"
$db->select(['foo', [-left => 'bar', foo_id => 'id']]);

# "SELECT foo AS bar FROM some_table"
$db->select('some_table', [[foo => 'bar']]);

# "SELECT * FROM some_table WHERE foo = '[1,2,3]'"
$db->select('some_table', '*', {foo => {'=' => {-json => [1, 2, 3]}}});

# "SELECT EXTRACT(EPOCH FROM foo) AS foo, bar FROM some_table"
$db->select('some_table', [\'extract(epoch from foo) AS foo', 'bar']);

# "SELECT 'test' AS foo, bar FROM some_table"
$db->select('some_table', [\['? AS foo', 'test'], 'bar']);

including a new last argument so you can pass a whole bunch of new options. look at all these:

# "SELECT * FROM some_table WHERE foo = 'bar' ORDER BY id DESC"
$db->select('some_table', '*', {foo => 'bar'}, {order_by => {-desc => 'id'}});

# "SELECT * FROM some_table LIMIT 10 OFFSET 20"
$db->select('some_table', '*', undef, {limit => 10, offset => 20});

# "SELECT * FROM some_table WHERE foo = 23 GROUP BY foo, bar"
$db->select('some_table', '*', {foo => 23}, {group_by => ['foo', 'bar']});

# "SELECT * FROM t WHERE a = 'b' GROUP BY c HAVING d = 'e'"
$db->select('t', '*', {a => 'b'}, {group_by => ['c'], having => {d => 'e'}});

# "SELECT * FROM some_table WHERE id = 1 FOR UPDATE"
$db->select('some_table', '*', {id => 1}, {for => 'update'});

# "SELECT * FROM some_table WHERE id = 1 FOR UPDATE SKIP LOCKED"
$db->select('some_table', '*', {id => 1}, {for => \'update skip locked'});

---

okay bestie, that's it for part 1 — don't go anywhere, there's more coming 💖✨

✨ select_p ✨

my $promise = $db->select_p($source, $fields, $where, \%options);

Bestie, this is literally just select but it does all the operations non-blocking and hands you back a Mojo::Promise object instead of taking a callback. No callback, just vibes. 🦋

$db->select_p(some_table => ['foo'] => {bar => 'yada'})->then(sub ($results) {
  ...
})->catch(sub ($err) {
  ...
})->wait;

---

🌸 tables 🌸

my $tables = $db->tables;

Gives you the table and view names for this database as an array reference — the ones that are visible to the current user and not internal. It's giving "here's what you're actually allowed to see" 💅

# Names of all tables
say for @{$db->tables};

---

🎀 unlisten 🎀

$db = $db->unlisten('foo');
$db = $db->unlisten('*');

Unsubscribe from a channel. And if you're done with literally everything, the "*" wildcard unsubscribes you from all channels at once. Clean slate energy, periodt. ✨

---

🔥 update 🔥

my $results = $db->update($table, \%fieldvals, \%where, \%options);

Generates an "UPDATE" statement with "abstract" in Mojo::Pg (usually an SQL::Abstract::Pg object) and runs it with "query". You can also tack a callback on the end to do it all non-blocking. 👑

$db->update(some_table => {foo => 'baz'} => {foo => 'bar'} => sub ($db, $err, $results) {
  ...
});
Mojo::IOLoop->start unless Mojo::IOLoop->is_running;

Use every single argument variation you'd pass to the "update" method of SQL::Abstract. Same menu, same options, no substitutions. 🍓

# "UPDATE some_table SET foo = 'bar' WHERE id = 23"
$db->update('some_table', {foo => 'bar'}, {id => 23});

# "UPDATE some_table SET foo = {1,2,3} WHERE id = 23"
$db->update('some_table', {foo => [1, 2, 3]}, {id => 23});

# "UPDATE some_table SET foo = 'bar' WHERE foo LIKE '%test%'"
$db->update('some_table', {foo => 'bar'}, {foo => {-like => '%test%'}});

# "UPDATE some_table SET foo = 'bar' WHERE id = 23 RETURNING id"
$db->update('some_table', {foo => 'bar'}, {id => 23}, {returning => 'id'});

# "UPDATE some_table SET foo = '[1,2,3]' WHERE bar = 23"
$db->update('some_table', {foo => {-json => [1, 2, 3]}}, {bar => 23});

---

💖 update_p 💖

my $promise = $db->update_p($table, \%fieldvals, \%where, \%options);

Same as "update", but non-blocking everything, and you get a Mojo::Promise instead of passing a callback. Twin of select_p, equally iconic. 🌈

$db->update_p(some_table => {foo => 'baz'} => {foo => 'bar'})->then(sub ($results) {
  ...
})->catch(sub ($err) {
  ...
})->wait;

---

👀 SEE ALSO 👀

• Mojo::Pg
• Mojolicious::Guides
• <https://mojolicious.org> — yes bestie, go click it 💅

---

okay that's the whole page, you ate it 🫶 This one shipped as part of perl v5.42.0, dated 2026-01-17, and it's the Mojo::Pg::Database(3) era. Go build something cute with it, ily, bye!! 💖✨🎀🦋🌸