Using Visual Studio Code with Snowflake
BREAKING NEWS! https://noazdad.medium.com/snowflakes-new-vscode-plugin-is-here-c1e4f3a55a01
Visual Studio Code is a free, cross-platform Integrated Development Environment (IDE) available from Microsoft, and compatible with Windows, Mac OS/X, and Linux. It’s the “baby brother” of their larger, more feature-rich Visual Studio IDE. The choice of IDE among developers is a fairly religious battle, but I’ve become very fond of VSCode and it’s my go-to for just about everything I do. One of VSCode’s great features is its simple extensibility. Rather than relying on Microsoft to add features to the IDE, which would delay release dates and bloat the base IDE package, they’ve built a simple extension mechanism called Plugins. There’s a plugin for nearly everything, from programming languages, to database development, to cloud platform integration. There’s an entire marketplace of plugins, available at visualstudio.com.
Even the plugins themselves are extensible, with one building on the work of another. An example of this is the SQLTools plugin, which provides a basic framework for working with all sorts of databases. The base SQLTools plugin comes with standard drivers for databases like Oracle, SQLServer, Postgres, and MySQL. But I use Snowflake, and we’ve not been invited to that party.
Until now!
There is a new VSCode plugin for working with Snowflake! It extends the SQLTools plugin, so you have to install that one first, but installing any plugin is a simple button click.
Creating a Snowflake connection
Once the plugins are installed, and VSCode has been reloaded, you should see a new SQLTools icon in the left-hand tray that looks like a database. Click that to get to the Connections dialog panel.
If you have defined connections to other databases, they’ll appear here. To create a new connection, click the Add new Connection button. This opens a 3-step wizard dialog.
The first step is to choose the Snowflake connector.
In step 2, you fill out the details of the Snowflake connection. Make sure that you don’t include the snowflakecomputing.com part of your Snowflake URL in the Account field.
Database and Warehouse name are required, while Schema and Role are optional fields.
Click TEST CONNECTION to see if you got everything correct, and SAVE CONNECTION to complete this screen.
Step 3 allows you to review all the settings and make the connection to your Snowflake account.
Using the Snowflake plugin
Once the connection is successful, you can use the Treeview on the left side to drill down into databases, schemas, tables, and views.
Note: userids that use multi-factor authentication may be challenged more frequently than desired. To stop this behavior, set ALLOW_ID_TOKEN to TRUE.
The plugin gives you a SQL scratchpad, just like the Worksheets tab in the Snowflake UI. It even has autocomplete for SQL statements, and built-in statement history.
There’s no blue Run button, so executing a query is a little different than in the Snowflake web UI. Once you have a query defined in the script, select the entire query text and press cmd-E, cmd-E (yes, press the key combination twice).
To emulate the web UI behavior of executing the current query (by placing the cursor anywhere in the query and pressing a key combination), just map that key combination to the Run Current Query command. I wanted to use the familiar cmd-Enter, but the problem is that several other plugins already use cmd-Enter as a mapped keystroke. I chose cmd-ctrl-Enter instead.
To map a keystroke combination to a SQLTools function, select View > Command Palette. That opens up a search field at the top. Enter SQLTools and scroll down to the entry for Run Current Query. Click the “gear” icon way on the right hand side of that line.
Now click the “pencil” icon on the left hand side. I already have the key mapping set, but your Keybinding value should be blank.
That opens a simple dialog where you type the keystroke combination you’d like to map to the command. Enter whatever you choose — I used cmd-ctrl-Enter — then press Enter to complete the mapping. You can now place the cursor anywhere in the SQL statement (between any two semi-colons), and type the keystroke combo to execute that statement.
A new results panel will open on the right side of the screen with the results of the query. From there, you can save the results as CSV or JSON. You can open the SQLTools console at the bottom of the screen to see additional feedback. There’s even recent query history, with the ability to re-execute queries without having to copy/paste them into the script window.
Source Code Integration
The new Snowsight UI (currently in Preview mode) will add the ability to share scripts between developers, which is a great addition. Integration with source code control tools like Git and Subversion is a stated direction, but still a ways off yet. Until then, you can use the native Git integration that comes with VSCode. Remember that the SQL scratchpad provided here is really just a text file editor, so you can manage Snowflake source code as separate .SQL files. The native Git integration lets you push and pull those files into a Git repository.
Conclusion
The new Snowflake SQLTools plugin for VSCode is a welcome addition to the toolbelt for any Snowflake developer. The extensible plugin architecture allows new features like this to be developed and released independently from the IDE vendor.
Let it Snow!
