Or "Use SQL For Everything Part 2". Like the previously covered Steampipe tool, Stackql enables the use of SQL for querying everything too, but with a twist.

In Steampipe - SQL For Everything we discovered Steampipe, a tool that renders SQL as the main query language
for more than purely databases, Cloud infrastructure included. Its underlying notion is that SQL has been the data access standard for decades, it levels the playing field, easily integrates with other systems and accelerates delivery. So why not leverage it for things other than the database, like querying APIs and Cloud services?

Steampipe did just that; it enables a uniform data access method for querying Cloud providers and Cloud-based services and it did that by leveraging PostgreSQL and its Foreign Data Wrappers to present the various data sources as database tables that interface with the external APIs through Plugins.

Stackql then is another tool with the same capabilities and beyond but with much lighter infrastructure requirements. Unlike Steampipe, Stackql is a standalone dev tool which doesn't require a Postgres DB, isn't FDW based and its Cloud providers are defined in OpenAPI extensions. These definitions are then used to generate the SQL schema and the API client, for StackQL to parse the issued SQL statements and transpile them into API requests to the cloud provider. The API calls are then executed and the results are returned to the user.

As such StackQL can be used for:

• Cloud infrastructure deployment
• Cloud asset inventory and reporting
• Cloud compliance and control attestation
• Configuration drift detection

The Cloud providers it supports are stored in the Registry which can be interogated by issuing REGISTRY LIST, which brings
up all the available providers which include Google, AWS, Azure, Okta and more. Installing the tool and then pulling a provider as:

REGISTRY PULL google;

you can now for instance select all resources deployed within a service using a basic SELECT statement:

SELECT * FROM google.compute.instances
WHERE project = 'stackql-demo'
AND zone = 'australia-southeast1-a';

This according to the object based resource system which StackQL is based of:

This hierarchy is expressed in the object notation <provider>.<service>.<resource> within the context of a given provider
as the example above.

However Stackql goes beyond SELECTs. With it you can have full support for lifecycle ops (like starting and stopping VMs) and mutation ops like creating instances, functions, buckets, etc. For instance let's create a new instance of a resource.

-- Create a Compute Engine Disk resource
INSERT INTO google.compute.disks (project, zone, name, sizeGb)
SELECT 'stackql-demo',
'australia-southeast1-a',
'test10gbdisk', 10;

Its language specification consists of the following key terms:

• SELECT
• INSERT
• UPDATE
• REPLACE
• DELETE
• SHOW
• DESCRIBE
• EXEC
• CREATE VIEW
• REFRESH VIEW
• DROP VIEW
• REGISTRY

EXEC in particular is most useful since it executes a provider resource method like
stopping a Google compute engine instance:

-- Stop a running Compute Engine resource instance
EXEC google.compute.instances.stop
@instance = 'demo-instance-1',
@project = 'stackql-demo',
@zone = 'australia-southeast1-a';

By mixing its object system with SQL, you can leverage the usual SQL functions to query and manipulate your Cloud environment:

• Aggregate
• Datetime
• General
• String
• Json
• Math
• Regular Expressions

Cloud providers aside, StackQL also supports Cloud services like Github, netlify, openai,antropic, etc as well as k8.

As said StackQL is a standalone application written in Go that can be used in client mode (via exec or shell) or accessed via a Postgres wire protocol client (psycopg2, etc.) using server mode (srv). Stanadlone binaries are available for Windows, MacOS and Linux, Docker is there too, but since it is open source and free you can grab the code from its Github repo and build it from source.

To sum up, StackQL looks to revolutionize the way you work with the Cloud by providing a post modern way of querying, deploying and managing Cloud resources.

More Information

Stackql

Stackql - Github

Related Articles

Steampipe - SQL For Everything

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info