Data Modeling

The PostgreSQL data connector makes available any database resource that is listed in its configuration. In order to populate the configuration, the connector supports introspecting the database via the CLI update command, see updating with introspection.

Queryable collections

The PostgreSQL data connector supports several types of queryable collections:

• Tables
• Views
• Native Operations

Tables and views are typically discovered automatically during the introspection process.

Native queries on the other hand are named SQL select statements that you specify in the data connector configuration. They are similar to a database view, but more expressive as they admit parameters similar to a function and do not require any DDL permissions on the database. See the configuration reference on Native Queries.

Scalar types

The PostgreSQL data connector supports any scalar type that PostgreSQL knows how encode and decode as JSON - No scalar type is handled as a special case.

Data type names

Many scalar types have aliases in PostgreSQL, which can be found in the PostgreSQL documentation on data types. The connector exposes only the canonical name of a scalar type (as per pg_catalog.pg_type(typname)) in its NDC schema.

In order to use standard GraphQL scalar types rather than custom scalar types in the resulting GraphQL schema, the DDN configuration enables expressing a mapping between data connector types and GraphQL types.

Limited support

The data connector doesn't currently support range types, and the introspection process will not include entities of these types.

Manually adding entries for a type to the configuration may work to an extent, but there is no guarantee that it will work as expected.

Structural types

The data connector supports PostgreSQL array and composite types, and they will appear as such in the schema it exposes.

Collections may have columns that are arrays of any supported type, and columns that are of a composite type, and arbitrary combinations thereof.

As an example (irrespective of whether this is a good database design or not) the following may be tracked with full typing fidelity:

CREATE TYPE address AS (address_lines varchar[], zipcode varchar, country varchar);
CREATE TABLE person(id int primary key, name varchar, address address);

Note that the json and jsonb types are not considered structural types. They are opaque scalar types that do not admit to any schema.

Queries

The connector supports all query operations; see the query documentation for details.

Filtering

The connector supports the introspection and usage of all binary comparison functions and operators defined for any tracked scalar type, built-in and user-defined alike.

However, because PostgreSQL operators don't translate syntactically well to GraphQL, and because not all binary procedures are necessarily appropriate comparison operators, the introspection process needs some guidance in order to map names and select which functions to include. See the configuration reference for Comparison Operators for details.

Mutations

We currently support two types of mutations:

Point mutations

These are single row insert, delete and update mutations generated for each database table, and exposed as procedures which can used as Commands in OpenDD. To expose these, set mutationsVersion: "v2" in your configuration.

• An insert procedure is generated per table

  v2_insert_<table>(
      objects: [<table-object>],
      post_check: <boolexpr>
  )

  Using it, you can insert multiple objects and include a post check for permissions.

• A delete procedure is generated for each unique constraint on each table

  v2_delete_<table>_by_<column_and_...>(
      key_<column1>: <value>,
      key_<column2>: <value>,
      ...,
      pre_check: <boolexpr>
  )

  Using it, you can delete a single row using the uniqueness constraint, and include a pre-check for permissions.

• An update procedure is generated for each unique constraint on each table

  v2_update_<table>_by_<column_and_...>(
      key_<column1>: <value>,
      key_<column2>: <value>,
      ...,
      update_columns: { <column>: { _set: <value> }, ... },
      pre_check: <boolexpr>,
      post_check: <boolexpr>
  )

  Using it, you can update a single row using the uniqueness constraint by updating the relevant columns, and include a pre-check and post-check for permissions.

The pre_check and post_check arguments of a mutation, can be set in metadata for all queries using an Argument Preset on the mutation's Command Permissions.

Native Mutations

You can also write your own SQL queries and expose them via the GraphQL engine using Native Mutations.