How to connect AWS Lambda to a Database in a Private subnet in a VPC ?

Overview
By Default, lambda function is launched outside our VPC. Therefore it can not access resources such as RDS, ElastiCache, internal ALB, etc in the VPC. So in order to allow lambda access our private resources, we have to define VPC ID, subnets, Security groups. Lambda will create elastic network interface(ENI) in private subnet which allows it connectivity.

In this small project we will understand
1)AWS lambda connectivity with the database in a private subnet.
2)How to install and use MariaDB database server on EC2 instance.
3)How to store and use the Database credentials securely in AWS secret Manager.
4)How to add pymysql library as lambda layer.

Note: You can simple launch RDS databse in private subnet instead of installing it on EC2 instance. But here I wanted to try it on EC2(less costly also).

Prerequisite
1)AWS Account.
2)Little experience with Python.
3)Understanding of VPC,Subnets,Security Groups,etc.
4)Familiarity with AWS lambda.
Thats it..Lets Start.

Step1: Set up VPC:
To create a VPC use the terraform script.
git clone https://github.com/pramodbs543/Terraform/tree/main/Simple_VPC
Here You have to just 1)store the Access key and Secret key as environment variables.(Shown in providers.tf file) 2)Change the Region in terraform.tfvars file.3)By default the backend.tf file stores tfstate file in S3 backend. You have to create respective S3 bucket and Dynamo DB for this. But if you want to skip this, simply delete the backend.tf file. Apply following commands.

terraform init
terraform apply -auto-approve

VPC is ready.

Step 2 Creat an IAM Role:
For this We need to create an IAM role first with AmazonSSMManagedInstanceCore policy attached. This IAM role allows us to connect with EC2(For installing DB) even if the EC2 is in private subnet and has no key pair nor public IP.

Search for AmazonSSMManagedInstanceCore policy and attach this to the role.

Give a name to the role and create role.

Step 3: Create Security groups:
1)Security group for Lambda:

2)Security group for DB.
In inbound rule, for MYSQL/Aurora type give source as lambda SG.

Step 4:Launch an EC2 instance in private subnet of VPC:

Select ubuntu as an AMI, t2.medium, Proceed without a key pair. Select db-sg as security group we created. Keep Public IP disabed. Select the VPC in network section and select the private subnet.
in Advance Details select the IAm role we created.

And launch the instance.

Step 5: install MariaDB server on the EC2:
Connect to the EC2.

Click in Session manager and click connect.

Change to root directory.
$ cd /
This command updates all packages to the latest version

sudo apt update -y

This command installs MySQL server on your machine, it also creates a systemd service
sudo apt install -y mariadb-server

This command enables the service created in previous step
sudo systemctl enable mariadb

This command starts the MySQL server service on your Linux instance
sudo systemctl start mariadb

This command helps you to set root user password and improve your DB security
sudo mysql_secure_installation

Here, just hit enter as we have not set any password yet
Enter current password for root (enter for none):

Here Reply Y
Switch to unix_socket authentication [Y/n]

Here, reply with Y
Change the root password? [Y/n]

Enter new password
New Password:

Re-enter new password
Re-enter new Password:

Say Y
Remove anonymous users? [Y/n]

Say Y
Disallow root login remotely? [Y/n]

Say N, as we would need them for verification
Remove test database and access to it? [Y/n]

Say Y
Reload privilege tables now? [Y/n]

Thanks for using MariaDB!

Verify the connection with the DB
mysql -h localhost -u root -p
Enter password.
Show Databases:
show databases;
Create Database:
create database mydatabase;
Switch to this Database:
use mydatabase;
Create a Table inside this database:

create table employees(eno int(10) primary key,ename varchar(20),esal double(10,2), eaddr varchar(30));

describe employees;

exit;

Configure the DB to accept remote connections:
127.0.0.1(localhost) makes your database accessible locally. We have to make it accessible remotely.
$ cd /etc/mysql/mariadb.conf.d
$ sudo vi 50-server.cnf

set bind-address=0.0.0.0 in place of 127.0.0.1

$ sudo systemctl restart mariadb
$ sudo ss -nlt

You will see 0.0.0.0:3306

Now, lets create an user which will have permissions to access mydatabase. Wildcard entry (%) allows this user01 to get connected from any host.
$ mysql -h localhost -u root -p
Enter root password we set earlier.

CREATE USER 'user01'@'%' IDENTIFIED BY 'password543';

GRANT ALL PRIVILEGES ON mydatabase.* to user01@'%' IDENTIFIED BY 'password543' WITH GRANT OPTION;

FLUSH PRIVILEGES;
exit

...Database is all set now.

Step 6: AWS Secret manager to store Db credentials:
Select Secrets Manager in AWS.
Store a new secret.

Now, in the bottom, in server address enter private IP of EC2 instance.

Give the secret a name and hit Next.

Hit NEXT again. Lastly hit Store the secret.(Note down your secret name. In this case "mariadbsecret")

Step 7: Create a Lambda Function.

Attach a role which has AWSsecretmanager policy attached to it.(We need to access the secrets from secret manager)

Select the VPC now.

Select both subnets for high availability and lambda security group.

Hit on Create function.
Add pymysql library as lambda layer:
You will need python installed on your local Windows PC and the python version should match the Runtime version you selected for your Lambda function, in this case Python 3.10. You also need to install pip so that you can download the pymysql package.
Once you have pip installed on your PC, open the command prompt and navigate to your desired directory and run the following command.

pip install --target ./python pymysql
This will create a folder called python in the desired directory. You will then need to compress the python folder as a zip file so that we can upload it to AWS.
In the Lambda console window, select the Layers option from the left side menu and click ‘Create layer’. Give your layer a name of ‘pymysql’ and upload the zip file that you just created. Select Python 3.10 as a Compatible runtime and click ‘Create’.

Click on lambda function. Scroll to bottom. There you will find Layers. Add layer pymysql.

Layer source as custom layers. Then from drop down select pymysql.

Now copy the code from
https://github.com/pramodbs543/lambda-mysqlonEC2
employees.py for lambda function. event file for providing json input to the code. The entries in the even file will be stored in database.

Once you Test the code, you will get success message.

The records in the database:

The Architecture: