Mojo::Pg(3pm)

1Mojo::Pg(3)           User Contributed Perl Documentation          Mojo::Pg(3)
2
3
4

NAME

6       Mojo::Pg - Mojolicious ♥ PostgreSQL
7

SYNOPSIS

9         use Mojo::Pg;
10
11         # Use a PostgreSQL connection string for configuration
12         my $pg = Mojo::Pg->new('postgresql://postgres@/test');
13
14         # Select the server version
15         say $pg->db->query('SELECT VERSION() AS version')->hash->{version};
16
17         # Use migrations to create a table
18         $pg->migrations->name('my_names_app')->from_string(<<EOF)->migrate;
19         -- 1 up
20         CREATE TABLE names (id SERIAL PRIMARY KEY, name TEXT);
21         -- 1 down
22         DROP TABLE names;
23         EOF
24
25         # Use migrations to drop and recreate the table
26         $pg->migrations->migrate(0)->migrate;
27
28         # Get a database handle from the cache for multiple queries
29         my $db = $pg->db;
30
31         # Use SQL::Abstract to generate simple CRUD queries for you
32         $db->insert('names', {name => 'Isabell'});
33         my $id = $db->select('names', ['id'], {name => 'Isabell'})->hash->{id};
34         $db->update('names', {name => 'Belle'}, {id => $id});
35         $db->delete('names', {name => 'Belle'});
36
37         # Insert a few rows in a transaction with SQL and placeholders
38         eval {
39           my $tx = $db->begin;
40           $db->query('INSERT INTO names (name) VALUES (?)', 'Sara');
41           $db->query('INSERT INTO names (name) VALUES (?)', 'Stefan');
42           $tx->commit;
43         };
44         say $@ if $@;
45
46         # Insert another row with SQL::Abstract and return the generated id
47         say $db->insert('names', {name => 'Daniel'}, {returning => 'id'})->hash->{id};
48
49         # JSON roundtrip
50         say $db->query('SELECT ?::JSON AS foo', {json => {bar => 'baz'}})
51           ->expand->hash->{foo}{bar};
52
53         # Select all rows blocking with SQL::Abstract
54         say $_->{name} for $db->select('names')->hashes->each;
55
56         # Select all rows non-blocking with SQL::Abstract
57         $db->select('names' => sub ($db, $err, $results) {
58           die $err if $err;
59           say $_->{name} for $results->hashes->each;
60         });
61         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
62
63         # Concurrent non-blocking queries (synchronized with promises)
64         my $now   = $pg->db->query_p('SELECT NOW() AS now');
65         my $names = $pg->db->query_p('SELECT * FROM names');
66         Mojo::Promise->all($now, $names)->then(sub ($now, $names) {
67           say $now->[0]->hash->{now};
68           say $_->{name} for $names->[0]->hashes->each;
69         })->catch(sub ($err) {
70           warn "Something went wrong: $err";
71         })->wait;
72
73         # Send and receive notifications non-blocking
74         $pg->pubsub->listen(foo => sub ($pubsub, $payload) {
75           say "foo: $payload";
76           $pubsub->notify(bar => $payload);
77         });
78         $pg->pubsub->listen(bar => sub ($pubsub, $payload) {
79           say "bar: $payload";
80         });
81         $pg->pubsub->notify(foo => 'PostgreSQL rocks!');
82         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
83

DESCRIPTION

85       Mojo::Pg is a tiny wrapper around DBD::Pg that makes PostgreSQL
86       <http://www.postgresql.org> a lot of fun to use with the Mojolicious
87       <https://mojolicious.org> real-time web framework. Perform queries
88       blocking and non-blocking, use all SQL features
89       <https://www.postgresql.org/docs/current/static/sql.html> PostgreSQL
90       has to offer, generate CRUD queries from data structures, manage your
91       database schema with migrations and build scalable real-time web
92       applications with the publish/subscribe pattern.
93

BASICS

95       Database and statement handles are cached automatically, and will be
96       reused transparently to increase performance. You can handle connection
97       timeouts gracefully by holding on to them only for short amounts of
98       time.
99
100         use Mojolicious::Lite -signatures;
101         use Mojo::Pg;
102
103         helper pg => sub { state $pg = Mojo::Pg->new('postgresql://postgres@/test') };
104
105         get '/' => sub ($c) {
106           my $db = $c->pg->db;
107           $c->render(json => $db->query('SELECT NOW() AS now')->hash);
108         };
109
110         app->start;
111
112       In this example application, we create a "pg" helper to store a
113       Mojo::Pg object. Our action calls that helper and uses the method "db"
114       in Mojo::Pg to dequeue a Mojo::Pg::Database object from the connection
115       pool. Then we use the method "query" in Mojo::Pg::Database to execute
116       an SQL <http://www.postgresql.org/docs/current/static/sql.html>
117       statement, which returns a Mojo::Pg::Results object. And finally we
118       call the method "hash" in Mojo::Pg::Results to retrieve the first row
119       as a hash reference.
120
121       While all I/O operations are performed blocking, you can wait for long
122       running queries asynchronously, allowing the Mojo::IOLoop event loop to
123       perform other tasks in the meantime. Since database connections usually
124       have a very low latency, this often results in very good performance.
125
126       Every database connection can only handle one active query at a time,
127       this includes asynchronous ones. To perform multiple queries
128       concurrently, you have to use multiple connections.
129
130         # Performed concurrently (5 seconds)
131         $pg->db->query('SELECT PG_SLEEP(5)' => sub ($db, $err, $results) {...});
132         $pg->db->query('SELECT PG_SLEEP(5)' => sub ($db, $err, $results) {...});
133
134       All cached database handles will be reset automatically if a new
135       process has been forked, this allows multiple processes to share the
136       same Mojo::Pg object safely.
137

GROWING

139       And as your application grows, you can move queries into model classes.
140
141         package MyApp::Model::Time;
142         use Mojo::Base -base, -signatures;
143
144         has 'pg';
145
146         sub now ($self) {
147           return $self->pg->db->query('SELECT NOW() AS now')->hash;
148         }
149
150         1;
151
152       Which get integrated into your application with helpers.
153
154         use Mojolicious::Lite -signatures;
155         use Mojo::Pg;
156         use MyApp::Model::Time;
157
158         helper pg   => sub { state $pg   = Mojo::Pg->new('postgresql://postgres@/test') };
159         helper time => sub { state $time = MyApp::Model::Time->new(pg => shift->pg) };
160
161         get '/' => sub ($c) {
162           $c->render(json => $c->time->now);
163         };
164
165         app->start;
166

EXAMPLES

168       This distribution also contains two great example applications
169       <https://github.com/mojolicious/mojo-pg/tree/main/examples/> you can
170       use for inspiration. The minimal chat
171       <https://github.com/mojolicious/mojo-pg/tree/main/examples/chat.pl>
172       application will show you how to scale WebSockets to multiple servers,
173       and the well-structured blog <https://github.com/mojolicious/mojo-
174       pg/tree/main/examples/blog> application how to apply the MVC design
175       pattern in practice.
176

EVENTS

178       Mojo::Pg inherits all events from Mojo::EventEmitter and can emit the
179       following new ones.
180
181   connection
182         $pg->on(connection => sub ($pg, $dbh) {
183           ...
184         });
185
186       Emitted when a new database connection has been established.
187
188         $pg->on(connection => sub ($pg, $dbh) {
189           $dbh->do('SET search_path TO my_schema');
190         });
191

ATTRIBUTES

193       Mojo::Pg implements the following attributes.
194
195   abstract
196         my $abstract = $pg->abstract;
197         $pg          = $pg->abstract(SQL::Abstract::Pg->new);
198
199       SQL::Abstract::Pg object used to generate CRUD queries for
200       Mojo::Pg::Database, defaults to enabling "array_datatypes" and setting
201       "name_sep" to "." and "quote_char" to """.
202
203         # Generate WHERE clause and bind values
204         my($stmt, @bind) = $pg->abstract->where({foo => 'bar', baz => 'yada'});
205
206   auto_migrate
207         my $bool = $pg->auto_migrate;
208         $pg      = $pg->auto_migrate($bool);
209
210       Automatically migrate to the latest database schema with "migrations",
211       as soon as "db" has been called for the first time.
212
213   database_class
214         my $class = $pg->database_class;
215         $pg       = $pg->database_class('MyApp::Database');
216
217       Class to be used by "db", defaults to Mojo::Pg::Database. Note that
218       this class needs to have already been loaded before "db" is called.
219
220   dsn
221         my $dsn = $pg->dsn;
222         $pg     = $pg->dsn('dbi:Pg:dbname=foo');
223
224       Data source name, defaults to "dbi:Pg:".
225
226   max_connections
227         my $max = $pg->max_connections;
228         $pg     = $pg->max_connections(3);
229
230       Maximum number of idle database handles to cache for future use,
231       defaults to 1.
232
233   migrations
234         my $migrations = $pg->migrations;
235         $pg            = $pg->migrations(Mojo::Pg::Migrations->new);
236
237       Mojo::Pg::Migrations object you can use to change your database schema
238       more easily.
239
240         # Load migrations from file and migrate to latest version
241         $pg->migrations->from_file('/home/sri/migrations.sql')->migrate;
242
243   options
244         my $options = $pg->options;
245         $pg         = $pg->options({AutoCommit => 1, RaiseError => 1});
246
247       Options for database handles, defaults to activating "AutoCommit",
248       "AutoInactiveDestroy" as well as "RaiseError" and deactivating
249       "PrintError" as well as "PrintWarn". Note that "AutoCommit" and
250       "RaiseError" are considered mandatory, so deactivating them would be
251       very dangerous.
252
253   parent
254         my $parent = $pg->parent;
255         $pg        = $pg->parent(Mojo::Pg->new);
256
257       Another Mojo::Pg object to use for connection management, instead of
258       establishing and caching our own database connections.
259
260   password
261         my $password = $pg->password;
262         $pg          = $pg->password('s3cret');
263
264       Database password, defaults to an empty string.
265
266   pubsub
267         my $pubsub = $pg->pubsub;
268         $pg        = $pg->pubsub(Mojo::Pg::PubSub->new);
269
270       Mojo::Pg::PubSub object you can use to send and receive notifications
271       very efficiently, by sharing a single database connection with many
272       consumers.
273
274         # Subscribe to a channel
275         $pg->pubsub->listen(news => sub ($pubsub, $payload) {
276           say "Received: $payload";
277         });
278
279         # Notify a channel
280         $pg->pubsub->notify(news => 'PostgreSQL rocks!');
281
282   search_path
283         my $path = $pg->search_path;
284         $pg      = $pg->search_path(['$user', 'foo', 'public']);
285
286       Schema search path assigned to all new connections.
287
288         # Isolate tests and avoid race conditions when running them in parallel
289         my $pg = Mojo::Pg->new('postgresql:///test')->search_path(['test_one']);
290         $pg->db->query('DROP SCHEMA IF EXISTS test_one CASCADE');
291         $pg->db->query('CREATE SCHEMA test_one');
292         ...
293         $pg->db->query('DROP SCHEMA test_one CASCADE');
294
295   username
296         my $username = $pg->username;
297         $pg          = $pg->username('sri');
298
299       Database username, defaults to an empty string.
300

METHODS

302       Mojo::Pg inherits all methods from Mojo::EventEmitter and implements
303       the following new ones.
304
305   db
306         my $db = $pg->db;
307
308       Get a database object based on "database_class" (which is usually
309       Mojo::Pg::Database) for a cached or newly established database
310       connection. The DBD::Pg database handle will be automatically cached
311       again when that object is destroyed, so you can handle problems like
312       connection timeouts gracefully by holding on to it only for short
313       amounts of time.
314
315         # Add up all the money
316         say $pg->db->select('accounts')->hashes->reduce(sub { $a->{money} + $b->{money} });
317
318   from_string
319         $pg = $pg->from_string('postgresql://postgres@/test');
320         $pg = $pg->from_string(Mojo::Pg->new);
321
322       Parse configuration from connection string or use another Mojo::Pg
323       object as "parent".
324
325         # Just a database
326         $pg->from_string('postgresql:///db1');
327
328         # Just a service
329         $pg->from_string('postgresql://?service=foo');
330
331         # Username and database
332         $pg->from_string('postgresql://sri@/db2');
333
334         # Short scheme, username, password, host and database
335         $pg->from_string('postgres://sri:s3cret@localhost/db3');
336
337         # Username, domain socket and database
338         $pg->from_string('postgresql://sri@%2ftmp%2fpg.sock/db4');
339
340         # Username, database and additional options
341         $pg->from_string('postgresql://sri@/db5?PrintError=1&pg_server_prepare=0');
342
343         # Service and additional options
344         $pg->from_string('postgresql://?service=foo&PrintError=1&RaiseError=0');
345
346         # Username, database, an option and search_path
347         $pg->from_string('postgres://sri@/db6?&PrintError=1&search_path=test_schema');
348
349   new
350         my $pg = Mojo::Pg->new;
351         my $pg = Mojo::Pg->new('postgresql://postgres@/test');
352         my $pg = Mojo::Pg->new(Mojo::Pg->new);
353
354       Construct a new Mojo::Pg object and parse connection string with
355       "from_string" if necessary.
356
357         # Customize configuration further
358         my $pg = Mojo::Pg->new->dsn('dbi:Pg:service=foo');
359
360   reset
361         $pg = $pg->reset;
362
363       Reset connection cache.
364

DEBUGGING

366       You can set the "DBI_TRACE" environment variable to get some advanced
367       diagnostics information printed by DBI.
368
369         DBI_TRACE=1
370         DBI_TRACE=15
371         DBI_TRACE=SQL
372

API

374       This is the class hierarchy of the Mojo::Pg distribution.
375
376       • Mojo::Pg
377
378       • Mojo::Pg::Database
379
380       • Mojo::Pg::Migrations
381
382       • Mojo::Pg::PubSub
383
384       • Mojo::Pg::Results
385
386       • Mojo::Pg::Transaction
387

AUTHOR

389       Sebastian Riedel, "sri@cpan.org".
390

CREDITS

392       In alphabetical order:
393
394         Christopher Eveland
395
396         Dan Book
397
398         Flavio Poletti
399
400         Hernan Lopes
401
402         Joel Berger
403
404         Matt S Trout
405
406         Peter Rabbitson
407
408         William Lindley
409

COPYRIGHT AND LICENSE

411       Copyright (C) 2014-2022, Sebastian Riedel and others.
412
413       This program is free software, you can redistribute it and/or modify it
414       under the terms of the Artistic License version 2.0.
415