The Stainless SQL generator (experimental) creates PostgreSQL extensions that wrap a REST API as typed SQL functions. This allows developers to query an API directly from PostgreSQL using standard SQL. The generator maps OpenAPI specifications to PostgreSQL as follows:
- API resources $\rightarrow$ PostgreSQL schemas
- API objects $\rightarrow$ composite types
- API operations $\rightarrow$ SQL functions (retrieve, list, create, update, delete)
- Pagination $\rightarrow$ handled via set-returning functions.
-- Load the PL/Python dependency (required)
CREATE EXTENSION IF NOT EXISTS plpython3u;
-- Load the extension
CREATE EXTENSION my_api;
-- Configure your API key (requires a new session to take effect)
ALTER DATABASE mydb SET my_api.api_key = 'sk-my-api-key';
-- List users
SELECT * FROM users.list();
-- Retrieve a single user
SELECT * FROM users.retrieve('user_123');
-- Create a new user
SELECT * FROM users.create(name := 'Alice', email := 'alice@example.com');