Introduction To A Tool for Data Investigation: SQLPad
Setup on Your Local Machine with Docker, Creating a Dummy DB, Create SQLPad Connections, Bind SQLite With Docker Volumes, Run Some Queries
Introduction
In this story, we will talk about an open-source tool for data investigation I met this week: SQLPad. Maybe you know, I have gotten some steps in my career that I have written in my previous paper. Nowadays, I meet some new technologies. Also, I will be writing about them in the coming weeks.
Basically, SQLPad is an SQL editor to run queries. We can do some basic visualizations in SQLPad. In my using cases, we use it as a tool for data investigation. We will set it up on our local machines and use it with Docker. You can visit its official website via the link below.
Let’s Start to Setup
There are 2 ways to run SQLPad. We will be following the way which uses Docker. We need to have a Docker engine on our machines. I assume you have the engine. If you have not, you can follow the link below to get the engine.
Pull The Image
I work on a Debian machine. So, I will execute the code in Debian’s terminal that I will use and explain in this paper. SQLPad’s Docker image can be accessed on Docker Hub.
Actually, I am not going to be explaining all Docker commands in this paper. Because explaining the Docker commands is not a main topic of this paper.
To download that image to my local, I am going to execute the code below.
docker pull sqlpad/sqlpadThe image will be downloaded on my local.
We can execute docker image ls for sure.
Run SQLPad By Using The Image
Now, we can create a container by using the image we have already pulled. To create a container, I am going to use the code below.
docker run -p 3000:3000 sqlpad/sqlpadWe bound port 3000 of our machine to the container’s port 3000. Therefore, we can access the SQLPad by using our machine’s port 3000. If you succeed, you should see a similar below output on your terminals.
And if you go to localhost:3000 on your machine, you have to see the same screen below.
Now, we can deep dive a little bit into SQLPad configure.
Creating a Dummy DB: Northwind
In this section of the paper, we are going to create a dummy database to connect from SQLPad. We use SQLite as a database and SQLiteBrowser as a DBMS.
In Debian, we can install them by using the code below.
sudo apt-get install sqlite3 && sudo apt-get install sqlitebrowserI am going to create a database by using the SQL Script below:
https://raw.githubusercontent.com/jpwhite3/northwind-SQLite3/master/Northwind.Sqlite3.create.sql
I created the database on my Desktop folder. It is important to keep the database’s file path in mind. We will use it while binding the database to a Docker container via Docker volumes.
SQLPad Configure and Create Script For Docker Container
We can play with SQLPad’s configs to set do we need to use an authentication mechanism or not, etc. You can visit the link below to see all options of SQLPad configures.
I have prepared a script file to create a Docker container automatically. You can access the script in the last section of this paper.
In my script, we created a default admin user, set a port to run SQLPad, and create a default connection. Here, we are using the database’s file path which we created in the previous section. We use -v REAL_PATH:CONTAINER_PATH to bind a volume to the container in Docker CLI.
In our example, we bonded the database we created on our desktop to /databases/NorthwindDB.sqlite path in the container. Otherwise, SQLPad can not access the SQLite database. Because the database is a physical file on our machine. We bound the volume to create a bridge between our machine and the container.
Run Some Queries
We set up a container to run SQLPad. I am going to execute the script we coded above.
./start-sqlpad.shIf we go to localhost:8000 address, we see the same screen below after login via admin user credentials we set in the script file.
SQLPad can not show the schema by working on SQLite. To show the tables we have, we are going to execute a query.
--We can not see the database schema with SQLite3.
--We execute the script below to handle this issue
SELECT
'dba' as table_schema,
name as table_name,
'unknown' as column_name,
'unknown' as data_type
FROM sqlite_master
WHERE type = 'table';
Also, we can do some visualization on SQLPad. To give an example, I am going to execute another query.
select OrderID, CategoryID, CategoryName, Quantity, UnitPrice from [Order Details]left join Categories on [Order Details].ProductID = Categories.CategoryIDwhere CategoryID not null
Finally
I think SQLPad is a useful and simple tool to use. Probably, I will use it on my own projects to solve my customers’ requests.
Also, I created a Gist for the bash script we used to adjust the Docker container.
I enjoyed writing and coding. Hopefully, you enjoyed too it. We have some extra features of SQLPad like adding users, adding database connections, managing user roles, etc. I didn’t show them because you can easily apply them via the GUI. I think we did the core features’ examples like playing with configs, binding volume, etc.
Kind regards.
