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:<p>1. Each CREATE statement needs to be in topological order, so pg-nano's dev command can execute them without issue.<p>2. pg-nano has a plugin system like Vite that allows SQL generation based on the parsed schema.<p>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.<p>So why is this project worth sharing with you?<p>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.<p>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 <i>generate</i> the type definitions. Genius, right? Okay... maybe not <i>genius</i>, but still cool, I think.<p>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 <i>translate</i> them into TypeScript definitions. (I'm sure <i>you</i> could have done it, yes yes. But did you? No siree bob)<p>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>P.S. The build for Windows is broken, so if anyone could lend a hand, you would be a true hero.<p>[1]: <a href="https://github.com/pg-nano/pg-nano">https://github.com/pg-nano/pg-nano</a> (not ready for production use)
Hey, this is really cool.<p>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.<p>Seems like this tool could essentially pre-create the types for any raw Postgres SQL statement?<p>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 <i>and</i> get query types "for free" would be amazing.
This is interesting. You seem to provide extra functionality besides the typescript types over <i>libpg-query</i>, 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.<p>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.
If it needs to be in topological order, how do you handle tables where the DDL includes mutually referencing columns (column A1 references B, column B1 references A).
Books have been written about SQL Injection. But in the end, SQL Injection just means that you get a different AST than what was intended.<p>So the simplest, stupid check for injection is to parse the query and see if multiple STMT's are found where only one was intended.<p>Better checks can easily be imagined.
offtopic: I tried writing some TypeScript, but ran into problems using Jest to test the code. I wanted to write in "up to date" ES6 but I'm not very experienced and wasn't sure which docs or examples to follow.<p>Should typescript code be written as .mts files, with "type = module" in package.json?<p>What test layout works best? (i.e. __tests__ in project root? filename.test.mts in the same directory as code?)<p>Are there any good examples of jest.config.mts (mjs?) and tsconfig.json?<p>Is the typescript compiler supposed to build the test files too? Is it correct to have a ./build/ in your project root, with all built files including tests under that? Do you then strip out the tests when deploying?<p>This would be targeting an AWS Lambda environment or similar, not browser based so no bundling, is that correct?
This is extremely cool. Well done! If I was able to use this with node-postgres without schema management, but views and plpgsql, I'd finally have what I always wanted for pg <-> ts.<p>I think this library is going to set a new standard for db integration!
libpg_query is a good library!<p><i>"C library for accessing the PostgreSQL parser outside of the server environment"</i><p><a href="https://github.com/pganalyze/libpg_query?tab=readme-ov-file#resources">https://github.com/pganalyze/libpg_query?tab=readme-ov-file#...</a><p><pre><code> pg_query wrappers in other languages:
- Ruby: pg_query
- Go: pg_query_go
- Javascript (Node): pgsql-parser
- Javascript (Browser): pg-query-emscripten
- Python: psqlparse, pglast, psqlparse2
- OCaml: pg_query-ocaml
- Rust: pg_query.rs</code></pre>
libpg-query-node already supports AST types via `@pgsql/types` which is generated by `pg-proto-parser` (using the protobuf file in libpg_query):
<a href="https://github.com/launchql/pg-proto-parser">https://github.com/launchql/pg-proto-parser</a><p>(v15 exports these, I think they still need to be added to v16).<p>I've worked with the maintainers of libpg-query-node and they are very friendly and open to improvements. My suggestion would be to work with them to upstream useful changes vs. forking.
question: we are using kysely.dev as postgresql query builder and porsager's postgres.js for high performance.. is this something that can complement our stack or something to replace it entirely?
Well the question you saw coming (hopefully) - how does it compare to Prisma use cases?<p>One thing I really like about Prisma is only updating my schema and having migrations generated as the "diff".