@pgpm/types

@pgpm/types

Core PostgreSQL data types with SQL scripts.

Overview

@pgpm/types provides a collection of validated PostgreSQL domain types for common data formats. These domains enforce data integrity at the database level through regex-based validation, ensuring that only properly formatted data is stored.

Features

• email: Case-insensitive email address validation
• url: HTTP/HTTPS URL validation
• origin: Origin URL validation (scheme + host)
• hostname: Domain name validation
• image: JSON-based image metadata with URL and MIME type
• attachment: File attachment metadata as a URL string or JSON with URL and MIME type
• upload: File upload metadata
• single_select: Single selection field
• multiple_select: Multiple selection field

Installation

If you have pgpm installed:

pgpm install @pgpm/types
pgpm deploy

This is a quick way to get started. The sections below provide more detailed installation options.

Prerequisites

# Install pgpm CLI 
npm install -g pgpm

# Start local Postgres (via Docker) and export env vars
pgpm docker start
eval "$(pgpm env)"

Tip: Already running Postgres? Skip the Docker step and just export your PG* environment variables.

Add to an Existing Package

# 1. Install the package
pgpm install @pgpm/types

# 2. Deploy locally
pgpm deploy 

Add to a New Project

# 1. Create a workspace
pgpm init workspace

# 2. Create your first module
cd my-workspace
pgpm init

# 3. Install a package
cd packages/my-module
pgpm install @pgpm/types

# 4. Deploy everything
pgpm deploy --createdb --database mydb1

Usage

Creating Tables with Validated Types

CREATE TABLE customers (
  id serial PRIMARY KEY,
  email email,
  website url,
  domain hostname,
  profile_image image,
  document attachment
);

Email Domain

The email domain validates email addresses using a comprehensive regex pattern and stores them as case-insensitive text (citext).

-- Valid emails
INSERT INTO customers (email) VALUES
  ('user@example.com'),
  ('john.doe@company.co.uk'),
  ('support+tag@service.io');

-- Invalid email (will fail)
INSERT INTO customers (email) VALUES ('not-an-email');
-- ERROR: value for domain email violates check constraint

Validation Pattern: RFC-compliant email format with support for special characters and subdomains.

URL Domain

The url domain validates HTTP and HTTPS URLs.

-- Valid URLs
INSERT INTO customers (website) VALUES
  ('http://example.com'),
  ('https://www.example.com/path?query=value'),
  ('http://foo.bar/path_(with)_parens');

-- Invalid URLs (will fail)
INSERT INTO customers (website) VALUES
  ('ftp://example.com'),  -- Only http/https allowed
  ('example.com'),        -- Missing protocol
  ('http://');            -- Incomplete URL

Validation Pattern: Requires http:// or https:// protocol and valid URL structure.

Hostname Domain

The hostname domain validates domain names without protocol or path.

-- Valid hostnames
INSERT INTO customers (domain) VALUES
  ('example.com'),
  ('subdomain.example.com'),
  ('my-site.co.uk');

-- Invalid hostnames (will fail)
INSERT INTO customers (domain) VALUES
  ('http://example.com'),           -- No protocol allowed
  ('example.com/path'),              -- No path allowed
  ('invalid..domain.com');           -- Invalid format

Validation Pattern: Standard domain name format with support for subdomains and hyphens.

Image and Attachment Domains

The image domain stores JSON objects with URL and MIME type information. The attachment domain accepts either that JSON shape or a plain URL string. The upload domain uses the same JSON object shape as image, ensuring both the file URL and MIME type are present.

-- Valid image
INSERT INTO customers (profile_image) VALUES
  ('{"url": "https://cdn.example.com/photo.jpg", "mime": "image/jpeg"}'::json);

-- Valid attachment
INSERT INTO customers (document) VALUES
  ('{"url": "https://storage.example.com/file.pdf", "mime": "application/pdf"}'::json);

-- Valid attachment as plain URL
INSERT INTO customers (document) VALUES ('https://storage.example.com/favicon.ico');

Structure: Image values and JSON-form attachments expect url and mime properties; attachments also allow a bare URL string.

Domain Types Reference

Domain	Base Type	Description	Example
email	citext	Case-insensitive email address	user@example.com
url	text	HTTP/HTTPS URL	https://example.com/path
origin	text	Origin (scheme + host)	https://example.com
hostname	text	Domain name without protocol	example.com
image	json	Image metadata with URL and MIME	{"url": "...", "mime": "image/jpeg"}
attachment	json	File attachment URL or metadata	{"url": "...", "mime": "application/pdf"} or https://example.com/favicon.ico
upload	json	File upload metadata (URL + MIME)	{"url": "...", "mime": "application/pdf"}
single_select	text	Single selection value	Text value
multiple_select	text[]	Multiple selection values	Array of text values

Validation Benefits

Using domain types provides several advantages over plain text columns:

1. Data Integrity: Invalid data is rejected at insert/update time
2. Self-Documenting: Column types clearly indicate expected format
3. Consistent Validation: Same rules applied across all tables
4. Database-Level Enforcement: No reliance on application-level validation alone

Dependencies

• @pgpm/verify: Verification utilities for database objects
• PostgreSQL citext extension (for email domain)

Testing

pnpm test

The test suite validates:

• Email format validation (valid and invalid cases)
• URL format validation with extensive test cases
• Hostname format validation
• Image, upload, and attachment JSON structure validation

Related Tooling

• pgpm: PostgreSQL Package Manager for modular Postgres development. Works with database workspaces, scaffolding, migrations, seeding, and installing database packages.
• pgsql-test: Isolated testing environments with per-test transaction rollbacks—ideal for integration tests, complex migrations, and RLS simulation.
• supabase-test: Supabase-native test harness preconfigured for the local Supabase stack—per-test rollbacks, JWT/role context helpers, and CI/GitHub Actions ready.
• graphile-test: Authentication mocking for Graphile-focused test helpers and emulating row-level security contexts.
• pgsql-parser: SQL conversion engine that interprets and converts PostgreSQL syntax.
• libpg-query-node: Node.js bindings for libpg_query, converting SQL into parse trees.
• pg-proto-parser: Protobuf parser for parsing PostgreSQL Protocol Buffers definitions to generate TypeScript interfaces, utility functions, and JSON mappings for enums.

Documentation & Skills

• constructive-skills: Platform documentation and AI agent skills — feature catalog, blueprint reference, SDK guides, and deployment guides.

Install skills for AI coding agents:

# All platform skills (security, blueprints, codegen, billing, etc.)
npx skills add constructive-io/constructive-skills

# Individual repo skills (pgpm, testing, CLI, search, etc.)
npx skills add https://github.com/constructive-io/constructive --skill pgpm
npx skills add https://github.com/constructive-io/constructive --skill constructive-testing

Disclaimer

AS DESCRIBED IN THE LICENSES, THE SOFTWARE IS PROVIDED "AS IS", AT YOUR OWN RISK, AND WITHOUT WARRANTIES OF ANY KIND.

No developer or entity involved in creating this software will be liable for any claims or damages whatsoever associated with your use, inability to use, or your interaction with other users of the code, including any direct, indirect, incidental, special, exemplary, punitive or consequential damages, or loss of profits, cryptocurrencies, tokens, or anything else of value.