A PostgreSQL extension that uses AI/LLM to generate and execute SQL queries from natural language descriptions. By default, it operates in read-only mode for safety - allowing only SELECT queries to execute while returning generated queries for modifications without executing them.
- PostgreSQL 18
- AI SDK C++ libraries
- OpenSSL, Brotli (These are dependencies of the AI-SDK library)
You need to set these 3 environment variables and then restart your postgres server:
-
PSQL_AI_KEY- Your LLM API key- For OpenAI: Your OpenAI API key
- For Anthropic: Your Anthropic API key
-
PSQL_AI_URL- The OpenAI spec API endpoint URL- For OpenAI:
https://api.openai.com/v1 - For Anthropic:
https://api.anthropic.com
- For OpenAI:
-
PSQL_AI_MODEL- The model identifier to use- For OpenAI:
gpt-4,gpt-4-turbo,gpt-3.5-turbo, etc. - For Anthropic:
claude-3-opus-20240229,claude-3-sonnet-20240229, etc.
- For OpenAI:
make clean
make
make installThen create the extension in your database:
CREATE EXTENSION psql_code_gen;Get up and running in 5 minutes with the sample ecommerce database.
The extension includes a complete sample ecommerce schema (sample/ecom.sql) with realistic marketplace data.
# Connect to your PostgreSQL database and load the sample schema
psql -U postgres -d your_database_name -f sample/ecom.sqlOr from within psql:
\i sample/ecom.sqlWhat Gets Created:
- Schema:
marketplace- Contains all tables and functions - Enums: User roles, order statuses, payment statuses, shipment statuses
- Core Tables:
- Users (customers, sellers, admins)
- Products with variants and inventory
- Orders, order items, payments
- Shipments, reviews, audit logs
- Carts and cart items
- Sellers, addresses, categories, coupons
- Sample Data:
- 5 users (2 customers, 2 sellers, 1 admin)
- 2 products with variants and inventory
- 1 complete order with payment and shipment records
- 2 reviews
- Sample coupons
Check that the extension and sample data are loaded:
-- List all tables in the marketplace schema
\dt marketplace.*
-- Count records in key tables
SELECT COUNT(*) as user_count FROM marketplace.users;
SELECT COUNT(*) as product_count FROM marketplace.products;
SELECT COUNT(*) as order_count FROM marketplace.orders;Start with a simple SELECT query in read-only mode (default):
-- Get all products with their seller info
SELECT * FROM psql_code_gen('show me all products in the marketplace with seller names');Expected Output:
command_type | rows_affected | result_json
--------------+---------------+---------------------------------------------------
SELECT | 1 | {"id":1,"title":"Air Runner Tee",...}
SELECT | 2 | {"id":2,"title":"ACME Anvil",...}
Query for orders placed in the last 30 days:
SELECT
result_json->>'order_number' as order_id,
result_json->>'total' as total,
result_json->>'status' as status
FROM psql_code_gen('find all orders from the last 30 days')
WHERE command_type = 'SELECT'
LIMIT 10;By default, write operations show the generated SQL without executing:
-- See what SQL would be generated for an insert
SELECT result_json FROM psql_code_gen('add a new product called "Running Shoes" with price 89.99');Output shows the generated SQL:
result_json
----------------------------------------------
"INSERT INTO products (title, price) ..."
To actually execute write operations:
-- Enable write mode
SELECT psql_code_gen_set_readonly(false);
-- Now this will actually INSERT
SELECT * FROM psql_code_gen('add a new user with email newuser@example.com');
-- Check the result
SELECT COUNT(*) as user_count FROM marketplace.users;
-- Switch back to read-only mode
SELECT psql_code_gen_set_readonly(true);Try these natural language queries against the sample database:
-- List all customers and their email addresses
SELECT result_json FROM psql_code_gen('show me all customer users with their email addresses');
-- Find premium sellers with high ratings
SELECT result_json FROM psql_code_gen('find all sellers with average rating above 4.0');-- Get products by category
SELECT result_json FROM psql_code_gen('list all products in the apparel category');
-- Check inventory levels
SELECT result_json FROM psql_code_gen('show product variants with low inventory less than 10 items');
-- Find discontinued products
SELECT result_json FROM psql_code_gen('find all products where is_active is false');-- Recent orders
SELECT
result_json->>'order_number' as order_number,
result_json->>'total' as total
FROM psql_code_gen('get the 10 most recent orders')
WHERE command_type = 'SELECT';
-- Orders by status
SELECT result_json FROM psql_code_gen('count how many orders have status shipped');
-- Payment status
SELECT result_json FROM psql_code_gen('find all failed payment transactions');-- Top sellers by sales
SELECT result_json FROM psql_code_gen('which sellers have the most orders');
-- Revenue by category
SELECT result_json FROM psql_code_gen('calculate total sales by product category');
-- Customer activity
SELECT result_json FROM psql_code_gen('show me the top 5 customers by order count');SELECT * FROM psql_code_gen('get all users from the users table');Output:
command_type | rows_affected | result_json
--------------+---------------+--------------------------------------------------------
SELECT | 1 | {"id":1,"email":"user@example.com","name":"John Doe"}
SELECT | 2 | {"id":2,"email":"jane@example.com","name":"Jane Smith"}
Extract specific fields from JSON results:
SELECT
result_json->>'email' as email,
result_json->>'name' as name
FROM psql_code_gen('get all users from the users table')
WHERE command_type = 'SELECT';When trying to modify data in read-only mode, the extension returns the generated query without executing it:
SELECT command_type, result_json FROM psql_code_gen('create a new user with email test@example.com');Output:
command_type | result_json
--------------+--------------------------------------------------
INSERT | INSERT INTO users (email, created_at) VALUES ...
The query is shown in result_json for your review. Logs also show a WARNING with the generated query.
To actually execute INSERT, UPDATE, DELETE, CREATE, DROP, ALTER statements:
SELECT psql_code_gen_set_readonly(false);
-- Now write operations will execute
SELECT * FROM psql_code_gen('insert a new product with name "Widget" and price 29.99');Output:
command_type | rows_affected | result_json
--------------+---------------+-------------
INSERT | 1 |
SELECT psql_code_gen_set_readonly(true);Generates and executes (or previews) SQL based on natural language input.
Parameters:
query(text): Natural language description of the SQL operation
Returns: SETOF psql_code_gen_result composite type with:
command_type(text): Type of SQL command (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER)rows_affected(integer): Number of rows affected (for SELECT: row number; for DML/DDL in read-only mode: 0)result_json(json): For SELECT queries, the row data in JSON; for write queries in read-only mode, the generated SQL query
Control read-only mode.
Parameters:
readonly(boolean):truefor read-only (default),falseto allow write operations
Example:
-- Enable read-only mode (safe)
SELECT psql_code_gen_set_readonly(true);
-- Disable read-only mode (allow writes)
SELECT psql_code_gen_set_readonly(false);- Read-Only Mode (Default): Write operations are never executed by default, only returned for preview
- LLM Injection: The extension passes user queries through an LLM. Ensure your LLM API is properly secured
- Schema Information: The extension has access to table definitions to provide context to the LLM
- User Permissions: Even in write-enabled mode, standard PostgreSQL permissions apply
SELECT
result_json->>'order_id' as order_id,
result_json->>'customer_name' as customer,
result_json->>'total' as total
FROM psql_code_gen('show me recent orders from the last 30 days')
LIMIT 10;SELECT psql_code_gen_set_readonly(true);
-- Review what query would be generated
SELECT result_json FROM psql_code_gen('update all inactive users to active status');
-- If satisfied, enable writes and re-run
SELECT psql_code_gen_set_readonly(false);
SELECT * FROM psql_code_gen('update all inactive users to active status');WITH generated_data AS (
SELECT
result_json->>'id' as id,
result_json->>'email' as email,
result_json->>'created_at' as created_at
FROM psql_code_gen('get all premium users')
)
SELECT * FROM generated_data WHERE created_at > NOW() - INTERVAL '7 days';The extension logs important events using PostgreSQL's logging system:
LOG: LLM response and detected SQL command typeWARNING: When write operations are blocked in read-only mode
View logs:
# View PostgreSQL server logs
tail -f /var/log/postgresql/postgresql.log
# Or check PostgreSQL logs in psql
SELECT * FROM pg_read_file('postmaster.log', 0, -1) AS content LIMIT 50;