Database Query Forwarder

Forwards a SQL query result to a web API endpoint.

Use Case

DQF was created to send data, aggregated in PostgreSQL, to the DHIS2 API for data value sets.

It is generic enough to send data from any database that can return a result as a JSON object (e.g. SQL Server, MySQL, MariaDB, Oracle Database, IBM Db2) to any API endpoint that accepts JSON data and supports basic authentication.

Based on this use case, DQF expects queries to return only one row, and only one field, which contains the payload. It will return an error if a query returns more than one row and more than one field.

Installation

Linux/Mac

1. Install uv.

2. Clone this repository and install dependencies:

   $ git clone https://github.com/dimagi/db-query-fwd.git
   $ cd db-query-fwd/
   $ uv sync --extra postgres

Windows

Download the Windows executable:

1. Open https://github.com/dimagi/db-query-fwd in your browser.
2. Navigate to the "Actions" tab.
3. Open the latest workflow run, or select "Build Windows executable" and click "Run workflow" if no workflow runs are listed.
4. Download the dqf-exe artifact.
5. Unzip dqf-exe.zip and extract dqf.exe.

No Python installation required.

Usage

Linux/Mac

$ dqf \
    --log-level [none,info,debug] \
    --log-file <filename> \
    --config-file <filename> \
    query_name \
    [query_param] [query_param] etc.

The log level given on the command line overrides the value set in the config file. If not specified, the default value is "info".

The log file given on the command line overrides the value set in the config file. If not specified, the default value is "dqf.log".

The config file defaults to "dqf.toml".

Windows

Using the Windows executable, dqf.exe:

> dqf.exe query_name [query_param] [query_param] etc.

Environment Variables

The use of environment variables is optional. The following are available:

DB_FWD_DB_URL stores the URL of the database to query for all queries.

DB_FWD_API_USERNAME stores the username to use for API authentication. Currently basic auth is the only API authentication method that DQF supports.

DB_FWD_API_PASSWORD stores the password to use for API authentication.

Configuration File

DQF looks for a configuration file named "dqf.toml" by default. The configuration file is given in TOML format. This file can contain passwords, and should only be readable by the user that DQF will run as.

The dqf.toml.example file includes detailed examples. It can be copied to dqf.tomland customized.

The following is an example configuration:

[dqf]
log_level = 'info'
log_file = 'dqf.log'
log_db_url = 'postgresql://username:[email protected]:5432/dbname'

[queries]
db_url = 'postgresql://username:[email protected]:5432/dbname'
api_username = '[email protected]'
api_password = 'Password!'

[queries.queryname1]
query = "SELECT json_payload FROM queryname1_view WHERE period = :param1;"
api_url = 'https://dhis2.example.com/api/dataset/abcd1234/'

[queries.queryname2]
query = "SELECT json_payload FROM queryname2_view WHERE category_id = :param1 AND period = :param2;"
api_url = 'https://dhis2.example.com/api/dataset/efgh5678/'

dqf Section

Logging

If not specified, log_level defaults to "info". Valid values are "none", "info" and "debug". The log level applies to both the log file and the log database.

If not specified, log_file defaults to "dqf.log".

log_db_url is optional. It stores a database URL. If it is specified then logs will be stored in the "dqf_logs" table.

queries Section

db_url is the URL of the database to query for all queries. This setting overrides the environment variable "DB_FWD_DB_URL". A query can override this value by specifying db_url in its section.

api_url can be used to specify the URL that all query results are forwarded to. This setting is optional. A query can override this value by specifying api_url in its section.

api_username sets the username, and api_password override the environment variables "DB_FWD_API_USERNAME" and "DB_FWD_API_PASSWORD".

queries.queryname Sections

Each query has a section, where "queryname" is the name of the query as it will be given on the command line.

query is the SQL query that will be executed. It must return a single field. Query parameters should use named placeholders (:param1, :param2, etc.). Parameters passed on the command line are mapped to these placeholders in order (first command line parameter becomes :param1, second becomes :param2, etc.).

api_url is the API endpoint that the value of the field returned by query will be forwarded to. If api_url was given in the queries section then this setting is optional. If both are given then this value overrides the value given in the queries section.

api_username and api_password can optionally be set here to override the values given in the queries section.