Azure SQL Database: CLI on Mac OS X

  • Article

In the last blog [Azure SQL Database: SQLCMD and BCP on Ubuntu Linux], we looked at connecting and querying an Azure SQL Database from the command line on Ubuntu Linux with SQLCMD. In this blog we are going to connect and query an Azure SQL Database from the Mac OS X Terminal using the mssql utility [source] – cross platform command line interface for SQL Server and Azure SQL Database (mssql also works on Linux or wherever Node.js runs).

If you want to keep up to date with Azure SQL Database, follow us on Twitter (@AzureSQLDB). If you have questions, please ask us on [Stack Overflow] or [MSDN].

Prerequisites:

  • Access to an Azure SQL Database [link] or SQL Server (Express [link])
  • Install Node.js and npm on Mac OS X [link]

Using Terminal, install the mssql utility:

 npm install -g sql-cli

Connect to Azure SQL Database (similar for SQL Server):

 mssql -s yourServerName.database.windows.net -u yourUsername@yourServerName -p yourPassword -d yourDatabase –e

Execute a command

 SELECT @@version

One of the cool features of the mssql utility is that it has a number of handy, built-in "usage hints" for listing databases, tables, and schema for a table.  This alleviates the need for having to remember and type queries such as "Select name from sys.databases".  Instead, it is simply ".databases".

 sql-cli version 0.4.5
 Enter ".help" for usage hints.
 mssql> .help
 
 command description 
 -------------- ------------------------------------------
 .help Shows this message 
 .tables Lists all the tables 
 .databases Lists all the databases 
 .read FILENAME Execute commands in a file 
 .run FILENAME Execute the file as a sql script 
 .schema TABLE Shows the schema of a table 
 .indexes TABLE Lists all the indexes of a table 
 .analyze Analyzes the database for missing indexes.
 .quit Exit the cli 
 
 mssql>

In the next blog, we'll look at querying Azure SQL Database from a graphical tool on Mac OS X.