# 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`](https://www.npmjs.com/package/pgsql-parser). For body-only PL/pgSQL deparsing, see [`plpgsql-deparser`](https://www.npmjs.com/package/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 ```bash npm install plpgsql-parser ``` ## Usage ```typescript 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`](../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. ```typescript 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](../traverse) 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: ```typescript 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](https://constructive.io) team — creators of modular Postgres tooling for secure, composable backends. If you like our work, contribute on [GitHub](https://github.com/constructive-io).** ## Related * [pgpm](https://pgpm.dev): A Postgres Package Manager that brings modular development to PostgreSQL with reusable packages, deterministic migrations, recursive dependency resolution, and tag-aware versioning. * [pgsql-test](https://www.npmjs.com/package/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](https://www.npmjs.com/package/pgsql-seed): PostgreSQL seeding utilities for CSV, JSON, SQL data loading, and pgpm deployment. * [pgsql-parser](https://www.npmjs.com/package/pgsql-parser): The real PostgreSQL parser for Node.js, providing symmetric parsing and deparsing of SQL statements with actual PostgreSQL parser integration. * [pgsql-deparser](https://www.npmjs.com/package/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](https://www.npmjs.com/package/@pgsql/parser): Multi-version PostgreSQL parser with dynamic version selection at runtime, supporting PostgreSQL 15, 16, and 17 in a single package. * [@pgsql/types](https://www.npmjs.com/package/@pgsql/types): Offers TypeScript type definitions for PostgreSQL AST nodes, facilitating type-safe construction, analysis, and manipulation of ASTs. * [@pgsql/enums](https://www.npmjs.com/package/@pgsql/enums): Provides TypeScript enum definitions for PostgreSQL constants, enabling type-safe usage of PostgreSQL enums and constants in your applications. * [@pgsql/utils](https://www.npmjs.com/package/@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](https://www.npmjs.com/package/@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](https://www.npmjs.com/package/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](https://github.com/constructive-io/libpg-query-node): 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.