SQuirreL SQL Client: Overview and Tutorial
SQuirreL SQL is one of the most popular options when it comes to SQL clients. In this article, we'll take a deep dive into the SQuirreL SQL client: what it is, what its benefits are, and how you can get started using it.
SQL Databases and Reporting
Today's business landscape is dominated by "big data," and it's only getting bigger and bigger. The average company is now storing 163 terabytes (163,000 gigabytes) of information--that's enough room for more than 40,000 feature-length movies on DVD.
Much of this data is stored in relational databases, in which data follows a predefined model and is organized into tables with rows and columns. Data workers can access the information inside a relational database using the language SQL (Structured Query Language), which is used to request and edit information.
However, businesses have a number of options when it comes to SQL databases: Microsoft SQL Server, Oracle Database, MySQL, IBM DB2, and dozens of other alternatives. In order to deal with all this complexity, SQL clients attempt to provide a "one-stop shop" for users who need an easy way to interact with these different SQL database types, like a translator that speaks multiple languages.
SQL clients are software applications that let you view a relational database's model, browse the information inside the database, and create SQL queries. Just like you use a web browser on your computer to access websites stored on other machines, you use a SQL client to access data stored in a remote database. SQL clients attempt to put a more "human-friendly" face on the bits and bytes inside a relational database.
What is SQuirreL SQL?
SQuirreL SQL is a client for accessing SQL databases through a user-friendly GUI (graphical user interface). Because SQuirreL SQL is written in the Java programming language, it can run on any computer with a JVM (Java Virtual Machine).
In addition, the SQuirreL SQL client is capable of interacting with any relational database that is compliant with the JDBC (Java Database Connectivity) interface. This includes the most popular relational databases, such as:
Microsoft SQL Server
Even better, SQuirreL SQL is completely free--"free" in terms of both its price and its source code. SQuirreL SQL uses the GNU General Public License v2.0, the most popular option for open-source software. The software is available in a dozen different languages, including English, Spanish, French, German, Russian, Portuguese, and Chinese.
SQuirreL SQL is useful for anyone who interacts with SQL databases on a regular basis: application administrators, software developers, software testers, database administrators, etc.
For example, an application administrator might use SQuirreL SQL to fix a bug with the software application at the database level. Using SQuirreL SQL, the administrator can much more easily scan for the incorrect value in a table, and then correct it by typing in the new value.
Meanwhile, database administrators may need to oversee a large variety of relational databases of various types on remote machines. SQuirreL SQL can help DBAs manage the databases under their command, viewing metadata and checking for problems with the tables.
The Benefits of SQuirreL SQL
Anyone who's worked extensively with SQL databases knows the pain of writing out long queries over and over again, or having to work with different types of databases on different machines. Often, each type of database comes with its own front-end GUI tool for interacting with the data, multiplying the complexity even further.
SQuirreL SQL solves this issue by providing a single coherent interface for users to interact with many different databases, located on both local and remote computers. There's no need to learn multiple database administration tools just to have access to the information you want--SQuirreL SQL is all you need.
What's more, if a certain type of SQL database has some non-standard "quirks" or behavior, you can install a Java plugin for SQuirreL SQL that make it fully capable of handling those operations. The plugins available for SQuirreL SQL are able to perform tasks such as:
Importing data from Excel and CSV files into a database (Data Import Plugin)
Copying and comparing tables between different sessions (DBCopy Plugin)
Storing "bookmarks" for commonly used snippets of SQL code (SQL Bookmarks Plugin)
Validating your SQL queries against the ISO SQL standard (SQL Validator Plugin)
Adding functionality for SQL database types such as IBM DB2, Firebird RDBMS, H2, and Apache Derby (DB2 Plugin, Firebird Plugin, Derby Plugin, etc.)
How to Use SQuirreL SQL
Installing SQuirreL SQL
To use SQuirreL SQL, you'll first need to install Java. The exact version of Java you need will depend on the version of SQuirreL SQL that you want to use. As of SQuirreL SQL version 3.0, you'll need at least Java 6; older or newer versions of the software will have different requirements.
SQuirreL SQL is available from the project website (http://www.squirrelsql.org/) as a JAR package file. The file name will take the form of "squirrel-sql-version-install.jar" or "squirrel-sql-version-MacOSX-install.jar" for Mac OS X systems, where version is the version of the SQuirreL SQL software.
Once the file is downloaded, install SQuirreL SQL using the setup assistant. You can select either a barebones "basic" installation, or a "standard" installation that comes with a few common useful plugins: code completion, highlighting syntax, changing the look and feel of the software, etc.
The next task is to launch the software for the first time and connect it to one of your relational databases. This requires two different steps:
Defining the driver: First, you need to point SQuirreL SQL to the JDBC JAR file that contains the driver for the correct type of database.
Creating an alias: Second, you need to use the driver to link to the specific database, on a specific machine, that you want to access.
When you open SQuirreL SQL, you should see a list of the different types of databases that the client supports. Each database should have either a blue checkmark next to it (indicating that the JDBC driver for that database is in your JVM classpath) or a red X (indicating that it is not).
If the SQL database type that you want to use (i.e. MySQL, PostgreSQL, IBM DB2, etc.) has a red X next to it, you'll need to get the appropriate driver from the database vendor. Download the driver to your local machine and use the Drivers window within SQuirreL SQL to point to that file.
Next, you'll need to create an alias. From the software's menu bar, select Aliases and then click on New Alias… You can also use the Ctrl+N keyboard shortcut.
You'll now be asked to input more information about the database, such as the URL and your username and password. Depending on the type of SQL database you're accessing, the URL may need to contain parts such as the machine name and the port number. Check with your database administrator if you're not sure what exactly needs to go in this space.
Assuming you've done everything correctly, that's it! You're now ready to start using SQuirreL SQL with your favorite JDBC-compliant databases.
Working with SQuirreL SQL
To start interacting with the database that you've connected to, double-click its name in SQuirreL SQL's Aliases window. This will open a new session. You can have multiple sessions open at the same time in SQuirreL SQL, each one connected to a different database.
Within the Session window, you have two options for viewing and interacting with the database: the Objects tab and the SQL tab.
The Objects tab is used for viewing the database's data and metadata. On the left-hand side of the window, you can navigate the database in tree form. Click on the name of a table within the database to display that table's contents and metadata, such as the types of columns and the number of rows it contains.
You can use the Objects tab to make changes to the database in-place. When viewing a table in editable mode, changing a value in the table will alter its value within the database itself. The Objects tab also allows for inserting and deleting rows and importing and exporting data.
The SQL tab is used for more sophisticated SQL queries and operations on the database, such as joins. Clicking on this tab allows you to enter any SQL text, which is then sent to the database and processed there. The result of this operation is returned as a table as well as the associated metadata.
SQuirreL SQL Plugins
As mentioned above, SQuirreL SQL supports a number of official and third-party Java plugins to enhance the user experience.
For example, with the right plugin, you can also create "bookmarks" for your most commonly used SQL code templates. To insert a template into SQuirreL SQL's editor, use the Ctrl+J keyboard shortcut. This will open a pop-up window from which you can select the correct template.
Another plugin worth mentioning here is the Graph plugin, which charts the relationships between the tables in your database, as well as their foreign key relations. This helps you visualize the most important connections when working with large databases with many different tables.
The Limitations of SQuirreL SQL
While SQuirreL SQL is a highly powerful and useful tool for anyone who interacts with SQL databases, the software nevertheless has some drawbacks:
- The process of manually configuring drivers and aliases can be difficult for first-time users.
- Some users find that SQuirreL SQL's interface is too "cluttered" and not immediately intuitive.
- As an open-source tool, SQuirreL SQL has only limited support options. Users should likely be at least moderately technically proficient in order to help themselves.
SQuirreL SQL and SQLBot
There's a lot to like about SQuirreL SQL: its compatibility with a wide range of relational databases, its simple and straightforward user interface, its low overhead and speedy performance, its open-source code base, its plugin-friendly architecture, etc.
Although there's a great deal of use you can extract from SQuirreL SQL, the most valuable part might just be how it interfaces with other SQL tools such as SQLBot.
When you use SQLBot in combination with SQuirreL SQL, you can easily generate reports from a variety of different SQL databases, and then post them to Slack or schedule email reports at the times of your choosing.
Looking for the best way to get scheduled SQL reports in your Slack feed? Sign up for a free account on SQLBot to see how it can benefit your organization.
Reviews of Other SQL Editors
Want to compare SQuirrel SQL with other SQL Editors? Check out our reviews!
Try SQLBot - It's Free!
Sign up for a free account on SQLBot to see how easy it is to get SQL reports into your Slack. What are you waiting for, it's free!
About the Author
David Tidmarsh is a freelance software and technology writer and graduate student in computer science who specializes in artificial intelligence and big data. He's passionate about translating complex tech issues into clear, comprehensible language.