npm package: @infisical/pg-view-generator
Description: TypeScript SDK to generate PostgreSQL schema + view SQL from YAML definitions
Latest version: 1.1.0

# pg-view-generator

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

## Installation

```bash
npm install pg-view-generator
```

## Usage

```typescript
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:

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

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

## Generated SQL

```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:

```sql
-- 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.

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

### `loadConfigFromString(content: string): Config`

Load and validate a configuration from a YAML string.

```typescript
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.

```typescript
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.

```typescript
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.

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

## Types

```typescript
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

```bash
# 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