La operación INSERT usando Node.js y PostgreSQL no funciona
Estoy creando mi primera aplicación usando Node.js y PostgreSQL. Esta aplicación se conecta a db, crea una tabla, hace un raspado web de un sitio web, inserta información en db y luego se desconecta de db. Estoy tratando de hacerlo usandoasync/await
.
El problema es que la operación de inserción no funciona. No hay errores, simplemente tablacoberturas permanece vacío
Este es mi codigo.
app.js:
const postgreSQLlib = require('./middlewares/postgreSQLlib.js')
const scraper = require('./routers/scraper.js');
const start = async function() {
await console.log('START');
// connect
await postgreSQLlib.connect();
// create tables
var queryCreateCoverages = {
text: 'CREATE TABLE IF NOT EXISTS coverages ('+
'id SERIAL PRIMARY KEY,' +
'vaccine VARCHAR(64) NOT NULL,' +
'country VARCHAR(255) NOT NULL,' +
'region VARCHAR(255),' +
'year VARCHAR(4) NOT NULL,' +
'value VARCHAR(12) NOT NULL);'
};
var queryRes = await postgreSQLlib.query(queryCreateCoverages);
//console.log('Result: ', queryRes);
// get data
await scraper.download();
const query = {
text: 'SELECT * FROM coverages;',
values: ['Italy']
}
var queryRes = await postgreSQLlib.query(query);
//console.log('Result: ', queryRes);
// disconnect
await postgreSQLlib.disconnect();
return 'FINISH';
}
// start application
start()
.then(function(res) {
console.log(res);
})
.catch(function(err) {
console.log(err);
});
postgreSQLlib.js:
var fs = require('fs');
const {Client} = require('pg'); // node-postgres npm package
const userDb = 'admin';
const passDb = 'admin';
const hostDb = 'localhost'; // default
const portDb = '5432'; // default
const nameDb = 'db';
const connectionString = 'postgres://' + userDb + ':' + passDb + '@' + hostDb + ':' + portDb + '/' + nameDb; // result is 'postgres://admin:admin@localhost:5432/db';
let client;
var methods = {};
methods.connect = async function() {
client = new Client({connectionString});
return await client.connect()
.then(async function() {
await console.log('\nConnected to ' + client.database + ' at ' + client.host + ':' + client.port + ' as ' + client.user + ' (pass: ' + client.password + ')');
})
.catch(function(err) {
console.log('\nError during connection to PostgreSQL');
throw err;
});
}
methods.query = function(query) {const start = Date.now();
return client.query(query)
.then(function(,res) {
const duration = Date.now() - start;
console.log('\nExecuted query: {\n ' + query.text + '\n [' + query.values + ']' + '\n duration: '+ duration + '\n rows: ' + res.rows.length + '\n}');
return res;
})
.catch(function(err) {
console.log('\nError executing query', err.stack);
});
}
methods.disconnect = async function() {
await client.end()
.then(function() {
console.log('\nConnection has ended');
})
.catch(function(err) {
console.log('\nError during clossing connection');
throw err;
});
}
module.exports = methods;
scraper.js:
var cheerio = require('cheerio');
var request = require('request');
var util = require('../helpers/util.js');
var postgreSQLlib = require('../middlewares/postgreSQLlib.js');
var methods = {};
var countries = {
'Italy': 'ITA',
'Latvia': 'LVA',
'Netherlands': 'NLD'
};
var outDir = './output/';
var finalData = outDir + 'coverage-eu.json'
var jsons = [];
methods.download = async function(req, res) {
for(country in countries) {
var url = 'http://apps.who.int/immunization_monitoring/globalsummary/coverages?c=' + countries[country];
return await request(url, (async function(country) {
var thisCountry = country;
return async function(error, res, html) {
if(error) {
throw error;
}
$ = cheerio.load(html);
var years = [];
var vaccines = [];
var coverages = [];
$('.ts .year').each(function() {
years.push($(this).text().trim());
});
$('.ts .odd td a, .ts .even td a').each(function() {
vaccines.push($(this).text().trim());
});
$('.ts .odd .statistics_small, .ts .even .statistics_small').each(function() {
coverages.push($(this).text().trim());
});
const numYears = years.length;
const numVaccines = vaccines.length;
for(var vaccineIdx = 0; vaccineIdx < numVaccines; vaccineIdx++) {
for(var yearIdx = 0; yearIdx < numYears; yearIdx++) {
// save on db
const query = {
text: 'INSERT INTO coverages (vaccine, country, region, year, value) VALUES ($1, $2, $3, $4, $5);',
values: [vaccines[vaccineIdx], country, '', years[yearIdx], coverages[vaccineIdx*numYears + yearIdx]]
}
var queryRes = await postgreSQLlib.query(query);
//console.log('Result: ', queryRes);
}
}
}
})(country));
}
};
module.exports = methods;
¿Por qué? No puedo resolver el error si no se muestran errores.
Cualquier ayuda es apreciada y perdón por mi mal inglés.
EDITAR 1Si uso el shell de SQL (psql) para insertar registros, funciona:
EDITAR 2Modifiqué elconsole.log
delquery
método:
methods.query = async function(query, print) {
return await client.query(query)
.then(function(res) {
console.log(print, 'OK query');
return res;
})
.catch(function(err) {
console.log(print, 'ERR query');
});
}
scraper.js:
methods.download = async function(req, res) {
for(country in countries) {
console.log('\nCOUNTRY:', country); // ADDED
var url = 'http://apps.who.int/immunization_monitoring/globalsummary/coverages?c=' + countries[country];
let res = await request(url);
insert(res);
}
}
//...
// save on db
const queryInsert = {
text: 'INSERT INTO coverages (vaccine, country, region, year, value) VALUES($1, $2, $3, $4, $5);',
values: [vaccines[vaccineIdx], country, '', years[yearIdx], coverages[vaccineIdx*numYears + yearIdx]]
}
var printText = '[INSERT ' + country + ' IN coverages]';
var queryRes = await postgreSQLlib.query(queryInsert, printText);
Estas son las impresiones:
START
Connected to db at localhost:5432 as admin (pass: admin)
[CREATE TABLE coverages] OK query
COUNTRY: Italy
COUNTRY: Latvia
[INSERT Italy IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
COUNTRY: Netherlands
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
[INSERT Netherlands IN coverages] OK query
Connection has ended
FINISH
[INSERT Netherlands IN coverages] ERR query
Como puede ver, hay un error al final porque la conexión ya está cerrada. Además, las impresiones relacionadas con el resultado de la consulta no se ordenan con respecto al país. Por ejemplo:
COUNTRY: Italy
COUNTRY: Latvia
[INSERT Italy IN coverages] OK query
[INSERT Latvia IN coverages] OK query
[INSERT Latvia IN coverages] OK query
No hay nada debajoPAÍS: Italia, pero los resultados sobre las consultas de Italia están debajoPAÍS: Letonia (Y no hay todos).