4 ms·

Show HN: Pg_yregress, Structured Testing for Postgres

56 points by yrashk 3y ago

Originally inspired by pg_regress, pg_yregress provides a TAP-compatible test executor that allows for better test organization, easier instance management, native JSON handling and so on.

weijun95 3y ago
Look like an interesting approach to handling YAML.
vira28 3y ago
As much as I don’t prefer YAML, this is a very good use case for it.
rohan_puri 3y ago
Looks interesting Yurii!
vkaku 3y ago
This is great. Good to see standardized testing improvements to the pg_regress add-on. YAML does better than JSON when it comes to readability. To an extent, TOML as well.
- smt88 3y ago
  I couldn't disagree more about YAML. Semantic whitespace is unreadable, as are all the optional language elements. I've been using YAML for years and still can't read it quickly. I find myself asking, "Wait is that a string or boolean? Is it an object or siblings in an array?" JSON is unambiguous and whitespace agnostic. There's only one way to do things. It's far more readable.
  - vkaku 3y ago
    Try adding anything with quotes and multiple lines, and you'll quickly realize that YAML works better than JSON here. JSON as-is has fewer types and some things are better realized with the JSONNET way than stock.
    - smt88 3y ago
      If I need quotes and multiple lines in a JSON file, it's probably something that shouldn't be a config file. Either way, JSON5 has been around for years and is a logical update because it brings parity with ECMAScript. https://mit.cs.uchicago.edu/haohsiang/topics-auction/tree/025dfe8adc7d42aa685641678bc8f0d0df8c687d/node_modules/json5#:~:text=The%20JSON5%20Data%20Interchange%20Format,JSON5%20parsing%20and%20serialization%20libraries https://mit.cs.uchicago.edu/haohsiang/topics-auction/tree/02....
  - lamontcg 3y ago
    UCL: https://github.com/vstakhov/libucl https://github.com/vstakhov/libucl
    - smt88 3y ago
      YAML files can be written in JSON format because it is a superset of JSON, which sounds as insane as it is.
  - hitchstory 3y ago
    It's readable, it just desperately needs type safety: https://github.com/crdoconnor/strictyaml https://github.com/crdoconnor/strictyaml When I read "gbq enabled: yes" I don't worry about whether it's a string or a boolean when reading it, I worry about whether it's a string or a boolean when I want to change it and it gets parsed again.
Deipayan 3y ago
I am fairly newbie to the Postgres ecosystem. Explain me like I am 5 how this is better than TAP - what pains precisely this addresses
- yrashk 3y ago
  This is not "better than TAP" but rather better than implementing tests as captures of psql outputs as done in pg_regress. Besides that, there are other features that are harder or impossible to test in pg_regress, like binary encodings of types. Plus, it provides TAP output for test runners/printers that support it.
rxrog 3y ago
This is such an interesting project. Having written SQL based tests with MTR, I really love the reusable queries and not having to dump the full SQL output into test results.
mdaniel 3y ago
Fun fact: IJ has "embedded language" support, meaning it understands that some parts of yaml (and any other source code) are another source code and thus offers both syntax highlighting and its usual bunch of introspections. That may not be as meaningful for test SQL, since it probably doesn't have a ton of existing schema against which it should check, but it undoubtedly is helpful when IJ is told this is the PostgreSQL dialect of SQL and thus is able to offer relevant completion and quick-doc for the functions One can tell IJ what's going on in one of two ways: comment markup or creating a JSON Schema for the yaml and placing x-intellij-language-injection metadata into the relevant elements tests: - #language=SQL query: select 1 as result results: - result: 1 or $id: pg_yregress $schema: "http://json-schema.org/draft-07/schema#" properties: tests: type: array items: $ref: '#/definitions/Test' definitions: Test: type: object properties: query: description: the test query to run type: string # https://github.com/JetBrains/intellij-community/blob/idea/233.9802.14/json/src/jsonSchema/schema.json#L52 x-intellij-language-injection: SQL results: type: array item: type: object additionalProperties: true
- yrashk 3y ago
  This is incredibly useful and it'd be great to add this helper to pg_yregress to improve the experience for IntelliJ users!
pilotdeveloper 3y ago
How does this compares to pgTAP? I've been using pgTAP for a long time, how is this better?
- yrashk 3y ago
  1. pgTAP is an extension, pg_yregress is not (although there are plans to make it available as such as well) 2. Allows for organizing and structuring nested tests (providing organizational clarity) 3. Binary encoding testing (useful for new data types) 4. pg_yregress manages Postgres instances and their configuration pgTAP has functionality that is not yet part of pg_yregress (vast library of helpers) Ultimately, I believe both of these tools can coexist rather nicely. Would be great to integrate neatly.

56 points by yrashk 3y ago

Originally inspired by pg_regress, pg_yregress provides a TAP-compatible test executor that allows for better test organization, easier instance management, native JSON handling and so on.

weijun95 3y ago

Look like an interesting approach to handling YAML.

vira28 3y ago

As much as I don’t prefer YAML, this is a very good use case for it.

rohan_puri 3y ago

Looks interesting Yurii!

vkaku 3y ago

This is great. Good to see standardized testing improvements to the pg_regress add-on. YAML does better than JSON when it comes to readability. To an extent, TOML as well.

smt88 3y ago
I couldn't disagree more about YAML. Semantic whitespace is unreadable, as are all the optional language elements. I've been using YAML for years and still can't read it quickly. I find myself asking, "Wait is that a string or boolean? Is it an object or siblings in an array?" JSON is unambiguous and whitespace agnostic. There's only one way to do things. It's far more readable.
- vkaku 3y ago
  Try adding anything with quotes and multiple lines, and you'll quickly realize that YAML works better than JSON here. JSON as-is has fewer types and some things are better realized with the JSONNET way than stock.
  - smt88 3y ago
    If I need quotes and multiple lines in a JSON file, it's probably something that shouldn't be a config file. Either way, JSON5 has been around for years and is a logical update because it brings parity with ECMAScript. https://mit.cs.uchicago.edu/haohsiang/topics-auction/tree/025dfe8adc7d42aa685641678bc8f0d0df8c687d/node_modules/json5#:~:text=The%20JSON5%20Data%20Interchange%20Format,JSON5%20parsing%20and%20serialization%20libraries https://mit.cs.uchicago.edu/haohsiang/topics-auction/tree/02....
- lamontcg 3y ago
  UCL: https://github.com/vstakhov/libucl https://github.com/vstakhov/libucl
  - smt88 3y ago
    YAML files can be written in JSON format because it is a superset of JSON, which sounds as insane as it is.
- hitchstory 3y ago
  It's readable, it just desperately needs type safety: https://github.com/crdoconnor/strictyaml https://github.com/crdoconnor/strictyaml When I read "gbq enabled: yes" I don't worry about whether it's a string or a boolean when reading it, I worry about whether it's a string or a boolean when I want to change it and it gets parsed again.

Deipayan 3y ago

I am fairly newbie to the Postgres ecosystem. Explain me like I am 5 how this is better than TAP - what pains precisely this addresses

yrashk 3y ago
This is not "better than TAP" but rather better than implementing tests as captures of psql outputs as done in pg_regress. Besides that, there are other features that are harder or impossible to test in pg_regress, like binary encodings of types. Plus, it provides TAP output for test runners/printers that support it.

rxrog 3y ago

This is such an interesting project. Having written SQL based tests with MTR, I really love the reusable queries and not having to dump the full SQL output into test results.

mdaniel 3y ago

Fun fact: IJ has "embedded language" support, meaning it understands that some parts of yaml (and any other source code) are another source code and thus offers both syntax highlighting and its usual bunch of introspections. That may not be as meaningful for test SQL, since it probably doesn't have a ton of existing schema against which it should check, but it undoubtedly is helpful when IJ is told this is the PostgreSQL dialect of SQL and thus is able to offer relevant completion and quick-doc for the functions One can tell IJ what's going on in one of two ways: comment markup or creating a JSON Schema for the yaml and placing x-intellij-language-injection metadata into the relevant elements tests: - #language=SQL query: select 1 as result results: - result: 1 or $id: pg_yregress $schema: "http://json-schema.org/draft-07/schema#" properties: tests: type: array items: $ref: '#/definitions/Test' definitions: Test: type: object properties: query: description: the test query to run type: string # https://github.com/JetBrains/intellij-community/blob/idea/233.9802.14/json/src/jsonSchema/schema.json#L52 x-intellij-language-injection: SQL results: type: array item: type: object additionalProperties: true

yrashk 3y ago
This is incredibly useful and it'd be great to add this helper to pg_yregress to improve the experience for IntelliJ users!

pilotdeveloper 3y ago

How does this compares to pgTAP? I've been using pgTAP for a long time, how is this better?

yrashk 3y ago
1. pgTAP is an extension, pg_yregress is not (although there are plans to make it available as such as well) 2. Allows for organizing and structuring nested tests (providing organizational clarity) 3. Binary encoding testing (useful for new data types) 4. pg_yregress manages Postgres instances and their configuration pgTAP has functionality that is not yet part of pg_yregress (vast library of helpers) Ultimately, I believe both of these tools can coexist rather nicely. Would be great to integrate neatly.