pg-view-generator

TypeScript SDK to generate PostgreSQL schema and view SQL from YAML definitions.

Installation

npm install pg-view-generator

Usage

import { loadConfig, generateSQL } from 'pg-view-generator';

// Load table definitions from YAML
const config = await loadConfig('./access.yaml');

// Generate SQL
const sql = generateSQL(config, { targetSchema: 'hex-analytics' });

console.log(sql);

YAML Format

The YAML file contains only table definitions:

tables:
  - name: customers
    source: public.customers
    columns:
      - id
      - name
      - city

  - name: orders
    source: public.orders
    columns:
      - id
      - customer_id
      - total
      - created_at

Generated SQL

-- Create schema
CREATE SCHEMA IF NOT EXISTS "hex-analytics";

-- Create views
CREATE VIEW "hex-analytics".customers AS
SELECT id, name, city
FROM public.customers;

CREATE VIEW "hex-analytics".orders AS
SELECT id, customer_id, total, created_at
FROM public.orders;

Workflow

The SDK generates schema and view creation SQL. Role and grant management is handled separately:

-- 1. Before migrations: drop the schema
DROP SCHEMA IF EXISTS "hex-analytics" CASCADE;

-- 2. Run your migrations...

-- 3. Run SDK-generated SQL (creates schema + views)

-- 4. Grant access to users
CREATE ROLE my_service_user LOGIN PASSWORD 'secret';
GRANT USAGE ON SCHEMA "hex-analytics" TO my_service_user;
GRANT SELECT ON ALL TABLES IN SCHEMA "hex-analytics" TO my_service_user;

API

loadConfig(path: string): Promise<Config>

Load and validate a configuration from a YAML file.

const config = await loadConfig('./access.yaml');

loadConfigFromString(content: string): Config

Load and validate a configuration from a YAML string.

const config = loadConfigFromString(`
tables:
  - name: users
    source: public.users
    columns: [id, email]
`);

generateSQL(config: Config, options: GenerateOptions): string

Generate the SQL script from the configuration.

const sql = generateSQL(config, { targetSchema: 'api' });

quoteIdentifier(name: string): string

Quote an identifier if it contains uppercase letters, special characters, or starts with a digit.

quoteIdentifier('users');        // 'users'
quoteIdentifier('Orders');       // '"Orders"'
quoteIdentifier('my-table');     // '"my-table"'

quoteQualifiedName(qualifiedName: string): string

Quote a qualified name (e.g., "schema.table") by quoting each part separately.

quoteQualifiedName('public.users');   // 'public.users'
quoteQualifiedName('public.Orders');  // 'public."Orders"'

Types

interface TableConfig {
  name: string;      // View name in target schema
  source: string;    // Fully qualified source table (e.g., "public.customers")
  columns: string[]; // Columns to expose in the view
}

interface Config {
  tables: TableConfig[];
}

interface GenerateOptions {
  targetSchema: string;
}

Development

# Install dependencies
npm install

# Build
npm run build

# Run tests (requires Docker for testcontainers)
npm test

# Run tests in watch mode
npm run test:watch

License

MIT