Using AWS Bedrock to Query a MySQL Database

In this blog, we will connect AWS Bedrock to our RDS MySQL database and help Bedrock understand our database structure. Then, we will ask Bedrock in plain English what we need from the database, and Bedrock will write the query for us. Lambda will run the query against the database and display the results to the user.

We will need an RDS MySQL database, VPC, and a lambda function in VPC. If you don't already have a VPC, use the SimpleVPC Terraform code by going to GitHub and cloning the Simple-VPC repository. Update the variables.tf and change the ENV on line 32. The terraform code will create a VPC with six Subnets, three public and three private. It will create an IGW with three NATs.

git clone https://github.com/mahmoodr786/simple-vpc.git
cd simple-vpc
terraform init
terraform apply

Next, follow the first part of Using ElastiCache with AWS RDS to create the RDS MySQL Database.

Next, follow the Slack AI Bot with AWS Bedrock blog and do the Lambda part to create the Lambda function.

Once both are done, you can move our Lambda function into the VPC. Remember to add IAM permissions to your lambda role so it can be added to a VPC. Go to configuration, add it to a VPC, and pick the private subnets. Also, remember to allow access to the database from the lambda function using security groups.

Since we will use PyMySQL, we will need to add this to the layer or to the lambda package itself. If you don't have time to create your own layer, head to my GitHub: https://github.com/mahmoodr786/boto3-bedrock-lambda-layer. Download the bedrock-pymysql.zip and upload it as a layer to your lambda function.

Now, we will create a function URL to access our function in the browser.

Feel free to use IAM authentication or other means to protect your function. For this blog, I will be using NONE.

Access your function in the browser to see if it works, and it should.

Now, we need to connect to our database and create two tables with fake data. If you already have a database and a way to connect, go for it. If you do not, you can create an EC2 instance in one of the private subnets, use SSM to SSH to the EC2, and connect to MySQL.

apt update
apt install mysql-client
mysql -u admin -p -h bedrockdb.cluster-cifbe2qttmpd.us-east-1.rds.amazonaws.com

Now that you are connected, run the queries below to create your database tables and data.

Show databases;
use bedrock

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

CREATE TABLE fruits (
    id INT PRIMARY KEY,
    user_id INT,
    fruit_name VARCHAR(50) NOT NULL,
    quantity INT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

That should create two tables for us. Now, we need to add some data to it.

INSERT INTO users (user_id, username, email) VALUES
(1, 'john_doe', 'john@example.com'),
(2, 'jane_smith', 'jane@example.com'),
(3, 'bob_jones', 'bob@example.com'),
(4, 'alice_wonderland', 'alice@example.com');

INSERT INTO fruits (id, user_id, fruit_name, quantity) VALUES
(101, 1, 'Apple', 5),
(102, 1, 'Banana', 8),
(103, 2, 'Orange', 12),
(104, 3, 'Grapes', 20),
(105, 4, 'Strawberry', 15),
(106, 4, 'Pineapple', 7);