Generate and Execute SQL Queries from Natural Language Using OpenAI GPT-4o

Build a privacy-preserving text-to-SQL pipeline that turns natural language questions into executable PostgreSQL queries using OpenAI GPT-4o. Instead of shipping table contents to an external model, this flow sends only the database schema (table and column names) to the LLM, so the model has enough context to write correct SQL without ever seeing a single row of sensitive data. It solves the core problem of LLM-driven analytics: giving non-technical users self-service access to a database in plain English while keeping production records private and queries safe.

How it works

1. The fetch_schema task (io.kestra.plugin.jdbc.postgresql.Queries) introspects the information_schema.columns catalog with fetchType: FETCH, returning every table in the public schema with its column names and data types and no row data.
2. The generate_sql task (io.kestra.plugin.openai.ChatCompletion) sends that schema plus the user's question input to GPT-4o (model: gpt-4o). A system prompt instructs the model to output a raw SELECT statement grounded only in the provided schema, or NO_SQL: <reason> when the question cannot be answered.
3. The check_sql task (io.kestra.plugin.core.flow.If) tests whether the model returned valid SQL. When it did, execute_query runs the generated statement against PostgreSQL and format_results (io.kestra.plugin.serdes.json.IonToJson) converts the output to JSON.
4. When the model returns NO_SQL, the log_no_sql task records the reason instead of running anything against the database.

What you get

• A working text-to-SQL pattern that never exposes table data to the LLM.
• Schema-grounded prompts that reduce hallucinated columns and invalid SQL.
• A safety branch that logs unanswerable questions instead of executing bad queries.
• Query results returned as clean JSON, ready for reporting, dashboards, or notifications.

Who it's for

• Data and analytics engineers building self-service query interfaces.
• Platform teams adding natural-language data access for non-technical users.
• Compliance-sensitive teams that cannot send production rows to external AI APIs.

Why orchestrate this with Kestra

OpenAI and PostgreSQL have no shared scheduler or workflow engine. Kestra ties them together in one declarative YAML flow with event triggers, automatic retries on transient API or database failures, and full execution lineage so every generated query and result is auditable. The If task gives you conditional execution that a raw prompt or a cron job cannot: queries run only when the model produces valid SQL, and the gap an LLM API alone cannot fill, deciding what to do next based on the model output, is handled natively.

Prerequisites

• A reachable PostgreSQL database with a populated public schema.
• An OpenAI API key with access to gpt-4o.

Secrets

• OPENAI_API_KEY: API key used by the generate_sql task.
• POSTGRES_URL: JDBC connection URL for your PostgreSQL database.
• POSTGRES_USER: database username.
• POSTGRES_PASSWORD: database password.

Quick start

1. Add the four secrets above to your Kestra instance.
2. Import this blueprint into your namespace.
3. Execute the flow and enter a question, for example "How many orders were placed last month?".
4. Inspect the generate_sql output for the SQL and format_results for the JSON answer.

How to extend

• Introspect multiple schemas or join across catalogs by adjusting the fetch_schema query.
• Cache schema or results in the KV store to cut latency and API cost.
• Route questions to different databases based on intent before execute_query.
• Chain format_results into a Slack or email task to deliver answers automatically.
• Swap the model or add validation tasks to enforce read-only access.

Links

• plugin-openai docs
• plugin-jdbc-postgres docs
• PostgreSQL orchestration