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/kraih/mojo-pg/tree/master/examples/> you can use
175       for inspiration. The minimal chat <https://github.com/kraih/mojo-
176       pg/tree/master/examples/chat.pl> application will show you how to scale
177       WebSockets to multiple servers, and the well-structured blog
178       <https://github.com/kraih/mojo-pg/tree/master/examples/blog>
179       application how to apply the MVC design pattern in practice.
180

EVENTS

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

ATTRIBUTES

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

METHODS

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

DEBUGGING

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

REFERENCE

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

AUTHOR

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

CREDITS

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

COPYRIGHT AND LICENSE

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