SQL

The SQL generator is currently experimental and we are excited for you to try it out! This extension has not yet been exhaustively tested in production environments and may be missing some features you would expect in a stable release. As we continue development, there may be breaking changes that require updates to your code.

We would love your feedback! Please share any suggestions, bug reports, feature requests, or general thoughts at support@stainless.com.

The Stainless SQL generator creates PostgreSQL extensions that wrap your REST API as typed SQL functions. You can query any API directly from PostgreSQL using standard SQL, without writing application code.

How it works

The SQL generator maps your OpenAPI specification to PostgreSQL constructs:

• Each API resource becomes a PostgreSQL schema
• API objects map to composite types
• API operations become SQL functions (retrieve, list, create, update, delete)
• Pagination is handled automatically through set-returning functions

The following hypothetical example shows what this looks like for an API with a users resource:

-- 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');

Prerequisites

Before generating a SQL extension, ensure you have:

• PostgreSQL 14 or higher with the PL/Python extension installed
• A Stainless project with an OpenAPI specification
• A python target configured in your Stainless config (the SQL extension depends on the generated Python SDK). See the Python target documentation for setup details.

PL/Python is a procedural language that allows PostgreSQL to execute Python code. The generated extension uses it to make HTTP requests to your API. Most PostgreSQL distributions include PL/Python, but it may need to be installed separately depending on your platform. If your PostgreSQL installation was built from source, ensure it was compiled with the --with-python flag.

Configuration

To generate a SQL extension, add the sql target to your Stainless configuration file:

targets:
  sql:
    extension_name: my_api
    edition: sql.2025-12-08

Common configuration options

targets:
  sql:
    extension_name: my_api
    edition: sql.2025-12-08

    # Link your production repository
    production_repo: my-org/my-api-sql

    # Enable publishing to PGXN
    publish:
      pgxn: true

For a complete list of configuration options, see the SQL target reference.

Extension name

The extension_name field defines the name of your PostgreSQL extension. This is the identifier users pass to CREATE EXTENSION:

CREATE EXTENSION my_api;

The extension name must be a valid PostgreSQL identifier: lowercase letters, digits, and underscores, starting with a letter. Choose a name that clearly identifies your API, such as my_api or acme.

Editions

Editions allow Stainless to make improvements to SDKs that are not backwards-compatible. You can explicitly opt in to new editions when you are ready. See the SDK and config editions reference for more information.

sql.2025-12-08

• Initial edition for SQL (used by default if no edition is specified)

Publishing

You can publish your generated extension to PGXN (PostgreSQL Extension Network) for distribution.

You need to link a production repository where Stainless will push your extension code.

To enable publishing, add the publish configuration:

targets:
  sql:
    extension_name: my_api
    production_repo: my-org/my-api-sql
    publish:
      pgxn: true

When you merge a Release PR:

1. Stainless creates a git tag with the version number
2. The extension is packaged and published to PGXN
3. Users can install it with pgxn install my_api

Troubleshooting

PL/Python not available

If you see an error like extension "plpython3u" is not available, you need to install the PL/Python package for your PostgreSQL distribution:

• Debian/Ubuntu: sudo apt-get install postgresql-plpython3-<version>
• RHEL/CentOS: sudo yum install postgresql<version>-plpython3
• macOS (Homebrew): PL/Python is included with the postgresql formula

After installing, create the extension in your database:

CREATE EXTENSION plpython3u;

Permission denied when setting configuration

If you see permission denied to set parameter, the current database user does not have permission to set extension configuration values. You need superuser or ALTER DATABASE privileges:

-- As a superuser
ALTER DATABASE mydb SET my_api.api_key = 'sk-my-api-key';

-- Or grant permissions to a specific role
GRANT ALTER ON DATABASE mydb TO my_role;

Extension not found after installation

If CREATE EXTENSION my_api fails with extension not found:

1. Verify the extension files are in the correct PostgreSQL extension directory (pg_config --sharedir/extension)
2. Restart the PostgreSQL server if you installed the extension while it was running
3. Check that the extension control file (my_api.control) exists in the extension directory

API calls timing out from within PostgreSQL

PostgreSQL functions have no default HTTP timeout. If your API is slow to respond, queries may appear to hang. You can configure a statement timeout at the session level:

SET statement_timeout = '30s';
SELECT * FROM users.list();

For persistent timeout configuration, set it at the database level:

ALTER DATABASE mydb SET statement_timeout = '30s';