SQuirreL SQL Client

Franco Martinig, Martinig & Associates, http://www.martinig.ch/

SQuirreL SQL Client is an open source graphical Java program that will allow you to view the structure of a JDBC compliant database, browse the data in tables, issue SQL commands etc, The minimum version of Java supported is 1.6.x as of SQuirreL version 3.0. Older versions of SQuirreL will work with older versions of Java. SQuirreL's functionality can be extended through the use of plugins.

Web Site: http://www.squirrelsql.org/ and http://sourceforge.net/projects/squirrel-sql/

Version Tested: SQuirreL SQL Client Snapshot-20090912_2118 to 20091128144, tested on Windows XP with MySQL 5.0.27 from September to December 2009

License & Pricing: Open Source

Support: User mailing list

Installation

The SQuirreL SQL Client is installed with the IzPack Java installer. Download the file squirrel-sql-<version>-install.jar and execute it using the following command:

java -jar squirrel-sql-<version>-install.jar

The installation runs smoothly and you can choose which database extensions and plugins you want to install. SQuirreL's default language is English. If you would like to use SQuirreL in one of the translations available (Chinese, French, German, ...) you need to check the desired optional translation package during the install process. Translations are available for 9 languages. At the end of the installation, the installer ask if you want to create a shortcut on your desktop and an installer script that will allow you to replicate the installation on other machines, an useful option if many persons in the same organizations plan to use the tool. An update manager allows you to check for new version and manage installation or removal of plugins. By default, the update is setup to run weekly in your Global Preferences. Updates are downloaded from the project web site and installed when the application is restarted by a single click.

Documentation

There is an extensive documentation on the web site. A detailed PDF paper is available in English or German but refers to version two. An on-line help system is associated to the program, but some pages seem to be outdated. SQuirreL SQL Client is certainly powerful, it is better to read the documentation before trying to use it intuitively.

Configuration

The different options that you can configure in the tool are managed using the "Global Preferences" window. Before using the tool, you should configure the driver you are going to use and the databases you are working with. Only Sun JDBC ODBC Bridge is available by default. The MySQL JDBC driver is located on http://dev.mysql.com/downloads/connector/j/5.1.html. You have then to create an "alias" for actual database you are going to work with. This allows you to connect to different databases using their own parameters (name, user, and password). This is done easily create a connection with the parameters "jdbc:mysql://localhost/testsquirrel" on the test database that I have created.

The alias creation window allow you to "test" your parameters to make sure that they are right. After this, you can click on the "connect" icon. This creates a session. Multiple sessions on different databases can be opened at the same time which allows to compare data or pass SQL statements between databases, something that could be for instance useful when you have a migration effort.

Database structure editing

After login, you can display a drop-down list of objects in your database in the "OBJECT" tab. Once you get on a particular table, you access to its properties and content.

Figure 1. Object navigation features

If you right click on a table, you can access to operations on the table that can depend on the database that you are targeting or the plugin that you have installed. You can for instance create automatically scripts (SQL statements) to create the same table with the same indexes or to insert data in the table. For MySQL, you can also analyze or optimize the table. These operations create the SQL instruction in the "SQL" tab. Clicking on the content allows also opening a simple form to insert a new row without using SQL.

The SQL tab offer a syntax sensitive editor with a lot of associated tools, like an autocompletion tool that could be called by pressing ctrl+space. An interesting autoreplace feature can help you type quicker (or better), allowing abbreviations like SF for "Select * From" or correcting classical typos like "form" instead of "from". All these tools take the form of a plugin that you can decide to include (or not) in settings. You can also parameterize your SQL code, entering the value of a variable during execution of the script.

All SQL statements are kept in history and could be recalled. You can also save and load statements in files.

Figure 2. Autocompletion example.

SQuirreL has also the notion of "bookmarks" that are templates for the most common SQL and DDL instructions, like CREATE TABLE for instance. Besides the predefined bookmarks already existing in the software, the user can add its own templates.

An interesting Graph plugin allows creating charts of tables and foreign-key relationships. You can select in the object view the tables to be included in the graph. Other plugins will assist you in other task like refactoring the database structure or validating your SQL on a web site.

Conclusion

I have found SQuirreL SQL Client to be a relatively sophisticated database management tool that allows working in the same time with different databases product. Its capabilities ask for more time to be confident with it than a simpler tool like phpMyAdmin, but if your software development activity involves a lot of database and SQL work, this is certainly an open source tool that you have to consider.

---

More Database Resources

Database Locking: What it is, Why it Matters and What to do About it

Database Videos and Tutorials

MySQL Tools

SQL Server Tools

---

Click here to view the complete list of tools reviews

This article was originally published in the Winter2009 issue of Methods & Tools