Mojo::Pg::Database(3pm)

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

NAME

6       Mojo::Pg::Database - Database
7

SYNOPSIS

9         use Mojo::Pg::Database;
10
11         my $db = Mojo::Pg::Database->new(pg => $pg, dbh => $dbh);
12         $db->query('select * from foo')
13           ->hashes->map(sub { $_->{bar} })->join("\n")->say;
14

DESCRIPTION

16       Mojo::Pg::Database is a container for DBD::Pg database handles used by
17       Mojo::Pg.
18

EVENTS

20       Mojo::Pg::Database inherits all events from Mojo::EventEmitter and can
21       emit the following new ones.
22
23   close
24         $db->on(close => sub {
25           my $db = shift;
26           ...
27         });
28
29       Emitted when the database connection gets closed while waiting for
30       notifications.
31
32   notification
33         $db->on(notification => sub {
34           my ($db, $name, $pid, $payload) = @_;
35           ...
36         });
37
38       Emitted when a notification has been received.
39

ATTRIBUTES

41       Mojo::Pg::Database implements the following attributes.
42
43   dbh
44         my $dbh = $db->dbh;
45         $db     = $db->dbh($dbh);
46
47       DBD::Pg database handle used for all queries.
48
49         # Use DBI utility methods
50         my $quoted = $db->dbh->quote_identifier('foo.bar');
51
52   pg
53         my $pg = $db->pg;
54         $db    = $db->pg(Mojo::Pg->new);
55
56       Mojo::Pg object this database belongs to.
57
58   results_class
59         my $class = $db->results_class;
60         $db       = $db->results_class('MyApp::Results');
61
62       Class to be used by "query", defaults to Mojo::Pg::Results. Note that
63       this class needs to have already been loaded before "query" is called.
64

METHODS

66       Mojo::Pg::Database inherits all methods from Mojo::EventEmitter and
67       implements the following new ones.
68
69   begin
70         my $tx = $db->begin;
71
72       Begin transaction and return Mojo::Pg::Transaction object, which will
73       automatically roll back the transaction unless "commit" in
74       Mojo::Pg::Transaction has been called before it is destroyed.
75
76         # Insert rows in a transaction
77         eval {
78           my $tx = $db->begin;
79           $db->insert('frameworks', {name => 'Catalyst'});
80           $db->insert('frameworks', {name => 'Mojolicious'});
81           $tx->commit;
82         };
83         say $@ if $@;
84
85   delete
86         my $results = $db->delete($table, \%where, \%options);
87
88       Generate a "DELETE" statement with "abstract" in Mojo::Pg (usually an
89       SQL::Abstract::Pg object) and execute it with "query". You can also
90       append a callback to perform operations non-blocking.
91
92         $db->delete(some_table => sub {
93           my ($db, $err, $results) = @_;
94           ...
95         });
96         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
97
98       Use all the same argument variations you would pass to the "delete"
99       method of SQL::Abstract.
100
101         # "delete from some_table"
102         $db->delete('some_table');
103
104         # "delete from some_table where foo = 'bar'"
105         $db->delete('some_table', {foo => 'bar'});
106
107         # "delete from some_table where foo like '%test%'"
108         $db->delete('some_table', {foo => {-like => '%test%'}});
109
110         # "delete from some_table where foo = 'bar' returning id"
111         $db->delete('some_table', {foo => 'bar'}, {returning => 'id'});
112
113   delete_p
114         my $promise = $db->delete_p($table, \%where, \%options);
115
116       Same as "delete", but performs all operations non-blocking and returns
117       a Mojo::Promise object instead of accepting a callback.
118
119         $db->delete_p('some_table')->then(sub {
120           my $results = shift;
121           ...
122         })->catch(sub {
123           my $err = shift;
124           ...
125         })->wait;
126
127   disconnect
128         $db->disconnect;
129
130       Disconnect "dbh" and prevent it from getting reused.
131
132   dollar_only
133         $db = $db->dollar_only;
134
135       Activate "pg_placeholder_dollaronly" for next "query" call and allow
136       "?"  to be used as an operator.
137
138         # Check for a key in a JSON document
139         $db->dollar_only->query('select * from foo where bar ? $1', 'baz')
140           ->expand->hashes->map(sub { $_->{bar}{baz} })->join("\n")->say;
141
142   insert
143         my $results = $db->insert($table, \@values || \%fieldvals, \%options);
144
145       Generate an "INSERT" statement with "abstract" in Mojo::Pg (usually an
146       SQL::Abstract::Pg object) and execute it with "query". You can also
147       append a callback to perform operations non-blocking.
148
149         $db->insert(some_table => {foo => 'bar'} => sub {
150           my ($db, $err, $results) = @_;
151           ...
152         });
153         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
154
155       Use all the same argument variations you would pass to the "insert"
156       method of SQL::Abstract.
157
158         # "insert into some_table (foo, baz) values ('bar', 'yada')"
159         $db->insert('some_table', {foo => 'bar', baz => 'yada'});
160
161         # "insert into some_table (foo) values ({1,2,3})"
162         $db->insert('some_table', {foo => [1, 2, 3]});
163
164         # "insert into some_table (foo) values ('bar') returning id"
165         $db->insert('some_table', {foo => 'bar'}, {returning => 'id'});
166
167         # "insert into some_table (foo) values ('bar') returning id, foo"
168         $db->insert('some_table', {foo => 'bar'}, {returning => ['id', 'foo']});
169
170       As well as some PostgreSQL specific extensions added by
171       SQL::Abstract::Pg.
172
173         # "insert into some_table (foo) values ('bar') on conflict do nothing"
174         $db->insert('some_table', {foo => 'bar'}, {on_conflict => undef});
175
176       Including operations commonly referred to as "upsert".
177
178         # "insert into t (a) values ('b') on conflict (a) do update set a = 'c'"
179         $db->insert('t', {a => 'b'}, {on_conflict => [a => {a => 'c'}]});
180
181         # "insert into t (a, b) values ('c', 'd')
182         #  on conflict (a, b) do update set a = 'e'"
183         $db->insert(
184           't', {a => 'c', b => 'd'}, {on_conflict => [['a', 'b'] => {a => 'e'}]});
185
186   insert_p
187         my $promise = $db->insert_p($table, \@values || \%fieldvals, \%options);
188
189       Same as "insert", but performs all operations non-blocking and returns
190       a Mojo::Promise object instead of accepting a callback.
191
192         $db->insert_p(some_table => {foo => 'bar'})->then(sub {
193           my $results = shift;
194           ...
195         })->catch(sub {
196           my $err = shift;
197           ...
198         })->wait;
199
200   is_listening
201         my $bool = $db->is_listening;
202
203       Check if "dbh" is listening for notifications.
204
205   listen
206         $db = $db->listen('foo');
207
208       Subscribe to a channel and receive "notification" events when the
209       Mojo::IOLoop event loop is running.
210
211   notify
212         $db = $db->notify('foo');
213         $db = $db->notify(foo => 'bar');
214
215       Notify a channel.
216
217   pid
218         my $pid = $db->pid;
219
220       Return the process id of the backend server process.
221
222   ping
223         my $bool = $db->ping;
224
225       Check database connection.
226
227   query
228         my $results = $db->query('select * from foo');
229         my $results = $db->query('insert into foo values (?, ?, ?)', @values);
230         my $results = $db->query('select ?::json as foo', {json => {bar => 'baz'}});
231
232       Execute a blocking SQL
233       <http://www.postgresql.org/docs/current/static/sql.html> statement and
234       return a results object based on "results_class" (which is usually
235       Mojo::Pg::Results) with the query results. The DBD::Pg statement handle
236       will be automatically reused when it is not active anymore, to increase
237       the performance of future queries. You can also append a callback to
238       perform operations non-blocking.
239
240         $db->query('insert into foo values (?, ?, ?)' => @values => sub {
241           my ($db, $err, $results) = @_;
242           ...
243         });
244         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
245
246       Hash reference arguments containing a value named "json", will be
247       encoded to JSON text with "to_json" in Mojo::JSON. To accomplish the
248       reverse, you can use the method "expand" in Mojo::Pg::Results, which
249       automatically decodes all fields of the types "json" and "jsonb" with
250       "from_json" in Mojo::JSON to Perl values.
251
252         # "I ♥ Mojolicious!"
253         $db->query('select ?::jsonb as foo', {json => {bar => 'I ♥ Mojolicious!'}})
254           ->expand->hash->{foo}{bar};
255
256       Hash reference arguments containing values named "type" and "value",
257       can be used to bind specific DBD::Pg data types to placeholders.
258
259         # Insert binary data
260         use DBD::Pg ':pg_types';
261         $db->query('insert into bar values (?)', {type => PG_BYTEA, value => $bytes});
262
263   query_p
264         my $promise = $db->query_p('select * from foo');
265
266       Same as "query", but performs all operations non-blocking and returns a
267       Mojo::Promise object instead of accepting a callback.
268
269         $db->query_p('insert into foo values (?, ?, ?)' => @values)->then(sub {
270           my $results = shift;
271           ...
272         })->catch(sub {
273           my $err = shift;
274           ...
275         })->wait;
276
277   select
278         my $results = $db->select($source, $fields, $where, \%options);
279
280       Generate a "SELECT" statement with "abstract" in Mojo::Pg (usually an
281       SQL::Abstract::Pg object) and execute it with "query". You can also
282       append a callback to perform operations non-blocking.
283
284         $db->select(some_table => ['foo'] => {bar => 'yada'} => sub {
285           my ($db, $err, $results) = @_;
286           ...
287         });
288         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
289
290       Use all the same argument variations you would pass to the "select"
291       method of SQL::Abstract.
292
293         # "select * from some_table"
294         $db->select('some_table');
295
296         # "select id, foo from some_table"
297         $db->select('some_table', ['id', 'foo']);
298
299         # "select * from some_table where foo = 'bar'"
300         $db->select('some_table', undef, {foo => 'bar'});
301
302         # "select * from some_table where foo like '%test%'"
303         $db->select('some_table', undef, {foo => {-like => '%test%'}});
304
305       As well as some PostgreSQL specific extensions added by
306       SQL::Abstract::Pg.
307
308         # "select * from foo join bar on (bar.foo_id = foo.id)"
309         $db->select(['foo', ['bar', foo_id => 'id']]);
310
311         # "select * from foo left join bar on (bar.foo_id = foo.id)"
312         $db->select(['foo', [-left => 'bar', foo_id => 'id']]);
313
314         # "select foo as bar from some_table"
315         $db->select('some_table', [[foo => 'bar']]);
316
317         # "select * from some_table where foo = '[1,2,3]'"
318         $db->select('some_table', '*', {foo => {'=' => {-json => [1, 2, 3]}}});
319
320         # "select extract(epoch from foo) as foo, bar from some_table"
321         $db->select('some_table', [\'extract(epoch from foo) as foo', 'bar']);
322
323         # "select 'test' as foo, bar from some_table"
324         $db->select('some_table', [\['? as foo', 'test'], 'bar']);
325
326       Including a new last argument to pass many new options.
327
328         # "select * from some_table where foo = 'bar' order by id desc"
329         $db->select('some_table', '*', {foo => 'bar'}, {order_by => {-desc => 'id'}});
330
331         # "select * from some_table limit 10 offset 20"
332         $db->select('some_table', '*', undef, {limit => 10, offset => 20});
333
334         # "select * from some_table where foo = 23 group by foo, bar"
335         $db->select('some_table', '*', {foo => 23}, {group_by => ['foo', 'bar']});
336
337         # "select * from t where a = 'b' group by c having d = 'e'"
338         $db->select('t', '*', {a => 'b'}, {group_by => ['c'], having => {d => 'e'}});
339
340         # "select * from some_table where id = 1 for update"
341         $db->select('some_table', '*', {id => 1}, {for => 'update'});
342
343         # "select * from some_table where id = 1 for update skip locked"
344         $db->select('some_table', '*', {id => 1}, {for => \'update skip locked'});
345
346   select_p
347         my $promise = $db->select_p($source, $fields, $where, \%options);
348
349       Same as "select", but performs all operations non-blocking and returns
350       a Mojo::Promise object instead of accepting a callback.
351
352         $db->select_p(some_table => ['foo'] => {bar => 'yada'})->then(sub {
353           my $results = shift;
354           ...
355         })->catch(sub {
356           my $err = shift;
357           ...
358         })->wait;
359
360   tables
361         my $tables = $db->tables;
362
363       Return table and view names for this database, that are visible to the
364       current user and not internal, as an array reference.
365
366         # Names of all tables
367         say for @{$db->tables};
368
369   unlisten
370         $db = $db->unlisten('foo');
371         $db = $db->unlisten('*');
372
373       Unsubscribe from a channel, "*" can be used to unsubscribe from all
374       channels.
375
376   update
377         my $results = $db->update($table, \%fieldvals, \%where, \%options);
378
379       Generate an "UPDATE" statement with "abstract" in Mojo::Pg (usually an
380       SQL::Abstract::Pg object) and execute it with "query". You can also
381       append a callback to perform operations non-blocking.
382
383         $db->update(some_table => {foo => 'baz'} => {foo => 'bar'} => sub {
384           my ($db, $err, $results) = @_;
385           ...
386         });
387         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
388
389       Use all the same argument variations you would pass to the "update"
390       method of SQL::Abstract.
391
392         # "update some_table set foo = 'bar' where id = 23"
393         $db->update('some_table', {foo => 'bar'}, {id => 23});
394
395         # "update some_table set foo = {1,2,3} where id = 23"
396         $db->update('some_table', {foo => [1, 2, 3]}, {id => 23});
397
398         # "update some_table set foo = 'bar' where foo like '%test%'"
399         $db->update('some_table', {foo => 'bar'}, {foo => {-like => '%test%'}});
400
401         # "update some_table set foo = 'bar' where id = 23 returning id"
402         $db->update('some_table', {foo => 'bar'}, {id => 23}, {returning => 'id'});
403
404         # "update some_table set foo = '[1,2,3]' where bar = 23"
405         $db->update('some_table', {foo => {-json => [1, 2, 3]}}, {bar => 23});
406
407   update_p
408         my $promise = $db->update_p($table, \%fieldvals, \%where, \%options);
409
410       Same as "update", but performs all operations non-blocking and returns
411       a Mojo::Promise object instead of accepting a callback.
412
413         $db->update_p(some_table => {foo => 'baz'} => {foo => 'bar'})->then(sub {
414           my $results = shift;
415           ...
416         })->catch(sub {
417           my $err = shift;
418           ...
419         })->wait;
420

NAME

SYNOPSIS

DESCRIPTION

EVENTS

ATTRIBUTES

METHODS

SEE ALSO