Queries from NodeJS to PostgreSQL DB doesn't properly show UTF8 characters

Question

I'm working on a project, and my mother tongue is spanish and inside my database I'm using characters such as "ñ" and "é". When I'm using the psql shell those characters properly show on the terminal, but when I make a query using node-postgress those characters doesn't show up, instead I get ¤ or ¢.

In my database I have both client_encoding and server_encoding to UTF8, and I even checked with node-postgress, using a query, to see if they also were set to UTF8 and they didn't change for some other reason.

My database connection it's set up like this

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

const db = new Pool({
  user: user,
  password: password,
  host: localhost,
  port: 5432,
  database: my_database,
});

module.exports = db;

And the code for my query is like this:

const router = require("express").Router(),
  db = require("../database/db");

//GET A PLACE ROUTE
router.get("/", async (req, res) => {
  try {
    const place = await db.query("SELECT * FROM places");
    console.log(place.rows[0].name);
    res.status(200).json({
      status: "success",
      data: {
        place_name: place.rows[0].name,
      },
    });
  } catch (error) {
    console.error(error.message);
    res.status(500).send("Error del servidor");
  }
});

And now, if the name of the place is for example "Salón de peñas", how it will show up both in the console.log and my json response will be like "Sal¢n de pe¤as".

At first, I thought that the problem was because I didn't correctly set up my json response charset, but then I sent these characters as a response, and they show up correctly. The problem is when these characters come from my database. I checked the database encoding (both the client and the server) and they're set to UTF8, and like I said before, these characters display correctly when I'm using the psql shell.

I'm basically having the exact same problem as this question that didn't get an answer

Answer 1

I think I found a workaround to this problem which may be hard to explain but I'll try my best.

So, I realized like for example a new user register and they have ñ or ó in their name, inside the database it shows as ├▒ and ├│. BUT if I do a query from my server and send a json response, "├▒" "├│" characters dissapear and "ñ" "ó" shows instead.

With this weird behavior I thought about inserting all my places through my backend into the database, instead of inserting it using the psql shell. That would be really annoying because I will need to create a route to insert data into my places table, and then do a post request with postman for every row that I need to insert, and then delete that post route because I don't really need it.

After that I realised that maybe using the \i command (the command to execute a .sql file) from the psql shell may cause a similar behavior like inserting data from the server with a post request. And it did! so now, I have a database.sql file with this inside:

CREATE DATABASE my_database;

\c my_database;

SET client_encoding = 'UTF8';

create extension if not exists "uuid-ossp";

CREATE TABLE users(
    user_id UUID DEFAULT uuid_generate_v4(),
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    PRIMARY KEY (user_id)
);

CREATE TABLE places(
    place_id SERIAL,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(50) NOT NULL,
    PRIMARY KEY(place_id)
);

INSERT INTO places(name, address) VALUES ('Salón de peñas', 'Calle 242');
INSERT INTO places(name, address) VALUES ('another place', 'another adress');
INSERT INTO places(name, address) VALUES ('another place', 'another adress');
INSERT INTO places(name, address) VALUES ('another place', 'another adress');

Now, if I need to add another row into my places table, I need to create a .sql file and execute it to add a row. Which may be annoying but it's just only needed when there are special characters in the row