Dynamic SQL Executor (Data Manager)— A Lightweight Service for Executing Dynamic SQL Across Multiple Data Sources

A minimal Spring Boot tool to run ad-hoc SELECT queries against configured PostgreSQL, MySQL, and MSSQL databases with built-in OpenAPI documentation.

Organizations often need a lightweight, auditable way to execute ad-hoc SQL across different database engines. Dynamic SQL Executor is a small Spring Boot service that centralizes that need: send a POST request with a target database identifier and a SQL statement, and receive the result as JSON. It’s intended as a prototyping/utility tool and a starting point for building safer, production-grade query services.

Why this exists

• Quickly run ad-hoc queries across multiple database types without writing per-database scripts.
• Provide a single, documented REST endpoint for internal tooling and debugging.
• Enable rapid iteration with OpenAPI/Swagger auto-generated documentation.

Tech stack

• Java 21
• Spring Boot 3
• springdoc-openapi for Swagger UI
• HikariCP for JDBC connection pooling
• Drivers: PostgreSQL, MySQL, Microsoft SQL Server

Key features

• Multiple data source definitions through YAML configuration.
• Single REST endpoint: POST /sql/execute
• Swagger UI for API discovery: /swagger-ui/index.html
• Restricts execution to SELECT queries (safety measure out of the box)

How it works (high level)

• Configuration: Data sources are declared under app.databases in application.yml. Each data source has driver, url, username, and password entries.
• Request flow: Client posts a JSON body containing the SQL text and the target db identifier. The service resolves the DataSource for that db and executes the query.
• Response: Query results are returned in JSON format.
• Documentation: springdoc scans controllers and auto-generates OpenAPI spec and a Swagger UI.

Quick start (run locally)

Prerequisites: Java 21 and Maven installed.

Run with Maven:

mvn spring-boot:run

Or build and run the jar:

mvn package
java -jar target/*.jar

API endpoints

• Swagger UI: http://localhost:8080/swagger-ui/index.html
• OpenAPI JSON: http://localhost:8080/v3/api-docs
• Execute SQL: POST http://localhost:8080/sql/execute

Example request (JSON)

{
  "sql": "SELECT id, name FROM users LIMIT 10",
  "db": "boa"
}

Example cURL

curl -X POST http://localhost:8080/sql/execute \
  -H "Content-Type: application/json" \
  -d '{"sql":"SELECT id, name FROM users LIMIT 10","db":"boa"}'

Configuration example (application.yml)

app:
  databases:
    boa:
      driver: org.postgresql.Driver
      url: jdbc:postgresql://localhost:5432/boa
      username: postgres
      password: 1234

Security considerations (important)

• Dynamic SQL execution is inherently risky. This project currently only allows SELECT queries as a basic safety restriction — do not expose this service to untrusted networks or users.
• For production use, add:
• Authentication/authorization (API keys, OAuth, network restrictions)
• Query validation/whitelisting or SQL parsing to disallow dangerous patterns
• Parameterized queries where possible (avoid injecting user-provided SQL directly)
• Audit logging and rate limiting
• Keep dependencies up to date: some transitive dependencies may have publicly reported vulnerabilities; track CVEs and patch as needed.

Repository: https://github.com/dogangunemre/DynamicDataManager