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



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



  • 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



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



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



  • 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


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


  • 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
    


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


  • 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



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



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

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


  • 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


  • 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()
    }
    


  • With out pool that worked thanks


Log in to reply
 

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