602 questions
1
vote
1
answer
49
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 ...
- 195
0
votes
0
answers
20
views
Executing queries on Postgres pool directly instead of fetching an idle instance
I'm trying to feather out some misunderstandings of how I've been querying with Postgres in my application.
Most of the documentation illustrates an approach like so:
const pool = new pg.Pool({
user:...
- 6,443
0
votes
1
answer
43
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
218
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 ...
- 191k
0
votes
0
answers
111
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
31
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(&...
- 69
0
votes
0
answers
66
views
abort/stop a postgres query from node, TypeScript?
I have a little express server which queries data from a PostgreSQL db.
this looks like this:
app.get('/query_tile/:z/:x/:y', async (req: Request, res: Response) => {
const t0 = performance.now(...
- 602
6
votes
2
answers
4k
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(),
...
- 83
0
votes
0
answers
199
views
How to enable debug logs in node-postgres?
I'm working on a Node.js application that uses node-postgres (pg) to interact with a PostgreSQL database. During development and debugging, I need more detailed logs to understand what's happening ...
- 365
0
votes
0
answers
109
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 (...
- 853
0
votes
1
answer
39
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,"...
- 2,047
1
vote
0
answers
39
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 ...
- 11
0
votes
0
answers
116
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....
- 61
0
votes
0
answers
219
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 ...
- 61
0
votes
0
answers
212
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 ...
- 61
0
votes
1
answer
91
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.2k
0
votes
0
answers
67
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
270
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-...
- 952
0
votes
1
answer
180
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,409
0
votes
0
answers
112
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,465
1
vote
1
answer
149
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,443
0
votes
0
answers
386
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 ...
- 164
1
vote
1
answer
404
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
265
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
76
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,144
0
votes
1
answer
724
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({...
- 310
4
votes
2
answers
801
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
543
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
160
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 = ...
- 900
1
vote
4
answers
382
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,401
2
votes
0
answers
1k
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
157
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
244
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
171
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
361
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
...
- 429
0
votes
1
answer
76
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
434
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
4
votes
1
answer
2k
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. ...
- 2,935
0
votes
2
answers
71
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,665
1
vote
1
answer
271
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.3k
1
vote
1
answer
191
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,319
0
votes
0
answers
134
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
596
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
871
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,663
0
votes
1
answer
1k
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 ...
- 191
0
votes
0
answers
278
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
96
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 ...
- 427
0
votes
1
answer
149
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 ...
- 528
0
votes
0
answers
211
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 ...
- 327
0
votes
1
answer
612
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,495