Home

Awesome

pgsql-parser

<p align="center" width="100%"> <img height="120" src="https://github.com/launchql/pgsql-parser/assets/545047/6440fa7d-918b-4a3b-8d1b-755d85de8bea" /> </p> <p align="center" width="100%"> <a href="https://github.com/launchql/pgsql-parser/actions/workflows/run-tests.yaml"> <img height="20" src="https://github.com/launchql/pgsql-parser/actions/workflows/run-tests.yaml/badge.svg" /> </a> <a href="https://www.npmjs.com/package/pgsql-parser"><img height="20" src="https://img.shields.io/npm/dt/pgsql-parser"></a> <a href="https://www.npmjs.com/package/pgsql-parser"><img height="20" src="https://img.shields.io/npm/dw/pgsql-parser"/></a> <a href="https://github.com/launchql/pgsql-parser/blob/main/LICENSE-MIT"><img height="20" src="https://img.shields.io/badge/license-MIT-blue.svg"/></a> <a href="https://www.npmjs.com/package/pgsql-parser"><img height="20" src="https://img.shields.io/github/package-json/v/launchql/pgsql-parser?filename=packages%2Fparser%2Fpackage.json"/></a> </p>

The real PostgreSQL parser for Node.js, pgsql-parser provides symmetric parsing and deparsing of SQL statements using the actual PostgreSQL parser. It allows you to parse SQL queries into AST and modify or reconstruct SQL queries from the AST.

Installation

npm install pgsql-parser

Key Features

Parser Example

Rewrite part of a SQL query:

import { parse, deparse } from 'pgsql-parser';

const stmts = parse('SELECT * FROM test_table');

// Assuming the structure of stmts is known and matches the expected type
stmts[0].RawStmt.stmt.SelectStmt.fromClause[0].RangeVar.relname = 'another_table';

console.log(deparse(stmts));

// SELECT * FROM "another_table"

Deparser Example

The pgsql-deparser module serializes ASTs to SQL in pure TypeScript, avoiding the full parser's native dependencies. It's useful when only SQL string conversion from ASTs is needed, and is written in pure TypeScript for easy cross-environment deployment.

Here's how you can use the deparser in your TypeScript code, using @pgsql/utils to create an AST for deparse:

import ast, { SelectStmt } from '@pgsql/utils';
import { deparse } from 'pgsql-deparser';

// This could have been obtained from any JSON or AST, not necessarily @pgsql/utils
const stmt: SelectStmt = ast.selectStmt({
  targetList: [
    ast.resTarget({
      val: ast.columnRef({
        fields: [ast.aStar()]
      })
    })
  ],
  fromClause: [
    ast.rangeVar({
      relname: 'some_table',
      inh: true,
      relpersistence: 'p'
    })
  ],
  limitOption: 'LIMIT_OPTION_DEFAULT',
  op: 'SETOP_NONE'
});

// Modify the AST if needed
stmt.SelectStmt.fromClause[0].RangeVar.relname = 'another_table';

// Deparse the modified AST back to a SQL string
console.log(deparse(stmts));

// Output: SELECT * FROM another_table

CLI

npm install -g pgsql-parser

usage

pgsql-parser <sqlfile>

Documentation

parser.parse(sql)

Parses the query and returns a parse object.

Parameters

parametertypedescription
queryStringSQL query

Returns an object in the format:

{ query: <query|Object>,
  error: { message: <message|String>,
           fileName: <fileName|String>,
           lineNumber: <line|Number>,
           cursorPosition: <cursor|Number> }

parser.deparse(query)

Deparses the query tree and returns a formatted SQL statement. This function takes as input a query AST in the same format as the query property of on the result of the parse method. This is the primary functionality of this module.

Parameters

parametertypedescription
queryObjectQuery tree obtained from parse

Returns a normalized formatted SQL string.

Versions

As of PG 13, PG majors versions maintained will have a matching dedicated major npm version. Only the latest Postgres stable release receives active updates.

Our latest is built with 13-latest branch from libpg_query

PostgreSQL Major Versionlibpg_queryStatusnpm
1313-latestActive developmentlatest
12(n/a)Not supported
11(n/a)Not supported
1010-latestNot supported@1.3.1 (tree)

Related

Thanks @lfittl for building the core libpg_query suite:

Credits

Thanks to @zhm for the OG parser that started it all:

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.