How to read from postgresql script in node.js

Question

I use pg module to connect postgres database to node.js project.

const pg = require('pg')
const pool = pg.Pool(
{
  "user":"postgres",
  "password":"password",
  "host": "localhost",
  "db" : "db1"
});

pool.connect();

pool.on('connect',(client)=>
{
client.query("insert into table1(id, name) values(1, "item1")")
   client.query("select * from table 1",(err, res)=>{
     if( err) throw err;
     console.log(res);
   });
}
)

My sql code is in a file called script.sql. My code is very similar to above and I want to read from script.sql file rather than putting sql code inside query function like client.query("insert into table1(id, name) values(1, 'item1')") . Is there a way to do it in pg module or do you suggest an effective way to read from script in node-postgres.

Answer 1

You either synchronously read the file before the server started (readFileSync blocks the server):

const pg = require('pg');
const { readFileSync } = require('fs');

const sqlInsert = readFileSync('insert.sql');
const sqlSelect = readFileSync('select.sql');

const pool = pg.Pool({
  "user":"postgres",
  "password":"password",
  "host": "localhost",
  "db" : "db1"
});

pool.connect();

pool.on('connect', client => {
  client.query(sqlInsert);
  client.query(sqlSelect, (err, res) => {
    if (err) throw err;
    console.log(res);
  });
});

or asynchronously read the file after the server started:

const pg = require('pg');
const { readFile } = require('fs');

const pool = pg.Pool({
  "user":"postgres",
  "password":"password",
  "host": "localhost",
  "db" : "db1"
});

pool.connect();

pool.on('connect', client => {
  readFile('insert.sql', sqlInsert => {
    client.query(sqlInsert);
  });
  readFile('select.sql', sqlSelect => {
    client.query(sqlSelect, (err, res) => {
      if (err) throw err;
      console.log(res);
    });
  });
});

You can also use promise-based operations instead of callback-based operations:

const pg = require('pg');
const { readFile } = require('fs/promises');

const pool = pg.Pool({
  "user":"postgres",
  "password":"password",
  "host": "localhost",
  "db" : "db1"
});

pool.connect();

pool.on('connect', async client => {
  await sqlInsert = readFile('insert.sql');
  client.query(sqlInsert);
  await sqlInsert = readFile('select.sql')
  client.query(sqlSelect, (err, res) => {
    if (err) throw err;
    console.log(res);
  });
});