Redgate Flyway

This tutorial shows you how to connect Flyway to Snowflake, using:

Programmatic access token (PAT)
key-pair authentication
password with MFA

For Snowflake Postgres, which is a managed PostgreSQL service, see Advanced capabilities for Snowflake instead.

Prerequisites

A Snowflake account, and a user and role for Flyway to connect as.
A warehouse that Flyway can use.
A database to deploy to and, if you'll generate migrations, a second database for your shadow environment.
Flyway Desktop 9.10.10 or later, if you're connecting from Flyway Desktop.
To generate migrations, view changes or check for drift: Flyway Teams or Enterprise in Flyway Desktop, or Flyway Enterprise for the generate and check -drift commands.

The Snowflake JDBC driver ships with Flyway, so there's nothing extra to install.

Getting your connection details

In Snowsight, open your account menu and select "Connect a tool to Snowflake".
Note the "Account/Server URL", for example myorganization-myaccount.snowflakecomputing.com.
Go to the "Connectors/Drivers" tab
Select "JDBC Connection String" from the "Select Connector or Driver" list
Choose the Warehouse, Database and Connection method.

The JDBC connection string is displayed and can be copied.

In Flyway Desktop, you can paste the JDBC connection string into the connection dialog and the individual fields are filled in for you.

When using the command line, use the JDBC connection string as the url.

The JDBC URL like this:

Flyway connecting to a JDBC URL

jdbc:snowflake://<server-hostname>/?user=<my-user>&warehouse=<my-warehouse>&db=<my-db>

db and warehouse are required. You can also add role, and schema to set the schema Flyway creates its schema history table in. That's PUBLIC if you don't set it. See the Snowflake driver reference for the other parameters.

It is recommended to not write secrets directly into flyway.toml if you intend to check it into version control. The examples below read them from environment variables, but you can also use any of Flyway's secret resolvers.

Connecting with a programmatic access token

Programmatic access tokens (PATs) are the quickest to set up and work in pipelines and Flyway Desktop. Flyway uses the token in place of a password.

Setting up a network policy

By default, Snowflake won't let a user create or use a PAT unless a network policy applies to them. If one doesn't already, an administrator can create one that limits which IP addresses can connect, and assign it to the user:

CREATE NETWORK POLICY flyway_policy ALLOWED_IP_LIST = ('<my ip addresses>');
ALTER USER flyway_user SET NETWORK_POLICY = flyway_policy;

Specify the IP range with the addresses Flyway connects from, such as your build agents. See Network policies in the Snowflake documentation.

Creating the token

In Snowsight, open your account menu and select "Settings" > "Authentication".
Under "Programmatic access tokens", click "Generate new token".
Give it a name, choose when it expires, and optionally restrict it to a single role.
Copy the token. Snowflake shows it only once.

An administrator can also create a token for another user, such as a service user, with

ALTER USER … ADD PROGRAMMATIC ACCESS TOKEN.

Connecting using the token

Add an environment to flyway.toml, and point password at the token, which this example reads from an environment variable rather than storing it in the file:

[environments.development]
url = "jdbc:snowflake://myorganization-myaccount.snowflakecomputing.com/?db=MY_DB&warehouse=MY_WH"
user = "FLYWAY_USER"
password = "${env.SNOWFLAKE_PAT}"

Create a new project, and choose "Snowflake" as the database type.
In the connection dialog, paste your JDBC connection string, or fill in "Server hostname", "Warehouse" and "Database".
Under "Authentication", choose "Username and password or PAT". Enter your username, and paste the token into "Password or PAT". Flyway Desktop keeps it in your operating system's secret store.
Click "Test connection", then "Save".

If you use a token in a pipeline, create it for a Snowflake service user rather than a named person. Tokens expire, so plan to rotate them before they do.

Connecting using key pair authentication

Key-pair authentication suits pipelines and other unattended use. Flyway signs in with a private key, so there's no password or token to rotate.

Setting up the key pair

1. Generate a private key and its public key:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

2. Assign the public key to the Snowflake user. Paste the contents of rsa_key.pub without the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- lines:

ALTER USER flyway_user SET RSA_PUBLIC_KEY = '<my-public-key>';

3. Keep rsa_key.p8 somewhere only Flyway can read it, such as your pipeline's secure files.

For an encrypted private key, or to rotate keys, see the Snowflake driver reference and Key-pair authentication in the Snowflake documentation.

Connecting using the key pair

Add `authenticator=snowflake_jwt` and the absolute path to the private key to the URL. No password is needed:

[environments.development]
url = "jdbc:snowflake://myorganization-myaccount.snowflakecomputing.com/?db=MY_DB&warehouse=MY_WH&authenticator=snowflake_jwt&private_key_file=/path/to/rsa_key.p8"
user = "FLYWAY_USER"

Create a new project, and choose "Snowflake" as the database type.
In the connection dialog, paste a JDBC URL that includes authenticator=snowflake_jwt and private_key_file, as in the command-line example.
Check that "Authentication" shows "Other (configure in JDBC URL)", and enter your username.
Click "Test connection", then "Save".

Connecting with a password and MFA

If your Snowflake user signs in with a password and multi-factor authentication (MFA), Flyway can do the same. Each sign-in may need you to approve a push notification in your authenticator app, so this is for interactive use only. Use a programmatic access token or key-pair authentication for pipelines.

Only push notification authenticator apps are supported. Flyway can't use TOTP codes or passkeys.

Reducing MFA prompts

To avoid approving every connection, an administrator can turn on MFA token caching for the account:

ALTER ACCOUNT SET ALLOW_CLIENT_MFA_CACHING = TRUE;

The driver then keeps a token in your operating system's secret store, and you're only prompted again when it expires. See Using MFA token caching in the Snowflake documentation.

Connecting using a password and MFA

Add authenticator=username_password_mfa to the URL, and point password at your password. This example reads it from your operating system's secret store using the Local Secret Resolver:

[environments.development]
url = "jdbc:snowflake://myorganization-myaccount.snowflakecomputing.com/?db=MY_DB&warehouse=MY_WH&authenticator=username_password_mfa"
user = "MY_USER"
password = "${localSecret.snowflakePassword}"

Create a new project, and choose "Snowflake" as the database type.
In the connection dialog, paste your JDBC connection string, or fill in "Server hostname", "Warehouse" and "Database".
Under "Authentication", choose "MFA", and enter your username and password.
Click "Test connection", and approve the notification in your authenticator app. Then click "Save".

Granting permissions

To connect, the role Flyway uses needs USAGE on the warehouse. To work with anything in the database, it also needs USAGE on the database and on every schema in it. Replace flyway_role with your role, and grant the role to the Flyway user if it isn't already:

GRANT USAGE ON WAREHOUSE MY_WH TO ROLE flyway_role;
GRANT USAGE ON DATABASE MY_DB TO ROLE flyway_role;
GRANT USAGE ON ALL SCHEMAS IN DATABASE MY_DB TO ROLE flyway_role;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE MY_DB TO ROLE flyway_role;
GRANT ROLE flyway_role TO USER flyway_user;

Deploying, drift checks and generating migrations need more privileges, including MONITOR on dynamic tables. See Database permissions. If privileges are missing, Snowflake hides objects from drift checks and generated migrations rather than reporting an error.

Testing the connection

From the command line, run:

flyway testConnection -environment=production

This opens a connection to Snowflake without changing anything. With Flyway Enterprise, it also checks that the comparison engine can connect.

In Flyway Desktop, use "Test connection" in the connection dialog.