Because CrateDB supports the PostgreSQL wire protocol, many PostgreSQL clients will work with CrateDB. And that includes graphical clients like SQuirreL.
SQuirreL is a popular SQL database administration client written in Java, available under an open source license. It runs under Linux, macOS, and Microsoft Windows.
In this post I will show you how to get set up with CrateDB and SQuirreL as a desktop client for macOS, but these instructions should be trivially adaptable for Linux or Windows.
Install CrateDB
If you don't already have CrateDB running locally, it's very easy to get set up.
Run this command:
$ bash -c "$(curl -L try.crate.io)"This command just downloads CrateDB and runs it from the tarball. If you'd like to actually install CrateDB a bit more permanently, or you are using Windows, check out our collection of super easy one-step install guides.
If you're using the command above, it should pop open the CrateDB admin UI for you automatically once it has finished. Otherwise, head over to http://localhost:4200/ in your browser.
You should see something like this:
Get Some Data
If you’re playing around with a fresh CrateDB install, chances are you don't have any data. So head on over to the Help screen by selecting the question mark icon on the left hand navigation menu.
The help screen looks like this:
Select IMPORT TWEETS FOR TESTING and follow the instructions to authenticate your Twitter account.
Don't worry. This isn’t going to post anything on your behalf. It doesn’t even look at your tweets. All this does is import a bunch of recent public tweets on Twitter.
Once you're done, select the Tables icon from the left hand navigation, and then select the tweets table. You should end up here:
http://localhost:4200/#/tables/doc/tweets
Which should look like this:
Get SQuirreL Running
Head on over to the SQuirreL download page and download the correct file for your operating system.
I'm using macOS, so I downloaded the jar of SQuirreL 3.8.1 for MacOS X.
Once the download has completed, control-click the squirrel-sql-3.8.1-MACOSX-install.jar file and select Open. macOS will warn you that the application has not been signed by an identified developer. If you're happy to proceed, select Open again from this dialog. (The control-click was necessary to reveal this option.)
Once the installer is running, follow the onscreen instructions. Accept all of the defaults. And when you're done, select Done.
You should now have a SQuirreLSQL app icon in your Applications folder.
Unfortunately, there are two macOS specific issues with the startup shell script that SQuirreL uses and the application will not start up by default.
Fortunately, this is easy to fix.
Open the /Applications/SQuirreLSQL.app/Contents/MacOS/squirrel-sql.sh file in your editor.
Go to line 33, and you should see this:
# IZPACK_JAVA_HOME is filtered in by the IzPack installer when this script is installed
IZPACK_JAVA_HOME=/Library/Internet Plug-Ins/JavaAppletPlugin.plugin/Contents/Home
Unfortunately, there is a problem with this. The path has not be quoted and there is an unescaped space character.
The fix is easy. Add quotation marks around the path, like so:
# IZPACK_JAVA_HOME is filtered in by the IzPack installer when this script is installed
IZPACK_JAVA_HOME="/Library/Internet Plug-Ins/JavaAppletPlugin.plugin/Contents/Home"
Now, go to line 68. You should see:
SQUIRREL_SQL_HOME=`dirname "$0"`/Contents/Resources/Java
This does not work.
An quick fix is to replace this with a hardcoded path:
SQUIRREL_SQL_HOME="/Applications/SQuirreLSQL.app/Contents/Resources/Java"
Now, when you double click the application icon, it should launch. Note, however, that the application will only work as long as it remains in the Applications folder.
There appears to be a few open bugs for the SQuirreL launcher. So a more permanent fix is left as an exercise for the reader.
There's one last step we need to take before SQuirreL is ready for use.
Install the CrateDB Driver
SQuirreL does not come with a bundled CrateDB driver. But we can install one pretty easily.
Firstly, download the latest CrateDB JDBC standalone jar.
Install the jar file like so:
$ mv crate-jdbc-standalone-2.2.0.jar /Applications/SQuirreLSQL.app/Contents/Resources/Java/lib/
Now, double click the SQuirreLSQL app icon.
Here's what you should see:
Select the Drivers tab from the left-hand navigation menu.
Now, select the blue plus icon.
Configure the dialog that pops up like so:
- Name:
CrateDB - Example URL:
jdbc:crate://localhost:5432/ - Website URL:
https://crate.io/ - Class Name:
io.crate.client.jdbc.CrateDriver
Leave the Java Class Path multi select menu as it is.
When you're finished it should look like this:
Select OK.
If everything has worked, you should see a new CrateDB driver:
Connect to CrateDB
Now, select Aliases from the left-hand navigation menu.
Select the blue plus icon.
Configure the dialog that pops up like so:
- Name:
CrateDB localhost - Driver: select CrateDB
- URL:
jdbc:crate://localhost:5432/ - User Name:
crate
When you're finished it should look like this:
Select OK.
You will be presented with the connection dialog:
Select Connect.
Once you're connected, you should see a screen like this:
Congratulations! It took a bit of work, but now you're ready to interact with CrateDB from SQuirreL.
Query CrateDB
Select SQL from the menu in the middle near the top of the screen.
You should see something like this:
Type the following query into the yellow text area:
SELECT * FROM doc.tweets LIMIT 1000;
When you're done, press control-Enter or select the little icon of the running person.
You should see some results:
And from here you can continue to explore the features of SQuirreL.
It's important to note that SQuirreL only supports PostgreSQL native features, so CrateDB objects, for instance, will show up as UnknownType, as you may have already noticed from the screenshot above.
Wrap Up
If you're looking for a desktop client that works with CrateDB, SQuirreL does the basics. The setup is a bit involved, but if you're already familiar with it, the CrateDB JDBC driver plugs right in and lets you continue using your favourite tool.
Don't forget to check out the other clients that work with CrateDB.