Protobi

← All help posts

View post

# Protobi Database API - User Guide

## What is the Protobi Database API?

The Protobi Database API allows you to connect business intelligence tools like Tableau, Looker, PowerBI, and other SQL-based analytics platforms directly to your Protobi projects using standard database connections.  Your BI tools can query Protobi data as if it were a PostgreSQL database, accessing all of Protobi's calculated fields, cross-tabulations, and statistical computations through standard SQL queries.

## Why Use This?

**Keep Your Existing Dashboards** - You don't need to rebuild dashboards or force everyone to learn a new tool. Continue using Tableau for executive dashboards while accessing Protobi's advanced survey analysis and weighting capabilities.

**Access Computed Data** - Query weighted data, recoded variables, computed metrics, and cross-tabulations directly from your BI tools without replicating complex calculations.

**Standard SQL Interface** - Use familiar SQL syntax (`SELECT`, `WHERE`, `GROUP BY`, `LIMIT`) to query your survey data.

## How to Connect

### Connection Details

Connect using any PostgreSQL-compatible client (Tableau, psql, TablePlus, DBeaver, etc.):

- **Host:** `localhost` (or your Protobi server hostname)
- **Port:** `5432`
- **Database:** Your Protobi project ID (e.g., `6744814e48513c0002f3d9d3`)
- **Username:** Your Protobi account email
- **Password:** Your Protobi API key
- **SSL:** Required

### Getting Your API Key

1. Log into Protobi
2. Go to Account Settings
3. Find or generate your API key
4. Copy and use as the password when connecting

### Example: Connecting with psql

```bash
psql "host=localhost port=5432 dbname=YOUR_PROJECT_ID user=you@example.com sslmode=require"
# Enter your API key when prompted for password
```

### Example: Connecting with Tableau

1. Choose "PostgreSQL" as the data source
2. Server: `localhost`, Port: `5432`
3. Database: Your project ID
4. Username: Your email
5. Password: Your API key
6. Check "Require SSL"

## What Can You Query?

### Elements as Tables

Each Protobi element (question, variable, computed field) appears as a table. Query any element by its key:

```sql
SELECT * FROM Q1;
SELECT * FROM region;
SELECT * FROM satisfaction_score;
```

### What You Get Back

By default, queries return **aggregated distributions** - cross-tabulations showing counts and statistics:

```sql
SELECT * FROM Q10a_cloud;
```

Returns a frequency distribution showing how many respondents selected each answer choice.

## Supported SQL

### Basic Query

```sql
SELECT * FROM element_key;
```

Returns the distribution for that element.

### Filter with WHERE

```sql
SELECT * FROM Q10a_cloud WHERE region IN (1, 3);
SELECT * FROM satisfaction WHERE score >= 4;
SELECT * FROM Q5 WHERE quarter = 'Q2' AND region = 'West';
```

Filters apply to the base respondent data before aggregation.

### Cross-Tabulate with GROUP BY

```sql
SELECT * FROM Q10a_cloud GROUP BY region;
SELECT * FROM satisfaction GROUP BY region, quarter;
```

Creates cross-tabs (banners) showing the element distribution broken out by the specified dimensions.

### Limit Results

```sql
SELECT * FROM Q10a_cloud LIMIT 10;
SELECT * FROM Q5 WHERE region IN (1,3) GROUP BY quarter LIMIT 20;
```

Returns only the specified number of rows.

### Combine Them All

```sql
SELECT * FROM satisfaction
WHERE region IN ('East', 'West') AND quarter = 'Q2'
GROUP BY region, age_group
LIMIT 50;
```

Filters respondents from East/West regions in Q2, cross-tabulates by region and age group, returns first 50 rows.

## What's Supported Now

✅ **SELECT * FROM element_key** - Query any Protobi element  
✅ **WHERE clauses** - Filter with `IN`, `=`, `>=`, `<=`, `>`, `<`, `!=`, and `AND`  
✅ **GROUP BY** - Cross-tabulate by one or more dimensions  
✅ **LIMIT** - Limit number of rows returned  
✅ **SSL/TLS** - Secure authentication with API keys

## What's Coming

🔄 **SELECT specific columns** - Access elements as columns (raw respondent data)  
🔄 **JOIN support** - Combine data from multiple elements  
🔄 **OR conditions** - More complex WHERE clause logic  
🔄 **Metadata queries** - Browse available elements via `information_schema`

## Authentication & Security

- All connections require SSL/TLS encryption
- API keys are scoped to your user account
- You can only access projects you have permission to view
- API keys can be regenerated in Account Settings if compromised

## Examples

### Tableau Use Case

**Scenario:** Executive team has existing Tableau dashboards showing sales and operations metrics. They want to add customer satisfaction scores from your Protobi survey, weighted by customer segment.

**Solution:**
1. Add Protobi as a PostgreSQL data source in Tableau
2. Query weighted satisfaction scores: `SELECT * FROM satisfaction_weighted GROUP BY segment`
3. Join with existing sales data on segment
4. Dashboard now shows satisfaction alongside business metrics

### Quick Analysis in psql

```sql
-- How many respondents per region?
SELECT * FROM respondents GROUP BY region;

-- Satisfaction scores for premium customers
SELECT * FROM satisfaction WHERE customer_tier = 'premium';

-- Top 10 product preferences by region
SELECT * FROM product_preference GROUP BY region LIMIT 10;
```

## Troubleshooting

**Connection refused:** Ensure the PostgreSQL server is running on port 5432

**Authentication failed:** Verify your email and API key are correct

**Element not found:** Check the element key matches exactly (case-sensitive)

**SSL required:** Make sure your client is configured to use SSL

## Need Help?

- Check the [Protobi Documentation](https://help.protobi.com)
- Contact support: support@protobi.com
- Report issues: [GitHub Issues](https://github.com/protobi/protobi-sql/issues)

Publishing

Date

Status

Slug edit

Content

Thumbnail

Categories Manage

Tools

AI Database

Publishing

Content

Tools

Danger zone