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 package provides a visitor pattern for traversing PL/pgSQL ASTs, similar to @pgsql/traverse but designed for PL/pgSQL node types.

walk(root, callback, options?)

Walks the tree of PL/pgSQL AST nodes using a visitor pattern.

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

await loadModule();

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

// Visit PL/pgSQL nodes
const visitor: PLpgSQLVisitor = {
  PLpgSQL_stmt_block: (path) => {
    console.log('Found block at path:', path.path);
  },
  PLpgSQL_stmt_return: (path) => {
    console.log('Found return statement');
  },
};

walk(parsed.functions[0].plpgsql.hydrated, visitor);

Options:

• walkSqlExpressions (default: true) - Whether to recurse into hydrated SQL expressions
• sqlVisitor - SQL visitor to use when walking hydrated SQL expressions (from @pgsql/traverse)

walkParsedScript(parsed, plpgsqlVisitor, sqlVisitor?)

Convenience function that walks both SQL statements and PL/pgSQL function bodies.

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

await loadModule();

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

walkParsedScript(
  parsed,
  // PL/pgSQL visitor
  {
    PLpgSQL_stmt_return: (path) => {
      console.log('PL/pgSQL return statement');
    },
  },
  // SQL visitor (optional) - visits both top-level SQL and embedded SQL in functions
  {
    CreateStmt: (path) => {
      console.log('CREATE TABLE statement');
    },
    RangeVar: (path) => {
      console.log('Table reference:', path.node.relname);
    },
  }
);

PLpgSQLNodePath

The path object passed to visitor functions:

class PLpgSQLNodePath<TTag extends string = string> {
  tag: TTag;           // Node type (e.g., 'PLpgSQL_stmt_block')
  node: any;           // The actual node data
  parent: PLpgSQLNodePath | null;  // Parent path
  keyPath: readonly (string | number)[];  // Full path array
  
  get path(): (string | number)[];  // Copy of keyPath
  get key(): string | number;       // Last element of path
}

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

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.