Convert API Data to SQLite: Using surveilr and Singer Taps for Cross-Platform Analysis

Turn Any API Into a SQL Database

surveilr is a tool that transforms API data from over 600 sources into standard SQLite tables. It utilizes the Singer protocol to ingest JSONL output and automatically infer SQL schemas.

Why This Matters

Traditional API integration requires writing custom scripts for every platform, managing disparate authentication methods, and wrestling with rate limits. This fragmented approach makes cross-platform analysis—such as joining GitHub commits with Jira tickets—nearly impossible without significant manual data wrangling in pandas or CSV exports. By centralizing this data into a single SQLite database, engineers can perform complex relational joins locally without repeated API calls.

Key Insights

• Singer Protocol Integration: Uses Python scripts (taps) that output JSONL (JSON Lines) containing SCHEMA, RECORD, and STATE messages to track incremental progress.
• Schema Inference: surveilr automatically creates SQL tables based on the Singer output, removing the need for manual DDL/schema definitions.
• Cross-Platform Joins: Enables relational queries across disparate services, such as matching Salesforce opportunities to Stripe payments via customer IDs.
• Local Persistence: Data is stored in a standard SQLite database (.db), allowing compatibility with tools like Datasette, Metabase, and DuckDB.

Working Examples

Quickstart workflow for installing surveilr, ingesting a Singer tap, and querying the resulting database.

# Install surveilr
brew tap surveilr/tap && brew install surveilr
# Initialize database
surveilr admin init -d project.db
# Ingest Singer tap script
surveilr ingest files -r ./github.surveilr[singer].py -d project.db
# Transform to SQL views
surveilr orchestrate adapt-singer -d project.db --stream-prefix github_
# Query data
surveilr shell -d project.db

Cross-platform join example linking Jira issues to GitHub commits based on ticket keys in commit messages.

SELECT j.key AS jira_ticket, j.summary, c.commit_sha, c.message, c.timestamp
FROM jira_issues j
JOIN github_commits c
ON c.message LIKE '%' || j.key || '%'
WHERE j.status = 'Done'
ORDER BY c.timestamp DESC;

Practical Applications

References:

• From internal analysis