How do I create an aws rds aurora database with some tables with python aws cdk?

Question

I want to create an AWS stack with the python aws-cdk library.

The requirements are as such: There must exist an aurora3 mysql database and it needs to have 2 tables (as a reduced example, lets say table1 needs to have 2 fields: id of integer type and name of string type, and table2 needs to have table1_id of integer which is a foreign key to table1, and a data field of string type

I have found some tutorials online on how to do this through the console, but I need to do it from within code, and not manually through the console, and the documentation seems lacking for this

Answer 1

The CDK code looks like this, call it from your app.py as you would normally

from aws_cdk import Stack, aws_rds, aws_ec2, triggers, aws_lambda, aws_secretsmanager, Duration
from constructs import Construct


class CdkRdsStack(Stack):

    def __init__(self, scope: Construct, construct_id: str, **kwargs) -> None:
        super().__init__(scope, construct_id, **kwargs)
        vpc = aws_ec2.Vpc(
            self, "my_vpc", ip_addresses=aws_ec2.IpAddresses.cidr("10.0.0.0/16")
        )
        selection = vpc.select_subnets(
            subnet_type=aws_ec2.SubnetType.PRIVATE_WITH_EGRESS
        )

        my_security_group = aws_ec2.SecurityGroup(
            self,
            "SecurityGroup",
            vpc=vpc,
            description="Allow RDS access",
            allow_all_outbound=True,
            disable_inline_rules=True
        )
        my_security_group.add_ingress_rule(
            aws_ec2.Peer.security_group_id(my_security_group.security_group_id),
            aws_ec2.Port.tcp(3306))

        db = aws_rds.DatabaseCluster(
            self,
            "my_database",
            engine=aws_rds.DatabaseClusterEngine.aurora_mysql(
                version=aws_rds.AuroraMysqlEngineVersion.VER_3_05_1
            ),
            credentials=aws_rds.Credentials.from_generated_secret(
                username="database_cluster_creds",
                secret_name="database_cluster_creds",
            ),
            writer=aws_rds.ClusterInstance.provisioned(
                "writer", publicly_accessible=False
            ),
            readers=[
                aws_rds.ClusterInstance.provisioned("reader1", promotion_tier=1),
                aws_rds.ClusterInstance.serverless_v2("reader2"),
            ],
            vpc_subnets=aws_ec2.SubnetSelection(
                subnet_type=aws_ec2.SubnetType.PRIVATE_WITH_EGRESS
            ),
            vpc=vpc,
            default_database_name="database_cluster",
            security_groups=[my_security_group],

        )
        database_cluster_creds = aws_secretsmanager.Secret.from_secret_name_v2(
            self,
            "database_cluster_creds",
            secret_name="database_cluster_creds"
        )

        lambda_fn = aws_lambda.Function(
            self,
            "MyFunction",
            runtime=aws_lambda.Runtime.PYTHON_3_11,
            handler="lambda_function.handler",
            code=aws_lambda.Code.from_asset("lambda/lambda_function.zip"),
            vpc=vpc,
            vpc_subnets=aws_ec2.SubnetSelection(
                subnet_type=aws_ec2.SubnetType.PRIVATE_WITH_EGRESS
            ),
            timeout=Duration.seconds(40),
            security_groups=[my_security_group]
        )

        database_cluster_creds.grant_read(lambda_fn.role)

        triggers.Trigger(
            self,
            "MyTrigger",
            handler=lambda_fn,
            execute_after=[db],
            # execute_on_handler_change=False,
        )

The CDK creates a trigger for a lambda the lambda updates the database using boto3. It is automatically triggered on every build but you can change this if you want

The lambda code needs packaging in a zip file with a package, from https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-lambda-tutorial.html#vpc-rds-update-function (the lambda code is not the same as we use here)

To include the PyMySQL dependency with your function code, create a .zip deployment package. The following commands work for Linux, macOS, or Unix: To create a .zip deployment package

Save the example code as a file named lambda_function.py.

In the same directory in which you created your lambda_function.py file, create a new directory named package and install the PyMySQL library.

 mkdir package
 pip install --target package pymysql

Create a zip file containing your application code and the PyMySQL library. In Linux or MacOS, run the following CLI commands. In Windows, use your preferred zip tool to create the lambda_function.zip file. Your lambda_function.py source code file and the folders containing your dependencies must be installed at the root of the .zip file.

cd package
zip -r ../lambda_function.zip .
cd ..
zip lambda_function.zip lambda_function.py

Otherwise, google how to make a lambda layer using CDK and a zip file created on an EC2

The code looks like this it uses a credential CDK puts in AWS secrets manager:

import json
import sys
import logging
import pymysql
import boto3
from botocore.exceptions import ClientError

logger = logging.getLogger()
logger.setLevel(logging.INFO)


def get_secret():
    secret_name = "database_cluster_creds"
    region_name = "eu-west-2"

    # Create a Secrets Manager client
    session = boto3.session.Session()
    client = session.client(
        service_name='secretsmanager',
        region_name=region_name
    )

    try:
        get_secret_value_response = client.get_secret_value(
            SecretId=secret_name
        )
    except ClientError as ex:
        raise ex

    return json.loads(get_secret_value_response['SecretString'])


try:
    secrets = get_secret()
    conn = pymysql.connect(
        host=secrets.get("host"),
        user=secrets.get("username"),
        passwd=secrets.get("password"),
        db=secrets.get("dbname"),
        connect_timeout=5
    )
except pymysql.MySQLError as e:
    logger.error("ERROR: Unexpected error: Could not connect to MySQL instance.")
    logger.error(e)
    sys.exit(1)

logger.info("SUCCESS: Connection to RDS for MySQL instance succeeded")


def handler(_, __):
    """
    This function creates a new RDS database table and writes records to it
    """

    cust_id = 334
    name = "Dummy Name"

    item_count = 0
    sql_string = f"insert into Customer (CustID, Name) values({cust_id}, '{name}')"

    with conn.cursor() as cur:
        cur.execute(
            "create table if not exists Customer ( CustID  int NOT NULL, Name varchar(255) NOT NULL, PRIMARY KEY (CustID))")
        cur.execute(sql_string)
        conn.commit()
        cur.execute("select * from Customer")
        logger.info("The following items have been added to the database:")
        for row in cur:
            item_count += 1
            logger.info(row)
    conn.commit()

    return f"Added {item_count} items to RDS for MySQL table"

The lambda logs to CloudWatch:

2024-02-10T04:22:49.079+00:00
[INFO]  2024-02-10T04:22:49.079Z    bf5039c9-1ed3-46b4-bc07-aad8337b2e06    The following items have been added to the database:
2024-02-10T04:22:49.079+00:00
[INFO]  2024-02-10T04:22:49.079Z    bf5039c9-1ed3-46b4-bc07-aad8337b2e06    (334, 'Dummy Name')

The AWS region in the lambda may be different to yours

You will have to write the SQL as you want it