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 {
58           my ($db, $err, $results) = @_;
59           die $err if $err;
60           say $_->{name} for $results->hashes->each;
61         });
62         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
63
64         # Concurrent non-blocking queries (synchronized with promises)
65         my $now   = $pg->db->query_p('select now() as now');
66         my $names = $pg->db->query_p('select * from names');
67         Mojo::Promise->all($now, $names)->then(sub {
68           my ($now, $names) = @_;
69           say $now->[0]->hash->{now};
70           say $_->{name} for $names->[0]->hashes->each;
71         })->catch(sub {
72           my $err = shift;
73           warn "Something went wrong: $err";
74         })->wait;
75
76         # Send and receive notifications non-blocking
77         $pg->pubsub->listen(foo => sub {
78           my ($pubsub, $payload) = @_;
79           say "foo: $payload";
80           $pubsub->notify(bar => $payload);
81         });
82         $pg->pubsub->listen(bar => sub {
83           my ($pubsub, $payload) = @_;
84           say "bar: $payload";
85         });
86         $pg->pubsub->notify(foo => 'PostgreSQL rocks!');
87         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
88

DESCRIPTION

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

BASICS

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

GROWING

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

EXAMPLES

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

EVENTS

183       Mojo::Pg inherits all events from Mojo::EventEmitter and can emit the
184       following new ones.
185
186   connection
187         $pg->on(connection => sub {
188           my ($pg, $dbh) = @_;
189           ...
190         });
191
192       Emitted when a new database connection has been established.
193
194         $pg->on(connection => sub {
195           my ($pg, $dbh) = @_;
196           $dbh->do('set search_path to my_schema');
197         });
198

ATTRIBUTES

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

METHODS

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

DEBUGGING

370       You can set the "DBI_TRACE" environment variable to get some advanced
371       diagnostics information printed by DBI.
372
373         DBI_TRACE=1
374         DBI_TRACE=15
375         DBI_TRACE=SQL
376

REFERENCE

378       This is the class hierarchy of the Mojo::Pg distribution.
379
380       · Mojo::Pg
381
382       · Mojo::Pg::Database
383
384       · Mojo::Pg::Migrations
385
386       · Mojo::Pg::PubSub
387
388       · Mojo::Pg::Results
389
390       · Mojo::Pg::Transaction
391
392       · SQL::Abstract::Pg
393

AUTHOR

395       Sebastian Riedel, "sri@cpan.org".
396

CREDITS

398       In alphabetical order:
399
400         Christopher Eveland
401
402         Dan Book
403
404         Flavio Poletti
405
406         Hernan Lopes
407
408         Joel Berger
409
410         Matt S Trout
411
412         Peter Rabbitson
413
414         William Lindley
415

COPYRIGHT AND LICENSE

417       Copyright (C) 2014-2020, Sebastian Riedel and others.
418
419       This program is free software, you can redistribute it and/or modify it
420       under the terms of the Artistic License version 2.0.
421