Encountering a problem when trying to insert JSON data into my postgres database – Node.js

I’m having a problem when i try to save my JSON data into a database. Right now, i have this code here:

My service that retrieves and cache data from an API with the given parameter cep:

const axios = require('axios');
const NodeCache = require('node-cache'); 

const myCache = new NodeCache();
const pool = require('../database/index');

async function verificaCache(cep) {
  const value = myCache.get(cep);

  if (value) {
    return { city: value, message: 'Data from the cache' };
  }

  const resp = await axios.get(`https://viacep.com.br/ws/${cep}/json/`);

  myCache.set(cep, resp.data, 600);

  return { city: resp.data, message: 'Data not from the cache' };
}

module.exports = verificaCache;

My route using the service:

const express = require('express');

const cityRouter = express.Router();

const verificaCache = require('../services/VerificaCacheService');

cityRouter.get('/:cep', async (request, response) => {
  const { cep } = request.params;

  try {
    const data = await verificaCache(cep);

    response.status(200).json(data);
  } catch (error) {
    response.status(400).send({ message: 'Erro no retorno do CEP' });
  }
});

module.exports = cityRouter;

It works with out problems:

Here that comes the problem. When i try to insert the data into my database implementing this code into my service:

const axios = require('axios');
const NodeCache = require('node-cache'); 

const myCache = new NodeCache();
const pool = require('../database/index');

async function verificaCache(cep) {
  const value = myCache.get(cep);

  if (value) {
    return { city: value, message: 'Data from the cache' };
  }

  const resp = await axios.get(`https://viacep.com.br/ws/${cep}/json/`);

  myCache.set(cep, resp.data, 600);
  
  // begins here the code to insert data into the database
  const {
    logradouro, complemento, bairro, localidade, uf,
    ibge, gia, ddd, siafi,
  } = resp.data;

  const values = [cep, logradouro, complemento, bairro, localidade, uf, ibge, gia, ddd, siafi];

  const sql = 'INSERT INTO public.city(cep, logradouro, complemento, bairro, localidade, uf, ibge, gia, ddd, siafi) VALUES ?';

  await pool.query(sql, values); // ends here the code to insert data into the database

  return { city: resp.data, message: 'Data not from the cache' };
}

module.exports = verificaCache;

It doesn’t insert anything! And i tried to put only city instead of public.city and it still doesn’t work.

Here is my index.js file for the connection:

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  password: '1234',
  database: 'eureka',
});

pool.connect((err) => {
  if (err) {
    console.log('Erro connecting to database...', err);
    return;
  }
  console.log('Connection established!');
});

pool.end((err) => {
  if (err) {
    console.log('Erro to finish connection...', err);
    return;
  }
  console.log('The connection was finish...');
});

module.exports = pool;

And my Database without any data:

I created the table city directly from the dbeaver.

As you can see, my application is connected to the database:

I’m a beginner and i would appreciate some help

Answer

You don’t seem to be using any library that handles dynamic mapping of your parameters to the required ordinal format into the query itself(like pg-parameterize) so the line:

const sql = 'INSERT INTO public.city(cep, logradouro, complemento, bairro, localidade, uf, ibge, gia, ddd, siafi) VALUES ?';

and more specifically the VALUES ? part shouldn’t be doing anything useful. Change that to:

const sql = 'INSERT INTO public.city(cep, logradouro, complemento, bairro, localidade, uf, ibge, gia, ddd, siafi) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)';

as the pg npm package is using ordered parameters for parameterized queries.

Checkout the “Parameterized query” section in the docs for queries.

EDIT:

You’re either inserting the new record or getting an error. There is no other option. If you don’t see any new record in the db after refresh, then you’re just swallowing the error result in your code. As you’re going the async/await promise route, the code await pool.query(sql, values); will throw if something goes wrong.

Then, in your caller:

cityRouter.get('/:cep', async (request, response) => {
  const { cep } = request.params;

  try {
    const data = await verificaCache(cep);

    response.status(200).json(data);
  } catch (error) {
    response.status(400).send({ message: 'Erro no retorno do CEP' });
  }
});

you catch the error. Check what the error object in the catch block is holding and you’ll see what’s wrong.