Add full support for config based on environment variables about sql_exporter

[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.

[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. 👍🚀

[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

[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.

[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.

[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:[email protected]: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'

[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. 🙂👍

[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.