= The Reactive PostgreSQL Client

Documentation:

- https://vertx.io/docs/vertx-pg-client/java/[Website documentation]

== Features

- Event driven
- Lightweight
- Built-in connection pooling
- Prepared queries caching
- Publish / subscribe using Postgres `LISTEN/NOTIFY`
- Batch and cursor support
- Row streaming
- `java.util.stream.Collector` row set transformation
- Command pipeling
- RxJava API
- Direct memory to object without unnecessary copies
- Java 8 Date and Time
- SSL/TLS
- Unix domain socket
- HTTP/1.x CONNECT, SOCKS4a or SOCKS5 proxy
- Request cancellation

== Supported Data Types

The *Reactive Postgres Client* currently supports the following data types

[cols="^,^,^,^,^", options="header"]
|====
| _
2+| Value
2+| Array

| Postgres | Java | Supported | JAVA | Supported

|`BOOLEAN`
|`j.l.Boolean`
|&#10004;
|`j.l.Boolean[]`
|&#10004;

|`INT2`
|`j.l.Short`
|&#10004;
|`j.l.Short[]`
|&#10004;

|`INT4`
|`j.l.Integer`
|&#10004;
|`j.l.Integer[]`
|&#10004;

|`INT8`
|`j.l.Long`
|&#10004;
|`j.l.Long[]`
|&#10004;

|`FLOAT4`
|`j.l.Float`
|&#10004;
|`j.l.Float[]`
|&#10004;

|`FLOAT8`
|`j.l.Double`
|&#10004;
|`j.l.Double[]`
|&#10004;

|`CHAR`
|`j.l.Character`
|&#10004;
|`j.l.Character[]`
|&#10004;

|`VARCHAR`
|`j.l.String`
|&#10004;
|`j.l.String[]`
|&#10004;

|`TEXT`
|`j.l.String`
|&#10004;
|`j.l.String[]`
|&#10004;

|`ENUM`
|`j.l.String`
|&#10004;
|`j.l.String[]`
|&#10004;

|`NAME`
|`j.l.String`
|&#10004;
|`j.l.String[]`
|&#10004;

|`SERIAL2`
|`j.l.Short`
|&#10004;
|`invalid type`
|&#10005;

|`SERIAL4`
|`j.l.Integer`
|&#10004;
|`invalid type`
|&#10005;

|`SERIAL8`
|`j.l.Long`
|&#10004;
|`invalid type`
|&#10005;

|`NUMERIC`
|`i.r.p.data.Numeric`
|&#10004;
|`i.r.p.data.Numeric[]`
|&#10004;

|`UUID`
|`j.u.UUID`
|&#10004;
|`j.u.UUID[]`
|&#10004;

|`DATE`
|`j.t.LocalDate`
|&#10004;
|`j.t.LocalDate[]`
|&#10004;

|`TIME`
|`j.t.LocalTime`
|&#10004;
|`j.t.LocalTime[]`
|&#10004;

|`TIMETZ`
|`j.t.OffsetTime`
|&#10004;
|`j.t.OffsetTime[]`
|&#10004;

|`TIMESTAMP`
|`j.t.LocalDateTime`
|&#10004;
|`j.t.LocalDateTime[]`
|&#10004;

|`TIMESTAMPTZ`
|`j.t.OffsetDateTime`
|&#10004;
|`j.t.OffsetDateTime[]`
|&#10004;

|`INTERVAL`
|`i.r.p.data.Interval`
|&#10004;
|`i.r.p.data.Interval[]`
|&#10004;

|`BYTEA`
|`i.v.c.b.Buffer`
|&#10004;
|`i.v.c.b.Buffer[]`
|&#10004;

|`JSON`
|`Object`
|&#10004;
|`Object[]`
|&#10004;

|`JSONB`
|`Object`
|&#10004;
|`Object[]`
|&#10004;

|`POINT`
|`i.r.p.data.Point`
|&#10004;
|`i.r.p.data.Point[]`
|&#10004;

|`LINE`
|`i.r.p.data.Line`
|&#10004;
|`i.r.p.data.Line[]`
|&#10004;

|`LSEG`
|`i.r.p.data.LineSegment`
|&#10004;
|`i.r.p.data.LineSegment[]`
|&#10004;

|`BOX`
|`i.r.p.data.Box`
|&#10004;
|`i.r.p.data.Box[]`
|&#10004;

|`INET`
|`io.vertx.pgclient.data.Inet`
|&#10004;
|`io.vertx.pgclient.data.Inet[]`
|&#10004;

|`MONEY`
|`io.vertx.pgclient.data.Money`
|&#10004;
|`io.vertx.pgclient.data.Money[]`
|&#10004;

|`PATH`
|`i.r.p.data.Path`
|&#10004;
|`i.r.p.data.Path[]`
|&#10004;

|`POLYGON`
|`i.r.p.data.Polygon`
|&#10004;
|`i.r.p.data.Polygon[]`
|&#10004;

|`CIRCLE`
|`i.r.p.data.Circle`
|&#10004;
|`i.r.p.data.Circle[]`
|&#10004;

|`TSVECTOR`
|`j.l.String`
|&#10004;
|`j.l.String[]`
|&#10004;

|`TSQUERY`
|`j.l.String`
|&#10004;
|`j.l.String[]`
|&#10004;

|`UNKNOWN`
|`j.l.String`
|&#10004;
|`j.l.String[]`
|&#10004;

|====

Note: PostgreSQL JSON and JSONB types are represented by the following Java types:

- `java.lang.String`
- `java.lang.Number`
- `java.lang.Boolean`
- `io.vertx.core.json.JsonObject`
- `io.vertx.core.json.JsonArray`
- `io.vertx.sqlclient.Tuple#JSON_NULL` for representing the JSON null literal

The following types

_MONEY_, _BIT_, _VARBIT_, _MACADDR_, _CIDR_, _MACADDR8_,
_XML_, _HSTORE_, _OID_,
_VOID_

are not implemented yet (PR are welcome).

== Developers

=== Testing

Out of the box, the test suite runs an embedded Postgres by default.

You can change the version of the embedded Postgres by passing a property `embedded.postgres.version` like this:

```
> mvn test -Dembedded.postgres.version=9.6
```

The following versions of embedded Postgres are supported:

- `9.6`
- `10.6` (default)
- `11.x` (Unix Domain Socket Test are ignored)

The embedded Postgres database binds to an arbitrary port by default to avoid conflicts.
Nevertheless, you can force the usage of the standard PostgreSQL port (5432) with a flag:

[source,bash]
----
mvn test -DcontainerFixedPort
----

=== Testing with an external database

You can run tests with an external database:

- the script `src/test/resources/create-postgres.sql` creates the test data
- the `TLSTest` expects the database to be configured with SSL with `src/test/resources/tls/server.key` / `src/test/resources/tls/server.cert``

You need to add some properties for testing:

```
> mvn test -Dconnection.uri=postgres://$username:$password@$host:$port/$database -Dtls.connection.uri=postgres://$username:$password@$host:$port/$database -Dunix.socket.directory=$path
```

- connection.uri(mandatory): configure the client to connect the specified database
- tls.connection.uri(mandatory): configure the client to run `TLSTest` with the specified Postgres with SSL enabled
- unix.socket.directory(optional): the single unix socket directory(multiple socket directories are not supported) to test Unix domain socket with a specified database, domain socket tests will be skipped if this property is not specified
(Note: Make sure you can access the unix domain socket with this directory under your host machine)
- unix.socket.port(optional): unix socket file is named `.s.PGSQL.nnnn` and `nnnn` is the server's port number,
this property is mostly used when you test with Docker, when you publish your Postgres container port other than 5432 in your host but Postgres may actually listen on a different port in the container,
you will then need this property to help you connect the Postgres with Unix domain socket

=== Testing with Docker

Run the Postgres containers with `docker-compose`:

```
> cd docker/postgres
> docker-compose up --build -V
```

Run tests:

```
> mvn test -Dconnection.uri=postgres://$username:$password@$host:$port/$database -Dtls.connection.uri=postgres://$username:$password@$host:$port/$database -Dunix.socket.directory=$path -Dunix.socket.port=$port
```
