Microsoft recently released a new tool to interact with SQL Server called MSSQL-cli. It’s an interactive command line interface to query SQL server database. Since the release of .NET Core, Microsoft is continuously releasing different cross-platform tools to reach out more developers. At the time of writing this post, the release is in public preview stage. This post talks about installing this tool and then show how to use this tool to query your SQL Server database.
What is MSSQL-cli
MSSQL-cli is a command line interface to interact with SQL Server. This open source tool works cross-platform and proud to be a part of the dbcli.org community. It features:
- Syntax highlighting
- Query history
- Configuration file support
- Multi-line queries
Installing MSSQL-cli is pretty simple. You can install this from pip. For those who don’t know about pip, it is a package management system used to install and manage software packages written in Python. Python is not installed by default on Windows. The latest Python installation package can be downloaded from here. When installing, select the ‘Add Python to PATH’ option. Python must be in the PATH environment variable.
For installing pip on Mac and Linux, please refer to this guide.
Once the Python is installed, open command prompt (as Admin) and execute the following command to install mssql-cli.
pip install mssql-cli
As you hit enter, the downloading process starts. You should see something similar.
It downloads multiple packages to install mssql-cli. You can verify the installation via executing the following command.
It should show the various options available with this cli tool. Let’s now connect to the SQL Server. To connect to SQL Server using integrated authentication,
mssql-cli -S <server Name> -d <database Name> -E
To connect using SQL Server authentication,
mssql-cli -S <server Name> -d <database Name> -U <user name>
Once you hit enter, it asks for the password. Type the correct password and hit enter to connect to the database. Like,
The database name is optional, if you don’t specify the database name it would connect you to the master database. You can use
USE [database Name] command to change the database. Like,
You can perform all kinds of DML and DDL operation on this tool. Here is a gif version which shows how to mssql-cli, execute different queries and show the features of this tool. [Click on the image to view full-screen.]
There are a few environment variables to play with. These are global variables and once set their value will be valid only for that session (or for that command prompt window only). As soon as the command prompt is closed, their previously set values have no significance on the new session. [It looks a bug to me.]
- MSSQL_CLI_SERVER – To set default server
- MSSQL_CLI_DATABASE – To set default database
- MSSQL_CLI_USER – To set default user
- MSSQL_CLI_PASSWORD – To set default password
- MSSQL_CLI_ROW_LIMIT – To set default row limit. This is not working, and I already raised an issue on GitHub repo.
Update: This issue is fixed now and the fix is available in 0.9.0 version. You can upgrade the mssql-cli via the following command,
pip install --upgrade mssql-cli
Now, when you set the row limit to some number and if the query result set has more records than the row limit, it will prompt you asking “The result set has more than [row limit] rows. Do you want to continue? [y/N]:“. Entering “N” will abort the query and “y” will return the result set.
Microsoft is releasing a number of cross-platform tools to reach out developers working on Mac or Linux. This tool is capable of executing all kinds of queries against your SQL Server database. It is lightweight, fast and the initial experience is good. The tool is still in public preview release and I am sure it will become more powerful and will offer more as and when it becomes mature.
Thank you for reading. Keep visiting this blog and share this in your network. Please put your thoughts and feedback in the comments section.PS: If you found this content valuable and want to return the favour, then