Connect Oracle DB from AWS Lambda Using Node.js

In this article, You will find step by step guide to connect to Oracle DB using the library oracledb-for-lambda. The library will act as an Oracle client to use inside of Lambda.

At the time of writing this articles, the following library versions were used.

1. Node – 8.10
2. oracledb-for-lambda: “^1.9.3-8a

1. Creating bundle

The size of the library oracledb_for_lambda is more than 70 MB which is quite high for you to compile and upload to lambda every single time. Instead of that, You can bundle the lib and add it as a layer. So that we can include the layer directly to Lambda.

Creating this bundle will be our first step. You need to create the below folder structure before adding it to the layer.

First, Create a Node Js project using the below command

npm init

Then Install the oracledb_for_lambda client by executing the below line

npm i oracledb-for-lambda

Now, In the Project folder, Create a folder named nodejs and You need to move the node_modules folder into this nodejs folder. Then, Copy the lib folder inside /node_modules/oracledb-for-lambda and paste it outside in the main project directory.

Finally, you will get a folder structure like the below image. That’s it, Zip the files inside the folder and Upload the Zip to S3.

2. Creating the Layer

As a next step, We are going to create the layer. Once the file is uploaded and available in S3, You can create a layer as described in the below screenshot.

Navigate to Layers and Click Create Layer button.

Then the Layer Configuration form opens. Here Provide the S3 link URL in which the Zip file is uploaded and create the layer.

In the above screenshot, a layer with the name oracle-client-node is created

3. Adding the layer to Lambda

After layer creation, You needs to add it to the Lambda. Open the Lamba function and Click Layers.

In the Layers section, click the Add a layer button and Select the oracle-client-node layer created in the previous step.

Once the layer is added to the Lambda function, You can view it in the Layers section as below.

4. Adding Hostalias

Next, You need to add host aliases pointing a hostname to localhost

But Lambda is something different here. The container (and the resources used by it) that runs our function is managed completely by AWS. It is made live when an event takes place and is turned off if it is not being used. If additional requests are made while the original event is being served, a new container is brought up to serve a request.

So, The hostname may change during every execution. For every execution the ephemeral disk space is available in the form of the /tmp directory. We can only use this space for temporary storage since subsequent invocations will not have access to this.

str_host = os.hostname() + ' localhost\n';
fs.writeFileSync(process.env.HOSTALIASES,str_host , function(err){
    if(err) throw err;
});

Using the above code we are getting the hostname of the execution container (and the resources used by lambda) and add hostaliases in the file /tmp/HOSTALIASES

Also, add the HOSTALIASES key-value in Environment Variables like below

5. Connecting to Oracle DB

Now we can use the below code to connect to Oracle DB from Lambda function.

'use strict';
var os = require('os');
var fs = require('fs');
var oracledb = require('oracledb-for-lambda');
exports.handler = async (event, context) => {
    let str_host = os.hostname() + ' localhost\n';
    fs.writeFileSync(process.env.HOSTALIASES, str_host, function(err) {
        if (err) throw err;
    });
    var connAttr = {
        user: process.env.USERNAME,
        password: process.env.PASSWORD,
        connectString: process.env.CONNECTION_STRING
    };

    const promise = new Promise(function(resolve, reject) {
        oracledb.getConnection(connAttr, function(err, connection) {
            if (err) {
                reject({
                    status: "ERROR"
                });
            }
            resolve({
                status: "SUCCESS"
            });
        });
    });
    return promise;
}

In the above code, the values of USERNAME, PASSWORD, HOSTALIASES, and CONNECTION_STRING can be stored and retrieved from the environment variables.

6. Inserting data into oracle DB

Once the database connection is successful, You can use the below Node Js snippet to insert data to the database.

'use strict';
var os = require('os');
var fs = require('fs');
var oracledb = require('oracledb-for-lambda');

exports.handler = async (event, context) => {
    let str_host = os.hostname() + ' localhost\n';
    fs.writeFileSync(process.env.HOSTALIASES, str_host, function(err) {
        if (err) throw err;
    });

    var connAttr = {
        user: process.env.USERNAME,
        password: process.env.PASSWORD,
        connectString: process.env.CONNECTION_STRING
    };

    const promise = new Promise(async function(resolve, reject) {
        try {
            let connection = await oracledb.getConnection(connAttr); //get connection
            let resultData = await connection.execute(
                "INSERT INTO TABLE_NAME(COLUMN1,COLUMN2) VALUES (:1, :2)", [value1, value2], {
                    autoCommit: true
                },
                function(err, result) {
                    if (err) {
                        resolve({
                            status: "ERROR"
                        })
                    } else {
                        resolve({
                            status: "SUCCESS"
                        })
                    }
                });
        } catch (err) {
            console.log("DB Exception " + err)
            resolve({
                status: "ERROR"
            })
        }
    });
    return promise;
}

7. Fetching the values from oracle DB

Below snippet is used to fetch values from the database.

const promise = new Promise(async function(resolve, reject) {

    try {
        let connection = await oracledb.getConnection(connAttr); //get connection
        let resultData = await connection.execute(
            "SELECT * FROM TABLE_NAME WHERE COLUMN1 = :1)", [value1],
            function(err, result) {
                if (err) {
                    resolve({
                        status: "ERROR"
                    })
                } else {
                    resolve({
                        status: "SUCCESS"
                    })
                }
            });
    } catch (err) {
        console.log("DB Exception " + err)
        resolve({
            status: "ERROR"
        })
    }
});
return promise;
});