DEV Community: Hashira Belén Vargas Candia

SQL AI Database Solutions: Talking to Databases with Artificial Intelligence

Hashira Belén Vargas Candia — Sun, 05 Jul 2026 04:55:15 +0000

Public example repository: https://github.com/Hashiravc/text-to-sql-ai

Abstract

SQL AI Database Solutions allow users to ask questions in natural language and receive answers from a database without manually writing SQL. The idea sounds simple: the user asks a question, an AI model generates a SQL query, and the application executes it. In a real-world system, however, generating SQL is not enough. The application must understand the database schema, validate the generated query, restrict permissions, and return reliable results. This article explains a practical Text-to-SQL architecture using Python, SQLite, Streamlit-style examples, and a realistic sales and customer support scenario.

1. Why Text-to-SQL Matters

Many organizations store valuable information in relational databases: sales, customers, products, payments, inventory, attendance, support tickets, or academic records. The challenge is that not every user knows SQL. A manager, for example, may want to ask:

Which customers have unpaid invoices greater than 500?

A data analyst could solve that with JOIN, GROUP BY, and filters. A SQL AI solution tries to convert that business question into a correct SQL query and run it against the database.

The freeCodeCamp reference presents this idea as an AI-powered data extractor: a user writes a question, and the system uses AI to query a database. Hugging Face also highlights an important warning: Text-to-SQL should not be treated as a simple translation task, because a generated query can be wrong and still return results that look valid. That is why real applications need validation, restrictions, and sometimes an agent that can inspect and improve the output.

2. General Architecture

A real SQL AI application can be organized like this:

User
  -> Web interface or chat
  -> Database schema reader
  -> Prompt for the AI model
  -> Generated SQL
  -> Security validator
  -> Read-only database connection
  -> Table result or natural-language summary

The most important rule is that the model should not invent tables or columns. It needs database context: table names, column names, data types, and relationships.

3. Real-World Scenario: Sales and Customer Support

Imagine an online store that stores customers, orders, and payment information. The support team wants to answer questions such as:

Which customers still owe money?
Which products sold the most this month?
Which orders were partially paid?
Which customers purchased more than three times?

A minimal database schema could look like this:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  country TEXT NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  order_date TEXT NOT NULL,
  total REAL NOT NULL,
  paid REAL NOT NULL,
  status TEXT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

With that data, a natural-language question like this:

Which customers have unpaid invoices greater than 500?

can be converted into SQL:

SELECT
  c.name,
  c.email,
  SUM(o.total - o.paid) AS pending_amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status IN ('pending', 'partial')
GROUP BY c.id, c.name, c.email
HAVING pending_amount > 500
ORDER BY pending_amount DESC;

This is where Text-to-SQL becomes useful: the user understands the business question, but may not know the SQL syntax required to answer it.

4. Creating a Test Database with Python

The following example creates a SQLite database with customers and orders. It is small, but it represents a realistic sales case.

import sqlite3

DB_PATH = "sales_demo.db"

schema = """
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  country TEXT NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  order_date TEXT NOT NULL,
  total REAL NOT NULL,
  paid REAL NOT NULL,
  status TEXT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);
"""

customers = [
    (1, "Ana Torres", "ana@example.com", "Peru"),
    (2, "Luis Ramos", "luis@example.com", "Chile"),
    (3, "Marta Diaz", "marta@example.com", "Colombia"),
]

orders = [
    (1, 1, "2026-06-01", 900.00, 300.00, "partial"),
    (2, 1, "2026-06-15", 150.00, 150.00, "paid"),
    (3, 2, "2026-06-20", 780.00, 0.00, "pending"),
    (4, 3, "2026-06-22", 120.00, 120.00, "paid"),
]

with sqlite3.connect(DB_PATH) as conn:
    conn.executescript(schema)
    conn.executemany(
        "INSERT INTO customers (id, name, email, country) VALUES (?, ?, ?, ?)",
        customers,
    )
    conn.executemany(
        """
        INSERT INTO orders (id, customer_id, order_date, total, paid, status)
        VALUES (?, ?, ?, ?, ?, ?)
        """,
        orders,
    )

print("Database ready:", DB_PATH)

5. Reading the Database Schema Automatically

Before asking a model to generate SQL, we should provide the real schema. In SQLite, we can read it like this:

import sqlite3

def get_schema(db_path: str) -> str:
    with sqlite3.connect(db_path) as conn:
        tables = conn.execute(
            "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
        ).fetchall()

        schema_lines = []
        for (table_name,) in tables:
            columns = conn.execute(f"PRAGMA table_info({table_name})").fetchall()
            schema_lines.append(f"Table: {table_name}")
            for column in columns:
                _, name, data_type, not_null, _, pk = column
                required = "NOT NULL" if not_null else "NULL"
                primary_key = "PRIMARY KEY" if pk else ""
                schema_lines.append(f"  - {name}: {data_type} {required} {primary_key}")

        return "\n".join(schema_lines)

print(get_schema("sales_demo.db"))

Expected output:

Table: customers
  - id: INTEGER NULL PRIMARY KEY
  - name: TEXT NOT NULL
  - email: TEXT NOT NULL
  - country: TEXT NOT NULL
Table: orders
  - id: INTEGER NULL PRIMARY KEY
  - customer_id: INTEGER NOT NULL
  - order_date: TEXT NOT NULL
  - total: REAL NOT NULL
  - paid: REAL NOT NULL
  - status: TEXT NOT NULL

This context reduces errors because the model knows that customers.email exists, but it should not invent a column such as customer_phone if it is not in the schema.

6. Prompting the Model to Generate SQL

The prompt should be direct and include clear rules:

def build_prompt(schema: str, question: str) -> str:
    return f"""
You are a Text-to-SQL assistant.
Generate one SQLite SELECT query for the user's question.
Use only the tables and columns listed in the schema.
Do not generate INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, PRAGMA, or ATTACH.
Return only SQL, without markdown.

Schema:
{schema}

Question:
{question}
"""

In a real application, the model call can use Hugging Face, OpenAI, Groq, Gemini, Claude, or another provider. The important part is to separate responsibilities:

schema = get_schema("sales_demo.db")
question = "Which customers have unpaid invoices greater than 500?"
prompt = build_prompt(schema, question)

sql_query = llm.generate(prompt)  # Replace this with your selected AI provider
print(sql_query)

7. Validating Before Execution

AI-generated SQL should never be executed without controls. For a demo, we can use a basic validator:

def is_safe_select(sql: str) -> bool:
    normalized = " ".join(sql.strip().lower().split())
    forbidden = [
        "insert",
        "update",
        "delete",
        "drop",
        "alter",
        "create",
        "replace",
        "truncate",
        "pragma",
        "attach",
        "detach",
    ]

    if not normalized.startswith("select "):
        return False

    return not any(word in normalized for word in forbidden)

For production, this should be improved with a SQL parser such as sqlglot, allowlisted tables, a read-only database user, row limits, and query auditing.

8. Executing with Read-Only Permissions

The following example opens SQLite in read-only mode and adds LIMIT 50 if the model did not include one:

import sqlite3

def run_safe_query(db_path: str, sql: str) -> list[tuple]:
    if not is_safe_select(sql):
        raise ValueError("Unsafe SQL generated by model")

    safe_sql = sql.strip().rstrip(";")
    if " limit " not in f" {safe_sql.lower()} ":
        safe_sql += " LIMIT 50"

    readonly_uri = f"file:{db_path}?mode=ro"
    with sqlite3.connect(readonly_uri, uri=True) as conn:
        return conn.execute(safe_sql).fetchall()

Usage:

sql_query = """
SELECT
  c.name,
  c.email,
  SUM(o.total - o.paid) AS pending_amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status IN ('pending', 'partial')
GROUP BY c.id, c.name, c.email
HAVING pending_amount > 500
ORDER BY pending_amount DESC
"""

rows = run_safe_query("sales_demo.db", sql_query)
print(rows)

Possible output:

[('Luis Ramos', 'luis@example.com', 780.0), ('Ana Torres', 'ana@example.com', 600.0)]

9. Mini Interface with Streamlit

A simple interface can display the user question, generated SQL, and database results:

import streamlit as st

DB_PATH = "sales_demo.db"

st.title("Text-to-SQL AI Demo")
question = st.text_input("Ask a question about sales")

if st.button("Generate SQL") and question:
    schema = get_schema(DB_PATH)
    prompt = build_prompt(schema, question)

    sql_query = llm.generate(prompt)
    st.code(sql_query, language="sql")

    try:
        rows = run_safe_query(DB_PATH, sql_query)
        st.write(rows)
    except Exception as error:
        st.error(f"Could not execute the query: {error}")

This flow matches the idea from the Medium reference: a lightweight Streamlit app, a SQLite database, and a model call. The key improvement here is that we add validation and dynamic schema reading instead of depending only on a fixed prompt.

10. Agent-Based Approach

Hugging Face shows another useful improvement: using an agent with a SQL tool. The tool contains the table description, and the agent can iterate if it needs to correct the query.

A conceptual example with smolagents looks like this:

from sqlalchemy import create_engine, inspect, text
from smolagents import CodeAgent, InferenceClientModel, tool

engine = create_engine("sqlite:///sales_demo.db")

def describe_tables() -> str:
    inspector = inspect(engine)
    description = []
    for table in inspector.get_table_names():
        columns = inspector.get_columns(table)
        description.append(f"Table {table}:")
        for column in columns:
            description.append(f"  - {column['name']}: {column['type']}")
    return "\n".join(description)

@tool
def sql_engine(query: str) -> str:
    """
    Execute a read-only SQL query over the sales database.

    Args:
        query: A safe SQLite SELECT query.
    """
    if not is_safe_select(query):
        return "Rejected: only safe SELECT queries are allowed."

    with engine.connect() as connection:
        rows = connection.execute(text(query)).fetchall()
    return "\n".join(str(row) for row in rows)

sql_engine.description = f"""
Use this tool to answer business questions with SQL.
Available schema:
{describe_tables()}
"""

agent = CodeAgent(
    tools=[sql_engine],
    model=InferenceClientModel(model_id="meta-llama/Llama-3.1-8B-Instruct"),
)

agent.run("Which customers have pending debt greater than 500?")

This approach is useful when the database has multiple tables and the model needs to reason about joins. Even then, the agent should use a safe tool, not a full-permission database connection.

11. Best Practices for Real Projects

Use a read-only database user.
Send only the required schema to the model, not sensitive data.
Validate generated SQL with a parser, not only with string checks.
Block dangerous statements such as DROP, DELETE, UPDATE, or ALTER.
Add a default LIMIT to avoid large accidental queries.
Log the user question, generated SQL, execution time, and errors.
Show the generated SQL to the user for transparency.
Require human confirmation in sensitive domains such as health, finance, or personal data.
Evaluate the system with real business questions.
Measure accuracy: a query that runs successfully is not always correct.

12. Important Observation

The hardest part of a SQL AI solution is not connecting the model. The hardest part is building trust. A model can generate a query that runs without errors but answers the wrong question. For example, it may forget a date filter, use the wrong column, or create a JOIN that duplicates totals. This is why the architecture should include validation, tests with known questions, and human review for critical reports.

Conclusion

SQL AI Database Solutions make databases more accessible. They allow non-technical users to ask business questions in natural language and receive answers from real data. However, the solution must be designed carefully: schema context, validation, read-only permissions, limits, and monitoring are required. AI does not replace the judgment of a developer or analyst; it amplifies it when integrated with good engineering practices.

In short: Text-to-SQL is powerful, but it should be treated as an assisted query tool, not as an unrestricted door into the database.

References

freeCodeCamp: How to Talk to Any Database Using AI - Build Your Own SQL Query Data Extractor
Hugging Face: Text-to-SQL with smolagents
Medium: Building a Text-to-SQL Query Generator with Streamlit and Hugging Face

Practical Modern Observability: FastAPI, OpenTelemetry, Prometheus, Jaeger, and Grafana

Hashira Belén Vargas Candia — Wed, 01 Jul 2026 06:38:56 +0000

In modern, distributed architectures (like microservices or serverless applications), understanding why a system failed or why it is running slowly is a massive engineering challenge.

When a user complains that a checkout operation failed, you cannot simply look at a single server’s log file. That request may have traversed a Gateway, an Order service, a Payment processor, an Inventory system, and a database. You need a way to correlate all of these events across network and process boundaries.

This is where Observability comes in.

In this article, we will cover the core concepts of modern observability practices, explain the OpenTelemetry standard, and walk through a complete, real-world Python implementation. We will build a containerized FastAPI application instrumented with OpenTelemetry that exports metrics to Prometheus, sends traces to Jaeger, and compiles everything into a unified Grafana dashboard.

Repository: https://github.com/Hashiravc/Observability-Practices.git

1. What is Observability?

Often confused with monitoring, observability is the measure of how well you can infer the internal states of a system based on its external outputs.

Monitoring tells you when a system is broken (e.g., "CPU usage is 99%" or "HTTP 500 error rate is > 5%"). It is about tracking pre-defined metrics.
Observability tells you why it is broken (e.g., "The payment service is slow because database query SELECT * FROM payment_methods took 3 seconds under a specific trace context"). It is about asking questions you didn't anticipate.

To achieve observability, we rely on the three pillars of telemetry:

Metrics: Structured, aggregated numerical data that track resource usage or business KPIs over time. (e.g., CPU load, request count, revenue). Metrics are ideal for alerting.
Traces: Represent the end-to-end journey of a request as it flows through a distributed system. A trace is composed of one or more spans (individual units of work). Traces are crucial for isolating latency bottlenecks and database serialization errors.
Logs: Timestamped, text-based entries describing specific, discrete events. In highly observable systems, logs are structured (JSON-formatted) and inject trace IDs to correlate logs directly with traces.

2. The OpenTelemetry Standard

Historically, implementing observability meant using proprietary libraries from vendor platforms (like Datadog, New Relic, or Dynatrace). If you wanted to change platforms, you had to rewrite your application instrumentation.

OpenTelemetry (OTel) is an open-source, vendor-neutral collection of APIs, SDKs, and tools hosted by the Cloud Native Computing Foundation (CNCF). It provides a single standard for gathering metrics, logs, and traces.

By instrumenting your code with the OpenTelemetry API, you can swap out telemetry backends (e.g., from local Prometheus/Jaeger to Datadog or AWS CloudWatch) by changing simple configuration variables—without modifying a single line of application code.

3. Demo Application Architecture

To demonstrate observability practices in a real-world scenario, we will build a containerized E-Commerce application with the following architecture:

  [ Traffic Generator ]
           │
           ▼ (HTTP request)
     [ FastAPI Web ] ──────────────► [ SQLite DB ]
           │ (Exposes /metrics)
           ├───────────────────────► [ Prometheus ] ──────┐
           │                                              ▼
           ├─► (OTLP Traces gRPC) ──► [ Jaeger ] ────► [ Grafana Dashboard ]
           │
        (W3C Trace Headers)
           │
           ▼
   [ /inventory/deduct ]

When a client hits /checkout:

A parent span checkout_transaction is created.
A database entry is written inside a child span (db_create_order).
An HTTP request is made to the /inventory/deduct route. To trace this across network boundaries, we manually inject W3C Trace Context headers.
The inventory service extracts the context and begins a child span inventory_deduct_span to deduct item stock.
If we request checkout of specific items, we simulate network latency (sleep) or write lock deadlocks (500 errors).

4. Code Walkthrough

Let us look at how this is implemented.

Dependency Configuration (`requirements.txt`)

We install FastAPI, Uvicorn, SQLAlchemy, and the official OpenTelemetry SDK packages, including the Prometheus metric reader and OTLP trace exporter:

fastapi==0.111.0
uvicorn==0.30.1
sqlalchemy==2.0.31
httpx==0.27.0
prometheus-client==0.20.0
opentelemetry-api==1.25.0
opentelemetry-sdk==1.25.0
opentelemetry-instrumentation-fastapi==0.46b0
opentelemetry-exporter-otlp==1.25.0
opentelemetry-exporter-prometheus==1.25.0

Telemetry Configuration (`app/telemetry.py`)

This file initializes OpenTelemetry, registers exporters, and defines custom application-level metrics.

import os
from fastapi import FastAPI
from opentelemetry import trace, metrics
from opentelemetry.sdk.trace import TracerProvider
from opentelemetry.sdk.trace.export import BatchSpanProcessor, ConsoleSpanExporter
from opentelemetry.exporter.otlp.proto.grpc.trace_exporter import OTLPSpanExporter
from opentelemetry.sdk.metrics import MeterProvider
from opentelemetry.exporter.prometheus import PrometheusMetricReader
from opentelemetry.sdk.resources import Resource, SERVICE_NAME
from opentelemetry.instrumentation.fastapi import FastAPIInstrumentor

# Define service metadata
SERVICE_NAME_VALUE = os.getenv("OTEL_SERVICE_NAME", "order-service")
resource = Resource.create({SERVICE_NAME: SERVICE_NAME_VALUE})

# 1. Tracing Setup
tracer_provider = TracerProvider(resource=resource)

# Configure OTLP Exporter (sending traces to Jaeger via gRPC)
otlp_endpoint = os.getenv("OTEL_EXPORTER_OTLP_ENDPOINT", "http://localhost:4317")
try:
    otlp_exporter = OTLPSpanExporter(endpoint=otlp_endpoint, insecure=True)
    span_processor = BatchSpanProcessor(otlp_exporter)
    tracer_provider.add_span_processor(span_processor)
except Exception as e:
    print(f"Could not initialize OTLP exporter: {e}. Falling back to console.")
    console_exporter = ConsoleSpanExporter()
    span_processor = BatchSpanProcessor(console_exporter)
    tracer_provider.add_span_processor(span_processor)

trace.set_tracer_provider(tracer_provider)
tracer = trace.get_tracer("order-service-tracer")

# 2. Metrics Setup (Prometheus Pull Exporter)
prometheus_reader = PrometheusMetricReader()
meter_provider = MeterProvider(resource=resource, metric_readers=[prometheus_reader])
metrics.set_meter_provider(meter_provider)
meter = metrics.get_meter("order-service-meter")

# Custom business and performance metrics
checkout_counter = meter.create_counter(
    name="order_checkouts_total",
    description="Total number of checkout requests processed",
    unit="1"
)

revenue_counter = meter.create_counter(
    name="order_revenue_total",
    description="Total revenue generated from checkouts",
    unit="USD"
)

checkout_errors = meter.create_counter(
    name="order_checkout_errors_total",
    description="Total failed checkouts",
    unit="1"
)

def setup_telemetry(app: FastAPI):
    """
    Instruments the FastAPI application.
    """
    # Auto-instrumentation hooks FastAPI request durations and details
    FastAPIInstrumentor.instrument_app(
        app,
        tracer_provider=tracer_provider,
        meter_provider=meter_provider
    )

Application Implementation (`app/main.py`)

Here we set up our FastAPI endpoints. Notice how trace context is injected on /checkout and extracted on /inventory/deduct using W3C Trace Context propagation.

import os
import time
import random
import httpx
from fastapi import FastAPI, Depends, HTTPException, Header, Response, Request
from pydantic import BaseModel
from sqlalchemy.orm import Session
from prometheus_client import generate_latest, CONTENT_TYPE_LATEST

from app.database import init_db, get_db, Order, Inventory
from app.telemetry import (
    setup_telemetry,
    tracer,
    checkout_counter,
    revenue_counter,
    checkout_errors
)
from opentelemetry import trace
from opentelemetry.trace.propagation.tracecontext import TraceContextTextMapPropagator
from opentelemetry.trace import StatusCode

app = FastAPI(title="E-Commerce Observability Demo API")
setup_telemetry(app)

INVENTORY_URL = os.getenv("INVENTORY_URL", "http://localhost:8000")

class CheckoutRequest(BaseModel):
    item: str
    quantity: int
    price: float

class InventoryDeductRequest(BaseModel):
    item: str
    quantity: int

@app.on_event("startup")
def on_startup():
    init_db()

@app.get("/")
def home():
    return {"message": "Welcome to the E-Commerce Observability Demo API!"}

@app.post("/checkout")
async def checkout(request_data: CheckoutRequest, db: Session = Depends(get_db)):
    checkout_counter.add(1, {"item": request_data.item})

    # Start parent trace span
    with tracer.start_as_current_span("checkout_transaction") as span:
        span.set_attribute("order.item", request_data.item)
        span.set_attribute("order.quantity", request_data.quantity)
        span.set_attribute("order.price_per_unit", request_data.price)

        # Child database span
        with tracer.start_as_current_span("db_create_order") as db_span:
            db_order = Order(
                item=request_data.item,
                quantity=request_data.quantity,
                price=request_data.price,
                status="PENDING"
            )
            db.add(db_order)
            db.commit()
            db.refresh(db_order)
            db_span.set_attribute("db.order_id", db_order.id)
            order_id = db_order.id

        # Downstream HTTP client span + Trace context injection
        headers = {}
        TraceContextTextMapPropagator().inject(headers) # Injects 'traceparent' header

        with tracer.start_as_current_span("http_call_inventory_service") as http_span:
            http_span.set_attribute("http.url", f"{INVENTORY_URL}/inventory/deduct")
            try:
                async with httpx.AsyncClient() as client:
                    response = await client.post(
                        f"{INVENTORY_URL}/inventory/deduct",
                        json={"item": request_data.item, "quantity": request_data.quantity},
                        headers=headers,
                        timeout=5.0
                    )
            except Exception as exc:
                checkout_errors.add(1, {"item": request_data.item, "reason": "inventory_network_error"})
                span.set_status(StatusCode.ERROR, f"Inventory communication failed: {exc}")
                http_span.record_exception(exc)
                db_order.status = "FAILED"
                db.commit()
                raise HTTPException(status_code=502, detail="Inventory service network error")

            if response.status_code != 200:
                checkout_errors.add(1, {"item": request_data.item, "reason": "inventory_rejection"})
                span.set_status(StatusCode.ERROR, f"Inventory check rejected: {response.text}")
                db_order.status = "FAILED"
                db.commit()
                raise HTTPException(status_code=response.status_code, detail=f"Inventory deduction rejected: {response.text}")

        # Success path
        db_order.status = "COMPLETED"
        db.commit()

        total_revenue = request_data.quantity * request_data.price
        revenue_counter.add(total_revenue, {"item": request_data.item})
        span.set_attribute("order.status", "COMPLETED")
        span.set_attribute("order.revenue", total_revenue)

        return {"order_id": order_id, "status": "COMPLETED", "item": request_data.item, "total_price": total_revenue}

@app.post("/inventory/deduct")
def deduct_inventory(request_data: InventoryDeductRequest, request: Request, db: Session = Depends(get_db)):
    # Extract trace parent header from HTTP client call
    carrier = {"traceparent": request.headers.get("traceparent", "")}
    extracted_context = TraceContextTextMapPropagator().extract(carrier=carrier)

    with tracer.start_as_current_span("inventory_deduct_span", context=extracted_context) as span:
        span.set_attribute("inventory.item", request_data.item)
        span.set_attribute("inventory.deduction_quantity", request_data.quantity)

        # Simulate network latency (2 seconds) for large smartphone orders
        if request_data.item == "smartphone" and request_data.quantity >= 3:
            delay = random.uniform(1.0, 2.5)
            span.set_attribute("simulation.latency_added", delay)
            time.sleep(delay)

        # Simulate db locks/deadlock conflicts (500 Server Error) for large laptop orders
        if request_data.item == "laptop" and request_data.quantity >= 2:
            if random.random() < 0.6:
                span.set_status(StatusCode.ERROR, "Simulated deadlock conflict")
                raise HTTPException(status_code=500, detail="Database deadlock conflict during write")

        db_item = db.query(Inventory).filter(Inventory.item == request_data.item).first()
        if not db_item or db_item.quantity < request_data.quantity:
            span.set_status(StatusCode.ERROR, "Insufficient stock")
            raise HTTPException(status_code=400, detail="Insufficient stock")

        db_item.quantity -= request_data.quantity
        db.commit()
        return {"status": "SUCCESS", "remaining_stock": db_item.quantity}

@app.get("/metrics")
def metrics_endpoint():
    """
    Exposes metrics scraped by Prometheus server.
    """
    return Response(generate_latest(), media_type=CONTENT_TYPE_LATEST)

5. Orchestrating the Stack with Docker Compose

To run this observability stack locally, we define a multi-container deployment using Docker Compose. We launch:

Our FastAPI App on port 8000.
Prometheus on port 9090 to collect the metric endpoints.
Jaeger on port 16686 (Web UI) and 4317 (gRPC collector) to receive tracing spans.
Grafana on port 3000 to aggregate datasources.

Here is the docker-compose.yml config:

version: "3.8"

services:
  web:
    build: .
    ports:
      - "8000:8000"
    environment:
      - OTEL_SERVICE_NAME=order-service
      - OTEL_EXPORTER_OTLP_ENDPOINT=http://jaeger:4317
      - INVENTORY_URL=http://web:8000
    depends_on:
      - jaeger

  jaeger:
    image: jaegertracing/all-in-one:1.57
    ports:
      - "16686:16686" # Web UI
      - "4317:4317"   # OTLP gRPC receiver
    environment:
      - COLLECTOR_OTLP_ENABLED=true

  prometheus:
    image: prom/prometheus:v2.52.0
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
    ports:
      - "9090:9090"

  grafana:
    image: grafana/grafana:11.0.0
    volumes:
      - ./grafana/provisioning:/etc/grafana/provisioning
    ports:
      - "3000:3000"
    environment:
      - GF_AUTH_ANONYMOUS_ENABLED=true
      - GF_AUTH_ANONYMOUS_ORG_ROLE=Admin
    depends_on:
      - prometheus
      - jaeger

6. How it Looks in Action

When you run docker compose up --build, start the traffic generator (generate_traffic.py), and log in to Grafana, you will see a unified, correlated interface.

High-Level Metric Monitoring: Grafana metrics charts track the overall request throughput, purchase revenue, and HTTP error rate.
Drill-down to Latency: Under the average latency chart, when a spike occurs, you notice the /checkout route takes 2.2 seconds.
Distributed Tracing Correlation: Because the trace context was propagated from /checkout down to /inventory/deduct, you can jump directly from Grafana into Jaeger to examine the trace hierarchy.
Root-Cause Isolation: In Jaeger, you see that the parent span checkout_transaction spent 95% of its execution time inside the child span inventory_deduct_span. Looking at the span details, you see simulation.latency_added: 2.15, pointing directly to the simulated network lag!

7. Observability Best Practices for Production

If you are implementing observability in production, keep these best practices in mind:

Use an OpenTelemetry Collector: In local development, exporting telemetry directly from the app to Prometheus/Jaeger is fine. In production, however, your application should stream telemetry data asynchronously via OTLP to a local OTel Collector daemon. The Collector processes, batches, and exports the data to your telemetry backend. This prevents application slowdowns during backend bottlenecks.
Inject Trace Context into Logs: Configure your logger (e.g., Python logging or structlog) to format logs as JSON and inject the active trace ID (trace.get_current_span().get_span_context().trace_id). This bridges the gap between logging and tracing.
Watch Out for Metric Cardinality: When defining metrics, do not add attributes with high cardinality (e.g. user IDs, order IDs, or session IDs) as tags. Adding tags with infinite possible values will bloat your metric database memory and crash Prometheus. Use traces or logs for high-cardinality attributes.
Enforce Sampling: Sending 100% of traces to your collector is expensive and unnecessary for high-traffic applications. Configure adaptive or head-based sampling (e.g., sample 5% of successful checkouts, but 100% of errors).

Conclusion

Observability is not just about installing software packages; it is an engineering discipline. Adopting OpenTelemetry ensures you decouple your application logic from any particular monitoring vendor.

Setting up unified tracing and metric aggregation, as shown in our E-Commerce demo, allows your team to go from identifying a high-level error spike to isolating the exact line of failing code or latency bottleneck in seconds.

Enterprise Design Patterns: The Repository Pattern in Practice

Hashira Belén Vargas Candia — Wed, 01 Jul 2026 06:05:31 +0000

Curso: Patrones de Software
Basado en: Catalog of Patterns of Enterprise Application Architecture, Martin Fowler

1. Introducción

Martin Fowler, en su libro Patterns of Enterprise Application Architecture (2002), documentó un conjunto de soluciones recurrentes a problemas que aparecen una y otra vez al construir software empresarial: cómo organizar la lógica de negocio, cómo mapear objetos a bases de datos, cómo estructurar la capa de presentación, entre otros. Su catálogo online agrupa estos patrones en categorías como Domain Logic Patterns, Data Source Architectural Patterns, Object-Relational Behavioral Patterns, etc.

En este artículo desarrollo el patrón Repository, perteneciente a la categoría de Object-Relational Behavioral Patterns, y lo acompaño con una implementación real en Python.

2. ¿Qué es el patrón Repository?

Fowler define el Repository como una capa que media entre el dominio y la capa de acceso a datos, comportándose como una colección de objetos en memoria. La idea central es que el resto de la aplicación (los casos de uso, los servicios, los controladores) no debería saber si los datos vienen de una base de datos SQL, de un archivo, de una API externa o de una lista en memoria. El Repository oculta esa complejidad detrás de una interfaz simple: add(), get(), list(), remove().

Problema que resuelve

Sin este patrón, es común ver consultas SQL o código de ORM esparcido por toda la lógica de negocio. Esto genera:

Acoplamiento fuerte entre la lógica de negocio y la tecnología de persistencia.
Dificultad para probar el código (hay que levantar una base de datos real para cada test).
Duplicación de queries similares en distintos puntos del sistema.

Solución

Se define una interfaz abstracta (un contrato) que declara las operaciones disponibles sobre una colección de objetos de dominio, y luego se implementan una o varias versiones concretas de esa interfaz (por ejemplo, una para SQLite, otra en memoria para pruebas, otra para MongoDB, etc.). El resto del sistema solo depende de la interfaz, nunca de la implementación concreta.

3. Ejemplo real de código (Python)

El ejemplo modela un sistema simple de gestión de pedidos (Orders) para una tienda. Se define:

Una entidad de dominio Order.
Una interfaz abstracta OrderRepository.
Dos implementaciones: InMemoryOrderRepository (para pruebas unitarias) y SqliteOrderRepository (para producción).
Un caso de uso (OrderService) que depende únicamente de la interfaz.

3.1 Entidad de dominio

# src/domain.py
from dataclasses import dataclass, field
from datetime import datetime
from uuid import uuid4


@dataclass
class Order:
    customer_name: str
    total_amount: float
    id: str = field(default_factory=lambda: str(uuid4()))
    created_at: datetime = field(default_factory=datetime.utcnow)

    def apply_discount(self, percentage: float) -> None:
        if not 0 <= percentage <= 100:
            raise ValueError("El descuento debe estar entre 0 y 100")
        self.total_amount -= self.total_amount * (percentage / 100)

3.2 Interfaz del Repository (el contrato)

# src/repository.py
from abc import ABC, abstractmethod
from typing import List, Optional

from .domain import Order


class OrderRepository(ABC):
    """Contrato que toda implementación de persistencia debe cumplir.
    La lógica de negocio solo conoce esta interfaz, nunca los detalles
    de SQLite, memoria, MongoDB, etc.
    """

    @abstractmethod
    def add(self, order: Order) -> None: ...

    @abstractmethod
    def get(self, order_id: str) -> Optional[Order]: ...

    @abstractmethod
    def list_all(self) -> List[Order]: ...

    @abstractmethod
    def remove(self, order_id: str) -> None: ...

3.3 Implementación en memoria (ideal para tests)

# src/in_memory_repository.py
from typing import Dict, List, Optional

from .domain import Order
from .repository import OrderRepository


class InMemoryOrderRepository(OrderRepository):
    def __init__(self) -> None:
        self._orders: Dict[str, Order] = {}

    def add(self, order: Order) -> None:
        self._orders[order.id] = order

    def get(self, order_id: str) -> Optional[Order]:
        return self._orders.get(order_id)

    def list_all(self) -> List[Order]:
        return list(self._orders.values())

    def remove(self, order_id: str) -> None:
        self._orders.pop(order_id, None)

3.4 Implementación con SQLite (persistencia real)

# src/sqlite_repository.py
import sqlite3
from datetime import datetime
from typing import List, Optional

from .domain import Order
from .repository import OrderRepository


class SqliteOrderRepository(OrderRepository):
    def __init__(self, db_path: str = "orders.db") -> None:
        self._conn = sqlite3.connect(db_path)
        self._create_table()

    def _create_table(self) -> None:
        self._conn.execute(
            """
            CREATE TABLE IF NOT EXISTS orders (
                id TEXT PRIMARY KEY,
                customer_name TEXT NOT NULL,
                total_amount REAL NOT NULL,
                created_at TEXT NOT NULL
            )
            """
        )
        self._conn.commit()

    def add(self, order: Order) -> None:
        self._conn.execute(
            "INSERT OR REPLACE INTO orders VALUES (?, ?, ?, ?)",
            (order.id, order.customer_name, order.total_amount,
             order.created_at.isoformat()),
        )
        self._conn.commit()

    def get(self, order_id: str) -> Optional[Order]:
        row = self._conn.execute(
            "SELECT id, customer_name, total_amount, created_at FROM orders WHERE id = ?",
            (order_id,),
        ).fetchone()
        return self._row_to_order(row) if row else None

    def list_all(self) -> List[Order]:
        rows = self._conn.execute(
            "SELECT id, customer_name, total_amount, created_at FROM orders"
        ).fetchall()
        return [self._row_to_order(row) for row in rows]

    def remove(self, order_id: str) -> None:
        self._conn.execute("DELETE FROM orders WHERE id = ?", (order_id,))
        self._conn.commit()

    @staticmethod
    def _row_to_order(row) -> Order:
        return Order(
            id=row[0],
            customer_name=row[1],
            total_amount=row[2],
            created_at=datetime.fromisoformat(row[3]),
        )

3.5 Caso de uso desacoplado de la persistencia

# src/service.py
from .domain import Order
from .repository import OrderRepository


class OrderService:
    """Depende solo de la abstracción OrderRepository.
    No sabe -ni le importa- si los pedidos viven en SQLite o en memoria.
    """

    def __init__(self, repository: OrderRepository) -> None:
        self._repository = repository

    def place_order(self, customer_name: str, total_amount: float) -> Order:
        order = Order(customer_name=customer_name, total_amount=total_amount)
        self._repository.add(order)
        return order

    def apply_discount_to_order(self, order_id: str, percentage: float) -> Order:
        order = self._repository.get(order_id)
        if order is None:
            raise ValueError(f"Pedido {order_id} no encontrado")
        order.apply_discount(percentage)
        self._repository.add(order)  # actualiza
        return order

3.6 Demostración: intercambiando implementaciones sin tocar la lógica

# main.py
from src.in_memory_repository import InMemoryOrderRepository
from src.sqlite_repository import SqliteOrderRepository
from src.service import OrderService


def run_demo(repository):
    service = OrderService(repository)
    order = service.place_order("Ariana Quispe", 250.00)
    print(f"Pedido creado: {order}")

    updated = service.apply_discount_to_order(order.id, 10)
    print(f"Pedido con descuento: {updated}")

    print("Todos los pedidos:", service._repository.list_all())


if __name__ == "__main__":
    print("--- Usando repositorio en memoria ---")
    run_demo(InMemoryOrderRepository())

    print("\n--- Usando repositorio SQLite (persistente) ---")
    run_demo(SqliteOrderRepository("orders.db"))

Punto clave del ejemplo: OrderService nunca cambia. Basta con inyectarle un repositorio distinto (InMemoryOrderRepository o SqliteOrderRepository) para cambiar completamente la forma en que se persisten los datos. Esto es exactamente el beneficio que describe Fowler: la lógica de dominio queda aislada de los detalles de infraestructura.

4. Pruebas unitarias (ventaja directa del patrón)

# tests/test_order_service.py
import pytest
from src.in_memory_repository import InMemoryOrderRepository
from src.service import OrderService


def test_place_order_stores_it_in_repository():
    repo = InMemoryOrderRepository()
    service = OrderService(repo)

    order = service.place_order("Gustavo Mamani", 100.0)


    assert repo.get(order.id) is not None
    assert repo.get(order.id).customer_name == "Gustavo Mamani"


def test_apply_discount_updates_total_amount():
    repo = InMemoryOrderRepository()
    service = OrderService(repo)
    order = service.place_order("Hashira", 200.0)

    updated = service.apply_discount_to_order(order.id, 25)

    assert updated.total_amount == 150.0

Gracias a la interfaz OrderRepository, estas pruebas corren en milisegundos, sin necesidad de una base de datos real — otro beneficio directo que Fowler atribuye al patrón.

5. Relación con otros patrones del catálogo

Data Mapper: el Repository suele apoyarse en un Data Mapper para traducir entre el objeto de dominio y las filas de la base de datos.
Unit of Work: en sistemas más grandes, el Repository trabaja junto a un Unit of Work que agrupa varias operaciones en una sola transacción.
Service Layer: OrderService en este ejemplo cumple el rol de Service Layer, orquestando el dominio y delegando la persistencia al Repository.

6. Conclusión

El patrón Repository es una de las herramientas más usadas hoy en arquitecturas limpias (Clean Architecture, Hexagonal Architecture) precisamente porque resuelve un problema muy concreto: desacoplar el "qué" (lógica de negocio) del "cómo" (tecnología de persistencia). El ejemplo en Python demuestra que, con una interfaz bien definida, es trivial cambiar de una implementación en memoria a una persistente sin tocar una sola línea de la lógica de negocio.

Repository https://github.com/Hashiravc/repository-pattern.git

Referencias

Fowler, M. (2002). Patterns of Enterprise Application Architecture. Addison-Wesley.
Fowler, M. Repository. Catalog of Patterns of Enterprise Application Architecture. https://martinfowler.com/eaaCatalog/repository.html
Catálogo completo: https://martinfowler.com/eaaCatalog/

Applying Fortify Static Code Analyzer to a Node.js Application: A Practical Guide

Hashira Belén Vargas Candia — Sat, 06 Dec 2025 04:34:32 +0000

Article by: Hashira Belén Vargas Candia
Systems Engineering Student – Application Security Focus

Introduction to SAST Tools

Static Application Security Testing (SAST) tools analyze source code to identify security vulnerabilities before the application is deployed. While tools like SonarQube, Snyk, and Semgrep are popular, this article explores Micro Focus Fortify Static Code Analyzer (SCA) – an enterprise-grade SAST solution for comprehensive security analysis.

Why Fortify SCA?

Fortify SCA offers:
Multi-language support (Java, .NET, C++, Python, JavaScript, etc.)
Deep code analysis with data flow and control flow tracking
Comprehensive vulnerability database (OWASP Top 10, CWE, etc.)
Integration capabilities with CI/CD pipelines
Detailed remediation guidance

Setting Up Fortify SCA for a Node.js Application

Prerequisites

Micro Focus Fortify SCA installation
Node.js project with source code
Fortify Plugin for your IDE (optional)

Step 1: Installation & Setup

# Download Fortify SCA from official Micro Focus portal
# Install with default settings
# Verify installation
fortify version

Step 2: Configure Scan Settings

Create a fortify-sca.properties file:

# Scan configuration for Node.js application
com.fortify.sca.Phase0HigherOrder.Languages=javascript
com.fortify.sca.EnableHTML5Scan=true
com.fortify.sca.NPM.EnableDependencyScanning=true
com.fortify.sca.Yarn.EnableDependencyScanning=true

Step 3: Running the Scan

# Navigate to your Node.js project directory
cd /path/to/your/nodejs-app

# Run sourceanalyzer to translate source code
sourceanalyzer -b myNodeAppBuild -clean
sourceanalyzer -b myNodeAppBuild -source 1.8 **/*.js **/*.ts **/*.jsx **/*.tsx

# Scan for vulnerabilities
fortifyclient start scan -b myNodeAppBuild

Real-World Example: Vulnerable Node.js Code

Before SAST Analysis

// app.js - Vulnerable code examples

// 1. SQL Injection vulnerability
app.get('/users', (req, res) => {
    const userId = req.query.id;
    // UNSAFE: Direct string concatenation
    db.query(`SELECT * FROM users WHERE id = ${userId}`, (err, result) => {
        res.json(result);
    });
});

// 2. XSS vulnerability
app.post('/comment', (req, res) => {
    const comment = req.body.comment;
    // UNSAFE: Direct DOM injection
    res.send(`<div>${comment}</div>`);
});

// 3. Hardcoded credentials
const dbPassword = 'Admin@123'; // Security issue

After Fortify SCA Analysis & Fixes

// app.js - Secure code after remediation

// 1. Fixed SQL Injection using parameterized queries
app.get('/users', (req, res) => {
    const userId = req.query.id;
    // SAFE: Parameterized query
    db.query('SELECT * FROM users WHERE id = ?', [userId], (err, result) => {
        res.json(result);
    });
});

// 2. Fixed XSS using output encoding
const escapeHtml = require('escape-html');
app.post('/comment', (req, res) => {
    const comment = req.body.comment;
    // SAFE: HTML escaping
    res.send(`<div>${escapeHtml(comment)}</div>`);
});

// 3. Removed hardcoded credentials
const dbPassword = process.env.DB_PASSWORD; // From environment variables

Understanding Fortify Scan Results

Sample Output Format

{
  "issues": [
    {
      "id": "CWE-89",
      "severity": "High",
      "category": "SQL Injection",
      "file": "/src/routes/users.js",
      "line": 45,
      "description": "User input flows into SQL query without validation",
      "recommendation": "Use parameterized queries or stored procedures"
    },
    {
      "id": "CWE-79",
      "severity": "Medium",
      "category": "Cross-Site Scripting (XSS)",
      "file": "/src/views/comments.ejs",
      "line": 23,
      "description": "User input directly reflected in HTML output",
      "recommendation": "Implement proper output encoding"
    }
  ],
  "summary": {
    "total_issues": 15,
    "high_severity": 3,
    "medium_severity": 8,
    "low_severity": 4
  }
}

Severity Classification

Critical/High: Immediate attention required (SQLi, RCE, etc.)
Medium: Address in next development cycle (XSS, CSRF, etc.)
Low: Consider fixing (information disclosure, etc.)

Integrating Fortify SCA into CI/CD Pipeline

GitHub Actions Integration

# .github/workflows/fortify-scan.yml
name: Fortify SAST Scan

on: [push, pull_request]

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

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

    - name: Set up Node.js
      uses: actions/setup-node@v3
      with:
        node-version: '18'

    - name: Install dependencies
      run: npm ci

    - name: Download Fortify SCA
      run: |
        wget https://download.fortify.com/sca/fortify-sca-latest.zip
        unzip fortify-sca-latest.zip

    - name: Run Fortify Scan
      run: |
        ./fortify-sca/bin/sourceanalyzer -b ${{ github.run_id }} -clean
        ./fortify-sca/bin/sourceanalyzer -b ${{ github.run_id }} **/*.js
        ./fortify-sca/bin/fortifyclient start scan -b ${{ github.run_id }}

    - name: Upload Results
      uses: actions/upload-artifact@v3
      with:
        name: fortify-results
        path: fortify-reports/

GitLab CI/CD Integration

# .gitlab-ci.yml
stages:
  - test
  - security

fortify_sast:
  stage: security
  image: node:18
  before_script:
    - apt-get update && apt-get install -y wget unzip
    - wget https://download.fortify.com/sca/fortify-sca-latest.zip
    - unzip fortify-sca-latest.zip
  script:
    - npm ci
    - ./fortify-sca/bin/sourceanalyzer -b $CI_PIPELINE_ID -clean
    - ./fortify-sca/bin/sourceanalyzer -b $CI_PIPELINE_ID **/*.js
    - ./fortify-sca/bin/fortifyclient start scan -b $CI_PIPELINE_ID
  artifacts:
    paths:
      - fortify-reports/
    when: always

Best Practices for SAST Implementation

Regular Scanning Schedule
Pre-commit hooks: Scan before each commit

Nightly builds: Comprehensive scans during off-hours
Release gates: Mandatory scans before production deployment

Tuning & Customization

# Custom rule pack configuration
com.fortify.sca.CustomRules.Path=/path/to/custom/rules.xml
com.fortify.sca.SuppressionFilter.Path=/path/to/false-positives.xml
com.fortify.sca.Severity.Threshold=Medium

Developer Education
Remediation workshops: How to fix identified issues
Secure coding training: Preventing vulnerabilities at source
Knowledge sharing: Internal security champions program

Comparison with Other SAST Tools

Feature	Fortify SCA	Checkmarx	CodeQL
Language Support	25+ languages	25+ languages	10+ languages
Analysis Depth	Deep flow analysis	Flow analysis	Semantic analysis
Integration	Extensive CI/CD plugins	Good integration	GitHub native
Learning Curve	Moderate to steep	Moderate	Moderate
Reporting	Enterprise-grade	Comprehensive	GitHub-focused

Common Challenges & Solutions

Challenge	Solution
False Positives	Create suppression filters for known false positives
Long Scan Times	Implement incremental scanning
Complex Setup	Use containerized deployment
High Resource Usage	Optimize scan configurations and schedule off-hours
Developer Resistance	Provide training and integrate smoothly into workflow

Challenge 1: False Positives

Solution: Create suppression filters for known false positives

<!-- false-positives.xml -->
<SuppressionFilters>
  <Suppress>
    <RuleID>CWE-78</RuleID>
    <File>.*legacy-code\.js</File>
  </Suppress>
</SuppressionFilters>

Challenge 2: Long Scan Times

Solution: Implement incremental scanning

## Only scan changed files
sourceanalyzer -b myApp -incremental

Challenge 3: Complex Setup

Solution: Use containerized deployment

# Dockerfile for Fortify SCA
FROM node:18
RUN wget https://download.fortify.com/sca/fortify-sca-latest.zip
RUN unzip fortify-sca-latest.zip
COPY . /app
WORKDIR /app
CMD ["./run-fortify-scan.sh"]

Conclusion

Fortify Static Code Analyzer provides robust security scanning for applications across multiple programming languages. While it requires initial setup and configuration, its comprehensive vulnerability detection and detailed remediation guidance make it valuable for enterprise security programs.

Key takeaways:

SAST tools like Fortify catch vulnerabilities early in development
Integration with CI/CD pipelines enables automated security testing
Regular scans and developer education significantly improve application security
Proper tuning reduces false positives and increases tool effectiveness

Additional Resources

OWASP SAST Tools List
Fortify SCA Documentation
NIST Application Security Guidelines
Secure Coding Practices Checklist

A Real-World Comparison of Testing Management Tools: GitHub Actions vs GitLab CI/CDs

Hashira Belén Vargas Candia — Thu, 04 Dec 2025 05:20:49 +0000

Article by: Hashira Belén Vargas Candia
Systems Engineering Student – CI/CD & DevOps Focus

Introduction
In modern software development, continuous integration and delivery (CI/CD) are essential for ensuring code quality and fast deployments. Automated testing tools allow test suites to run automatically with every code change. In this article, I will compare two of the most popular tools: GitHub Actions and GitLab CI/CD, providing real configuration examples.

GitHub Actions
Overview
GitHub Actions is the native CI/CD solution integrated directly into GitHub. It allows workflow automation using YAML files in the .github/workflows directory. It is highly flexible, with a marketplace of pre-built actions and support for Docker containers.

Example Configuration

# .github/workflows/run-tests.yml
name: Run Tests

on:
  push:
    branches: [ main, develop ]
  pull_request:
    branches: [ main ]

jobs:
  test:
    runs-on: ubuntu-latest

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

    - name: Set up Node.js
      uses: actions/setup-node@v3
      with:
        node-version: '18'

    - name: Install dependencies
      run: npm ci

    - name: Run unit tests
      run: npm test

    - name: Run integration tests
      run: npm run test:integration

    - name: Upload coverage reports
      uses: codecov/codecov-action@v3

GitLab CI/CD

Overview
GitLab CI/CD is the continuous integration tool included within the GitLab platform. It is configured using a .gitlab-ci.yml file in the repository root. It offers visual pipelines, deployment environments, and deep integration with the DevOps lifecycle.

Example Configuration

# .gitlab-ci.yml
stages:
  - test
  - deploy

unit_tests:
  stage: test
  image: node:18-alpine
  script:
    - npm ci
    - npm test
  artifacts:
    when: always
    paths:
      - coverage/
    reports:
      junit: junit.xml

integration_tests:
  stage: test
  image: node:18-alpine
  services:
    - postgres:latest
  variables:
    POSTGRES_DB: test_db
    POSTGRES_USER: runner
    POSTGRES_PASSWORD: ""
  script:
    - npm ci
    - npm run test:integration

pages:
  stage: deploy
  script:
    - npm run build:coverage
  artifacts:
    paths:
      - public

Detailed Comparison

Feature	GitHub Actions	GitLab CI/CD
Native Integration	With GitHub (perfect if you use GitHub)	With GitLab (complete DevOps ecosystem)
Configuration Syntax	YAML with reusable actions	YAML with defined stages and jobs
Marketplace/Pre-built Actions	Extensive GitHub Marketplace	Reusable templates and components
Visual Environments	Basic but functional	More detailed visual pipelines
Pricing for Private Repos	2000 free minutes/month	400 free minutes/month on SaaS free tier
Self-hosted Runners	Yes (runners)	Yes (GitLab runners)
Cache and Artifacts	Built-in support	Very robust with configurable storage
Kubernetes Integration	Yes (via actions)	Native and very strong

Real-World Use Case: Testing Pipeline for a REST API
Context
I developed a REST API with Node.js/Express that requires:

Unit tests (Jest)

Integration tests with PostgreSQL

Load testing (optional)

Coverage reports

GitHub Actions Solution

- name: Run load tests
  if: github.ref == 'refs/heads/main'
  run: |
    npm install -g artillery
    artillery run load-test.yml
GitLab CI/CD Solution
yaml
performance_tests:
  stage: test
  only:
    - main
  script:
    - npm install -g artillery
    - artillery run load-test.yml

Both tools allow running load tests only on the main branch, optimizing resource usage.

Tool Selection Recommendations

Choose GitHub Actions if:

Your repository is already on GitHub
You need integration with many third-party tools*
You prefer a community-driven actions ecosystem
Your team is small to medium and values simplicity

Choose GitLab CI/CD if:

You already use GitLab for repository management
You need a complete DevOps pipeline (from issues to deploy)
You require native integration with Kubernetes
You work in a large team with auditing and security needs

Conclusion
Both GitHub Actions and GitLab CI/CD are powerful tools for test automation. The choice mainly depends on where your code is hosted and your specific DevOps workflow needs.

GitHub Actions stands out for its simplicity and vast action ecosystem, while GitLab CI/CD offers deeper integration with the full development lifecycle. Both enable robust, scalable, and maintainable testing pipelines.

Applying API Testing Frameworks: Real-World Examples with Swagger and JUnit

Hashira Belén Vargas Candia — Tue, 04 Nov 2025 07:11:07 +0000

Introduction

In today's software development landscape, APIs (Application Programming Interfaces) are essential for enabling communication between different systems. Ensuring that these APIs function correctly is crucial, which is why API testing is an integral part of the development process.

This article explores how to apply API testing frameworks in real-world scenarios, using Swagger and JUnit as examples. These frameworks help automate and streamline the testing process, ensuring that APIs are functional, secure, and performant.

What is an API Testing Framework?

An API testing framework is a set of tools, libraries, and processes that facilitate the testing of APIs. The goal of such a framework is to standardize the testing process, making it easier to write, run, and maintain tests for APIs across different endpoints and services.

Benefits of Using an API Testing Framework:

Automation: Reduces manual effort by automating repetitive tasks.
Consistency: Ensures uniformity in testing across various API endpoints.
Scalability: Makes it easier to scale tests as the system grows.
Integration: Can be integrated into Continuous Integration (CI) and Continuous Deployment (CD) pipelines for continuous testing.

Using Swagger for API Testing

Swagger (now known as OpenAPI) is one of the most widely used frameworks for defining and testing APIs. It allows developers to define their APIs in a standardized format, which can then be used for documentation and testing.

Example 1: Testing API with Swagger

To test an API with Swagger, first define the API endpoints in a Swagger (OpenAPI) specification file. Here's a simple example of how a user API might be defined in Swagger:

openapi: 3.0.0
info:
  title: User API
  description: API for managing users in a system
  version: 1.0.0
paths:
  /users/{id}:
    get:
      summary: Get a user by ID
      parameters:
        - in: path
          name: id
          required: true
          schema:
            type: integer
            example: 1
      responses:
        200:
          description: User found
          content:
            application/json:
              schema:
                type: object
                properties:
                  id:
                    type: integer
                  name:
                    type: string
                example:
                  id: 1
                  name: "John Doe"
        404:
          description: User not found

In this Swagger file, we've defined a GET request to retrieve a user by their ID.

Testing with Swagger Inspector

You can use Swagger Inspector to validate the API by running tests directly from the Swagger specification.

To use Swagger Inspector, simply enter the Swagger URL or upload the Swagger JSON/YAML file, and it will generate test requests for each defined endpoint.

Using JUnit and RestAssured for API Testing in Java

JUnit is one of the most widely used testing frameworks for Java. When combined with RestAssured, a library designed for testing REST APIs, JUnit provides a powerful solution for testing APIs in Java.

Example 2: Testing an API with JUnit and RestAssured

First, add RestAssured and JUnit to your pom.xml:

<dependency>
    <groupId>io.rest-assured</groupId>
    <artifactId>rest-assured</artifactId>
    <version>4.3.3</version>
    <scope>test</scope>
</dependency>

<dependency>
    <groupId>org.junit.jupiter</groupId>
    <artifactId>junit-jupiter-api</artifactId>
    <version>5.7.0</version>
    <scope>test</scope>
</dependency>

Now, let's write a simple JUnit test to verify the GET request for the /users/{id} endpoint.

import io.restassured.RestAssured;
import org.junit.jupiter.api.Test;
import static org.hamcrest.Matchers.*;

public class UserApiTest {

    @Test
    public void testGetUser() {
        RestAssured.baseURI = "https://reqres.in/api"; // Base URL

        RestAssured
            .given()
                .pathParam("id", 1) // Path parameter
            .when()
                .get("/users/{id}") // GET request to /users/{id}
            .then()
                .statusCode(200) // Assert that the status code is 200 (OK)
                .body("data.id", equalTo(1)) // Validate that the ID is 1
                .body("data.first_name", equalTo("George")); // Validate first_name
    }
}

In this example:

RestAssured is used to send a GET request to the /users/{id} endpoint.
JUnit is used to validate that the response code is 200 and that the returned ID and first_name match the expected values.

Step 3: Running the Test
You can run the test using Maven:

mvn test

This will execute the test, and JUnit will validate the response based on the conditions defined in the test.

Integrating API Tests into CI/CD Pipelines

Integrating API tests into your CI/CD pipeline ensures that your tests run automatically with each code change. This helps identify issues early in the development process.

Integrating with Jenkins

To run the JUnit tests in Jenkins, you can add the following steps to your Jenkinsfile:

pipeline {
    agent any
    stages {
        stage('Build') {
            steps {
                sh 'mvn clean install'
            }
        }
        stage('Test') {
            steps {
                sh 'mvn test' // Run JUnit tests
            }
        }
    }
}

This Jenkins pipeline will trigger the JUnit tests automatically every time code is committed to your repository.

Running Swagger Tests in CI/CD

You can also integrate Swagger Inspector in a CI/CD pipeline by calling the Swagger API using a curl command in your pipeline to validate the API against your Swagger definitions.

Best Practices for API Testing

When applying frameworks for API testing, consider the following best practices:

Organize Your Tests: Group your tests by API category or functionality for easier maintenance.
Use Data-Driven Testing: Test different data sets to ensure the API behaves correctly with a variety of inputs.
Mock APIs: Use mock servers to simulate APIs during testing, especially when the actual API is not available.
Automate Your Tests: Automate your API tests and integrate them into the CI/CD pipeline for continuous testing.

Conclusion

API testing is an essential part of ensuring the functionality, security, and performance of your APIs. By using Swagger for API documentation and JUnit with RestAssured for testing, you can streamline the testing process and ensure that your APIs are thoroughly tested.

Integrating these tools into your CI/CD pipeline allows for continuous testing, ensuring your APIs remain reliable and meet the expected requirements.

References

Swagger Documentation
JUnit 5 Documentation
RestAssured Documentation
Top 15 API Testing Tools in 2022

DEV Community: Hashira Belén Vargas Candia

SQL AI Database Solutions: Talking to Databases with Artificial Intelligence

Abstract

1. Why Text-to-SQL Matters

2. General Architecture

3. Real-World Scenario: Sales and Customer Support

4. Creating a Test Database with Python

5. Reading the Database Schema Automatically

6. Prompting the Model to Generate SQL

7. Validating Before Execution

8. Executing with Read-Only Permissions

9. Mini Interface with Streamlit

10. Agent-Based Approach

11. Best Practices for Real Projects

12. Important Observation

Conclusion

References

Practical Modern Observability: FastAPI, OpenTelemetry, Prometheus, Jaeger, and Grafana

Repository: https://github.com/Hashiravc/Observability-Practices.git

1. What is Observability?

2. The OpenTelemetry Standard

3. Demo Application Architecture

4. Code Walkthrough

Dependency Configuration (requirements.txt)

Telemetry Configuration (app/telemetry.py)

Application Implementation (app/main.py)

5. Orchestrating the Stack with Docker Compose

6. How it Looks in Action

7. Observability Best Practices for Production

Conclusion

Enterprise Design Patterns: The Repository Pattern in Practice

1. Introducción

2. ¿Qué es el patrón Repository?

Problema que resuelve

Solución

3. Ejemplo real de código (Python)

3.1 Entidad de dominio

3.2 Interfaz del Repository (el contrato)

3.3 Implementación en memoria (ideal para tests)

3.4 Implementación con SQLite (persistencia real)

3.5 Caso de uso desacoplado de la persistencia

3.6 Demostración: intercambiando implementaciones sin tocar la lógica

4. Pruebas unitarias (ventaja directa del patrón)

5. Relación con otros patrones del catálogo

6. Conclusión

Referencias

Applying Fortify Static Code Analyzer to a Node.js Application: A Practical Guide

Introduction to SAST Tools

Why Fortify SCA?

Setting Up Fortify SCA for a Node.js Application

Prerequisites

Step 1: Installation & Setup

Step 2: Configure Scan Settings

Step 3: Running the Scan

Real-World Example: Vulnerable Node.js Code

Before SAST Analysis

After Fortify SCA Analysis & Fixes

Understanding Fortify Scan Results

Sample Output Format

Severity Classification

Integrating Fortify SCA into CI/CD Pipeline

GitHub Actions Integration

GitLab CI/CD Integration

Best Practices for SAST Implementation

Comparison with Other SAST Tools

Common Challenges & Solutions

Challenge 1: False Positives

Challenge 2: Long Scan Times

Challenge 3: Complex Setup

Conclusion

Key takeaways:

Additional Resources

A Real-World Comparison of Testing Management Tools: GitHub Actions vs GitLab CI/CDs

Tool Selection Recommendations

Applying API Testing Frameworks: Real-World Examples with Swagger and JUnit

Introduction

What is an API Testing Framework?

Benefits of Using an API Testing Framework:

Using Swagger for API Testing

Example 1: Testing API with Swagger

Dependency Configuration (`requirements.txt`)

Telemetry Configuration (`app/telemetry.py`)

Application Implementation (`app/main.py`)