Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Installing and Using mssql-cli on Linux for SQL Server


By:   |   Last Updated: 2018-11-27   |   Comments   |   Related Tips: More > Tools

Problem

Microsoft recently released an interactive, cross-platform command line query tool called mssql-cli.  My previous tip new interactive command line tool mssqlcli for SQL Server, shows the mssql-cli installation and features on the Windows OS. In this tip, we will see how to install mssql-cli on Linux Ubuntu.

Solution

Microsoft released mssql-cli tool under the OSF (Open Source Foundation) BSD 3 license. The tool is officially available on the below platforms:

  • Windows (x64)
  • Windows (x86)
  • macOS 10.12+
  • Ubuntu 17.04
  • Ubuntu 16.04
  • Ubuntu 14.04
  • Debian 8.7+
  • Debian 9
  • CentOS 7
  • Red Hat Enterprise Linux 7
  • OpenSUSE 42.2+
  • SUSE Enterprise Linux (SLES) 12
  • Fedora 25
  • Fedora 26

Mssql-cli improves the interactive CLI experience for T-SQL and includes support for SQL Server, MySQL, and PostgreSQL. This tool provides great enhancements over SQLCMD due to its features and use.  This works great for developers, administrators, and DevOps specialists.

Some of the important features are:

  • Auto-completion
  • T-SQL IntelliSense
  • Syntax highlighting
  • Query history
  • Multi-line queries
  • Formatting for query results, including Vertical Format

Mssql-cli installation on Ubuntu Linux

Mssql-cli installation on Ubuntu depends upon the OS version. In this demo, my operating system version is Ubuntu 16.04.

You can check and verify the OS version using the below methods

Method:1

  • Open the terminal.
  • Enter the lsb_release --a command
Image 1: Ubuntu OS version

Method:2

  • Open "System Settings" from the desktop main menu in Unity.
  • Click on the "Details" icon under "System".
Image 2: Setting in ubuntu OS
  • See version information
Image 3: ubunutu OS version

Steps to install mssql-cli on Ubuntu 16.04

Mssql-cli for Linux, is published to package repositories for easy installation (and updates). This is the preferred method. Follow the below steps for the mssql-cli installation.

Step 1: Open terminal and import the public repository GPG keys.

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
Image 4: Import GPG keys

The wget command allows downloading files from the Internet using a Linux operating system such as Ubuntu.  It can be used to retrieve files using HTTP, HTTPS and FTP. Wget is non-interactive which gives great flexibility in using it. It can be easily called from scripts, cron jobs, terminals, etc. In this case, we have used it to import the public repository keys.

You can get more information about wget using wget --help.

Image 5: wget help

Step 2: Register the Microsoft Ubuntu repository

We will use curl for this step.

sudo curl -o /etc/apt/sources.list.d/microsoft.list https://packages.microsoft.com/config/ubuntu/16.04/prod.list

curl is a tool to download or transfer files/data from or to a server using FTP, HTTP, HTTPS, SCP, SFTP, SMB and other supported protocols on Linux or Unix-like system.

Image 6: Register Microsoft ubuntu repository

Step 3: Update the list of products

Sudo apt-get update

apt-get update downloads the package lists from the repositories and updates them to get information on the newest versions of packages and their dependencies. It will do this for all repositories and PPAs.

Image 7: Update the list of products

Step 4: Install mssql-cli

Now use the below command to install mssql-cli tool using apt-get command.

Sudo apt-get install mssql-cli

apt-get  (advance packaging tools) is the command-line tool for handling packages. It is a rapid, practical, and efficient way to install packages on your system. Dependencies are managed automatically, configuration files are maintained, and upgrades and downgrades are handled carefully to ensure system stability.

Image 8: install mssql-cli

This installs the packages, dependencies on Ubuntu.

Steps to install mssql-cli on Ubuntu 17.04

Similarly, we can install mssql-cli on Ubuntu 17.04 with the below steps.

Step 1: Import the public repository GPG keys

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

Step 2: Register the Microsoft Ubuntu repository

sudo curl -o /etc/apt/sources.list.d/microsoft.list https://packages.microsoft.com/config/ubuntu/17.04/prod.list

Step 3: Update the list of products

sudo apt-get update

Step 4: Install mssql-cli

sudo apt-get install mssql-cli

Explore mssql-cli on Ubuntu

Once we have installed mssql-cli on Ubuntu, we can check the mssql-cli help documentation using below command:

 mssql-cli --help
Image 9: mssql-cli help

Some of the important parameters for mssql-cli are:

-S SQL Server instance name or address
-U Username to connect to the database
-W Force password Prompt
-I Integrated Windows authentication
-d Database name to connect, if not specified it connect to master database.
-N Connect SQL Server if it uses SSL encryption
-V Version of the mssql-cli

Now let us connect to the SQL Server instance running on Ubuntu.  The syntax for connecting SQL instance is as shown below:

Mssql-cli -S 'SQL instance' -U 'user id' -P 'password'

Once connected, it gives a prompt to enter database queries.  By default, it connects to the SQL Server master database, so it prompts as master>.

Image10: Connect SQL instance using mssql-cli

Now we can start writing queries with the help of T-SQL intellisense. As soon as we start typing, we get a suggestion to write the query.

Image 11: T SQL intelligence

Mssql-cli also identifies the schema, table, views, and functions. For example, in the below screenshot, we can see that it gives a suggestion for schema, table, views, and functions after select * from query.

Image 12: Help in writing queries

Mssql-cli also gives output in a nice way as compared to existing tools like sqlcmd. We can easily read the information in a tabular format.

Image 13: Formatted output

In mssql-cli, we can also write a long query which might take few lines instead of one line. By default, multiline support is off as shown below

Image 14: Multiline mode

In order to use multiline support, we can press F3.  It turns on the multiline mode.

Image 15: Multiline mode ON

In multiline mode, also T-SQL intelligence works fine and gives suggestions as we type in.

Image 16: Writing queries in multiline

In multiline mode, in order to execute the query it should end with a semicolon (;). It makes it easy to navigate up and down for making changes to the query. For example, in the below screenshot, we have made a typo in the column name, so we get an error as an invalid column name.

Image 17: Correcting query in multiline mode-1

Now, we can just use the up arrow key to get to the last query.

Image 18: Correcting query in multiline mode-2

With the help of the arrow key, go to the first line and make the changes.

Image 19: Correcting query in multiline mode-3

Make the correction and execute the query.

Image 20: Running multiline query with semicolon

As stated above, multiline mode requires a semicolon to execute the query. If we do not type a semicolon, it will continue to use multiple lines to write queries.

Image 21: Running multiline query with semicolon

Mssq-cli is intelligent enough to help in writing join queries.

Image 22: Help in writing join queries

We can just select the required join columns using the arrow key and press Enter.

Image 23: Selecting column list for join queries

This writes the desired columns in the query and we can simply execute it.

Image 23: Running queries with join
Image 24: Execute join query

Mssql-cli is a nice interactive tool which makes it easy to write and execute queries in SQL Server using a command line.

Next Steps


Last Updated: 2018-11-27


next webcast button


next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools