How To Manage MySQL / PostgreSQL / SQL Server using SQLPad Editor

By Posted on 499 views

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:

install-SQLPad-Linux-01-1024x584

Then input email address and password:

install-SQLPad-Linux-02-1024x407

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 -

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 [email protected] \
--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
  • [email protected] 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

sqlpad-check-status-min-1024x229

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.

sqlpad-signup-min-1024x454

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

sqlpad-add-database-connection-02-min-1024x958

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.

sqlpad-list-connections-min-1024x198

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

sqlpad-add-query-min-1024x480

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.

Gravatar Image
Ben Bennett - https://www.thetodaypost.net
A systems engineer with excellent skills in systems administration, cloud computing, systems deployment, virtualization, containers, and a certified ethical hacker.