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

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:

Running SQLPad in a Docker container
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 -

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

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.

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.

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