June 2017

Volume 32 Number 6

[Data Points]

Visual Studio Code: Create a Database IDE with MSSQL Extension

By Julie Lerman | June 2017

While Visual Studio is still my go-to IDE for heavy-­duty .NET development, I’ve fallen head over heels with Visual Studio Code (VS Code), whether I’m working in C# or JavaScript or Node and whether I’m working in Windows or in macOS. Of course, there are many other languages and frameworks supported by VS Code and its myriad extensions, but these are my current toolsets. I’m not alone in my love for VS Code. During the Visual Studio 2017 launch in March 2017, Microsoft announced that VS Code had 1.3 million active monthly users. What’s really great is that, as with any cross-platform tool, each member of your team can pick their favorite OS, yet all can still use the same coding tools.

And then there’s data—there’s always data involved. I spent years and years working only with SQL Server and coding Windows applications. But in the past few years, I’ve expanded my horizons—not only with a new IDE (VS Code), but also new databases and new platforms (as I type this on my MacBook Pro).

My foray into VS Code began as part of my wanderings into Node.js, which you’ve witnessed in this very column. That was originally on Windows. But because VS Code is cross-platform (built with Electron), I eventually found myself going back and forth, working on the same code, sometimes in Windows, sometimes in macOS with GitHub as the common denominator. Thanks to the C# extension and the cross-platform nature of .NET Core, I eventually reached beyond Node.js to write .NET Core apps with EF Core in both environments. When in Visual Studio, I rely heavily on the built-in SQL Server Data Tools and the SQL CE/SQLite Toolbox extension to explore most of the data my apps are creating. But with VS Code, I need something external to explore the data. When using Windows and the super lightweight VS Code, it never felt right to open up SQL Server Management Studio, which is anything but lightweight. For other databases, whether on my Windows or Mac boxes, I’ve also been using JetBrains DataGrip (jetbrains.com/datagrip), a cross-platform database tool that supports a slew of databases.

But as the universe of extensions for VS Code has grown (to just short of 3,000 as I’m writing this in late April 2017), a number of extensions for interacting with data stores have come to the table. Two that I’ve already worked with are the mssql extension (bit.ly/2gb2ICf) from the SQL Server team, and the vscode-database extension for MySQL and PostgreSQL (bit.ly/2mh8nYF). These allow you to write and execute SQL against your databases. You can see demos of both in my Pluralsight course, “Entity Framework Core: Getting Started.” There are other data-related extensions, as well—for example, to interact with Azure Data Lake (also from Microsoft), Redis and SalesForce data. Some of these are still in preview, including the mssql extension.

In this article, I’m going to show you some of the basics of using the mssql extension. Originally I planned to write about both mssql and vscode-database, but mssql is so rich in features, I had a lot of fun exploring it and easily filled up my allotment of words for this column. It makes no difference whether you’re on Windows, macOS or Linux to use mssql.

The mssql extension lets you interact with a variety of SQL Server databases: Microsoft SQL Server, Azure SQL Database and SQL Data Warehouse. As I happen to be sitting in front of a MacBook, I’ll connect to an external SQL Server. You may be aware that SQL Server now runs on Linux—is that not amazing? That means you could spin up a Docker container that runs SQL Server. I won’t do that for this article, but I’ve written a blog post about that, which you can read at bit.ly/2qaev9r. What I will do, however, is connect to an Azure SQL Server database in the cloud. This is a powerful reminder that you don’t need to be a Windows developer or admin or a C# developer to take advantage of SQL Server. You can manage it on the Azure portal and connect to it from any type of app. So, imagine your app is PHP and you’re using the PHP extension for VS Code and you’re talking to a SQL Server database on Azure. 

I took advantage of the free Azure account and credits that come with a Visual Studio subscription to create an Azure SQL database based on the AdventureWorks sample. The documentation to do this is at bit.ly/2o2IDTy.

Back in VS Code, you can install the mssql extension by clicking on the extension icon in the VS Code Activity Bar, filtering on mssql and then clicking its Install button. You’ll be prompted to reload VS Code when it’s ready, but keep in mind that it will delay installing part of the extension until you use one of its commands. Note that for macOS, you’ll need to install OpenSSL. Check the mssql documentation link (provided earlier) for details.

In addition to the query execution engine that’s installed, mssql places a slew of commands in the VS Code command palette. It makes sense to start by connecting to your database, although other functions will prompt you to connect if you haven’t already.

Open the palette with F1 (or Ctrl or Command+Shift+P if you have one of those funny keyboards without function keys) and type MS SQL to filter on all of the mssql commands. If you don’t have any other extensions that provide commands with the SQL keyword, just SQL will do the trick. I love that you can even get to help files with the Getting Started Guide command. You can also store different connections and then connect easily with the Manage Connection Profiles feature.

The easiest way to interact with mssql is by having a file open for editing and ensuring that VS Code knows you’re editing SQL. You can do this whether you have a folder or project open thanks to the New query command. That will create a SQLQuery.sql file and the sql extension will cause VS Code to switch to the mssql editor. The current editor is noted in the lower right-hand corner of VS Code, and this changes based on file exten­sions to give you proper IntelliSense and other relevant features provided by the extension. You can click on what’s displayed to change it if needed. The mssql editor will not only give you help writing TSQL, it also knows how to execute queries and perform other tasks defined by the extension.

With a SQL file open in the editor, selecting MS SQL: Connect from the Command Palette will display a list of existing connection profiles you’ve already created and let you create a new one. Select Create Connection Profile and you’ll then be prompted for each key element of the connection string. For example, the first prompt asks for the Server name, as shown in Figure 1. My SQL Azure database is on a server named thedatafarmsqlerver.database.windows.net, so that’s what I’ll enter.

Figure 1 Connecting to a SQL Database with the mssql Extension

Next, you’ll be prompted to enter the database name, your login and password, then an optional profile name. By the way, Manage Connection Profiles can also lead you to this point as it has a Create menu option.

Once you’ve filled out the connection information and are successfully connected, the profile will get saved in the VS Code settings file. With version 0.3, if the connection fails, the profile won’t get stored; but that experience is on deck to change. You can look at the stored profile by going to Preferences and Settings from the VS Code menu or via the Ctrl or Command+, (comma) keystroke combo. Here’s an example of a connection profile:

"mssql.connections": [
  {
    "authenticationType": "SqlLogin",
    "server": "thedatafarmsqlserver.database.windows.net",
    "database": "AdventureWorksSample",
    "user": "me",
    "password": "mypassword",
    "savePassword": true,
    "profileName": "AzureAWSample"}
  }
]

With profiles stored in the settings, you have the option to select AzureAWSample or other stored profiles when you want to connect.

Once connected, you can start writing and executing TSQL. The easiest way to do that is to have a file with the SQL extension open in the editor. As I mentioned, this forces the SQL editor features to kick in, and one of its wonderful features is the built-in TSQL snippets.

Begin by typing sql in the editor window and IntelliSense will display the list of built-in snippets, as shown in Figure 2.

Figure 2 TSQL Snippets Provided by the mssql extension

You can see that this isn’t limited to simply querying existing data—mssql will execute any (valid and permissible) TSQL. I like to list the databases or tables and views from a selected database to verify that I’ve connected to the correct database, and the ListTablesAndViews snippet is great for that. Normally, I’d have to ask Dr. GoogleBing to help me with TSQL like this, so I’m extra grateful for this snippet.

Pressing Ctrl or Command+Shift+E will execute the command. You can also select the text and right-click for the context menu, which includes an Execute option.

A results pane opens up with the response like the one in Figure 3.

Figure 3 Results of a Schema Query

In the upper right, notice there are two small icons. The first lets you save the results as a CSV text file. The second is for saving the results as JSON. Let’s do a query that gets Customer data, see what those results look like, then save them to JSON. With the list of tables in front of me, I can start typing my query and, as Figure 4 shows, IntelliSense kicks in with knowledge of the database schema.

Figure 4 IntelliSense Reads the Schema and Helps Build TSQL

I modified my select statement to get three columns from Sales­LT.Customer. Then, I was able to highlight the statement and use the Ctrl or Command+Shift+E keyboard shortcut again to execute just that query. As with SQL Server Management Studio, I can execute one or more statements and, in response, see one or more result sets.

The results are presented in a grid as shown in Figure 5. You can highlight one or more row and column combinations and then click the CSV or JSON icons, which will then prompt you for a file name to save to. And you can easily select all of the data from a context menu by right-clicking on the grid.

Figure 5 Results of a Customer Data Query

The filename prompt displays the current folder path. If that’s where you want to save the file, you don’t have to retype the path. Just type the name of the file and it will be saved in that folder. I didn’t realize that the first few times, so learn from my mistake.

I selected only the first row of the customer data my query projected, and then used the Save to JSON icon, specifying a file name. Here’s the JSON that was output to my file:

[
  {
    "CustomerID": "1",
    "FirstName": "Orlando",
    "LastName": "Gee"
  }
]

Keep in mind that you can easily add your own snippets to VS Code. Let’s create one to list stored procedures and functions. Start by going to Preferences and User Snippets. You’ll see that the mssql extension added a template for SQL snippets. Choose that and the blank template will open. As you add more snippets, you’ll continue to work in this file. Also, if you’ve created some snippets to share (or perhaps find a bug or have another idea for mssql), this is an open source extension, and you can contribute by going to github.com/Microsoft/vscode-mssql to submit pull requests or issues.

After a long session with Dr. GoogleBing and testing various ideas, here’s the snippet I created to list all of the stored procedures and functions in the target database:

"List Stored Procedures": {
  "prefix": "sqlListStoredProcedures",
  "body": [
    "SELECT [Name],[Type_Desc] " ,
    "FROM [sys].[all_objects] " ,
    "WHERE ([Type] = 'P' OR [Type]='FN' OR [Type]='TF' OR [Type]='IF') ",
    "AND [Is_MS_Shipped] = 0"
  ],
  "description": "List Stored Procedures"
}

Now when I type sql in the editor window, sqlListStoreProcedures is among the options. The results of executing that command against my target database are:

Name                         Type_Desc
uspPrintError                SQL_STORED_PROCEDURE
uspLogError                  SQL_STORED_PROCEDURE
ufnGetAllCategories          SQL_TABLE_VALUED_FUNCTION
ufnGetSalesOrderStatusText   SQL_SCALAR_FUNCTION
ufnGetCustomerInformation    SQL_INLINE_TABLE_VALUED_FUNCTION

I was able to share this output by right-clicking on the results grid and selecting its “Copy with headers” option.

As the late-night television ads say, “But wait! There’s more!” The editor window has a context menu, as well (see Figure 6).

Figure 6 Editor Window Context Menu

The most interesting items on it (in my humble opinion) are Go to Definition and Peek Definition. If you select a table name in the edit window—for example, Customer in the command shown in Figure 5—these commands will show you the CREATE script for the customer table.

The mssql extension is constantly evolving and I’m looking forward to future updates. The version I’ve shared here is still a preview, version 0.3.0. If you have it installed in Visual Studio Code, you’ll be notified of updates. You can watch and participate in its evolution on its GitHub site at aka.ms/mssql-marketplace.

Julie Lerman is a Microsoft Regional Director, Microsoft MVP, software team mentor and consultant who lives in the hills of Vermont. You can find her presenting on data access and other topics at user groups and conferences around the world. She blogs at thedatafarm.com/blog and is the author of “Programming Entity Framework,” as well as a Code First and a DbContext edition, all from O’Reilly Media. Follow her on Twitter: @julielerman and see her Pluralsight courses at juliel.me/PS-Videos.

Thanks to the following Microsoft technical experts for reviewing this article: Kevin Cunnane, Eric Kang and Sanjay Nagamangalam

Discuss this article in the MSDN Magazine forum