Skip to main content

Codecs

A PgCodec ("codec") represents a type (data type) in the database. There are loads of built in codecs for dealing with the built-in types in Postgres made available via the TYPES export, but you can also create your own codecs for other types using the various helpers.

Every codec has a name, which is a handy identifier for you to use to reference it (you can reference codecs from the registry via registry.pgCodecs[name]). A codec also has an identifier which is the name of the type in the database.

Codecs are responsible for performing coercion and validation; they should throw errors if the value supplied is invalid.

TYPES​

import { TYPES } from "@dataplan/pg";

const intCodec = TYPES.int;

The TYPES object comprises a number of built-in codecs for common types in your database; the following keys on TYPES represent the similarly named database types (e.g. TYPES.boolean represents the bool type):

  • boolean
  • int2
  • int
  • bigint
  • float4
  • float
  • money
  • numeric
  • char
  • bpchar
  • varchar
  • text
  • name
  • json
  • jsonb
  • xml
  • citext
  • uuid
  • timestamp
  • timestamptz
  • date
  • time
  • timetz
  • inet
  • regproc
  • regprocedure
  • regoper
  • regoperator
  • regclass
  • regtype
  • regrole
  • regnamespace
  • regconfig
  • regdictionary
  • cidr
  • macaddr
  • macaddr8
  • interval
  • bit
  • varbit
  • point
  • line
  • lseg
  • box
  • path
  • polygon
  • circle
  • hstore
  • void

recordCodec(config)​

recordCodec is a helper function that returns a PgCodec representing a "composite type" (or "record") - a structured type with attributes. This is most commonly used when defining a table (the "attributes" in this case being which columns it has), but is also useful in other cases.

The record codec config should contain:

  • name: string - the name to use for this codec
  • identifier: SQL - the database name for this type
  • attributes: Record<string, PgCodecAttribute> - the attributes (columns) on this codec; the keys on this object are the attribute names, and the values are objects with the following options:
    • codec: PgCodec - the PgCodec that details the type of this attribute
    • notNull: boolean (optional) - if true, indicates that the column cannot be null
    • hasDefault: boolean (optional) - if true, indicates that the column has a default (and thus may be omitted from INSERT operations)
    • expression(alias: SQL): SQL (optional) - indicates that this attribute is not a real attribute, but instead a computed value that can be computed using the given expression
  • polymorphism (extremely optional) - see polymorphism

Example​

const forumCodec = recordCodec({
  name: "forums",
  identifier: sql`app_public.forums`,
  attributes: {
    id: {
      codec: TYPES.uuid,
      notNull: true,
      hasDefault: true,
    },
    name: {
      codec: TYPES.citext,
      notNull: true,
    },
    archived_at: {
      codec: TYPES.timestamptz,
    },
    is_archived: {
      codec: TYPES.boolean,
      expression(alias) {
        return sql`${alias}.archived_at is not null`;
      },
    },
  },
});

listOfCodec(innerCodec, config = {})​

listOfCodec returns a new codec that represents a list (array) of the given innerCodec. Optionally you may provide details about this codec:

  • identifier - the database name for this type

Example​

For example, in this hypothetical E-commerce scenario, listOfCodec is used in combination with the $pgSelect.placeholder() method to return a SQL expression that allows the transformed list of $orderIds to be referenced inside the step for selecting the associated order items.

const $orders = orders.find({
  customer_id: context().get("customerId"),
});

const $orderIds = applyTransforms(each($orders, ($order) => $order.get("id")));

const $orderItems = registry.pgResources.order_items.find();

$orderItems.where(
  sql`${$orderItems}.order_id = ANY (${$orderItems.placeholder(
    $orderIds,
    listOfCodec(TYPES.uuid),
  )})`,
);

rangeOfCodec(innerCodec, name, identifier)​

rangeOfCodec returns a new codec that represents a range of the given innerCodec. You must specify the name and identifier to use for this codec.

Custom scalar codecs​

Should you need to define more scalar codecs than those available via TYPES, you may create a PgCodec object representing them. The object can have the following properties:

  • name: string (required) - the name to use for this codec
  • sqlType: string (required) - the identifier for this codec, the SQL fragment that represents the name of the type in the database
  • fromPg - optional callback function that, given the textual representation from postgres, returns the internal representation for the value to use in JavaScript
  • toPg - optional callback function that, given the internal representation used in JavaScript, returns the value to insert into an SQL statement - this should be a simple scalar (text, etc) that can be cast by postgres
  • attributes - see recordCodec instead
  • polymorphism - see polymorphism