plpgsql-parser

plpgsql-parser

Combined SQL + PL/pgSQL parser with hydrated ASTs and transform API.

⚠️ Experimental: This package is currently experimental. If you're looking for just SQL parsing, see pgsql-parser. For body-only PL/pgSQL deparsing, see plpgsql-deparser.

Overview

This package provides a unified API for heterogeneous parsing and deparsing of SQL scripts containing PL/pgSQL functions. It handles the full pipeline: parsing SQL + PL/pgSQL together, transforming ASTs, and deparsing back to complete SQL.

Use this package when you need to:

• Parse and deparse complete CREATE FUNCTION statements with PL/pgSQL bodies
• Transform both SQL and embedded PL/pgSQL expressions (e.g., rename schemas)
• Round-trip SQL through parse → modify → deparse

Key features:

• Auto-detects CREATE FUNCTION statements with LANGUAGE plpgsql
• Hydrates PL/pgSQL function bodies into structured ASTs
• Automatic RETURN statement handling based on function return type
• Transform API for parse → modify → deparse workflows
• Re-exports underlying primitives for power users

Installation

npm install plpgsql-parser

Usage

import { parse, transform, deparseSync, loadModule } from 'plpgsql-parser';

// Initialize the WASM module
await loadModule();

// Parse SQL with PL/pgSQL functions - auto-detects and hydrates
const result = parse(`
  CREATE FUNCTION my_func(p_id int)
  RETURNS void
  LANGUAGE plpgsql
  AS $$
  BEGIN
    RAISE NOTICE 'Hello %', p_id;
  END;
  $$;
`);

console.log(result.functions.length); // 1
console.log(result.functions[0].plpgsql.hydrated); // Hydrated AST

// Transform API for parse -> modify -> deparse pipeline
const output = transformSync(sql, (ctx) => {
  // Modify the function name
  ctx.functions[0].stmt.funcname[0].String.sval = 'renamed_func';
});

// Deparse back to SQL
const sql = deparseSync(result, { pretty: true });

API

parse(sql, options?)

Parses SQL and auto-detects PL/pgSQL functions, hydrating their bodies.

Options:

• hydrate (default: true) - Whether to hydrate PL/pgSQL function bodies

Returns a ParsedScript with:

• sql - The raw SQL parse result
• items - Array of parsed items (statements and functions)
• functions - Array of detected PL/pgSQL functions with hydrated ASTs

transform(sql, callback, options?)

Async transform pipeline: parse -> modify -> deparse.

transformSync(sql, callback, options?)

Sync version of transform.

deparseSync(parsed, options?)

Converts a parsed script back to SQL.

Options:

• pretty (default: true) - Whether to pretty-print the output

Traverse API

The walkers themselves live in @pgsql/traverse and are re-exported here, so one import covers parsing and traversal. This package owns the one entry point that genuinely needs a parser: SQL text in.

walkSql(sql, visitors, options?)

Parses a SQL string, hydrates its PL/pgSQL function bodies, and walks both with the given visitors — SQL statements and PL/pgSQL bodies in a single pass.

import { loadModule, walkSql } from 'plpgsql-parser';

await loadModule();

const result = walkSql(sql, {
  // SQL nodes, at the top level and inside function bodies
  RangeVar: (path, ctx) => {
    if (ctx.isWrite && path.node.schemaname === 'audit') {
      ctx.abort('the audit schema is read-only');
    }
    if (ctx.insideFunction) {
      console.log(`${path.node.relname} referenced by ${ctx.functionName}`);
    }
  },
  // PL/pgSQL-only nodes, in the same visitor
  PLpgSQL_stmt_dynexecute: (_path, ctx) => ctx.abort('dynamic EXECUTE is not allowed')
});

result.aborted; // true when a visitor called ctx.abort()
result.reason;  // 'the audit schema is read-only'

Pass an array of visitors to compose independent policies in one parse. Every callback receives a WalkContext (stmtTag, stmtIndex, isWrite, isRead, insideFunction, functionName, abort) — see the @pgsql/traverse README for the full traversal reference.

Options:

• walkFunctionBodies (default: true) - Hydrate and walk PL/pgSQL function bodies. false skips the PL/pgSQL parse entirely
• walkSqlExpressions (default: true) - Recurse into hydrated SQL expressions inside bodies
• sqlVisitor - Override the visitor used for those SQL expressions

Unparseable input is reported as { aborted: true, reason } rather than throwing, so a validator can treat "rejected" and "could not be understood" uniformly.

walk(ast, visitors, options?)

Re-exported from @pgsql/traverse. Same behavior as walkSql, but takes an AST you already have — a ParsedScript from parse(), a ParseResult, a SQL node, or a PL/pgSQL node:

import { loadModule, parse, walk } from 'plpgsql-parser';

await loadModule();

const parsed = parse(`
  CREATE TABLE users (id int);
  CREATE FUNCTION get_user(id int) RETURNS text LANGUAGE plpgsql AS $$
  BEGIN
    RETURN (SELECT name FROM users WHERE users.id = id);
  END;
  $$;
`);

walk(parsed, {
  CreateStmt: () => console.log('CREATE TABLE statement'),
  RangeVar: (path) => console.log('Table reference:', path.node.relname),
  PLpgSQL_stmt_return: () => console.log('PL/pgSQL return statement')
});

Also re-exported: walkSqlAst (SQL-only primitive), walkPlpgsqlAst (PL/pgSQL-only primitive), PlpgsqlNodePath, and the WalkContext / UnifiedVisitor / WalkResult types.

Re-exports

For power users, the package re-exports underlying primitives:

• parseSql - SQL parser from @libpg-query/parser
• parsePlpgsqlBody - PL/pgSQL parser from @libpg-query/parser
• deparseSql - SQL deparser from pgsql-deparser
• deparsePlpgsqlBody - PL/pgSQL deparser from plpgsql-deparser
• hydratePlpgsqlAst - Hydration utility from plpgsql-deparser
• dehydratePlpgsqlAst - Dehydration utility from plpgsql-deparser
• walk, walkSqlAst, walkPlpgsqlAst - Walkers from @pgsql/traverse

License

MIT

🛠 Built by the Constructive team — creators of modular Postgres tooling for secure, composable backends. If you like our work, contribute on GitHub.

Related

• pgpm: A Postgres Package Manager that brings modular development to PostgreSQL with reusable packages, deterministic migrations, recursive dependency resolution, and tag-aware versioning.
• pgsql-test: Instant, isolated PostgreSQL databases for each test with automatic transaction rollbacks, context switching, and clean seeding for fast, reliable database testing.
• pgsql-seed: PostgreSQL seeding utilities for CSV, JSON, SQL data loading, and pgpm deployment.
• pgsql-parser: The real PostgreSQL parser for Node.js, providing symmetric parsing and deparsing of SQL statements with actual PostgreSQL parser integration.
• pgsql-deparser: A streamlined tool designed for converting PostgreSQL ASTs back into SQL queries, focusing solely on deparser functionality to complement pgsql-parser.
• @pgsql/parser: Multi-version PostgreSQL parser with dynamic version selection at runtime, supporting PostgreSQL 15, 16, and 17 in a single package.
• @pgsql/types: Offers TypeScript type definitions for PostgreSQL AST nodes, facilitating type-safe construction, analysis, and manipulation of ASTs.
• @pgsql/enums: Provides TypeScript enum definitions for PostgreSQL constants, enabling type-safe usage of PostgreSQL enums and constants in your applications.
• @pgsql/utils: A comprehensive utility library for PostgreSQL, offering type-safe AST node creation and enum value conversions, simplifying the construction and manipulation of PostgreSQL ASTs.
• @pgsql/traverse: PostgreSQL AST traversal utilities for pgsql-parser, providing a visitor pattern for traversing PostgreSQL Abstract Syntax Tree nodes, similar to Babel's traverse functionality but specifically designed for PostgreSQL AST structures.
• pg-proto-parser: A TypeScript tool that parses PostgreSQL Protocol Buffers definitions to generate TypeScript interfaces, utility functions, and JSON mappings for enums.
• libpg-query: The real PostgreSQL parser exposed for Node.js, used primarily in pgsql-parser for parsing and deparsing SQL queries.

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 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 Software code or Software CLI, including any direct, indirect, incidental, special, exemplary, punitive or consequential damages, or loss of profits, cryptocurrencies, tokens, or anything else of value.