Use static query analysis for type generation

[danielrearden]

I wrote a small library for generating TypeScript types from Postgres queries using only static query analysis. There's a number of advantages of using static query analysis over the approach currently used by @slonik/typegen including:

• No dependency on psql
• Read-only access to the database
• Accurate nullability with more complex queries that include CTEs, subqueries, etc.
• Accurate nullability when using functions like coalesce or nullif
• Parsing constant values into TypeScript literal types

There's also some pretty cool things we could do in the future, for example, providing very accurate types for JSON objects built using json_build_object or narrowing the types based on WHERE conditions.

There are, of course, tradeoffs as well. Namely, because static analysis requires us to effectively duplicate logic that's already handled by Postgres, there's a risk of human error and the possibility that we haven't covered every possible expression. However, I think the flexibility and simplicity of the approach outweigh the risks.

I actually wrote pg-magic with the hope that it could be used with @slonik-typegen 😅 Let me know whether you think it would be worthwhile to move in this direction!

[mmkal]

Hi @danielrearden, this sounds very cool! Yeah, slonik-typegen is slowly moving in that direction already. It started by blindly accepting the pgdesc results from psql but over time it's had to do more and more static query analysis, mostly to figure out nulls. But null detection is far from perfect, and I've tried and failed a couple of times to improve it. Specifically, I want it it to be able to trace the "source" table for CTEs and views (and maybe even functions). Can pg-magic do that? I've also thought a little about the JSON idea you're describing so all of this sounds great. A couple of questions:

1. What about enums? Do you do something similar to slonik-typegen where you build a big map of enums in order to turn them into literal unions?
2. Would it be possible to adopt incrementally? i.e. keep using psql and use pg-magic to replace the static analysis that happens here? I do like the idea but I'm low on time right now and my team are already using slonik-typegen and it's working pretty well (we are having to take some risks with non-null assertions in some places, so would definitely appreciate an improvement, but it's not painful enough to justify a big time commitment in the next month or two. That could change in spring, though).

[mmkal]

Also curious if it could help with #315