3. Helper dbHandler.connect('postgres://jsreports:

Helper dbHandler.connect('postgres://jsreports:


  • @jan_blaha we have two postgres DBs we need to get data from the same user for both DB on AWS RDS whats the best way to handle this with user name and password set once?

    
function beforeRender(req, res, done) {
    dbHandler.connect('postgres://jsreports:#######>********@****-ro-replica.********.us-east-2.rds.amazonaws.com:5432/postgres', function (err) {
        if (err) {
                return done(err)
            }

            dbHandler.query('SELECT signup_date, total_customer FROM public.account_signup_day', function (err, results) {
                if (err) {
                    return done(err)
                }

                // next line fill the data that your current report is going to use 
                req.data = results // results should be containing the data of your query (array, object, etc)

                done()
            })
        }
    0

  • It is about where to store the postgres username and password?

    You can store it in the environment variables.
    Both options are described in the documentation here
    https://jsreport.net/learn/scripts

    0

  • Do i need to
    npm install pg
    for Postgres db connection or 3.4 have that built-in too?

    0

  • Something like this but it did not work

    const {accounts} = require('pg');
const db = new accounts({
  user: 'domo',
  host: '***-ro-replica.*********.us-east-2.rds.amazonaws.com',
  database: 'ats_whitelabel',
  password: '*******************',
  port: 5432,
});
db.connect();
function beforeRender(req, res, done) {
  let query = "SELECT * from accounts";
  
  query += " ORDER BY id LIMIT 25";
  db.query(query, (err, result) => {
    req.data = {
      rows: result.rows
    };
    db.end();
    done();
  });
}

    where table is accounts, database is ats_whitelabel

    0

  • @jan_blaha Can you confirm if the connection format is correct as I was not able to connect to the DB

    0

  • jsreport doesn't have external third-party modules built-in.
    You can try the code in the plain nodejs and test it works properly.
    If you have questions regarding the usage of a particular library, please refer to the documentation and github page of that particular lib.

    Everything that works in nodejs should work in jsreport.

    0

  • I have a new client and it's been over 1.5 years since worked with jsreports. You have made lots of new progress. We are going to use this for a sales dashboard, I hope. @jan_blaha Ok made progress, and I appreciated the help. Thanks for helping me get over the hump of getting things going again.

    Have a helper file but get an error when I run it.

     node helpers.js 
node:internal/bootstrap/switches/does_own_process_state:126
    cachedCwd = rawMethods.cwd();
                           ^

Error: ENOENT: no such file or directory, uv_cwd
    at process.wrappedCwd [as cwd] (node:internal/bootstrap/switches/does_own_process_state:126:28)
    at node:path:1082:24
    at Object.resolve (node:path:1096:39)
    at resolveMainPath (node:internal/modules/run_main:19:40)
    at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:71:24)
    at node:internal/main/run_main_module:17:47 {
  errno: -2,
  code: 'ENOENT',
  syscall: 'uv_cwd'
}

Node.js v17.7.2

    helper.js

    function now() {
    return new Date().toLocaleDateString()
};
const pg = require('pg')
const config = {
    host: 'dev11-********************.us-east-2.rds.amazonaws.com',
    port: '5432',
    password: 'K****************************9',
    user: 'd**o',
    database: 'ats_whitelabel'
}

const pool = new Pool(config);
pool.connect((err, client, done) => {
    if (err) throw err;
    client.query('SELECT  username, email, registration_status, brand_id, balance FROM  accounts WHERE brand_id = 3', (err, res) => {
        if (err)
            console.log(err.stack);
        else {
            console.log(res.rows);
        }
        pool.end()
    })
})

    content.handlebars

    <h1> Player KYC Status  </h1>
    <td>
        <br> Created: {{now}}
        <br> Brand: {{brand_id}}
    </td>
<div>
    <span>Username: {{username}}</span>
    <span>Email: {{email}}</span>
    <span>Registration Status: {{registration_status}}</span>
</div>

    Report "SDGPN_Player_Status" render failed.

Error when evaluating engine handlebars for template /PD-report/SDGPN_Player_Status
require of "pg" module has been blocked. To be able to require custom modules you need to add to configuration { "allowLocalFilesAccess": true } or enable just specific module using { sandbox: { allowedModules": ["pg"] }

(sandbox.js line 4:12)

  667 |     return new Date().toLocaleDateString()
  668 | };
> 669 | const pg = require('pg')
      |            ^
  670 | const config = {
  671 |     host: 'dev11-ro-replica.ca8zbbworcoy.us-east-2.rds.amazonaws.com',
  672 |     port: '5432',


Error: require of "pg" module has been blocked. To be able to require custom modules you need to add to configuration { "allowLocalFilesAccess": true } or enable just specific module using { sandbox: { allowedModules": ["pg"] }
    at _require (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/sandbox/safeSandbox.js:78:19)
    at require (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/sandbox/safeSandbox.js:111:21)
    at BaseHandler.apply (/home/ubuntu/jsreportapp/node_modules/vm2/lib/bridge.js:477:11)
    at sandbox.js:669:12
    0

  • if i add allowedModules": 'pg' i get different error

    unction now() {
    return new Date().toLocaleDateString()
};
const pg = require('pg')
const config = {
    host: 'dev11-ro-replica.ca8zbbworcoy.us-east-2.rds.amazonaws.com',
    port: '5432',
    password: 'KKpu3wnCrWKBUrSghtqz9Yt2Kn6GkMc9',
    user: 'domo',
    database: 'ats_whitelabel',
    allowedModules": 'pg'
}

    node:internal/bootstrap/switches/does_own_process_state:126
    cachedCwd = rawMethods.cwd();
                           ^

Error: ENOENT: no such file or directory, uv_cwd
    at process.wrappedCwd [as cwd] (node:internal/bootstrap/switches/does_own_process_state:126:28)
    at node:path:1082:24
    at Object.resolve (node:path:1096:39)
    at resolveMainPath (node:internal/modules/run_main:19:40)
    at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:71:24)
    at node:internal/main/run_main_module:17:47 {
  errno: -2,
  code: 'ENOENT',
  syscall: 'uv_cwd'
}

    report run error

    
Error when evaluating engine handlebars for template /PD-report/SDGPN_Player_Status
Unterminated string constant (676:18)
SyntaxError: Unterminated string constant (676:18)
    at Parser.pp$4.raise (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:3460:15)
    at Parser.pp.readString (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:5318:35)
    at Parser.pp.getTokenFromCode (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:5091:19)
    at Parser.pp.readToken (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:4815:17)
    at Parser.pp.nextToken (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:4806:17)
    at Parser.pp.next (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:4767:10)
    at Parser.pp$5.parseIdent (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:3393:10)
    at Parser.pp$5.parsePropertyName (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:3195:111)
    at Parser.pp$5.parseProperty (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:3122:10)
    at Parser.pp$5.parseObj (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:3076:23)
    0 

  • ebug+0Rendering engine handlebars
warn+35Error when processing render request 29 Error when evaluating engine handlebars for template /PD-report/SDGPN_Player_Status
require of "pg" module has been blocked. To be able to require custom modules you need to add to configuration { "allowLocalFilesAccess": true } or enable just specific module using { sandbox: { allowedModules": ["pg"] }

(sandbox.js line 4:12)

  667 |     return new Date().toLocaleDateString()
  668 | };
> 669 | const pg = require('pg')
      |            ^
  670 | const config = {
  671 |     host: 'dev11-ro-replica.ca8zbbworcoy.us-east-2.rds.amazonaws.com',
  672 |     port: '5432',

 Error: require of "pg" module has been blocked. To be able to require custom modules you need to add to configuration { "allowLocalFilesAccess": true } or enable just specific module using { sandbox: { allowedModules": ["pg"] }
    at _require (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/sandbox/safeSandbox.js:78:19)
    at require (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/sandbox/safeSandbox.js:111:21)
    at BaseHandler.apply (/home/ubuntu/jsreportapp/node_modules/vm2/lib/bridge.js:477:11)
    at sandbox.js:669:12
warn+0Rendering request 29 finished with error in 38 ms
    0

  • or if you use

    function now() {
    return new Date().toLocaleDateString()
};
import pg from 'pg';
const config = {
    host: 'dev11-ro-replica.ca8zbbworcoy.us-east-2.rds.amazonaws.com',
    port: '5432',
    password: 'KKpu3wnCrWKBUrSghtqz9Yt2Kn6GkMc9',
    user: 'domo',
    database: 'ats_whitelabel',
    allowedModules: 'pg'
}

    error

    Report "SDGPN_Player_Status" render failed.

Error when evaluating engine handlebars for template /PD-report/SDGPN_Player_Status
'import' and 'export' may only appear at the top level (669:0)
SyntaxError: 'import' and 'export' may only appear at the top level (669:0)
    at Parser.pp$4.raise (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:3460:15)
    at Parser.pp$8.parseStatement (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:928:18)
    at Parser.pp$8.parseBlock (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:1232:23)
    at Parser.pp$5.parseFunctionBody (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:3286:24)
    at Parser.pp$5.parseArrowExpression (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:3247:10)
    at Parser.pp$5.parseSubscript (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:2698:21)
    at Parser.pp$5.parseSubscripts (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:2646:26)
    at Parser.pp$5.parseExprSubscripts (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:2630:23)
    at Parser.pp$5.parseMaybeUnary (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:2593:19)
    at Parser.pp$5.parseExprOps (/home/ubuntu/jsreportapp/node_modules/acorn/dist/acorn.js:2520:21)
    0

  • if i add allowedModules": 'pg' i get different error

    You need to set "allowLocalFilesAccess": true or

    { sandbox: { allowedModules": ["pg"] }

    'import' and 'export' may only appear at the top level (669:0)

    Use require instead...

    Generally, you should connect to the database and fetch data rather in the beforeRender part of the custom script.
    https://jsreport.net/learn/scripts

    0

  • I have added

    { sandbox: { allowedModules": ["pg"] }

    "extensions": {
      "authentication": {
        "cookieSession": {
          "secret": "agaQ_9j0eedP2quZ"
        },
        "admin": {
          "username": "sdadmin",
          "password": "T*****g"
        },
        "enabled": true
      },
      "sandbox": { 
        "allowedModules": ["pg"]
      },

    error

    warn: Error when processing render request 1 Error when evaluating engine handlebars for template /PD-report/SDGPN_Player_Status
require of "pg" module has been blocked. To be able to require custom modules you need to add to configuration { "allowLocalFilesAccess": true } or enable just specific module using { sandbox: { allowedModules": ["pg"] }

(sandbox.js line 4:12)

  667 |     return new Date().toLocaleDateString()
  668 | };
> 669 | const pg = require('pg')
      |            ^
  670 | const config = {
  671 |     host: 'dev11-ro-replica.ca8zbbworcoy.us-east-2.rds.amazonaws.com',
  672 |     port: '5432',

 Error: require of "pg" module has been blocked. To be able to require custom modules you need to add to configuration { "allowLocalFilesAccess": true } or enable just specific module using { sandbox: { allowedModules": ["pg"] }
    at _require (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/sandbox/safeSandbox.js:78:19)
    at require (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/sandbox/safeSandbox.js:111:21)
    at BaseHandler.apply (/home/ubuntu/jsreportapp/node_modules/vm2/lib/bridge.js:477:11)
    at sandbox.js:669:12 rootId=gz8c5ae7pjimdx0, id=gz8c5ae7pjimdx0
2022-03-28T11:36:20.504Z - warn: Rendering request 1 finished with error in 779 ms rootId=gz8c5ae7pjimdx0, id=gz8c5ae7pjimdx0

    What is the best approach to getting the Postgres data from rds? FYI some tables will have millions of records.

    0

  • It should be in the root, not nested in the "extensions"

     "sandbox": { 
        "allowedModules": ["pg"]
      }
    0

  • Go it thank that worked.

    Now have issue it does not like pool

    Report "SDGPN_Player_Status" render failed.

Error when evaluating engine handlebars for template /PD-report/SDGPN_Player_Status
Pool is not defined

(sandbox.js line 14:14)

  677 | }
  678 |
> 679 | const pool = new Pool(config);
      |              ^
  680 | pool.connect((err, client, done) => {
  681 |     if (err) throw err;
  682 |     client.query('SELECT  username, email, registration_status, brand_id, balance FROM  accounts WHERE brand_id = 3', (err, res) => {


ReferenceError: Pool is not defined
    at sandbox.js:679:14
    0

  • This is a very basic example that works for me

    const { Client } = require('pg')
const client = new Client({
    host: "localhost",
    port: 5432,
    database: "jsreport",
    user: "postgres",
    password: "password"
})

async function beforeRender (req) {
    await client.connect()
    const res = await client.query('SELECT $1::text as message', ['Hello world!'])
    req.data.rows = res.rows
    await client.end()
}
    0

  • With out pool that worked thanks

    0
Log in to reply
 

Looks like your connection to jsreport forum was lost, please wait while we try to reconnect.