steampipe plugin install github

Table: github_pull_request - Query GitHub Pull Requests using SQL

GitHub Pull Requests are a feature of GitHub, a web-based hosting service for version control using Git. Pull requests let you tell others about changes you've pushed to a branch in a repository on GitHub. Once a pull request is opened, you can discuss and review the potential changes with collaborators and add follow-up commits before your changes are merged into the base branch.

Table Usage Guide

The github_pull_request table provides insights into pull requests within GitHub. As a developer or project manager, explore pull request-specific details through this table, including the status, assignees, reviewers, and associated metadata. Utilize it to track the progress of pull requests, identify bottlenecks in the review process, and ensure timely merging of approved changes.

Important Notes

  • You must specify the repository_full_name (repository including org/user prefix) where or join clause to query the table.

Examples

List open pull requests in a repository

Determine the areas in which there are active discussions about code changes in a specific project. This is useful for project managers and contributors to track ongoing development efforts and understand the status of proposed modifications.

select
repository_full_name,
number,
title,
state,
mergeable
from
github_pull_request
where
repository_full_name = 'turbot/steampipe'
and state = 'OPEN';
select
repository_full_name,
number,
title,
state,
mergeable
from
github_pull_request
where
repository_full_name = 'turbot/steampipe'
and state = 'OPEN';

List the pull requests for a repository that have been closed in the last week

This example provides a way to monitor recent activity in a specific GitHub repository. It's particularly useful for project managers who want to track the progress of their projects by identifying which pull requests have been closed in the last week.

select
repository_full_name,
number,
title,
state,
closed_at,
merged_at,
merged_by
from
github_pull_request
where
repository_full_name = 'turbot/steampipe'
and state = 'CLOSED'
and closed_at >= (current_date - interval '7' day)
order by
closed_at desc;
## Runtime error: parsing time "2023-12-01" as "2006-01-02 15:04:05.999": cannot parse "" as "15"
select
repository_full_name,
number,
title,
state,
closed_at,
merged_at,
merged_by
from
github_pull_request
where
repository_full_name = 'turbot/steampipe'
and state = 'CLOSED'
and closed_at >= date('now', '-7 day')
order by
closed_at desc;

List the open PRs in a repository with a given label

Explore which open pull requests in a specific repository are tagged as 'bug'. This can help prioritize bug-fixing efforts and manage the project more efficiently.

select
repository_full_name,
number,
state,
labels
from
github_pull_request
where
repository_full_name = 'turbot/steampipe-plugin-aws'
and labels -> 'bug' = 'true';
select
repository_full_name,
number,
state,
labels
from
github_pull_request
where
repository_full_name = 'turbot/steampipe-plugin-aws'
and json_extract(labels, '$.bug') = 1;

List the open PRs in a repository assigned to a specific user

This query can be used to identify all the open pull requests in a specific repository that have been assigned to a particular user. This is useful in tracking and managing the workload of individual contributors within a project.

select
repository_full_name,
number,
title,
state,
assignee_data ->> 'login' as assignee_login
from
github_pull_request,
jsonb_array_elements(assignees) as assignee_data
where
repository_full_name = 'turbot/steampipe-plugin-aws'
and assignee_data ->> 'login' = 'madhushreeray30'
and state = 'OPEN';
select
repository_full_name,
number,
title,
state,
json_extract(assignee_data.value, '$.login') as assignee_login
from
github_pull_request,
json_each(assignees) as assignee_data
where
repository_full_name = 'turbot/steampipe-plugin-aws'
and json_extract(assignee_data.value, '$.login') = 'madhushreeray30'
and state = 'OPEN';

Join with github_my_repository to find open PRs in multiple repos

This query allows you to identify open pull requests across multiple repositories within the 'turbot/steampipe' project. It's particularly useful for project managers who need to track ongoing contributions and updates across various parts of the project.

select
i.repository_full_name,
i.number,
i.title
from
github_my_repository as r,
github_pull_request as i
where
r.name_with_owner like 'turbot/steampip%'
and i.state = 'OPEN'
and i.repository_full_name = r.name_with_owner;
select
i.repository_full_name,
i.number,
i.title
from
github_my_repository as r,
github_pull_request as i
where
r.name_with_owner like 'turbot/steampip%'
and i.state = 'OPEN'
and i.repository_full_name = r.name_with_owner;

List open PRs in a repository with an array of associated labels

This query is useful for exploring open pull requests in a specific repository, along with their associated labels. This can help in managing and prioritizing work by understanding the context and importance of each pull request.

select
r.repository_full_name,
r.number,
r.title,
jsonb_agg(l ->> 'name') as labels
from
github_pull_request r,
jsonb_array_elements(r.labels_src) as l
where
repository_full_name = 'turbot/steampipe'
and state = 'OPEN'
group by
r.repository_full_name,
r.number,
r.title;
select
r.repository_full_name,
r.number,
r.title,
json_group_array(json_extract(l.value, '$.name')) as labels
from
github_pull_request r,
json_each(r.labels_src) as l
where
repository_full_name = 'turbot/steampipe'
and state = 'OPEN'
group by
r.repository_full_name,
r.number,
r.title;

OR

select
repository_full_name,
number,
title,
json_agg(t) as labels
from
github_pull_request r,
jsonb_object_keys(r.labels) as t
where
repository_full_name = 'turbot/steampipe'
and state = 'OPEN'
group by
repository_full_name,
number,
title;
select
repository_full_name,
number,
title,
json_group_array(t.value) as labels
from
github_pull_request r,
json_each(r.labels) as t
where
repository_full_name = 'turbot/steampipe'
and state = 'OPEN'
group by
repository_full_name,
number,
title;

OR

select
number,
created_at,
array(
select
jsonb_object_keys(labels)
) as labels
from
github_pull_request
where
state = 'OPEN'
and repository_full_name = 'turbot/steampipe';
select
number,
created_at,
(
select
group_concat(key)
from
json_each(labels)
) as labels
from
github_pull_request
where
state = 'OPEN'
and repository_full_name = 'turbot/steampipe';

Schema for github_pull_request

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
active_lock_reasontextReason that the conversation was locked.
additionsbigintThe number of additions in this pull request.
assigneesjsonbA list of Users assigned to the pull request.
assignees_total_countbigintA count of users assigned to the pull request.
authorjsonbThe author of the pull request.
author_associationtextAuthor's association with the pull request.
base_refjsonbThe base ref associated with the pull request.
base_ref_nametextIdentifies the name of the base Ref associated with the pull request, even if the ref has been deleted.
bodytextThe body as Markdown.
can_apply_suggestionbooleanIf true, current user can apply suggestions.
can_closebooleanIf true, current user can close the pull request.
can_delete_head_refbooleanIf true, current user can delete/restore head ref.
can_disable_auto_mergebooleanIf true, current user can disable auto-merge.
can_edit_filesbooleanIf true, current user can edit files within this pull request.
can_enable_auto_mergebooleanIf true, current user can enable auto-merge.
can_merge_as_adminbooleanIf true, current user can bypass branch protections and merge the pull request immediately.
can_reactbooleanIf true, current user can react to the pull request.
can_reopenbooleanIf true, current user can reopen the pull request.
can_subscribebooleanIf true, current user can subscribe to the pull request.
can_updatebooleanIf true, current user can update the pull request.
can_update_branchbooleanIf true, current user can update the head ref of the pull request by merging or rebasing the base ref.
cannot_update_reasonsjsonbReasons why the current user cannot update the pull request, if applicable.
changed_filesbigintThe number of files changed in this pull request.
checks_urltextURL for the checks of this pull request.
closedbooleanIf true, pull request is closed.
closed_attimestamp with time zoneTimestamp when the pull request was closed.
commits_total_countbigintA count of commits in the pull request.
created_attimestamp with time zoneTimestamp when the pull request was created.
created_via_emailbooleanIf true, pull request comment was created via email.
deletionsbigintThe number of deletions in this pull request.
did_authorbooleanIf true, current user authored the pull request.
editorjsonbThe actor who edited the pull request's body.
head_refjsonbThe head ref associated with the pull request.
head_ref_nametextIdentifies the name of the head Ref associated with the pull request, even if the ref has been deleted.
head_ref_oidtextIdentifies the oid/sha of the head ref associated with the pull request, even if the ref has been deleted.
idbigintThe ID of the pull request.
includes_created_editbooleanIf true, this pull request was edited and includes an edit with the creation data.
is_cross_repositorybooleanIf true, head and base repositories are different.
is_draftbooleanIf true, the pull request is a draft.
is_read_by_userbooleanIf true, this pull request was read by the current user.
labelsjsonbA map of labels for the pull request.
labels_srcjsonbThe first 100 labels associated to the pull request.
labels_total_countbigintA count of labels applied to the pull request.
last_edited_attimestamp with time zoneTimestamp the editor made the last edit.
lockedbooleanIf true, the pull request is locked.
login_idtext=, !=, ~~, ~~*, !~~, !~~*Unique identifier for the user login.
maintainer_can_modifybooleanIf true, maintainers can modify the pull request.
merge_commitjsonbThe merge commit associated the pull request, null if not merged.
mergeabletextWhether or not the pull request can be merged based on the existence of merge conflicts.
mergedbooleanIf true, the pull request was merged.
merged_attimestamp with time zoneTimestamp when pull request was merged.
merged_byjsonbThe actor who merged the pull request.
milestonejsonbThe milestone associated with the pull request.
node_idtextThe node ID of the pull request.
numberbigint=The number of the pull request.
permalinktextPermanent URL for the pull request.
published_attimestamp with time zoneTimestamp the pull request was published.
repository_full_nametext=The full name of the repository the pull request belongs to.
revert_urltextURL to revert the pull request.
review_decisiontextThe current status of this pull request with respect to code review.
review_requests_total_countbigintA count of reviews requested on the pull request.
reviews_total_countbigintA count of completed reviews on the pull request.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statetext=The current state of the pull request.
subscriptiontextStatus of current users subscription to the pull request.
suggested_reviewersjsonbSuggested reviewers for the pull request.
titletextThe title of the pull request.
total_comments_countbigintThe number of comments on the pull request.
updated_attimestamp with time zoneTimestamp when the pull request was last updated.
urltextURL of the pull request.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- github

You can pass the configuration to the command with the --config argument:

steampipe_export_github --config '<your_config>' github_pull_request