In this post, we are going to discuss a new cross-platform and interactive command-line query tool that can be used to communicate with Microsoft SQL Server. Unlike sqlcmd command-line utility, mssql-cli supports cross-platform and can be used on Windows, macOS, Linux, Ubuntu, Debian, CentOS, Fedora, and etc. This is an open-source tool and it supports a very-rich and interactive command-line experience.
The mssql-cli is written in Python and it is released under open source BSD-3 license by Microsoft. It provides a very user-friendly terminal experience for T-SQL queries. It is a very small REPL utility and takes very less amount on the disk. It provides a highly interactive interface for writing T-SQL queries. Let’s discuss the features of the mssql-cli in detail.
Features of mssql-cli
Below we have listed the main features of mssql-cli:
1. Auto-completion
It offers an improved command-line experience for T-SQL by enabling support for IntelliSense in a terminal window. The IntelliSense helps us by bringing the relevant keywords and object names when we start typing. This brings a context-aware auto-completion suggestion list. That is it brings the object names from the current database only.
2. Syntax highlighting:
It supports syntax highlighting for SQL keywords which enables better readability. In addition to that, it also helps us to avoid some typos by highlighting some common errors like unbalanced brackets or unfinished quotation marks.
3. Formatted output results:
The query output is pretty much formatted and looks very nice. We can modify the config file in order to get results vertically, horizontally, or to be rendered automatically as per the screen size.
4. Auto-escaping
The object names which contain white spaces or SQL keywords in their names are escaped automatically by mssql-cli in the auto-completion list. We don’t need to escape these object names manually.
5. Configuration support:
The config file which is located at C:\Users\<Username>\AppData\Local\dbcli\mssqlcli\config path is created with default values during the installation. We can open this file and modify the values if needed.
6. Multiline query support:
The mssql-cli supports multiline querying. We can enable it from the terminal window itself. The tool will not send the command to the server until it finds the terminate keyword which is “GO” by default. You can modify the config file if you want to use Esc + Enter to execute a multiline command instead of typing “GO” and hitting the Enter key.
7. Query history:
This REPL tool stores all the queries being executed from the terminal window in a file located at C:\Users\<Username>\AppData\Local\dbcli\mssqlcli\history. It suggests the matching statements from history whenever it is relevant to suggest. The history is stored in a text file and is available even when we restart the mssql-cli utility. Below we can see the history file which gets created when we install the mssql-cli tool.
In the below screenshot we can see that how query suggestions are coming from history.
How to install mssql-cli
We need to install Python (latest version recommended) first. We can download it from this link and follow the on-screen instructions to install Python on our machine. Once you have installed Python, add Python entry to the Path variable path in case you are using a Windows machine.
Now, open the command prompt or terminal window and use below command:
pip install mssql-cli
Alternatively, we can use direct download and install the respective files from here.
This will install the mssql-cli on the current machine.
Once it is installed, we can start it using mssql-cli command in a terminal window with the required options. Let’s see how we can connect to a SQL Server instance and start executing T-SQL queries using mssql-cli tool.
Connect to a server using mssql-cli in an interactive mode
In order to connect to a server, we can use the below syntax in a terminal window.
Connect to a local server:
mssql-cli -S localhost -E
The above command will connect to a local SQL Server instance with a windows authentication mode. As we have not used any database name with -d option, it will connect to the master database by default.
Connect to a specific database:
mssql-cli -S localhost -E -d TestDB
The above command will connect to TestDB as we have used -d option to define the database.
Enabling multiline query mode in the terminal window
In order to enable multiline query mode, we can press the F3 key. It keeps showing this hint at the end of the terminal window.
Exiting from mssql-cli tool
In order to exit from the mssql-cli window, we can press Ctrl + D on Windows or Cmd + D on macOS.
Conclusion
We have analyzed a new open-source and interactive query tool that can be used cross-platform. It is highly interactive and supports IntelliSense in a terminal window. You can install the mssql-cli and explore it.
Thanks for the reading. Please share your inputs in the comment section.
