Dynamic queries

pgTyped doesn't support query composition or concatenation, but this doesn't mean you can't create dynamic queries. Instead of providing non-typesafe query composition, pgTyped forces you to move the dynamic logic into the SQL layer.

Dynamic WHERE filters

A frequently used pattern is a query with an optional filter that selects all rows by default. This can be achieved using a IS NULL construct. Here is an example of a query with optional age and name filters:

/* @name GetUsers */

SELECT * FROM users

WHERE (:name :: TEXT IS NULL OR name = :name)

AND (:age_gt :: INTEGER IS NULL OR age > :age_gt);

Dynamic ORDER BY sorting

Sorting by a dynamic column is another widely used dynamic query:

/* @name GetAllComments */

SELECT * FROM book_comments

WHERE id = :id ORDER BY :order_column;

Next, if we want to include a dynamic sort order as well:

/* @name GetAllUsers */

SELECT * FROM users

ORDER BY (CASE WHEN :asc = true THEN :sort_column END) ASC, :sort_column DESC;

Advanced dynamic queries

More complicated dynamic queries can be built similarly to the above two.
Note that highly dynamic SQL queries can lead to worse DB execution times, so sometimes it is worth to split a complex query into multiple independent ones.