Evidence + Steampipe = 🚀

This repository contains an example of configuring a Postgres database and a Steampipe database using postgres_fwd - then exploring it using Evidence.

Evidence is a Business Intelligence as Code framework, using markdown + sql to make it easy to build rich reports and analyses.

Steampipe is a postgres-based tool that connects to external APIs and lets you query them with SQL, creating an ergonomic method of getting data from over 100 popular APIs.

Project Setup

Prerequisites

Ensure docker and docker-compose are installed
Ensure node is installed (min version v16)
1. If you are using linux, I recommend using nvm to install node
The scripts in this project make a few assumptions
1. They assume they are being run on Linux
  1. This may work on MacOS, but you may need to execute them differently e.g. instead of ./scripts/x.sh, use zsh ./scripts/x.sh
  2. This may work on Windows, if run within WSL2
2. bash, sudo, unzip, wget, and psql are installed

Bootstrapping the databases

Run ./scripts/prepare.sh
1. Creates ./postgres directory
2. Creates ./steampipe directory
  1. Changes ownership of ./steampipe to userid 9193
  2. This step asks for sudo premissions, it is the only sudo step in the project. Steampipe will not start without this step.
Copy steampipe.env.example -> steampipe.env
1. Add your Github Personal Access Token
Start the databases with docker compose up
1. Wait for both steampipe and postgres to become available
  1. Postgres will log: database system is ready to accept connections
  2. Steampipe will log: Steampipe service is running
Run ./scripts/add-remote-schemas.sh
1. Installs the github plugin in steampipe
2. Executes ./scripts/sql/remote-schema.sql
  1. Enables postgres_fdw
  2. Sets up the connection to steampipe
  3. Imports the github schema
3. Executes ./scripts/sql/test-query.sql
  1. Verifies that the evidence-dev organization can be selected from postgres (not steampipe)

Run the evidence project

Run npm install
Run npm run dev
Open http://localhost:3000
Configure the Database
1. You can use the connection string postgres://postgres:postgres@localhost:5432/postgres
???
Profit

So what?

Using Steampipe with postgres_fdw provides a pretty straightforward way to immediately enrich a Postgres database with a wide variety of external APIs, without the need for ETL software.

Evidence plays nicely with API rate limits, data is only pulled from the database when the project is built. This makes it a great choice for reports where you may join to an API, which could result in many API calls; or reports that may see a lot of traffic. In the (near) future, Evidence will support multiple data sources (and a SQL runtime to query across them), so postgres_fdw will no longer be required, and the same fundamental approach will work with any Evidence supported data source.