DungBeetle Documentation

DungBeetle is a lightweight, single-binary distributed job server designed for queuing and asynchronously executing large numbers of SQL read jobs (such as reports) against SQL databases.

When jobs are executed, the results are written to separate ephemeral results databases where each job's results are stored in its own dedicated table. This architecture enables faster retrieval and prevents heavy report generation from overloading primary source databases.

Tasks are defined in .sql files. Each task must have a -- name: <name> header. You can use special tags to configure task behavior:

• -- db: <db_names>: Specifies which databases to use. If multiple are listed, the task runs against a random one unless a specific db is provided in the API request.
• -- queue: <queue_name>: Routes the task to a specific queue.
• -- results: <results_db_name>: Specifies the results backend.
• -- raw: 1: Prevents the server from preparing the statement (useful for complex queries).

Placeholders: Use ? for MySQL or $1, $2 ... for PostgreSQL.

-- name: get_profit_summary
SELECT SUM(amount) AS total, entry_date FROM entries WHERE user_id = ? GROUP BY entry_date;

-- name: get_profit_entries_by_date
-- raw: 1
SELECT * FROM entries WHERE user_id = ? AND timestamp > ? and timestamp < ?;

To start the server, provide the path to your configuration file and the directory containing your .sql task files.

By default, this starts a set of workers listening on a default queue and an HTTP service on http://127.0.0.1:6060. To run as a worker only (without the HTTP control interface), use the --worker-only flag.

dungbeetle --config /path/to/config.toml --sql-directory /path/to/your/sql/queries

All successful and error responses from the Dungbeetle HTTP server follow a standard JSON envelope structure defined by models.HTTPResp.

Success Response:

{
  "status": "success",
  "data": <response_data>
}

Error Response:

{
  "status": "error",
  "message": "error message details"
}

If you are setting up DungBeetle for the first time, you can generate a template configuration file using the --new-config flag. This will create a config.toml file based on the internal config.sample.toml.

Note: If a config.toml already exists in the current directory, the command will fail to prevent overwriting your existing configuration. You must remove the existing file before regenerating.

Poll the /jobs/{jobID} endpoint to check if a job is finished and to retrieve its results.

In the response, the Results field indicates the number of rows generated by the query.

$ curl localhost:6060/jobs/myjob
{"status":"success","data":{"job_id":"myjob","status":"SUCCESS","results":[{"Type":"int64","Value":2}],"error":""}}

Send a POST request to /tasks/{taskName}/jobs with a JSON body to queue a task.

Job Parameters:

• job_id (string, optional): Alphanumeric ID. If omitted, one is generated. Non-unique IDs allow preventing duplicate concurrent requests for the same report.
• queue (string, optional): Specific queue to route the job to.
• eta (string, optional): Start timestamp (yyyy-mm-dd hh:mm:ss).
• retries (int, optional): Number of retries on failure (default 0).
• args[] (array of strings, optional): Positional arguments for the SQL query.

$ curl localhost:6060/tasks/get_profit_entries_by_date/jobs -H "Content-Type: application/json" -X POST --data '{"job_id": "myjob", "args": ["USER1", "2017-12-01", "2017-01-01"]}'

Use the /groups endpoint to schedule multiple jobs that run concurrently. You can poll the group status to determine when all jobs in the group have finished.

Group Parameters:

• group_id (string, optional): Alphanumeric ID for the group.
• concurrency (int, optional): Number of jobs to run concurrently in the group.
• jobs (array, required): A list of job objects, each containing task, job_id, and args.

$ curl localhost:6060/groups -H "Content-Type: application/json" -X POST --data '{"group_id": "mygroup", "concurrency": 3, "jobs": [{"job_id": "myjob", "task": "get_profit_entries_by_date", "args": ["USER1", "2017-12-01", "2017-01-01"]}, {"job_id": "myjob2", "task": "get_profit_entries_by_date", "args": ["USER1", "2017-12-01", "2017-01-01"]}]'