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

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', '[e-mail]');
INSERT INTO users (name, email) VALUES ('Jane Smith', '[e-mail]');
INSERT INTO users (name, email) VALUES ('Samuel Jackson', '[e-mail]');

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!