A web app for writing and running SQL queries and visualizing the results. Supports Postgres, MySQL, SQL Server, Crate, Vertica, Presto, and SAP HANA. Other databases potentially supported via unix odbc support.
The docker image runs on port 3000 and uses /var/lib/sqlpad for the embedded database directory.
latest tag is continously built from latest commit in repo. Use specific version tags to ensure stability.
See docker-examples directory for example docker-compose setup with SQL Server.
For instructions on installing/running SQLPad from git repo see developer guide
SQLPad may be configured via environment variables, config file, or command line flags.
Config file path may be specified passing command line option --config or environment variable SQLPAD_CONFIG.
For example:
node server.js --config ~/sqlpad.iniFor INI and JSON config file examples, see config-example.ini and config-example.json in GitHub repository.
admin
Email address to whitelist/give admin permissions to
Env var: SQLPAD_ADMIN
adminPassword
Password to set for admin email address on application start. Requires admin setting to also be provided.
Env var: SQLPAD_ADMIN_PASSWORD
allowCsvDownload
Enable csv, json and xlsx downloads.
Env var: SQLPAD_ALLOW_CSV_DOWNLOAD
Default: true
baseUrl
Path to mount sqlpad app following domain. Example, if '/sqlpad' is provided queries page would be mydomain.com/sqlpad/queries
Env var: SQLPAD_BASE_URL
certPassphrase
Passphrase for your SSL certification file
Env var: CERT_PASSPHRASE
certPath
Absolute path to where SSL certificate is stored
Env var: CERT_PATH
cookieName
Name used for cookie. If running multiple SQLPads on same domain, set to different values.
Env var: SQLPAD_COOKIE_NAME
Default: sqlpad.sid
cookieSecret
Secret used to sign cookies
Env var: SQLPAD_COOKIE_SECRET
Default: secret-used-to-sign-cookies-please-set-and-make-strong
dbPath
Directory to store SQLPad embedded database content. This includes queries, users, query result cache files, etc.
Env var: SQLPAD_DB_PATH
dbInMemory
If enabled, runs embedded database nedb in memory. dbPath is still required to be provided for cache and session support. (dbPath will be made optional in future release)
Env var: SQLPAD_DB_IN_MEMORY
debug
Add a variety of logging to console while running SQLPad
Env var: SQLPAD_DEBUG
disableAuth
Set to TRUE to disable authentication altogether.
Env var: DISABLE_AUTH
disableUserpassAuth
Set to TRUE to disable built-in user authentication. Use to restrict auth to OAuth only.
Env var: DISABLE_USERPASS_AUTH
editorWordWrap
Enable word wrapping in SQL editor.
Env var: SQLPAD_EDITOR_WORD_WRAP
googleClientId
Google Client ID used for OAuth setup. Authorized redirect URI for sqlpad is '[baseurl]/auth/google/callback'
Env var: GOOGLE_CLIENT_ID
googleClientSecret
Google Client Secret used for OAuth setup. Authorized redirect URI for sqlpad is '[baseurl]/auth/google/callback'
Env var: GOOGLE_CLIENT_SECRET
httpsPort
Port for SQLPad to listen on.
Env var: SQLPAD_HTTPS_PORT
Default: 443
ip
IP address to bind to. By default SQLPad will listen from all available addresses (0.0.0.0).
Env var: SQLPAD_IP
Default: 0.0.0.0
keyPath
Absolute path to where SSL certificate key is stored
Env var: KEY_PATH
passphrase
A string of text used to encrypt sensitive values when stored on disk.
Env var: SQLPAD_PASSPHRASE
Default: At least the sensitive bits won't be plain text?
port
Port for SQLPad to listen on.
Env var: SQLPAD_PORT
Default: 80
publicUrl
Public URL used for OAuth setup and email links. Protocol expected. Example: https://mysqlpad.com
Env var: PUBLIC_URL
queryResultMaxRows
By default query results are limited to 50,000 records.
Env var: SQLPAD_QUERY_RESULT_MAX_ROWS
Default: 50000
samlAuthContext
SAML authentication context URL
Env var: SAML_AUTH_CONTEXT
samlCallbackUrl
SAML callback URL
Env var: SAML_CALLBACK_URL
samlCert
SAML certificate in Base64
Env var: SAML_CERT
samlEntryPoint
SAML Entry point URL
Env var: SAML_ENTRY_POINT
samlIssuer
SAML Issuer
Env var: SAML_ISSUER
sessionMinutes
Minutes to keep a session active. Will extended by this amount each request.
Env var: SQLPAD_SESSION_MINUTES
Default: 60
slackWebhook
Supply incoming Slack webhook URL to post query when saved.
Env var: SQLPAD_SLACK_WEBHOOK
smtpFrom
From email address for SMTP. Required in order to send invitation emails.
Env var: SQLPAD_SMTP_FROM
smtpHost
Host address for SMTP. Required in order to send invitation emails.
Env var: SQLPAD_SMTP_HOST
smtpPassword
Password for SMTP.
Env var: SQLPAD_SMTP_PASSWORD
smtpPort
Port for SMTP. Required in order to send invitation emails.
Env var: SQLPAD_SMTP_PORT
smtpSecure
Toggle to use secure connection when using SMTP.
Env var: SQLPAD_SMTP_SECURE
Default: true
smtpUser
Username for SMTP. Required in order to send invitation emails.
Env var: SQLPAD_SMTP_USER
systemdSocket
Acquire socket from systemd if available
Env var: SQLPAD_SYSTEMD_SOCKET
tableChartLinksRequireAuth
When false, table and chart result links will be operational without login.
Env var: SQLPAD_TABLE_CHART_LINKS_REQUIRE_AUTH
Default: true
timeoutSeconds
HTTP server timeout as number of seconds. Extend as necessary for long running queries.
Env var: SQLPAD_TIMEOUT_SECONDS
Default: 300
whitelistedDomains
Allows pre-approval of email domains. Delimit multiple domains by empty space.
Env var: WHITELISTED_DOMAINS
allowConnectionAccessToEveryone
Allows access on every connection to every user.
Env var: SQLPAD_ALLOW_CONNECTION_ACCESS_TO_EVERYONE
Default: true
queryHistoryRetentionTimeInDays
Query history entries created before the retention period will be deleted automatically.
Env var: SQLPAD_QUERY_HISTORY_RETENTION_PERIOD_IN_DAYS
Default: 30
queryHistoryResultMaxRows
By default query history results are limited to 1,000 records.
Env var: SQLPAD_QUERY_HISTORY_RESULT_MAX_ROWS
Default: 1000
appLogLevel
Minimum level for app logs. Should be one of 'fatal', 'error', 'warn', 'info', 'debug', 'trace' or 'silent'.
Env var: SQLPAD_APP_LOG_LEVEL
Default: info
webLogLevel
Minimum level for web logs. Should be one of 'fatal', 'error', 'warn', 'info', 'debug', 'trace' or 'silent'.
Env var: SQLPAD_WEB_LOG_LEVEL
Default: info
seedDataPath
Path to root of seed data directories. See Seed Data documentation.
Env var: SQLPAD_SEED_DATA_PATH
As of 3.2.0 connections may be defined via application configuration.
Every connection defined should provide a name and driver value, with driver equaling the value in header parentheses below. name will be the label used in the UI to label the connection.
Field names and values are case sensitive.
The connection ID value used can be any alphanumeric value, and is case-sensitive. This can be a randomly generated value like SQLPad's underlying embedded database uses, or it can be a more human-friendly name, or an id used from another source.
How connections are defined in configuration depends on the source of the configuration.
When using environment variables, connection field values must be provided using an environment variable with the convention SQLPAD_CONNECTIONS__<connectionId>__<fieldName>. Note double underscores between SQLPAD_CONNECTIONS, <connectionId>, and <fieldName>. Both connection ID and field name values are case sensitive. Boolean values should be the value true or false.
Example for a MySQL connection with id prod123.
SQLPAD_CONNECTIONS__prod123__name="Production 123"
SQLPAD_CONNECTIONS__prod123__driver=mysql
SQLPAD_CONNECTIONS__prod123__host=localhost
SQLPAD_CONNECTIONS__prod123__mysqlInsecureAuth=trueWhen defining a connection in an INI file, use section header with the value connections.<connectionId>.
[connections.prod123]
name = Production 123
driver = mysql
host = localhost
mysqlInsecureAuth = trueWhen using JSON file, provide <connectionId> as a key under connections.
{
"connections": {
"prod123": {
"name": "Production 123",
"driver": "mysql",
"host": "localhost",
"mysqlInsecureAuth": true
}
}
}| key | description | data type |
|---|---|---|
| name | Name of connection | text |
| driver | Must be crate | text |
| host | Host/Server/IP Address | text |
| port | Port (optional) | text |
| key | description | data type |
|---|---|---|
| name | Name of connection | text |
| driver | Must be drill | text |
| host | Host/Server/IP Address | text |
| port | Port (optional) | text |
| username | Database Username | text |
| password | Database Password | text |
| drillDefaultSchema | Default Schema | text |
| ssl | Use SSL to connect to Drill | boolean |
| key | description | data type |
|---|---|---|
| name | Name of connection | text |
| driver | Must be hdb | text |
| host | Host/Server/IP Address | text |
| hanaport | Port (e.g. 39015) | text |
| username | Database Username | text |
| password | Database Password | text |
| hanadatabase | Tenant | text |
| hanaSchema | Schema (optional) | text |
| key | description | data type |
|---|---|---|
| name | Name of connection | text |
| driver | Must be mysql | text |
| host | Host/Server/IP Address | text |
| port | Port (optional) | text |
| database | Database | text |
| username | Database Username | text |
| password | Database Password | text |
| mysqlInsecureAuth | Use old/insecure pre 4.1 Auth System | boolean |
| key | description | data type |
|---|---|---|
| name | Name of connection | text |
| driver | Must be postgres | text |
| host | Host/Server/IP Address | text |
| port | Port (optional) | text |
| database | Database | text |
| username | Database Username | text |
| password | Database Password | text |
| postgresSsl | Use SSL | boolean |
| postgresCert | Database Certificate Path | text |
| postgresKey | Database Key Path | text |
| postgresCA | Database CA Path | text |
| useSocks | Connect through SOCKS proxy | boolean |
| socksHost | Proxy hostname | text |
| socksPort | Proxy port | text |
| socksUsername | Username for socks proxy | text |
| socksPassword | Password for socks proxy | text |
| key | description | data type |
|---|---|---|
| name | Name of connection | text |
| driver | Must be presto | text |
| host | Host/Server/IP Address | text |
| port | Port (optional) | text |
| username | Database Username | text |
| prestoCatalog | Catalog | text |
| prestoSchema | Schema | text |
| key | description | data type |
|---|---|---|
| name | Name of connection | text |
| driver | Must be sqlserver | text |
| host | Host/Server/IP Address | text |
| port | Port (optional) | text |
| database | Database | text |
| username | Database Username | text |
| password | Database Password | text |
| domain | Domain | text |
| sqlserverEncrypt | Encrypt (necessary for Azure) | boolean |
| sqlserverMultiSubnetFailover | MultiSubnetFailover | boolean |
| readOnlyIntent | ReadOnly Application Intent | boolean |
| key | description | data type |
|---|---|---|
| name | Name of connection | text |
| driver | Must be vertica | text |
| host | Host/Server/IP Address | text |
| port | Port (optional) | text |
| database | Database | text |
| username | Database Username | text |
| password | Database Password | text |
| key | description | data type |
|---|---|---|
| name | Name of connection | text |
| driver | Must be cassandra | text |
| contactPoints | Contact points (comma delimited) | text |
| localDataCenter | Local data center | text |
| keyspace | Keyspace | text |
This feature may continue to change before being finalized. Use at your own risk
Data may be seeded to a SQLPad instance via JSON files on the file system. At this time only query and connection data may be seeded.
Seed data is loaded into SQLPad at server start, with the data being created or updated based on matching an attribute. Previously loaded seed data will be updated each server start, so plan accordingly.
If data is seeded and then removed from the seed data directory, it will not be removed from a SQLPad instance.
This mechanism is intended to be used for "system" data that is not intended to be modified by end users. For example, a "system" user can be created along with a set of canned queries, set to be shared with everyone.
To seed data, specify the directory to load data via config setting seedDataPath. This directory should contain directories for each item type to be loaded, each containing .json files for each item to be loaded.
Example structure:
/path/to/seed-data
/connections
connection-1.json
/queries
some-query.json
another-query.json
Connections may be seeded to SQLPad as an alternative to defining connections via configuration. For fields supported refer to documentation on connection configuration via config file or environment variable. Seed connections differ in that the connection ID is provided by the id field.
Example seed connection JSON file:
{
"id": "seed-connection-1",
"name": "postgres seed connection",
"driver": "postgres",
"host": "pghost",
"database": "pgdatabase",
"username": "pgusername",
"password": "pguserpassword"
}Queries are created or replaced matching on query id. At this time the query ACL implementation controls whether queries may be updated within SQLPad. It is entirely possible for these to be loaded, altered in the UI, then have those changes lost on next server start.
At this point SQLPad does not enforce referential integrity, so queries may be created with a createdBy containing an email address for a user that does not exist.
Example seed query JSON file (comments only added for doc purposes):
{
"id": "seed-query-1",
"name": "Seed query 1",
"connectionId": "seed-connection-1",
"queryText": "SELECT * FROM seed_table",
"createdBy": "admin@sqlpad.com",
"acl": [
// an ACL entry with write=false allows that user to read
// (and execute if they have connection permission)
// write=true allows user to save query
{
"userId": "some-userId-in-sqlpad",
"write": false
},
// ACL entry can also be specified with a users email address.
// The user does not need to exist in SQLPad at this point
{
"userEmail": "someone@sqlpad.com",
"write": true
},
// Alternatively a special __EVERYONE__ group may be used to share the query with all SQLPad users
{
"groupId": "__EVERYONE__",
"write": true
}
]
}SQLPad logs json messages using pino, a simple and fast logging library. Log messages are sent to stdout, leaving how to handle the messages up to you. The pino ecosystem supports a variety of transports http://getpino.io/#/docs/transports that should cover most logging setups.
Two categories of messages are logged by SQLPad: "app" messages containing info messages, warnings, and errors relating to application code, and "web" messages relating to the actual http requests handled by SQLPad. The level at which these logs are logged can be configured separately, or even turned off, with the appLogLevel and webLogLevel settings. This level represents the minimum level to be logged. The value used should be one of fatal, error, warn, info, debug, trace or silent. Setting the level to silent will effectively disable the logger.
In production, log messages will be a single json object per line:
> node-dev server.js --dbPath ../db --port 3010 --debug --baseUrl /sqlpad --config './config.dev.ini'
{"level":30,"time":1581974034149,"pid":7226,"hostname":"hostname.local","name":"sqlpad-app","msg":"Loading users","v":1}
{"level":30,"time":1581974034150,"pid":7226,"hostname":"hostname.local","name":"sqlpad-app","msg":"Loading connections","v":1}
{"level":30,"time":1581974034150,"pid":7226,"hostname":"hostname.local","name":"sqlpad-app","msg":"Loading connectionAccesses","v":1}
{"level":30,"time":1581974034150,"pid":7226,"hostname":"hostname.local","name":"sqlpad-app","msg":"Loading queries","v":1}
{"level":30,"time":1581974034150,"pid":7226,"hostname":"hostname.local","name":"sqlpad-app","msg":"Loading queryHistory","v":1}
{"level":30,"time":1581974034150,"pid":7226,"hostname":"hostname.local","name":"sqlpad-app","msg":"Loading cache","v":1}
{"level":30,"time":1581974035055,"pid":7226,"hostname":"hostname.local","name":"sqlpad-app","msg":"Creating access on every connection to every user...","v":1}
{"level":30,"time":1581974035066,"pid":7226,"hostname":"hostname.local","name":"sqlpad-app","msg":"Welcome to SQLPad! Visit http://localhost:3010/sqlpad to get started","v":1}
{"level":30,"time":1581974050933,"pid":7226,"hostname":"hostname.local","name":"sqlpad-web","req":{"id":2,"method":"GET","url":"/sqlpad/signin"},"res":{"statusCode":200},"responseTime":3,"msg":"request completed","v":1}
{"level":30,"time":1581974050970,"pid":7226,"hostname":"hostname.local","name":"sqlpad-web","req":{"id":3,"method":"GET","url":"/sqlpad/javascripts/vendor/tauCharts/tauCharts.min.css"},"res":{"statusCode":304},"responseTime":2,"msg":"request completed","v":1}
During development, the logs can be piped to pino-pretty, which is used when running npm start by default, producing a more human friendly format:
> node-dev server.js --dbPath ../db --port 3010 --debug --baseUrl /sqlpad --config './config.dev.ini' | pino-pretty
[1581974767500] INFO (sqlpad-app/7387 on hostname.local): Loading users
[1581974767501] INFO (sqlpad-app/7387 on hostname.local): Loading connections
[1581974767501] INFO (sqlpad-app/7387 on hostname.local): Loading connectionAccesses
[1581974767501] INFO (sqlpad-app/7387 on hostname.local): Loading queries
[1581974767501] INFO (sqlpad-app/7387 on hostname.local): Loading queryHistory
[1581974767501] INFO (sqlpad-app/7387 on hostname.local): Loading cache
[1581974768401] INFO (sqlpad-app/7387 on hostname.local): Creating access on every connection to every user...
[1581974768413] INFO (sqlpad-app/7387 on hostname.local): Welcome to SQLPad! Visit http://localhost:3010/sqlpad to get started
[1581974774313] INFO (sqlpad-web/7387 on hostname.local): request completed
req: {
"id": 1,
"method": "GET",
"url": "/sqlpad/signin"
}
res: {
"statusCode": 304
}
responseTime: 8
[1581974774342] INFO (sqlpad-web/7387 on hostname.local): request completed
req: {
"id": 2,
"method": "GET",
"url": "/sqlpad/javascripts/vendor/tauCharts/tauCharts.min.css"
}
res: {
"statusCode": 304
}
responseTime: 3
MIT