Background
In one of the previous post we saw how serverless code works with AWS Lambda and API gateway.
In this post we will see how we can configure Lambda function to connect to RDS instance and run queries on it. RDS is AWS service for Relational database service. It offers multiple databases like -- mysql
- aurora
- postgres
- oracle etc
https://www.pgadmin.org/ : If you want a GUI based client to test postgres on local try using pgAdmin.
How to connect to postgres RDS from AWSa Lambda
Code for Lambda function to connect to RDS is as follows -
'use strict';
const pg = require('pg');
const async = require('async');
const databaseUser = process.env.DB_USER;
const databasePassword = process.env.DB_PASSWORD;
const databaseName = process.env.DB_DB_NAME;
const databaseHost = process.env.DB_HOST;
const databasePort = process.env.DB_PORT;
const databaseMaxCon = process.env.DB_MAX_CONNECTIONS;
exports.handler = (event, context) => {
console.log('Received event : ' + JSON.stringify(event) + ' at ' + new Date());
let dbConfig = {
user: databaseUser,
password: databasePassword,
database: databaseName,
host: databaseHost,
port: databasePort,
max: databaseMaxCon
};
let pool = new pg.Pool(dbConfig);
pool.connect(function(err, client, done) {
if (err) {
console.error('Error connecting to pg server' + err.stack);
callback(err);
} else {
console.log('Connection established with pg db server');
client.query("select * from employee", (err, res) => {
if (err) {
console.error('Error executing query on pg db' + err.stack);
callback(err);
} else {
console.log('Got query results : ' + res.rows.length);
async.each(res.rows, function(empRecord) {
console.log(empRecord.name);
});
}
client.release();
pool.end();
console.log('Ending lambda at ' + new Date());
});
}
});
};
}
Explanation
Here we are using postgres library called pg. You can install this module using -
- npm install pg
- In first part we create a pool of connection giving required parameters to connect to postgres DB. Notice how we are reading these parameters from environment variables.
- Next we call connect on it and pass a callback to get the connection when successful
- In the callback we can execute client.query() and pass a callback to get rows of data we need for the employee table.
- Finally we iterate over each record using async and print the employee record name.
- Release the client when you are done with that particular connection
- You can end the pool when all the DB operations are done.
AWS specific notes
- By default AWS Lambda has internet connection. So it can access web resources.
- Lambda by default does not have to AWS services running in private subnet.
- If you want to access services in private subnet eg. RDS running in private subnet then you need to configure the VPC, private subnet to run lambda in and security group is network section of Lambda.
- However once you do this you will no longer have access to internet (since it is run in private subnet now).
- Now if you still need internet access then you need to spin up a NAT gateway or a NAT instance in public subnet and make a route from private subnet to this NAT.
- Note if you are encrypting lambda environment variable using KMS you will require internet access (KMS needs that). So if your RDS is running in private subnet you need to follow above steps to make it work. Else you are going to get bunch of timeout exceptions.
- Also note maximum run time of Lambda is 5 mins. So make sure your lambda execution completes withing that time. You should probably limit the queries returned by DB and process that much in one Lambda execution.
- You can also run lambda as a batch job (using cron expression) from cloud watch.
while using postgreSQL with Express.js is there a way to create a pool globally once and use the same pool again and again?
ReplyDelete