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 fawaka@gmail.com

COPYRIGHT AND LICENSE

Copyright 2023 Leon Timmermans

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