Nancy's Preferred Machete

    cloudformation-sql-run
    TypeScript icon, indicating that this package has built-in type declarations

    2.1.5 • Public • Published

    Call SQL during CloudFormation update

    An aws-cdk construct for calling SQL during CloudFormation stack update.

    Usage

    The SqlRun resource provides 2 callbacks: up, down.

    The up callback runs a forward migration. The down command should do the opposite of up.

    The following example will issue a POST request when a Some API resource creates:

    import * as ec2 from 'aws-cdk-lib/aws-ec2';
    import * as rds from "aws-cdk-lib/aws-rds";
    import {
      DatabaseInstanceEngine,
      PostgresEngineVersion
    } from "aws-cdk-lib/aws-rds";
    import * as secretmanager from 'aws-cdk-lib/aws-secretsmanager';
    import * as cdk from "aws-cdk-lib";
    import { RemovalPolicy } from "aws-cdk-lib";
    import { SqlRun, SqlRunConnection, SqlSecret } from "cloudformation-sql-run";
    import { Construct } from "constructs";
    
    export class ExamplesStack extends cdk.Stack {
      constructor(scope: Construct, id: string, props?: cdk.StackProps) {
        super(scope, id, props);
    
        const password = new secretmanager.Secret(this, 'Some Password')
    
        const vpc = new ec2.Vpc(this, 'vpc', {
          natGateways: 1
        })
    
        const db = new rds.DatabaseInstance(this, 'db', {
          databaseName: 'sqlrunexample',
          engine: DatabaseInstanceEngine.postgres({
            version: PostgresEngineVersion.VER_10
          }),
          vpc: vpc,
          removalPolicy: RemovalPolicy.DESTROY
        })
    
        const createDatabaseUser = new SqlRun(this, 'Create Database User', {
          vpc: vpc,
          connection: SqlRunConnection.fromDatabaseInstance(db),
          up: {
            run: [{
              sql: `CREATE TABLE items(name varchar)`
            }, {
              sql: `INSERT INTO items(name) VALUE (:secret)`,
              parameters: {
                secret: SqlSecret.fromSecretsManager(password)
              }
            }],
          },
          down: {
            run: [{
              sql: `DROP TABLE items`
            }]
          }
        });
      }
    }

    SQL results as data

    You can access results of the last sql statement as follows:

    const createDatabaseUser = new SqlRun(this, 'Create Database User', {
      up: { run: [{ sql: `select count(*) as admin_count, company_id from users where is_admin = true group by company_id` }] }
    });
    
    new CfnOutput(this, 'Row at index 0', {
      description: "First result",
      value: sampleQuery.getResultField("[0].admin_count")
    })
    
    new CfnOutput(this, 'Row at index 1', {
      description: "Second result",
      value: sampleQuery.getResultField("[1].company_id")
    })

    Install

    npm i cloudformation-sql-run

    DownloadsWeekly Downloads

    36

    Version

    2.1.5

    License

    MIT

    Unpacked Size

    18.5 MB

    Total Files

    18

    Last publish

    Collaborators

    • miensol