如何使用CDK V2在RDS Aurora MySQL中创建表？新手技术问询

可以通过CDK实现创建Aurora MySQL实例的同时创建表，常用两种方案：自定义资源（Lambda驱动）或RDS初始化脚本，以下是具体实现：

方案一：自定义资源（Lambda执行SQL） #

通过自定义资源触发Lambda函数，在RDS集群就绪后执行建表SQL，适合需要灵活控制初始化逻辑的场景。

TypeScript代码示例 #

import * as cdk from 'aws-cdk-lib';
import { Construct } from 'constructs';
import * as rds from 'aws-cdk-lib/aws-rds';
import * as lambda from 'aws-cdk-lib/aws-lambda';
import * as cr from 'aws-cdk-lib/custom-resources';
import * as ec2 from 'aws-cdk-lib/aws-ec2';

export class AuroraTableSetupStack extends cdk.Stack {
  constructor(scope: Construct, id: string, props?: cdk.StackProps) {
    super(scope, id, props);

    // 创建VPC（复用现有VPC可跳过此步骤）
    const vpc = new ec2.Vpc(this, 'AuroraVpc', { maxAzs: 2 });

    // 构建Aurora MySQL集群
    const auroraCluster = new rds.DatabaseCluster(this, 'AuroraMysqlCluster', {
      engine: rds.DatabaseClusterEngine.auroraMysql({
        version: rds.AuroraMysqlEngineVersion.VER_3_03_0,
      }),
      credentials: rds.Credentials.fromGeneratedSecret('dbadmin'),
      instanceProps: {
        vpc,
        vpcSubnets: { subnetType: ec2.SubnetType.PRIVATE_ISOLATED },
        instanceType: ec2.InstanceType.of(ec2.InstanceClass.T3, ec2.InstanceSize.SMALL),
      },
      instances: 1,
    });

    // 创建执行建表逻辑的Lambda函数
    const tableInitLambda = new lambda.Function(this, 'TableInitLambda', {
      runtime: lambda.Runtime.NODEJS_18_X,
      handler: 'index.handler',
      code: lambda.Code.fromInline(`
        const mysql = require('mysql2/promise');
        const AWS = require('aws-sdk');
        const secretsManager = new AWS.SecretsManager();

        exports.handler = async (event) => {
          // 仅在资源创建时执行，更新/删除操作直接返回成功
          if (event.RequestType !== 'Create') return { Status: 'SUCCESS' };

          try {
            // 从Secrets Manager获取数据库凭证
            const secretResp = await secretsManager.getSecretValue({
              SecretId: '${auroraCluster.secret?.secretArn}'
            }).promise();
            const dbCreds = JSON.parse(secretResp.SecretString);

            // 建立数据库连接
            const connection = await mysql.createConnection({
              host: '${auroraCluster.clusterEndpoint.hostname}',
              user: dbCreds.username,
              password: dbCreds.password,
              database: 'mysql'
            });

            // 执行幂等建表语句
            await connection.execute(\`
              CREATE TABLE IF NOT EXISTS myapp.user_profiles (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(64) NOT NULL UNIQUE,
                full_name VARCHAR(128),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
              ) ENGINE=InnoDB;
            \`);

            await connection.end();
            return { Status: 'SUCCESS' };
          } catch (err) {
            console.error('建表失败:', err);
            return { Status: 'FAILED', Reason: err.message };
          }
        };
      `),
      vpc,
      vpcSubnets: { subnetType: ec2.SubnetType.PRIVATE_ISOLATED },
    });

    // 给Lambda授权访问Secrets Manager和RDS
    auroraCluster.secret?.grantRead(tableInitLambda);
    auroraCluster.connections.allowDefaultPortFrom(tableInitLambda);

    // 创建自定义资源，触发Lambda执行建表
    new cr.CustomResource(this, 'InitTableResource', {
      serviceToken: tableInitLambda.functionArn,
      dependsOn: [auroraCluster],
    });
  }
}

关键注意事项 #

• Lambda需部署在RDS所在VPC的私有子网，确保网络可达
• 建表语句必须使用CREATE TABLE IF NOT EXISTS保证幂等，避免重复执行报错
• 若需创建多表或复杂逻辑，建议将Lambda代码抽离为单独文件，而非内嵌字符串

方案二：RDS初始化脚本 #

将建表SQL上传至S3，在创建Aurora集群时指定初始化脚本，适合一次性初始化场景。

TypeScript代码示例 #

import * as cdk from 'aws-cdk-lib';
import { Construct } from 'constructs';
import * as rds from 'aws-cdk-lib/aws-rds';
import * as s3 from 'aws-cdk-lib/aws-s3';
import * as ec2 from 'aws-cdk-lib/aws-ec2';

export class AuroraInitScriptStack extends cdk.Stack {
  constructor(scope: Construct, id: string, props?: cdk.StackProps) {
    super(scope, id, props);

    const vpc = new ec2.Vpc(this, 'AuroraVpc', { maxAzs: 2 });
    const initBucket = s3.Bucket.fromBucketName(this, 'InitBucket', 'your-init-script-bucket');

    const auroraCluster = new rds.DatabaseCluster(this, 'AuroraMysqlCluster', {
      engine: rds.DatabaseClusterEngine.auroraMysql({
        version: rds.AuroraMysqlEngineVersion.VER_3_03_0,
      }),
      credentials: rds.Credentials.fromGeneratedSecret('dbadmin'),
      instanceProps: {
        vpc,
        vpcSubnets: { subnetType: ec2.SubnetType.PRIVATE_ISOLATED },
        instanceType: ec2.InstanceType.of(ec2.InstanceClass.T3, ec2.InstanceSize.SMALL),
      },
      instances: 1,
      // 指定初始化脚本
      initialization: {
        databaseName: 'myapp',
        initScript: rds.Script.fromBucket(initBucket, 'init.sql'),
      },
    });

    // 授权RDS访问S3脚本桶
    initBucket.grantRead(auroraCluster);
  }
}

关键注意事项 #

• 脚本在集群首次创建及后续重建时都会执行，必须保证脚本幂等
• init.sql中可包含创建数据库、表、插入初始数据等逻辑
• 需确保S3桶与RDS集群在同一区域，或配置跨区域访问权限

内容的提问来源于stack exchange，提问作者Premchand