PGSQL Parser Testing

Name: pgsql-parser-testing
Rating: 72
Author: constructive-io

Testing workflow for the pgsql-parser repository. This skill is scoped specifically to the constructive-io/pgsql-parser monorepo.

When to Apply

Use this skill when:

•Working in the pgsql-parser repository
•Fixing deparser or parser issues
•Running parser/deparser tests
•Validating SQL round-trip correctness
•Adding new SQL syntax support

Repository Structure

code

pgsql-parser/
  packages/
    parser/           # SQL parser (libpg_query bindings)
    deparser/         # SQL deparser (AST to SQL)
    plpgsql-parser/   # PL/pgSQL parser
    plpgsql-deparser/ # PL/pgSQL deparser
    types/            # TypeScript type definitions
    utils/            # Utility functions
    traverse/         # AST traversal utilities
    transform/        # AST transformation utilities

Testing Strategy

The pgsql-parser uses AST-level equality for correctness, not string equality:

code

parse(sql1) → ast1 → deparse(ast1) → sql2 → parse(sql2) → ast2

While sql2 !== sql1 textually, a correct round-trip means ast1 === ast2.

Key Principle

Exact SQL string equality is not required. The focus is on comparing resulting ASTs. Use expectAstMatch (deparser) or expectPGParse (ast package) to validate correctness.

Development Workflow

Initial Setup

bash

pnpm install
pnpm build

Running Tests

Run all tests:

bash

pnpm test

Run tests for a specific package:

bash

cd packages/deparser
pnpm test

Watch mode for rapid iteration:

bash

cd packages/deparser
pnpm test:watch

Run a specific test:

bash

pnpm test --testNamePattern="specific-test-name"

Fixing Deparser Issues

Systematic Approach

•
One test at a time: Focus on individual failing tests
bash
```
pnpm test --testNamePattern="specific-test"
```
•
Always check for regressions: After each fix, run full test suite
bash
```
pnpm test
```
•
Build before testing: Always rebuild after code changes
bash
```
pnpm build && pnpm test
```

•

Clean commits: Stage files explicitly

bash

git add packages/deparser/src/specific-file.ts

Workflow Loop

code

Make changes → pnpm build → pnpm test --testNamePattern="target" → pnpm test (full) → commit

Test Utilities

Deparser Tests

Location: packages/deparser/test-utils/index.ts

typescript

import { expectAstMatch } from '../test-utils';

it('deparses SELECT correctly', () => {
  expectAstMatch('SELECT * FROM users');
});

AST Package Tests

Location: packages/ast/test/utils/index.ts

Uses database deparser for validation:

typescript

import { expectPGParse } from '../test/utils';

it('round-trips through database deparser', async () => {
  await expectPGParse('SELECT * FROM users WHERE id = 1');
});

Note: AST tests require the database to have deparser.expressions_array function available.

Common Commands

Command	Description
`pnpm build`	Build all packages
`pnpm test`	Run all tests
`pnpm test:watch`	Run tests in watch mode
`pnpm lint`	Run linter
`pnpm clean`	Clean build artifacts

Package-Specific Testing

Parser Package

Tests libpg_query bindings and SQL parsing:

bash

cd packages/parser
pnpm test

Deparser Package

Tests AST-to-SQL conversion:

bash

cd packages/deparser
pnpm test

PL/pgSQL Packages

Tests PL/pgSQL parsing and deparsing:

bash

cd packages/plpgsql-parser
pnpm test

cd packages/plpgsql-deparser
pnpm test

Debugging Tips

•
Use isolated debug scripts for complex issues (don't commit them)

•

Check the AST structure when tests fail:

typescript

import { parse } from 'pgsql-parser';
console.log(JSON.stringify(parse('SELECT 1'), null, 2));

•

Compare ASTs visually to understand differences:

typescript

const ast1 = parse(sql1);
const ast2 = parse(deparse(ast1));
console.log('Original:', JSON.stringify(ast1, null, 2));
console.log('Round-trip:', JSON.stringify(ast2, null, 2));

Troubleshooting

Issue	Solution
Tests fail after changes	Run `pnpm build` before `pnpm test`
Type errors	Check `packages/types` for type definitions
Shared code changes	Rebuild dependent packages
Snapshot mismatches	Review changes, update with `pnpm test -u` if correct

Important Notes

•Changes to types or utils packages may require rebuilding dependent packages
•Each package can be developed and tested independently
•The project uses Lerna for monorepo management
•Always verify no regressions before committing

References

•Deparser testing docs: packages/deparser/TESTING.md
•Quoting rules: packages/deparser/QUOTING-RULES.md
•Deparser usage: packages/deparser/DEPARSER_USAGE.md
•PL/pgSQL deparser: packages/plpgsql-deparser/AGENTS.md