3 posts tagged with "devops"

We've released a new StackQL provider for GitLab:

• gitlab - the GitLab GraphQL API as a read-only SQL surface: projects, groups, users, issues, merge_requests, ci, work_items, security, packages, snippets, boards, analytics, audit, metadata, admin, workspaces, ml and duo (18 services, 244 resources, every one of them SELECT)

The provider is generated from the introspection schema gitlab.com publishes, pinned by content hash and refreshed as a reviewed diff. It works against gitlab.com out of the box and routes to a self-managed instance from an environment variable. It is read-only by architecture: StackQL's GraphQL path is a query path, so there are no INSERT, UPDATE, DELETE or EXEC methods. What it is for is inventory, reporting and cross-provider joins over the GitLab control plane - the questions that otherwise need a script and three API clients.

Connect​

Authentication is a personal access token with the read_api scope, read from GITLAB_TOKEN - the same variable the Terraform GitLab provider uses:

export GITLAB_TOKEN=glpat-...
stackql shell

Public projects and groups on gitlab.com are readable without a token (--auth='{"gitlab": {"type": "null_auth"}}'). For a self-managed instance, set GITLAB_HOST=gitlab.example.com and every query routes there; an explicit WHERE host = ... still wins for addressing another instance in the same session.

Three scopes, one shape​

Resources follow the GraphQL schema. Instance-scoped resources take optional filters only (projects, users, runners); project-scoped resources are prefixed project_ and take the project path; group-scoped resources are prefixed group_ and take the group path. Columns are snake_case, and the nested identity objects GitLab attaches everywhere (author, namespace, milestone, user) are JSON columns one json_extract away.

Every project in a group tree, with activity and visibility signals:

SELECT full_path, visibility, archived,
       star_count, forks_count,
       open_issues_count, open_merge_requests_count,
       last_activity_at
FROM gitlab.groups.group_projects
WHERE full_path = 'gitlab-org' AND include_subgroups = true
ORDER BY last_activity_at DESC;

GitLab connections are Relay-paginated with a hard page size of 100. StackQL walks the pageInfo cursor chain transparently, so the query above returns the whole tree, not the first page.

Filters are pushed down​

Every scalar or enum argument a GitLab field accepts is a WHERE parameter rendered into the GraphQL query itself, so the API does the filtering. Open merge requests with their approval state and age:

SELECT iid, title,
       json_extract(author, '$.username') AS author,
       draft, approved, approvals_left, detailed_merge_status,
       ROUND(julianday('now') - julianday(created_at)) AS age_days
FROM gitlab.merge_requests.project_merge_requests
WHERE full_path = 'gitlab-org/gitlab-runner' AND state = 'opened'
ORDER BY age_days DESC;

Cycle time over a window (merged_after is a filter, merged_at a column):

SELECT iid, title,
       ROUND((julianday(merged_at) - julianday(created_at)) * 24, 1) AS hours_to_merge
FROM gitlab.merge_requests.project_merge_requests
WHERE full_path = 'gitlab-org/gitlab-runner'
  AND state = 'merged'
  AND merged_after = '2026-09-01T00:00:00Z'
ORDER BY merged_at DESC;

Pipelines and runners​

Pipeline outcomes for a project, and the same across every project in a group by joining the inventory to each project's pipelines (the engine issues one pipelines request per project):

SELECT status, count(*) AS pipelines, ROUND(AVG(duration) / 60.0, 1) AS avg_minutes
FROM gitlab.ci.project_pipelines
WHERE full_path = 'gitlab-org/gitlab-runner'
GROUP BY status;

SELECT p.full_path,
       count(*) AS pipelines,
       SUM(c.status = 'FAILED') AS failed,
       ROUND(100.0 * SUM(c.status = 'FAILED') / count(*), 1) AS failure_pct
FROM gitlab.groups.group_projects p
JOIN gitlab.ci.project_pipelines c ON c.full_path = p.full_path
WHERE p.full_path = 'my-group'
GROUP BY p.full_path
ORDER BY failure_pct DESC;

Runner fleet status for a group, with contact recency (the instance-wide runner listing is administrator-only on gitlab.com; the group and project listings are what a token can read):

SELECT id, description, runner_type, status, paused, contacted_at, upgrade_status
FROM gitlab.ci.group_runners
WHERE full_path = 'my-group'
ORDER BY contacted_at DESC;

Vulnerability reporting​

Findings across a group by severity and state, then the unresolved criticals with their owning project:

SELECT severity, state, count(*) AS findings
FROM gitlab.security.group_vulnerabilities
WHERE full_path = 'my-group'
GROUP BY severity, state;

SELECT json_extract(project, '$.full_path') AS project,
       title, report_type, detected_at, web_url
FROM gitlab.security.group_vulnerabilities
WHERE full_path = 'my-group' AND severity = 'CRITICAL' AND state = 'DETECTED'
ORDER BY detected_at;

Membership, and a join across providers​

Group membership with access level is a table:

SELECT json_extract(user, '$.username') AS username,
       json_extract(user, '$.name') AS name,
       json_extract(access_level, '$.string_value') AS access_level,
       expires_at
FROM gitlab.groups.group_group_members
WHERE full_path = 'my-group';

which makes the offboarding question a LEFT JOIN against the identity provider - GitLab members with no active Okta user, computed locally by the SQL engine after registry pull okta:

SELECT json_extract(m.user, '$.username') AS gitlab_username,
       json_extract(m.user, '$.name') AS gitlab_name,
       o.status AS okta_status
FROM gitlab.groups.group_group_members m
LEFT JOIN okta.user.users o
  ON lower(json_extract(o.profile, '$.login')) = lower(json_extract(m.user, '$.username') || '@example.com')
 AND o.subdomain = 'my-okta-org'
WHERE m.full_path = 'my-group'
  AND (o.status IS NULL OR o.status != 'ACTIVE');

How it is built​

• The selection set for every resource is generated by one policy from the schema: all scalar and enum fields of the node type plus a fixed allowlist of nested identity objects. The query text and the response schema come from the same field list, so DESCRIBE always matches what the wire returns.
• GitLab enforces a query complexity limit (200 anonymous, 250 authenticated on gitlab.com). Every generated query is scored against the live limit as a build gate; the one node type that exceeded it (merge requests, at 245) was trimmed by measured per-field cost, protecting the approval and merge-state columns, to 190.
• A handful of resolvers time out on large result sets or answer anonymous callers with a server error. Those fields are excluded by a recorded policy entry rather than left to fail whole pages.
• all_services.csv in the repository records which schema field backs every resource and method, and regeneration fails when a mapping moves, so resource names stay stable between provider versions.

Premium-tier fields (issue weight, health status, epics) read back as null on the free tier, and the schema is gitlab.com's, so an older self-managed instance may reject fields it does not serve.

Get started​

Pull the provider from the public registry:

registry pull gitlab;

Provider docs are at gitlab-provider.stackql.io. Let us know what you build. Star us on GitHub.

The StackQL Linode provider is now available. Using the StackQL Linode provider you can create, query, and manage Linodes (instances), Volumes, NodeBalancers, Firewalls, StackScripts, Databases, Kubernetes Clusters, Object Storage Buckets, and much more.

You can use the StackQL Linode provider with other StackQL providers (such as aws, google, azure, digitalocean, and more) to perform multi-cloud CSPM, inventory queries, or multi-provider stack deployments. Documentation for the Linode provider is available at StackQL Linode provider docs.

Here is an example of creating a Linode (a VM instance), passing variables from a jsonnet config file as well as CI secrets (GitHub Actions Secrets, GitLab CI Secrets, etc.):

INSERT INTO linode.instances.linodes(
  data__authorized_keys,
  data__authorized_users,
  data__root_pass,
  data__image,
  data__label,
  data__region,
  data__type
)
SELECT
  '[ "{{ .authorized_key }}" ]',
  '[ "{{ .authorized_user }}" ]',
  '{{ .root_pass }}',
  '{{ .image }}',
  '{{ .label }}',
  '{{ .region }}',
  '{{ .type }}'
;

Querying objects in Linode can be done using SELECT statements, such as:

select id, 
label, 
region, 
JSON_EXTRACT(specs, '$.vcpus') as vcpus,
JSON_EXTRACT(specs, '$.memory') as memory,
JSON_EXTRACT(specs, '$.disk') as disk,
status 
from linode.instances.linodes;

Which would return:

|----------|-----------|--------------|-------|--------|-------|---------|                                                                                                                                              
|    id    |   label   |    region    | vcpus | memory | disk  | status  |                                                                                                                                              
|----------|-----------|--------------|-------|--------|-------|---------|                                                                                                                                              
| 46063573 | my-linode | ap-southeast |     1 |   1024 | 25600 | running |                                                                                                                                              
|----------|-----------|--------------|-------|--------|-------|---------| 

Summary or aggregate queries such as GROUP BY -> COUNT or SUM are fully supported with StackQL, as are JOIN and UNION operations (including cross-provider JOIN operations).

StackQL supported outputs include table, csv (using a comma or user-specified delimiter), and json.

StackQL can be accessed through the interactive shell stackql shell as well as noninteractive access using stackql exec and server-based access using stackql srv - where you can use any Postgres wire protocol client to run StackQL queries. GitHub actions, Jupyter notebooks, and Superset dashboards are other options for using StackQL.

The Digital Ocean provider is now available for StackQL. You can use StackQL to provision, manage or report on Droplets, Apps, Functions, Databases, Volumes, Spaces, and more.

To use the Digital Ocean provider, generate a Personal Access Token from the Digital Ocean Control Panel under the API section. Export the value of the token created to a variable named DIGITALOCEAN_TOKEN (on your local system or as a CI secret). You can then run queries against the Digital Ocean provider using StackQL.

The following example demonstrates the creation of a Droplet in Digital Ocean.

INSERT INTO digitalocean.droplets.droplets ( 
data__name, 
data__region, 
data__size, 
data__image, 
data__backups, 
data__ipv6, 
data__monitoring, 
data__tags 
) 
SELECT 
'droplet-1.example.com', 
'nyc3', 
's-1vcpu-1gb', 
'ubuntu-20-04-x64', 
true, 
true, 
true, 
'["env:prod", "web"]';

You can use jsonnet as a configuration, templating language with StackQL to provide variables or parameters to IaC operations in StackQL; this can be done using the --data flag in the stackql exec command as follows:

./stackql exec --infile create_droplets.iql --iqldata vars.jsonnet

The code for create_droplets.iql and vars.jsonnet is shown here:

{{range $index, $element := .droplets}}
INSERT INTO digitalocean.droplets.droplets ( 
data__name, 
data__region, 
data__size, 
data__image, 
data__backups, 
data__ipv6, 
data__monitoring, 
data__tags 
) 
SELECT 
'droplet-{{$index}}.stackql.io', 
'nyc3', 
'{{.size}}', 
'ubuntu-20-04-x64', 
true, 
true, 
true, 
'["env:prod", "web"]';
{{end}}

{
	droplets: [
		{ size: 's-1vcpu-512mb-10gb' },
		{ size: 's-1vcpu-1gb' },
		{ size: 's-1vcpu-1gb-amd' },
	]
}

StackQL is a unified SQL-based framework that can be used for analytics and reporting as well as provisioning, de-provisioning, and lifecycle opertaions. As a native multi-cloud solution, StackQL can analyze and report across assets across multiple different providers; an example is shown here:

SELECT 
name, 
JSON_EXTRACT(region, '$.name') as region, 
JSON_EXTRACT(size, '$.slug') as size,
'digitalocean' as provider
FROM digitalocean.droplets.droplets
UNION
SELECT
instanceId as name,
'us-east-1' as region,
instanceType as size,
'aws' as provider
FROM aws.ec2.instances 
WHERE region = 'us-east-1';

Digital Ocean and multi-cloud queries can be visualized using BI tools or notebooks; examples of using StackQL with Jupyter can be found here.

More information about the Digital Ocean provider for StackQL can be found here.