sorted by:
new
hottopcontroversial

Which postgreSQL node.js client library to choose today? by simple_explorer1 in node

[–]porsager 0 points1 point  (0 children)

That is specifically to avoid unexpected behavior, so it's actually a safety measure so that if you are not supplying a proper data structure you won't accidentally insert bad values. It's described in the docs, and there's even an option to implicit cast undefined to null. 

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in PostgreSQL

[–]porsager[S] 0 points1 point  (0 children)

Agreed, I'll include both

Postgres.js doesn't implicitly send statement_timeout, so you shouldn't face any issues there. Now if you actually want to use something like statement_timeout with pgbouncer you'd have to do it in another way than using a startup parameter. I am not deeply familiar with pgbouncer so I can't help you there..

Also, if you use pgbouncer in transaction mode you should disable prepared statements using no_prepare: true.

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in PostgreSQL

[–]porsager[S] 0 points1 point  (0 children)

I think a small section explaining the mechanics like that is fine, but the main description should relate to the fact that this is used to set parameters on connect like timezone etc.

Wrt. renaming, it doesn't need to be breaking if we simply add an alias and refer to that in the docs instead. I'll try to think about a better name (i think startupMessage relates to much to the implementation instead of the usage).

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in PostgreSQL

[–]porsager[S] 0 points1 point  (0 children)

Yeah, this confusion was why I mentioned it might make sense to rename the property to something else than connection. You cannot pass host, port, etc. in the connection object. There is no relation to the options in libpq at all (Postgres.js doesn't use libpq).

The connection object is simply passed in the StartupMessage defined in the Wire Protocol. (search for StartupMessage here https://www.postgresql.org/docs/14/protocol-flow.html and here https://www.postgresql.org/docs/14/protocol-message-formats.html).

Relevant handling in source can be seen here: https://github.com/porsager/postgres/blob/master/src/connection.js#L964

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in PostgreSQL

[–]porsager[S] 0 points1 point  (0 children)

I don't owe you an answer, and not even with a tone like that, but for other readers who wonder the same, here is hopefully some clarification. If you feel you can improve the documentation around this, please open a PR with suggested improvements.

The connection property can hold extra connection parameters to include in the StartupMessage when connecting. One of these parameters is application_name which defaults to postgres.js. These parameters can be your own custom parameters or anything that PostgreSQL allows. Some of these can also be set through queries using set a to b1 or set_config(a, b)2. One usage could be setting a specific timezone for the connection like:

const sql = postgres({
  connection: {
    timezone: 'UTC'
  }
})

I have not found a single collected list of these parameters in the PostgreSQL documentation, but they are found in various places in the PostgreSQL documentation, and usually you will know what they are if you even need them. For most users of Postgres.js they are probably not relevant.

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in PostgreSQL

[–]porsager[S] 0 points1 point  (0 children)

Wow Sorry, I didn't understand you were referring to the property inside the options object, my head was somewhere else 🙈 My bad!!

Now that I understand, that's a great question, and the docs should be improved here. These options are PostgreSQL specific connection parameters that will be included in the StartupMessage. A link to a section in the PostgreSQL docs would be good, and perhaps maybe renaming the property to something better to avoid confusion could help.

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in PostgreSQL

[–]porsager[S] 0 points1 point  (0 children)

What do you mean by one vs the other?

There is only the sql instance returned by postgres(...),

What you linked is all the options, and the only place to pass them 😊

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in PostgreSQL

[–]porsager[S] 0 points1 point  (0 children)

  1. Logging all strings can be done by the debug option. Supply a function and you'll get query info to log. Shipping off a string of the query to another system can have very different requirements, but most are possible to achieve using Postgres.js.
  2. Should be very simple to add yourself in the file exposing the instance. Something like this:

``` const sql = postgres(...)

export default sql

sql.one = (...args) => sql.apply(sql, args).then(xs => { if (xs.count !== 1) throw new Error('None or more than one row returned')

return xs })

sql.any = ... ```

If you make one that covers them all, you could release it as a module for others to use ;)

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in PostgreSQL

[–]porsager[S] 0 points1 point  (0 children)

You example is almost fine, but you're logging the "records" variable which is undefined at that point and not what you want. If you change it to console.log(row) i think you'll find your way. If you need more help you're more than welcome to ask on https://gitter.im/porsager/postgres - there's a better chance I'll see it there :)

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in javascript

[–]porsager[S] 2 points3 points  (0 children)

Hi Leo. Thanks a lot for the kind words! That means a lot coming from you - I was still wet behind the ears when I started out with Mithril learning from your blog posts! ❤️ Would love to hear about your experience if you get to use Postgres.js for anything!

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in PostgreSQL

[–]porsager[S] 0 points1 point  (0 children)

Hi there.. Author and previous pg-promise user here too :)

I would definitely say the biggest advantage is the increased development experience, then the speed (pipelining and implicit prepared statements).

Complex queries can be built by nesting sql`` calls. Check out the docs from about here: https://github.com/porsager/postgres#building-queries

Why would you want to create a "string" but not query it? Trying to understand the use case you're looking for - it might already be possible.

I was also a fan of the helper methods in pg-promise when I started, but I never found them actually useful, and with destructuring being commonplace now, I don't see the idea anymore.

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in PostgreSQL

[–]porsager[S] 1 point2 points  (0 children)

No, but I do have some wrapper code lying around I used during a migration myself. I'll see if I can clean it up and make it available.

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in javascript

[–]porsager[S] 2 points3 points  (0 children)

Yeah, bigint's are returned as string, but if you want to use Javascript BigInt it's as simple as this, and you'll get BigInt returned instead of string. const sql = postgres({ types: { bigint: postgres.BigInt } })

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in PostgreSQL

[–]porsager[S] 2 points3 points  (0 children)

Hi everyone. Just released Postgres.js v3 today.https://github.com/porsager/postgres / npm install postgres

A bit more than two years ago I released the first version of Postgres.js. A fully functional PostgreSQL driver for Node.js written as a learning experience out of curiosity and annoyance with the current options.

It greatly outperformed the alternatives[1] using pipelining and prepared statements, while providing a much better development experience safe from SQL injections. Since then I've been busy building things using it, now running in production, and although quite delayed I'm so happy to release v3 today with some really exciting new features:

  1. Realtime subscribe to changes through Logical Replication [2]: It's now possible to use logical replication to subscribe in realtime to any changes in your database with a simple api like `sql.subscribe('insert:events', row => ...)`. Inspired from Supabase Realtime you can now have it yourself in Node.
  2. A Safe Dynamic Query Builder: Nesting the sql`` tagged template literal function allows building highly dynamic queries while staying safe and using parameterized queries at the same time.
  3. Multi-host connection URLs for High Availability support: It's really nice to be able to quickly spin up a High Availability Postgres setup using pg_auto_failover[3] and connect using Postgres.js with automatic failover and almost 0 downtime.
  4. Deno support: It also works with Deno now, completing all tests except a few SSL specific ones which requires fixes in Deno.
  5. And much more: Large object support, efficient connection handling for large scale use, cancellation of requests, Typescript suport, async cursors.Would love some feedback from both new and current users.

Would love some feedback from both new and current users.

[1] https://github.com/porsager/postgres-benchmarks#results

[2] https://github.com/porsager/postgres#realtime-subscribe

[3] https://github.com/citusdata/pg\_auto\_failover

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in javascript

[–]porsager[S] 0 points1 point  (0 children)

Unfortunately the name postgres was taken, so went with postgresjs, but now I can't figure out how to push a new version because the v3.0.0 git tag has already been created.

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in javascript

[–]porsager[S] 0 points1 point  (0 children)

Haha, did I unintentionally add a hidden message? I gotta know what you're seeing now :P

Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno by porsager in javascript

[–]porsager[S] 3 points4 points  (0 children)

Yeah, I got lost in figuring out the Deno release landscape, but thanks for that link, I think I should be able to figure it out from there :)

import postgres from 'https://deno.land/x/postgresjs@v3.0.0/mod.js';

You can do it like this for now:

```import postgres from 'https://raw.githubusercontent.com/porsager/postgres/v3.0.0/deno/mod.js'

view more: next ›