python/pulumi_via_jupyter/database_migration.ipynb

{
    "cells": [
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "# Enabling Data Engineering Workflows\n",
                "\n",
                "With Automation API, choosing between small, decoupled units of infrastructure and a maintainable orchestration process is no longer necessary. Your Pulumi code now executes just like any other code in your application, meaning that it is possible to deploy your infrastructure and manage related activities all from within your notebook.\n",
                "\n",
                "In this notebook, we will set up an AWS Aurora SQL database and execute a database \"migration\" using the resulting connection info. For the migration, we will create a table, insert a few rows of data and then read the data back to verify the setup.\n",
                "\n",
                "### Define your database\n",
                "\n",
                "Consider the following function called `rds_database`. It creates an AWS Aurora SQL cluster and database instance and exports the relevant connection information.\n"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "import pulumi\n",
                "import pulumi_aws as aws\n",
                "\n",
                "def rds_database():\n",
                "    default_vpc = aws.ec2.get_vpc(default=True)\n",
                "    public_subnet_ids = aws.ec2.get_subnet_ids(vpc_id=default_vpc.id)\n",
                "    subnet_group = aws.rds.SubnetGroup(\"db_subnet\", subnet_ids=public_subnet_ids.ids)\n",
                "\n",
                "    # make a public security group for our cluster for the migration\n",
                "    security_group = aws.ec2.SecurityGroup(\"public_group\",\n",
                "                                           ingress=[aws.ec2.SecurityGroupIngressArgs(\n",
                "                                               protocol=\"-1\",\n",
                "                                               from_port=0,\n",
                "                                               to_port=0,\n",
                "                                               cidr_blocks=[\"0.0.0.0/0\"]\n",
                "                                           )],\n",
                "                                           egress=[aws.ec2.SecurityGroupEgressArgs(\n",
                "                                              protocol=\"-1\",\n",
                "                                              from_port=0,\n",
                "                                              to_port=0,\n",
                "                                              cidr_blocks=[\"0.0.0.0/0\"]\n",
                "                                           )])\n",
                "\n",
                "    # example, you should change this\n",
                "    db_name = \"hellosql\"\n",
                "    db_user = \"hellosql\"\n",
                "    db_pass = \"hellosql\"\n",
                "\n",
                "    # provision our db\n",
                "    cluster = aws.rds.Cluster(\"db\",\n",
                "                              engine=aws.rds.EngineType.AURORA_MYSQL,\n",
                "                              engine_version=\"5.7.mysql_aurora.2.12.1\",\n",
                "                              database_name=db_name,\n",
                "                              master_username=db_user,\n",
                "                              master_password=db_pass,\n",
                "                              skip_final_snapshot=True,\n",
                "                              db_subnet_group_name=subnet_group.name,\n",
                "                              vpc_security_group_ids=[security_group.id])\n",
                "\n",
                "    cluster_instance = aws.rds.ClusterInstance(\"db_instance\",\n",
                "                                               cluster_identifier=cluster.cluster_identifier,\n",
                "                                               instance_class=aws.rds.InstanceType.T3_SMALL,\n",
                "                                               engine=aws.rds.EngineType.AURORA_MYSQL,\n",
                "                                               engine_version=\"5.7.mysql_aurora.2.12.1\",\n",
                "                                               publicly_accessible=True,\n",
                "                                               db_subnet_group_name=subnet_group.name)\n",
                "\n",
                "    pulumi.export(\"host\", cluster.endpoint)\n",
                "    pulumi.export(\"db_name\", db_name)\n",
                "    pulumi.export(\"db_user\", db_user)\n",
                "    pulumi.export(\"db_pass\", db_pass)\n"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Deploy the infrastructure\n",
                "\n",
                "Now, let's go ahead and deploy our infrastructure in a Pulumi stack."
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "import json\n",
                "from pulumi import automation as auto\n",
                "\n",
                "# create (or select if one already exists) a stack that uses our inline program\n",
                "stack = auto.create_or_select_stack(stack_name=\"dev\",\n",
                "                                    project_name=\"database_migration\",\n",
                "                                    program=rds_database)\n",
                "\n",
                "print(\"successfully initialized stack\")\n",
                "\n",
                "# for inline programs, we must manage plugins ourselves\n",
                "print(\"installing plugins...\")\n",
                "stack.workspace.install_plugin(\"aws\", \"v4.0.0\")\n",
                "\n",
                "# set stack configuration specifying the AWS region to deploy\n",
                "print(\"setting up config\")\n",
                "stack.set_config(\"aws:region\", auto.ConfigValue(value=\"us-west-2\"))\n",
                "\n",
                "print(\"refreshing stack...\")\n",
                "stack.refresh(on_output=print)\n",
                "print(\"refresh complete\")\n",
                "\n",
                "print(\"updating stack...\")\n",
                "up_res = stack.up(on_output=print)\n",
                "print(f\"update summary: \\n{json.dumps(up_res.summary.resource_changes, indent=4)}\")"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Extract the outputs\n",
                "\n",
                "Let's get the important connection details out of the stack outputs."
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {
                "pycharm": {
                    "name": "#%%\n"
                }
            },
            "outputs": [],
            "source": [
                "outputs = stack.outputs()\n",
                "host = outputs['host'].value\n",
                "user = outputs['db_user'].value\n",
                "password = outputs['db_pass'].value\n",
                "database = outputs['db_name'].value\n",
                "\n",
                "host, user, password, database"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Seed the database\n",
                "\n",
                "Alright, now we can configure the database and create our table."
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {
                "scrolled": true
            },
            "outputs": [],
            "source": [
                "from mysql.connector import connect\n",
                "\n",
                "connection = connect(host=host, user=user, password=password, database=database)\n",
                "\n",
                "create_table_query = \"\"\"CREATE TABLE IF NOT EXISTS hello_pulumi(\n",
                "    id int(9) NOT NULL PRIMARY KEY,\n",
                "    color varchar(14) NOT NULL);\n",
                "\"\"\"\n",
                "\n",
                "with connection.cursor() as cursor:\n",
                "    cursor.execute(create_table_query)\n",
                "    connection.commit()"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "Seed the table with some data to start."
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {
                "pycharm": {
                    "name": "#%%\n"
                }
            },
            "outputs": [],
            "source": [
                "seed_table_query = \"\"\"INSERT IGNORE INTO hello_pulumi (id, color)\n",
                "VALUES\n",
                "    (1, 'Purple'),\n",
                "    (2, 'Violet'),\n",
                "    (3, 'Plum');\n",
                "\"\"\"\n",
                "\n",
                "with connection.cursor() as cursor:\n",
                "    cursor.execute(seed_table_query)\n",
                "    connection.commit()"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Verify the data\n",
                "\n",
                "Finally, let's read the data back to ensure that everything worked as expected."
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {
                "pycharm": {
                    "name": "#%%\n"
                }
            },
            "outputs": [],
            "source": [
                "read_table_query = \"\"\"SELECT COUNT(*) FROM hello_pulumi;\"\"\"\n",
                "with connection.cursor() as cursor:\n",
                "    cursor.execute(read_table_query)\n",
                "    print(f\"Result: {cursor.fetchone()}\")"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Clean up\n",
                "\n",
                "Now that we're done testing everything out, we can close our database connection and destroy and remove our stack."
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {
                "pycharm": {
                    "name": "#%%\n"
                }
            },
            "outputs": [],
            "source": [
                "connection.close()\n",
                "stack.destroy(on_output=print)\n",
                "stack.workspace.remove_stack(\"dev\")\n",
                "print(f\"stack successfully removed\")"
            ]
        }
    ],
    "metadata": {
        "kernelspec": {
            "display_name": "PyCharm (automation-api-examples)",
            "language": "python",
            "name": "pycharm-327b49b8"
        },
        "language_info": {
            "codemirror_mode": {
                "name": "ipython",
                "version": 3
            },
            "file_extension": ".py",
            "mimetype": "text/x-python",
            "name": "python",
            "nbconvert_exporter": "python",
            "pygments_lexer": "ipython3",
            "version": "3.8.7"
        }
    },
    "nbformat": 4,
    "nbformat_minor": 4
}