sql-cli v0.3.0 Released

I just released the v0.3.0 of sql-cli on npm. It has following new features:

.analyze command: Lists all the missing indexes on a table. Works only on actively used databases.
.read command: Allows you to run a sql script file in the cli
-config argument: Allows you to specify a json config file to read connection information from

Feel free to share feedback or fork and send pull requests on github.

sql-cli v0.1.0 Released

I just released the v0.1.0 of sql-cli on npm. It has following new features:

  • .index command: Lists all the indexes on a table
  • .schema command: Now tells you whether column is indexed or not
  • New output format switch -f: Now you can export your query results as json, xml or csv

I also fixed a few formatting/output related issues and improved the .schema and .tables queries. Feel free to share feedback or fork and send pull requests.

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