Microsoft recently released a new and interactive command-line query tool for SQL Server with modern features such as auto-completion and syntax highlighting. It’s called mssql-cli and this fully open source tool works on cross-platform under the BSD-3 license.
Mssql-cli was created and released with the goal to provide the following key enhancements over sqlcmd in the Terminal environment:
- Auto-completion
- Syntax highlighting
- Query history
- Configuration file support
- Multi-line queries
So, let’s get started and check out this tool.
Installation
In order to install and run mssql-cli, you must have Python installed on your machine and it works with both Python 2.7 and 3.6 versions. For supported operating system specific installations including Python, see this full detailed installation guides. Since I’m a Windows user, let’s go through the steps of installation and set up mssql-cli on the Windows environment to see it in action.
Python is not installed by default on Windows, so we need to obtain the latest installation package from here. Once the installation file is downloaded, double-click it to begin the installation process.
In the first step, check the ‘Add Python 3.6 to PATH’ option because Python must be in the PATH environment variable and select the ‘Install Now’ option to install Python with default settings:
Depending on your User Account Control (UAC) settings you might be prompted to allow application to make changes to the computer. Just click the Yes button to continue with the installation:
Note: When a standard user attempts to perform this task, the credential prompt will be presented requiring administrative privileges
Once the installation is completed, open a Command Prompt, and install mssql-cli using the below command:
C:\> pip install mssql-cli
Mssql-cli is installed via pip. It will start fetching the required packages, so an active internet connection is a requirement in this step:
Note: If Python was installed into the “Program Files” directory, you may need to open the command prompt as an administrator for the above command to succeed
Once we have mssql-cli installed, connect to your database with the following command:
$ mssql-cli -S <server name> -U <user name> -d <database name>
Next, you’ll be prompted to enter the username password. There is no indication that a password is being entered and this is entirely intentional for entering passwords at the command line. Hit the return key when you’re ready to proceed:
Note: Connecting using Windows authentication is also possible using the “-E” option as it is for sqlcmd
If you encounter an error message that the connection failed like the one shown below in the screenshot, make sure the user account that you’re using is a member of the sysadmin fixed server role:
This can be easily done from Object Explorer in SQL Server Management Studio (SSMS). On the toolbar of Object Explorer, click the Connect icon and then in the Connect to Server dialog box connect to your instance of SQL Server. Expand the Security folder, do the same for Logins folder, right-click <user name>, and then click the Properties command:
In the newly open Login Properties dialog select the Server Roles page in the top left, ensure that the sysadmin role is checked under the Server roles list, and click the OK button at the bottom right to save changes:
Go back to command prompt, hit the up-arrow key to bring the last executed command for connecting to your database. Once again, you’ll be prompted for the password, and this time the login should be successful:
Features
Let’s check out the features that we mentioned at the very beginning of this article.
As soon as the user starts to type an SQL command, the auto-completion will appear that is context aware and it will narrow down suggestions as you type:
Writing multi-line queries is easy to read with the included appropriate syntax highlighting and “smart” auto-completion:
Query history is self-explanatory; you can reuse recently executed queries by browsing thru them using the up and down arrow keys. Furthermore, the hints are auto-suggested from history as you type.
If you’re a Windows user like me, you can find the configuration file under the following path: C:\Users\<user name>\AppData\Local\dbcli\mssqlcli
If the default setting doesn’t work for you, you can edit the configuration file to your liking. Also, the config file is well written with examples for each option, so changing setting should be fairly easy. My advice, if you’re playing with the setting, make a backup of this file to e.g. Desktop, so you can restore it at any time:
Note: To access AppData folder you’ll have to enable “Show hidden files, folders, and drives” option in Windows
Multi-line edit mode can be controlled by pressing the F3 key. You can see at bottom of the screen if this option is on or off. To execute your multi-line query, add a semi-colon at the end of the last line of your query, and then press the Enter key:
In my Windows 7 VM instance, I was experiencing the “Not enough memory” error after executing most of the queries:
This bug is already logged as issue #98 under the mssql-cli GitHub project. If you encounter any bugs with the tool, you can report them in the Issues section of GitHub repo. You can use the same thread to provide feedback as well. Microsoft is open to any questions, feedback, or any feature suggestions for future releases.
To quickly get the hang of this tool, check out this user guide for details on options and example usage.
Conclusion
After SQL Server 2017 was released on Linux and macOS (Docker), the dbcli community raised the need to modernize sqlcmd. Not that we didn’t have a popular set of open source cross-platform CLI tools for databases, but this relatively new tool which supports “smart” auto-completion, syntax highlighting and easy installation looks very promising. The community grows each day and these guys are really passionate about creating better database CLI tools. The roadmap shows some very useful features and areas of focus in the future mssql-cli work. Overall, this is a great tool that supports all versions of SQL Server and has support for cross-platform which can be useful for users who can’t or don’t wish to deploy SSMS on client computers, etc. or they’re just into command line database clients.
He has written extensively on both the SQL Shack and the ApexSQL Solution Center, on topics ranging from client technologies like 4K resolution and theming, error handling to index strategies, and performance monitoring.
Bojan works at ApexSQL in Nis, Serbia as an integral part of the team focusing on designing, developing, and testing the next generation of database tools including MySQL and SQL Server, and both stand-alone tools and integrations into Visual Studio, SSMS, and VSCode.
See more about Bojan at LinkedIn
View all posts by Bojan Petrovic
- Visual Studio Code for MySQL and MariaDB development - August 13, 2020
- SQL UPDATE syntax explained - July 10, 2020
- CREATE VIEW SQL: Working with indexed views in SQL Server - March 24, 2020