Can't connect to PostgreSQL from DO function

Question

Hello everyone,

I am trying to create a simple function that should return data from my database. However, I got :

2023-02-20T13:21:13.802551358Z stdout: SyntaxError: Unexpected reserved word

My code is:

function main(args) {
    const { Client } = require('pg');

    // create a new PostgreSQL client instance
    const client = new Client({
      user: 'ABC',
      host: 'ABC',
      database: 'defaultdb',
      password: 'ABC',
      port: 25060, // or the port that your Postgres instance is using
    });

    try {
      // connect to the database
      await client.connect();

      // perform some database operation, e.g. SELECT * FROM mytable
      const res = await client.query('SELECT * FROM order');

      // return the results as the response of the Lambda function
      return {
        statusCode: 200,
        body: JSON.stringify(res.rows),
      };
    } catch (err) {
      // handle any errors that occur during database connection or operations
      return {
        statusCode: 500,
        body: JSON.stringify({ message: err.message }),
      };
    } finally {
      // always close the database connection when done
      await client.end();
    }
}

I would be grateful if someone helps me to sort this out

Matt Welke · Answer

I was able to reproduce your issue by creating a new Node.js function project with doctl and replacing the contents of hello.js with your code.

Our error logs will show the following when this code is deployed with our latest Node.js runtime (18):

"2023-02-21T17:49:31.472086812Z stdout: SyntaxError: Unexpected reserved word",
"2023-02-21T17:49:31.472133574Z stdout:     at Loader.moduleStrategy (internal/modules/esm/translators.js:145:18)",
"2023-02-21T17:49:31.472140917Z stdout:     at async link (internal/modules/esm/module_job.js:67:21)"

This error message isn’t as clear as the error message displayed if you try to run the code with Node.js directly (instead of how our system tries to import your code). When I run node hello.js, I get:

> node packages/sample/hello/hello.js 
/home/<name>/functions-pg-test/packages/sample/hello/hello.js:15
      await client.connect();
      ^^^^^

SyntaxError: await is only valid in async functions and the top level bodies of modules
    at internalCompileFunction (node:internal/vm:73:18)
    at wrapSafe (node:internal/modules/cjs/loader:1176:20)
    at Module._compile (node:internal/modules/cjs/loader:1218:27)
    at Module._extensions..js (node:internal/modules/cjs/loader:1308:10)
    at Module.load (node:internal/modules/cjs/loader:1117:32)
    at Module._load (node:internal/modules/cjs/loader:958:12)
    at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:81:12)
    at node:internal/main/run_main_module:23:47

Node.js v18.14.1

The solution is for you to fix your code to make the main function async. For example, this is a valid Node.js function for DigitalOcean Functions:

async function main(event, context) {
    // your code here
}

However, you will need to deploy your code as a module (using exports.main = ) because you will need to add a package.json file that includes the pg dependency you want to use, because pg isn’t included in the Node.js runtime.

So, when you make this change, the beginning of your code will look like this:

exports.main = async function (args) {
    const { Client } = require('pg');

    // create a new PostgreSQL client instance
    const client = new Client({
    
    // your code here

We’re in the process of updating our Functions documentation to better describe the function signature (including how to enable async programming, like we’re doing here) and have more examples. Keep an eye on our docs for more help.

As an aside, when you’ve got your code working, consider optimizing how long your PostgreSQL operations will take by reusing your connections instead of opening and closing them during each invocation. You can do that by moving your client variable out of the function body and into the top level of the JS file.

Also, if you do change to re-using PostgreSQL connections, consider using a PostgreSQL connection pooling library, like the pool available from the NPM package pg. This is because the pools have logic to recreate connections that get closed as needed.

Question

Can't connect to PostgreSQL from DO function

Try DigitalOcean for free

Popular Topics