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:
     • Important file location paths on Linux
       • Configuration file – ~/.config/mssqlcli/config
       • Log file ~/.config/mssqlcli/config/mssqlcli.log
       • History file – ~/.config/mssqlcli/config/history
     • Important file location paths on Windows
       • Configuration file – %USERPROFILE%\AppData\Local\dbcli\mssqlcli\
       • Log file – %USERPROFILE%\AppData\Local\dbcli\mssqlcli\mssqlcli.log
       • 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.

Further reading mssql-cli

Happy Learing!