You're probably aware of collaboration tools like Google Docs, Dropbox Paper, and Microsoft Office Online that let multiple people work on shared documents in real time. But what if you could collaborate like that on SQL queries?
Well now you can, with PopSQL, a collaborative desktop SQL editor for Mac OS X, Windows, and Linux.
PopSQL lets you:
- Work on SQL queries in realtime
- Organize a library of SQL queries
- Share SQL queries by URLs
- Visualize your SQL queries
- And more...
Because PopSQL supports PostgreSQL, it works with CrateDB. This is because CrateDB supports the PostgreSQL wire protocol.
In this post we’ll show you how to get set up with CrateDB and PopSQL.
Install CrateDB
PopSQL requires the pg_backend_pid() function to exist. You can create this yourself as a user defined function. But the easiest thing to do is install a CrateDB release greater than 2.2.4, which includes a PostgreSQL compatibility fix that adds this function for you.
At the time of publishing, this requires that you to install a CrateDB nightly release. However, you might also want to check the downloads page. If you see a version number higher than 2.2.4, you can go ahead and install that.
Once you have your tarball, you can follow the basic install method.
In short, unpack the tarball:
$ tar -xzf crate-*.tar.gzChange into the crate-* directory:
$ tar -xzf crate-*And run bin/crate:
$ ./bin/crateEasy.
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 in the admin UI:
You should see something 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 PopSQL Running
Head on over to the PopSQL website and select Download from the menu in the top right corner.
Once PopSQL has been downloaded, open it and follow the instructions.
You will be asked to sign up. We recommend that you sign up with Google if you can, because this will enable the sharing features with the rest of your organization.
Connect to CrateDB
Once you're signed in, you will be presented with a screen that allows you to create a new connection. You may also be presented with existing shared connections that exist for your organization.
Select Create a new connection, and you should see a screen that looks like this:
Fill out the information as follows:
- Nickname:
Tweets demo - Type: select PostgreSQL
- Port:
5432 - Database
tweets - Username:
crate - Password: leave blank
Tick the last two boxes according to your preferences.
Select Test. You should get a message saying Looks good!
Select Connect. This will bring you to a window titled My first private query:
Type the following query:
SELECT * FROM doc.tweets LIMIT 1000;You should notice auto-complete working for you here, with both SQL syntax such as statements and clauses as well as schema information like doc.tweets!
Select Run.
If, at this point, you get an error about pg_backend_pid(), please consult the note in the earlier install section of this post.
You should see a small results window appear on the right hand side. Select See more... to expand this to the full window.
And from here you can continue to explore the features of PopSQL. For instance, try selecting Share instead of Run on the query screen and start collaborating with a colleague.
Wrap Up
PopSQL is a great tool for writing, editing, running, visualising, sharing, and collaborating on SQL queries. And because CrateDB supports the PostgreSQL wire protocol, you can use PopSQL with CrateDB.
If you plan on using PopSQL with CrateDB, why don't you give the PopSQL developers a shout and tell them how you're using their product.