Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: Parse your Postgres queries into a fully-typed AST in TypeScript

github.com

Post reply on HN
Print view View original

1–10 of 22 posts

Show HN: Parse your Postgres queries into a fully-typed AST in TypeScript

#1

Post by aleclarsoniv »

Hey all, I'm the creator of @pg-nano/pg-parser. I'm using it in pg-nano[1] to statically analyze Postgres schemas spread across multiple SQL files for a couple of reasons:

1. Each CREATE statement needs to be in topological order, so pg-nano's dev command can execute them without issue.

2. pg-nano has a plugin system like Vite that allows SQL generation based on the parsed schema.

Probably to the surprise of no one, working with an untyped AST feels like you're back in the days of JavaScript, because well... you are. Most of you know by now just how great TypeScript and static types in general are, especially if you appreciate SQL.

So why is this project worth sharing with you?

Well, writing the AST type definitions by hand would have taken me way too much time. It would also be a bear to keep up-to-date as Postgres continues to evolve.

To my surprise, I discovered that libpg_query, the C library used under-the-hood, includes JSON definitions in their /srcdata/ folder. I figured I could use them to generate the type definitions. Genius, right? Okay... maybe not genius, but still cool, I think.

You see, those JSON definitions provided by libpg_query? They don't exactly contain the TypeScript definitions (was that obvious?). No, no. I had to translate them into TypeScript definitions. (I'm sure you could have done it, yes yes. But did you? No siree bob)

It was pain-staking, but overall really not too hard. Time-consuming? Yes, but not as much as writing the type definitions by hand. So... was it worth it? Only time will tell. I hope you find it as useful as I do. And that's all I've got, so thanks for reading.

P.S. The build for Windows is broken, so if anyone could lend a hand, you would be a true hero.

[1]: https://github.com/pg-nano/pg-nano (not ready for production use)

Show HN: Parse your Postgres queries into a fully-typed AST in TypeScript
github.com

Re: Show HN: Parse your Postgres queries into a fully-typed AST in TypeScript

#2

Post by ardsh »

This is interesting. You seem to provide extra functionality besides the typescript types over libpg-query, like the walk function, right? I assume that's the reason these changes can't be easily merged into the main library and you chose to fork entirely.

As an aside, do you think it's possible to use your libraries to get the return type of an arbitrary postgres query, even if it's dynamic? I have a library that requires users to write the return type of queries manually always, and I'd like to automate that step.

Re: Show HN: Parse your Postgres queries into a fully-typed AST in TypeScript

#3

Post by aleclarsoniv »

ardsh wrote: post #2

This is interesting. You seem to provide extra functionality besides the typescript types over libpg-query , like the walk function, right? I assume that's the reason these changes can't be easily merged into the main library and you chose to fork entirely. As an aside, do you think it's possible to use your libraries to get the return type of an arbitrary postgres query, even if it's dynamic? I have a library that r…

The main reason I didn't contribute my changes via PR is I wanted a package without "deparse" support, which adds considerably to the install size. I also didn't want pre-compiled binaries for every supported platform to be published to NPM, preferring a postinstall script that only downloads the necessary binary. I don't know how the walk function would be received by the original maintainers, as I didn't ask.

> do you think it's possible to use your libraries to get the return type of an arbitrary postgres query, even if it's dynamic?

Yes it is. I've solved that exact problem in pg-nano. I use the `describePrepared` feature of libpq: https://github.com/pg-nano/pg-nano/blob/4cca3dbe6be609c479e4...

Re: Show HN: Parse your Postgres queries into a fully-typed AST in TypeScript

#5

Post by pocketarc »

flockonus wrote: post #4

Well the question you saw coming (hopefully) - how does it compare to Prisma use cases? One thing I really like about Prisma is only updating my schema and having migrations generated as the "diff".

Are those migrations still editable, out of curiosity? Oftentimes I'll want to have migrations add things that simply aren't possible with some ORMs (e.g. generated columns, which can't be schema'd in most ORMs).

The main thing holding me back from Prisma is precisely what you like about it - if the migrations are auto-generated and I can't edit them afterward, I won't be able to do what I need to.

Re: Show HN: Parse your Postgres queries into a fully-typed AST in TypeScript

#6

Post by moltar »

pocketarc wrote: post #5
flockonus wrote: post #4

Well the question you saw coming (hopefully) - how does it compare to Prisma use cases? One thing I really like about Prisma is only updating my schema and having migrations generated as the "diff".

Are those migrations still editable, out of curiosity? Oftentimes I'll want to have migrations add things that simply aren't possible with some ORMs (e.g. generated columns, which can't be schema'd in most ORMs). The main thing holding me back from Prisma is precisely what you like about it - if the migrations are auto-generated and I can't edit them afterward, I won't be able to do what I need to.

You can edit Prisma migrations. They are plain SQL files with no magic.

Re: Show HN: Parse your Postgres queries into a fully-typed AST in TypeScript

#8

Post by hn_throwaway_99 »

Hey, this is really cool.

Suggestion: check out the Slonik library for Postgres. It encourages writing raw SQL using string template literals (i.e. sql`select foo from bar where zed = ${someParam}`). It also supports strong typing of results, but the programmer needs to create their own Zod validators manually to check this and get strong typing support.

Seems like this tool could essentially pre-create the types for any raw Postgres SQL statement?

I think this approach of using raw SQL is much better than a custom query builder like kysely, where half the time I'm just trying to translate the SQL that I know in my head to some custom, library-specific API. But the benefit of using kysely is the automatic typing support. Being able to use raw SQL and get query types "for free" would be amazing.

Re: Show HN: Parse your Postgres queries into a fully-typed AST in TypeScript

#9

Post by notduncansmith »

moltar wrote: post #6
pocketarc wrote: post #5

Earlier quoted context omitted.

Are those migrations still editable, out of curiosity? Oftentimes I'll want to have migrations add things that simply aren't possible with some ORMs (e.g. generated columns, which can't be schema'd in most ORMs). The main thing holding me back from Prisma is precisely what you like about it - if the migrations are auto-generated and I can't edit them afterward, I won't be able to do what I need to.

You can edit Prisma migrations. They are plain SQL files with no magic.

+1 have had to edit Prisma files after generating a migration, it went fine. As ‘moltar says, no magic.
Post reply on HN