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

ComputingPost
5 min readSep 14, 2022

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 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

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.

https://www.computingpost.com/how-to-manage-mysql-postgresql-sql-server-using-sqlpad-editor/?feed_id=126&_unique_id=63226ae36edbd

--

--

ComputingPost

ComputingPost — Linux Howtos, Tutorials, Guides, News, Tips and Tricks.