Learn how to – How To Manage MySQL / PostgreSQL / SQL Server using SQLPad Editor

Learn how to – How To Manage MySQL / PostgreSQL / SQL Server using SQLPad Editor.

SQLPad is a web-based SQL editor for writing and running SQL queries and visualizing the results. It supports MySQL, SQL Server, PostgreSQL, Presto, Vertica, Crate, SAP HANA, and Cassandra. It is a self-hosted that you can install in your Infrastructure (VM, Container, Dedicated server e.t.c) or running in a cloud compute instance.

Install SQLPad Web-based SQL Editor on Linux

There are two ways we can install SQLPad on Linux:

1. Running SQLPad in a Docker container
2. Install using NPM

Method 1: Run SQLPad on Linux in Docker

First install Docker in your system:

curl -fsSL https://get.docker.com  | sudo bash

Add your user account to Docker group:

sudo usermod -aG docker $USER
newgrp docker

Once Docker has been installed confirm version:

$ docker version
Client: Docker Engine - Community
 Version:           20.10.7
 API version:       1.41
 Go version:        go1.13.15
 Git commit:        f0df350
 Built:             Wed Jun  2 11:56:38 2021
 OS/Arch:           linux/amd64
 Context:           default
 Experimental:      true

Server: Docker Engine - Community
 Engine:
  Version:          20.10.7
  API version:      1.41 (minimum version 1.12)
  Go version:       go1.13.15
  Git commit:       b0f5bc3
  Built:            Wed Jun  2 11:54:50 2021
  OS/Arch:          linux/amd64
  Experimental:     false
 containerd:
  Version:          1.4.6
  GitCommit:        d71fcd7d8303cbf684402823e425e9dd2e99285d
 runc:
  Version:          1.0.0-rc95
  GitCommit:        b9ee9c6314599f1b4a7f497e1f1f856fe433d3b7
 docker-init:
  Version:          0.19.0
  GitCommit:        de40ad0

Create persistent data directory for SQLPad:

mkdir -p ~/sqlpad/data

Run the command below to start SQLPad container:

docker run --name sqlpad -p 3000:3000 --volume ~/sqlpad/data:/var/lib/sqlpad --detach sqlpad/sqlpad:latest

This will pull the Docker image and start the container:

latest: Pulling from sqlpad/sqlpad
b4d181a07f80: Pull complete
f9bc4241c5e2: Pull complete
e3758d51f5d3: Pull complete
a35f4c89ed46: Pull complete
97b67d1fa480: Pull complete
3589ca9a0aca: Pull complete
92dd252a3f71: Pull complete
742966445ca1: Pull complete
b2515f59fbe8: Pull complete
be7d4d86dbc2: Pull complete
329c314166c3: Pull complete
Digest: sha256:1680087a7b2776cb8caa59c9ca3763e332f1784fcbce785423f47ba9b3f15725
Status: Downloaded newer image for sqlpad/sqlpad:latest
bce70f2d72609dc5fb8ee2be58e590265db6857d9aac87fda9228dcb322cf2dc

Confirm the container is running:

$ docker ps
CONTAINER ID   IMAGE                  COMMAND                CREATED         STATUS         PORTS                                       NAMES
bce70f2d7260   sqlpad/sqlpad:latest   "/docker-entrypoint"   2 minutes ago   Up 2 minutes   0.0.0.0:3000->3000/tcp, :::3000->3000/tcp   sqlpad

Then access SQLPad Web interface in:

http://server_ip_address:3000

Use Signup to create new admin account:

Then input email address and password:

To stop running docker image by name use:

$ docker stop sqlpad

Method 2: Install SQLPad using Node.JS NPM

SQLPad application is written in Node.js and you’ll need to install it first.

Install Node.js on Ubuntu / Debian

curl -sL https://deb.nodesource.com/setup_12.x | sudo -E bash -
sudo apt install -y nodejs

Install Node.js on RHEL, CentOS or Fedora

Run the commands:

curl --silent --location https://rpm.nodesource.com/setup_12.x | sudo bash -

For other distributions, refer to How to run multiple versions of Node.js on Linux

Confirm installation by checking Node.JS version:

$ node --version
v12.22.2

Once Node is installed, install sqlpad package using npm

npm install sqlpad -g

This will install the SQLPad command line utility used to run an SQLPad server.

The  sqlpad command should be located under /usr/bin/sqlpad:

$ which sqlpad 
/usr/bin/sqlpad

To get help and see parameters:

$ sqlpad --help

SQLPad Help:
Usage: sqlpad [options]

Options: 
  --passphrase [phrase]   Passphrase for modest encryption
                            optional, default: *******
                            environment var: SQLPAD_PASSPHRASE
  --dir [path]            Data directory 
                            optional, default: $HOME/sqlpad/db
                            environment var: SQLPAD_DB_PATH
  --ip [ip]               IP address to bind to
                            optional, default: 0.0.0.0 (all IPs)
                            environment var: SQLPAD_IP
  --port [port]           Port to run on 
                            optional, default: 80
                            environment var: SQLPAD_PORT
  --base-url [path]       Base url to mount sqlpad routes to 
                            optional, default: ''
                            environment var: SQLPAD_BASE_URL
  --admin [emailaddress]  Whitelist/add admin permission to email provided.
                            optional, default: ''
                            environment var: SQLPAD_ADMIN
  --debug                 Enable extra console logging
                            optional, default: false
                            environment var: SQLPAD_DEBUG (set to TRUE)

  --save                  Saves above parameters to file for future use.
  --forget                Forget parameters previously saved.

  See configuration management page in-application for 
  additional settings and further documentation.

Example: 
  sqlpad --dir ./sqlpaddata --ip 127.0.0.1 --port 3000 --passphrase secr3t

Configure SQLPad Server to start on boot

We’ll use systemd to manage sqlpad service on our system. SQLPad stores its data in $HOME/sqlpad/db but can use a directory.

sudo mkdir -p /var/lib/sqlpad/db

Add system user that will run and manage sqlpad service

sudo groupadd --system sqlpad
sudo useradd -s /sbin/nologin --system -g sqlpad sqlpad

Set permissions for /var/lib/sqlpad/

sudo chown -R sqlpad:sqlpad /var/lib/sqlpad/
sudo chmod -R 775  /var/lib/sqlpad/

Create a systemd service file

sudo vim /etc/systemd/system/sqlpad.service

Add content like below

[Unit]
Description=SQLPad Web based SQL Editor
Documentation=https://github.com/rickbergfalk/sqlpad
Wants=network-online.target
After=network-online.target

[Service]
Type=simple
User=sqlpad
Group=sqlpad
ExecReload=/bin/kill -HUP $MAINPID
ExecStart=/usr/bin/sqlpad --dbPath /var/lib/sqlpad/db 
--ip 0.0.0.0 
--port 8000 
--admin admin@example.com 
--passphrase StrongPassphrase
SyslogIdentifier=sqlpad
Restart=always

[Install]
WantedBy=multi-user.target

Replace:

•  StrongPassphrase with your desired Passphrase.
• 0.0.0.0 with your machine IP if you don’t want the service to listen on all available interfaces
• admin@example.com with the email address you’re adding admin permissions for.
• Port 8000 with your desired service port

Reload systemd and start the service

sudo systemctl daemon-reload
sudo systemctl start sqlpad

Enable the service to start on boot

sudo systemctl enable sqlpad

If the start was successful, a status message should be similar to below

Access SQLPad web interface

Now that the setup is complete, open http://serverip:port/signup on your browser to create  admin user with the email whitelisted in the configuration file.

Provide required details and click “Sign Up”, when done login to the dashboard and add a new database connection by navigating to admin > Connections

Select Database Driver and fill all connection details – IP address, username, password. Database to use is optional since it will display all databases which the user has grants for.

When done, click on the New Query tab to start adding your queries and visualize them.

That’s all. You now have the power to play with SQLPad and provide feedback to the developer for improvements. I hope this guide was helpful.

Best Udemy Video Courses to Learn MySQL / MariaDB Databases:

• The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
• SQL – MySQL for Data Analytics and Business Intelligence
• MySQL, SQL and Stored Procedures from Beginner to Advanced
• SQL for Beginners: Learn SQL using MySQL and Database Design
• The Complete MySQL Developer Course
• MySQL Database Administration: Beginner SQL Database Design
• Learn Database Design with MySQL