pgsql-parser

The project uses a specific naming pattern for test fixtures to enable isolation and performance. Multi-statement SQL files are split into individual line-by-line test cases.

Pattern: __tests__/kitchen-sink/<version-range>/<folder>/<statement-index>.sql

Example: __tests__/kitchen-sink/16-17/pretty/misc-6.sql

• Folder (pretty/): Indicates the test fixture directory (e.g., pretty-printing).
• Statement Index (misc-6.sql): Indicates this is the 6th SQL statement from the original source file.

Locating Original Source: If you need to see the original multi-statement file, look in the __fixtures__ directory. For the example above, the original file would be located at: __fixtures__/kitchen-sink/pretty/misc.sql

The parse() method in @pgsql/parser is asynchronous and returns a Promise. You must use await or .then() when calling it.

Consequences of forgetting await:

• The transformer receives an empty object {} instead of an AST structure.
• Visitor pattern methods (like FuncCall) will never be invoked.
• Transformations will appear broken or fail to apply.

// ✅ CORRECT - returns actual AST structure  
const { Parser } = require('@pgsql/parser');
const parser = new Parser({version: 13});
const result = await parser.parse(sql); // With await

It is important to choose the correct package based on your requirements:

• plpgsql-deparser: A body-only deparser. It converts the internal PL/pgSQL function body (the BEGIN...END part) back to a string. It does not handle the outer CREATE FUNCTION statement.
• plpgsql-parser: A full SQL + PL/pgSQL deparsing pipeline. Use this if you need to deparse complete CREATE FUNCTION statements. It handles the outer statement, hydrates embedded SQL expressions, and manages RETURN statements based on the function's return type.

The generated schema consists of two primary interfaces used to describe the AST structure:

NodeSpec

Represents an AST node type.

• name: The name of the node (e.g., "CreateStmt").
• wrapped: A boolean indicating if the type is listed in Node.oneof.
• fields: An array of FieldSpec objects.

FieldSpec

Represents a field within a node.

• name: The field name.
• type: The field type (e.g., "RangeVar", "string", or "Node").
• isArray: A boolean indicating if the field is repeated.
• optional: A boolean indicating if the field is optional.

Note: To detect if a field references a specific AST node, check if the type matches a specific node type found in the schema rather than the generic "Node" type.

Standard PostgreSQL AST types (like those in @pgsql/types) often use a generic Node type for fields, which can represent any of ~250 different node types. This makes it difficult to know which types to actually handle during AST traversal.

pgsql-types solves this by providing narrowed Node unions. These narrowed types are inferred by parsing thousands of SQL statements from PostgreSQL's regression test suite and tracking which node types actually appear in specific fields. This provides developers with a much smaller, actionable set of types to handle for any given field.

The deparse function supports the following input structures:

1. ParseResult from libpg-query: A complete object containing a version and an array of stmts (where stmts is an array of RawStmt objects).
2. Wrapped ParseResult Node: A node where the ParseResult is a key (e.g., { ParseResult: { ... } }).
3. Wrapped RawStmt Node: A node where a RawStmt is a key (e.g., { RawStmt: { stmt: {...}, stmt_len: 32 } }).
4. Array of Nodes: An array of individual statement nodes (e.g., [{ SelectStmt: {...} }, { InsertStmt: {...} }]).
5. Single Statement Node: A single statement node directly (e.g., { SelectStmt: {...} }).

The project supports multiple PostgreSQL versions (13-17) by using version-specific configurations. Each PostgreSQL version is mapped to:

• Version numbers for each package (e.g., pgsql-parser, pgsql-deparser, @pgsql/types).
• npm tags for publishing (e.g., pg13, pg14, pg15, pg16, pg17).
• libpg-query dependency versions.

All mappings are managed in config/versions.json. To update these versions globally, run pnpm bump-versions from the root.

{
  "versions": {
    "13": {
      "libpg-query": "13.5.7",
      "pgsql-parser": "13.18.0", 
      "pgsql-deparser": "13.17.0",
      "@pgsql/types": "13.11.1",
      "npmTag": "pg13"
    }
  }
}

The project is organized as a monorepo using Lerna. The core functionality is divided into the following packages:

• packages/deparser: SQL deparser implementation
• packages/parser: SQL parser implementation
• packages/types: TypeScript type definitions
• packages/utils: Utility functions

Note: Because packages depend on each other, changes made to shared packages like types or utils may require you to rebuild the dependent packages to reflect the changes.

You can generate different types of utility functions for runtime enum conversions via the utils.enums configuration:

1. Bidirectional (Default): Generates a getEnumValue function that converts between string and number.
2. Unidirectional (Switch Statements): Generates separate functions for string-to-number (getEnumInt) and number-to-string (getEnumString).
3. Unidirectional (Nested Objects): Generates a nested object structure where each enum type has its own conversion function (e.g., enumToIntMap.OverridingKind("VALUE")).

// Example: Unidirectional functions with switch statements
const parser = new PgProtoParser(inFile, {
  outDir,
  utils: {
    enums: {
      enabled: true,
      unidirectional: true,
      toIntFilename: 'enum-to-int.ts',
      toStringFilename: 'enum-to-string.ts'
    }
  }
});

When verifying the correctness of the deparser or pretty-formatters, do not rely on exact SQL string equality. Instead, use an AST-level round-trip validation. This ensures that while the formatting (whitespace, casing, etc.) might change, the underlying SQL semantics remain identical.

The validation pipeline:

1. parse(sql1) $\rightarrow$ ast
2. deparse(ast) $\rightarrow$ sql2
3. Verify that parse(sql2) produces an AST equal to the original ast.

Use the expectAstMatch utility (located in packages/deparser/test-utils/index.ts) to perform this comparison.

The @pgsql/utils library provides a nodes namespace that contains helper methods for constructing complex PostgreSQL Abstract Syntax Tree (AST) nodes. These helpers are designed to work seamlessly with @pgsql/types and can be used to programmatically build SQL queries that can later be converted back to SQL strings using a deparser (like pgsql-deparser).

import * as t from '@pgsql/utils';
// Use t.nodes.<nodeType>({ ... }) to build your AST