PostgreSQL SQL & PL/pgSQL AST Parser (@pgsql/parser)

Common errors

• Cannot find package '@pgsql/parser' or its corresponding type declarations.

  cause The `@pgsql/parser` package has not been installed, or TypeScript cannot resolve it.
  fix Install the package: `npm install @pgsql/parser` or `yarn add @pgsql/parser`.

• Syntax error at or near "..."

  cause The input SQL or PL/pgSQL string contains invalid syntax according to the PostgreSQL grammar, or the parser version used does not support certain syntax.
  fix Carefully review the SQL/PL/pgSQL string for typos, missing punctuation, incorrect keywords, or dialect-specific syntax not compatible with standard PostgreSQL. Ensure the `@pgsql/parser` version supports the target PostgreSQL version's syntax.

• TypeError: parse is not a function (or similar for require)

  cause Attempting to use CommonJS `require` syntax in an ESM module, or vice-versa, or incorrectly accessing the named export `parse`.
  fix For ESM (`type: 'module'` in package.json or `.mjs` files): `import { parse } from '@pgsql/parser';`. For CommonJS (`type: 'commonjs'` or `.cjs` files): `const { parse } = require('@pgsql/parser');`.

Warnings

• breaking The package `plpgsql-parser` with version `0.5.8` as specified is likely an outdated reference. The current, actively maintained parser from the `constructive-io/pgsql-parser` monorepo is `@pgsql/parser`, which is at major version `17.x.x`. Users should install `@pgsql/parser` and other scoped packages like `@pgsql/deparser` and `@pgsql/types`.
  Fix: Migrate to `@pgsql/parser@^17.0.0` and update import paths and API calls according to the latest documentation within the `constructive-io/pgsql-parser` monorepo. Install specific sub-packages, e.g., `npm install @pgsql/parser @pgsql/deparser @pgsql/types`.
• gotcha The package structure is a monorepo. While a top-level `pgsql-parser` package might exist in the monorepo, the recommended and actively developed components for parsing, deparsing, and types are the scoped packages (`@pgsql/parser`, `@pgsql/deparser`, `@pgsql/types`, etc.). Relying on a non-scoped `pgsql-parser` may lead to outdated versions or missing functionalities.
  Fix: Always explicitly import from the scoped packages, e.g., `import { parse } from '@pgsql/parser';`.
• breaking Breaking changes occur between major versions of `@pgsql/parser` due to updates in the underlying PostgreSQL parser (libpg-query) and API refinements. For example, changes in AST node structures or function signatures may be introduced to align with new PostgreSQL features or improve consistency.
  Fix: Always consult the changelog of `@pgsql/parser` and related packages (`@pgsql/deparser`, `@pgsql/types`) when upgrading major versions. Thoroughly test existing parsing and transformation logic after an upgrade.
• gotcha Direct manipulation of the PostgreSQL AST can be complex due to its depth and the specific structure reflecting PostgreSQL's internal representation. Incorrect modifications can lead to invalid SQL or runtime errors during deparsing.
  Fix: Utilize helper packages like `@pgsql/utils` for programmatic AST construction and `@pgsql/traverse` for safer traversal and modification via a visitor pattern. Always validate modified ASTs by deparsing and testing the resulting SQL.

Install

• npm install plpgsql-parser npm
• yarn add plpgsql-parser yarn
• pnpm add plpgsql-parser pnpm

Imports

• parse
  wrong:

  const { parse } = require('pgsql-parser');

  correct:

  import { parse } from '@pgsql/parser';

  The primary parsing function. For CommonJS, use `require('@pgsql/parser').parse`. The `pgsql-parser` entrypoint in the monorepo might not directly expose `parse` in older versions or without specific imports. Prefer scoped package.
• deparse
  wrong:

  import { deparse } from '@pgsql/parser';

  correct:

  import { deparse } from '@pgsql/deparser';

  Deparsing (converting AST back to SQL) is typically handled by the separate `@pgsql/deparser` package for a lighter-weight dependency when only deparsing is needed.
• SelectStmt
  wrong:

  import { SelectStmt } from '@pgsql/parser';

  correct:

  import type { SelectStmt } from '@pgsql/types';

  TypeScript types for AST nodes are provided by the `@pgsql/types` package, ensuring type safety when working with the AST structure. Use `import type` for type-only imports.

Quickstart

Demonstrates parsing a SQL query into an AST, inspecting the AST (specifically a SELECT statement's target list), and then deparsing the AST back into a SQL string. Includes error handling for invalid SQL and shows a PL/pgSQL function parsing.

import { parse } from '@pgsql/parser';
import { deparse } from '@pgsql/deparser';
import type { SelectStmt } from '@pgsql/types';

async function processSqlQuery(sql: string) {
  try {
    console.log(`Parsing SQL: ${sql}`);
    // Parse the SQL query into an Abstract Syntax Tree (AST)
    const ast = await parse(sql);
    console.log('Parsed AST (partial):', JSON.stringify(ast.stmts[0]?.stmt, null, 2));

    // Example: Accessing a specific type of statement and its properties
    if (ast.stmts[0]?.stmt.SelectStmt) {
      const selectStmt = ast.stmts[0].stmt.SelectStmt as SelectStmt;
      console.log('Target list items:', selectStmt.targetList?.map(t => JSON.stringify(t.ResTarget?.val)));
    }

    // Convert the AST back to SQL (deparse)
    const deparsedSql = await deparse(ast);
    console.log(`Deparsed SQL: ${deparsedSql}`);

  } catch (error: any) {
    console.error('Error processing SQL:', error.message);
    if (error.sqlDetails) {
      console.error('SQL Details:', error.sqlDetails);
    }
  }
}

// Example SQL queries
processSqlQuery('SELECT id, name FROM users WHERE status = $1 ORDER BY created_at DESC;');
processSqlQuery('CREATE FUNCTION add(a INT, b INT) RETURNS INT LANGUAGE plpgsql AS $$ BEGIN RETURN a + b; END; $$;');
// Intentionally malformed SQL to demonstrate error handling
// processSqlQuery('SELECT * FROM users WHERE;');