pg-meta

Pure-TypeScript SQL generators for Postgres schema operations. Studio uses this package whenever it needs to read or modify the database schema — listing tables, creating columns, altering policies, defining triggers, etc.

Purpose

Decouple Studio's UI from the actual SQL. The package returns { sql, zod } pairs (a SQL statement plus the Zod schema for validating its result), and is the only place SQL strings are constructed. Studio's custom ESLint rule studio/require-safe-sql-fragment ensures no UI code interpolates user input into SQL by hand.

Directory layout

packages/pg-meta/
├── src/
│   ├── pg-meta-tables.ts
│   ├── pg-meta-columns.ts
│   ├── pg-meta-functions.ts
│   ├── pg-meta-policies.ts
│   ├── pg-meta-publications.ts
│   ├── pg-meta-roles.ts
│   ├── pg-meta-schemas.ts
│   ├── pg-meta-triggers.ts
│   ├── pg-meta-views.ts
│   ├── pg-meta-extensions.ts
│   ├── pg-meta-foreign-tables.ts
│   ├── pg-meta-indexes.ts
│   ├── pg-meta-materialized-views.ts
│   ├── pg-meta-types.ts
│   ├── pg-meta-table-privileges.ts
│   ├── pg-meta-column-privileges.ts
│   ├── pg-meta-version.ts
│   ├── pg-meta-config.ts
│   ├── helpers.ts
│   ├── constants.ts
│   ├── index.ts
│   ├── pg-format/        # Safe identifier / literal / format helpers
│   ├── query/            # Query builders
│   └── sql/              # Raw SQL templates
├── test/
└── package.json

Key abstractions

Generator	What it produces	Used in Studio at
pg-meta-tables.ts	List / create / alter / drop table SQL.	Table editor, table-rows.
pg-meta-columns.ts	Column CRUD (8 KB+ — many edge cases).	Table editor, column edits.
pg-meta-policies.ts	RLS policy CRUD.	Auth → Policies.
pg-meta-publications.ts	Logical replication publications.	Database → Replication.
pg-meta-roles.ts	DB roles and grants.	Database → Roles.
pg-meta-functions.ts, pg-meta-triggers.ts	DB functions and triggers.	Database → Functions / Triggers.
pg-meta-extensions.ts	Postgres extensions.	Database → Extensions.
pg-meta-foreign-tables.ts	FDW (foreign data wrappers).	Integrations / wrappers.
pg-meta-indexes.ts, pg-meta-materialized-views.ts, pg-meta-views.ts	Indexes, mat views, views.	Various Database surfaces.
pg-meta-types.ts, pg-meta-version.ts, pg-meta-config.ts	Misc metadata.	Project info, type pickers.

Safety

packages/pg-meta/src/pg-format/ exports format, literal, and ident helpers — analogous to PostgreSQL's format() function — so user-supplied values are always escaped. The studio/require-safe-sql-fragment ESLint rule prevents direct template-string SQL with user input.

How it works

Generators return statements like:

import pgMeta from 'pg-meta';

const { sql, zod } = pgMeta.tables.create({
  name: 'users',
  columns: [
    /* ... */
  ],
});

sql is the prepared statement and zod is a schema for validating the response. Studio then sends sql through apps/studio/data/sql/execute-sql-query.ts (which calls the platform's "execute SQL" endpoint) and validates the returned rows against zod.

graph LR
    UI[Studio UI]
    Hook[data/<domain>/use*Mutation]
    PgMeta[packages/pg-meta]
    SqlExec[data/sql/execute-sql-mutation]
    PgRest[Mgmt API "execute SQL"]
    Postgres[(Postgres)]

    UI --> Hook
    Hook -->|generate sql + zod| PgMeta
    Hook -->|run sql| SqlExec
    SqlExec --> PgRest
    PgRest --> Postgres
    Postgres --> PgRest
    PgRest --> SqlExec
    SqlExec -->|validate| Hook
    Hook --> UI

Tests

packages/pg-meta/test/ contains snapshot-style tests that assert generated SQL is what we expect. CI: pg-meta-tests.yml.

Integration points

• Consumed by apps/studio — every schema-editing surface.
• The package itself has no network code; it's pure SQL generation.

Entry points for modification

• Add a new generator → create src/pg-meta-<thing>.ts with { sql, zod }-returning helpers, plus tests in test/.
• Add a new option to an existing generator → extend the generator's options type and update tests.
• Add a new safe-formatting helper → put it under src/pg-format/.