Skip to main content

3 posts tagged with "github"

View All Tags

· 3 min read

GitHub Codespaces is a development environment completely hosted online, enabling seamless development without setting up local machines. One of the great features of Codespaces is its compatibility with IPython, providing an interactive computing environment to test and prototype StackQL queries.


stackql is a dev tool that allows you to query and manage cloud and SaaS resources using SQL, which developers and analysts can use for CSPM, assurance, user access management reporting, IaC, XOps and more.

Codespaces and the power of IPython and notebooks provide a quick and easy way to use StackQL to analyze and report on your cloud estate and resource configuration. No software necessary!


Setting Up StackQL in Codespaces

See the stackql-codespaces-notebook repository as an example.

With the example devcontainer.json configuration file shown below, you can use the stackql/stackql-codespaces-base image, which includes stackql and the pystackql package (which provides the IPython magic extension used to run stackql queries and return Pandas dataframes for inspection or visualization).

"image": "stackql/stackql-codespaces-base",
"containerEnv": {
"hostRequirements": {
"cpus": 2
"customizations": {
"vscode": {
"extensions": [

Note that the devcontainer configuration includes the essential extensions for Jupyter and Python; you can also optionally specify host requirements for the Codespaces machine.

Provider Authentication

The environment variables required to authenticate to your specific provider or providers can be supplied using Codespaces secrets and passed securely to the Codespaces container using the containerEnv object in the example above. For specifics about variables required for different providers, see the provider documentation for your provider in the StackQL Provider Registry.

Launching the Codespaces Environment

Given the devcontainer configuration shown above in the root of your repository at .devcontainer/devcontainer.json, you can launch codespaces from <your_repo_slug>/codespaces for example You can start a codespaces environment on any branch of your repo.

Running queries and visualizing output

When the environment is provisioned (usually takes a minute or two), you can use the StackQL magic extension and the %%stackql magic decorator to seamlessly run stackql queries, including variable substitution, for example.

%load_ext pystackql.magic

(loads the Stackql magic extension, making the %%stackql decorator available)

region = 'us-east-1'

(set some notebook variables for reusability)

SELECT instanceType, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = '$region'
GROUP BY instanceType

(run a query)

_.plot(kind='pie', y='num_instances', labels=_['instanceType'], title='Instances by Type', autopct='%1.1f%%')

(visualize the results - using matplotlib, plotly, or any other visualization package)

heres an example:


Using the pystackql package, you can also run asynchronous queries such as querying assets across AWS regions, Azure resource groups (or subscriptions) or Google projects in one statement, for example:

# get multiple regions asynchronously
regions = ["ap-southeast-2", "us-east-1"]

queries = [
SELECT '{region}' as region, instanceType, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = '{region}'
GROUP BY instanceType
for region in regions

instances_df = await stackql.executeQueriesAsync(queries)

Visit us and give us a ⭐ on GitHub

· 2 min read

StackQL and the StackQL GitHub provider can be used to query objects in GitHub, including releases, tags, forks, commits, and much more. This article shows how you can automate releases using StackQL.

Push tags

In my case, I merged a PR to the main branch for an updated GitHub action in a repo called stackql-exec, then I pushed a tag with an updated semver:

git tag v1.2.1
git push origin v1.2.1

Pull the StackQL GitHub provider

From the StackQL shell or via the exec command, pull the latest GitHub provider for StackQL using:


Generate a template for creating a release (optional)

StackQL allows you to create resource templates for creating resources such as ec2 instances, google cloud storage buckets, or github releases. This is done using the SHOW INSERT INTO command as seen here:

stackql exec --output text "SHOW INSERT INTO github.repos.releases"

The template will also generate an optional jsonnet variable block to define reusable or externally sourced variables (e.g., from environment variables).

Run your INSERT statement

In this case, I have just used literals to keep it simple...

INSERT INTO github.repos.releases(
'Updated authentication and added support for external variables',

SELECT FROM github.repos.releases (optional)

You can inspect the releases created for your repo, including the one you just created, using a simple SELECT statement as shown here...

from github.repos.releases where owner = 'stackql' and repo = 'stackql-exec';

You will see output like the following:

| name | tag_name | target_commitish | created_at |
| v1.2.1 | v1.2.1 | main | 2023-09-03T05:44:40Z |
| v1.2.0 | v1.2.0 | main | 2023-09-03T05:20:42Z |
| Fix composite action issue | v1.0.1 | main | 2023-03-01T09:14:20Z |
| with path | | | |
| Production release with | v1.0.0 | main | 2023-02-25T23:07:32Z |
| outputs | | | |
| Initial release | v1.0.0-beta | main | 2023-02-05T12:50:01Z |

More information about the GitHub Provider for StackQL can be found here.


· 4 min read

The GitHub provider for StackQL is now generally available. This can be used to query resources in GitHub Cloud or GitHub Enterprise, including orgs, teams, users, repositories, branches, pull requests, issues, workflows/actions and much more!

See available providers

You can see the versions of GitHub Provider (and other providers) available using:

stackql registry list

or from the StackQL Command Shell (stackql shell) using:


this would return a list of all the providers that are currently available, for example:

| provider | version |
| github | v0.1.0 |
| google | v0.1.0 |
| okta | v0.1.0 |

Pull the github provider

To pull v0.1.0 of the github provider use:

stackql registry pull github v0.1.0


REGSITRY PULL github v0.1.0;

to see what providers are installed use:


this would return something like...

| name |
| github |

Explore the github provider and query public resources

The provider and public objects can be queried without authentication as shown here:

AUTH='{"github": { "type": "null_auth" }}'
stackql shell --auth="${AUTH}"

you can now enumerate services, resources, attributes and methods in the github provider using the SHOW and DESCRIBE meta commands, for instance:

show services in github from either the StackQL command shell or via stackql exec would return something like...

| id | name | title |
| actions_enterprises:v0.1.0 | actions_enterprises | GitHub v3 REST API - actions_enterprises |
| billing:v0.1.0 | billing | GitHub v3 REST API - billing |
| repos:v0.1.0 | repos | GitHub v3 REST API - repos |
| ... | ... | ... |

Use the EXTENDED operator with the SHOW or DESCRIBE commands to get additional information about services, resources, attributes and methods, e.g. DESCRIBE EXTENDED github.repos.repos

show resources in github.repos would return something like...

| name | id |
| branches | github.repos.branches |
| commits | github.repos.commits |
| deployments | github.repos.deployments |
| environments | github.repos.environments |
| forks | github.repos.forks |
| releases | github.repos.releases |
| repos | github.repos.repos |
| statistics | github.repos.statistics |
| statuses | github.repos.statuses |
| traffic | github.repos.traffic |
| ... | ... |

to see fields in a resource (which can be queried or updated) use DESCRIBE for example DESCRIBE github.repos.commits would return something like...

| name | type |
| files | array |
| stats | object |
| commit | object |
| url | string |
| html_url | string |
| parents | array |
| node_id | string |
| comments_url | string |
| committer | object |
| sha | string |
| author | object |

to see methods available in a resource use the SHOW METHODS command for example SHOW METHODS IN github.repos.commits would return something like...

| MethodName | RequiredParams |
| compare_commits | basehead, owner, repo |
| get_commit | owner, ref, repo |
| list_branches_for_head_commit | commit_sha, owner, repo |
| list_commits | owner, repo |
| list_pull_requests_associated_with_commit | commit_sha, owner, repo |

Methods beginning with list or get can usually be accessed via SELECT statements. For example,

SELECT github.repos.commits.sha 
FROM github.repos.commits
WHERE owner='${owner}' AND repo='${repo}';

Other methods can be accessed using the EXEC command (for more information see EXEC)

Query protected resources

Accessing protected resources requires authentication using a Personal Access token as shown here:

export GITHUB_CREDS=$(echo -n 'yourgithubusername:ghp_YOURPERSONALACCESSTOKEN' | base64)
AUTH='{ "github": { "type": "basic", "credentialsenvvar": "GITHUB_CREDS" } }'
stackql shell --auth="${AUTH}"

Now you are able to access protected resources, for example:

select id, name, private 
from github.repos_orgs.repos_orgs
where org = 'stackql';

which would return something like...

| id | name | private |
| 443987542 | stackql | false |
| 441087132 | stackqlproviderregistry | false |
| 409393414 | | false |
| 435085734 | | true |
| 443979486 | | true |
| 447890554 | stackqldevel | true |
| ... | ... | ... |

Welcome your feedback by getting in touch or raising issues at stackql/stackql-provider-registry, ⭐️ us while you are there!