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
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');