StackQL: SQL for Cloud Services & APIs

In the fast-paced digital age, modern cloud stacks have become more complex, meaning users often have to learn various technologies and utilities to manage them.

Each cloud provider offers its own set of tools, interfaces, and APIs, leading to a fragmented experience, that steepens the learning curve and limits the efficiency.

StackQL has been created to help developers standardize their cloud workflows, introducing a unified environment for cloud resources management.

In this article, we will list StackQL's benefits, explore its working principles, learn how to set it up and run practical queries to see how it works in practice.

We will take a look at how users can navigate the cloud services, how they can perform CRUD operations, and other practical operations on the cloud.

Thanks to the StackQL team for sponsoring this article.

What is StackQL and its main features

StackQL is an SQL-based framework that allows users to query, provision, and manage cloud and SaaS resources using familiar SQL syntax.

It is an open-source project, make sure to ⭐ their GitHub repository if you like the project.

Users can easily get started with various commonly used cloud providers like Google Cloud, AWS, Microsoft Azure, Digital Ocean, and many more.

StackQL supports several usage modes including the interactive REPL shell, command line utility, or the execution of the StackQL statements via an input file (IQL file).

It can be used in many practical scenarios like performing live queries against cloud resources, making configuration changes, managing cloud infrastructure, and many more.

StackQL is available to download for Windows, MacOS, and Linux.

Explaining the resource hierarchy

StackQL organizes the objects in a structured hierarchy, mirroring the inner build structure of the cloud service providers themselves.

This arrangement allows users to navigate and manage cloud resources in an easy-to-understand and straightforward way using familiar SQL queries.

See the operational principles of StackQL for Google Cloud Compute service:

At the top level, it categorizes resources by the specific provider, such as Google Cloud or AWS.

Within each provider, the resources are further divided into available services for each provider, such as Compute, Storage, and Networking.

Each service then further contains specific resource types, such as Virtual machines, Storage buckets, Routes, Firewalls, and Licenses.

Finally, each resource has supported methods users can utilize to create, read, update, delete, and perform other operations depending on the resource.

Installing StackQL on your machine

First, navigate to the Downloads section where users can find details about installation, configuration, and utilization across different platforms.

For the purpose of this tutorial, we are gonna use the MSI installer. Follow the instructions in the setup wizard and the installation should not take longer than a minute.

This will install StackQL globally, so we can later access it from anywhere in the system.

After the installation is complete, open your terminal and run the command stackql --version. If the installation was successful, the terminal will return the version of StackQL.

We will be using StackQL shell, meaning we will interact with StackQL and it will provide the outputs to us directly from the terminal.

Setting up Google Cloud provider

StackQL currently supports 20+ commonly used providers, including Google Cloud, AWS, Microsoft Azure, Digital Ocean, GitHub, and many others.

For the purpose of this tutorial, we will be working with Google Cloud provider.

First, you will need to create an account on Google Cloud if you already do not have one. Navigate to the Google Cloud signup page and you can create one for free.

To get started, we will have to create access credentials, so we can authenticate your identity when later working with Google Cloud via StackQL.

Next, select Service Accounts in the left sidebar. Create a Service Account. Enter the account name and ID, and add a description.

Add a new key and choose JSON as the key type. Click Create to download the JSON file, which will contain all of the necessary credentials.

Now navigate back to the terminal and set the variables for the system so we can use them, make sure to replace the path where you saved the credentials JSON file.

Run run the authentification command: stackql shell --auth="{ 'google': { 'type': 'service_account', 'credentialsfilepath': 'creds/sa-key.json'}}".

If the authorization was successful, you should now enter the StackQL interactive command line where we will run the commands to work with Google Cloud.

To begin, we will need to pull in the latest version of Google Cloud provider. You can do that by running registry pull google;.

After the installation is successful, you will be returned the installed version of the provider and the current version of it, as shown below:

For future reference, you can see all of the supported providers and their most recent versions by running a command registry list; and see your current installed ones by running show providers;.

Navigate Cloud APIs for services

First, let's take a look at how we can easily use StackQL to get acquainted with the provider. This will help us to explore their structure and capabilities.

1. Discover available services and resources

A common use case for starters would be to SHOW all of the services available in a provider, or SHOW all of the resources available in a given service.

For example, we can easily list all the services with names containing "compute" and resources with names that include "instances" in the Compute resource.

SHOW SERVICES IN google LIKE '%compute%';
SHOW RESOURCES IN google.compute LIKE '%instances%';

Now, check your terminal and you should have an organized table of the results.

2. Show attributes of the resource

For a more detailed description, users can utilize DESCRIBE keyword, which would provide more information including all of the output fields for a given resource.

For example, we can display the available fields of the resource, or include an additional EXTENDED keyword in our query to get additional metadata.

DESCRIBE google.compute.instances;
DESCRIBE EXTENDED google.compute.instances;

If we check the terminal, we now get an easy-to-use data table with more detailed info.

3. Show methods for the resource

If you would like to see what methods we could use to perform operations for each resource, we can receive the structured data in the table format for that, too.

In that case, we would run SHOW METHODS and provide the particular resource we want to work with to display all the available methods for it.

SHOW METHODS IN google.compute.instances;

If we check the received output from the query, we would see that the columns has provided us with the method name, respective SQL verb and the required params for the query.

Perform CRUD operations on the Cloud

Thanks to the power of StackQL, we can create, read, update, and delete resources on the most commonly used cloud providers.

Now let's try some practical operations on the cloud.

Create a new instance

Creating a new resource is as simple as you would do in the database creating a new row of data using the familiar INSERT statement.

Let's try to create a new Compute Engine Disk resource with a specific name and size and within a specific project and zone.

INSERT INTO google.compute.disks (project, zone, name, sizeGb)
SELECT 'stackql-demo',
'europe-north1-a',
'disk-1', 16;

In order to check the result, navigate to the Disks section, and notice the new Disk instance has been created with the specific parameters we provided.

Read an instance

Let's also test if it is possible to access the newly created resource directly via StackQL.

Execute the SELECT statement to list all or specific resources deployed within a particular service. Let’s list the Disk size of the resource we created earlier.

SELECT sizeGB FROM google.compute.disks
WHERE project = 'stackql-demo'
AND zone = 'europe-north1-a';

Notice that StackQL provides us with the expected output as we wanted.

Update the instance

StackQL can also make changes to the existing resources, all you need to know is the resource to update and the property to set.

Let's try to run the UPDATE statement to modify the Compute Engine Disk size and use the SET statement to define the new value of its capacity.

UPDATE google.compute.disks
SET sizeGb = 32
WHERE project = 'stackql-demo'
AND zone = 'europe-north1-a'
AND name = 'disk-1';

If we check the Google Cloud Compute dashboard, we can see that the disk size is now set to 32GB, which is the new amount we defined in our query.

Delete the instance

Finally, if we no longer need some of the resources, it is also possible to delete them directly via the StackQL interface. It is useful to make sure the resources are being used efficiently.

We would need to run a basic DELETE statement by providing the name of the instance, the project name, and the zone. We will remove the Disk resource we created for test purpose.

DELETE FROM google.compute.disks
WHERE disk = 'disk-1'
AND project = 'stackql-demo'
AND zone = 'europe-north1-a';

Same as before, switch back to the Google Cloud platform and see that the Compute Engine instance was removed from the list of resources.

Perform operations on Cloud resources

We can utilize the power of StackQL even further. For example, we can change the states of the resources by running SQL-like commands to control when they start and stop.

This functionality can be very valuable for developers looking to automate infrastructure management and looking to acheive cost optimization through programmatic control of cloud resources.

For the purpose of this tutorial we have created a virtual machine instance "instance-1". All of your virtual machines will be listed at "VM instances" section.

Stop operations on cloud resources

Upon creating the instance it would be started by default, so let's try to use the EXEC command to stop the Google Cloud Compute instance first.

Provide the instance name, project name, and the zone and StackQL should be able to stop it.

EXEC google.compute.instances.stop
  @instance = 'instance-1',
  @project = 'stackql-demo',
  @zone = 'europe-north1-a';

If we now visit the Google Cloud Compute instances list and check the status of the earlier created resource, we see that the instance is indeed stopped.

Start operations on cloud resources

If we would like to start an inactive Compute Engine instance again, we would need to run a similar query, only this time using start method.

Edit the previous query to the following and execute it in your terminal:

EXEC google.compute.instances.start
  @instance = 'instance-1',
  @project = 'stackql-demo',
  @zone = 'europe-north1-a';

Check back in the Google Cloud to see that the operation has been completed. You will notice that the instance is started again and ready to use.

Conclusion

In this tutorial, we learned that the approach of StackQL is unique and targeted to the developers to standardize the processes of working with cloud operations.

The object hierarchy and workflow of StackQL are very intuitive. The tool essentially bridges the gap between the traditional query language and the modern cloud computing stacks.

We also tested StackQL in practice using various practical use cases from navigating cloud APIs, to performing CRUD operations, and changed the state of the instances.

You could further use it for areas such as cloud asset inventory and reporting, cost optimization, CI/CD Integration for deployment validation, and much more.

I hope that StackQL will be useful for you to improve your tool stack and it will come in handy in future projects whenever you are working with the cloud resources.

Did you like the article? Here is more 👇

Join 6000+ others to receive the best DEV resources, tools, productivity tips, and career growth advice I discover by subscribing to my newsletter!

Also, connect with me on Twitter, LinkedIn, and GitHub!

Writing has always been my passion and it gives me pleasure to help and inspire people. If you want to get featured or partner up, feel free to get in touch!

Introducing StackQL - Manage Your Cloud Services & Interact with APIs using SQL 🧑‍💻🔥