DEV Community: Brice Fotzo

Your Free and Offline SQL console in a few steps

Brice Fotzo — Tue, 10 Oct 2023 07:40:25 +0000

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:

Your favorite IDE: Open your IDE to navigate to the folder where your Docker Compose file and SQL script are located.
Edit your aueries: Open the SQL file in your IDE and make your changes or add new queries as needed.
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!

SQL + Docker: The combo for Quick and Safe Query Testing

Brice Fotzo — Fri, 08 Sep 2023 14:20:35 +0000

Testing SQL queries is a common need for developers, data engineers, analysts and so on.

Docker provides a remarkable solution to run SQL queries, especially for concerns such as offline access, data safety, reliability, or flexibility to switch RDBMS and their versions.

In this article, I will walk you through executing your SQL queries using Docker.

This guide is ideal for SQL enthusiasts, data guys, and developers.
Want to learn SQL, write and dry run complex transformations or even test queries before adding them to your app code? Stay tuned for this and upcoming articles around this topic.

Let’s dive in!

Prequisites

Before starting, make sure you have:

Basic understanding of SQL and Docker(this tutorial can help)
Docker installed on your machine

I will guide you through the steps you need to run SQL queries with docker.

Getting Started:

Step 1: Identify and Pull a SQL Database

Choose the desired SQL database version and fetch its image from Docker Hub.

For this demonstration, we’ll use version 13 of PostgreSQL.

Let’s pull our desired image version using the docker pull command:

docker pull: This command fetches a Docker image from a repository, usually from Docker Hub. Once pulled, the image is stored locally on your machine, allowing you to create containers from it.

docker pull postgres:13.12-bullseye

postgres:13.12-bullseye: This is the name and tag of the Docker image we want to pull. Here, we're pulling version 13.12 of the PostgreSQL image tagged with "bullseye".

Step 2: Run a DB container

Initiate the selected database server within a Docker container for testing.

Having the image on our local Docker registry, let’s run the PostgresSQL server in a container with the command docker run:

docker run: This command is used to start a new container from a Docker image.

docker run --name sql_container -e POSTGRES_PASSWORD=root -d postgres:13.12-bullseye

--name sql_container: This flag allows us to name our container. In this case, we're naming it "sql_container".
-e POSTGRES_PASSWORD=root: The -e flag lets us set environment variables inside the container. Here, we're setting the PostgreSQL password to "root".
-d: This flag means "detached mode", which runs the container in the background.
postgres:13.12-bullseye: This specifies the image (and its tag) from which the container should be created.

Step 3: Import your SQL queries into the container

Prepare and transfer your SQL script into the Docker container for execution.

Let’s say we want to test a simple query that filters a user table for names starting by J. The query would be:

-- Retrieve records from the users table
SELECT * 
FROM users
WHERE name LIKE 'J%';

To test this, you need some sample data. Here is a sample:

-- Drop the users table if it already exists
DROP TABLE IF EXISTS users;

-- Create a new table named users
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

-- Insert a few records into the users table
INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com');
INSERT INTO users (name, email) VALUES ('Jane Smith', 'jane.smith@example.com');
INSERT INTO users (name, email) VALUES ('Samuel Jackson', 'samuel.jackson@example.com');

Let’s encapsulate all of that into a singular .sql script and copy it into the container using docker cp:

docker cp: This command is used to copy files or directories from the host system (your machine) to a container or vice versa.

docker cp /path/to/the/script.sql sql_container:/query.sql

/path/to/the/script.sql: This is the path on your machine where your SQL script is located. You should replace this with the actual path to your file.
sql_container: This is the name of the container where you want to copy the file. As mentioned before, we named our container "sql_container".
:/query.sql: This specifies the destination path inside the container. After the command is executed, your SQL script will be accessible inside the container at the path /query.sql.

Step 4: Execute the query

Run your SQL script within the containerized database to see the results.

Default execution mode

Once the script is available in the container, run it using the command docker exec:

docker exec: This command allows you to run commands inside a running Docker container.

docker exec sql_container sh -c "psql -U postgres -a -f /query.sql"

sql_container: This is the name we gave our running PostgreSQL container. We're telling Docker to execute our command inside this container.
sh -c: We're using the shell (sh) to execute a command. The -c flag allows us to pass in the command we want the shell to execute.
"psql -U postgres -a -f /query.sql": This is the command we're asking the shell to run. Let's break it down further:

By running this command, you’re effectively telling Docker to run your SQL script inside the PostgreSQL server that’s running in the sql_container. You end up with the result:

Optionally, if you need to stop the container after usage use:

docker stop sql_container
docker rm sql_container

Console execution mode

It’s also possible to run your queries in SQL console mode, enabling quick edits and reruns. I will cover in the next article.

While traditional sandboxing methods work, Docker provides added advantages:

No Interference: With Docker, you can test SQL without touching your main databases. It’s like having a separate room for testing.
Different SQL Versions: With Docker, you can use many SQL versions without installing them all. Just choose and use.
Consistent Everywhere: If you use Docker, your SQL tests will work the same on any computer. This is good for teams that wants to share tests in the same environment.
Clean After Use: When you finish testing, Docker lets you remove everything easily. Your computer stays clean.
Works with DevOps: If you use DevOps tools, Docker can fit in. This makes testing SQL automatic and easy.

In summary, in just a few steps, you can setup a local sandbox suitable for any RDBMS database for your querying needs.

Docker makes SQL testing easy and safe. If you want to test SQL, Docker helps a lot. It’s simple, clean, and useful for everyone, even if you’re not an expert. When you need to test SQL, think of Docker.

I hope you find this helpful. I plan to share more content on how to leverage Docker not only for SQL but other tasks. Stay tuned for more insights!

Feel free to share your thoughts or alternative methods in the comments.

Happy Querying!

Connect with me LinkedIn to continue the discussion!