602 questions
0
votes
1
answer
48
views
Connection to remote PostgreSQL server with hashtag in password with node-postgres
I have a password for a remote PostgreSQL server that I cannot change. This password contains hashtags (#). From searching, I know that this can be a problem.
I can connect to the server from the ...
- 147
2
votes
1
answer
59
views
Does the pg.Pool from node-postgres support acquireTimeoutMillis?
I'm having a hard time with an app that has some spikes in connections. I think if acquireTimeoutMillis was working these would be queued pre-database. Trying to set it in testing doesn't seem to be ...
- 920
0
votes
1
answer
39
views
"Merge INTO" can't catch exists data
I have a problem about inserting datas to my table with "MERGE INTO" method.
I am using a procedure to insert datas, and my datas come from array fields.
My Table:
CREATE TABLE IF NOT ...
- 3,340
1
vote
1
answer
61
views
Node-postgres query to remove object from array
I'm building backend with Express and TypeScript. DB is Postgres, client is node-postgres.
In DB I have table user_collection. This table has column collection of type JSONB. Collection column ...
- 215
0
votes
1
answer
168
views
SET search_path on every user with database connection pooling
I have a question which may sound dumb but I have never done this before and have some questions that I cant seem to find the answer of.
My project is in Next.js 14 with Kysely and node-postgres. I am ...
0
votes
1
answer
544
views
Are PostgreSQL's cursors per connection or something else?
I'm using node-postgres (pg) and its pg-cursor extension. I noticed when reusing the same connection between multiple cursors, it locks due to what I would assume that a single connection is only able ...
- 192k
0
votes
0
answers
146
views
How to fetch postgresql with pg-query-stream from server side?
I might be close, but not quite there yet as the below throws Error: Invalid response from route /api: handler should return a Response object, probably I do return just an object and not a Response?
...
- 15
0
votes
1
answer
35
views
Syntax Error in PostgresSQL When import pool into another file and run query
I'm trying to make connection with PostgreSQL and export it. When I import the pool into other file and run a query it throws the error: syntax error at end of input
postgre.js
const config = require(&...
- 49
15
votes
3
answers
10k
views
How to declare self-referencing foreign key with Drizzle ORM
In a Typescript project, declaring a table using Drizzle on postgres-node as follows:
const contractsTable = pgTable("contracts", {
id: serial("id").primaryKey(),
...
- 173
0
votes
0
answers
144
views
Error: password authentication failed for user 'abc' when connecting to PostgreSQL database with node-postgres
I have a multi tenant node application. I'm encountering an issue when trying to connect to local PostgreSQL database using node-postgres. Whenever the program attempt to execute a function (...
- 863
0
votes
1
answer
49
views
Semantically Identical JSONB Object Passed Into Postgres Function Cannot Be Parsed When Using Placeholders
I have two identical JSON objects.
First Object
Second Object
[{"id": 123,"firstname": "Mike","lastname": "Smith","ani": 123456,"email&...
- 2,097
1
vote
0
answers
62
views
postman request doesnt show results and stuck on sending request
the problem may reside either in the /login route or in the local strategy but a post request to /login makes postman send infinitely and no results come back. i've searched a lot but there doesn't ...
- 21
0
votes
0
answers
176
views
How do I fix a PostgreSQL order query (ASC/DESC) error (for complex queries) once I have seeded the database with data?
I am trying to do some complex queries in my bird database to get all the birds back but I want to be able to use queries in the url to get back by diet and to sort accordingly and order appropriately....
- 71
0
votes
0
answers
421
views
How do you do a PATCH request on data seeded into a PostgreSQL database in JavaScript
In summary I have a bird database and I am trying to do a PATCH request on seeded data I have added using the node-postgres Pool object to create a database connection with a pool of clients. I am ...
- 71
0
votes
0
answers
378
views
How to fix PostgreSQL error (42601) in my development database using node postgres I get with insert function
I am trying to an insert query of bird family data below ( using function insertIntoB_familiestbl) related to bird families into bird families table. But I am getting an error relating to the ...
- 71
0
votes
1
answer
149
views
Querying data with pg-cursor
I have the following code to read the first 10 rows from a query with a PG Cursor in a CloudFlare worker:
const client = new Client(`<DATABASE URL>`)
await client.connect()
const cursor = client....
- 22.3k
0
votes
0
answers
83
views
Node.js not recognising Exception from Postgres Procedure as an Error
I have a procedure in postgres which completes some validations and inserts some data. If the validations are not passed then it raises an exception.
CREATE OR REPLACE PROCEDURE EnrolStudentToProgram(...
- 2,108
0
votes
0
answers
382
views
Blackduck Policy Violation
For a TypeScript project we use pg-promise library and it depends on node-postgres.
For a daily scan we use Blackduck and it detects "High Vulnerability Security Issue" as follows:
2024-01-...
- 972
0
votes
1
answer
331
views
What is the correct syntax for dollar-parameters in PostgreSQL queries?
I am struggling with the correct syntax for a dollar-parameter. It sometimes works, sometimes not.
I am using the 'pg' package for NodeJS, but since AFAIK parsing the query happens on the server side, ...
- 1,529
0
votes
0
answers
139
views
Node.js: node-postgres native raise error SCRAM authentication requires libpq version 10 or above
I'm on Window 11 with Node.JS 20 and PostgreSQL 15.1:
import { native } from 'pg'; // npm install pg pg-native
const pool = new native.Pool({ .. });
pool.connect();
pool.query raise error
SCRAM ...
- 7,649
1
vote
1
answer
190
views
Re-using parameters passed to node-postgres upsert query
I'm using node-pg and I've decided to refactor some code that would first make a select query to see if a record exists and then make a second query to either insert or update a record.
Suppose the ...
- 6,625
0
votes
0
answers
481
views
node-postgres: Performance drops drastically as we keep sending queries
We recently switched from mysql to postgresql, and we are using node-posgres to send queries. Everything runs fine for a few minutes, and then queries start slowing down until our service is no longer ...
- 204
1
vote
1
answer
650
views
Error "no unique or exclusion constraint matching ON CONFLICT" for composite keys with overlapping columns [duplicate]
I have a table that looks like this:
CREATE TABLE IF NOT EXISTS list (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tok TEXT,
sid TEXT NOT NULL,
aid TEXT,
hash TEXT,
qtt SMALLINT,
p ...
- 23
0
votes
1
answer
366
views
Next.js parse data-type from PostgreSQL
I'm new to Next.js and I am currently following the official tutorial. To spice it up, I decided to run PostgreSQL locally instead of using the @vercel/postgres SDK used in the tutorial.
In the ...
- 13
0
votes
1
answer
97
views
using a subquery as a value in node-postgres
I need to perform something like this:
pg.query(`insert into tbl (field1, field2) values($1, $2)`, ["a", "(select id from another_table limit 1)"])
this causes the following error
...
- 2,316
0
votes
1
answer
1k
views
How to mock node-postgres in vitest
I am trying to mock node-postgres in vitest with esm. But I am running into different problems.
When I am using the Pool class in my function:
import {Pool} from 'pg';
...
const pool = new Pool({...
- 340
4
votes
2
answers
1k
views
Enforcing row-level security in a shared postgres pool
I'm working on a typical client-server webapp. It is using a system somewhat like GraphQL where the client has some flexibility in specifying what data it needs, without custom API endpoints for every ...
- 1,061
1
vote
0
answers
674
views
PG Client Error : could not write to file "base/pgsql_tmp/temp_file"
I am using node-postgres PG client on a node.js app to query data from the database and pass the results to an express.js response. This works perfectly fine. However, it takes a bit of time to get ...
- 13
0
votes
1
answer
200
views
Is this javascript vulnerable to SQL Injection? [duplicate]
I am using node-postgres library.
const sql = `
SELECT *
FROM "Employees"
where employee_id = '${employee_id}' ;
`;
console.log(`Query formatted: ${sql}`);
const result = ...
- 910
1
vote
4
answers
531
views
Recursive Breadth Traversal Query (with max depth), Returning Nested JSON, Filtering the Initial Seed
(I had originally asked this question on the DBA StackExchange, but didn't get much activity in it).
In my DB, I have tables for items and connections between them, creating a likely sparse graph, ...
- 7,863
2
votes
0
answers
2k
views
pg-node error when connecting to amazon rds database
I have created a database instance using the Amazon RDS Console, assigning it the following VPC security group, authorizing any IPV4 address to connect to the database (I know this is bad practice, ...
- 37
0
votes
0
answers
176
views
Use PGP_SYM_ENCRYPT function with node-postgres (fastify)
I'm trying to use the PGP_SYM_ENCRYPT function to save encrypted data in a PostgreSQL database using Fastify.
The pgcrypto extension has been added using this command
CREATE EXTENSION IF NOT EXISTS ...
- 71
0
votes
1
answer
312
views
How to update all table in postgreSQL
I'm starting with postgreSQL and NodeJS and I have a table of office objects with the fields object_id(PK), inventory(varchar), area(FK) and description(text). This table must be updated weekly. How ...
0
votes
0
answers
195
views
node-postgres parameterized query runs 10 times slower
I was using node-posgres parameterized queries.
However, it was inserting data very slowly: 100 rows was taking 5 seconds!
Switching to pg-format and producing plain old SQL strings - even when ...
- 358
2
votes
0
answers
435
views
Knex - Postgres RDS not able to use SSL
I am trying to use ssl in knex for postgres.
I downloaded RDS CA's certificate from here https://truststore.pki.rds.amazonaws.com/ap-south-1/ap-south-1-bundle.pem
I set up the knex config as follows
...
- 459
0
votes
1
answer
83
views
I fail to understand why I can't PUT values inside an existing table in a database ;/
I have a precreated table in a database which I want to insert values into from the frontend (i.e by the user) but for some reason the code I wrote fails to achieve what I am seeking :/
I'm still a ...
0
votes
2
answers
528
views
How can i pass an array as a parameter to a Vertica query from node.js?
I'm trying to execute sql queries against a vertica db. that works so far. but to prevent sql injection, I want to use parameterized queries. looks like vertica supports parameters as ? (compared to ...
- 6,605
6
votes
1
answer
3k
views
When to use pg Client instead of Pool max 1 connection on serverless?
There are several SO answers explaining the difference between the node-postgres (pg) Client and Pool classes. All these answers essentially say to use Pool for efficient use of multiple connections. ...
- 3,055
0
votes
2
answers
108
views
How can I replace this obviously bad string interpolation with a prepared statement?
Apart from checking that the attributeId is in a list of predefined strings – possible, but in this particular case costly – is there any way to re-write this in a less 'injectable' fashion (since the ...
- 4,745
1
vote
1
answer
292
views
can connect to remote database using psql but not with node-postgres
I can connect to my database with psql
❮❮❮ psql postgres://postgres:<password>@<host>:5432/postgres
psql (12.14 (Ubuntu 12.14-0ubuntu0.20.04.1), server 13.10)
WARNING: psql major version ...
- 10.5k
1
vote
1
answer
246
views
How Cloud SQL node library authenticates over pg connection libraries like npm pg
Was checking out the new google node sql connector for Cloud SQL.
It tells the authentication mechanism for libraries like pg as follows.
import pg from 'pg';
import {Connector} from '@google-cloud/...
- 1,429
0
votes
0
answers
177
views
postgreSQL pg_hba.conf - connection refused when using a LAN domain with node.js-postgres - allowed with pgAdmin 4
Attempts to connect using pgAdmin 4 are successful.
Attempts to connect using NPM package PG are refused.
ConnectErr connect EHOSTUNREACH fe80::xx:xxxx:xxxx:xxxx:5432 - Local (fe80::xxxx:xxx:xxxx:xxx%...
0
votes
2
answers
861
views
Finally block is running before Promise.all() finished
I'm working on a script to seed my database but am having issues with my node-pg connection being terminated before my rebuild function is finished.
const rebuildDB = async () => {
try {
...
- 11
1
vote
1
answer
946
views
When to Open/Close Connection with Node-Postgres and Serverless
I'm migrating from AWS Aurora PG to Heroku PG and I'm currently refactoring my queries. Using the Node-Postgres library in my Serverless Framework API, I now have to manage opening and closing the ...
- 9,931
0
votes
1
answer
2k
views
Node + PostgreSQL - is this a valid way to execute transactions?
I have an api built on Nodejs that uses node_postgres.
The website says: ...if you initialize or use transactions with the pool.query method you will have problems.
Im using pool.query where I don't ...
- 181
0
votes
0
answers
327
views
Node-postgres multiple queries best practice
I am learning postgres/SQL and am wondering what the best approach is here.
I have an invoice db design that has recipients, drafts, items.
Drafts are the invoices, items are the lines on the invoices,...
- 134
0
votes
1
answer
100
views
Is writing multiple INSERTS versus UPDATE faster for temporary POSTGRES databases?
I am re-designing a project I built a year ago when I was just starting to learn how to code. I used MEAN stack, back then and want to convert it to a PERN stack now. My AWS knowledge has also grown a ...
- 498
0
votes
1
answer
181
views
Is using Pool instead of Client in node-postgres useful despite Nodejs being single threaded?
I am using Node.js express for building REST api with postgres database using node-postgres package.
My question is whether I should use Client or Pool? I found this answer:
How can I choose between ...
- 806
0
votes
0
answers
246
views
node-postgres library connection dies when it encounters an error
I have inherited a legacy system made in nodeJS and postgres. Whenever I encounter a database call error e.g let's say an insert violates a duplicate constraint the db client throws an error which I ...
- 347
0
votes
1
answer
693
views
Promise.all() throwing error while connecting to database Error: timeout exceeded when used with Node-Postgres
I have Node.js express app with Postgres as a database. I'm using pg for database communication from the app.
This is how my db.service looks like
import { Pool } from 'pg';
const dbConfig = {/*my ...
- 7,645