arun-gupta/lambda-rds-mysql

Shows how to use a Lambda function to store data in an RDS instance

AWS Lambda, RDS and Java

This project shows how to manipulate a database table in MySQL from an AWS Lambda function. The MySQL instance is hosted using Amazon Relational Database Service (RDS). AWS Lambda function is written using Java and packaged using Maven. Serverless Application Model (SAM) is used to deploy the Lambda function. AWS CLI is used to invoke the function.

Create and Configure RDS

1. Create RDS instance

   aws rds create-db-instance \
       --db-instance-identifier LambdaMySQLTest \
       --db-instance-class db.r3.large \
       --engine MySQL \
       --port 3006 \
       --allocated-storage 5 \
       --db-name ExampleDB \
       --master-username master \
       --master-user-password master123 \
       --backup-retention-period 3

2. Create inbound role for the security group:

   1. Get security group id:

      aws rds describe-db-instances | jq ".DBInstances[].VpcSecurityGroups[].VpcSecurityGroupId"

   2. Add inbound role:

      aws ec2 authorize-security-group-ingress --group-id <group-id> --protocol all --port 3006 --cidr 0.0.0.0/0

3. Get instance id:

   aws rds describe-db-instances | jq ".DBInstances[0].Endpoint.Address"

4. Install MySQL client: brew install mysql

5. Create a table and add some rows mysql -h <endpoint> -P 3306 -u master -pmaster123:

   mysql: [Warning] Using a password on the command line interface can be insecure.
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 43
   Server version: 5.6.35-log MySQL Community Server (GPL)
   
   Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql> use ExampleDB;
   Database changed
   mysql> CREATE TABLE EMPLOYEE(id int NOT NULL, name VARCHAR(20), PRIMARY KEY(id));
   Query OK, 0 rows affected (0.03 sec)
   
   mysql> INSERT INTO EMPLOYEE (id, name) VALUES (1, "Penny");
   Query OK, 1 row affected (0.02 sec)
   
   mysql> INSERT INTO EMPLOYEE (id, name) VALUES (2, "Sheldon");
   Query OK, 1 row affected (0.02 sec)
   
   mysql> INSERT INTO EMPLOYEE (id, name) VALUES (3, "Leonard");
   Query OK, 1 row affected (0.02 sec)
   
   mysql> INSERT INTO EMPLOYEE (id, name) VALUES (4, "Howard");
   Query OK, 1 row affected (0.02 sec)
   
   mysql> INSERT INTO EMPLOYEE (id, name) VALUES (5, "Raj");
   Query OK, 1 row affected (0.02 sec)
   
   mysql> INSERT INTO EMPLOYEE (id, name) VALUES (6, "Amy");
   Query OK, 1 row affected (0.02 sec)
   
   mysql> INSERT INTO EMPLOYEE (id, name) VALUES (7, "Bernadette");
   Query OK, 1 row affected (0.02 sec)
   
   mysql> exit;
   Bye

Package and Deploy Lambda Function

1. Create a package: mvn package

2. Upload to S3: mvn install

3. Deploy Lambda: aws cloudformation deploy --template-file app.yml --stack-name employee

4. Invoke Lambda:

   aws lambda invoke \
   --function-name Employee \
   --payload '{ "id": 10, "name": "Smith" }' \
   employee.out

5. Delete Lambda: aws cloudformation delete-stack --stack-name employee

lambda-rds-mysql's Issues

Connect AWS-RDS Mysql Using AWS

I Have Two Questions to ASk

1. Why Do we need EC2 if we Using AWS-RDS and AWS Lambda..?
2. I've been Using AWS-RDS and Lambda Using API Gateway and it's Working Fine.Then I wanted to Add Hibernate for my Appication.i Tried all your approach without using EC2 and Session not Opens.It just time Out without any Error After 15 sec.

hibernate.cfg.xml location

I have the following error

org.hibernate.internal.util.config.ConfigurationException: Could not locate cfg.xml resource [hibernate.cfg.xml]

My config is hibernate.cfg.xml in src/main/resources just like you. Why is this occurring?

Getting config issue

Hi Arun,

I have modified the cfg.xml to work with postgres and I see the below error on Function test

{ "errorMessage": "Unknown entity: org.sample.serverless.aws.rds.Employee", "errorType": "org.hibernate.MappingException", "stackTrace": [ "org.hibernate.metamodel.internal.MetamodelImpl.entityPersister(MetamodelImpl.java:620)", "org.hibernate.internal.SessionImpl.getEntityPersister(SessionImpl.java:1639)", "org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:108)", "org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:192)", "org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:38)", "org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:177)", "org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:32)", "org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:73)", "org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:694)", "org.hibernate.internal.SessionImpl.save(SessionImpl.java:686)", "org.hibernate.internal.SessionImpl.save(SessionImpl.java:681)", "org.sample.serverless.aws.rds.EmployeeHandler.handleRequest(EmployeeHandler.java:21)", "org.sample.serverless.aws.rds.EmployeeHandler.handleRequest(EmployeeHandler.java:11)" ]

I see the employee class in the following <mapping class="org.sample.serverless.aws.rds.Employee"></mapping> Can you please let me know if there is something I am missing