(Last Updated On: October 4, 2018)

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.

How to install SQLPad on Linux

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_8.x | sudo -E bash -
sudo apt-get install -y nodejs

Install Node.js on RHEL, CentOS or Fedora

Run the commands:

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

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

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.

After the installation of SQLPad, you should be ready to start the server.

sqlpad

The  sqlpad command should be located under /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 --dir /var/lib/sqlpad/ \
--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 emall 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.