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

PostgresJs: PostgreSQL client for Node.js and Deno

github.com

Post reply on HN
Print view View original

11–20 of 148 posts

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#11

Post by seanhunter »

noduerme wrote: post #9

>> Prepared statements will automatically be created for any queries where it can be inferred that the query is static What does this mean in practice? Like, actual prepared statements are created against the DB session if there are no bound variables, even if that query is only made once? If so, it's an interesting but highly opinionated approach...

Postgres supports 2 types of prepared statements - "generic" prepared statements (which can accept parameters) and "custom" which are prepared for a specific execution so bake the parameters in.

https://www.postgresql.org/docs/current/sql-prepare.html explains it. Read the section called "Notes" for the plan types.

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#12

Post by promiseofbeans »

qwertox wrote: post #4

I was wondering how old this project is: v1.0.1 - Jan 2020 v2.0.0 - Jun 2020 but never left beta. v3.0.0 - Mar 2022, which appears to be when the project really got started. I also wonder how solid it is, because it looks very interesting.

I've been using it in production (Deno) for a while. It's pretty solid, but their documentation is sorely lacking, so you often have to feel your way to getting more complex things to work.

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#13

Post by latch »

noduerme wrote: post #9

>> Prepared statements will automatically be created for any queries where it can be inferred that the query is static What does this mean in practice? Like, actual prepared statements are created against the DB session if there are no bound variables, even if that query is only made once? If so, it's an interesting but highly opinionated approach...

If you're using parameterized queries, then you _have_ to use PostgreSQL's "Extended Query" flow, which is what most people would think of as a "prepared statement". This is hardly opinionated.

But normally, you use an unnamed prepared statement and/or portal, which PG will clean up for you, essentially only letting you have one of those per session (what we think of as a connection).

I agree that sentence didn't make any sense. So I looked at the code (1) and what they mean is that they'll use a named prepared statement automatically, essentially caching the prepared statement within PG and the driver itself. They create a signature for the statement. I agree, this is opinionated!

(1) The main place where the parse/describe/bind/execute/sync data is created is, in my opinion, pretty bad code: https://github.com/porsager/postgres/blob/bf082a5c0ffe214924...

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#14

Post by noduerme »

latch wrote: post #13
noduerme wrote: post #9

>> Prepared statements will automatically be created for any queries where it can be inferred that the query is static What does this mean in practice? Like, actual prepared statements are created against the DB session if there are no bound variables, even if that query is only made once? If so, it's an interesting but highly opinionated approach...

If you're using parameterized queries, then you _have_ to use PostgreSQL's "Extended Query" flow, which is what most people would think of as a "prepared statement". This is hardly opinionated. But normally, you use an unnamed prepared statement and/or portal, which PG will clean up for you, essentially only letting you have one of those per session (what we think of as a connection). I agree that sentence didn't mak…

Thanks... I haven't got the mental energy to follow their code ATM but yeah, it seems weird to buffer a static query as a prepared statement if it's only going to be used once.

Maybe that kind of goes with a Nodejs philosophy, though? It seems like an assumption that in most cases a static query will recur... and maybe that's usually accurate with long running persistent connections. I'm much more used to working in PHP and not using persistent connections, and so sparing hitting a DB with any extra prepare call if you don't have to, unless it's directly going to benefit you later in the script.

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#15

Post by noduerme »

latch wrote: post #13
noduerme wrote: post #9

>> Prepared statements will automatically be created for any queries where it can be inferred that the query is static What does this mean in practice? Like, actual prepared statements are created against the DB session if there are no bound variables, even if that query is only made once? If so, it's an interesting but highly opinionated approach...

If you're using parameterized queries, then you _have_ to use PostgreSQL's "Extended Query" flow, which is what most people would think of as a "prepared statement". This is hardly opinionated. But normally, you use an unnamed prepared statement and/or portal, which PG will clean up for you, essentially only letting you have one of those per session (what we think of as a connection). I agree that sentence didn't mak…

my own, very opinionated way of doing this in nodejs was to wrap node-mysql in promises and then force myself to make it explicit when preparing a a query whether I want it to set up a prepared statement which returns a reusable handle or run directly. That requires tracking prepared statement names and key/values for each one.

you'll probably find this bad code too, but it was more of an experiment... I still don't feel safe using nodejs in deployment.

https://github.com/joshstrike/StrikeDB/blob/master/src/Strik...

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#16

Post by oulipo »

9dev wrote: post #7
skrebbel wrote: post #5

I feel obliged to namedrop Zapatos here, another postgres JS client with a remarkably similar design (sql in template strings), but fully typesafe. If TypeScript is your thing, you might appreciate it: https://jawj.github.io/zapatos/ Personally I feel it’s one of the best designed (and documented) TS libraries out there and I’m sad it’s not very well known.

Seconded. The lateral join features in combination with the shortcut functions make it exceptionally easy to build backends with actual SQL, without compromising on ease of use.

Does that work also with Postgres-compatible backends like Bigquery et al ?

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#17

Post by xctr94 »

skrebbel wrote: post #5

I feel obliged to namedrop Zapatos here, another postgres JS client with a remarkably similar design (sql in template strings), but fully typesafe. If TypeScript is your thing, you might appreciate it: https://jawj.github.io/zapatos/ Personally I feel it’s one of the best designed (and documented) TS libraries out there and I’m sad it’s not very well known.

We had to abandon Zapatos because a) it doesn’t support multiple schemas; b) the types wouldn’t always be very readable.

PgTyped was the alternative, and 2 years later I’m very glad we made the switch.

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#19

Post by fauigerzigerk »

latch wrote: post #13
noduerme wrote: post #9

>> Prepared statements will automatically be created for any queries where it can be inferred that the query is static What does this mean in practice? Like, actual prepared statements are created against the DB session if there are no bound variables, even if that query is only made once? If so, it's an interesting but highly opinionated approach...

If you're using parameterized queries, then you _have_ to use PostgreSQL's "Extended Query" flow, which is what most people would think of as a "prepared statement". This is hardly opinionated. But normally, you use an unnamed prepared statement and/or portal, which PG will clean up for you, essentially only letting you have one of those per session (what we think of as a connection). I agree that sentence didn't mak…

Looks like named prepared statements can be disabled by setting "prepare" to false in the connection settings:

https://github.com/porsager/postgres#connection-details

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#20

Post by Exuma »

xctr94 wrote: post #17
skrebbel wrote: post #5

I feel obliged to namedrop Zapatos here, another postgres JS client with a remarkably similar design (sql in template strings), but fully typesafe. If TypeScript is your thing, you might appreciate it: https://jawj.github.io/zapatos/ Personally I feel it’s one of the best designed (and documented) TS libraries out there and I’m sad it’s not very well known.

We had to abandon Zapatos because a) it doesn’t support multiple schemas; b) the types wouldn’t always be very readable. PgTyped was the alternative, and 2 years later I’m very glad we made the switch.

What do you mean multiple schemas ? What is an example of a non readable type?
Post reply on HN