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.
|
|
|
|
|
Generate complex SQL queries without deep SQL knowledge Quickly explore database structures and relationships Prototype queries in natural language before refinement |
Speed up database query development Reduce time spent debugging complex SQL syntax Generate queries for unfamiliar database schemas |
Provide simplified access to non-technical team members Quickly generate queries for common reporting needs Validate schema design through natural language |
pip install psql-query-builder# 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"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)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.
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 executingDry 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|
Start an interactive session for multiple queries: Then enter queries at the prompt: |
Execute a single query and exit: Perfect for scripts and automation |
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)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!
Contributions are welcome! Here's how you can help:
- Report bugs or request features: Open an issue describing what you found or what you'd like to see
- Submit improvements: Fork the repository, make your changes, and submit a pull request
- Improve documentation: Help clarify or expand the documentation
- Share your use cases: Let us know how you're using PSQL Query Builder
MIT