13 posts tagged with "infrastructure-as-code"

StackQL now supports the Model Context Protocol (MCP). This integration enables AI agents and assistants to query and manage cloud infrastructure across multiple providers using natural language.

What is the Model Context Protocol?​

The Model Context Protocol is an open standard that enables AI applications to securely connect to external data sources and tools. By running StackQL as an MCP server, AI agents like Claude, ChatGPT, and other LLM-based assistants can interact with your cloud infrastructure using StackQL's powerful SQL-based query capabilities.

Why MCP + StackQL?​

Combining MCP with StackQL creates a powerful interface for AI-assisted infrastructure management:

• Natural Language Infrastructure Queries: Ask questions about your cloud resources in plain English and get structured data back
• Multi-Cloud Support: Access resources across AWS, Google Cloud, Azure, and 100+ other providers through a single interface
• Secure and Standardized: MCP provides a secure, standardized way for AI agents to interact with your infrastructure
• SQL-Powered Analytics: Leverage StackQL's full SQL capabilities including joins, aggregations, and complex queries through AI agents

Deployment Options​

StackQL's MCP server supports three flexible deployment modes to suit different architectural requirements:

1. Standalone MCP Server​

Perfect for development and AI agent integration:

stackql mcp \
  --mcp.server.type=http \
  --mcp.config '{"server": {"transport": "http", "address": "127.0.0.1:9912"}}'

2. Dual-Protocol Server (In-Memory)​

Run both MCP and PostgreSQL wire protocol simultaneously with high-performance in-memory communication:

stackql srv \
  --mcp.server.type=http \
  --mcp.config '{"server": {"transport": "http", "address": "127.0.0.1:9912"}}' \
  --pgsrv.port 5665

This mode is ideal when you need both AI agent access and traditional database client connectivity.

3. Reverse Proxy with TLS​

For production environments requiring distributed deployments and encrypted connections:

stackql srv \
  --mcp.server.type=reverse_proxy \
  --mcp.config '{"server": {"tls_cert_file": "/path/to/cert.pem", "tls_key_file": "/path/to/key.pem", "transport": "http", "address": "127.0.0.1:9004"}, "backend": {"dsn": "postgres://stackql:stackql@127.0.0.1:5446?default_query_exec_mode=simple_protocol"}}' \
  --pgsrv.port 5446

Available MCP Tools​

When running as an MCP server, StackQL exposes several tools that AI agents can invoke:

Integration with Claude Desktop​

To integrate StackQL with Claude Desktop, add this configuration to your MCP settings file (~/Library/Application Support/Claude/claude_desktop_config.json on macOS):

{
  "mcpServers": {
    "stackql": {
      "command": "stackql",
      "args": [
        "mcp",
        "--mcp.server.type=http",
        "--mcp.config",
        "{\"server\": {\"transport\": \"http\", \"address\": \"127.0.0.1:9912\"}}"
      ]
    }
  }
}

Example Use Cases​

Once configured, you can ask your AI assistant questions like:

• "Show me all my EC2 instances across all AWS regions"
• "List all Google Cloud Storage buckets with public access"
• "Find all Azure virtual machines that haven't been updated in 30 days"
• "Compare compute costs across AWS, Azure, and GCP"
• "Show me IAM policies that grant admin access in my Google Cloud projects"

The AI agent will use StackQL's MCP server to execute the appropriate queries and return structured results.

Example Query Flow​

Here's how an AI agent interacts with StackQL via MCP:

# AI agent lists available providers
Tool: list_providers
Response: ["google", "aws", "azure", "github", ...]

# AI agent explores a provider's services
Tool: list_services
Args: {"provider": "google"}
Response: ["compute", "storage", "cloudresourcemanager", ...]

# AI agent executes a query
Tool: query_v2
Args: {"sql": "SELECT name, status FROM google.compute.instances WHERE project = 'my-project' AND zone = 'us-east1-a'"}
Response: [{"name": "instance-1", "status": "RUNNING"}, ...]

Getting Started​

1. Download StackQL version 0.9.250 or later from stackql.io/install

2. Set up provider authentication:

export GOOGLE_CREDENTIALS=$(cat /path/to/credentials.json)
export AWS_ACCESS_KEY_ID=your-access-key
export AWS_SECRET_ACCESS_KEY=your-secret-key

3. Start the MCP server:

stackql mcp \
  --mcp.server.type=http \
  --mcp.config '{"server": {"transport": "http", "address": "127.0.0.1:9912"}}'

4. Configure your AI assistant to use the StackQL MCP server (see MCP documentation for details)

Documentation​

For comprehensive documentation on configuring and using the MCP server, including:

• Detailed configuration options
• TLS/mTLS setup
• Architecture considerations
• Testing and troubleshooting

Visit the MCP command documentation.

What's Next?​

We're actively developing additional MCP capabilities and welcome your feedback. Future enhancements may include:

• Enhanced resource provisioning and lifecycle management through MCP
• Built-in prompt templates for common infrastructure queries
• Extended tool catalog for specialized operations
• Support for additional MCP transport protocols

Try It Out!​

The MCP server feature is available now in StackQL 0.9.250. We'd love to hear about your experiences integrating StackQL with AI agents. Share your use cases, provide feedback, or contribute to the project on GitHub.

⭐ Star us on GitHub and join our community!

The stackql-deploy docs site is now available, offering a comprehensive guide to using stackql-deploy for your cloud resource deployments and tests. The site includes detailed documentation, examples, and best practices to help you get started quickly and effectively.

Let us know what you think! ⭐ us on GitHub.

StackQL is now available for ARM64-based Linux systems. To download the ARM64 binary, please visit our downloads page, where you can find the appropriate version for your system.

Let us know what you think! ⭐ us on GitHub.

stackql-deploy is now available in the GitHub Actions Marketplace.

Given this example stackql-deploy stack definition in a GitHub repo, you would simply add the following to your GitHub Actions workflow:

...
jobs:
  stackql-actions-test:
    name: StackQL Actions Test
    runs-on: ubuntu-latest
    env:
      GOOGLE_CREDENTIALS: ${{ secrets.GOOGLE_CREDENTIALS }}
    
    steps:
      - name: Checkout
        uses: actions/checkout@v4

      - name: Deploy a Stack
        uses: stackql/setup-deploy@v1.0.1
        with:
          command: build
          stack-dir: examples/k8s-the-hard-way
          stack-env: dev
          env-vars: GOOGLE_PROJECT=stackql-k8s-the-hard-way-demo

Example output is shown here:

Let us know what you think! ⭐ us on GitHub.

stackql-deploy is a multi-cloud resource provisioning framework using stackql. It is inspired by dbt (data build tool), which manages data transformation workflows in analytics engineering by treating SQL scripts as models that can be built, tested, and materialized incrementally. With StackQL, you can create a similar framework for cloud and SaaS provisioning. The goal is to treat infrastructure-as-code (IaC) queries as models that can be deployed, managed, and interconnected.

This ELT/model-based framework for IaC allows you to provision, test, update, and tear down multi-cloud stacks, similar to how dbt manages data transformation projects, with the benefits of version control, peer review, and automation. This approach enables you to deploy complex, dependent infrastructure components in a reliable and repeatable manner.

Features​

StackQL simplifies the interaction with cloud resources by using SQL-like syntax, making it easier to define and execute complex cloud management operations. Resources are provisioned with INSERT statements, and tests are structured around SELECT statements.

Features include:

• Dynamic state determination (eliminating the need for state files)
• Pre-flight and post-deploy assurance tests for resources
• Simple flow control with rollback capabilities
• Single code base for multiple target environments
• SQL-based definitions for resources and tests

Installing stackql-deploy​

To get started with stackql-deploy, run the following:

pip install stackql-deploy

stackql-deploy will automatically download the latest release of stackql using the pystackql Python package. You can then use the info command to get runtime information:

$ stackql-deploy info
stackql-deploy version: 1.1.0
pystackql version     : 3.6.1
stackql version       : v0.5.612
stackql binary path   : /home/javen/.local/stackql
platform              : Linux x86_64 (Linux-5.15.133.1-microsoft-standard-WSL2-x86_64-with-glibc2.35), Python 3.10.12

Project structure​

A stackql-deploy project is a directory with declarative SQL definitions to provision, de-provision, or test resources in a stack. The key components and their definitions are listed here:

• stackql_manifest.yml : The manifest file for your project, defining resources and properties in your stack.
• stackql_resources directory : Contains StackQL queries to provision and de-provision resources in your stack.
• stackql_tests directory : Contains StackQL queries to test the desired state for resources in your stack.

Getting started​

Use the init command to create a starter project directory:

stackql-deploy init activity_monitor

You will now have a directory named activity_monitor with stackql_resources and stackql_tests directories and a sample stackql_manifest.yml file, which will help you to get started.

Usage​

The general syntax for stackql-deploy is described here:

stackql-deploy [OPTIONS] COMMAND [ARGS]...

Commands include:

• build: Create or update resources based on the defined stack.
• teardown: Remove or decommission resources that were previously deployed.
• test: Execute test queries to verify the current state of resources against the expected state.
• info: Display the version information of the stackql-deploy tool and current configuration settings.
• init: Initialize a new project structure for StackQL deployments.

Optional global options (for all commands) include:

• --custom-registry TEXT: Specify a custom registry URL for StackQL. This URL will be used by all commands for registry interactions.
• --download-dir TEXT: Define a download directory for StackQL where all files will be stored.
• --help: Show the help message and exit.

Options for build, test, and teardown include:

• --on-failure [rollback|ignore|error]: Define the action to be taken if the operation fails. Options include rollback, ignore, or treat as an error.
• --dry-run: Perform a simulation of the operation without making any actual changes.
• -e <TEXT TEXT>...: Specify additional environment variables in key-value pairs.
• --env-file TEXT: Path to a file containing environment variables.
• --log-level [DEBUG|INFO|WARNING|ERROR|CRITICAL]: Set the logging level to control the verbosity of logs during execution.

Example​

Using the activity_monitor stack we created previously using the init command, we can start defining a stack and defining the associated queries; here is the manifest file:

version: 1
name: activity_monitor
description: oss activity monitor stack
providers:
  - azure
globals:
  - name: subscription_id
    description: azure subscription id
    value: "{{ vars.AZURE_SUBSCRIPTION_ID }}"
  - name: location
    value: eastus
  - name: resource_group_name_base
    value: "activity-monitor"
resources:
  - name: monitor_resource_group
    description: azure resource group for activity monitor
    props:
      - name: resource_group_name
        description: azure resource group name
        value: "{{ globals.resource_group_name_base }}-{{ globals.stack_env }}"
	# more resources would go here...

globals.stack_env is a variable referencing the user-specified environment label.

Our stackql_resources directory must contain a .iql file (StackQL query file) with the same name as each resource defined in the resources key in the manifest file. Here is an example for stackql_resources/monitor_resource_group.iql:

/*+ createorupdate */
INSERT INTO azure.resources.resource_groups(
  resourceGroupName,
  subscriptionId,
  data__location
)
SELECT
  '{{ resource_group_name }}',
  '{{ subscription_id }}',
  '{{ location }}'

/*+ delete */
DELETE FROM azure.resources.resource_groups 
WHERE resourceGroupName = '{{ resource_group_name }}' AND subscriptionId = '{{ subscription_id }}'

Similarly, our stackql_tests directory must contain a .iql file (StackQL query file) with the same name as each resource defined in the stack. Here is an example for stackql_tests/monitor_resource_group.iql:

/*+ preflight */
SELECT COUNT(*) as count FROM azure.resources.resource_groups
WHERE subscriptionId = '{{ subscription_id }}'
AND resourceGroupName = '{{ resource_group_name }}'

/*+ postdeploy, retries=2, retry_delay=2 */
SELECT COUNT(*) as count FROM azure.resources.resource_groups
WHERE subscriptionId = '{{ subscription_id }}'
AND resourceGroupName = '{{ resource_group_name }}'
AND location = '{{ location }}'
AND JSON_EXTRACT(properties, '$.provisioningState') = 'Succeeded'

Now we can build, test, and teardown our example stack using these commands (starting with a dry-run, which will render the target queries without executing them):

# stackql-deploy build|test|teardown {stack_name} {stack_env} [{options}]
stackql-deploy build example_stack prd -e AZURE_SUBSCRIPTION_ID 00000000-0000-0000-0000-000000000000 --dry-run
stackql-deploy build example_stack prd -e AZURE_SUBSCRIPTION_ID 00000000-0000-0000-0000-000000000000
stackql-deploy test example_stack prd -e AZURE_SUBSCRIPTION_ID 00000000-0000-0000-0000-000000000000
stackql-deploy teardown example_stack prd -e AZURE_SUBSCRIPTION_ID 00000000-0000-0000-0000-000000000000

Give us your feedback! ⭐ us here!