Skip to content
Merged
Show file tree
Hide file tree
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
3 changes: 2 additions & 1 deletion .gitignore
Original file line number Diff line number Diff line change
@@ -1,14 +1,15 @@
src/.stackql
src/bin/*
src/creds/*
.stackql/*
.stackql
creds/*
stackql
stackql.exe
*.tmp
.demo-creds.sh
.demo-creds.ps1
.demo-queries.sql
.env

# Logs
logs
Expand Down
3 changes: 3 additions & 0 deletions .vscode/settings.json
Original file line number Diff line number Diff line change
@@ -0,0 +1,3 @@
{
"deno.enable": true
}
15 changes: 0 additions & 15 deletions Dockerfile

This file was deleted.

48 changes: 35 additions & 13 deletions README.md
Original file line number Diff line number Diff line change
Expand Up @@ -39,19 +39,27 @@ The StackQL middleware server is built on top of the [StackQL core](https://gith

The StackQL middleware server enables clients to query api backends using a natural SQL language for providers installed with authentication configured in the StackQL server. The StackQL server includes a front-end parser and query planner, which translates the SQL query into one or more REST and/or GraphQL requests and then executes the queries against the backends.

Results can be operated on using SQL functions and operators (including grouping, windowing, and aggregation functions) and then returned to the client as a JSON response. The following architecture diagram illustrates the StackQL middleware server's components.
Results can be operated on using SQL functions and operators (including grouping, windowing, and aggregation functions) and then returned to the client as a JSON response. The following architecture diagram illustrates the StackQL middleware server's components.

### Context Diagram

<center>
<img src="https://www.plantuml.com/plantuml/proxy?src=https://raw.githubusercontent.com/stackql/stackql-middleware/main/puml/stackql-middleware-context.puml" alt="StackQL Middleware Context" width="90%"/>
</center>
```mermaid
flowchart TB;
user["User or UserAgent"] -->|submits queries| api["stackql API (Deno Oak)"];
api --> |gets results| user

Detailed design documentation can be found [here](docs/detailed-design.md).
subgraph middleware["stackql Middleware"]
direction TB
api <-.-> |via pgwire| runner["stackql runner (stackql srv)"];
end

runner -->|API calls| cloudprovider["Cloud Provider"];
cloudprovider --> |data| runner
```

## Example Usage


## Request Structure
### Request Structure

StackQL queries are sent to the server using the `POST` method and the `application/json` content type. The request body should contain a JSON object with the following properties:

Expand All @@ -70,11 +78,25 @@ StackQL queries are sent to the server using the `POST` method and the `applicat

`showMetadata` is an optional property that, if set to `true`, will cause the server to return metadata about the query execution. This includes the query plan, the number of rows returned, and the time to execute the query. The default value is `false`.

## Response Structure
An example is shown here:

```bash
curl -X POST http://localhost:8080/stackql \
-H "Content-Type: application/json" \
-d '{
"query": "SELECT COUNT(*) FROM aws.ec2.instances_list_only WHERE region = '\''ap-southeast-2'\''",
"params": {
"region": "ap-southeast-2"
},
"showMetadata": true
}'
```

### Response Structure

Responses can include metadata and the results of a query or will return information about errors if they occur.

### Without Metadata
#### Without Metadata

If the `showMetadata` option is not set or set to `false`, the response will be a JSON array containing the query results. The following example shows the response to a query that returns a single row with two columns:

Expand All @@ -89,7 +111,7 @@ If the `showMetadata` option is not set or set to `false`, the response will be
}
```

### With Metadata
#### With Metadata

If the query was submitted with the `showMetadata` option set, additional information about the request and response will be returned. An example is shown here:

Expand Down Expand Up @@ -121,7 +143,7 @@ If the query was submitted with the `showMetadata` option set, additional inform
}
```

### With Errors
#### With Errors

If there are errors in the execution of a query, a response similar to the following will be returned:

Expand All @@ -144,12 +166,12 @@ Here is a [quick start guide](docs/quickstart.md) to get you up and running with

## Generating a Provider

Providers for any API backend can be generated using StackQL tools, including [stackql/openapi-doc-util](https://github.com/stackql/openapi-doc-util).
Providers for any API backend can be generated using StackQL tools, including [stackql/openapisaurus](https://github.com/stackql/openapisaurus).


## Acknowledgements

This project is made possible by the following open source projects:
- [StackQL](https://github.com/stackql/stackql)
- [Deno Oak](https://deno.land/x/oak@v11.1.0)
- [Oak Server](https://oakserver.org/)
- [Deno Postgres Driver](https://github.com/denodrivers/postgres)
26 changes: 0 additions & 26 deletions demo.ps1

This file was deleted.

40 changes: 18 additions & 22 deletions demo.sh
Original file line number Diff line number Diff line change
@@ -1,28 +1,24 @@
## credentials
export AWS_ACCESS_KEY_ID=YOURAWSACCESSKEYID
export AWS_SECRET_ACCESS_KEY=YOURAWSSECRETACCESSKEY
AZ_ACCESS_TOKEN_RAW=$(az account get-access-token --query accessToken --output tsv)
export AZ_ACCESS_TOKEN=`echo $AZ_ACCESS_TOKEN_RAW | tr -d '\r'`
export GITHUB_CREDS=$(echo -n 'yourusername:ghp_YOURPERSONALACCESSTOKEN' | base64)
export OKTA_SECRET_KEY=YOUROKTASECRETKEY # Okta API Token
export NETLIFY_TOKEN=YOURAPITOKEN
LOGLEVEL=DEBUG docker compose up --build

# auth objects
AWS_AUTH='{ "type": "aws_signing_v4", "credentialsenvvar": "AWS_SECRET_ACCESS_KEY", "keyIDenvvar": "AWS_ACCESS_KEY_ID" }'
AZURE_AUTH='{ "type": "bearer", "credentialsenvvar": "AZ_ACCESS_TOKEN" }'
GITHUB_AUTH='{ "type": "basic", "credentialsenvvar": "GITHUB_CREDS" }'
OKTA_AUTH='{ "type": "api_key", "credentialsenvvar": "OKTA_SECRET_KEY" }'
NETLIFY_AUTH='{ "type": "bearer", "credentialsenvvar": "NETLIFY_TOKEN" }'
docker compose exec api deno test --allow-net tests/api.test.ts

## auth struct
template='{ "aws": %s, "azure": %s, "github": %s, "okta": %s, "netlify": %s }'
export AUTH_STR=$(printf "$template" "$AWS_AUTH","$AZURE_AUTH","$GITHUB_AUTH","$OKTA_AUTH","$NETLIFY_AUTH")
curl -X POST http://localhost:8080/stackql \
-H "Content-Type: application/json" \
-d '{
"query": "SELECT COUNT(*) FROM aws.ec2.instances_list_only WHERE region = '\''$region'\''",
"params": {
"region": "ap-southeast-2"
},
"showMetadata": true
}'

## build and run env
docker compose up --build
curl -X POST http://localhost:8080/stackql \
-H "Content-Type: application/json" \
-d '{
"query": "SELECT COUNT(*) FROM aws.ec2.instances_list_only WHERE region = '\''ap-southeast-2'\''",
"showMetadata": true
}'

## demo playground @ localhost:3000
## demo API using Postman or curl @ localhost:8080
# SELECT COUNT(*) FROM aws.ec2.instances_list_only WHERE region = 'ap-southeast-2';

## remove containers
docker compose down
87 changes: 39 additions & 48 deletions docker-compose.yml
Original file line number Diff line number Diff line change
@@ -1,66 +1,57 @@
version: "3.9"
services:
# credentialsgen:
# image: alpine/openssl
# volumes:
# - ./vol/srv/credentials:/srv:rw
# command:
# - bash
# - -c
# - |
# wget https://raw.githubusercontent.com/stackql/stackql/main/test/server/mtls/openssl.cnf /srv/openssl.cnf && \
# openssl req -x509 -keyout /srv/pg_server_key.pem -out /srv/pg_server_cert.pem -config /srv/openssl.cnf -days 365 && \
# openssl req -x509 -keyout /srv/pg_client_key.pem -out /srv/pg_client_cert.pem -config /srv/openssl.cnf -days 365
playground:
image: stackql/stackql-playground:latest
environment:
MIDDLEWARE_SCHEME: 'http'
MIDDLEWARE_HOST: 'api'
MIDDLEWARE_PORT: '8080'
ports:
- 3000:3000
- "3000:3000"
depends_on:
- api

runner:
build:
context: .
cache_from:
- stackql/stackql
working_dir: /home/stackql
# volumes:
# - ./vol/srv/credentials:/srv:ro
# environment:
# PGSSLSRVKEY: 'true'
# PGSSLROOTCERT: 'true'
# CLIENT_CERT: 'true'
# depends_on:
# - credentialsgen
dockerfile: runner.Dockerfile
working_dir: /home/stackql
expose:
- 5444
environment:
- AUTH_STR=${AUTH_STR}
- AWS_ACCESS_KEY_ID=${AWS_ACCESS_KEY_ID}
- AWS_SECRET_ACCESS_KEY=${AWS_SECRET_ACCESS_KEY}
- AZ_ACCESS_TOKEN=${AZ_ACCESS_TOKEN}
- GITHUB_CREDS=${GITHUB_CREDS}
- OKTA_SECRET_KEY=${OKTA_SECRET_KEY}
- NETLIFY_TOKEN=${NETLIFY_TOKEN}
command:
- /bin/sh
- -c
- |
stackql --version && \
stackql --auth="${AUTH_STR}" --pgsrv.port=5444 srv
- STACKQL_GITHUB_USERNAME
- STACKQL_GITHUB_PASSWORD
- AZURE_TENANT_ID
- AZURE_CLIENT_ID
- AZURE_CLIENT_SECRET
- AWS_ACCESS_KEY_ID
- AWS_SECRET_ACCESS_KEY
- GOOGLE_CREDENTIALS
- DIGITALOCEAN_ACCESS_TOKEN
- LINODE_TOKEN
- DATABRICKS_ACCOUNT_ID
- DATABRICKS_CLIENT_ID
- DATABRICKS_CLIENT_SECRET
- CONFLUENT_CLOUD_API_KEY
- CONFLUENT_CLOUD_API_SECRET
- OKTA_API_TOKEN
- NETLIFY_AUTH_TOKEN
healthcheck:
test: ["CMD", "stackql", "--version"]
interval: 10s
timeout: 5s
retries: 3
command: stackql --pgsrv.port=5444 srv

api:
build: src/
# volumes:
# - ./vol/srv/credentials:/srv:ro
build:
context: ./src
dockerfile: Dockerfile
environment:
DB_HOST: 'runner'
DB_HOST: runner
DB_PORT: 5444
DB_DEBUG: 'false'
LOGLEVEL: ${LOGLEVEL}
LOGLEVEL: ${LOGLEVEL:-INFO}
ports:
- 8080:8080
# depends_on:
# - runner
cache:
image: redis:alpine
- "8080:8080"
depends_on:
runner:
condition: service_healthy
11 changes: 0 additions & 11 deletions docs/detailed-design.md

This file was deleted.

46 changes: 13 additions & 33 deletions docs/quickstart.md
Original file line number Diff line number Diff line change
@@ -1,12 +1,6 @@
# StackQL Middleware Quickstart

Use the [docker-compose.yml](../docker-compose.yml) file included with this repository to get up and running quickly with the following architecture:

<center>
<img src="https://www.plantuml.com/plantuml/proxy?src=https://raw.githubusercontent.com/stackql/stackql-middleware/main/puml/stackql-docker-compose.puml" alt="StackQL Middleware Docker Compose Environment" width="80%"/>
</center>

The quickstart includes the following components:
Use the [docker-compose.yml](../docker-compose.yml) file included with this repository which contains a quick start to get up and running quickly. This includes the following components:

- [StackQL Server](https://github.com/stackql/stackql)
- [StackQL Playground](https://github.com/stackql/stackql-playground)
Expand All @@ -30,36 +24,22 @@ This quick start uses [docker compose v2](https://docs.docker.com/compose/).

1. __Populate Environment Variables for Provider Authentication__

Populate the following environment variables where required (for a provider for which you have credentials and want to query):
(Optional) Populate the following environment variables where required (for a provider for which you have credentials and want to query):

- `GITHUB_CREDS` (base64 encoded value of the GitHub username and Personal Acces Token)
- `AZ_ACCESS_TOKEN` (Azure access token)
- `STACKQL_GITHUB_USERNAME` and `STACKQL_GITHUB_PASSWORD` (GitHub Username and Personal Access Token)
- `AZURE_TENANT_ID`, `AZURE_CLIENT_ID`, `AZURE_CLIENT_SECRET` (for Azure access)
- `AWS_ACCESS_KEY_ID` and `AWS_SECRET_ACCESS_KEY` (AWS API credentials)
- `OKTA_SECRET_KEY` (Okta API key)
- `NETLIFY_TOKEN` (Netlify API key)

Then export a variable named `AUTH_STR` which will be passed to the stackql server. A complete example using GitHub credentials is shown here:

__MacOS/Linux__

```bash
export GITHUB_CREDS=$(echo -n 'yourusername:ghp_YOURPERSONALACCESSTOKEN' | base64)
export AUTH_STR='{ "github": { "type": "basic", "credentialsenvvar": "GITHUB_CREDS" }}'
```

__Windows/PowerShell__

```powershell
$Env:GITHUB_CREDS = [System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes("yourusername:ghp_YOURPERSONALACCESSTOKEN"))
$Env:AUTH_STR = "{ 'github': { 'type': 'basic', 'credentialsenvvar': 'GITHUB_CREDS' } }"
```
- `GOOGLE_CREDENTIALS` (Google service account)
- `DIGITALOCEAN_ACCESS_TOKEN`
- `LINODE_TOKEN`
- `DATABRICKS_ACCOUNT_ID`, `DATABRICKS_CLIENT_ID`, `DATABRICKS_CLIENT_SECRET` (for `databricks_account` or `databricks_workspace`)
- `CONFLUENT_CLOUD_API_KEY`, `CONFLUENT_CLOUD_API_SECRET` (for Confluent cloud access)
- `OKTA_API_TOKEN`
- `NETLIFY_AUTH_TOKEN`

> for additional provider support, add their authentication objects to the `auth` struct as shown below, for more information about authentication, check the documentation for the respective provider, see [here](https://registry.stackql.io/)
> for additional provider support, export their authentication environment variables and add them to the `environment` section of the `runner` in [docker-compose.yml](../docker-compose.yml), for more information about authentication, check the documentation for the respective provider, see [here](https://registry.stackql.io/)

```bash
export GITHUB_CREDS=$(echo -n 'yourusername:ghp_YOURPERSONALACCESSTOKEN' | base64)
export AUTH_STR='{ "github": { "type": "basic", "credentialsenvvar": "GITHUB_CREDS" }, "aws": { "type": "aws_signing_v4", "credentialsenvvar": "AWS_SECRET_ACCESS_KEY", "keyIDenvvar": "AWS_ACCESS_KEY_ID" }}'
```
> ℹ️ To bring your own custom provider, add the provider dirctory to the `.stackql/src` directory on the runner image, then export a variable named `AUTH_STR` and pass this to the the stackql server as an argument in the `runner` section of the `docker-compose.yml` (`stackql --auth="${AUTH_STR}" --pgsrv.port=5444 srv`). The `AUTH_STR` should map to the authentication method and variable names in your custom `provider.yaml` definition, e.g. `'{"my_provider": { "type": "api_key", "credentialsenvvar": "MY_KEY_VAR" }}'`.

2. __Start Environment__

Expand Down
Loading