Getting Started with Mssql-cli Command-Line Query Tool

A recent announcement on the release of several SQL Server tools has raised expectations across various groups. Product requirements and business are almost always a trade-off, and striking the right balance in a product in terms of the toolset is a sign of a successful product. After testing the SQL Operations Studio, I feel that it’s a promising tool for many developers, administrators, and DevOps specialists. In my opinion, the mssql-cli tool adds another feature to SQL Server in order to make it a leading database product.

Microsoft announced mssql-cli, a SQL Server user-friendly, command line interactive tool hosted by the dbcli-org community on GitHub. It’s an interactive, cross-platform command line query tool. The public preview release of mssql-cli is available for testing. mssql-cli is based on Python and the command-line interface projects such as pgcli and mycli. Microsoft released this tool under the OSF (Open Source Foundation) BSD 3 license. We can find its source code on GitHub. Now, the tool and the code are available for public preview. The tool is officially supported on Windows, Linux, and MacOS, and is compatible with Python versions 2.7, 3.4, and higher.

Mssql-cli improves the interactive CLI experience for T-SQL and includes support for SQL Server, MySQL, and PostgreSQL. The SQL Tools Service is the micro-service that forms the backend and based on .NET SDK Framework.

Mssql-cli is a little ahead of sqlcmd in terms of functionality and enhancements. Some of these features are introduced below:

Features

1. T-SQL IntelliSense
   • This provides an array of keyword references that can be easily accessible during the development process.
   • The parameter or metadata population is contextual.
   • It also provides a quick reference.
2. Syntax highlighting
   • This feature helps in identifying the troubleshooting of typo or syntax errors.
   • It helps to complete keywords and snippets automatically, thus, improving efficiency.
   • Autosuggestion and auto-completion of tasks where needed are available.
3. Query history
   • It provides a way to store the history.
   • This tool is no different than many other editors—use the up/down arrow keys to select the desired SQL statement.
   • The advantage of this tool is to automatically suggest commands from the history.

4. Configuration
   • A configuration file is used to configure the initial settings and parameters.
   • By default, the configuration file is stored to the following path:
     1. 1. Important file location paths on Linux
           1. Configuration file – ~/.config/mssqlcli/config
           2. Log file ~/.config/mssqlcli/config/mssqlcli.log
           3. History file – ~/.config/mssqlcli/config/history
        2. Important file location paths on Windows
           1. Configuration file – %USERPROFILE%\AppData\Local\dbcli\mssqlcli\
           2. Log file – %USERPROFILE%\AppData\Local\dbcli\mssqlcli\mssqlcli.log
           3. History file – %USERPROFILE%\AppData\Local\dbcli\mssqlcli\history\
5. Multi-line queries

Linebreaking within a query is very similar to what we have in SQL. To provide another example, in many languages, even shell languages, we can break up a single long command or a chain of commands into multiple lines. In PowerShell, we break the line with a backtick (`). In BAT/CMD, we use ^ to indicate that we have not yet done to issue commands to the shell.

6. Mssql-cli modes

mssql-cli works in two very popular modes right out of the box: VI and EMACS. Those who are familiar with UNIX would instantly recognize these tools—text editors, to be precise. Most VI (and VIM—or VI Improved) users talk about “thinking in VI(M)”, which essentially means that the editor is so friendly and minimal that typing on it is almost the second nature to most VI users. If you’re one of them, you should feel right at home using this tool in the VI mode.

If you’re one of the EMACS users, you can enable or even customize this mode on mssql-cli. EMACS has a library of the LISP code that enhances EMACS and extends its capabilities.

Prerequisite:

1. 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.
2. Python Pip Package – It’s the setup tools to install and manage Python APIs, and is required to install packages to extend mssql-cli.

How to configure mssql-cli on Linux Distribution CentOS

To configure mssql-cli on Linux distribution, CentOS/RHEL, follow the steps below:

1. Install the Python-pip package using the YUM utility #yum install python-pip python-wheel
2. Run the #yum -y install epel-release command
   
3. The CentOS distribution requires the dependent libraries, linwunwind and libicu, installed. Let’s do this with the following commands:#yum -y install libunwind libicu
4. Install the mssql-cli package as the root or superuser with the #pip install mssql–cli command. 
   You can get the necessary help using the following command: #mssql-cli –helpIf you run the aforementioned command, you’ll see something interesting: –auto-vertical output. If the result set overflows from the command window (because the result set is wider than the terminal) the result set is displayed in a vertical format.
5. Test the connection with the mssql-cli command

The black terminal looks classic. Let’s change the terminal preferences to the black theme for the feel-good factor.

After starting the mssql-cli session, the auto-completion events are triggered and they show the little snippets according to the context thereby speeding up the user querying experience.

Switching to the editor mode is pretty simple and straight-forward. At the bottom of the screen, we can see the help bar which guides us through the switching process between the available editor modes. The options available for instant switching are the multiline mode, activated by pressing F3, and the Emacs mode, activated by pressing the F4 button.

To run the multi-line query in the multi-line mode, append the query with a semicolon and then press the enter key to execute it.

Use the same keys as mentioned above to turn on and turn off the editor modes—F3 for the multi-line query mode and F4 for the EMACS mode.

View a demo to switch between the editor modes.

In the following example, we can see that the multi-line mode is enabled and create table demo is added as the multi-line statement followed by a ‘;’ to execute the query. The semicolon indicates the terminal that you have finished to create the query, and that the query can be executed.

As you can see, the multi-line mode is disabled, which limits adding the queries to the multiline and errors due to the “partial entry” of the query.

We know that the query history is the default; it’s self-explanatory in most of the tools. We can browse the recently-executed queries using the up and down arrow keys.

Now, to demonstrate the power of the query auto-completion feature based on what we’ve already done on the terminal, look at how it automatically fills in the rest of the third line—I have just typed “nam”. This is almost like IntelliSense, but a lot lighter. It enhances user experience to great levels!

Let’s enable both multi-line and EMACS modes by pressing F3 and F4. We can see that there are two options available in the EMACS mode: first, the default EMACS mode, and second, the VI editor mode.

To enable VI editor, press F4 again. This is very useful for the administrators and developers who are comfortable to work extensively on the VI editor.

This is where VI comes in handy. Let us place the cursor on a line and delete the entire line.  Since the session is in the VI mode, you can press ESC (to enter the VI “normal” mode), and then dd to remove the entire line. Just like that. Two strokes!

To move on, let’s figure out how the integration works with SQL Operations Studio.

In SQL Operations Studio, open the terminal and type mssql-cli. You’ll notice how it’s no different than connecting to it from a standard terminal.

#mssql-cli –S <ServerName> -U SA

Press Enter. This step will take you to enter the password.

There you go; we’re all set!

Wrapping Up

Configuring mssql-cli on Linux is pretty simple and straightforward. It takes hardly five minutes to configure this simple tool. The article gives an overview of the feature and the steps to configure it on a Linux distribution.  The tool integration works well with SQL Operations Studio.  It’s a simple-to-use tool for effectively and efficiently querying an SQL instance.

References

https://docs.microsoft.com/en-us/sql/relational-databases/scripting/intellisense-sql-server-management-studio

http://tldp.org/HOWTO/Emacs-Beginner-HOWTO-3.html

https://github.com/dbcli/mssql-cli/blob/master/doc/installation_guide.md