notebooks/Flyway-Setup.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "powershell",
            "display_name": "PowerShell"
        },
        "language_info": {
            "name": "powershell",
            "codemirror_mode": "shell",
            "mimetype": "text/x-sh",
            "file_extension": ".ps1"
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# To use this notebook\n",
                "\n",
                "-   Open in Azure Data Studio\n",
                "-   Ensure the Kernel is set to \"PowerShell\"\n",
                "\n",
                "# You can run Flyway in a variety of ways\n",
                "\n",
                "Community edition is free\n",
                "\n",
                "You may download and install locally - [https://flywaydb.org/download/](https://flywaydb.org/download/)\n",
                "\n",
                "You may use the flyway docker container - [https://github.com/flyway/flyway-docker](https://github.com/flyway/flyway-docker)"
            ],
            "metadata": {
                "azdata_cell_guid": "a831471b-4f74-4659-be1e-e2b548f28532"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Running the Flyway Docker container\n",
                "\n",
                "Install Docker and make sure it's running - [https://docs.docker.com/get-docker/](https://docs.docker.com/get-docker/)\n",
                "\n",
                "Instructions to run Flyway via Docker are here - [https://github.com/flyway/flyway-docker](https://github.com/flyway/flyway-docker)\n",
                "\n",
                "Some examples of this are below\n",
                "\n",
                "# Run Flyway and return info on available commands\n",
                "\n",
                "If the image isn't available for you locally yet (first run), this command should automatically pull it.\n",
                "\n",
                "The --rm causes Docker to automatically remove the container when it exits."
            ],
            "metadata": {
                "azdata_cell_guid": "cb72b6d5-254e-4291-9299-9b9f1eb34474"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "docker run --rm flyway/flyway"
            ],
            "metadata": {
                "azdata_cell_guid": "64c065eb-6c3c-4587-929c-428af1d841fd",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "name": "stdout",
                    "text": "Usage\n=====\n\nflyway [options] command\n\nBy default, the configuration will be read from conf/flyway.conf.\nOptions passed from the command-line override the configuration.\n\nCommands\n--------\nmigrate  : Migrates the database\nclean    : Drops all objects in the configured schemas\ninfo     : Prints the information about applied, current and pending migrations\nvalidate : Validates the applied migrations against the ones on the classpath\nundo     : [teams] Undoes the most recently applied versioned migration\nbaseline : Baselines an existing database at the baselineVersion\nrepair   : Repairs the schema history table\n\nOptions (Format: -key=value)\n-------\ndriver                       : Fully qualified classname of the JDBC driver\nurl                          : Jdbc url to use to connect to the database\nuser                         : User to use to connect to the database\npassword                     : Password to use to connect to the database\nconnectRetries               : Maximum number of retries when attempting to connect to the database\ninitSql                      : SQL statements to run to initialize a new database connection\nschemas                      : Comma-separated list of the schemas managed by Flyway\ntable                        : Name of Flyway's schema history table\nlocations                    : Classpath locations to scan recursively for migrations\nresolvers                    : Comma-separated list of custom MigrationResolvers\nskipDefaultResolvers         : Skips default resolvers (jdbc, sql and Spring-jdbc)\nsqlMigrationPrefix           : File name prefix for versioned SQL migrations\nundoSqlMigrationPrefix       : [teams] File name prefix for undo SQL migrations\nrepeatableSqlMigrationPrefix : File name prefix for repeatable SQL migrations\nsqlMigrationSeparator        : File name separator for SQL migrations\nsqlMigrationSuffixes         : Comma-separated list of file name suffixes for SQL migrations\nstream                       : [teams] Stream SQL migrations when executing them\nbatch                        : [teams] Batch SQL statements when executing them\nmixed                        : Allow mixing transactional and non-transactional statements\nencoding                     : Encoding of SQL migrations\nplaceholderReplacement       : Whether placeholders should be replaced\nplaceholders                 : Placeholders to replace in sql migrations\nplaceholderPrefix            : Prefix of every placeholder\nplaceholderSuffix            : Suffix of every placeholder\njdbcProperties               : Properties to pass to the JDBC driver object\ninstalledBy                  : Username that will be recorded in the schema history table\ntarget                       : Target version up to which Flyway should use migrations\ncherryPick                   : [teams] Comma separated list of migrations that Flyway should consider when migrating\nskipExecutingMigrations      : [teams] Whether Flyway should skip actually executing the contents of the migrations\noutOfOrder                   : Allows migrations to be run \"out of order\"\ncallbacks                    : Comma-separated list of FlywayCallback classes, or locations to scan for FlywayCallback classes\nskipDefaultCallbacks         : Skips default callbacks (sql)\nvalidateOnMigrate            : Validate when running migrate\nvalidateMigrationNaming      : Validate file names of SQL migrations (including callbacks)\nignoreMissingMigrations      : Allow missing migrations when validating\nignoreIgnoredMigrations      : Allow ignored migrations when validating\nignorePendingMigrations      : Allow pending migrations when validating\nignoreFutureMigrations       : Allow future migrations when validating\ncleanOnValidationError       : Automatically clean on a validation error\ncleanDisabled                : Whether to disable clean\nbaselineVersion              : Version to tag schema with when executing baseline\nbaselineDescription          : Description to tag schema with when executing baseline\nbaselineOnMigrate            : Baseline on migrate against uninitialized non-empty schema\nconfigFiles                  : Comma-separated list of config files to use\nconfigFileEncoding           : Encoding to use when loading the config files\njarDirs                      : Comma-separated list of dirs for Jdbc drivers & Java migrations\ncreateSchemas                : Whether Flyway should attempt to create the schemas specified in the schemas property\ndryRunOutput                 : [teams] File where to output the SQL statements of a migration dry run\nerrorOverrides               : [teams] Rules to override specific SQL states and errors codes\noracle.sqlplus               : [teams] Enable Oracle SQL*Plus command support\nlicenseKey                   : [teams] Your Flyway license key\ncolor                        : Whether to colorize output. Values: always, never, or auto (default)\noutputFile                   : Send output to the specified file alongside the console\noutputType                   : Serialise the output in the given format, Values: json\n\nFlags\n-----\n-X          : Print debug output\n-q          : Suppress all output, except for errors and warnings\n-n          : Suppress prompting for a user and password\n-v          : Print the Flyway version and exit\n-?          : Print this usage info and exit\n-community  : Run the Flyway Community Edition (default)\n-teams      : Run the Flyway Teams Edition\n\nExample\n-------\nflyway -user=myuser -password=s3cr3t -url=jdbc:h2:mem -placeholders.abc=def migrate\n\nMore info at https://flywaydb.org/documentation/commandline\n",
                    "output_type": "stream"
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": [
                "# A simple test of Flyway's info command using the H2 in memory database"
            ],
            "metadata": {
                "azdata_cell_guid": "36a52642-918e-45d7-964b-58bfcf6c3d52"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "docker run --rm flyway/flyway -url=jdbc:h2:mem:test -user=sa info"
            ],
            "metadata": {
                "azdata_cell_guid": "128b53d5-fe0d-47be-90dc-1b22342bcd9d"
            },
            "outputs": [
                {
                    "name": "stdout",
                    "text": "Flyway Community Edition 7.0.3 by Redgate\nDatabase: jdbc:h2:mem:test (H2 1.4)\nSchema version: << Empty Schema >>\n\n+----------+---------+-------------+------+--------------+-------+\n| Category | Version | Description | Type | Installed On | State |\n+----------+---------+-------------+------+--------------+-------+\n| No migrations found                                            |\n+----------+---------+-------------+------+--------------+-------+\n\n",
                    "output_type": "stream"
                }
            ],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Let's talk to a SQL Server\n",
                "\n",
                "I'm using a config file here, by passing in a volume with -v. We are naming the volume /flyway/conf.\n",
                "\n",
                "-   This needs to be an absolute path to the folder where you have flyway.conf\n",
                "-   You will need to edit the connection string, user, and password in flyway.conf\n",
                "-   You will need to create a database named GitForDBAs (or change the config file to reference a database of another name which already exists)\n",
                "\n",
                "I'm using a second volume mapping to a folder that holds my flyway migrations. We are naming the volume /flyway/sql.\n",
                "\n",
                "-   This needs to be an absolute path to the folder where you have migrations stored\n",
                "-   The filenames for the migrations matter -- Flyway uses the file names to understand what type of script it is and the order in which it should be run\n",
                "\n",
                "Note: I have spread this across multiple lines using the \\` character for readability purposes  \n",
                "\n",
                "# Call Flyway info to inspect"
            ],
            "metadata": {
                "azdata_cell_guid": "7c2ae31c-ef6d-4fea-ac23-6f6fbfd9b1ce"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "docker run --rm `\r\n",
                "    -v C:\\Git\\GitForDBAs\\flywayconf:/flyway/conf  `\r\n",
                "    -v C:\\Git\\GitForDBAs\\migrations:/flyway/sql  `\r\n",
                "    flyway/flyway info"
            ],
            "metadata": {
                "azdata_cell_guid": "bdbc60ea-81d6-4108-8008-a348975f113e"
            },
            "outputs": [
{
    "name": "stdout",
    "text": "Flyway Community Edition 7.0.3 by Redgate\nDatabase: jdbc:jtds:sqlserver://host.docker.internal:1433/GitForDBAs (Microsoft SQL Server 14.0)\nSchema version: << Empty Schema >>\n\n+-----------+---------+-----------------------+------+--------------+---------+\n| Category  | Version | Description           | Type | Installed On | State   |\n+-----------+---------+-----------------------+------+--------------+---------+\n| Versioned | 1       | Initial               | SQL  |              | Pending |\n| Versioned | 2       | YOLO                  | SQL  |              | Pending |\n| Versioned | 2.1     | ILikeDags             | SQL  |              | Pending |\n| Versioned | 2.2     | InsertRowsInILikeDags | SQL  |              | Pending |\n| Versioned | 2.3     | livedemo              | SQL  |              | Pending |\n+-----------+---------+-----------------------+------+--------------+---------+\n\n",
    "output_type": "stream"
}
],
            "execution_count": 4
        },
        {
            "cell_type": "markdown",
            "source": [
                "#  Call Flyway migrate to execute"
            ],
            "metadata": {
                "azdata_cell_guid": "3a547590-2187-4544-a121-1de4ba2e465f"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "docker run --rm `\r\n",
                "    -v C:\\Git\\GitForDBAs\\flywayconf:/flyway/conf  `\r\n",
                "    -v C:\\Git\\GitForDBAs\\migrations:/flyway/sql  `\r\n",
                "    flyway/flyway migrate"
            ],
            "metadata": {
                "azdata_cell_guid": "4c8dd9da-d4f8-4507-9534-abdf4bc14ef0"
            },
            "outputs": [
{
    "name": "stdout",
    "text": "Flyway Community Edition 7.0.3 by Redgate\nDatabase: jdbc:jtds:sqlserver://host.docker.internal:1433/GitForDBAs (Microsoft SQL Server 14.0)\nSuccessfully validated 5 migrations (execution time 00:00.035s)\nCreating Schema History table [GitForDBAs].[dbo].[flyway_schema_history] ...\nCurrent version of schema [dbo]: << Empty Schema >>\nMigrating schema [dbo] to version \"1 - Initial\"\nMigrating schema [dbo] to version \"2 - YOLO\"\nMigrating schema [dbo] to version \"2.1 - ILikeDags\"\nMigrating schema [dbo] to version \"2.2 - InsertRowsInILikeDags\"\nMigrating schema [dbo] to version \"2.3 - livedemo\"\nSuccessfully applied 5 migrations to schema [dbo] (execution time 00:00.235s)\n",
    "output_type": "stream"
}
],
            "execution_count": 5
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Examine the table - open a new query\n",
                "\n",
                "USE GitForDBAs;\n",
                "\n",
                "GO\n",
                "\n",
                "  \n",
                "\n",
                "EXEC sp\\_help 'dbo.HelloWorld';\n",
                "\n",
                "GO\n",
                "\n",
                "  \n",
                "\n",
                "SELECT \\*  FROM dbo.HelloWorld;\n",
                "\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "2001f5ec-2b5a-4874-9800-fb17c635f227"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Call Flyway clean to drop everything 🔥🔥🔥"
            ],
            "metadata": {
                "azdata_cell_guid": "9a2e8358-8c3b-4a66-a870-19067dfae5a6"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "docker run --rm `\r\n",
                "    -v C:\\Git\\GitForDBAs\\flywayconf:/flyway/conf  `\r\n",
                "    -v C:\\Git\\GitForDBAs\\migrations:/flyway/sql  `\r\n",
                "    flyway/flyway clean"
            ],
            "metadata": {
                "azdata_cell_guid": "51772993-1171-4c26-9e95-eb7b44217dec",
                "tags": []
            },
            "outputs": [
{
    "name": "stdout",
    "text": "Flyway Community Edition 7.0.3 by Redgate\nDatabase: jdbc:jtds:sqlserver://host.docker.internal:1433/GitForDBAs (Microsoft SQL Server 14.0)\nSuccessfully dropped pre-schema database level objects (execution time 00:00.002s)\nSuccessfully cleaned schema [dbo] (execution time 00:00.177s)\nSuccessfully dropped post-schema database level objects (execution time 00:00.007s)\n",
    "output_type": "stream"
}
],
            "execution_count": 3
        }
    ]
}