Installing and Using mssql-cli on Linux for SQL Server

By: Rajendra Gupta   |   Updated: 2018-11-27   |   Comments (1)   |   Related: > 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

Method:2

• Open "System Settings" from the desktop main menu in Unity.
• Click on the "Details" icon under "System".

• See version information

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 -

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.

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.

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.

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.

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

Some of the important parameters for mssql-cli are:

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>.

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.

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.

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.

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

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

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

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.

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

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

Make the correction and execute the query.

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.

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

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

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

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

• Explore more features of mssql-cli in tip new interactive command line tool mssql-cli for SQL Server and upgrading mssql-cli and new commands.
• Explore the mssql-cli-documentation.
• Read more SQL Server tools tips.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips