Add full support for config based on environment variables

[hhromic]

Is your feature request related to a problem? Please describe.

We use the exporter inside a Docker container, where we dynamically generate a configuration file in an entrypoint shell script on start based on a set of environment variables passed to the container to pre-configure the SQL exporter.

The entrypoint script we made needed to address the following requirements:

• Be able to configure database aspects from separate environment variables (host, port, dbname, etc)
• Be able to read database secrets from an env variable or a file (i.e. to use container secrets feature)
• Allow to configure any global configuration of the exporter without rebuilding the image
• Be able to configure the exporter only from mounted collector files, and not need to mount a static global config.

We are aware that at the moment there is partial support for env variables in the exporter, namely:

• SQLEXPORTER_DEBUG
• SQLEXPORTER_CONFIG
• SQLEXPORTER_TARGET_DSN

But we would like to be able to override any configuration in the config files, not just target.dsn.

Describe the solution you'd like

We would like for the SQL exporter to be able to be fully configured from environment variables, therefore greatly increasing usability and user experience when running in containers (not only Docker) because we can fully avoid writing entrypoint scripts.

If you are open to this feature request, I'm happy to implement it and send a PR for reviewing.

Describe alternatives you've considered

To fulfil the requirements, our current entrypoint script is as follows:

#!/usr/bin/env sh

die() { printf "fatal: %s\n" "$*" > /dev/stderr; exit 1; }
uri() { printf "%s" "$(jq -nr --arg v "$*" '$v | @uri')"; }

# define working directories and files
SHM_DIR=/dev/shm
CONFIG_FILE=$SHM_DIR/sql-exporter.yaml
COLLECTORS_DIR=  # use root directory of container

# check required environment variables
[ -z "$DB_HOST" ] && die "missing required environment variable: DB_HOST"
[ -z "$DB_USER" ] && die "missing required environment variable: DB_USER"
if [ -z "$DB_PASSWORD" ] && [ -z "$DB_PASSWORD_FILE" ]; then
  die "missing required environment variable: one of DB_PASSWORD or DB_PASSWORD_FILE"
fi
[ -z "$DB_DATABASE" ] && die "missing required environment variable: DB_DATABASE"

# prepare optional environment variables
SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT=${SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT:-10s}
SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT_OFFSET=${SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT_OFFSET:-500ms}
SQLEXPORTER_GLOBAL_MIN_INTERVAL=${SQLEXPORTER_GLOBAL_MIN_INTERVAL:-0s}
SQLEXPORTER_GLOBAL_MAX_CONNECTIONS=${SQLEXPORTER_GLOBAL_MAX_CONNECTIONS:-3}
SQLEXPORTER_GLOBAL_MAX_IDLE_CONNECTIONS=${SQLEXPORTER_GLOBAL_MAX_IDLE_CONNECTIONS:-3}
DB_PORT=${DB_PORT:-5433}

# read password from file if provided
[ -n "$DB_PASSWORD_FILE" ] && DB_PASSWORD=$(cat "$DB_PASSWORD_FILE")

# collect the names of all available collectors
COLLECTORS=
for COLL_FILE in "$COLLECTORS_DIR"/*.collector.yaml; do
  [ -e "$COLL_FILE" ] || [ -L "$COLL_FILE" ] || continue
  COLLECTOR=$(sed -rn '/^collector_name: / s/collector_name: (.+)/'"'"'\1'"'"'/g p' "$COLL_FILE")
  if [ -z "$COLLECTORS" ]; then
    COLLECTORS=$COLLECTOR
  else
    COLLECTORS="$COLLECTORS, $COLLECTOR"
  fi
done

# build the data source name (DSN) to use
DSN=driver://$(uri "$DB_USER"):$(uri "$DB_PASSWORD")
DSN=${DSN}@${DB_HOST}:${DB_PORT}/${DB_DATABASE}

# generate configuration file for the SQL exporter
cat > "$CONFIG_FILE" << __EOF__
global:
  scrape_timeout: $SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT
  scrape_timeout_offset: $SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT_OFFSET
  min_interval: $SQLEXPORTER_GLOBAL_MIN_INTERVAL
  max_connections: $SQLEXPORTER_GLOBAL_MAX_CONNECTIONS
  max_idle_connections: $SQLEXPORTER_GLOBAL_MAX_IDLE_CONNECTIONS
target:
  data_source_name: '$DSN'
  collectors: [$COLLECTORS]
collector_files: ['$COLLECTORS_DIR/*.collector.yaml']
__EOF__

# execute SQL exporter with the generated configuration file
exec sql_exporter --config.file="$CONFIG_FILE"

As it can be seen, the above script is quite a lot of boilerplate that could be instead managed natively in the SQL exporter if it had full support for environment variables, especially the global config.

[hhromic]

Support for separate target DB config variables is not strictly necessary and can be left with the current SQLEXPORTER_TARGET_DSN of course. That would still greatly simplify our boilerplate entrypoint script regardless.

Anyway let me know if you would still be interested in that part, especially for the "password from file" feature.

[burningalchemist]

Hey @hhromic, yeah, it makes total sense. 👍

Currently, I have several thoughts in mind:

• to make environment variables automatically generated from the yaml config (similar to how they do it in Grafana for example, and having the same format you've shared above)
• probably limit to global for the time being and maybe to target as maximum
• avoid introducing fat dependencies, minimal and small are good, though.

Also, the password from file might be a neat feature, let's give it a try. 👍 The problem we have is that some drivers (e.g. libpq for PostgreSQL) have builtin environment variables that override connection parameters on a driver level (like PGUSER, PGPASSWORD, etc), while the others don't. It'd be great to have it consistently implemented on a driver side, but probably won't happen. 😞

But it was one of the reasons I decided to migrate to dburl package to achieve some consistency at least there. In this case, we can parse the DSN and apply/modify username, password, etc via envs.

[hhromic]

Hi @burningalchemist !
I got a bit of time now to design/plan the implementation of this feature.

Apologies in advance for the long post! Take your time to read it and to think about it.

Let me know if you are okay with the proposal below and if you have further input/feedback before implementation.

First some comments to your thoughts mentioned above:

• to make environment variables automatically generated from the yaml config (similar to how they do it in Grafana for example, and having the same format you've shared above)

I'm very familiar with Grafana's approach to this as we use Grafana fully with env variables :)
Grafana was indeed the inspiration for the env variable formatting I proposed.
While the env variable system of Grafana is quite fancy and powerful, I think something like that for the SQL exporter would be overkill. Grafana has a way bigger set of configurations and therefore understandable why they went that way.

For the SQL exporter I would go with a simple static set of recognised env variables.

• probably limit to global for the time being and maybe to target as maximum

As shown in the initial issue, yes I was thinking on global, target and collector_files to be configurable (see below).

• avoid introducing fat dependencies, minimal and small are good, though.

I'm planning for the implementation to use standard Go only, no libraries for now. 👍

The problem we have is that some drivers (e.g. libpq for PostgreSQL) have builtin environment variables that override connection parameters on a driver level (like PGUSER, PGPASSWORD, etc), while the others don't. It'd be great to have it consistently implemented on a driver side, but probably won't happen. 😞

I don't think we should worry about that. The scope of the SQL exporter env variables should be only to prepare the DSN to send to dburl.Parse(). After that, any other variables that each driver supports independently are responsibility of the drivers themselves and the end-user is free to mix'n'match if they know what they are doing.
I think the most important thing is to document the behaviour clearly so users know exactly how it works.
And yes, I also agree drivers will never be consistent. It hasn't been consistent for decades 😅.

But it was one of the reasons I decided to migrate to dburl package to achieve some consistency at least there. In this case, we can parse the DSN and apply/modify username, password, etc via envs.

Parsing the DSN and then overriding components looks a bit tricky to implement from what I have seen.
I propose to instead optionally build a DSN on the fly from separate env variables (see below).

Proposed Set of Recognised Variables

I propose to recognise the following env variables for the SQL exporter itself.
All variables are optional, but using some variables override others. See below.

Some variables populate array configs. For these, I propose to separate elements using commas (,).
This, of course, limits each element to not be able to use commas in them, but I think is a reasonable separator character to use for typical configurations done using environment variables.

• Variables for the global top-level configuration
  • SQLEXPORTER_GLOBAL_MIN_INTERVAL
  • SQLEXPORTER_GLOBAL_MAX_CONNECTION_LIFETIME
  • SQLEXPORTER_GLOBAL_MAX_CONNECTIONS
  • SQLEXPORTER_GLOBAL_MAX_IDLE_CONNECTIONS
  • SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT
  • SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT_OFFSET
• Variables for the target top-level configuration
  • SQLEXPORTER_TARGET_DSN (see below)
  • SQLEXPORTER_TARGET_COLLECTORS (comma-separated elements)
• Variables for the collector_files top-level configuration
  • SQLEXPORTER_COLLECTOR_FILES (comma-separated elements)

Due to complexity, no support for the collectors and jobs top-level configs is proposed at this time.
I don't think support for these is really necessary as more complex configurations should not use env variables anyway.

Proposed Target DSN Variables

The user can provide SQLEXPORTER_TARGET_DSN (as currently is possible), but alternatively can provide the components of the DSN in separate env variables to build a DSN on the fly instead. This allows for (1) more readable configurations and (2) the ability to read password secrets from files if required.

The proposed variables follow the URL format for dburl:

• SQLEXPORTER_TARGET_DSN_OPAQUE (optional, boolean to indicate scheme:// or scheme: -opaque-, default false)
• SQLEXPORTER_TARGET_DSN_PROTOCOL (required, also known as "driver" or "scheme")
• SQLEXPORTER_TARGET_DSN_TRANSPORT (optional, default to empty)
• SQLEXPORTER_TARGET_DSN_USER (optional, default to empty)
• SQLEXPORTER_TARGET_DSN_PASSWORD (optional and only used if the above is provided)
• SQLEXPORTER_TARGET_DSN_PASSWORD_FILE (optional and overrides the above if provided)
• SQLEXPORTER_TARGET_DSN_HOST (required, can optionally contain a port number)
• SQLEXPORTER_TARGET_DSN_DBNAME (optional)
• SQLEXPORTER_TARGET_DSN_OPTIONS (optional comma-separated k=v elements for the DB driver, default to empty)

If none of the "required" variables above is provided, then SQLEXPORTER_TARGET_DSN is used as before.

Full Example

With a single DSN variable:

SQLEXPORTER_GLOBAL_MIN_INTERVAL=0s
SQLEXPORTER_GLOBAL_MAX_CONNECTION_LIFETIME=0s
SQLEXPORTER_GLOBAL_MAX_CONNECTIONS=3
SQLEXPORTER_GLOBAL_MAX_IDLE_CONNECTIONS=3
SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT=10s
SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT_OFFSET=500ms
SQLEXPORTER_TARGET_DSN=sqlserver://prom_user:prom_password@dbserver1.example.com:1433
SQLEXPORTER_TARGET_COLLECTORS=prices_*,users
SQLEXPORTER_COLLECTOR_FILES=/path/to/colectors/*.collector.yaml

With separate DSN variables:

SQLEXPORTER_GLOBAL_MIN_INTERVAL=0s
SQLEXPORTER_GLOBAL_MAX_CONNECTION_LIFETIME=0s
SQLEXPORTER_GLOBAL_MAX_CONNECTIONS=3
SQLEXPORTER_GLOBAL_MAX_IDLE_CONNECTIONS=3
SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT=10s
SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT_OFFSET=500ms
SQLEXPORTER_TARGET_DSN_PROTOCOL=sqlserver
SQLEXPORTER_TARGET_DSN_USER=prom_user
SQLEXPORTER_TARGET_DSN_PASSWORD_FILE=/run/secrets/db-password
SQLEXPORTER_TARGET_DSN_HOST=dbserver1.example.com:1433
SQLEXPORTER_TARGET_COLLECTORS='prices_*,users'
SQLEXPORTER_COLLECTOR_FILES='/path/to/colectors/*.collector.yaml,/more-collectors/*.collector.yaml'

Another example with separate DSN variables:

SQLEXPORTER_TARGET_DSN_PROTOCOL=sqlserver
SQLEXPORTER_TARGET_DSN_USER=prom_user
SQLEXPORTER_TARGET_DSN_PASSWORD_FILE=/run/secrets/db-password
SQLEXPORTER_TARGET_DSN_HOST=dbserver1.example.com:1433
SQLEXPORTER_TARGET_DSN_DBNAME=mydb
SQLEXPORTER_TARGET_DSN_OPTIONS='encrypt=true,keepAlive=15'

[burningalchemist]

Hey @hhromic, thanks for such a descriptive proposal! 👍

While the env variable system of Grafana is quite fancy and powerful, I think something like that for the SQL exporter would be overkill. Grafana has a way bigger set of configurations and therefore understandable why they went that way.

Yeah, agreed. My thought was that once we introduce such a way to generate environment variables, we don't need to think about that anymore. I also agree that the number of potential parameters in sql_exporter is way smaller, and it's finite. I would even say, we want to be small and finite.

I'm planning for the implementation to use standard Go only, no libraries for now. 👍

Yeah, in the case we go simple I also think there is nothing to worry about.

Overall, I believe we're on the same page and your design looks great. 👍🚀

[hhromic]

Hey @burningalchemist !
I've not forgotten about this feature request and my promise to implement it. I just got a bit stuck with other things!
As soon as I get the time, I'll dive into this. 👍

[burningalchemist]

Hey @hhromic, sure thing, no worries. 🙂👍

[abogdanov37]

Hi folks!
May be our solution helps some one.
A week ago we with sql-exporter. It a great tool. But it can't configure with enviranment variables. ((( In our case we use jobs to aggregate access to several data bases (near 100 db)
Soluton
We add all env variables which we need in configs
Then when pods run first we start initial container with tool envsubst which replace all env variables by value
That's all
When we look at the config in UI we saw

[burningalchemist]

Hey @abogdanov37, thanks for your suggestion! 👍 Happy to hear you're using sql_exporter against ~100 db. 😮

The goal here is to make such a configuration supported natively, so you don't even need to run envsubst. Until then, though, we have populate the config aside.

[abogdanov37]

Hey @abogdanov37, thanks for your suggestion! 👍 Happy to hear you're using sql_exporter against ~100 db. 😮

The goal here is to make such a configuration supported natively, so you don't even need to run envsubst. Until then, though, we have populate the config aside.

Hi! Yep. I understand the idea in that issue. We also wait implementation. I share our solution may be someone need use env just now.
And simple comment in discussion I don't see env variables support for jobs. It's critical for us.