PostgreSQL Proxy Client (Persister Interface)

The persister interface for PostgreSQL Proxy is a higher-level interface
offering (on top of the usual connection and query interface) a CRUD
abstraction over database tables and few utility methods.

* Connecting
* Schema Definition
* Model Views
* Create
* Upsert
* Read
* Find
* Update
* Delete
* Pinging the database
* PGProxy
* Copyright Notice
* License

$3

In the code, you can simply depend on the Persister class:

``ts
import { Persister } from '@juit/pgproxy-persister'

const client = new Persister()
`

As with the standard client (PGClient) persisters can be constructed with a
url as a parameter, indicating the endpoint of the connection _and_
the specific client to be used.

$3

The Persister interface (and the Models bound to it) is a _generic_
interface. The Schema type parameter can be used to provide a fully typed
view over the columns (and related Models) it manages.

Formally, the Schema is a type mapping table and column names to column
definitions. Each column definition is a type containing the following
properties:

* type: the _type_ of the column
* isNullable _(optional)_: if true the column is _nullable_ and henceforth
the null value can be used in lieu of the type above.
* hasDefault _(optional)_: if true the column _specifies a default value_
and therefore can be omitted in create operations.

An example of a Schema is as follows:

`ts
/* Definition for all modelable columns /
export interface MySchema {
/* Columns for the users table /
users: {
/* Definition for the id column in users /
id: { type: number, hasDefault: true } // not nullable, but has default
/* Definition for the email column in users /
email: { type: string } // not nullable, no default, required creating
/* Definition for the age column in users /
age: { type: number, isNullable: true, hasDefault: false }

// ... all other columns
},

// ... all other tables
}
`

The @juit/pgproxy-utils comes with a useful schema generator, querying a
database for all of its tables and generating a proper TypeScript interface.

$3

Model views offer a very basic interface to Create, Read, Update
and Delete data from a table.

A _CRUD_ model can be obtained by calling the in(tableName) on a Persister
or connection object, for example:

`ts
const model = persister.in('myTable')
model.create({ ... })
model.delete({ ... })

persister.connect(async (connection) => {
const model = connection.in('myTable')
await model.create({ ... })
await model.delete({ ... })
})
`

#### Create

The model's create(object) function will create INSERT INTO ... RETURNING *
statements based on the specified object.

Each key in the object will represent a _column name_ and its associated value
will be inserted in place.

This function will return (obviously) the values inserted, including any default
value calculated by the database.

`typescript
persisterOrConnection.in('myTable').create({ myString: 'foo', myNumber: 123 })
// INSERT INTO "myTable" ("myString", "myNumber") VALUES ('foo', 123) RETURNING *

persisterOrConnection.in('myTable').create({})
// INSERT INTO "myTable" DEFAULT VALUES RETURNING *
`

#### Upsert

The model's upsert(keys, data) function will create upsert statements like
INSERT INTO ... ON CONFLICT (...) DO UPDATE ... RETURNING *.

The keys object passed as a first argument indicates the columns (and values
to set) for which conflicts are to be detected, while data is an object
containing other columns to update.

This function will return the values inserted and/or updated.

`typescript
persisterOrConnection.in('myTable').upsert({
myKey: 'myValue', anotherKey: 'anotherValue'
}, {
myString: 'foo', myNumber: 123
})
// INSERT INTO "myTable" ("myKey", "anotherKey", "myString", "myNumber")
// VALUES ('myValue', 'anotherValue', 'foo', 123)
// ON CONFLICT ("myKey", "anotherKey") DO UPDATE
// SET "myString"='foo',
// "myNumber"=123
// RETURNING *
`

#### Read

The model's read(query, sort) function will create SELECT * FROM ...
statements based on the specified query and sort parameters.

Each key/value mapping in the query object will be mapped to a WHERE key=value
statement part.

The sort parameter must be an Array of string(s) containing the column name
and (optionally) the keywords ASC or DESC:

`ts
persisterOrConnection.in('myTable').read({ myString: 'foo', myNumber: 123 }, [
'mySortColumn',
'anotherSortColumn ASC',
'yetAnotherSortColumn DESC',
])
// SELECT * FROM "myTable" WHERE "myString"='foo' AND "myNumber"=123
// ORDER BY "mySortColumn", "anotherSortColumn" ASC, "yetAnotherSortColumn" DESC
`

#### Find

Similar to read(...) this method will return the _first_ result of the
generated SELECT query, or _undefined_ in case of no results:

`ts
persisterOrConnection.in('myTable').find({ myString: 'foo', myNumber: 123 }, [
'mySortColumn',
'anotherSortColumn ASC',
'yetAnotherSortColumn DESC',
])
// SELECT * FROM "myTable" WHERE "myString"='foo' AND "myNumber"=123
// ORDER BY "mySortColumn", "anotherSortColumn" ASC, "yetAnotherSortColumn" DESC
// LIMIT 1
`

#### Update

The model's update(query, patch) function will create
UPDATE ... WHERE ... SET ... RETURNING * statements.

* the query parameter will work as in read, generating WHERE ...
statement parts.
* the patch parameter will work similarly to create, generating
SET ...=... statement parts.

This function will _cowardly_ fail when the query parameter is an empty object
(by design, we don't allow modification of _all_ rows in a database).

This function will return an Array of all rows modified by this call.

`javascript
persisterOrConnection.in('myTable').update({ myString: 'foo'}, { myNumber: 123 })
// UPDATE "myTable" SET "myNumber=123 WHERE "myString"='foo' RETURNING *
`

#### Delete

The model's delete(query) function will create
DELETE FROM ... WHERE ... RETURNING * statements.

* the query parameter will work as in read, generating WHERE ...
statement parts.
* the patch parameter will work similarly to create, generating
SET ...=... statement parts.

This function will _cowardly_ fail when the query parameter is an empty object
(by design, we don't allow deletion of _all_ rows in a database).

This function will return the _number of rows_ deleted by the query.

`javascript
persisterOrConnection.in('myTable').delete({ myString: 'foo'})
// DELETE FROM "myTable" WHERE "myString"='foo' RETURNING *
`

$3

The ping() method on Persister is a simple shortcut to
void query('SELECT now()')` and can be used to ping the database (for health
checks, connectivity checks, keepalives, ...).