Configure the SQuirreL SQL Client to use the SingleStore JDBC Driver
Tags: singlestoredb, squirrelsql, jdbc
Published: 2022-11-04 15:34:09

## Abstract
SingleStore has developed a JDBC Driver. This article will show how to configure and use this JDBC driver from SQuirreL SQL, a popular database client.

## Introduction
This article used a Virtual Machine running Ubuntu 20.04.3 with a Java 8 JRE, previously installed as follows:

```shell
sudo apt install openjdk-8-jre
```

## Create a SingleStoreDB Cloud account
A [previous article](https://dev.to/veryfatboy/quick-tip-using-dbt-with-singlestoredb-161g) showed the steps required to create a free SingleStoreDB Cloud account. We'll use **SQuirreL Demo Group** as our Workspace Group Name and **squirrel-demo** as our Workspace Name. We'll make a note of our **password** and **host** name.

## Create the database and table
We'll use a subset of the inventory system example from a [previous article](https://medium.com/@VeryFatBoy/using-singlestore-as-a-json-document-database-bc386fc5c563), as it provides a combination of both Relational and JSON data. In the **SQL Editor** in SingleStoreDB Cloud, we'll create a database and a table:

```sql
CREATE DATABASE e_store;

USE e_store;

CREATE TABLE products (
     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
     name VARCHAR(250) NOT NULL,
     brand_id INT UNSIGNED NOT NULL,
     category_id INT UNSIGNED NOT NULL,
     attributes JSON NOT NULL,
     PRIMARY KEY(id),
     INDEX CATEGORY_ID(category_id ASC),
     INDEX BRAND_ID(brand_id ASC)
);
```

## Populate the database table
Let's now populate the **products** table:

```sql
-- Televisions
INSERT INTO products (name, brand_id, category_id, attributes) VALUES
('Prime', '1', '1', '{"screen" : "50 inch", "resolution" : "2048 x 1152 pixels", "ports" : {"hdmi" : 1, "usb" : 3}, "speakers" : {"left" : "10 watt", "right" : "10 watt"}}'),
('Octoview', '1', '1', '{"screen" : "40 inch", "resolution" : "1920 x 1080 pixels", "ports" : {"hdmi" : 1, "usb" : 2}, "speakers" : {"left" : "10 watt", "right" : "10 watt"}}'),
('Dreamer', '1', '1', '{"screen" : "30 inch", "resolution" : "1600 x 900 pixels", "ports" : {"hdmi" : 1, "usb" : 1}, "speakers" : {"left" : "10 watt", "right" : "10 watt"}}'),
('Bravia', '1', '1', '{"screen" : "25 inch", "resolution" : "1366 x 768 pixels", "ports" : {"hdmi" : 1, "usb" : 0}, "speakers" : {"left" : "5 watt", "right" : "5 watt"}}'),
('Proton', '1', '1', '{"screen" : "20 inch", "resolution" : "1280 x 720 pixels", "ports" : {"hdmi" : 0, "usb" : 0}, "speakers" : {"left" : "5 watt", "right" : "5 watt"}}');

-- Mobile Phones
INSERT INTO products (name, brand_id, category_id, attributes) VALUES
('Desire', '2', '2', JSON_BUILD_OBJECT("network",
        JSON_ARRAY_PUSH_STRING('["GSM", "CDMA", "HSPA"]', 'EVDO'),
        "body",
        "5.11 x 2.59 x 0.46 inches",
        "weight",
        "143 grams",
        "sim",
        "Micro-SIM",
        "display",
        "4.5 inches",
        "resolution",
        "720 x 1280 pixels",
        "os",
        "Android Jellybean v4.3"
    )
),
('Passion', '2', '2', JSON_BUILD_OBJECT("network",
        JSON_ARRAY_PUSH_STRING('["GSM", "CDMA"]', 'HSPA'),
        "body",
        "6.11 x 3.59 x 0.46 inches",
        "weight",
        "145 grams",
        "sim",
        "Micro-SIM",
        "display",
        "4.5 inches",
        "resolution",
        "720 x 1280 pixels",
        "os",
        "Android Jellybean v4.3"
    )
),
('Emotion', '2', '2', JSON_BUILD_OBJECT("network" ,
        JSON_ARRAY_PUSH_STRING('["GSM", "CDMA"]', 'EVDO'),
        "body",
        "5.50 x 2.50 x 0.50 inches",
        "weight",
        "125 grams",
        "sim",
        "Micro-SIM",
        "display",
        "5.00 inches",
        "resolution",
        "720 x 1280 pixels",
        "os",
        "Android KitKat v4.3"
    )
),
('Sensation', '2', '2', JSON_BUILD_OBJECT("network",
        JSON_ARRAY_PUSH_STRING('["GSM", "HSPA"]', 'EVDO'),
        "body",
        "4.00 x 2.00 x 0.75 inches",
        "weight",
        "150 grams",
        "sim",
        "Micro-SIM",
        "display",
        "3.5 inches",
        "resolution",
        "720 x 1280 pixels",
        "os",
        "Android Lollipop v4.3"
    )
),
('Joy', '2', '2', JSON_BUILD_OBJECT("network",
        JSON_ARRAY_PUSH_STRING('["CDMA", "HSPA"]', 'EVDO'),
        "body",
        "7.00 x 3.50 x 0.25 inches",
        "weight",
        "250 grams",
        "sim",
        "Micro-SIM",
        "display",
        "6.5 inches",
        "resolution",
        "1920 x 1080 pixels",
        "os",
        "Android Marshmallow v4.3"
    )
);

-- Cameras
INSERT INTO products (name, brand_id, category_id, attributes) VALUES
('Explorer', '3', '3', '{"sensor_type" : "CMOS", "processor" : "Digic DV III", "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LCD"}'),
('Runner', '3', '3',   '{"sensor_type" : "CMOS", "processor" : "Digic DV II",  "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LED"}'),
('Traveler', '3', '3', '{"sensor_type" : "CMOS", "processor" : "Digic DV II",  "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LCD"}'),
('Walker', '3', '3',   '{"sensor_type" : "CMOS", "processor" : "Digic DV I",   "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LED"}'),
('Jumper', '3', '3',   '{"sensor_type" : "CMOS", "processor" : "Digic DV I",   "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LCD"}');
```

## Install SQuirreL SQL
The latest version of SQuirreL SQL compatible with Java 8 is 4.2. We'll [download](https://sourceforge.net/projects/squirrel-sql/files/1-stable/4.2.0/) this from SourceForge into our `Downloads` folder.

Next, we'll install the software, as follows:

```shell
java -jar squirrel-sql-4.2.0-standard.jar
```

We'll take all the defaults from the installation wizard, including the `squirrel-sql-4.2.0` target directory, in our home folder.

## Download SingleStore JDBC Driver

We'll [download](https://github.com/memsql/S2-JDBC-Connector/releases) the latest version of the SingleStore JDBC Driver that, at the time of writing this article, was `singlestore-jdbc-client-1.1.4.jar`.

We'll move the jar file to the directory `squirrel-sql-4.2.0/lib`.

## Launch SQuirreL SQL
In a terminal window from the directory `squirrel-sql-4.2.0` we'll run the following command:

```shell
./squirrel-sql.sh
```

This will launch SQuirreL SQL, as shown in Figure 1.

![Figure 1. Home Screen.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sdv2386z9xchpo5eq4vk.png)

<center>Figure 1. Home Screen.</center>

## Add Driver
From the left-hand navigation pane, we'll select **Drivers**.

Next, we'll select the ✚ sign to **Create a New Driver**. We'll enter the following details:

- **Name:** SingleStoreDB
- **Example URL:** jdbc:singlestore://&lt;host&gt;:3306/&lt;database&gt;
- **Java Class Path:** singlestore-jdbc-client-1.1.4.jar
- **Class Name:** com.singlestore.jdbc.Driver

This is shown in Figure 2.

![Figure 2. Add Driver.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bvwtygx9w2qmwjkjlz9c.png)

<center>Figure 2. Add Driver.</center>

Next, we'll click **OK**.

## Add Alias
From the left-hand navigation pane, we'll select **Alias**.

Next, we'll select the ✚ sign to **Create a New Alias**. We'll enter the following details:

- **Name:** e_store
- **Driver:** SingleStoreDB
- **URL:** jdbc:singlestore://&lt;host&gt;:3306/e_store
- **User Name:** admin
- **Password:** &lt;password&gt;

This is shown in Figure 3.

![Figure 3. Add Alias.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bxpo0mka301vsg7v8g07.png)

<center>Figure 3. Add Alias.</center>

We'll replace the `<host>` and `<password>` with the values from our SingleStoreDB Cloud account.

We'll click **Test** to test the connection. It should be successful, as shown in Figure 4.

![Figure 4. Test Connection.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6un2twnms3mppi3fqjji.png)

<center>Figure 4. Test Connection.</center>

We'll click **OK** and then click **Connect** followed by **Connect** again.

## Explore the database

From the **Objects** tab, we can expand **e_store &gt; TABLE &gt; products**, and explore the data, as shown in Figure 5.

![Figure 5. Objects tab.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c5exn5luztso9n9yj09g.png)

<center>Figure 5. Objects tab.</center>

## Run SQL queries
We can run SQL queries from the **SQL** tab, as shown in Figure 6.

![Figure 6. SQL tab.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m39y7xv852j83bdeiwlx.png)

<center>Figure 6. SQL tab.</center>

## Summary
This article has shown how to configure and use the SingleStore JDBC Driver with a popular SQL client.