Build a Data Analytics API with DuckDB

DuckDB is a fast, in-process SQL database that can query CSV, Parquet and JSON files directly no data loading required. Combined with RapidForge, you can create powerful analytics endpoints in minutes.

Why DuckDB + RapidForge?

• Zero setup: DuckDB is a single binary, just like RapidForge
• Query any file: CSV, Parquet, JSON, even remote files over HTTP
• SQL power: Full-featured SQL with aggregations, joins, and window functions
• No database server: Runs in-process, perfect for air-gapped environments

Use Case 1: Dynamic CSV Analytics Endpoint

Create a webhook that queries a CSV file with dynamic filters using URL parameters. RapidForge automatically injects URL parameters as environment variables ($URL_PARAM_<name>).

Create a new block with a GET endpoint called sales-report:

#!/bin/bash
# URL: /webhooks/sales-report?category=Electronics&limit=10
# RapidForge injects: $URL_PARAM_CATEGORY, $URL_PARAM_LIMIT

CATEGORY=${URL_PARAM_CATEGORY:-"all"}
LIMIT=${URL_PARAM_LIMIT:-10}

if [ "$CATEGORY" = "all" ]; then
    FILTER=""
else
    FILTER="WHERE category = '${CATEGORY}'"
fi

duckdb -json -c "
SELECT
    product_name,
    category,
    SUM(quantity) as total_sold,
    SUM(price * quantity) as revenue
FROM 'data/sales.csv'
${FILTER}
GROUP BY product_name, category
ORDER BY revenue DESC
LIMIT ${LIMIT}
"

Now users can call:

• /webhooks/sales-report - returns top 10 products across all categories
• /webhooks/sales-report?category=Electronics - filter by category
• /webhooks/sales-report?category=Electronics&limit=5 - filter and limit results

Use Case 2: Scheduled Data Report

Create a periodic job that runs daily to generate a summary report and save it:

#!/bin/bash
# Daily sales summary - runs as a cron job
REPORT_DATE=$(date +%Y-%m-%d)

duckdb -c "
COPY (
    SELECT
        DATE_TRUNC('day', order_date) as date,
        COUNT(*) as orders,
        SUM(total) as revenue
    FROM 'data/orders.csv'
    WHERE order_date >= CURRENT_DATE - INTERVAL 7 DAY
    GROUP BY ALL
    ORDER BY date
) TO 'reports/weekly-summary-${REPORT_DATE}.csv' (HEADER, DELIMITER ',')
"

echo "Report generated: weekly-summary-${REPORT_DATE}.csv"

Use Case 3: Customer Dashboard with Drag-and-Drop Editor

Build a complete analytics dashboard by combining DuckDB queries with RapidForge's drag-and-drop page editor.

Step 1: Create a GET endpoint called customer-stats that joins multiple data files:

#!/bin/bash
# Join customers CSV with orders Parquet for a complete view
# URL: /webhooks/customer-stats?min_value=1000

MIN_VALUE=${URL_PARAM_MIN_VALUE:-0}

duckdb -json -c "
SELECT
    c.name,
    c.email,
    c.signup_date,
    COUNT(o.order_id) as total_orders,
    SUM(o.amount) as lifetime_value
FROM 'data/customers.csv' c
JOIN 'data/orders.parquet' o ON c.id = o.customer_id
GROUP BY c.name, c.email, c.signup_date
HAVING lifetime_value > ${MIN_VALUE}
ORDER BY lifetime_value DESC
"

Step 2: Create a page using the drag-and-drop editor:

1. Go to Pages → Create New Page
2. Drag a Table component onto the canvas
3. In the JS tab, write plain JavaScript using fetch to hit the endpoint and populate the table:

// Fetch customer stats and populate the table
async function loadCustomerData(minValue = 0) {
  const response = await fetch(`/webhooks/customer-stats?min_value=${minValue}`);
  const data = await response.json();

  const table = document.getElementById('customer-table');
  const tbody = table.querySelector('tbody');
  tbody.innerHTML = '';

  data.forEach(row => {
    const tr = document.createElement('tr');
    tr.innerHTML = `
      <td>${row.name}</td>
      <td>${row.email}</td>
      <td>${row.signup_date}</td>
      <td>${row.total_orders}</td>
      <td>$${row.lifetime_value.toLocaleString()}</td>
    `;
    tbody.appendChild(tr);
  });
}

// Load data on page load
loadCustomerData();

// Refresh button handler
document.getElementById('refresh-btn').addEventListener('click', () => {
  const minValue = document.getElementById('min-value-input').value || 0;
  loadCustomerData(minValue);
});

4. Add a Text Input component with id min-value-input for filtering by minimum value
5. Add a Button component with id refresh-btn to refresh the data

The page editor lets you build interactive dashboards without writing frontend code—just connect your DuckDB-powered endpoints to UI components.

Tips

• Use -json flag to output JSON (perfect for API responses and connecting to the page editor)
• Use -csv flag for CSV output
• DuckDB can query S3 files directly with the httpfs extension
• Combine with RapidForge's credential management to securely store database connection strings
• URL parameters are always available as $URL_PARAM_<NAME> in uppercase

DuckDB's simplicity pairs perfectly with RapidForge—both are single binaries with zero dependencies, making them ideal for quick deployments and air-gapped environments.