Wow! It has been a year since my last blog post.
This post was originally going to cover getting #Flyway set up with Snowflake. Then a couple of days ago, my friend Kent Graziano tweeted a link on getting Visual Studio Code (VSC) connected to Snowflake. Considering I had just come off a lengthy project where VSC was the IDE of choice, I figured, what the heck let’s get this set up. I’m sure to use this in some capacity in the future.

I then decided to see how I can use both VSC and Flyway in an, at least to me, an interesting way. Here’s what I did.

Configure your Snowflake Account:
Log into Snowflake and create the following objects to use the setup examples in this blog post:

CREATE OR REPLACE DATABASE DEVOPS;
CREATE OR REPLACE SCHEMA FLYWAY;

CREATE OR REPLACE DATABASE QA_DB;
CREATE OR REPLACE SCHEMA FLYWAY;

CREATE WAREHOUSE QA WITH WAREHOUSE_SIZE = 'XSMALL' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
CREATE WAREHOUSE DEVOPS WITH WAREHOUSE_SIZE = 'XSMALL' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;

Install and Configure Flyway:
Installation:
I am using a MacBook, so Homebrew was the best way to go for me.
Here is the link for that: Flyway

One thing to note, is this method of installation, created all the necessary directories here: /usr/local/Cellar/flyway/7.5.1/ with a symlink to /usr/local/bin/flyway. You can leave that alone, but I made some changes to aid in the remainder of this exercise.

1. Create a directory for flyway:
   • ➜  ~ mkdir ~/development/flyway/
2. Create a sql directory:
   • ➜  ~ mkdir ~/development/flyway/sql/
3. Create a conf directory:
   • ➜  ~ mkdir ~/development/flyway/conf/
4. Copy the flyway.conf file from /usr/local/Cellar/flyway/7.5.1/libexec/conf/ to ~/development/flyway/conf/
5. (OPTIONAL) Download and copy the latest Snowflake JDBC driver to the /usr/local/Cellar/flyway/7.5.1/libexec/drivers directory. At the time of this post, it is located here. Since you will ultimately need a Snowflake account you can also get to the driver from within the Snowflake UI.
   • Flyway includes a version of the Snowflake driver which will work. If you decide to use the newer version, delete the version included with the Flyway install
6. I am using zsh, so modify your .zshrc file to point to the flyway.conf file copied in step 3
   • export FLYWAY_CONFIG_FILES=/Users/db/development/flyway/conf/flyway.conf

 

Configuration:
The configuration was simpler than I thought. The important thing here will be to make sure that your Snowflake credentials are correct and you have Snowflake set up properly.

First, we need to make some changes to the flyway.conf file we copied over. Find the following lines in the flyway.conf file. If you created the objects in your Snowflake account, you can update the bolded information with the appropriate information based upon your account information, and pasted these lines directly into your flyway.conf file.

flyway.url=jdbc:snowflake://account.snowflakecomputing.com/?db=QA_DB&warehouse=QA&schema=FLYWAY

# User to use to connect to the database. Flyway will prompt you to enter it if not specified, and if the JDBC
# connection is not using a password-less method of authentication.
flyway.user=<username>

# Password to use to connect to the database. Flyway will prompt you to enter it if not specified, and if the JDBC
# connection is not using a password-less method of authentication.
flyway.password=<password>

*Login information is passed in clear text with this setup. Flyway does support private/public key authentication with Snowflake.

Check your Flyway Connection:
Create a file in the ~/development/flyway/sql/ directory called “V1__create_table.sql” with the following code:
(*V1 is followed by two underscores )

/* CREATE TABLE */
create or replace table flyway.flyway_demo
(name VARCHAR);

 

From your command prompt execute the migration:

you may see some warnings, but ultimately ‘Successfully applied 1 migration to schema “FLYWAY”‘ is what we are looking for. Now we can Snowflake to confirm that the table was created.

As we can see, the FLYWAY_DEMO table has been created in the QA_DB under the FLYWAY schema, which we set up in the earlier steps. You will also notice a flyway_schema_history table. This is a table that Flyway uses to track migration history, and created and managed by the Flyway application.

To get us ready for the rest of this exercise we will use Flyway to “clean up” this environment by executing “flyway clean” from the command prompt:

and a quick look at Snowflake show no objects in the FLYWAY schema in the QA_DB database.

Add Snowflake extension to Visual Studio Code:
Take a look here (Snowflake: VS Code, SQL Tools, and GitHub) for the post I used to add and configure the Snowflake extension.

Refer to the image below for connection values to continue using the examples in this post

After completing the steps go ahead and confirm you can connect to Snowflake from Visual Studio Code, and run something simple like
SELECT current_date() FROM dual;

You should have something similar to this.

What can we do now?
Now that we have all the tools in place and everything properly connected and tested, where do we go from here?

I thought it would be fun to bring these two things together and work through a process where I can test my code in a DEV environment (our DEVOPS Database) and deploy my tested code into QA within the same IDE. Let’s get it started!

Launch Visual Studio Code and connect to the DEVOPS database in Snowflake and write one CREATE TABLE sql statement.

Run this sql statement and confirm the table gets created in the DEVOPS schema.
Save the file in the ~/development/flyway/sql/ directory as V1__create_table.sql

 

Now that we have confirmed that the sql statement works and successfully created the desired table in the DEVOPS database, let’s deploy this to the QA_DB database.

Bring up the terminal window in Visual Studio Code and from the command prompt, execute flyway migrate

Now, we can create and test an sql statement in DEVOPS and deploy that working code to QA_DB within a single IDE. Let’s confirm that we now have a new table and the flyway_schema_history table in Snowflake

As we continue to develop our data warehouse, we need to modify the table we just created. Let’s do a simple ALTER TABLE statement.

Opps! The code didn’t work! Being able to test the validity of the code before hand, can let us catch issues prior to deployment to higher environments.  Let’s go correct that.

 

Much better! Now that we have working code and can confirm that the table has been ALTERed by the addition of the new column. Finally, we can save this file as V2__alter_table.sql in the ~/development/flyway/sql/ directory and run flyway migrate from the terminal in the Visual Studio Code IDE.

There we have it. Changes test in DEVOPS and deployed to QA_DB within the Visual Studio Code IDE

 

Things to consider are multiple developers. Maybe GitHub integration, which I have seen mentioned, might be an option.

Enjoy!