Your Free and Offline SQL console in a few steps
Tags: devops, sql, database, dataengineering
Published: 2023-10-10 07:40:25

Continuing from the [previous article](https://dev.to/docker/sql-docker-the-combo-for-quick-and-safe-query-testing-3ib), on running SQL queries with **Docker**, we’ll explore how to achieve the same goal with a more straightforward and efficient approach.

One of the key benefits of this tutorial is the ability to establish your **own SQL console** that’s not only free, but also flexible. You’re not limited to PostgreSQL(*that we’ll use in the following*); you can opt for any RDBMS of your choice. Plus your independant from an internet connection. For this we’ll leverage Docker Compose.

This tool allows you to manage multiple container configurations through a single YAML file, making your workflow more efficient. In this tutorial, we’ll focus on setting up two containers: one for the SQL server (`sql-server`) and another for the SQL client (`sql-client`).

## Prerequisites

- Basic understanding of SQL, Docker, and [Docker Compose](https://docs.docker.com/compose/)
- Docker and [Docker Compose](https://docs.docker.com/compose/install/) installed on your system

In this guide, I’ll walk you through two simple steps to execute your SQL script effortlessly.

![2 steps to run SQL queries with docker](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nqeg820bgj8p7pvy5ugc.png)

## Step 1: Specify Docker Compose Configurations

Here’s a sample Docker Compose file to get you started:

```
version: '3'
services:
  sql-server:
    image: postgres:13.12-bullseye
    container_name: sql_server
    environment:
      POSTGRES_PASSWORD=root
    networks:
      - sql_network
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD", "pg_isready", "-U", "postgres"]
      interval: 5s
      timeout: 5s
      retries: 2
      start_period: 1s

  sql-client:
    image: postgres:13.12-bullseye
    container_name: sql_client
    environment:
      PGPASSWORD=root
    networks:
      - sql_network
    depends_on:
      sql-server:
        condition: service_healthy
    volumes:
      - ./local_scripts:/container_scripts
    command: ["/bin/bash", "-c", "psql -h sql-server -U postgres -f /container_scripts/query.sql"]

networks:
  sql_network:
```

### Decoding the Docker Compose File

**1. Version**: The `version: '3'` line indicates that we're using the third version of the Docker Compose file format.

**2. Services:** The `services:` section is where you list all the containers you wish to run. In this example, we have two: `sql-server` and `sql-client`.

**SQL Server Service:** Here’s a breakdown of the `sql-server` service configuration:

- `image`: Specifies the Docker image, in this case, [PostgreSQL](https://www.postgresql.org/) version 13.12. I chose the specific tag **13.12-bullseye** from the [Docker Hub](https://hub.docker.com/_/postgres/tags?page=1&name=13).
- `container_name`: Names the container as `sql_server`.
- `environment`: Sets the PostgreSQL password through an environment variable.
- `networks`: Links the container to a custom network named `sql_network`.
- `ports`: Maps the container's port 5432 to the host machine's corresponding port.
- `healthcheck`: Sets up a health check to ensure the service is operational.

**SQL Client Service:** This service has additional parameters:

- `depends_on`: Indicates a dependency on the `sql_server` being healthy.
- `volumes`: Maps a local directory to a container directory. *Specify the local directory containing your .sql script there.*
- `command`: Specifies the command to run at startup, which in this case is our SQL script.

**3. Networks:** This section defines a custom network (`sql_network`) that both services will use for communication.

## Step 2: Edit and execute your SQL Queries

> Ensure your SQL script is in the local directory specified in the volumes section of your Docker Compose file.
> 

To execute the script, run the following command:

```
docker-compose up sql-client
```

This command only starts the `sql-client` container but also initiates the `sql-server` in the background due to the dependency. The advantage is that you'll only see the output logs for `sql-client`, keeping your console clean.

![docker compose up sql-client outputs](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7rc2ykt8apz874kiiim4.png)

If we’ve used docker comose up without specifying, we would have the sql_server outputs too.


![docker compose up output
](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/39b7nt7g33lx9lkkquug.png)

You can identify in the left part, the container for which the logs are.

## Best Practices Digest

### Create a Free and Offline SQL Console

After following this tutorial, all you need to create your own console is your preferred IDE, a file to edit your queries, and this Docker Compose setup.

Here’s how it works:

1. Your favorite IDE: Open your IDE to navigate to the folder where your Docker Compose file and SQL script are located.
2. Edit your aueries: Open the SQL file in your IDE and make your changes or add new queries as needed.
3. Run the command: Once you’ve edited the SQL file, simply run the following command to execute your queries:

```
docker-compose up sql-client
```

This approach not only streamlines your SQL testing process but also makes it incredibly accessible. Each time you want to test a query, you just have to navigate to the folder in your IDE, edit the file, and run the command. It’s that simple!

### Using .env Files

It’s crucial to manage environment variables using `.env` files for better security and maintainability. It prevent you from pushing sensitive information in your VCS(github, gitlab, etc…).

You can replace the `environment` section with `env_file`, like so:

```
# Replace the section
environment:
      POSTGRES_PASSWORD=root
# By the section
env_file:
  - server.env
```

With `server.env` containing :

```
# Variables for the database server
POSTGRES_PASSWORD=root
```

### The Power of CTEs

CTEs are invaluable for simulating table creation and executing complex queries, making your SQL scripts more flexible and readable.

To illustrate the utility of CTEs, let’s look at a SQL sample where we use a CTE to create a temporary table for inline queries:

```
WITH users AS (
  SELECT 'John Doe' as name, 'john.doe@example.com' as email
  UNION
  SELECT 'Jane Smith' as name, 'jane.smith@example.com' as email
  UNION
  SELECT 'Samuel Jackson' as name, 'samuel.jackson@example.com' as email)
-- Retrieve some records from the users table
SELECT *
FROM users
WHERE name LIKE 'J%';

```

In summary, Docker Compose simplifies SQL testing, making it more accessible and efficient. Employing `.env` files and CTEs(Common Table Expressions) in your SQL scripts are not just good practices; they're best practices that enhance security, maintainability, and flexibility.

I hope you find this guide useful. Stay tuned for more articles on maximizing Docker for a variety of tasks. Feel free to share your thoughts or alternative methods in the comments below.

Don’t forget to follow me for the latest updates and insights.

Happy Querying!

I would like to thank [Ajeet Singh RainaVoir](https://www.linkedin.com/in/ajeetsraina) for his contribution in the Docker Compose file spcification. 

To keep the conversation going, connect with me on [LinkedIn](https://www.linkedin.com/in/bricefotzo/)!