Odoo POS Product Loading Performance Improvement with AWS Lambda

Odoo POS works well and loads quickly if the number of products are reasonable. But if you are looking to load hundreds of products the Odoo POS can slow down and each session could take more than 15 mins to load first time. In order to solve this performance issue we have used power of the Amazon Cloud to make this loading faster. We use the AWS Lambda web Service along with Node.js. The following describes how we achieved better performance and load times using AWS Lambda

AWS Lambda is an event-driven, Serverless computing platform provided by Amazon as a part of the Amazon Web Services. It is a compute service that runs code in response to events and automatically manages the compute resources required by that code.

The purpose of Lambda, as compared to AWS EC2, is to simplify building smaller, on-demand applications that are responsive to events and new information. AWS targets starting a Lambda instance within milliseconds of an event. Node.js, Python, Java and C# through .NET Core are all officially supported as of 2016.

Basic Lambda Function Creation in AWS

Below are the steps and configuration which will be helpful for creating a basic lambda function:

1. Select the Lambda service from the services listed in AWS service after login in AWS browser console.
2. After selecting Lambda service you will redirected to lambda page. Here you can see all the lambda functions created using this aws account.
   
3. Click on the “Create a Lambda function” button. You will be redirected to another page, where you will find the example lambda functions available by-default in it. Select “Blank Function”.
   
4. After selecting “Blank function”, you will be redirected to another page. Click on “Next” button.
5. After that you can see the lambda console where you have to write your code. First write the name of the lambda function. And then select the language in which you want to write the lambda function.
   
6. After writing your code you have to configure your lambda function according to your requirement. Let the Handler value be same. Select the Role from dropdown.
   
7. You can also configure the execution time and memory limit to be used for execution. And you can do this in “Advanced setting”. Set it as below:
   
8. You can test your function and check the log in your lambda console.

AWS Lambda Connection to Postgres Database and Retrieving the Product file 

Our main purpose to use lambda function is to improve the performance of generating a dump data file from postgres database. I have written aws lambda function in NodeJs language. For this I have used the available postgres library to connect to postgres. Basically we don’t need any folder or other file to execute simple lambda function. But since we are using external NodeJs library. We have the following folder structure:

• Create one folder in your system. And add keep all your files and module folder inside it.
• Main file is index.js file for which lambda searches whenever we upload a zip folder in lambda console. This is a mandatory file in Lambda function.
• Next is our connection file (connection.js). We have created this separate file for reusability, so that we can access this file from another files wherever we want a db connection.
• Next we have node_modules folder. This folder is created when we use any NodeJs library.

To upload all this in lambda, zip all your custom files, folders and index.js file and upload that zip file in Lambda console.

Below is the main index.js file which lambda treat as a default file to be executed :

      var pool = require('./connection');
       var AWS = require('aws-sdk');

AWS.config.update({accessKeyId: "yOURaCCESSkEYiD",secretAccessKey: yOURaCCESSsECRETkEY'});
var s3bucket = new AWS.S3({params: {Bucket: 'BucketName'}});

 exports.handler = (event, context, callback) => {
              pool.connect(function(err, client, done)  {
                 if(err) {
           callback('error fetching client from pool' + err, null);
            }
client.query('SELECT * FROM product_product ORDER BY id', function(err, result) {
               if(err)
                     { callback(err, null);
             }else {
           var params = {
       Key: 'sbarro_5.json',
       Body: JSON.stringify(result.rows)
   };

          s3bucket.upload(params, function (err, res) {
    if(err)
             callback("Error in uploading file on s3 due to " + err, null);
    else
            callback(null, "File successfully uploaded.");
      });
     }
         client.end(function (err)
 { if (err) callback("Error in uploading file on s3 due to " + err, null);
      });
 });
      });
};

In the above code, I have written code to execute our query which we want to run in postgres database and to generate a file after reading data from database.

Code breakdown description is as below:

• In the first line, I have included the connection file which we have written in NodeJs to connect with our postgres db.
• In the second line, I have included aws-sdk. This library is pre-installed in lambda environment. I have used it to use AWS S3 bucket, where we will store our generated file.
• After that, I have provided the S3 bucket credentials to connect.
• “pool.query('SELECT * FROM product_product ORDER BY id', function (err, result) “ is the important function to run a query to fetch data from database.
• After fetching data from database in variable result.rows, I have converted it into Json format and passed data to json file which I have stored in S3 Bucket.
• Using “s3bucket.upload(params, function (err, res)” function to upload the json file in S3bucket.
• Finally, after completing the execution, we use client.end(function (err) {} function to end the PostGres Connection and exit our Lambda function execution.

const pg = require('pg');
     var config = {
      database: DBname,
        user: Username,
        password: ‘Password’,
        host: 'IP', port: 'XXXX',
        idleTimeoutMillis: 1500000,
 };

        const pool = new pg.Pool(config);
               pool.on('error', function (err, client) {
                console.error('idle client error', err.message, err.stack);
          });

       module.exports.query = function (text, values, callback) {
                               return pool.query(text, values, callback);
           };

        module.exports.connect = function (callback) {
                      return pool.connect(callback);
           }; 

Code breakdown description is as below:

• In first line, I have included the “pg” library to connect postgres from NodeJs.
• In variable config, I have mentioned all the credentials which we need to change for our POS database connection.
• And rest code is to connect with database and it will throw an error if we have any issue in connection.

NOTE: For connecting Postgres in NodeJs, we don’t need to make any changes in Postgres database. We just need the above mentioned credentials to connect it.

HOW TO CALL AWS LAMBDA FUNCTION IN YOUR APPLICATION

We can use lambda function in different application by integrating Lambda function with another AWS microservice API GATEWAY. By integrating it with API gateway, we can use our single lambda function into multiple application where we need same functionality as our written lambda function. To integrate lambda function with API gateway, you can follow the below steps:

1. Select the API gateway service from the services listed in AWS.
2. After that click on “Create API” button. You will get a page where you have to name your API. After that click on “Create API” button
   
3. Select “Create Method” option from Action dropdown. Then select “Get method” from dropdown in blue color. You will see the below screen.After that Keep “Integration Type” as Lambda Function. Select your Lambda region from dropdown, then you will get option to select your lambda function name. Then Save your Get method.
   
4. After saving GET method, you will see the below screen. After that select the “Deploy API” option from the ACTION dropdown in the top. Once you will click on “Deploy API” option, you will see the popup. Create a deployment stage.
   
5. Once you will deploy your API, you will get the URL through which you can access your lambda function.
   

NOTE: Now we can use our lambda function in any application as an API.

HOW TO USE AWS LAMBDA FUNCTION IN ODOO POS APPLICATION

We have prepared a lambda function to read the Postgres database data and generate a json file of that data. To use this functionality in odoo we can replace the code (reading data from postgres, converting data to json and generating json file) with our API. Further to enhance the functionality, we can use xmlrpc connection to implement validations and to use odoo methods in lambda function.