TypeScript

In my current setup I use pgtyped and ts-sql-query together in production. I think this is a stellar combination. I have previously used zapatos but moved away in favor of current combination. I'll share my motivations below.

Composition of tagged templates (as slonic/zapatos/squid etc. encourage) gets messy very quick esp. as queries become more dynamic. ts-sql-query is amazing for complex dynamic queries - it has great support for things like subselects, upserts, CTEs, complex projections across a breadth of mainstream databases. It especially shines for complex conditional filters where a lot of js orms fall short. Of course it also works very well for general CRUD operations.

With libraries like zapatos single table crud operations are type-safe but for anything involving multi-table joins the library is not able to help much around type-safety and you need to provide result types yourself. If tomorrow after some schema refactoring the type of result changes, you will get a runtime exception. In case of ts-sql-query, complex multiple table operations are also type-safe and even when you perform joins on multiple tables, select some subset of columns etc. in most cases it is able to infer types. There is also a tool ts-sql-codegen (which I maintain) which is able to codegen the types from database so for common use cases the problem of keeping db schema and typescript types in sync is auto-solved for you.

Similarl pgtyped is great when you have some queries which are not very dynamic but lean heavily on postgres specific features, or custom extensions etc. For us these are mostly reporting, analytics queries, full text search queries etc. For these cases it is convenient to interactively try out sql in a notebook, arrive at the right query and then just dump it in a sql file after making it parameterized - this eliminates the need for a manual translation from SQL to typescript API. The pgtyped type generator ensures that the generated typescript API is reasonably well typed.

pgtyped approach becomes unwieldy if you need very dynamic query patterns unless you also lean heavily on stored procedures and pushing more logic into the database. This is something we avoid because frankly we find writing typescript much more enjoyable than pl/pgSQL. Also our services are read-heavy and being able to keep logic on ts side enables us to utilize granular caching better.