Squirrel My Go To Database Controller

Posted on By Luis Fernandez

“The best database tool is the one you actually open every day. For me that is Squirrel.”

Why Squirrel stuck with me

I keep a lot of tools around for data work. MySQL Workbench looks nice, psql is always there, and on the Mac side Sequel Pro is friendly. Still, the one window I keep coming back to is SQuirreL SQL Client also called Squirrel. It is Java, it runs on anything, and it connects to pretty much every database that speaks JDBC. When I say database controller, I mean the app I use to poke, fix, and explore my data every single day.

Context matters. We just got the iPhone 4S with Siri chatting back, Lion is on my Mac, and the web is buzzing with Node and MongoDB. Meanwhile PostgreSQL 9.1 landed with synchronous replication that looks pretty sweet. In the middle of all this new stuff, I still need to ship. That means quick reads, fast schema checks, safe edits, repeatable scripts, and painless jumps between MySQL, Postgres, SQLite, and the odd Oracle box. Squirrel has been steady for that.

A week in my queries

This is what an average week looks like with Squirrel driving. Monday is schema archaeology on a legacy MySQL dump from a client. I point Squirrel at the JDBC driver, scan table nodes, and drag columns into a query tab to build a quick report. Tuesday is a Postgres day. I tune a slow endpoint by running EXPLAIN ANALYZE right in the SQL tab, then tweak an index and commit. Wednesday is export day. I grab a result set and save it to CSV for the marketing team without touching a script. Thursday I build a view, test permissions with a different user right from the same app, and stash the working query into my bookmarks. Friday I clean up, compare two schemas with the graph plugin, and push a hot fix using a transaction so a mistake can be rolled back in one click.

None of this is flashy. It is just fast. Tabs for sessions, a tight SQL editor with syntax color, results in a grid, metadata a click away, and a plugin model that covers the gaps. Because it is Java I can run the same setup on my Mac at home and my Linux box at the office. I carry my driver jars and settings in Dropbox and I am good to go in minutes. When a teammate pings me with a fire drill on a Windows desktop in a conference room, I can still help. That consistency matters.

Deep dive one: Getting SQuirreL SQL Client ready

First run is simple if you know the pieces. You need Java, the right JDBC driver jar, and a session profile. Here is the clean path I keep around for teammates who are new to Squirrel.

  • Install Java. Any recent Java 6 is fine. On Mac with Lion that is already handled. On Linux use your package manager. On Windows grab the JRE from Oracle.
  • Download SQuirreL SQL Client. Grab the installer from the official site. Run it and open the app.
  • Add JDBC drivers. In the Drivers panel click the plus button and point to the jar. The driver URL, class and sample URL help you get connected fast.

Here are the common ones I keep in my toolbox.

# MySQL
Driver Class: com.mysql.jdbc.Driver
Sample URL: jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8

# PostgreSQL
Driver Class: org.postgresql.Driver
Sample URL: jdbc:postgresql://localhost:5432/mydb

# SQLite
Driver Class: org.sqlite.JDBC
Sample URL: jdbc:sqlite:/path/to/my.db

Then create an Alias for each database. Give it a name like MyCompany Prod MySQL or Analytics Dev PG, select the driver, set the URL, and store the user if you want. I do not store the password on shared laptops. Two checkboxes I use all the time are Auto logon for my throwaway local boxes and Auto commit off for anything that is scary.

If you connect to a remote database through a jump box or a bastion, an SSH tunnel pairs well with Squirrel. You can forward a local port and then point Squirrel to localhost. This is my go to pattern.

ssh -L 5433:db.internal.example.com:5432 user@bastion.example.com
# In SQuirreL use jdbc:postgresql://localhost:5433/mydb

Same trick for MySQL works great too.

ssh -L 3307:db.internal.example.com:3306 user@bastion.example.com
# In SQuirreL use jdbc:mysql://localhost:3307/mydb

Once your aliases are set, save a session template with the font and color you like. I make production sessions use a bold red label in the status area so I do not fat finger a table drop in the wrong window.

Deep dive two: Daily power moves inside Squirrel

The editor is where I live. A few features matter more than they first look.

  • Code completion. Turn on the code completion plugin and tap control space to expand table names and columns. It saves typos and keeps me out of the mouse.
  • Parameter prompts. Use question marks in templates, then fill the values when you run. Good for reusable scripts you do not want to edit each time.
  • Aliases and bookmarks. Save common queries as bookmarks, snap short names for long schemas, and keep them in folders. My saved items include health checks, slow query hunts, and quick counts per table.
  • Transaction control. Toggle auto commit off and run a group of statements, then press commit when the result looks good. If not, hit rollback and breathe.
  • Explain plans. Right click in the results and open the execution plan. On Postgres I run EXPLAIN ANALYZE and study the nodes. On MySQL I use EXPLAIN to compare index choices. This is where speed comes from.
  • Export and import. Select rows and save as CSV or XML. Import from a file into a table with the table import plugin. I use this to hand a subset to a teammate or to seed dev data.
  • Multiple sessions. Open tabs to the same server as different users. One as app reader, one as admin. This helps me test grants without logging in and out.

I also like the split between the Objects tree and the SQL area. I can drag a table into the editor to get a full qualified name with schema, which keeps names precise when I am on servers with many schemas. The contents tab for a table is handy to peek at data with a quick filter without running a select by hand.

-- sample troubleshooting snippet I keep bookmarked
BEGIN;

-- find heavy queries in last hour on Postgres
SELECT query, calls, total_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- quick check on missing indexes
SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

ROLLBACK;  -- safe default in prod sessions

Those two selects and a safe rollback have saved me from mistakes more times than I can count. For MySQL I keep a similar bookmark that hits INFORMATION_SCHEMA and the slow log when it is enabled.

Deep dive three: Plugins and scripting that save you time

Squirrel grows with your needs. The core is solid and the plugin list covers real work. These are the ones that stick for me.

  • Graph plugin. Generates a quick diagram from your schema. I use it to explain table flow to teammates and to spot missing foreign keys. Not a full blown modeling tool, but perfect for a fast map.
  • SQL templates. Shortcuts that expand into common statements. I set up select star from with a cursor at the table name, create index with a cursor at the column list, and grant templates for new roles.
  • Search in SQL. Lets me find every place a column shows up in a folder of scripts. Great for migrations and cleanup tasks.
  • Data script plugin. Builds insert scripts from a result set. I use this to move a tiny slice of data between environments without opening a full dump.
  • BeanShell scripting. If you know a bit of Java you can automate tasks inside Squirrel. I keep a script that tags my session title with the current git branch and a small helper that runs a suite of health checks on login.

Here is a tiny BeanShell sample that toggles auto commit on sessions where the alias name contains the word prod. It is simple but it keeps my hands safe.

import net.sourceforge.squirrel_sql.client.session.ISession;

ISession s = _session;
String alias = s.getAlias().getName();
if (alias.toLowerCase().indexOf("prod") >= 0) {
    s.getApplication().getSquirrelPreferences().setAutoCommit(false);
    print("Auto commit disabled for " + alias);
}

If scripting is not your thing, you still get plenty of mileage from ready made plugins. The maintainers and the community keep shipping small things that make daily work smoother. I like tools that let me bend the workflow without fighting me. Squirrel hits that sweet spot.

Looking forward while typing SQL

The scene is moving fast. New document stores grab the headlines, Redis keeps winning for cache and queues, and client side frameworks change every month. I enjoy that buzz, but at the end of the day most apps still have a core that lives in relational databases. Tables, keys, joins, transactions. That core needs a steady hand. For me, SQuirreL SQL Client has been that steady hand. Cross platform, no drama, and friendly to every JDBC driver I throw at it.

If you spend your time between MySQL and PostgreSQL, or you bounce into Oracle here and there, give Squirrel a try as your daily database controller. Set up your drivers, build a handful of bookmarks, add the code completion plugin, and make a production session template that screams do not drop anything. Then just use it. A week later it will feel like muscle memory.

We lost Steve Jobs this month. A lot of us have been reflecting on craft and taste. Squirrel does not look flashy and it will not win a design award. What it nails is the part that matters to me. It respects my time. It stays out of the way. It lets me ship. That is why Squirrel is my go to database controller.

Keywords: SQuirreL SQL Client, Squirrel SQL, database controller, JDBC, MySQL, PostgreSQL, SQLite, Oracle, SQL tool, open source SQL client, cross platform database tool

Content Management Systems Marketing Technologies Software Architecture Software Engineering