Your Free and Offline SQL console in a few steps

Continuing from the previous article, 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
• Docker and Docker Compose installed on your system

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

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 version 13.12. I chose the specific tag 13.12-bullseye from the Docker Hub.
• 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.

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

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 for his contribution in the Docker Compose file spcification.

To keep the conversation going, connect with me on LinkedIn!