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:
- Auto-completion
- Syntax highlighting
- Query history
- Configuration file support
- Multi-line queries
Installing MSSQL-cli
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.
mssql-cli --help
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.
Conclusion
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.
Is there syntax for sending a single line command to specify server, user, password, and query string?
Having need to install Python for this is such a bummer. Not always feasible or easy.
I have a few questions:
1. Will this utility come by default with next version of standalone SQL server installer?
2. Will it be possible to do schema compare and update (typically now done using sqlpackage.exe) with this tool?
@Nawfal, To answer you question,
1. I don’t think so as it has dependency on Python.
2. At this point of time, it’s not possible but not sure about future road map. It would be nice to have this feature..