@graffy/pg
v0.16.20
Published
The standard Postgres module for Graffy. Each instance this module mounts a Postgres table as a Graffy subtree.
Downloads
1,855
Readme
Postgres
The standard Postgres module for Graffy. Each instance this module mounts a Postgres table as a Graffy subtree.
Requires the pg library to be installed as a peer dependency.
Usage
import pg from '@graffy/pg';
import Graffy from '@graffy/core';
import link from '@graffy/link';
const store = new Graffy();
store.use(path, pg(options));
Options
table
, the name of the table. If not provided, the last segment of thepath
is used. This table must exist.idCol
: the name of the column to use as ID. Defaults toid
. This column must exist and be the primary key or have a unique constraint.verCol
: the name of the column to store the Graffy version number. This column must exist, and must have aDEFAULT
SQL expression defined - this expression is evaluated to calculate the version number. Graffy versions must monotonically increase, so this expression is typically based onCURRENT_TIMESTAMP
.joins
: other tables that have foreign keys referencing the ID column in this table, where we want to filter this table using columns of that other table via a "join". The value is a map of join names to join options.- join names are the names used to refer to joined tables in filter expressions. Typically, these are the names of those tables.
- join options are objects with optional properties
table
,idCol
,refCol
andverCol
.refCol
is the name of the column in the join table that references this one.
connection
: a pg Client or Pool object (recommended), or the arguments for constructing a new Pool object. Optional.
Database connection
Graffy Postgres can be configured to use a specific pg.Client object on a per-request basis, by including a pgClient
property on the read or write options. This is useful for implementing transactions, partitioning, row-level security, etc.
If no pgClient
is provided for a particular operation, Graffy Postgres falls back to the "global" pg.Client or pg.Pool object defined in the connection
parameter in the initialization options. If no connection
parameter was passed, a new pg.Pool will be created using PSQL environment variables.
Data model
Graffy Postgres interprets each property as the name of a column, except for $count
, $sum
etc. as described in the aggregation section below.
It also interprets the $key
as specifying filtering, sorting, pagination and aggregation parameters.
Filters
Query filters are JSON-based, somewhat like MongoDB.
- Filters expressions follow a property, operator, value order. Values are scalar values (strings or numbers).
- Property names are always object keys. They may be strings with dots
.
. - Operators are placed in objects as well and have a leading
$
. - Values are JSON values.
- Multiple properties in an object are combined with
AND
. Items in an arrays are combined withOR
. - The supported operators are:
$eq
: optional in most cases.$lt
,$lte
,$gt
,$gte
: Ranges$re
,$ire
: Regex match (case sensitive and insensitive versions)$text
: Full text search, always case insensitive$not
: Modifies other filters or inverts a condition$and
,$or
: Combines conditions; optional in most cases$all
,$has
,$any
: Apply conditions to the elements of a collection (list or map)
Basic
{ foo: 5 }
and{ foo: { $eq: 5 } }
compile to SQLfoo = 5
.{ foo: { $gt: 5, $lt: 6 } }
becomesfoo > 5 AND foo < 6
.{ foo: { $ire: '^wor.*' } }
becomesfoo ~* "^wor.*"
.{ foo: { $text: 'potatoes' } }
becomesfoo @@ websearch_to_tsquery('potatoes')
. For this to work,foo
must be a TSVector column.
Or
{ foo: [5, 6] }
means foo equals 5 or foo equals 6, and the compiler is smart enough to simplify this tofoo IN (5, 6)
. There is no separate $in.{ foo: [ 5, { $gt: 6 } ] }
becomesfoo = 5 OR foo > 6
[ { foo: 6 }, { bar: 7 } ]
becomesfoo = 6 OR bar = 7
Not
{ foo: { $not: 6 } }
becomesfoo <> 6
(the SQL not equals operator){ foo: { $not: [5, 6] }
becomesfoo NOT IN (5, 6)
{ foo: { $not: [ 5, { $gt: 6 } ] } }
becomesNOT (foo = 5 OR foo > 6)
Logic
By default, objects mean
AND
and arrays meanOR
:[ { foo: 5, bar: 6 }, { baz: 7, qux: 4 } ]
becomes(foo = 5 AND bar = 6) OR (baz = 7 AND qux = 4)
Use
$and
and$or
operators explicitly to use any structure in either context:{ $and: [ { $or: { foo: 5, bar: 6 } }, { $or: { baz: 7, qux: 4 } } ] }
becomes(foo = 5 OR bar = 6) AND (baz = 7 OR qux = 4)
.
Contains and Contained by
{ tags: { $cts: ['foo', 'bar'] } }
becomestags @> '{"foo","bar"}'
. Tags must contain both foo and bar. Note that the array of conditions here does not haveOR
semantics.{ tags: { $ctd: ['foo', 'bar', 'baz'] } }
becomestags <@ '{"foo","bar","baz"}'
. Every tag must be one of foo, bar or baz.
Joins
joinName: { ...expression }
,
Notes
- We drop several MongoDB operators while retaining the capability:
$ne
: Use $not instead.$in
: Use an array of values; it is a combination of implicit $or and $eq.$nin
: Use $not and an array of values.$exists
: Usenull
or{ $not: null }
. Postgres does not distinguish betweenundefined
andnull
, and neither does Graffy (in this context at least; it's complicated.)
- Graffy
$has
is equivalent to MongoDB$all
(each of the provided conditions is met by at least one element in the array). Graffy$all
(every element of the array meets a condition) has no direct MongoDB equivalent, but can be expressed as{ $not: { $elemMatch: { $not: (cond) } } }
- Graffy has a separate operator for case-insensitive regex, and configures the text search locale in the database object rather than the query. This makes MongoDB's regex
$options
, full text$language
etc. unnecessary. - Graffy does not have equivalents for MongoDB operators
$type
,$expr
,$jsonSchema
,$where
,$mod
,$size
and the geospatial operators.
Order by
The root of the Graffy filter object must be an object. (Use $or
if required.) The property $order
specifies the order. Its value must be an array of order specifiers, each of which may be a string property name. The order is ascending by default; property names may be prefixed with !
to switch to descending.
Full Text Search
A full-text search query typically has three requirements:
- Filter:
{ tsv: { $text: 'query' } }
. Return only results that match. - Order:
{ $order: [{ $text: ['tsv', 'query'] }], ... }
. Sort results by relevance. - Projection:
{ tsv: { query: true } }
. Return snippets of the document surrounding matches.
In all three, tsv
is a computed column of type TSVector.
Aggregations
In Graffy PG, aggregations are specified using the $group
argument in $key, and special properties like $count
, $sum
etc. in the projection. $group
may be true
or an array.
Consider a table of books with columns authorId
and copiesSold
. We want the to compute aggregates on the copiesSold
column.
Without Group By
Let's say we want the total copies sold of all the books in our database. We use $group: true
, like:
{
books: {
$key: { $group: true },
$sum: { copiesSold: true }
}
Note how the field to sum is specified; this way, multiple fields may be specified.
As always, the result will mirror the query:
books: [{
$key: { $group: true },
$sum: { copiesSold: 12345 }
}]
With Group By
Now let's say we want the separate totals for each author. As we might have a very large number of authors, we might need to paginate over the results.
The grouping properties (e.g. authorId
) may also be included in the projection, and these values may even be used to construct links.
{
books: {
$key: { $group: ['authorId'], $first: 30 },
authorId: true,
author: { namme: true }, // Link to ['users', authorId]
$sum: { copiesSold: true }
}
Aggregate functions
Graffy supports the following aggregate functions.
$count
of rows; this is just specified as$count: true
, without any fields under it. (All other aggregate functions require fields to be specified.)$sum
,$avg
,$max
,$min
$card
(cardinality), or the number of unique values in a column