Are there Node.js examples for how to connect to AWS Aurora Serverless PostgreSQL via Lambda

Question

I've set up an AWS Aurora Serverless PostgreSQL DB. I've also got API Gateway running endpoints to Lambda functions. Right now the Lambda functions are connecting to DynamoDB, but RDS is going to work better for my use case.

I've scoured the interwebs for hours but can't seem to find an example of how to access my Aurora Serverless DB via Lambda with Node.js. I'm not sure what imports I need in my function and I'm having a difficult time finding the right methods in the API as well.

Just a basic Node.js example to get me started would be amazingly helpful.

Thanks in advance.

Have you looked at pg-promise(http://vitaly-t.github.io/pg-promise/index.html) or https://stackoverflow.com/questions/34382796/where-should-i-initialize-pg-promise — Shuchi Sethi, Oct 11 '19 at 00:50
You would use the `RDSDataService` client in the AWS SDK https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/RDSDataService.html#executeSql-property — Mark B, Oct 11 '19 at 11:29
The `RDSDataService` API isn't supported for Serverless Aurora v2. — Enrique Avina, Feb 22 '23 at 16:15

score 2 · Answer 1 · answered Jun 22 '21 at 15:55

You should be able to connect to your Aurora instance with the same tools/client you are using to connect to your PostgreSQL database. For example when you navigate to your cluster's Connectivity and security tab you are shown an Endpoint name - you can use this and the port number in the connection string of any script.

Connecting to an Amazon Aurora PostgreSQL DB cluster Creating a DB cluster and connecting to a database on an Aurora PostgreSQL DB cluster

score 1 · Answer 2 · answered Oct 14 '19 at 02:23

I got a response on the AWS Developer Forums that was exactly what I needed to get started.

Evidently to use the PostgreSQL connector you have to build the function locally and import rather than use the online Lambda console.

Here is the example code provided by MrK: https://forums.aws.amazon.com/message.jspa?messageID=919394

//this imports the postgres connector into the file so it can be used
const { Client } = require('pg');

//instantiates a client to connect to the database, connection settings are passed in
const client = new Client({
    user: '<your db username>',
    host: '<your endpoint>',
    database: '<your database name>',
    password: '<your database password>',
    port: 5432
});

//the lambda funtion code
exports.handler = async (event, context, callback) => {

    try {

        await client.connect();
        callback(null, "Connected Successfully");
        //your code here

    } catch (err) {

        callback(null, "Failed to Connect Successfully");
        throw err;
        //error message
    }

    client.end();

};

This is not the way to connect to Aurora Serverless. This works for connecting to an ordinary RDS instance. It is not a good solution however, since it would create a connection in each invocation, which is slow and quickly makes the DB run out of available connections if you have lots of users. To connect to Aurora Serverless, use the Data API from RDSDataService in the AWS SDK as mentioned by Mark B in a comment on the question itself. — Henrik Hansson, Aug 31 '20 at 17:03

score 1 · Answer 3 · answered Dec 15 '21 at 08:13

Somewhat old question but the answer is to either use the AWS.RDSDataService() from the AWS SDK. (See samples/setup here: https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/RDSDataService.html)

Or to use a "wrapper" package which we opted for with the data-api-client.

const dataApiClient = require('data-api-client');

const db = dataApiClient({
  secretArn: process.env.RDS_AURORA_DATA_API_SECRET_ARN || '',
  resourceArn: process.env.RDS_AURORA_DATA_API_ARN || '',
  database: process.env.RDS_AURORA_DATA_API_DB || '',
  region: 'eu-west-1',
  options: {
    endpoint:
      process.env.IS_OFFLINE
        ? process.env.AURORA_LOCAL_ENDPOINT || 'http://0.0.0.0:8080'
        : null
  }
});

If you run a local DB and want to develop locally the options.endpoint to run locally works great with the Docker container koxudaxi/local-data-api. Make sure PG Server is running and then run the Docker cmd:

docker run -d --rm -it --name my-data-api -p 8080:80 -e ENGINE=PostgreSQLJDBC -e POSTGRES_HOST=host.docker.internal -e POSTGRES_PORT=
5432 -e POSTGRES_USER=qipdb_user -e POSTGRES_PASSWORD=password -e RESOURCE_ARN=arn:aws:rds:eu-west-1:00000000000:cluster:dummy -e SECRET_ARN=arn:aws:
secretsmanager:eu-west-1:00000000000:secret:dummy koxudaxi/local-data-api

As the Data API only takes SQL we opted to use a "disconnected" knex instance to produce the SQL (this is completely optional though as normal SQL works directly if preferred):

// knex is only use to produce SQL, it does not have any DB connection!
const connKnex = require('knex');
const knex = connKnex({ client: 'pg' });

Then you can use knex to get the SQL, like:

async function getSomethingFromDB() {
const sql = await knex
    .select('aColumn')
    .from('aTable')
    .where({
      someColumn: 'isSomething'
    })
    .toString();
  const obj = await db.query(sql);

  return !obj.records[0] ? null : obj.records[0];
}

Are there Node.js examples for how to connect to AWS Aurora Serverless PostgreSQL via Lambda

3 Answers3