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

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


  • 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.