[SOLVED] Nodejs - Trying to execute postgre sql file

Nodejs - Trying to execute postgre sql file

I'm trying to run the following command via nodejs:

psql -U postgres -d dbName -f import.sql

But I got the following prompt in the console:

Password for user postgres:

I tried to set the password via environnment variable, like this:

SET PGPASSWORD=mypassword && psql -U postgres -d dbName -f import.sql

But for some reason I get the following error:

psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "postgres"

I tripled check and it is the correct password, I also tried to change the password to use something without any special characters, suspecting an encoding issue, but still fails.

Then I tried with node-postgres, like this:

const file = (await readFile("./download/import.sql")).toString();

const pool = new Pool({
  user: "postgres",
  host: "localhost",
  database: "dbName",
  password: "myPassword",
  port: 5432,
});

await pool.query(file);

But it fails because my script contains lines like this: \copy tableA from 'db/tableA' and I have no idea how to solve this.

Next, I tried with spawn, like this:

const child = spawn("psql", ["-h", "localhost", "-U", "postgres", "-d", "dbName", "-f", "./download/import.sql"]);

child.stdin.write("myPassword");
child.stdin.end();

But I can still see the prompt asking for a password.

I'm running out of idea

EDIT: I'm using Windows

Solution

I found a solution!

It works if I use the connection string to connect:

execSync(`psql -f ./download/import.sql "postgresql://postgres:myPassword@localhost/dbName"`);

Here is the format: postgresql://<username>:<password>@<server>/<db>