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

Connecting

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

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 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:

/** 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 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:

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.

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.

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:

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:

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.

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.

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