pgsql-deparser

pgsql-deparser

pgsql-deparser is a streamlined tool designed to convert PostgreSQL Abstract Syntax Trees (AST) back into SQL queries. It is a companion module to pgsql-parser, which is capable of both parsing SQL queries into ASTs and deparsing these ASTs back into SQL. However, unlike pgsql-parser, which incorporates the full PostgreSQL parser, pgsql-deparser focuses solely on the deparser functionality. This makes it an excellent choice for scenarios where only AST-to-SQL conversion is needed, avoiding the extra overhead associated with the full parsing capabilities.

Installation

npm install pgsql-deparser

Key Features

• Lightweight and Pure TypeScript: Built entirely in TypeScript, pgsql-deparser is lightweight and doesn't rely on native dependencies, facilitating easier integration and deployment across various environments.
• Focused Functionality: By concentrating on decompiling ASTs to SQL, pgsql-deparser offers a specialized, efficient solution for those who need to generate SQL statements from ASTs without the full parsing mechanism.
• Compatibility: Ideal for use cases where the AST is already obtained from another source or process, allowing for seamless SQL generation from AST representations.

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

Why Use pgsql-deparser?

pgsql-deparser is particularly useful in development environments where native dependencies are problematic or in applications where only the deparser functionality is required. Its independence from the full pgsql-parser package allows for more focused and lightweight SQL generation tasks.

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 Version	libpg_query	Status	npm
13	13-latest	Active development	latest
12	(n/a)	Not supported
11	(n/a)	Not supported
10	10-latest	Not supported	@1.3.1 (tree)

Related

• 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-enums: A utility package offering easy access to PostgreSQL enumeration types in JSON format, aiding in string and integer conversions of enums used within ASTs to compliment pgsql-parser
• @pgsql/enums: Provides PostgreSQL AST enums in TypeScript, enhancing type safety and usability in projects interacting with PostgreSQL AST nodes.
• @pgsql/types: Offers TypeScript type definitions for PostgreSQL AST nodes, facilitating type-safe construction, analysis, and manipulation of ASTs.
• @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.
• 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.

Thanks @lfittl for building the core libpg_query suite:

• libpg_query
• pg_query
• pg_query.go

Credits

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

• pg-query-parser
• pg-query-native
• Original LICENSE

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.