npm package: @juit/pgproxy-persister
Description: 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.
Author: Juit Developers
Latest version: 1.4.23

# 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](#connecting)
* [Schema Definition](#schema-defintion)
* [Model Views](#model-views)
  * [Create](#create)
  * [Upsert](#upsert)
  * [Read](#read)
  * [Find](#find)
  * [Update](#update)
  * [Delete](#delete)
* [Pinging the database](#pinging-the-database)
* [PGProxy](https://github.com/juitnow/juit-pgproxy/blob/main/README.md)
* [Copyright Notice](https://github.com/juitnow/juit-pgproxy/blob/main/NOTICE.md)
* [License](https://github.com/juitnow/juit-pgproxy/blob/main/NOTICE.md)



### Connecting

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.



### Schema Definition

The `Persister` interface (and the `Model`s 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 `Model`s) 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.



### Model views

Model views offer a very basic interface to **C**reate, **R**ead, **U**pdate
and **D**elete 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](#read), generating `WHERE ...`
  statement parts.
* the `patch` parameter will work similarly to [create](#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](#read), generating `WHERE ...`
  statement parts.
* the `patch` parameter will work similarly to [create](#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 *
```



### Pinging the database

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, ...).