psql-query-builder 0.2.0

Generate PostgreSQL queries from natural language using AI

🔍 PSQL Query Builder

Transform natural language into optimized PostgreSQL queries with AI

✨ Overview

PSQL Query Builder is a powerful Python tool that transforms natural language into optimized PostgreSQL queries using AI. It bridges the gap between human language and SQL, making database interaction accessible to everyone - not just SQL experts.

Built on OpenAI's advanced language models, this tool analyzes your database schema and generates precise, efficient SQL queries based on plain English descriptions. Whether you're a data analyst without SQL expertise, a developer looking to speed up query writing, or a database administrator seeking to simplify access for your team, PSQL Query Builder streamlines your workflow.

✅ Features

🤖 AI-Powered SQL Generation Transform natural language to optimized SQL Context-aware query generation Support for complex queries and joins	⚡ Smart Schema Caching Automatic database schema analysis Configurable cache TTL Significant performance improvement
🛠️ Flexible Configuration Environment variables support Command-line arguments Multiple output formats (table, JSON, CSV)	🔧 Intelligent Error Handling Automatic fix suggestions Detailed error messages Interactive query correction

🎯 Use Cases

📊 Data Analysts Generate complex SQL queries without deep SQL knowledge Quickly explore database structures and relationships Prototype queries in natural language before refinement

👨‍💻 Developers Speed up database query development Reduce time spent debugging complex SQL syntax Generate queries for unfamiliar database schemas

🛡️ Database Admins Provide simplified access to non-technical team members Quickly generate queries for common reporting needs Validate schema design through natural language

🚀 Quick Start

Installation

pip install psql-query-builder

Basic Usage

# Using environment variables for connection
export PSQL_CONNECTION_STRING="postgresql://username:password@localhost:5432/database"
export OPENAI_API_KEY="your-openai-api-key"

# Run a query
psql-query-builder --query "Find all active users who registered last month"

Python API

from psql_query_builder import QueryBuilder

# Initialize the query builder
builder = QueryBuilder(
    connection_string="postgresql://username:password@localhost:5432/database",
    openai_api_key="your-openai-api-key"
)

# Generate and execute a query
results = builder.run_query(
    "Find all products with more than 100 units in stock and price less than $50",
    execute=True
)

# Print the results
print(results)

LangChain Integration

PSQL Query Builder can be easily integrated with LangChain to enable natural language database queries in your AI applications:

# Using the @tool decorator approach
from langchain_core.tools import tool

@tool
def query_database(query: str, connection_string: str = "postgresql://user:pass@localhost/db"):
    """Execute a natural language query against a PostgreSQL database."""
    from psql_query_builder import QueryBuilder
    
    builder = QueryBuilder(connection_string=connection_string)
    return builder.run_query(query, execute=True)

# Use with LangChain
from langchain_openai import ChatOpenAI
from langchain.agents import create_openai_tools_agent, AgentExecutor

llm = ChatOpenAI(model="gpt-4-turbo")
tools = [query_database]
agent = create_openai_tools_agent(llm, tools, prompt=None)
agent_executor = AgentExecutor(agent=agent, tools=tools)

# Run the agent
response = agent_executor.invoke({"input": "Find all users who registered last month"})
print(response["output"])

For more advanced integration options, see the full documentation.

⚙️ Configuration

Environment Variables

# Full connection string
export PSQL_CONNECTION_STRING="postgresql://username:password@localhost:5432/database"

# Or individual connection parameters
export PSQL_HOST="localhost"
export PSQL_PORT="5432"
export PSQL_USER="username"
export PSQL_PASSWORD="password"
export PSQL_DATABASE="database"

# OpenAI API key
export OPENAI_API_KEY="your-openai-api-key"

Command-line Arguments

psql-query-builder \
  --host localhost \
  --port 5432 \
  --user username \
  --password password \
  --database database \
  --api-key your-openai-api-key \
  --query "Find all products with more than 100 units in stock" \
  --dry-run  # Optional: generate SQL without executing

Dry Run Mode

# Generate SQL only, don't execute (useful for reviewing queries before running on production databases)
psql-query-builder --query "Find all inactive users who haven't logged in for 3 months" --dry-run

# Output:
# Generated SQL query (dry run mode):
# --------------------------------------------------
# SELECT u.id, u.username, u.email, u.last_login
# FROM users u
# WHERE u.last_login < NOW() - INTERVAL '3 months'
# ORDER BY u.last_login ASC;
# --------------------------------------------------

Schema Caching

For better performance with repeated queries, enable schema caching:

# Enable schema caching with default settings
psql-query-builder --query "Find all users who placed orders in the last week" --cache

# Specify cache path and TTL (time-to-live in seconds)
psql-query-builder --query "Find all users who placed orders in the last week" \
  --cache \
  --cache-path "/tmp/schema_cache" \
  --cache-ttl 3600

# Force refresh the cache
psql-query-builder --query "Find all users who placed orders in the last week" \
  --cache \
  --force-refresh

📘 Advanced Usage

💬 Interactive Mode Start an interactive session for multiple queries: psql-query-builder Then enter queries at the prompt: > Find all customers in California > Show revenue by product category > exit

🔍 Single Query Mode Execute a single query and exit: psql-query-builder --query "Find all users who registered in the last month" Perfect for scripts and automation

📚 API Reference

Python API

from psql_query_builder import QueryBuilder

# Initialize with connection string
builder = QueryBuilder(
    connection_string="postgresql://username:password@localhost:5432/database",
    openai_api_key="your-openai-api-key"
)

# Or with individual parameters
builder = QueryBuilder(
    host="localhost",
    port=5432,
    database="mydatabase",
    user="myuser",
    password="mypassword",
    openai_api_key="your-openai-api-key"
)

# Generate SQL without executing
sql = builder.generate_query("Find all users who registered last month")
print(sql)

# Generate and execute query
results = builder.run_query(
    "Find all products with more than 100 units in stock",
    execute=True
)
print(results)

🚩 Roadmap

Future development plans for PSQL Query Builder include:

• Support for more database systems (MySQL, SQLite, SQL Server)
• Interactive SQL editing and refinement
• Query history management and reuse
• Integration with popular database tools and ORMs
• Web interface for non-CLI usage
• Query optimization suggestions
• Support for database migrations and schema changes

Feel free to contribute to any of these features or suggest new ones!

👨‍💻 Contributing

Contributions are welcome! Here's how you can help:

1. Report bugs or request features: Open an issue describing what you found or what you'd like to see
2. Submit improvements: Fork the repository, make your changes, and submit a pull request
3. Improve documentation: Help clarify or expand the documentation
4. Share your use cases: Let us know how you're using PSQL Query Builder

📜 License

MIT