node-postgres parameterized SELECT query-CodePudding

I have a problem with fetching specific columns from the database. I want to fetch balance for a user with provided user_id and currency.

import dotenv from 'dotenv';
import pkg from 'pg';

dotenv.config();

const {Pool, Client} = pkg

const DB_USER = process.env.DB_USER;
const DB_HOST = process.env.DB_HOST;
const DB_DATABASE = process.env.DB_DATABASE;
const DB_PASSWORD = process.env.DB_PASSWORD;
const DB_PORT = process.env.DB_PORT;

const credentials = {
    user: DB_USER,
    host: DB_HOST,
    database: DB_DATABASE,
    password: DB_PASSWORD,
    port: DB_PORT,
};

async function getBalance(user_id, currency) {
    const pool = new Pool(credentials);
    const res = await pool.query('SELECT $1 FROM wallet WHERE user_id = $2;', [currency, user_id]);
    console.log(res.rows);
    await pool.end();
}

And I'm getting >[ { '?column?': 'USD' } ] for provided currency = 'USD', instead of [ {'USD': 1.2}]. And when I don't use column name then I'm fetching whole balances for a user.

The table is looking something like this: user_id | USD | EUR | GBP | ... 123123 1.2 2.3 3.4
(Balances for that user in that currency), so for user 123123, his USD balance is 1.2, EUR is 2.3 and GBP is 3.4

CodePudding user response：

You cannot parameterise a column name, you'll need to build the SQL string dynamically for that:

async function getBalance(user_id, currency) {
    const client = new Client(credentials);
    await client.connect();

    const query = 'SELECT ' client.escapeIdentifier(currency) ' FROM wallet WHERE user_id = $1;';
    const res = await client.query(query, [user_id]);
    console.log(res.rows);

    await client.end();
}

You might also want to validate the currency names to be existing columns (and not 'user_id'), like

if (!['USD', 'EUR', 'GBP'].includes(currency)) throw new RangeError('Currency not available');