Getting Started with SQL Workbench/J on a Distributed SQL Database

Although most databases ship with a command line utility to administer the instance, these utilities often come with a steep learning curve. If using the command line and learning sometimes arcane syntax makes you uncomfortable, there are graphical tools that can help you speed up many daily DBA tasks. In fact, you can find a comprehensive list of tools on the PostgreSQL wiki here. In a previous blog we explored the DBeaver database management tool, and in this post we’ll show you how to get up and running with SQL Workbench/J and the popular PostgreSQL Northwind dataset.

What is SQL Workbench/J?

SQL Workbench/J is a free cross-platform SQL query and admin tool that can help developers run SQL queries either interactively or as a batch job. It is Java based and supports several databases over JDBC, so it is obviously a popular tool among Java developers.

SQL Workbench/J has several key features worth highlighting:

• Rich support for importing/exporting datasets
• Editing, inserting and deleting data directly in the query result view
• Running queries in console mode

Prerequisites

Before we get into SQL Workbench/J, you’ll need to set up YugabyteDB and install a sample database. For the purposes of this blog post we’ll be using the Northwind sample database. The instructions for how to get up and running in just a few minutes can be found in our previous blog post, “The Northwind PostgreSQL Sample Database Running on a Distributed SQL Database.”

In this how-to we’ll be focused on getting up and running on a Mac, although SQL Workbench/J supports all the popular operating environments.

Installing SQL Workbench/J

Download and Install SQL Workbench/J

Go to the SQL Workbench/J site and download the latest stable version for your operating system. For the purposes of this blog we downloaded the file labeled “Generic package for all systems without support for importing or exporting Excel or OpenOffice spreadsheets.”

To get started unzip the archive, and execute the sqlworkbench.jar file. The first thing you should see is the “Select Connection Profile” window pop-up when you launch the app.

Installing the PostgreSQL JDBC Driver

A JDBC driver is required to connect SQL Workbench/J to PostgreSQL. To download the current version, see the PostgreSQL JDBC Driver page.

When the initial connection dialog is displayed (“Select Connection Profile”), click on the “Manage Drivers” button to specify the location of the driver’s JAR file(s).

Connecting to the Northwind Database

Once the PostgreSQL JDBC driver is installed, simply set up your connection string so that it looks like this:

• Driver: PostgreSQL (org.postgresql.Driver)
• URL: jdbc:postgresql://127.0.0.1:5433/northwind
• Username: postgres
• Password: blank
• Autocommit: On

By default, the YugabyteDB port number is set to 5433 and the IP address is 127.0.0.1 (or whatever is found using the yb-ctl status command.) YugabyteDB also creates a user called “postgres” by default with no password.

Test the connection and you should be good to go.

Working with the Northwind Sample Database

That’s it! You are ready to start exploring the Northwind database running on a distributed SQL backend using SQL Workbench/J.

View Database Objects

Go to Tools > Show Database Explorer to see all the tables in the Northwind database.

Querying Data

If you want to try your hand at writing your own SQL queries, you can do that by clicking on the View > Statement menu option. You can now create, run and view the results of your query in this window.

There’s a lot more development and administrative capabilities in SQL Workbench/J. Make sure to check the SQL Workbench/J User’s Manual to dig into how all their various features work.

What’s Next

• Compare YugabyteDB in depth to databases like CockroachDB, Google Cloud Spanner and MongoDB.
• Get started with YugabyteDB on macOS, Linux, Docker, and Kubernetes.
• Contact us to learn more about licensing, pricing or to schedule a technical overview.