postgrest-js

When performing a join in a .select() query, postgrest-js uses the relationship metadata to decide the shape of the resulting object.

Forward Relationships

For a forward relationship (e.g., table(relation_name)):

• One-to-One: Returns the object directly: { relation_name: T }.
• One-to-Many: Returns an array: { relation_name: T[] }.
• Inner Join: If innerJoin: true is specified, the result is not nullable.

Reverse Relationships

For reverse relationships (e.g., child_table(parent_id)):

• Non-self-reference: If the relationship is nullable, it returns T | null. If not, it returns T.
• Self-reference via column: If using a column match (e.g., parent_id), it returns a single object T | null (if nullable) or T.
• Self-reference via relation: If using the reference relation, it returns an array T[].

Error Handling

If a relationship cannot be resolved or is invalid, the type will resolve to a SelectQueryError containing a descriptive string, such as 'Failed to resolve relationship.' or 'Invalid Relationships cannot infer result type'.

The spread operator (...) in a .select() query allows you to include all fields from a related table. The resulting type depends on the relationship type and the PostgREST version.

One-to-One or Many-to-One

The spread operator returns the object properties directly within the parent object.

Many-to-Many (PostgREST v13+)

If PostgrestVersion is 13 or higher and you spread over a many-to-many relationship, the library uses SpreadOnManyEnabled logic. Instead of returning an array of objects, it converts the fields into correlated arrays.

For example, if spreading a many-to-many relation that would normally return [{a: 1, b: 2}, {a: 3, b: 4}], the spread result becomes { a: [1, 3], b: [2, 4] }.

Errors

If you attempt to spread a relationship that is not many-to-one or one-to-one, you will receive a SelectQueryError: "<RelationName>" and "<TargetName>" do not form a many-to-one or one-to-one relationship spread not possible.

The PostgrestFilterBuilder provides a fluent API for constructing PostgREST filter queries. It allows you to chain multiple filter methods to refine your database queries. Most methods return this, enabling a builder pattern.

Common Filter Methods

• Equality: .eq(column, value) matches rows where the column equals the value. Use .is(column, value) to check for NULL or boolean values.
• Inequality: .neq(column, value) matches rows where the column is not equal to the value.
• Comparison: .gt(), .gte(), .lt(), and .lte() for greater than, greater than or equal, less than, and less than or equal.
• Pattern Matching: .like(column, pattern) and .ilike(column, pattern) (case-insensitive) for string matching. You can also use .likeAllOf() or .likeAnyOf() for multiple patterns.
• Set Membership: .in(column, values) matches if the column value is included in the provided array.
• JSONB/Array/Range: .contains(column, value), .containedBy(column, value), and .overlaps(column, value) for complex types.
• Text Search: .textSearch(column, query, options) for full-text search on text or tsvector columns.
• Logical Operators: .match(queryObject) is a shorthand for multiple .eq() calls. Use .or(filters, options) to satisfy at least one filter, and .not(column, operator, value) to negate a filter.

The GetResult type is the main entry point for determining the TypeScript type of a query result based on a PostgREST select string. It performs deep type inference by parsing the query string and mapping it against the database schema, table relationships, and row definitions.

It handles several complex scenarios:

• Standard Selects: Maps columns and aliases to their correct types.
• RPC Calls: Infers types from the return value of a database function, allowing for chained .select() calls.
• Embedded Resources: Automatically determines if a relationship returns a single object or an array based on the relationship direction (forward/reverse) and nullability.
• Spread Operators: Handles ... spread syntax, including special handling for many-to-many relationships in PostgREST v13+ where fields are converted into correlated arrays.
• Aggregate Functions: Correctly types results from functions like .sum() or .count().
• JSON Paths: Resolves types when accessing nested JSON properties via specific paths.

/**
 * Main entry point for constructing the result type of a PostgREST query.
 *
 * @param Schema - Database schema.
 * @param Row - The type of a row in the current table.
 * @param RelationName - The name of the current table or view.
 * @param Relationships - Relationships of the current table.
 * @param Query - The select query string literal to parse.
 * @param ClientOptions - Client configuration options.
 */
export type GetResult<
  Schema extends GenericSchema,
  Row extends Record<string, unknown>,
  RelationName,
  Relationships,
  Query extends string,
  ClientOptions extends ClientServerOptions
> = ...