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

Start and enable docker service.

sudo systemctl enable --now docker

Once Docker has been installed confirm version:

$ docker version
Client: Docker Engine - Community
 Version:           24.0.7
 API version:       1.43
 Go version:        go1.20.10
 Git commit:        afdd53b
 Built:             Thu Oct 26 09:11:35 2023
 OS/Arch:           linux/amd64
 Context:           default

Server: Docker Engine - Community
 Engine:
  Version:          24.0.7
  API version:      1.43 (minimum version 1.12)
  Go version:       go1.20.10
  Git commit:       311b9ff
  Built:            Thu Oct 26 09:10:36 2023
  OS/Arch:          linux/amd64
  Experimental:     false
 containerd:
  Version:          1.6.25
  GitCommit:        d8f198a4ed8892c764191ef7b3b06d8a2eeb5c7f
 runc:
  Version:          1.1.10
  GitCommit:        v1.1.10-0-g18a0cb0
 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
7d97e254a046: Pull complete
b323a342d564: Pull complete
5817cb8b5fae: Pull complete
8535a7dc8bd9: Pull complete
a5e8db731e10: Pull complete
0c90ac660e09: Pull complete
da4f341837c1: Pull complete
f5abd5cb3b5d: Pull complete
06dba2083f91: Pull complete
a1449111046d: Pull complete
fd6798aed004: Pull complete
Digest: sha256:330decbcd01f5e7a878c5a40afdbef5599ecfd1538c04692840821fec1f79366
Status: Downloaded newer image for sqlpad/sqlpad:latest
3d90ebeeb46e7dd9643ae5a0ef21db1bee2413788c32d00d043233162d52fcc1

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

Then input email address and password:

install SQLPad Linux 02

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

sudo apt update
sudo apt install -y ca-certificates curl gnupg
sudo mkdir -p /etc/apt/keyrings
curl -fsSL https://deb.nodesource.com/gpgkey/nodesource-repo.gpg.key | sudo gpg --dearmor -o /etc/apt/keyrings/nodesource.gpg

NODE_MAJOR=18
echo "deb [signed-by=/etc/apt/keyrings/nodesource.gpg] https://deb.nodesource.com/node_$NODE_MAJOR.x nodistro main" | sudo tee /etc/apt/sources.list.d/nodesource.list
sudo apt update
sudo apt install nodejs -y

Install Node.js on RHEL, CentOS or Fedora

Run the commands:

NODE_MAJOR=18
sudo yum install https://rpm.nodesource.com/pub_$NODE_MAJOR.x/nodistro/repo/nodesource-release-nodistro-1.noarch.rpm -y
sudo yum install nodejs -y
sudo yum install gcc-c++ make -y

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

Confirm installation by checking Node.JS version:

$ node --version
v18.18.2

Once Node is installed, install sqlpad package using npm

sudo 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 version:  3.1.0

CLI examples:

  sqlpad --dbPath ../db --port 3010 --debug --baseUrl /sqlpad
  node server.js --dbPath ../db --port 3010 --debug --baseUrl /sqlpad
  node server.js --config path/to/file.json
  node server.js --config path/to/file.ini

Options:

  --config                      undefined
  --cookieName                  undefined
  --cookieSecret                undefined
  --sessionMinutes              undefined
  --ip                          undefined
  --port                        undefined
  --systemdSocket               undefined
  --httpsPort                   undefined
  --dbPath                      undefined
  --baseUrl                     undefined
  --passphrase                  undefined
  --certPassphrase              undefined
  --keyPath                     undefined
  --certPath                    undefined
  --admin                       undefined
  --adminPassword               undefined
  --debug                       undefined
  --googleClientId              undefined
  --googleClientSecret          undefined
  --publicUrl                   undefined
  --disableUserpassAuth         undefined
  --allowCsvDownload            undefined
  --editorWordWrap              undefined
  --queryResultMaxRows          undefined
  --slackWebhook                undefined
  --tableChartLinksRequireAuth  undefined
  --smtpFrom                    undefined
  --smtpHost                    undefined
  --smtpPort                    undefined
  --smtpSecure                  undefined
  --smtpUser                    undefined
  --smtpPassword                undefined
  --whitelistedDomains          undefined
  --samlEntryPoint              undefined
  --samlIssuer                  undefined
  --samlCallbackUrl             undefined
  --samlCert                    undefined
  --samlAuthContext             undefined

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

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

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

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

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

sqlpad add query min

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.

Recommended books to read:

RELATED ARTICLESMORE FROM AUTHOR

LEAVE A REPLY

Please enter your comment!
Please enter your name here