An introduction to mssql-cli, a command-line client for SQL Server

Microsoft released SQL Server 2017 for Linux early this year. Now it is releasing mssql-cli, a cross-platform command-line client for SQL Server with modern features such as auto-completion and syntax highlighting.

Mssql-cli is written in Python and based on the popular command-line interface projects pgcli and mycli. Microsoft released it under the open source BSD 3-clause license, and its source code can be found on GitHub. The tool is officially supported on Windows, Linux, and MacOS, and it is compatible with Python versions 2.7, 3.4, and above.

Getting started

Mssql-cli can be installed using the binaries supplied by Microsoft or via pip.

$ pip install mssql-cli

Once it is installed, it can be launched from the command line using mssql-cli --help.

Features

Auto-completion

The tool will start to suggest completions as soon as the user starts to type an SQL command. The suggestions shown in the completion menu are context-aware. Only table names from the current database are suggested after the FROM keyword, and column names suggested after the WHERE keyword are scoped to the current table.

Image by:

opensource.com

This feature not only saves keystrokes, but it acts as a powerful way to explore a new database.

Syntax highlighting

The SQL statements entered in the REPL are automatically syntax highlighted. This is more than just eye candy; it can surface trivial errors such as unfinished quotes or unbalanced brackets.

Image by:

opensource.com

Auto-escaping

Sometimes a table name needs to be escaped because it has white spaces or it is named after an SQL keyword. This is automatically done by mssql-cli. The suggestions in the auto-completion menu are automatically escaped when needed.

Image by:

opensource.com

Configuration

The config file is located in Unix-like systems under ~/.config/mssqlcli/config. In Windows machines it is located under C:\Users\<Username>\AppData\Local\dbcli\mssqlcli\.

The config file is thoroughly documented with examples. The tool ships with reasonable defaults, so tweaking the config file is only necessary if you're not satisfied with the defaults.

History

Any self-respecting REPL must have a decent way to store the history, and mssql-cli is no different. In addition to the basic history support using the up/down arrows, commands are auto-suggested from history as you type. This is an idea borrowed from the fish shell.

Conclusion

Microsoft has released a solid command-line client for SQL Server that is modern, user-friendly, and released under an open source license, demonstrating Microsoft's support of the open source community.