Postgres SQL AST Parser

12.0.2 · active · verified Tue Apr 21

pgsql-ast-parser is a JavaScript and TypeScript library designed for parsing PostgreSQL SQL syntax into a typed Abstract Syntax Tree (AST), and then facilitating its modification or conversion back to SQL. It is currently at version 12.0.2 and appears to have an active release cadence, with major versions indicating significant structural changes to the AST. Key differentiators include its ability to run in both Node.js and browser environments, its robust TypeScript typing which is strongly recommended for usage, and its foundational role as the underlying parser for `pg-mem`, an in-memory PostgreSQL database emulator. While it covers most common PostgreSQL syntaxes, it explicitly states it does not support PL/pgSQL or some obscure syntaxes, requiring users to test specific complex queries.

Common errors

```
Syntax Error: Unexpected token at position X
```
cause The input SQL string contains syntax that the parser does not understand or is malformed.

fix Review the SQL query for typos or unsupported features (like PL/pgSQL). Simplify the query or test specific parts to isolate the problematic syntax. If it's standard PostgreSQL syntax, consider opening a bug report.
```
Property 'X' does not exist on type 'Y'
```
cause This TypeScript error occurs when attempting to access a property on an AST node that is either not present on that specific node type or has changed in a breaking library version.

fix Consult the library's TypeScript definitions and AST documentation for the specific `pgsql-ast-parser` version you are using. Ensure your code aligns with the current AST structure, especially after major version upgrades.
```
require is not defined
```
cause You are attempting to use CommonJS `require()` to import `pgsql-ast-parser` in an ES module (ESM) environment (e.g., in a file where `type: "module"` is set in `package.json` or a `.mjs` file).

fix Change your import statements from `const pkg = require('pgsql-ast-parser');` to `import * as pkg from 'pgsql-ast-parser';` or `import { parse } from 'pgsql-ast-parser';`.

Warnings

breaking In version 9.0.0, the AST interface for `ALTER TABLE` statements changed. The `change` property was renamed to `changes` and is now an array of `TableAlteration[]`.
Fix: Update code that accesses `ALTER TABLE` AST nodes to use the `changes` array property instead of `change`.
breaking Version 8.0.0 introduced a significant breaking change in how `INSERT` statements are parsed. The `InsertStatement` now has a single `insert` property, unifying the parsing logic for `INSERT ... VALUES` and `INSERT ... SELECT`.
Fix: Refactor code that inspects `INSERT` statement ASTs to account for the new unified `insert` property, rather than separate `values` or `select` properties.
gotcha The parser does not (yet) support PL/pgSQL or some advanced/funky PostgreSQL syntaxes. Attempting to parse such SQL might result in parsing errors.
Fix: Avoid parsing PL/pgSQL blocks directly. For complex or unusual SQL, test parsing capabilities thoroughly and consider simplifying the input SQL or contributing support for missing syntax.
gotcha While usable in JavaScript, the library strongly recommends using TypeScript due to the complexity and depth of the generated Abstract Syntax Tree (AST) types. Without TypeScript, navigating the AST can be prone to errors.
Fix: Utilize TypeScript in your project to leverage the provided type definitions for the AST, which will greatly improve developer experience and reduce type-related bugs when working with the parsed SQL structures.

Install

npm install pgsql-ast-parser npm
yarn add pgsql-ast-parser yarn
pnpm add pgsql-ast-parser pnpm

Imports

parse, Statement
wrong:
```
const { parse, Statement } = require('pgsql-ast-parser');
```
correct:
```
import { parse, Statement } from 'pgsql-ast-parser';
```
These are the primary function for parsing multiple SQL statements and the core type for AST nodes. The library ships with TypeScript types.
parseFirst
wrong:
```
import parseFirst from 'pgsql-ast-parser';
```
correct:
```
import { parseFirst } from 'pgsql-ast-parser';
```
Use this named import for parsing a single SQL statement; it returns a single Statement object instead of an array.
astVisitor, toSql, astMapper
wrong:
```
const astVisitor = require('pgsql-ast-parser').astVisitor;
```
correct:
```
import { astVisitor, toSql, astMapper } from 'pgsql-ast-parser';
```
These are utility functions for traversing (astVisitor), converting back to SQL (toSql), and modifying (astMapper) the parsed AST. All are named exports.

Quickstart

This example demonstrates how to parse a SQL statement and then use an `astVisitor` to traverse the Abstract Syntax Tree, collecting all referenced table names and counting the number of joins.

import { astVisitor, parseFirst } from 'pgsql-ast-parser';

const tables = new Set<string>();
let joins = 0;

// Create an AST visitor to collect information
const visitor = astVisitor(map => ({
    // Hook into table reference nodes to get table names
    tableRef: t => tables.add(t.name),
    // Hook into join nodes to count joins
    join: t => {
        joins++;
        // Call the default implementation to ensure subtrees are also traversed
        map.super().join(t);
    }
}));

// Parse a single SQL statement into an AST
const sqlStatement = `SELECT o.order_id, c.customer_name FROM orders AS o LEFT JOIN customers AS c ON o.customer_id = c.customer_id WHERE o.order_date > '2023-01-01';`;
const ast = parseFirst(sqlStatement);

// Start traversing the AST with our visitor
visitor.statement(ast);

// Print the collected results
console.log(`SQL: ${sqlStatement}`);
console.log(`Used tables: ${[...tables].join(', ')}`);
console.log(`Number of joins: ${joins}`);

view raw JSON →