ElastiCache for Redis for RDS

const vpc = aws_ec2.Vpc.fromLookup(this, 'Vpc', {
  vpcId: props.vpcId,
  vpcName: props.vpcName
})

const subnetGroup = new aws_elasticache.CfnSubnetGroup(
  this,
  'SubnetGroupForRedisCluster',
  {
    subnetIds: vpc.privateSubnets.map(subnet => subnet.subnetId),
    description: 'subnet group for redis cluster'
  }
)

const redisSecurityGroup = new aws_ec2.SecurityGroup(
  this,
  'SecurityGroupForRedisCluster',
  {
    securityGroupName: 'SecurityGroupForRedisCluster',
    vpc: vpc
  }
)

redisSecurityGroup.addIngressRule(
  // production SG peer
  aws_ec2.Peer.securityGroupId(webAppSecurityGroup.securityGroupId),
  // redis port
  aws_ec2.Port.tcp(6379)
)

const redis = new aws_elasticache.CfnCacheCluster(this, 'RedisClusterDmoe', {
  clusterName: 'RedisClusterDemo',
  engine: 'redis',
  cacheNodeType: 'cache.t3.small',
  numCacheNodes: 1,
  cacheSubnetGroupName: subnetGroup.ref,
  vpcSecurityGroupIds: [redisSecurityGroup.securityGroupId]
})

const dbSecurityGroup = new aws_ec2.SecurityGroup(this, 'SecurityGroupForDb', {
  securityGroupName: 'SecurityGroupForDb',
  vpc: vpc
})

dbSecurityGroup.addIngressRule(
  aws_ec2.Peer.securityGroupId(webAppSecurityGroup.securityGroupId),
  aws_ec2.Port.tcp(3306)
)

const rds = new aws_rds.DatabaseInstance(this, 'RdsDatabaseRedisDemo', {
  databaseName: 'covid',
  deletionProtection: false,
  engine: aws_rds.DatabaseInstanceEngine.mysql({
    version: aws_rds.MysqlEngineVersion.VER_8_0_23
  }),
  vpc,
  port: 3306,
  instanceType: aws_ec2.InstanceType.of(
    aws_ec2.InstanceClass.BURSTABLE3,
    aws_ec2.InstanceSize.MEDIUM
  ),
  // password generated and stored in secret-manager
  credentials: aws_rds.Credentials.fromGeneratedSecret('admin', {
    secretName: 'rds-secret-name'
  }),
  // iam authentication
  iamAuthentication: true,
  // for testing => production retain
  removalPolicy: RemovalPolicy.DESTROY,
  // for testing => production true
  securityGroups: [dbSecurityGroup],
  storageEncrypted: false,
  // vpc subnet
  vpcSubnets: {
    subnetType: aws_ec2.SubnetType.PRIVATE_WITH_NAT
  }
})

const webAppSecurityGroup = new aws_ec2.SecurityGroup(
  this,
  'SecurityGroupForRedisCulster',
  {
    securityGroupName: 'SecurityGroupForRedisCulster',
    vpc: vpc
  }
)

webAppSecurityGroup.addIngressRule(
  // production SG peer
  aws_ec2.Peer.anyIpv4(),
  // application port 8080
  aws_ec2.Port.tcp(8080)
)

const role = new aws_iam.Role(this, 'RoleForEc2AccessRdsRedisDemo', {
  roleName: 'RoleForEc2AccessRdsRedisDemo',
  assumedBy: new aws_iam.ServicePrincipal('ec2.amazonaws.com')
})

role.addManagedPolicy(
  aws_iam.ManagedPolicy.fromAwsManagedPolicyName('AmazonSSMManagedInstanceCore')
)

role.addManagedPolicy(
  aws_iam.ManagedPolicy.fromAwsManagedPolicyName(
    'AWSCloudFormationReadOnlyAccess'
  )
)

role.attachInlinePolicy(
  new aws_iam.Policy(this, 'PolicyForEc2AccessRdsRedisDemo', {
    policyName: 'PolicyForEc2AccessRdsRedisDemo',
    statements: [
      new aws_iam.PolicyStatement({
        effect: aws_iam.Effect.ALLOW,
        actions: ['secretsmanager:GetSecretValue'],
        resources: ['arn:aws:secretsmanager:*']
      })
    ]
  })
)

const ec2 = new aws_ec2.Instance(this, 'Ec2RdsRedisDemo', {
  instanceName: 'Ec2RdsRedisDemo',
  instanceType: aws_ec2.InstanceType.of(
    aws_ec2.InstanceClass.T3,
    aws_ec2.InstanceSize.SMALL
  ),
  machineImage: aws_ec2.MachineImage.latestAmazonLinux({
    generation: aws_ec2.AmazonLinuxGeneration.AMAZON_LINUX_2,
    edition: aws_ec2.AmazonLinuxEdition.STANDARD,
    storage: aws_ec2.AmazonLinuxStorage.GENERAL_PURPOSE
  }),
  vpc: vpc,
  role: role,
  securityGroup: webAppSecurityGroup,
  vpcSubnets: {
    subnetType: aws_ec2.SubnetType.PUBLIC
  }
})

// add user data for ec2
ec2.addUserData(fs.readFileSync('./lib/user-data.sh', 'utf8'))

import redis
import pymysql

def query_mysql_and_cache(sql,db_host, db_username, db_password, db_name):
    '''
    This function retrieves records from the cache if it exists, or else gets it from the MySQL database.
    '''

    res = Cache.get(sql)

    if res:
        print ('Records in cache...')
        return ({'records_in_cache': True, 'data' : res})

    res = mysql_fetch_data(sql, db_host, db_username, db_password, db_name)

    if res:
        print ('Cache was empty. Now populating cache...')
        Cache.setex(sql, ttl, json.dumps(res))
        return ({'records_in_cache': False, 'data' : res})
    else:
        return None

def mysql_fetch_data(sql, db_host, db_username, db_password, db_name):
    '''
    This function excutes the sql query and returns dataset.
    '''
    try:
        con = pymysql.connect(host=db_host,
                                user=db_username,
                                password=db_password,
                                database=db_name,
                                autocommit=True,
                                local_infile=1,
                                charset='utf8mb4',
                                cursorclass=pymysql.cursors.DictCursor)
        # Create cursor and execute SQL statement
        cursor = con.cursor()
        cursor.execute(sql)
        data_set = cursor.fetchall()
        con.close()
        return data_set

    except Exception as e:
        print('Error: {}'.format(str(e)))
        sys.exit(1)

def initialize_database(configs):
    '''
    This function initialize the MySQL database if not already done so and generates
    all configurations needed for the application.
    '''

    # Initialize Database
    print ('Initializing MySQL Database...')

    #Drop table if exists
    sql_command = "DROP TABLE IF EXISTS covid.articles;"
    mysql_execute_command(sql_command, configs['db_host'], configs['db_username'], configs['db_password'])

    #Create table
    sql_command = "CREATE TABLE covid.articles (OBJECTID INT, SHA TEXT, PossiblePlace TEXT, Sentence TEXT, MatchedPlace TEXT, DOI  TEXT, Title TEXT, Abstract TEXT, PublishedDate TEXT, Authors TEXT, Journal TEXT, Source TEXT, License TEXT, PRIMARY KEY (OBJECTID));"
    mysql_execute_command(sql_command, configs['db_host'], configs['db_username'], configs['db_password'])

    #Load CSV file into mysql
    sql_command = """
    LOAD DATA LOCAL INFILE '{0}'
    INTO TABLE covid.articles
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 ROWS;
    """.format(configs['dataset_file'])
    mysql_execute_command(sql_command, configs['db_host'], configs['db_username'], configs['db_password'])