Database Aggregator

database_aggregator is a real transformer plugin. Niamoto ships it in src/niamoto/core/plugins/transformers/aggregation/database_aggregator.py.

What It Does

The plugin executes read-only SQL queries against the project database and returns structured results that other widgets or exporters can consume.

It supports:

  • direct SQL queries

  • reusable SQL templates

  • computed fields derived from query results

  • basic database validation before execution

Config Shape

The plugin expects a normal transformer entry:

site_stats:
  plugin: database_aggregator
  params:
    queries:
      species_count:
        sql: "SELECT COUNT(*) FROM taxon_ref WHERE rank_name = 'species'"
      occurrence_count:
        sql: "SELECT COUNT(*) FROM occurrences"
    computed_fields:
      occurrences_per_species:
        expression: "round(occurrence_count / species_count, 1) if species_count else 0"
        dependencies: [occurrence_count, species_count]

Paste that block under a widgets_data: section in transform.yml.

Output Formats

Each query can return one of four formats:

Format

Result

scalar

single value

table

list of dictionaries

series

single-column list

single_row

one dictionary

The plugin also adds _metadata with computed_at, plugin, and query counts.

Safety

The plugin only accepts SELECT statements. It rejects patterns such as:

  • DROP

  • DELETE

  • INSERT

  • UPDATE

  • SQL comments

  • EXEC

Current Limitation

database_aggregator does not inject the current group_id into your SQL. If you attach it to a transform group with many items and your query does not depend on that group, Niamoto will recompute the same result for every item in that group.

That means the plugin works best when you:

  • need advanced SQL and accept repeated execution

  • use a workflow that materializes one canonical result row

  • keep the query cost low enough for repeated runs

Do not assume that group_by: global has special built-in behavior. The current transform runtime does not reserve that group name.

When To Use It

Use database_aggregator when the standard transformers cannot express the query you need, especially for:

  • multi-table joins

  • SQL aggregations with custom grouping

  • summary tables for dashboards

  • derived metrics that combine several SQL results

If a regular transformer can compute the same result with structured inputs, use that transformer first.