Abstract

NAME

Net::Postgres::Abstract - Abstractly and asynchronously querying your postgresql database

SYNOPSIS


use Net::Postgres::Abstract;

my $dbh = await Net::Postgres::Abstract.connect-tcp(:$host, :user, :$password);

await $dbh.transaction: {
	my $row = await $dbh.insert('table', { :user<leont>, :language<raku> }, :returning<id>);
	...
}

my $results = await $dbh.select('table', <user password country>, { :age('>' => 25) });
for $results.objects(User) -> $user {
	...
}

my $sth = await $dbh.select('table', *, { :id(delegate('id')) }, :prepare);
for @ids -> $id {
	my $row = await $sth->execute(:$id);
	...
}

Description

Net::Postgres::Abstract is at its core a combination of query builder SQL::Abstract and postgresql client Net::Postgres.

Rationale

Abstract

It abstracts the generation of SQL queries, so you don't have to write them yourself.

Asynchronous

All queries are performed asynchronously.

Prepared

Full support for prepared queries, including named substitution, is offered.

Constructors

connect-tcp(--> Promise)

This creates a promise to a new Net::Postgres::Abstract client. It takes the following named arguments:

Str :$host = 'localhost'
Int :$port = 5432
Str :$user = ~$*USER
Str :password
Str :$database
TypeMap :$typemap = Protocol::Postgres::TypeMap::JSON
Bool :$tls = False
:%tls-args = ()

connect-local(--> Promise)

This creates a new Net::Postgres::Abstract client much like connect-tcp, but does so via a unix domain socket.

IO(Str) :$path = '/var/run/postgresql/'.IO
Int :$port = 5432
Str :$user = ~$*USER
Str :password
Str :$database
TypeMap :$typemap = Protocol::Postgres::TypeMap::JSON

connect(--> Promise)

This takes the same arguments as connect-local and connect-tcp. It will call the former if the $host is localhost and the $path exists, otherwise it will call connect-tcp.

Querying methods

All these methods return a Promise. if a :$prepared argument is given to them the Promise will contain a Net::Postgres::Abstract::PreparedStatement object, otherwise it will be the usual Net::Postgres return type (usually a Net::Postgres::ResultSet if it returns rows, or a Str if it doesn't).

select


method select(Source(Any) $source, Column::List(Any) $columns = *, Conditions(Any) $where?, Common(Any) :$common,
Distinction(Any) :$distinct, GroupBy(Any) :$group-by, Conditions(Any) :$having, Window::Clauses(Any) :$windows,
Compound(Pair) :$compound, OrderBy(Any) :$order-by, Int :$limit, Int :$offset, Locking(Any) :$locking)

This will generate a SELECT query. It will select $columns from $source, filtering by $conditions.


my $join = { :left<books>, :right<authors>, :using<author_id> };
my $result = $abstract.select($join, ['books.name', 'authors.name'], { :cost('<' => 10) });
# SELECT books.name, authors.name FROM books INNER JOIN authors USING (author_id) WHERE cost < 10

my $counts = $abstract.select('artists',
    [ 'name', :number(:count(*)) ],
    { :name(like => 'A%') },
    :group-by<name>, :order-by(:number<desc>));
# SELECT name, COUNT(*) as number FROM artists WHERE name LIKE 'A%' GROUP BY name ORDER BY number DESC

update


method update(Table(Any) $target, Assigns(Any) $assigns, Conditions(Any) $where?,
Common(Any) :$common, Source(Any) :$from, Column::List(Any) :$returning)

This will update $target by assigning the columns and values from $set if they match $where, returning $returning.


$abtract.update('artists', { :name('The Artist (Formerly Known as Prince)') }, { :name<Prince> });
# UPDATE artists SET name = 'The Artist (Formerly Known as Prince)' WHERE name = 'Prince'

insert

Map insertion


method insert(Table(Any) $target, Assigns(Any) $values, Common(Any) :$common,
Overriding(Str) :$overriding, Conflicts(Any) :$conflicts, Column::List(Any) :$returning)

Inserts the values in $values into the table $target, returning the columns in $returning


$abstract.insert('artists', { :name<Metallica> }, :returning(*));
# INSERT INTO artists (name) VALUES ('Metallica') RETURNING *

List insertions


method insert(Table(Any) $target, Identifiers(Any) $columns, Rows(List) $rows, Common(Any) :$common,
Overriding(Str) :$overriding, Conflicts(Any) :$conflicts, Column::List(Any) :$returning)

Insert into $target, assigning each of the values in Rows to a new row in the table. This way one can insert a multitude of rows into a table.


$abstract.insert('artists', ['name'], [ ['Metallica'], ['Motörhead'] ], :returning(*));
# INSERT INTO artists (name) VALUES ('Metallica'), ('Motörhead') RETURNING *

$abstract.insert('artists', List, [ [ 'Metallica'], ], :returning<id>);
# INSERT INTO artists VALUES ('Metallica') RETURNING id

Select insertion


method insert(Table(Any) $target, Identifiers(Any) $columns, Select(Map) $select, Common(Any) :$common,
Overriding(Str) :$overriding, Conflicts(Any) :$conflicts, Column::List(Any) :$returning)

This selects from a (usually different) table, and inserts the values into the table.


$abstract.insert('artists', 'name', { :source<new_artists>, :columns<name> }, :returning(*));
# INSERT INTO artists (name) SELECT name FROM new_artists RETURNING *

delete


method delete(Table:D(Any:D) $target, Conditions(Any) $where, Common(Any) :$common,
Source(Any) :$using, Column::List(Any) :$returning)

This deletes rows from the database, optionally returning their values.


$abstract.delete('artists', { :name<Madonna> });
# DELETE FROM artists WHERE name = 'Madonna'

query


method query(Str $sql, @arguments?)
method query(SQL::Query $query)
method query(SQL::Abstract::Expression $query)

Perform a manual query to the database.

prepare


method prepare(Str $sql)
method prepare(Str $sql, @names)

This prepares a raw query. If given @names it will take named parameters on execute, without it will take positional ones.

transaction

To use a transaction, one can use the transaction(&code) method. It's code reference will act as a wrapper for the transaction. If anything throws an exception out of the callback (e.g. a failed query method), a rollback will be attempted.

Connection methods

listen(Str $channel-name --> Promise[Supply])
This listens to notifications on the given channel. It returns a Promise to a Supply of Notifications.
terminate(--> Nil)
This sends a message to the server to terminate the connection
disconnected(--> Promise)
This returns a Promise that will be be kept if the connection or broken to signal the connection is lost.
process-id(--> Int)
This returns the process id of the backend of this connection. This is useful for debugging purposes and for notifications.
get-parameter(Str $name --> Str)
This returns various parameters, currently known parameters are: server_version, server_encoding, client_encoding, application_name, default_transaction_read_only, in_hot_standby, is_superuser, session_authorization, DateStyle, IntervalStyle, TimeZone, integer_datetimes, and standard_conforming_strings.

PreparedStatement

This class is much like Net::

Exported functions

Under the :functions tag it exports two helper functions

delegate(Str $name, Any $default-value, Any:U :$type)
value(Any $value)

AUTHOR

Leon Timmermans <[email protected]>

COPYRIGHT AND LICENSE

This library is free software; you can redistribute it and/or modify it under the Artistic License 2.0.