Connections

A Connection in SQLPad is a configuration to a specific database instance. Business Intelligence and reporting software may call these “data sources”. A connection may involve a connection string, user credentials, host, port, etc. The data required by a connection depends on the database driver it uses to connect to the target database.

When a user write’s a query, they’ll pick a connection to use to run it. This connection choice will also be saved with the query.

Admins can create connections in the UI, but connections can also be created via environment variables or experimental seed data files.

Multi-Statement Transaction Support

Important: When using Multi-Statement transactions SQLPad becomes a stateful service. HTTP API calls must consistently resolve to same SQLPad instance using sticky sessions or similar.

Multi-statement transaction support adds the ability for a user to use the same underlying connection across query executions. This allows things like opening a transaction, running queries, and rolling the transaction back or committing the transaction across query runs. It also opens up the ability to create and use temp tables that are generally scoped per connection session.

Multi-statement transaction support is opt-in based on connection configuration. If a connection uses a driver and multi-statement transaction support is not enabled, the connection falls back to the legacy SQLPad behavior of opening a new connection for each query execution, then immediately closing it following the query.

Work is under way to add multi-statement transaction support to drivers that benefit from the addition. At this time MySQL, SQLite, Postgres, and ODBC drivers support this approach.

Defining Connections via Configuration

When defining connections via 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>.

Fields and values are case sensitive.

The connection ID value used can be any alphanumeric value. 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.

The fieldName referenced in the environment variable should correspond with a field key noted in the table below for the driver used.

Boolean values should be the value true or false.

Every connection defined should provide a name and driver value, with driver equaling the value specified in the driver rows below. name will be the label used in the UI to label the connection.

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

Connection selection

A default connection selection can be set using environment variable SQLPAD_DEFAULT_CONNECTION_ID. It can also be specified as part of the query editor URL, i.e., https://mysqlpad.example.com/queries/new?connectionName=connection1 or https://mysqlpad.example.com/queries/new?connectionId=xxx-xxxxx-xxx-xxx.

CrateDB

Apache Drill

Apache Pinot

ClickHouse

SAP Hana (hdb)

MySQL

MySQL2

PostgreSQL (postgres)

PrestoDB

Redshift

Redshift uses the Postgres driver, using a different query for pulling schema.

SQL Server

Trino

Vertica

Cassandra

BigQuery

SQLite

Athena

ODBC (unixodbc)

?> Despite the underlying driver being unixodbc, this appears to be functional on Windows.

The ODBC driver by default use the following SQL to try and obtain schema information for the database connected. This may not work for your target database however.

If information schema is not supported by your target database, you may override this query using the schema_sql key.

This query is used for the schema sidebar and autocomplete purposes, and is not required to be able to run queries against the target database.

SELECT
  c.table_schema AS table_schema,
  c.table_name AS table_name,
  c.column_name AS column_name,
  c.data_type AS data_type
FROM
  INFORMATION_SCHEMA.columns c
WHERE
  c.table_schema NOT IN ('INFORMATION_SCHEMA', 'information_schema')
ORDER BY
  c.table_schema,
  c.table_name,
  c.ordinal_position