Introducing sql-cli: a Nodejs Command-line Interface for Sql Server

SQL Server used to be a database that was only used by Windows users in the past and so not much effort has been put in to making tools like SQL Server Managed Studio and sqlcmd available for linux and mac users. However with advent of cloud and hosted databases, it doesn’t matter much what OS the database is hosted on; as long as you can connect to the database from variety of platforms. This is true for SQL Azure which is now used by many mac and linux users directly or indirectly (via Azure Mobile Services). However due to lack of good tools to manage the database, users have been forced to use browser based tools or using Windows tools from inside a virtual machine. However this is now going to change.

Today I’m pleased to announce sql-cli: A cross platform command line interface to SQL Server written in nodejs. Without further ado let’s see how you can use it.

Installation

You can install sql-cli simply by running the following command.

npm install -g sql-cli

Alternatively you can clone the git repository

git clone https://github.com/hasankhan/sql-cli.git
cd sql-cli
npm install -g

Get Started

Once installed you get a new ‘mssql’ command in your environment. Running it without arguments will give show you the following:

  Usage: mssql [options]

  Options:

    -h, --help                     output usage information
    -V, --version                  output the version number
    -s, --server [server]          Server to conect to
    -u, --user [user]              User name to use for authentication
    -p, --pass [pass]              Password to use for authentication
    -o, --port [port]              Port to connect to
    -t, --timeout [timeout]        Connection timeout in ms
    -d, --database [database]      Database to connect to
    -v, --tdsVersion [tdsVersion]  Version of tds protocol to use
    -e, --encrypt                  Enable encryption

To connect to a sql server instance invoke mssql as follows

mssql -s localhost -u user -p pass -d dbname

For SQL Azure you have to enable encryption and add server name in username

mssql -s abcdef.database.windows.net -u username@abcdef -p thepassword -d mydatabase -e

Once connected you’re greeted with a prompt as follows:

Connecting to localhost...done

sql-cli version 0.0.6
Enter ".help" for usage hints.
mssql>

To get help you can use the ‘.help’ command like so:

command        description
-------------  ---------------------------
.help          Shows this message
.tables        Lists all the tables
.schema TABLE  Shows the schema of a table
.databases     Lists all the databases
.quit          Exit the cli

Following is a sample session:

mssql> use test
OK
mssql> .tables
TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME          TABLE_TYPE
-------------  ------------  ------------------  ----------
test           dbo           Customers           BASE TABLE
test           dbo           Orders              BASE TABLE
test           dbo           MyEntityDatas       BASE TABLE
test           dbo           __MigrationHistory  BASE TABLE

mssql> select * from Customers
CustomerId  Name
----------  --------
1           Henrik
2           Scott
3           Benjamin

Feel free to fork the git repo and contribute enhancements https://github.com/hasankhan/sql-cli

Leave a Reply

Your email address will not be published. Required fields are marked *