Hacker News Reader

Comment by noduerme | Hacker News Reader

Somewhat off topic, as someone who hasn't used PostgreSQL and only has experience with mysql/MariaDB... I've never liked writing queries with numbered parameters from an array with placeholders like $1 in this example. I find them much easier to read and debug when I pass them with string keys, basically:

`UPDATE t SET x=:x WHERE 1` `{x:42}`

I found that the original node-mysql didn't even allow this, so I wrote my own parser on top of it. But I don't see this style of binding used in examples very often. Is it frowned upon for some reason?

View on HN

PostgreSQL uses the format $1, $2 in the protocol, so I think it's just that nobody has bothered to implement named parameters in clients.

In another style, postgres.js uses calls such as sql`select * from t where id = ${variable}` (which is safe because it's a tagged template, not string interpolation).

A sometimes easier solution now in TS/JS is to use a simple template interpolation function (template tag) to inline the parameters and let the library autonumber them.

    const x = 42
    const id = 'example'
    const { rows } = pgquery`Update t set x = ${x} where id = ${id}`

The pgquery function can convert that into the $1 and $2 that Postgres expects, but the source code is a little easier to read and has named parameters.

Of course, it potentially makes debugging the query from the database side a little harder because the query itself in its running form is still going to show the $1 and $2 placeholders so you'd have to count "holes" to figure which is which when trying to grep which source line is generating that query. I know that's why some frown on this template-based auto-placeholders because they want the code to better resemble the queries as they run in Postgres.

(Also yeah, it might be nice if Postgres also supported named placeholders like some of the other SQL databases do.)

This is basically how we do it in .NET. With Dapper it’s particularly neat sometimes because you can just pass an object that you were using anyway, and it will match parameter names to its properties case-insensitively.

I.e.

Query("select * from MyTable where Id = @Id", myEntity)

The idiom is to use anonymous objects which you can new up inline like “new { id, age = 5 }”, where id is an existing variable that will automatically lend its name. So it’s pretty concise.

The syntax is Sql Server native (which supports named params at the protocol level), but the Npgsql dat provider converts it to PG’s positional system automatically.

Clorinde does this. It lets you write raw sql (with keyword arguments) and generate strongly typed Rust API (that deals with things like Option for nullable columns, etc)

https://github.com/halcyonnouveau/clorinde/?tab=readme-ov-fi...

Slonik uses template strings to do this inline, safely. https://github.com/gajus/slonik

Reply on news.ycombinator.com