DEV Community: Cristhian Carlos MAMANI CORI

A deep dive into building Text-to-SQL solutions using AI models, transforming natural language into SQL magic.

Cristhian Carlos MAMANI CORI — Sun, 05 Jul 2026 13:48:54 +0000

Introduction

As data grows exponentially, accessing and querying databases efficiently becomes a crucial bottleneck. Not everyone is a SQL expert, but almost everyone can ask questions in natural language. What if we could bridge this gap using Artificial Intelligence?

In this article, we'll explore SQL AI Database Solutions, specifically focusing on how to build a Text-to-SQL query data extractor. This technology leverages Large Language Models (LLMs) to translate human language into executable SQL queries, democratizing data access across organizations.

The Power of Text-to-SQL

Text-to-SQL systems work by understanding the schema of a database and the intent of a user's question. Instead of writing SELECT SUM(price) FROM sales WHERE category = 'Electronics' AND date >= '2023-01-01';, a user can simply ask, "What was the total revenue for electronics this year?"

The AI model acts as a translator, ensuring that the query is syntactically correct and semantically matches the user's request.

Real-World Code Example

Let's look at a practical demonstration of how this works under the hood. While a full implementation involves an LLM API (like Hugging Face or OpenAI) and prompt engineering, the core logic revolves around database connection and query execution.

Below is a Python demonstration showing how a simulated LLM output interacts with a SQLite database:

import sqlite3

def setup_database():
    """Sets up a sample SQLite database with some dummy data."""
    conn = sqlite3.connect('sales.db')
    cursor = conn.cursor()

    # Create table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY,
            product_name TEXT,
            quantity INTEGER,
            price REAL,
            order_date TEXT
        )
    ''')

    # Insert some dummy data
    cursor.execute("SELECT COUNT(*) FROM orders")
    if cursor.fetchone()[0] == 0:
        sample_data = [
            ('Laptop', 5, 1200.00, '2023-10-01'),
            ('Mouse', 15, 25.50, '2023-10-02'),
            ('Keyboard', 10, 45.00, '2023-10-03'),
            ('Monitor', 3, 300.00, '2023-10-04')
        ]
        cursor.executemany('INSERT INTO orders (product_name, quantity, price, order_date) VALUES (?, ?, ?, ?)', sample_data)
        conn.commit()

    return conn

def execute_sql(conn, query):
    """Executes a SQL query and returns the results."""
    try:
        cursor = conn.cursor()
        cursor.execute(query)
        columns = [description[0] for description in cursor.description]
        results = cursor.fetchall()
        return columns, results
    except Exception as e:
        return None, str(e)

if __name__ == "__main__":
    conn = setup_database()

    # Simulated Natural Language Query: "What is the total revenue from Laptops?"
    # The AI Model processes the prompt and the schema to generate this SQL:
    generated_sql = "SELECT SUM(quantity * price) as total_revenue FROM orders WHERE product_name = 'Laptop';"

    print(f"Generated SQL: {generated_sql}")

    columns, results = execute_sql(conn, generated_sql)

    if columns:
        print("\nResults:")
        print(columns)
        for row in results:
            print(row)

    conn.close()

Output:

Generated SQL: SELECT SUM(quantity * price) as total_revenue FROM orders WHERE product_name = 'Laptop';

Results:
['total_revenue']
(6000.0,)

Exploring the Full Repository & Live Demo

We have built an interactive web interface where you can experiment with this tool live!

🌐 Live Demo: SQL.AI Database Explorer

You can also inspect the code repository, which includes automation via GitHub Actions to ensure our scripts run flawlessly.

🔗 GitHub Repository: Research-Team-Work-N-01-SQL-AI-Database-Solutions

In the repository, we've implemented CI/CD pipelines (Automation) that automatically test the backend code execution and deploy the updated static frontend to GitHub Pages upon every commit, ensuring that our AI database interactions remain stable.

Conclusion & Discussion

Building a Text-to-SQL engine can drastically change how non-technical stakeholders interact with data. By combining robust database engineering with advanced AI, we empower users to uncover insights instantly.

To my team members: What do you think about the security implications of executing AI-generated SQL directly on a database? I'd love to read your abstracts and observations in the comments!

If you found this useful, please leave a like and a comment below! Let's reach our goal of 10 likes and comments! ❤️

A Practical Comparison of Modern CI/CD and Testing Management Tools

Cristhian Carlos MAMANI CORI — Sun, 05 Jul 2026 13:33:07 +0000

In modern software development, Continuous Integration and Continuous Deployment (CI/CD) pipelines have become an indispensable part of ensuring code quality and delivering features reliably. Choosing the right Testing Management Tool or CI/CD platform can make a huge difference in your team's productivity.

In this article, we will compare three of the most popular CI/CD and testing management tools: GitHub Actions, GitLab CI/CD, and Jenkins. We will explore their pros, cons, and look at real-world code examples for setting up an automated testing pipeline.

You can find the full demonstration code and repository for this comparison here:
🔗 Example Public Repository (GitHub)

1. GitHub Actions

GitHub Actions is deeply integrated into the GitHub ecosystem. It allows you to automate your build, test, and deployment pipeline directly from your repository.

Pros

Seamless Integration: If your code is already on GitHub, there is no need to configure third-party webhooks.
Vast Marketplace: You can reuse community-built actions for almost any task (e.g., setting up Node, configuring AWS credentials).
YAML Configuration: Workflows are easy to read and define.

Cons

Vendor Lock-in: Tied exclusively to GitHub. If you migrate your codebase to GitLab or Bitbucket, you will have to rewrite your pipelines.

Real-World Example

Here is a complete .github/workflows/ci.yml file from our demonstration repository that runs a suite of Node.js Jest tests on every push and pull request:

name: Node.js CI

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

jobs:
  build-and-test:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v3
    - name: Use Node.js 18.x
      uses: actions/setup-node@v3
      with:
        node-version: 18.x
        cache: 'npm'
    - run: npm ci
    - run: npm test

2. GitLab CI/CD

GitLab CI/CD is a built-in feature of GitLab. It is renowned for its robust pipeline features, deep integration with Docker, and Auto DevOps capabilities.

Pros

Built-in Container Registry: Excellent out-of-the-box support for Docker.
Comprehensive UI: Easy visualization of complex pipelines and environments.
Open Source: You can self-host GitLab and its CI runners for free.

Cons

Learning Curve: The .gitlab-ci.yml syntax can get complicated with advanced features like rules, needs, and includes.

Real-World Example

To achieve the exact same Node.js testing pipeline in GitLab, you would create a .gitlab-ci.yml file at the root of your project:

image: node:18

stages:
  - test

run_tests:
  stage: test
  script:
    - npm ci
    - npm test
  cache:
    key: ${CI_COMMIT_REF_SLUG}
    paths:
      - node_modules/
  rules:
    - if: $CI_PIPELINE_SOURCE == "merge_request_event"
    - if: $CI_COMMIT_BRANCH == "main"

3. Jenkins

Jenkins is the grandfather of CI/CD. It is an open-source automation server with a massive plugin ecosystem that can integrate with almost anything.

Pros

Flexibility: With over 1,000 plugins, you can customize it to do practically anything.
Agnostic: It doesn't care where your code is hosted (GitHub, GitLab, Bitbucket, on-prem).
Mature: Extremely stable with a massive community.

Cons

Maintenance Overhead: Requires you to manage the server, install updates, and configure plugins manually.
Clunky UI: The interface feels dated compared to modern alternatives (though Blue Ocean improves this somewhat).

Real-World Example

In Jenkins, you define your pipeline using a Jenkinsfile (Declarative Pipeline syntax):

pipeline {
    agent {
        docker {
            image 'node:18'
        }
    }
    stages {
        stage('Install') {
            steps {
                sh 'npm ci'
            }
        }
        stage('Test') {
            steps {
                sh 'npm test'
            }
        }
    }
}

The Demonstration Application

To prove our CI pipelines work, our repository contains a simple Express API with automated tests using Jest and Supertest.

Here is our application code (index.js):

const express = require('express');
const app = express();
const port = process.env.PORT || 3000;

app.get('/api/status', (req, res) => {
  res.status(200).json({ status: 'OK', message: 'CI/CD pipeline is working!' });
});

if (require.main === module) {
  app.listen(port, () => {
    console.log(`Server running on port ${port}`);
  });
}

module.exports = app;

And our automated test (index.test.js):

const request = require('supertest');
const app = require('./index');

describe('GET /api/status', () => {
  it('should return 200 OK and correct message', async () => {
    const res = await request(app).get('/api/status');
    expect(res.statusCode).toEqual(200);
    expect(res.body).toHaveProperty('status', 'OK');
    expect(res.body).toHaveProperty('message', 'CI/CD pipeline is working!');
  });
});

Conclusion

Choosing the right testing management and CI/CD tool depends heavily on your team's existing infrastructure:

Choose GitHub Actions if your source code already lives on GitHub and you want zero-maintenance CI/CD.
Choose GitLab CI/CD if you prefer a tightly integrated, all-in-one DevOps platform with superb Docker support.
Choose Jenkins if you have complex, highly customized requirements and the resources to maintain your own servers.

Regardless of the tool, automating your tests is the most important step towards reliable software delivery!

Applying API Testing Frameworks: A Comparative Guide

Cristhian Carlos MAMANI CORI — Sun, 05 Jul 2026 13:19:20 +0000

Welcome to this comprehensive comparative guide on API testing! This article is designed to showcase how to effectively apply API testing frameworks using real-world examples and automation, satisfying all the requirements of our team research on API testing frameworks.

We’ll be looking at two of the most popular tools in the ecosystem: Pytest (Python) and Supertest (Node.js), exploring their setup, code structure, and how to automate them using GitHub Actions. If you are interested in a broader perspective on testing tools and types, check out this great article: Top 10 API Testing Tools in 2020 (Detailed Reviews).

1. Why API Testing is Crucial

APIs form the backbone of modern web applications. Ensuring that these interfaces are reliable, secure, and performant is vital. By using testing frameworks, we can guarantee that our endpoints respond with the correct status codes, data types, and payloads, minimizing regressions when the codebase evolves.

Let's dive into some hands-on examples targeting the public JSONPlaceholder API.

2. Python: Pytest + Requests

Pytest is an incredibly robust testing framework for Python, and when combined with the requests library, it makes for a powerful API testing suite.

Setup

All you need is a requirements.txt:

pytest
requests

Demonstration Code

Here is a real-world example testing both GET and POST methods (test_api.py):

import pytest
import requests

BASE_URL = "https://jsonplaceholder.typicode.com"

def test_get_users():
    # Fetch list of users
    response = requests.get(f"{BASE_URL}/users")

    # Assertions
    assert response.status_code == 200
    assert len(response.json()) > 0
    assert "name" in response.json()[0]

def test_create_post():
    # Payload for the POST request
    payload = {
        "title": "API Testing with Pytest",
        "body": "It's super clean and easy!",
        "userId": 1
    }

    response = requests.post(f"{BASE_URL}/posts", json=payload)

    # Assertions for successful creation
    assert response.status_code == 201
    assert response.json()["title"] == "API Testing with Pytest"

Pytest makes assertions intuitive by just using the standard Python assert statement.

3. Node.js: Supertest + Jest

If your stack is built on JavaScript/TypeScript, Supertest (often used alongside Jest or Mocha) provides a fluent API for testing HTTP servers.

Setup

Configure your package.json:

{
  "name": "node-supertest",
  "scripts": {
    "test": "jest"
  },
  "dependencies": {
    "supertest": "^7.0.0"
  },
  "devDependencies": {
    "jest": "^29.7.0"
  }
}

Demonstration Code

Here is the equivalent test implemented in Node.js (test.js):

const request = require('supertest');
const BASE_URL = 'https://jsonplaceholder.typicode.com';

describe('API Testing with Supertest', () => {
  it('should fetch users successfully', async () => {
    const response = await request(BASE_URL).get('/users');

    expect(response.status).toBe(200);
    expect(response.body.length).toBeGreaterThan(0);
    expect(response.body[0]).toHaveProperty('name');
  });

  it('should create a new post', async () => {
    const payload = {
      title: 'API Testing with Supertest',
      body: 'Fluent and awesome!',
      userId: 1
    };

    const response = await request(BASE_URL)
      .post('/posts')
      .send(payload);

    expect(response.status).toBe(201);
    expect(response.body.title).toBe('API Testing with Supertest');
  });
});

Supertest excels at chainability, but using async/await with Jest's expect matchers produces very readable and clean code.

4. Comparison and Observations

Feature	Pytest (Python)	Supertest (Node.js)
Syntax	Minimalist, relies on raw `assert`	Descriptive `describe/it` blocks, rich matchers
Setup	Simple `pip install`	Requires a test runner (e.g., Jest/Mocha)
Ecosystem	Ideal for Data Science / Backend Python apps	Ideal for MERN/MEAN stack developers

Observation for Team Members: While both tools do a great job validating HTTP responses, your choice should align with your team's primary language. Pytest's lack of boilerplate is great for quick setups, whereas Supertest provides a very semantic and descriptive approach that integrates seamlessly with existing Node.js projects. If we want cross-platform test readability, using tools like Postman or Playwright might be another great approach!

5. CI/CD Automation with GitHub Actions

To ensure that our API tests run automatically, we have integrated GitHub Actions into our repository. This allows our team to confidently merge code!

Here is our .github/workflows/api-tests.yml:

name: API Tests Automation

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

jobs:
  pytest-tests:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v3
    - name: Set up Python
      uses: actions/setup-python@v4
      with:
        python-version: '3.10'
    - name: Install dependencies
      run: |
        python -m pip install --upgrade pip
        pip install -r demo-code/python-pytest/requirements.txt
    - name: Run Pytest
      run: pytest demo-code/python-pytest/

  supertest-tests:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v3
    - name: Use Node.js
      uses: actions/setup-node@v3
      with:
        node-version: '18.x'
    - name: Install dependencies
      working-directory: demo-code/node-supertest
      run: npm install
    - name: Run Jest/Supertest
      working-directory: demo-code/node-supertest
      run: npm test

This CI pipeline ensures both our Python and Node.js suites are executed on every push.

Summary

In this article, we demonstrated how to implement API testing using two top-tier frameworks (Pytest and Supertest). The complete code and automation configuration are hosted in our team's GitHub repository. Keep pushing quality software by incorporating automated API tests into your workflow! 🚀

Securing Your Terraform Infrastructure with Checkov and GitHub Actions

Cristhian Carlos MAMANI CORI — Sun, 05 Jul 2026 12:35:35 +0000

Infrastructure as Code (IaC) has revolutionized how we provision and manage cloud resources. Tools like Terraform, Pulumi, and OpenTofu allow us to define infrastructure using code, making it versionable, repeatable, and scalable. However, with great power comes great responsibility. Misconfigurations in IaC can lead to massive security breaches, such as publicly exposed data storage or overly permissive access roles.

This is where Static Application Security Testing (SAST) comes in. SAST tools analyze your source code to find security vulnerabilities before the code is deployed. In this article, we'll explore how to apply SAST to a Terraform project using Checkov, a popular open-source static analysis tool for IaC, and how to automate this process using GitHub Actions.

(Note: We are intentionally avoiding tfsec for this demonstration to explore other powerful alternatives).

Why Checkov?

Checkov, created by Bridgecrew (now part of Prisma Cloud), is a static code analysis tool for IaC. It scans cloud infrastructure provisioned using Terraform, Terraform plan, Cloudformation, Kubernetes, Dockerfile, Serverless, or ARM Templates and detects security and compliance misconfigurations. It includes hundreds of built-in policies covering security and compliance best practices for AWS, Azure, and Google Cloud.

The Demo Scenario: A Vulnerable S3 Bucket

Let's start by creating a simple Terraform configuration for an AWS S3 bucket. We will intentionally introduce a security misconfiguration: making the bucket public without encryption.

Create a file named main.tf:

# main.tf
provider "aws" {
  region = "us-east-1"
}

resource "aws_s3_bucket" "my_vulnerable_bucket" {
  bucket = "my-company-public-data-bucket-12345"
}

# Misconfiguration 1: Public Read Access
resource "aws_s3_bucket_acl" "example" {
  bucket = aws_s3_bucket.my_vulnerable_bucket.id
  acl    = "public-read"
}

If we were to deploy this, anyone on the internet could read the contents of this bucket. Let's see how Checkov can catch this before it reaches production.

Running Checkov Locally

You can easily install Checkov using Python's package manager, pip:

pip install checkov

Once installed, navigate to the directory containing your main.tf and run:

checkov -d .

The Output

Checkov will scan the directory and output a detailed report. You will see something like this:

       _               _              
   ___| |__   ___  ___| | _______   __
  / __| '_ \ / _ \/ __| |/ / _ \ \ / /
 | (__| | | |  __/ (__|   < (_) \ V / 
  \___|_| |_|\___|\___|_|\_\___/ \_/  

By Prisma Cloud | version: 3.1.0

terraform scan results:

Passed checks: 0, Failed checks: 3, Skipped checks: 0

Failed checks:
1. Check: CKV_AWS_20: "S3 Bucket has an ACL defined which allows public READ access."
        FAILED for resource: aws_s3_bucket_acl.example
        File: /main.tf:10-13
        Guide: https://docs.prismacloud.io/en/enterprise-edition/policy-reference/aws-policies/s3-policies/s3-1-acl-read-access

2. Check: CKV_AWS_19: "Ensure all data stored in the S3 bucket is securely encrypted at rest"
        FAILED for resource: aws_s3_bucket.my_vulnerable_bucket
        File: /main.tf:6-8

3. Check: CKV_AWS_21: "Ensure all data stored in the S3 bucket have versioning enabled"
        FAILED for resource: aws_s3_bucket.my_vulnerable_bucket
        File: /main.tf:6-8

Checkov successfully identified our public ACL misconfiguration (CKV_AWS_20) and also pointed out that we are missing encryption at rest and versioning!

Automating SAST with GitHub Actions

Running SAST tools locally is great, but to truly secure your pipeline, you must automate it. We can use GitHub Actions to run Checkov on every Pull Request. This ensures no vulnerable code can be merged into the main branch.

Create a .github/workflows/checkov.yml file in your repository:

name: Checkov IaC Scan

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

jobs:
  scan:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repository
        uses: actions/checkout@v3

      - name: Run Checkov action
        uses: bridgecrewio/checkov-action@master
        with:
          directory: .
          framework: terraform
          output_format: cli
          soft_fail: false # Set to true if you don't want the build to fail on violations

How it works:

Trigger: The workflow runs on any push or pull request to the main branch.
Checkout: It checks out your repository code.
Checkov Action: It uses the official bridgecrewio/checkov-action to scan the current directory (.) specifically looking for Terraform files.
Soft Fail: By setting soft_fail: false, the GitHub Action will fail if any vulnerabilities are found, blocking the Pull Request from being merged.

Fixing the Code

To fix our pipeline, we need to address the vulnerabilities in main.tf. Here is the secure version:

# main.tf
provider "aws" {
  region = "us-east-1"
}

resource "aws_s3_bucket" "my_secure_bucket" {
  bucket = "my-company-secure-data-bucket-12345"
}

# Fix 1: Private ACL
resource "aws_s3_bucket_acl" "example" {
  bucket = aws_s3_bucket.my_secure_bucket.id
  acl    = "private"
}

# Fix 2: Enable Versioning
resource "aws_s3_bucket_versioning" "versioning_example" {
  bucket = aws_s3_bucket.my_secure_bucket.id
  versioning_configuration {
    status = "Enabled"
  }
}

# Fix 3: Enable Server-Side Encryption
resource "aws_s3_bucket_server_side_encryption_configuration" "example" {
  bucket = aws_s3_bucket.my_secure_bucket.id

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

Now, if you commit these changes, the GitHub Action will run Checkov again, and it will pass successfully!

Conclusion

Securing Infrastructure as Code is not optional; it is a critical part of modern cloud architecture. By integrating a SAST tool like Checkov into your GitHub Actions pipeline, you establish a strong first line of defense against misconfigurations. It shifts security "left," empowering developers to fix issues before they ever see the light of a production environment.

Demo Repository: You can find the complete code for this demo in my GitHub Repository: https://github.com/Cristhian465/Research-Team-Work-N-02-Sast-tools-for-infraestructure

If you found this article helpful, please leave a like or comment below! Have you used Checkov or other SAST tools? Share your experience!

Automating Security in Python: A Hands-On Guide to SAST with Bandit and GitHub Actions

Cristhian Carlos MAMANI CORI — Sun, 05 Jul 2026 04:05:26 +0000

Introduction

In today's fast-paced development cycles, security can no longer be an afterthought. Static Application Security Testing (SAST) tools help developers catch vulnerabilities early by analyzing source code before it is compiled or deployed to production.

In this article, we will explore how to apply a SAST tool to a Python application. While tools like SonarQube or Snyk are popular, we will focus on Bandit, a powerful and lightweight tool designed specifically to find common security issues in Python code.

What is Bandit?

Bandit is an open-source SAST tool maintained by the Python Packaging Authority (PyPA). It parses Python code, builds an Abstract Syntax Tree (AST), and runs appropriate plugins against the AST nodes to detect security flaws like hardcoded passwords, injection vulnerabilities, and unsafe library usage.

The Demo Application

Let's create a simple Python application that intentionally contains some security vulnerabilities for Bandit to find. We'll simulate a basic script that has hardcoded credentials and uses dangerous functions.

Here is our app.py:

import subprocess
import yaml

# VULNERABILITY 1: Hardcoded credentials
DB_PASSWORD = "super_secret_password_123"

def run_command(user_input):
    # VULNERABILITY 2: Command injection risk (shell=True)
    print("Executing command...")
    subprocess.call(user_input, shell=True)

def parse_config(yaml_data):
    # VULNERABILITY 3: Unsafe YAML loading
    config = yaml.load(yaml_data)
    return config

if __name__ == "__main__":
    print("App started.")
    run_command("ls -la")

Running Bandit Locally

To test our code locally, we first install Bandit via pip:

pip install bandit

Then, we can run it against our app.py file:

bandit -r app.py

Bandit will immediately flag the issues we planted:

B105: hardcoded_password_string - Flags the hardcoded DB_PASSWORD.
B602: subprocess_popen_with_shell_equals_true - Warns about the potential command injection in subprocess.call.
B506: yaml_load - Warns about using the unsafe yaml.load function instead of yaml.safe_load.

Automating SAST with GitHub Actions

Running tools locally is great, but enforcing them automatically on every push is the best practice. Let's set up a GitHub Action to automatically run Bandit whenever code is pushed to our repository.

Create a file in your repository at .github/workflows/bandit.yml:

name: Bandit SAST Scan

on:
  push:
    branches: [ "main" ]

jobs:
  security-scan:
    runs-on: ubuntu-latest

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

    - name: Set up Python
      uses: actions/setup-python@v5
      with:
        python-version: '3.10'

    - name: Install dependencies
      run: pip install bandit

    - name: Run Bandit
      run: bandit -r . -f txt -o bandit-report.txt || true

    - name: Upload Bandit Report
      uses: actions/upload-artifact@v4
      with:
        name: bandit-security-report
        path: bandit-report.txt

How this automation works:

Triggers: The action runs on every push to the main branch.
Execution: It installs Bandit and runs it across the entire repository (-r .).
Artifacts: It generates a report and uploads it as an artifact, so developers can download and review the findings directly from the GitHub Actions tab. (Note: We use || true to prevent the workflow from failing immediately so the report can be uploaded).

Conclusion

Integrating a SAST tool like Bandit into your Python projects is a straightforward process that significantly enhances your code's security posture. By automating this process with GitHub Actions, you ensure that vulnerabilities are caught in the CI/CD pipeline before they ever reach production.

🔗 Check out the full demo code and automated GitHub Action in this repository:
https://github.com/Cristhian465/Trabajo-en-equipo-de-investigaci-n-N-01-Herramientas-Sast-para-aplicaciones