Table: circleci_project - Query CircleCI Projects using SQL

CircleCI is a continuous integration and delivery platform that automates the build, test, and deploy process for software applications. It allows developers to rapidly release code by automating the build, test, and delivery process. It integrates seamlessly with GitHub, Bitbucket, and other version control systems, making it a popular choice for software development teams.

Table Usage Guide

The circleci_project table provides insights into projects within CircleCI. As a DevOps engineer, explore project-specific details through this table, including vcs type, username, project name, and default branch. Utilize it to uncover information about projects, such as those with specific configurations, the default branches for each project, and the version control systems in use.

Examples

List checkout keys of a project

Explore the different checkout keys associated with a specific project to gain insights into their attributes such as fingerprint, preference, public key, time, type, and login. This can be particularly useful for project management and security purposes, such as tracking key usage and identifying preferred keys.

select
  k ->> 'fingerprint' as fingerprint,
  k ->> 'preferred' as preferred,
  k ->> 'public_key' as public_key,
  k ->> 'time' as time,
  k ->> 'type' as type,
  k ->> 'login' as login
from
  circleci_project p,
  jsonb_array_elements(p.checkout_keys) k
where
  p.slug = 'gh/fluent-cattle/sp-plugin-test'
order by
  k ->> 'time';

select
  json_extract(k.value, '$.fingerprint') as fingerprint,
  json_extract(k.value, '$.preferred') as preferred,
  json_extract(k.value, '$.public_key') as public_key,
  json_extract(k.value, '$.time') as time,
  json_extract(k.value, '$.type') as type,
  json_extract(k.value, '$.login') as login
from
  circleci_project p,
  json_each(p.checkout_keys) as k
where
  p.slug = 'gh/fluent-cattle/sp-plugin-test'
order by
  json_extract(k.value, '$.time');

Projects with builds running on the main branch

Explore which projects are currently executing builds on the main branch. This can be useful in identifying active development efforts and monitoring build statuses in real-time.

select
  concat(username, '/', reponame) as repository,
  'main' as branch,
  jsonb_array_length(branches -> 'main' -> 'running_builds') as running_builds
from
  circleci_project
where
  (branches -> 'main') is not null;

select
  username || '/' || reponame as repository,
  'main' as branch,
  json_array_length(json_extract(branches, '$.main.running_builds')) as running_builds
from
  circleci_project
where
  json_extract(branches, '$.main') is not null;

Project's last successful build (main branch)

Analyze the settings to understand the last successful build for a project's main branch in CircleCI. This is useful for tracking the progress and stability of your main branch over time.

select
  concat(username, '/', reponame) as repository,
  branches -> 'main' -> 'last_success' -> 'build_num' as build_num
from
  circleci_project
where
  (branches -> 'main') is not null;

select
  username || '/' || reponame as repository,
  json_extract(
    json_extract(branches, '$.main'),
    '$.last_success.build_num'
  ) as build_num
from
  circleci_project
where
  json_extract(branches, '$.main') is not null;

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)" -- circleci

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

steampipe_export_circleci --config '<your_config>' circleci_project