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

11–20 of 22 posts

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

#11

Post by aleclarsoniv »

hn_throwaway_99 wrote: post #8

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…

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

It's a syntax parser that produces an AST. So only information explicitly defined in the syntax is available. To infer input/output types for an arbitrary SQL command, you need introspection (the most fool-proof way being PQdescribePrepared[1]).

> Being able to use raw SQL and get query types "for free" would be amazing.

That's basically what pg-nano does, but you need to use Postgres functions, rather than "$1" or "?" placeholder templating. Of course, some people prefer co-locating their raw SQL inside their TypeScript files, in which case, pg-nano is not for them.

[1]: https://www.postgresql.org/docs/current/libpq-exec.html#LIBP...

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

#12

Post by aleclarsoniv »

inquisitor26234 wrote: post #7

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?

You could use both side-by-side if you prefer query builders for certain tasks, but that means you'll be bundling two Postgres drivers in your application server, which could mean hitting the connection limit of your Postgres instance. Although, pg-nano has a way to limit number of parallel connections at the cost of reduced throughput.

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

#13

Post by aleclarsoniv »

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".

I wouldn't compare the two directly, as they serve different preferences. I would say that using Postgres functions is more powerful, but that may not matter for your app, depending on its complexity and needs. Ultimately, I'm not concerned with persuading Prisma users to switch over just yet.

As far as migrations go, pg-nano is taking the same “schema diffing” approach that I assume Prisma does, where the active schema of your Postgres instance is compared to the desired schema (defined via SQL files in pg-nano's case) and a migration plan is generated from there. In the context of migrating a non-local Postgres instance, pg-nano still has some R&D to do.

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

#14

Post by aleclarsoniv »

MarceColl wrote: post #10

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).

That's not supported yet, but it will involve removing the foreign key constraint from one table (by parsing and rewriting the CREATE statement before running it). Then after the referenced table has been created, add the FK constraint with an ALTER statement.

There's also the DEFERRABLE constraint setting[1] for one-to-one relationships (which can usually be avoided via a joint reference table). This pattern should already work in pg-nano.

[1]: https://www.postgresql.org/docs/current/sql-set-constraints....

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

#15

Post by bloopernova »

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.

Should typescript code be written as .mts files, with "type = module" in package.json?

What test layout works best? (i.e. __tests__ in project root? filename.test.mts in the same directory as code?)

Are there any good examples of jest.config.mts (mjs?) and tsconfig.json?

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?

This would be targeting an AWS Lambda environment or similar, not browser based so no bundling, is that correct?

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

#16

Post by G1N »

bloopernova wrote: post #15

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. Should typescript code be written as .mts files, with "type = module" in package.json? What test layout works best? (i.e. __tests__ in project root? filename.test.mts in the same directory as code?) Are there…

Sharing some packages, tsconfig.json, and jest.config.ts I used in a recent project that I'm pretty happy with below (I'm not using `"type": "module"` in my package.json.

Relevant npm packages: - @jest/globals (I use these to import `describe`, `test`, `expect` and other test-related functions) - ts-jest

My tsconfig.json:

  {  
    "buildOptions": {},  
    "compilerOptions": {  
      "target": "ES6",  
      "module": "ESNext",  
      "lib": [  
        "ES6",  
        "DOM",  
        "ES2017",  
        "DOM.Iterable"  
      ],  
      "moduleResolution": "Bundler",  
      "jsx": "react-jsx",  
      "declaration": true,  
      "strict": true,  
      "esModuleInterop": true,  
      "skipLibCheck": true,  
      "forceConsistentCasingInFileNames": true,  
      "outDir": "./dist"  
    },  
    "include": [  
      "src",  
      "__tests__",  
      "*.config.ts",  
      "sandbox.ts",  
      "sandbox-ui.js"  
    ]
  }
and my jest config (jest.config.ts):

  import type { Config } from "jest";

  export default {  
    preset: "ts-jest",  
    testEnvironment: "jsdom",  
    testMatch: ["\*/__tests__/\*/*.test.ts"],  
    globals: {  
      fetch: global.fetch,  
    },  
  } as Config;
I have my tests set up under the `__tests__` directory in project root as you noted, and use a `.test.ts` suffix on all relevant test files. Doing this, ts-jest handles the actual transpilation + execution of the tests (just by running jest), and you don't have to worry about including them in your built solution. I have a separate `tsconfig.build.json` for actually building my project (this... is probably inefficient... but it works well for me :P).

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

#17

Post by bloopernova »

G1N wrote: post #16
bloopernova wrote: post #15

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. Should typescript code be written as .mts files, with "type = module" in package.json? What test layout works best? (i.e. __tests__ in project root? filename.test.mts in the same directory as code?) Are there…

Sharing some packages, tsconfig.json, and jest.config.ts I used in a recent project that I'm pretty happy with below (I'm not using `"type": "module"` in my package.json. Relevant npm packages: - @jest/globals (I use these to import `describe`, `test`, `expect` and other test-related functions) - ts-jest My tsconfig.json: { "buildOptions": {}, "compilerOptions": { "target": "ES6", "module": "ESNext", "lib": [ "ES6",…

You are a fantastic person, thank you for helping this random internet stranger :) This looks to be exactly what I was after!

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

#18

Post by jansommer »

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.

I think this library is going to set a new standard for db integration!

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

#19

Post by gregnr »

libpg-query-node already supports AST types via `@pgsql/types` which is generated by `pg-proto-parser` (using the protobuf file in libpg_query): https://github.com/launchql/pg-proto-parser

(v15 exports these, I think they still need to be added to v16).

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.

Post reply on HN