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.
npm install @juit/pgproxy-persisterThe 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
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 aurl as a parameter, indicating the endpoint of the connection _and_
the specific client to be used.
The Persister interface (and the Models bound to it) is a _generic_Schema
interface. The type parameter can be used to provide a fully typedModel
view over the columns (and related 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 columnisNullable
* _(optional)_: if true the column is _nullable_ and henceforthnull
the 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:
`tsusers
/* Definition for all modelable columns /
export interface MySchema {
/* Columns for the table /id
users: {
/* Definition for the column in users /email
id: { type: number, hasDefault: true } // not nullable, but has default
/* Definition for the column in users /age
email: { type: string } // not nullable, no default, required creating
/* Definition for the 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 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 Persisterconnection
or 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 likeINSERT INTO ... ON CONFLICT (...) DO UPDATE ... RETURNING *.
The keys object passed as a first argument indicates the columns (and valuesdata
to set) for which conflicts are to be detected, while 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 nameASC
and (optionally) the keywords 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 theSELECT
generated 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 createUPDATE ... WHERE ... SET ... RETURNING * statements.
* the query parameter will work as in read, generating WHERE ...patch
statement parts.
* the parameter will work similarly to create, generatingSET ...=...
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 createDELETE FROM ... WHERE ... RETURNING * statements.
* the query parameter will work as in read, generating WHERE ...patch
statement parts.
* the parameter will work similarly to create, generatingSET ...=...
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 *
The ping() method on Persister is a simple shortcut tovoid query('SELECT now()')` and can be used to ping the database (for health
checks, connectivity checks, keepalives, ...).