Node.js将API汇率数据插入PostgreSQL的数据映射问题

• tcmbDovizKuru(cb) 调用位置错误，写在insertToDatabase函数内部会触发无限循环调用
• data['tarihDate']['currency']是对象数组，直接将整个数组传给pg查询参数，和SQL语句里的7个占位符完全不匹配
• 未提取数组内每个货币对象的对应字段，且currencyCode字段存储在子属性attributes下，直接读取会得到undefined
• 数据库操作完成后未关闭连接，会造成连接泄漏

方案1：逐条插入（适合数据量小的场景） #

完整可运行代码如下：

var tcmbDovizKuru = require('tcmb-doviz-kuru');
var pg = require('pg');
// 替换为你自己的PostgreSQL连接串
const connectionString = 'postgresql://用户名:密码@数据库地址:端口/数据库名称';

function cb(error, data) {
    if (error) {
        return console.log('汇率接口请求失败', error)
    }
    insertToDatabase(data);
}

async function insertToDatabase(data){
    const pgClient = new pg.Client(connectionString);
    try {
        await pgClient.connect();
        const currencyList = data['tarihDate']['currency'];
        const insertSql = `INSERT INTO currency.kur("date", forexBuying, forexSelling, banknoteBuying, banknoteSelling, unit, isim, currencyCode)
            VALUES(CURRENT_TIMESTAMP,$1,$2,$3,$4,$5,$6,$7)`;
        
        // 遍历每条汇率数据依次插入
        for (let currency of currencyList) {
            // 严格按照SQL占位符顺序提取对应字段
            const params = [
                currency.forexBuying,
                currency.forexSelling,
                currency.banknoteBuying,
                currency.banknoteSelling,
                currency.unit,
                currency.isim,
                currency.attributes.currencyCode
            ];
            await pgClient.query(insertSql, params);
            console.log(`${currency.attributes.currencyCode} 汇率插入成功`);
        }
    } catch (e) {
        console.error('数据插入失败：', e);
    } finally {
        // 操作完成后关闭数据库连接
        await pgClient.end();
    }
}

// 接口调用放在全局位置，不要放在insertToDatabase内部
tcmbDovizKuru(cb);

方案2：批量插入（适合数据量大的场景，性能更高） #

仅需要修改insertToDatabase函数即可，一次性提交所有插入请求，减少数据库IO开销：

async function insertToDatabase(data){
    const pgClient = new pg.Client(connectionString);
    try {
        await pgClient.connect();
        const currencyList = data['tarihDate']['currency'];
        const values = [];
        const placeholders = [];

        // 批量生成占位符和参数数组
        currencyList.forEach((currency, idx) => {
            const offset = idx * 7;
            placeholders.push(`(CURRENT_TIMESTAMP,$${offset+1},$${offset+2},$${offset+3},$${offset+4},$${offset+5},$${offset+6},$${offset+7})`);
            values.push(
                currency.forexBuying,
                currency.forexSelling,
                currency.banknoteBuying,
                currency.banknoteSelling,
                currency.unit,
                currency.isim,
                currency.attributes.currencyCode
            );
        });

        const insertSql = `INSERT INTO currency.kur("date", forexBuying, forexSelling, banknoteBuying, banknoteSelling, unit, isim, currencyCode)
            VALUES ${placeholders.join(',')}`;
        await pgClient.query(insertSql, values);
        console.log(`共${currencyList.length}条汇率数据插入成功`);
    } catch (e) {
        console.error('数据插入失败：', e);
    } finally {
        await pgClient.end();
    }
}

内容的提问来源于stack exchange，提问作者msanli224