PgTyped supports parsing queries from SQL files, allowing developers to write DB queries in their favourite database IDE. To help PgTyped generate executable queries from these SQL files, they need to be annotated with special comments.
PgTyped has a number of requirements for SQL file contents:
- Each query must be preceded with an annotation (comment).
- An annotation must specify the query name using the
- Each query must be a single SQL statement that ends with a semicolon.
- Queries can contain parameters. Parameters should start with a colon, ex.
- Annotations can include param expansions if needed using the
Parameter expansions allow the user to pass arrays and objects as query parameters. This allows to build more complicated queries, which would be impossible or would look too big if they used only scalar parameters.
For example, with parameter expansions a typical insert query looks like this:
comments -> ((userId, commentBody)...) is a parameter expansion that instructs pgtyped to expand
comments into an array of objects with each object having a
A query can also contain multiple expansions if needed:
At the moment, PgTyped supports three expansion types:
The array spread expansion allows to pass an array of scalars as parameter.
The object pick expansion allows to pass an object as a parameter.
Array spread and pick
The array spread-and-pick expansion allows to pass an array of objects as a parameter.
We will be adding more annotation tags and expansion types in the future.
If you have an idea for a new expansion type, or a new annotation tag, please submit an issue for that so we can consider it.