Pgsql Parser Testing

PGSQL Parser Testing

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

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:

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

pnpm install
pnpm build

Running Tests

Run all tests:

pnpm test

Run tests for a specific package:

cd packages/deparser
pnpm test

Watch mode for rapid iteration:

cd packages/deparser
pnpm test:watch

Run a specific test:

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

Fixing Deparser Issues

Systematic Approach

1. One test at a time: Focus on individual failing tests

   pnpm test --testNamePattern="specific-test"
   

2. Always check for regressions: After each fix, run full test suite

   pnpm test
   

3. Build before testing: Always rebuild after code changes

   pnpm build && pnpm test
   

4. Clean commits: Stage files explicitly

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

Workflow Loop

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

Test Utilities

Deparser Tests

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

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:

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

Package-Specific Testing

Parser Package

Tests libpg_query bindings and SQL parsing:

cd packages/parser
pnpm test

Deparser Package

Tests AST-to-SQL conversion:

cd packages/deparser
pnpm test

PL/pgSQL Packages

Tests PL/pgSQL parsing and deparsing:

cd packages/plpgsql-parser
pnpm test

cd packages/plpgsql-deparser
pnpm test

Debugging Tips

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

2. Check the AST structure when tests fail:

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

3. Compare ASTs visually to understand differences:

   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

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