DEV Community: Abel Fernando PACOMPIA ORTIZ

# SQL AI Database Solutions: Code and Real-World Examples

Abel Fernando PACOMPIA ORTIZ — Sun, 05 Jul 2026 03:55:02 +0000

Public Example Repository: https://github.com/Abel-GG-777/sql-ai-database-solutions

Abstract

SQL AI Database Solutions combine database systems with artificial intelligence techniques that allow users to ask questions in natural language and receive SQL queries or query results. This article explains the concept of Text-to-SQL, why AI is useful for database querying, and how a small Python application can translate natural language into safe SQLite SELECT statements. The repository includes a working Streamlit demo, a realistic sample database, code examples, and tests. It also discusses real-world use cases, benefits, limitations, risks, and security considerations for production systems.

Introduction

Databases are essential in modern organizations because they store sales records, customer information, student data, inventory levels, support tickets, and many other operational facts. However, many users who need answers from a database do not know SQL. A sales manager may ask, "Which products generated the most revenue this month?" A student services worker may ask, "Which active students are enrolled in Computer Science?" These questions are easy for humans to understand, but they normally require technical SQL knowledge.

SQL AI Database Solutions address this gap by using artificial intelligence to help users communicate with databases in natural language. The goal is not to remove database professionals, but to make data access faster and more understandable while preserving security and correctness.

What Are SQL AI Database Solutions?

SQL AI Database Solutions are systems that use AI models, rules, or hybrid methods to help users interact with relational databases. In a typical solution, the user writes a natural language question, the system reads the database schema, an AI component generates an SQL query, and the database executes the query.

The solution may return the generated SQL, the query results, or both. In educational and professional environments, showing the generated SQL is useful because users can learn how natural language maps to database logic. It also supports transparency, review, and debugging.

What Is Text-to-SQL?

Text-to-SQL is the task of converting natural language into SQL. For example:

Natural language question:

Which products are low in stock?

Generated SQL:

SELECT
    sku,
    name,
    category,
    stock_quantity,
    reorder_level
FROM products
WHERE stock_quantity <= reorder_level
ORDER BY stock_quantity ASC;

Modern Text-to-SQL systems often use large language models trained or prompted to understand schemas, table relationships, and user intent. Some systems use general language models, while others use specialized models available through platforms such as Hugging Face.

Why AI Is Useful for Database Querying

AI is useful for database querying because it can reduce the technical barrier between users and data. A non-technical user can ask a question in normal language instead of memorizing SQL syntax. AI can also speed up the work of analysts by creating first drafts of queries, suggesting joins, and helping explore unfamiliar schemas.

AI also supports education. Students can compare their natural language questions with generated SQL and understand how tables, filters, grouping, and ordering work. This repository supports that learning goal by showing the generated query before displaying the result.

Architecture of the Demo Application

The demo application uses a simple and safe architecture:

The user enters a natural language question in Streamlit.
The application connects to a local SQLite database.
The schema reader extracts table and column information.
The query generator creates an SQL query.
The validator allows only SELECT queries and blocks dangerous keywords.
The query executor runs the query.
Streamlit displays the generated SQL and the results.

The main files are:

app.py: Streamlit user interface.
src/database.py: SQLite connection and database creation.
src/schema_reader.py: Table and column extraction.
src/query_generator.py: Optional Hugging Face generation and rule-based fallback.
src/query_executor.py: SQL validation and execution.
data/seed_data.sql: Sample database schema and realistic data.

Code Examples

Database Connection

The database module opens a SQLite connection and configures rows so they can be converted into dictionaries.

import sqlite3
from pathlib import Path

DEFAULT_DB_PATH = Path("data/sample.db")

def get_connection(db_path=DEFAULT_DB_PATH):
    connection = sqlite3.connect(db_path)
    connection.row_factory = sqlite3.Row
    return connection

SQLite is used because it is local, portable, and does not require exposing credentials.

Schema Extraction

The application reads the database schema so the query generator can understand available tables and columns.

def list_tables(connection):
    query = """
        SELECT name
        FROM sqlite_master
        WHERE type = 'table'
          AND name NOT LIKE 'sqlite_%'
        ORDER BY name;
    """
    return [row["name"] for row in connection.execute(query).fetchall()]

The extracted schema can be formatted as prompt context:

products(product_id INTEGER, sku TEXT, name TEXT, category TEXT, unit_price REAL)
sales(sale_id INTEGER, customer_id INTEGER, product_id INTEGER, sale_date TEXT)

Natural Language Prompt Input

In the Streamlit interface, users write a natural language question:

question = st.text_area(
    "Ask a question about the sample database",
    value="Which products are low in stock?"
)

This makes the interface accessible to users who do not know SQL.

SQL Query Generation

The demo supports optional Hugging Face integration using environment variables:

if os.getenv("HF_API_TOKEN"):
    return _generate_with_hugging_face(question, schema_text)
return generate_rule_based_sql(question)

If no token is configured, the app uses a rule-based fallback:

if "low" in normalized_question and "stock" in normalized_question:
    return """
    SELECT sku, name, category, stock_quantity, reorder_level
    FROM products
    WHERE stock_quantity <= reorder_level
    ORDER BY stock_quantity ASC
    """

This keeps the classroom demo functional without paid services or external APIs.

SQL Validation

The demo limits generated SQL to SELECT statements only:

if first_token != "SELECT":
    raise SqlValidationError("Only SELECT queries are allowed.")

It also blocks dangerous keywords:

BLOCKED_SQL_KEYWORDS = {
    "ALTER", "CREATE", "DELETE", "DROP", "INSERT",
    "REPLACE", "TRUNCATE", "UPDATE", "VACUUM"
}

This is basic validation for a sample project. It is not enough for production environments.

SQL Query Execution

The executor validates the SQL before running it:

def execute_select_query(connection, sql):
    safe_sql = validate_sql(sql)
    cursor = connection.execute(safe_sql)
    return [dict(row) for row in cursor.fetchall()]

Result Display

Streamlit displays the generated SQL and the returned rows:

st.code(safe_sql, language="sql")
st.dataframe(pd.DataFrame(rows), use_container_width=True)

This design helps users see both the query and the data result.

Real-World Use Cases

Business Dashboard Queries

A manager can ask:

What are the top selling products by revenue?

The system can generate a query that joins sales and products, groups by product, and orders by revenue. This supports quick dashboard exploration.

Student Records Search

A university office can ask:

Find active students in Computer Science.

The system can filter the students table by program and enrollment status. This is useful for academic advising, reporting, and enrollment management.

Sales Analysis

A sales analyst can ask:

Show total sales by month.

The SQL query can group records by month and calculate revenue. This helps identify trends over time.

Inventory Management

An operations employee can ask:

Which products are low in stock?

The query can compare stock_quantity with reorder_level. This supports restocking decisions.

Customer Support Data Extraction

A support supervisor can ask:

List open high priority support tickets.

The system can join tickets with customers and filter by status and priority. This helps teams prioritize urgent cases.

Advantages

SQL AI Database Solutions provide several advantages:

They make databases easier for non-technical users.
They reduce the time needed to write common queries.
They help analysts explore unfamiliar schemas.
They can support training and SQL education.
They improve transparency when generated SQL is shown to the user.
They can be integrated into dashboards, internal tools, and support systems.

Limitations and Risks

AI-generated SQL can be incorrect. It may choose the wrong table, misunderstand a business term, miss a filter, or create an expensive query. Natural language can also be ambiguous. For example, "best customers" could mean highest revenue, highest number of purchases, or highest satisfaction score.

Large language models can also hallucinate columns or tables that do not exist. Even when the SQL is valid, the result may not answer the user's actual question. For this reason, generated SQL should be reviewed, especially when used for financial, academic, legal, or operational decisions.

Security Considerations

Security is one of the most important topics in SQL AI systems. A production system should not simply generate SQL and run it with full database permissions. It should use a dedicated read-only database account, strict access control, audit logging, query limits, and strong validation.

This demo includes basic safety rules:

SQLite is used locally.
No database credentials are required.
Only SELECT statements are allowed.
Dangerous keywords such as DROP, DELETE, UPDATE, INSERT, ALTER, and TRUNCATE are blocked.
Hugging Face integration is optional and controlled by environment variables.

Production systems need stronger validation, permissions, logging, monitoring, rate limiting, and human review workflows. Sensitive data should be protected with masking, role-based access, and privacy rules.

Conclusion

SQL AI Database Solutions can make data access more natural, faster, and more inclusive. By combining natural language input, schema extraction, Text-to-SQL generation, validation, and result display, users can interact with relational databases without writing SQL manually.

This repository demonstrates the core idea using Python, Streamlit, and SQLite. It includes a local rule-based fallback so the project works without an API key, and it includes optional Hugging Face integration for experimentation with AI models. The project is intentionally simple, but it shows the foundation of real-world systems that help people talk to databases using natural language.

References

Hugging Face. "Models." https://huggingface.co/models
Streamlit Documentation. https://docs.streamlit.io/
SQLite Documentation. https://www.sqlite.org/docs.html
Python sqlite3 Documentation. https://docs.python.org/3/library/sqlite3.html
Spider: A Large-Scale Human-Labeled Dataset for Complex and Cross-Domain Semantic Parsing and Text-to-SQL Task. https://yale-lily.github.io/spider
Defog SQLCoder model collection. https://huggingface.co/defog

Testing Management Tools Comparative Study

Abel Fernando PACOMPIA ORTIZ — Sun, 05 Jul 2026 03:21:19 +0000

Public example repository: https://github.com/Abel-GG-777/testing-management-tools-comparison

Abstract

Testing management tools help software teams automate quality checks, run repeatable pipelines, publish feedback, and reduce the risk of releasing broken software. This article compares nine widely used tools: GitHub Actions, GitLab CI/CD, Jenkins, CircleCI, TeamCity, Travis CI, Bitbucket Pipelines, Tekton, and Harness. The comparison includes technical characteristics, advantages, disadvantages, real-world use cases, and configuration examples that run the sample Python unit tests included in this repository.

Sample Project Used for the Examples

The repository includes a small Python application in sample-app/. The application calculates a software quality score from testing metrics. The same basic command is used by all pipeline examples:

cd sample-app
PYTHONPATH=src python -m unittest discover -s tests

This gives every platform a common testing scenario: checkout the repository, prepare a Python runtime, and execute automated unit tests.

Comparison Table

Tool	Open source or commercial	Cloud or self-hosted	Language support	Ease of use	Scalability	Best use case
GitHub Actions	Commercial platform with free tiers; many open source actions	GitHub-hosted and self-hosted runners	Any language supported by runners or containers	High	High	GitHub-native automation
GitLab CI/CD	Open core plus commercial tiers	GitLab.com and self-managed	Any language through runners and images	High	High	Integrated DevSecOps
Jenkins	Open source	Self-hosted	Very broad through plugins and agents	Medium	Very high	Custom enterprise automation
CircleCI	Commercial	Cloud and self-hosted runners	Broad container and VM support	High	High	Fast containerized CI
TeamCity	Commercial	Cloud and self-hosted	Broad build runner support	Medium-high	High	Enterprise build chains
Travis CI	Commercial	Cloud	Many language environments	High	Medium	Simple hosted CI
Bitbucket Pipelines	Commercial	Bitbucket Cloud	Any language through Docker images	High	Medium-high	Bitbucket-native automation
Tekton	Open source	Kubernetes-native	Any language through containers	Medium	Very high	Platform engineering on Kubernetes
Harness	Commercial	SaaS and self-managed options	Broad CI and CD integrations	Medium-high	Very high	Enterprise delivery governance

1. GitHub Actions

Overview

GitHub Actions is GitHub's automation platform. Workflows are stored as YAML files under .github/workflows/ and are triggered by events such as pushes, pull requests, schedules, releases, or manual dispatches. Because it is built into GitHub, it is especially convenient for repositories hosted there.

Main Features

Event-driven workflows for repository activity.
GitHub-hosted runners for Linux, Windows, and macOS.
Self-hosted runners for private infrastructure.
Matrix builds for testing multiple versions or environments.
Marketplace actions for reusable automation.
Secrets, environments, and permissions for controlled execution.

Advantages

Very easy to adopt in GitHub repositories.
Strong ecosystem of reusable actions.
Good integration with pull requests, code review, releases, and repository permissions.
YAML configuration is version-controlled with the source code.

Disadvantages

Best experience is tied to GitHub.
Third-party marketplace actions require security review.
Complex workflows can become difficult to maintain.
Usage minutes and larger runners may create cost concerns.

Real-World Use Cases

Running unit tests and linters on every pull request.
Publishing packages after a tagged release.
Building Docker images and pushing them to a registry.
Deploying static sites or cloud services after approval.

Configuration Example

name: Python CI

on:
  push:
    branches: ["main"]
  pull_request:
    branches: ["main"]

jobs:
  test:
    runs-on: ubuntu-latest
    defaults:
      run:
        working-directory: sample-app
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.12"
      - run: python -m unittest discover -s tests
        env:
          PYTHONPATH: src

In this repository, the complete workflow is available at .github/workflows/ci.yml.

2. GitLab CI/CD

Overview

GitLab CI/CD is GitLab's integrated pipeline system. It uses a .gitlab-ci.yml file at the repository root and executes jobs through GitLab Runners. It is part of a broader DevSecOps platform that can include issues, merge requests, security scanning, artifacts, environments, and deployments.

Main Features

Pipeline stages and jobs defined in YAML.
Shared, group, project, and self-managed runners.
Docker image support for job environments.
Artifacts, caches, environments, and deployments.
Parent-child pipelines and multi-project pipelines.
Security and compliance features in GitLab tiers.

Advantages

Strong integration across planning, source control, testing, and deployment.
Flexible runner architecture.
Good support for monorepos and complex release workflows.
Built-in security scanning options.

Disadvantages

Most valuable when a team uses GitLab as its main platform.
Advanced enterprise features may require paid tiers.
YAML files can become large in complex organizations.
Runner management requires operational discipline.

Real-World Use Cases

Testing merge requests before approval.
Building container images and deploying to Kubernetes.
Running security scanning as part of a DevSecOps pipeline.
Coordinating multiple services with child pipelines.

Configuration Example

stages:
  - test

python_tests:
  stage: test
  image: python:3.12-slim
  variables:
    PYTHONPATH: "$CI_PROJECT_DIR/sample-app/src"
  script:
    - cd sample-app
    - python -m unittest discover -s tests

The repository includes this example in .gitlab-ci.yml.

3. Jenkins

Overview

Jenkins is an open source automation server with a long history in continuous integration. It is usually self-hosted and can be extended through a large plugin ecosystem. Jenkins pipelines are commonly written in a Jenkinsfile using declarative or scripted pipeline syntax.

Main Features

Declarative and scripted pipelines.
Large plugin ecosystem.
Distributed builds through agents.
Integration with many source control, testing, artifact, and deployment systems.
Credentials management and role-based access through plugins.
Support for custom infrastructure and legacy systems.

Advantages

Extremely flexible and customizable.
Open source and widely adopted.
Strong for enterprises with special infrastructure needs.
Can integrate with older tools that modern SaaS products may not support.

Disadvantages

Requires server administration, plugin maintenance, backups, and security patching.
Plugin conflicts can create maintenance risks.
User experience is less modern than many hosted platforms.
Scaling requires careful architecture.

Real-World Use Cases

Enterprise CI/CD across multiple programming languages.
Legacy application builds that require custom agents.
Highly customized deployment workflows.
Internal automation beyond software builds.

Configuration Example

pipeline {
    agent {
        docker {
            image 'python:3.12-slim'
        }
    }

    stages {
        stage('Unit Tests') {
            steps {
                dir('sample-app') {
                    withEnv(['PYTHONPATH=src']) {
                        sh 'python -m unittest discover -s tests'
                    }
                }
            }
        }
    }
}

The complete Jenkins example is available in Jenkinsfile.

4. CircleCI

Overview

CircleCI is a hosted CI/CD platform focused on fast builds, reusable configuration, and container-based execution. Configuration is stored in .circleci/config.yml. CircleCI also supports reusable packages called orbs.

Main Features

Docker, machine, macOS, and Windows execution environments.
Workflows that coordinate multiple jobs.
Dependency caching and workspaces.
Parallelism and test splitting.
Orbs for reusable integrations.
Cloud service with self-hosted runner options.

Advantages

Strong developer experience for containerized projects.
Good caching and parallel execution features.
Orbs reduce repeated configuration.
Useful insights for build performance.

Disadvantages

Commercial pricing can matter for larger teams.
Configuration is specific to CircleCI.
Advanced workflows can require learning platform-specific concepts.
Some environments have resource limits depending on plan.

Real-World Use Cases

Fast test pipelines for web services.
Container image builds.
Mobile application builds.
Multi-job workflows with approvals before deployment.

Configuration Example

version: 2.1

jobs:
  test:
    docker:
      - image: cimg/python:3.12
    steps:
      - checkout
      - run:
          name: Run Python unit tests
          command: |
            cd sample-app
            PYTHONPATH=src python -m unittest discover -s tests

workflows:
  test_sample_app:
    jobs:
      - test

The repository includes this file at .circleci/config.yml.

5. TeamCity

Overview

TeamCity is JetBrains' CI/CD product. It provides a strong web interface, build chains, project templates, test reporting, and configuration as code through Kotlin DSL. It is available as TeamCity Cloud and TeamCity On-Premises.

Main Features

Build configurations and build chains.
Kotlin DSL for version-controlled configuration.
Rich test reporting and build history.
Build agents for multiple environments.
Templates for repeated project patterns.
Strong IDE-style support for Kotlin configuration.

Advantages

Excellent visualization of build chains and test results.
Kotlin DSL is typed and maintainable for complex setups.
Strong enterprise build management capabilities.
Good fit for teams already using JetBrains tools.

Disadvantages

Commercial licensing may be required.
Less common in open source repositories than GitHub Actions or GitLab CI/CD.
Kotlin DSL requires some Kotlin familiarity.
On-premises deployments require server and agent maintenance.

Real-World Use Cases

Enterprise build pipelines with multiple dependent components.
Projects requiring detailed test trend history.
Organizations using JetBrains IDEs and TeamCity agents.
Complex build chains with shared templates.

Configuration Example

import jetbrains.buildServer.configs.kotlin.*
import jetbrains.buildServer.configs.kotlin.buildSteps.script

version = "2025.11"

project {
    buildType {
        id("SampleAppTests")
        name = "Sample App Unit Tests"

        steps {
            script {
                name = "Run Python unit tests"
                scriptContent = """
                    cd sample-app
                    python -m unittest discover -s tests
                """.trimIndent()
                param("env.PYTHONPATH", "src")
            }
        }
    }
}

The repository includes a TeamCity Kotlin DSL example in teamcity/kotlin-config.kt.

6. Travis CI

Overview

Travis CI is a hosted continuous integration service known for simple .travis.yml configuration files. It became popular in open source communities because repositories could enable CI quickly with minimal setup.

Main Features

YAML configuration through .travis.yml.
Built-in support for many programming languages.
Build stages and job matrices.
Branch and pull request builds.
Deployment integrations.
Hosted build environments.

Advantages

Simple for small and medium projects.
Easy language-specific setup.
Good for straightforward test pipelines.
Configuration is easy to read.

Disadvantages

Less flexible than Jenkins or Tekton for custom infrastructure.
Commercial model and usage limits may affect projects.
Smaller modern ecosystem than GitHub Actions.
Advanced deployment workflows may require more custom scripting.

Real-World Use Cases

Running tests for open source libraries.
Validating pull requests in small repositories.
Simple release automation.
Multi-version language testing.

Configuration Example

language: python
python:
  - "3.10"
  - "3.11"
  - "3.12"

script:
  - cd sample-app
  - PYTHONPATH=src python -m unittest discover -s tests

The repository includes this example in .travis.yml.

7. Bitbucket Pipelines

Overview

Bitbucket Pipelines is the CI/CD service built into Bitbucket Cloud. Configuration is stored in bitbucket-pipelines.yml, and builds commonly run inside Docker images. It is a natural option for teams that host code in Bitbucket.

Main Features

YAML pipelines stored in the repository.
Docker image based build steps.
Branch, pull request, tag, and custom pipelines.
Built-in caches and artifacts.
Deployment environments.
Integration with Bitbucket pull requests and permissions.

Advantages

Very convenient for Bitbucket Cloud repositories.
Simple Docker-based execution model.
Good integration with Atlassian tools.
Easy to understand for small projects.

Disadvantages

Closely tied to Bitbucket Cloud.
Not as flexible as Jenkins for custom infrastructure.
Large pipelines may need plan upgrades.
Smaller marketplace than GitHub Actions.

Real-World Use Cases

Testing pull requests in Bitbucket.
Deploying to staging or production after branch merges.
Building Docker images for services.
Running quality checks for Atlassian-centered teams.

Configuration Example

image: python:3.12-slim

pipelines:
  default:
    - step:
        name: Run unit tests
        script:
          - cd sample-app
          - PYTHONPATH=src python -m unittest discover -s tests

The repository includes this example in bitbucket-pipelines.yml.

8. Tekton

Overview

Tekton is an open source framework for creating CI/CD systems on Kubernetes. Instead of being a single hosted product, it provides Kubernetes custom resources such as Task, Pipeline, and PipelineRun. Tekton is often used by platform teams to build reusable CI/CD services.

Main Features

Kubernetes-native custom resources.
Reusable tasks and pipelines.
Container-based execution for every step.
Workspaces for shared data.
Parameters and results for pipeline composition.
Strong fit with GitOps and cloud-native infrastructure.

Advantages

Open source and highly extensible.
Scales with Kubernetes.
Good for platform engineering and internal developer platforms.
Avoids being tied to a single SaaS CI provider.

Disadvantages

Requires Kubernetes knowledge.
More complex initial setup than hosted CI tools.
User interface and developer experience depend on additional tooling.
Teams must operate cluster resources and security policies.

Real-World Use Cases

Building internal CI/CD platforms.
Running pipelines close to Kubernetes workloads.
Standardizing reusable build tasks across teams.
Cloud-native delivery with GitOps systems.

Configuration Example

apiVersion: tekton.dev/v1
kind: Task
metadata:
  name: run-python-tests
spec:
  workspaces:
    - name: source
  steps:
    - name: unit-tests
      image: python:3.12-slim
      workingDir: "$(workspaces.source.path)/repo/sample-app"
      env:
        - name: PYTHONPATH
          value: src
      script: |
        #!/usr/bin/env sh
        set -eu
        python -m unittest discover -s tests

The complete Tekton example is available in tekton/pipeline.yaml.

9. Harness

Overview

Harness is a commercial software delivery platform that includes CI, CD, feature flags, cloud cost management, governance, and verification features. It focuses on enterprise-scale delivery with templates, approvals, policies, and deployment visibility.

Main Features

CI and CD pipeline stages.
YAML-based pipeline definitions.
Connectors for Git, container registries, cloud providers, and Kubernetes.
Templates, approval gates, and governance features.
Deployment verification and rollback support.
Secrets management and role-based access controls.

Advantages

Strong enterprise governance and deployment controls.
Good support for complex multi-environment delivery.
Useful for organizations that need approvals, audit trails, and templates.
Combines testing automation with release management.

Disadvantages

Commercial product with platform-specific concepts.
More complex than simple CI tools for small projects.
Best value appears in larger organizations.
Requires setup of connectors, delegates, projects, and organizations.

Real-World Use Cases

Enterprise continuous delivery with approvals.
Deployments to Kubernetes and cloud environments.
Standardized pipelines across many teams.
Release governance with audit requirements.

Configuration Example

pipeline:
  name: Testing Management Tools Comparison
  identifier: testing_management_tools_comparison
  stages:
    - stage:
        name: Unit Tests
        identifier: unit_tests
        type: CI
        spec:
          cloneCodebase: true
          platform:
            os: Linux
            arch: Amd64
          runtime:
            type: Cloud
            spec: {}
          execution:
            steps:
              - step:
                  type: Run
                  name: Run Python unit tests
                  identifier: run_python_unit_tests
                  spec:
                    shell: Sh
                    command: |
                      cd sample-app
                      PYTHONPATH=src python -m unittest discover -s tests

The repository includes this reference example in harness/pipeline.yaml.

Discussion

The best testing management tool depends on the development context. GitHub Actions is the most direct option for this repository because the code is hosted on GitHub. GitLab CI/CD is excellent when the full GitLab platform is used. Jenkins remains a strong choice when a team needs extreme customization or must integrate legacy systems. CircleCI, Travis CI, and Bitbucket Pipelines are easier hosted options with different ecosystem strengths. TeamCity provides enterprise build management and a typed configuration model. Tekton is the strongest Kubernetes-native option for teams building internal platforms. Harness is most valuable where continuous delivery governance, approvals, and auditability are major requirements.

Conclusion

Testing management tools are not only test runners. They define how a team controls quality, automates feedback, protects releases, and creates repeatable delivery processes. A university-level comparison must therefore consider technical syntax, platform model, scalability, ecosystem, cost, and organizational fit. This repository demonstrates those factors with a working sample application and realistic configuration files for each tool.

References

GitHub Actions workflow syntax: https://docs.github.com/en/actions/reference/workflows-and-actions/workflow-syntax
GitLab CI/CD YAML syntax reference: https://docs.gitlab.com/ci/yaml/
Jenkins Pipeline syntax: https://www.jenkins.io/doc/book/pipeline/syntax/
CircleCI configuration reference: https://circleci.com/docs/reference/configuration-reference/
TeamCity Kotlin DSL documentation: https://www.jetbrains.com/help/teamcity/kotlin-dsl.html
Travis CI build customization: https://docs.travis-ci.com/user/customizing-the-build/
Bitbucket Pipelines configuration reference: https://support.atlassian.com/bitbucket-cloud/docs/bitbucket-pipelines-configuration-reference/
Tekton Pipelines documentation: https://tekton.dev/docs/pipelines/pipelines/
Harness pipeline YAML documentation: https://developer.harness.io/docs/platform/pipelines/yaml/

Applying Pytest and Requests for Real-World API Testing in a FastAPI Application

Abel Fernando PACOMPIA ORTIZ — Sat, 27 Jun 2026 07:41:53 +0000

Introduction

API testing is an essential practice for validating backend applications before they are deployed. In this project, I built a small FastAPI application and tested its endpoints using Pytest and FastAPI TestClient. The goal is to show a practical and academic example of how automated API tests can improve reliability.

What is API Testing?

API testing verifies the behavior of application endpoints. Instead of checking visual elements, API tests send HTTP requests and validate status codes, response bodies, data formats, and error handling.

Why API Testing is Important

Modern applications often depend on APIs to connect frontends, services, databases, and external systems. If an API endpoint fails, many parts of the system can be affected. Automated API tests help detect problems early and provide confidence when making changes.

API Testing Frameworks Comparative

Framework	Language / Ecosystem	Best use case	Automation support
Pytest + Requests / TestClient	Python	Developer-friendly automated API tests	Excellent with CI/CD tools
Postman + Newman	JavaScript / CLI ecosystem	Manual and automated API collections	Good for pipeline execution
Rest Assured	Java	API testing in Java projects	Strong with Maven, Gradle, and CI
Karate DSL	Java / DSL	BDD-style API tests with readable scenarios	Good CI/CD integration

Why I Chose Pytest

I chose Pytest because it is simple, readable, and widely used in Python projects. It allows developers to write test functions with plain assertions, and it integrates easily with FastAPI through TestClient.

Demo API with FastAPI

The demo API provides endpoints for a welcome message, health check, listing users, retrieving a user by id, and creating a new user in memory.

Example from app/main.py:

@app.get("/users/{user_id}", response_model=User)
def get_user_by_id(user_id: int) -> User:
    """Return one user by id or a clear 404 error."""
    for user in users:
        if user.id == user_id:
            return user

    raise HTTPException(
        status_code=status.HTTP_404_NOT_FOUND,
        detail=f"User with id {user_id} was not found.",
    )

Writing Real-World API Test Cases

The test suite checks successful responses, response content, list structures, user lookup, error handling, and validation failures.

Example from tests/test_api.py:

def test_health_check_returns_ok():
    # Validates that the health endpoint reports the API as available.
    response = client.get("/health")

    assert response.status_code == 200
    assert response.json() == {"status": "ok"}

Running the Tests Locally

To install dependencies and run the tests:

python -m pip install -r requirements.txt
pytest -v

Automating API Tests with GitHub Actions

GitHub Actions can run the test suite automatically when code is pushed or when a pull request is opened.

Example from .github/workflows/api-tests.yml:

name: API Tests with Pytest

on:
  push:
    branches: [ "main" ]
  pull_request:
    branches: [ "main" ]

jobs:
  api-tests:
    runs-on: ubuntu-latest

Results

The project includes seven API tests that validate the main behavior of the demo application. These tests can be executed locally with Pytest and automatically in GitHub Actions.

Conclusion

Pytest is a strong option for API testing in Python because it is readable, flexible, and easy to automate. Combined with FastAPI and GitHub Actions, it supports a simple but effective workflow for validating backend behavior before deployment.

GitHub Repository Link

GitHub repository: https://github.com/Abel-GG-777/api-testing-pytest-demo.git

Applying Checkov SAST to Detect Security Issues in Terraform Infrastructure as Code

Abel Fernando PACOMPIA ORTIZ — Sat, 27 Jun 2026 05:47:57 +0000

Introduction

Security issues in cloud infrastructure often start as small configuration mistakes. A public network rule, a missing encryption setting, or an overly permissive policy can create serious risk when infrastructure is deployed.

This demo project shows how to use Checkov as a Static Application Security Testing tool for Terraform Infrastructure as Code. The goal is academic and practical: detect insecure Terraform configuration before deploying anything to the cloud.

What is Infrastructure as Code?

Infrastructure as Code, or IaC, is the practice of defining infrastructure using code. Instead of manually creating cloud resources through a web console, teams describe resources in files that can be versioned, reviewed, tested, and automated.

Terraform is one of the most popular IaC tools. It allows teams to define providers, networks, storage, compute resources, permissions, and other infrastructure components using declarative configuration files.

What is SAST for IaC?

Static Application Security Testing normally means analyzing source code without running it. For IaC, the same idea applies to infrastructure definitions. A scanner can inspect Terraform files and identify risky patterns before the infrastructure is created.

This is useful because security feedback arrives earlier in the development lifecycle. Developers and DevOps teams can fix misconfigurations before they become real cloud exposure.

Why Checkov?

Checkov is a static analysis tool designed for Infrastructure as Code. It supports Terraform and can detect issues such as public access, missing encryption, weak network rules, and insecure cloud service configuration.

For this project, Checkov is a good fit because it is simple to run locally, easy to integrate into GitHub Actions, and focused on IaC security scanning.

Vulnerable Terraform demo

The vulnerable Terraform file defines an AWS provider, a security group, and an S3 bucket. The file is intentionally insecure for demonstration purposes only.

One important issue is SSH exposed to the entire internet:

ingress {
  description = "Insecure SSH access from anywhere"
  from_port   = 22
  to_port     = 22
  protocol    = "tcp"
  cidr_blocks = ["0.0.0.0/0"]
}

SSH open to 0.0.0.0/0 is insecure because any public IP address can attempt to connect. This increases the attack surface and can expose servers to brute-force attacks, credential attacks, and unauthorized access attempts.

The vulnerable version also includes fully open outbound traffic:

egress {
  description = "Overly permissive outbound access"
  from_port   = 0
  to_port     = 0
  protocol    = "-1"
  cidr_blocks = ["0.0.0.0/0"]
}

Fully open egress is too permissive because it allows outbound traffic to any destination, using any protocol and port. In a real environment, this can make data exfiltration or unauthorized external communication easier.

The S3 bucket is also basic and does not define extra protections such as public access blocking or explicit encryption:

resource "aws_s3_bucket" "vulnerable_bucket" {
  bucket = "checkov-sast-demo-vulnerable-bucket"
}

Running Checkov locally

Checkov can be installed and executed with Python:

python -m pip install checkov
checkov -d . --framework terraform --skip-path venv
checkov -d . --framework terraform --skip-path venv -o cli > checkov-report.txt

The -d . option tells Checkov to scan the current directory. The -o cli option prints the report in command-line format, and the final command stores the output in a text report.

Explaining findings

Checkov analyzes the Terraform files and compares them with security policies. In this demo, it should identify risky patterns such as public SSH exposure, missing S3 security controls, and overly permissive network configuration.

These findings matter because infrastructure misconfigurations can become real vulnerabilities after deployment. Detecting them statically helps reduce risk before cloud resources exist.

Secure Terraform version

The secure Terraform version restricts SSH to a trusted example IP address:

ingress {
  description = "SSH access from a trusted example IP"
  from_port   = 22
  to_port     = 22
  protocol    = "tcp"
  cidr_blocks = ["203.0.113.10/32"]
}

The 203.0.113.10/32 address is documentation-only example IP space. In a real project, this should be replaced with an approved corporate VPN, bastion host, or administrative IP range.

The secure file also restricts egress to HTTPS:

egress {
  description = "HTTPS outbound access only"
  from_port   = 443
  to_port     = 443
  protocol    = "tcp"
  cidr_blocks = ["0.0.0.0/0"]
}

For S3, the secure version enables public access blocking:

resource "aws_s3_bucket_public_access_block" "secure_bucket_public_access" {
  bucket = aws_s3_bucket.secure_bucket.id

  block_public_acls       = true
  block_public_policy     = true
  ignore_public_acls      = true
  restrict_public_buckets = true
}

Blocking public access helps prevent accidental exposure of data. This is especially important because S3 buckets are commonly used to store sensitive application, backup, log, or user data.

The secure version also enables server-side encryption:

resource "aws_s3_bucket_server_side_encryption_configuration" "secure_bucket_encryption" {
  bucket = aws_s3_bucket.secure_bucket.id

  rule {
    apply_server_side_encryption_by_default {
      sse_algorithm = "AES256"
    }
  }
}

S3 encryption is a good practice because it protects stored objects at rest. Even when access controls are also required, encryption adds another layer of defense.

GitHub Actions automation

The project includes a GitHub Actions workflow that runs Checkov automatically on pushes and pull requests to the main branch:

name: Checkov IaC SAST Scan

on:
  push:
    branches:
      - main
  pull_request:
    branches:
      - main

jobs:
  checkov:
    name: Run Checkov
    runs-on: ubuntu-latest

    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Run Checkov Terraform scan
        uses: bridgecrewio/checkov-action@master
        with:
          directory: .
          framework: terraform
          output_format: cli
          soft_fail: true

Integrating Checkov into GitHub Actions improves the DevSecOps workflow because every change can be scanned automatically before it is merged. This helps teams detect insecure Terraform code during code review instead of after deployment.

In this academic demo, soft_fail: true is used because the repository intentionally contains vulnerable Terraform code. This setting keeps the pipeline successful while still displaying the security findings in the workflow logs.

Conclusion

This project demonstrates how Checkov can be used to detect security issues in Terraform Infrastructure as Code. The vulnerable version shows common cloud misconfigurations, while the secure version demonstrates safer alternatives.

By combining local scanning with GitHub Actions automation, teams can introduce security checks early and continuously in the CI/CD process.

GitHub repository link placeholder

GitHub repository: https://github.com/Abel-GG-777/checkov-terraform-sast-demo.git

Applying Bandit SAST to Detect Vulnerabilities in a Python Flask Application

Abel Fernando PACOMPIA ORTIZ — Sat, 27 Jun 2026 03:26:12 +0000

Introduction

Security should be part of the development workflow, not only a final checklist before deployment. One practical way to introduce security earlier is by using Static Application Security Testing tools. In this article, I demonstrate how to use Bandit to analyze a small Python Flask application that intentionally contains insecure code.

GitHub repository: https://github.com/Abel-GG-777/bandit-sast-python-demo

What Is SAST?

Static Application Security Testing, commonly called SAST, is a security testing approach that analyzes source code without executing the application. Instead of waiting until runtime, SAST tools inspect code patterns and identify potential vulnerabilities early in the development lifecycle.

SAST is especially useful in academic and professional environments because it can be automated in CI/CD pipelines and used by developers before code is merged.

Why Bandit?

Bandit is a SAST tool designed specifically for Python. It scans Python files and reports common security issues such as hardcoded credentials, unsafe subprocess usage, weak cryptography, and risky configuration.

For this demo, Bandit is a good choice because it is lightweight, easy to install, simple to run from the command line, and easy to integrate with GitHub Actions.

Demo Vulnerable Code Explanation

The demo application is a small Flask project in app.py. It intentionally includes several insecure patterns so Bandit can detect them:

A hardcoded administrator password stored directly in the source code.
A subprocess.check_output call using shell=True.
User input from a query parameter concatenated into a shell command, creating a possible command injection risk.
The use of hashlib.md5, a weak hashing algorithm for security-sensitive use cases.
Flask running with debug=True.

These vulnerabilities are intentionally simple so the results are easy to explain in a classroom or presentation.

Running Bandit Locally

First, install the dependencies:

python -m pip install -r requirements.txt

Then run Bandit against the repository:

python -m bandit -r .

To generate a text report, run:

python -m bandit -r . -f txt -o bandit-report.txt

Explaining Findings

Bandit reports issues with identifiers, severity levels, confidence levels, file paths, and line numbers. In this project, the expected findings are related to hardcoded credentials, subprocess execution, shell usage, MD5 hashing, and debug mode.

The important part of the demo is not only seeing the warnings, but understanding why each pattern is risky. For example, shell=True becomes dangerous when combined with user-controlled input because the shell may interpret special characters as commands.

Secure Code Improvements

The corrected version is implemented in app_secure.py. It applies simple improvements:

The password is loaded from the ADMIN_PASSWORD environment variable.
shell=True is removed.
The subprocess call uses a list of arguments instead of a shell command string.
MD5 is replaced with SHA-256.
Flask debug mode is disabled.

This secure version keeps the application easy to understand while showing how vulnerable patterns can be improved.

GitHub Actions Automation

The repository includes a GitHub Actions workflow at .github/workflows/bandit.yml. It runs on pushes and pull requests targeting the main branch.

The workflow checks out the repository, configures Python 3.11, installs dependencies, and runs:

python -m bandit -r . -f txt

This demonstrates how SAST can become part of a continuous integration process. Every push or pull request can be scanned automatically before changes are accepted.

Conclusion

Bandit is a practical tool for introducing SAST into Python projects. It is simple to run locally, easy to automate, and useful for identifying common insecure coding patterns. This demo shows how a vulnerable Flask application can be scanned, how the findings can be explained, and how a corrected version can reduce the reported risks.

Applying Bandit SAST to Detect Vulnerabilities in a Python Flask Application

Abel Fernando PACOMPIA ORTIZ — Sat, 27 Jun 2026 03:26:12 +0000

Introduction

GitHub repository: https://github.com/Abel-GG-777/bandit-sast-python-demo

What Is SAST?

SAST is especially useful in academic and professional environments because it can be automated in CI/CD pipelines and used by developers before code is merged.

Why Bandit?

For this demo, Bandit is a good choice because it is lightweight, easy to install, simple to run from the command line, and easy to integrate with GitHub Actions.

Demo Vulnerable Code Explanation

The demo application is a small Flask project in app.py. It intentionally includes several insecure patterns so Bandit can detect them:

A hardcoded administrator password stored directly in the source code.
A subprocess.check_output call using shell=True.
User input from a query parameter concatenated into a shell command, creating a possible command injection risk.
The use of hashlib.md5, a weak hashing algorithm for security-sensitive use cases.
Flask running with debug=True.

These vulnerabilities are intentionally simple so the results are easy to explain in a classroom or presentation.

Running Bandit Locally

First, install the dependencies:

python -m pip install -r requirements.txt

Then run Bandit against the repository:

python -m bandit -r .

To generate a text report, run:

python -m bandit -r . -f txt -o bandit-report.txt

Explaining Findings

Secure Code Improvements

The corrected version is implemented in app_secure.py. It applies simple improvements:

The password is loaded from the ADMIN_PASSWORD environment variable.
shell=True is removed.
The subprocess call uses a list of arguments instead of a shell command string.
MD5 is replaced with SHA-256.
Flask debug mode is disabled.

This secure version keeps the application easy to understand while showing how vulnerable patterns can be improved.

GitHub Actions Automation

The repository includes a GitHub Actions workflow at .github/workflows/bandit.yml. It runs on pushes and pull requests targeting the main branch.

The workflow checks out the repository, configures Python 3.11, installs dependencies, and runs:

python -m bandit -r . -f txt

This demonstrates how SAST can become part of a continuous integration process. Every push or pull request can be scanned automatically before changes are accepted.