Let's explore MSSQL-CLI an Interactive Cross-Platform Command-Line Tool

What is MSSQL-CLI Tool?

MSSQL-CLI or Microsoft SQL Server CLI is an open-source and cross-platform interactive command line query tool for SQL Server on-premise and on the cloud. If you are a user of SQLCMD, you will love the interactive and modern design components in MSSQL-CLI. With this release, you will also be able to use MSSQL-CLI in non-interactive scenarios such as scripting and automation. This command-line tool can be used as an enhanced alternative to SQLCMD. It is fully open-source under the BSD-3 license, and a contribution to the dbcli organization, an open-source suite of interactive CLI tools for relational databases including SQL Server, PostgresSQL, and MySQL. The command-line UI is written in Python and the tool leverages the same microservice backend (sqltoolsservice) that powers the VS Code SQL extension, SQL Operations Studio, and the other Python CLI tool we announced earlier, MSSQL-SCRIPTER.

One of the major features of MSSQL-CLI is that you get Intellisense as you are writing queries in the command line. This is the first time that our SQL Server command-line tools have included this experience, and it will make it much easier to write your ad-hoc queries and even discover your database objects.

What features provide by the MSSQL-CLI?

• Auto-completion: fewer keystrokes needed to complete complicated queries.
• Syntax highlighting: highlights T-SQL keywords.
• Query history: easily complete an auto-suggested query that was previously executed.
• Configuration file support: customize the MSSQL-CLI experience for your needs.
• Multi-line queries: execute multiple queries at once using the multi-line edit mode.
• Horizontal Paging: enable users to navigate with arrow keys to read through the results of a query
• Non-interactive support: execute a query without jumping into the interactive experience.

How to install MSSQL-CLI?

Pre-requisite

Before the installation of MSSQL-CLI, make sure you have completed the following pre-requisites.

• Python 2.7 and higher. The tool is officially supported on Windows, Linux, and MacOS, and is compatible with Python versions 2.7, 3.4, and higher.

The latest Python installation package can be downloaded from here

• Python Pip Package – It’s the setup tools to install and manage Python APIs, and is required to install packages to extend MSSQL-CLI.

Install MSSQL-CLI

python -m pip install mssql-cli

or

pip install mssql-cli

Now at this stage, MSSQL-CLI installation is complete. To verify and launch it, we need to run the below command.

mssql-cli --help

It shows the various options available with MSSQL-CLI. Some of the important options are:

• -S: SQL Server Instance Name
• -U: User Name to connect with SQL Server Instance
• -W: Force password prompt to enter user password
• -E: To use Windows Integrated authentication
• -d: If we want to connect to a specific database, we can specify here. The default will connect to the master database.
• -M: If the application is connecting to an AlwaysOn AG on different subnets, setting this provides a faster detection and connection to the active server.
• -A: To connect SQL Server using a Dedicated Administrator Connection.

Let's explore MSSQL-CLI by example

Connect to a Server:

mssql-cli -S localhost -d AdventureWorks -U sa

or

mssql-cli -S localhost -d AdventureWorks -E

Connect to a server, a specific database, and with a username. -S -d and -U are optional. You can set environment variables to set default settings.

Below are the environment variables that can be set.

• MSSQL_CLI_SERVER - Set Default Server

set MSSQL_CLI_SERVER=localhost
mssql-cli

• MSSQL_CLI_DATABASE - Set Default Database

set MSSQL_CLI_DATABASE=AdventureWorks
mssql-cli -S localhost -U sa

• MSSQL_CLI_USER - Set Default User

set MSSQL_CLI_USER=sa
mssql-cli -S localhost -d AdventureWorks

• MSSQL_CLI_PASSWORD - Set Default Password

set MSSQL_CLI_PASSWORD=abc123
mssql-cli -S localhost -d AdventureWorks -U sa

• MSSQL_CLI_ROW_LIMIT - Set Default Row Limit

set MSSQL_CLI_ROW_LIMIT=10
mssql-cli -S localhost -U sa

Exit MSSQL-CLI

• Press Ctrl+D or type quit

Navigate multiple pages of a query result

If you select a table that has many rows, it may display the results in multiple pages.

• Press Enter key to see one row at a time.
• Press Space to see one page at a time.
• Press q to escape from the result view.

Quit a query

• If you are in the middle of writing a query and would like to cancel it, press Ctrl+C

Clear Screen

• If you want to clear the Terminal view, press Ctrl+K
• If you want to clear in Command Prompt, press Ctrl+L

Toggle multi-line mode

To enable multi-line mode, press F3 key. You can see at the bottom of the screen if Multiline is on or off. To use multi-line mode, follow these instructions:

Type the beginning of your query.

SELECT *

Press Enter key and finish our query. You can keep adding lines pressing Enter key.

SELECT * 
FROM "SalesLT"."SalesOrderHeader" as SOH

To go back and make changes to your query, navigate with the arrow keys, including up or down.

SELECT "SalesOrderID","CustomerID","Status" 
FROM "SalesLT"."SalesOrderHeader" as SOH

To execute your multi-line query, add GO on a new-line, then press Enter key

SELECT "SalesOrderID","CustomerID","Status" 
FROM "SalesLT"."SalesOrderHeader" as SOH
GO

You can see a similar behavior as below

Horizontal Paging:

In the past, users did not have many options when displaying results that go off the screen due to the size of the result. With MSSQL-CLI, we support horizontal paging so that users can easily navigate with arrow keys to read through the results of a query.

Special Command:

Special commands are meant to make your life easier. They are shortcuts to a lot of commonly performed tasks and queries. Moreover you can save your own commonly used queries as shortcuts.

All special commands start with a backslash ('\') and doing so will bring up an autocomplete with all special commands and their descriptions.

For more help simply type '\?' inside the MSSQL-CLI prompt to list all the special commands, their usage and description.

mssql-cli>\?

will show you the below result

Example List Tables with special commands

Show all tables which contain sales in their names:

mssql-cli>\lt sales

with verbose output

mssql-cli>\lt+ sales

Non-interactive option

Non-interactive mode is a great way to query SQL Server using MSSQL-CLI without needing to jump into an interactive command-line interface.

MSSQL-CLI supports the following options for query execution in non-interactive mode:

Note: Ensure a connection to your server is established using the -S, -U, -P, and -d arguments, or by specifying your environment variables.

From SQLCMD, we now support the following parameters:

• -Q: Run a query in the command line
• -i: Execute a provided T-SQL input file
• -o: Save the result in an output file

For more information

• MSSQL-CLI GitHub
• MSSQL-CLI command-line query tool
• MSSQL-CLI installation guide

Enjoy and Happy Learning!