Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Introduction

pg_sqlgen is a database extension for PostgreSQL that enables text-to-sql functionality. It is based on the Dubo-SQL method for fine-tuned Text-to-SQL generation.

As an extension, pg_sqlgen provides a number of advantages over other text-to-sql solutions, including:

  • Performance, eliminates network round-trips and connection overhead.
  • Security and data locality, no data is sent over a network or to an external service.
  • Authorization, everything lives within Postgres’ permission model instead of an external service.
  • Consistency, real-time RAG metadata maintenance via triggers.

Features

  • Simple text-to-sql generation via sqlgen.generate().
  • Explain what queries do and how they work via sqlgen.explain_query().
  • Continuous schema-change capture through extension-managed triggers.
  • Support for multiple model APIs
  • Support for multiple independent text-to-SQL engines with isolated RAG metadata.

Getting Started

Installation

Linux

Packages on the x86_64 platform are available on the following distributions:

  • Debian
  • Ubuntu
  • Fedora
  • RHEL

Download pakages from releases page.

For Debian/Ubuntu:

$ sudo dpkg -i pg-sqlgen_<release-version>_amd64.deb

For RHEL/Fedora

$ dnf -y install pg_sqlgen-<release-version>-1.x86_64.rpm

From source (OSX and other Linux distributions)

This assumes you already have PostgreSQL installed.

Follow the instructions in Setup pgvector before continuing. This is necessary for tests to pass, but not strictly needed for a functional developer environment.

$ cargo pgrx init --pg-config "$(which pg_config)"

# This may require root access
$ cargo pgrx install --pg-config "$(which pg_config)" --release

Building packages

Run this command to build both deb and rpm packages, producing artifacts in the repository’s root directory (rpm and fpm is required).

make package

This will create installation packages in the repository’s root directory.

Create the extension

Once the package has been installed to your PostgreSQL instance, run the following command.

CREATE EXTENSION sqlgen CASCADE;

Remove the extension

Removing the extension requires the CASCADE parameter.

DROP EXTENSION sqlgen CASCADE;

API

API

All functions and views live under the sqlgen prefix.

Generation

Generation

Functions related to text generation.


generate

Generates an SQL statement from a natural language query.

Parameters

user_query
  • Description: Natural language query
  • Type: TEXT
  • Optional: false
engine
  • Description: Engine to generate query with. If NULL, defaults to value set via set_default_engine.
  • Type: TEXT
  • Optional: true
  • Default: NULL

Example

-- Without default engine
SELECT sqlgen.generate('Get top 10 customers by monthly spend');

-- With default engine
SELECT sqlgen.generate('Get top 10 customers by monthly spend', 'my_engine');

explain_query

Explains what a query does and how it works in natural language.

Parameters

user_query
  • Description: SQL query
  • Type: TEXT
  • Optional: false
engine
  • Description: Engine to generate query with. If NULL, defaults to value set via set_default_engine.
  • Type: TEXT
  • Optional: true
  • Default: NULL

Example

-- Without default engine
SELECT sqlgen.explain_query('SELECT name, price FROM inventory');

-- Without default engine
SELECT sqlgen.explain_query('SELECT name, price FROM inventory', 'my_engine');

Engine

Engine

Engines contain all of the relevant prompt templates and metadata required to generate results from generation functions. An engine must be created in order to generate SQL.

All engine names must be in snake case.


engines

List of engines currently available.

Usage

SELECT * FROM sqlgen.engines;

create_engine

Create an engine.

Returns

  • Type: VOID

Parameters

name
  • Description: Engine name.
  • Type: TEXT
  • Optional: false
instruct_model
  • Description: Name of instruct model to use for engine (see model).
  • Type: TEXT
  • Optional: false
encoder_model
  • Description: Name of encoder model to use for engine (see model).
  • Type: TEXT
  • Optional: false
schema_names
  • Description: Names of database schemas that model operates on.
  • Type: TEXT[]
  • Optional: true
  • Default: ARRAY[current_schema()
table_filter_type
  • Description: Determines how tables are filtered in the RAG workflow. quick uses cosine similarity, smart uses an LLM.
  • Type: TEXT, either quick or smart
  • Optional: true
  • Default: smart
column_filter_limit
  • Description: Determines how many instances of column metadata are passed into any prompt at a time. Useful if there are strong limits on a model’s maximum token input.
  • Type: INT
  • Optional: true
  • Default: 128
error_correction_rounds
  • Description: Determines how many rounds of error correction should be made before giving up.
  • Type: INT
  • Optional: true
  • Default: 3
system_prompt_template
  • Description: System prompt used when generating SQL queries from text. See Prompt templates for more information about formatting and defaults.
  • Type: TEXT
  • Optional: true
  • Default: NULL
user_prompt_template
  • Description: User prompt used when generating SQL queries from text. See Prompt templates for more information about formatting and defaults.
  • Type: TEXT
  • Optional: true
  • Default: NULL
relevant_ddls_template
  • Description: Segment of user_prompt_template used to specify how relevant DDL’s are rendered. See Prompt templates for more information about formatting and defaults.
  • Type: TEXT
  • Optional: true
  • Default: NULL
similar_queries_template
  • Description: Segment of user_prompt_template used to specify how similar queries are rendered. Currently not in use. See Prompt templates for more information about formatting and defaults.
  • Type: TEXT
  • Optional: true
  • Default: NULL
filter_ddls_template
  • Description: User prompt used when filtering DDLs with the smart filter. See Prompt templates for more information about formatting and defaults.
  • Type: TEXT
  • Optional: true
  • Default: NULL
syntax_correction_template
  • Description: User prompt used when correcting SQL syntax. See Prompt templates for more information about formatting and defaults.
  • Type: TEXT
  • Optional: true
  • Default: NULL
explain_query_template
  • Description: User prompt used when generating explanations of how queries work. See Prompt templates for more information about formatting and defaults.
  • Type: TEXT
  • Optional: true
  • Default: NULL

Example

SELECT create_engine('my_engine', 'my_llm', 'my_encoder');

SELECT sqlgen.create_engine(
    name => 'stub_engine',
    instruct_model => 'stub_model',
    encoder_model => 'stub_model',
    schema_names => ARRAY['public', 'analytics'],
    table_filter_type => 'quick'
);

remove_engine

Remove an engine.

Returns

  • Type: VOID

Parameters

name
  • Description: Name of engine.
  • Type: TEXT
  • Optional: false

Example

SELECT sqlgen.remove_engine('my_engine');

set_default_engine

Sets the default engine. When generation functions are called without an engine specified, the value set here is used.

Returns

  • Type: VOID

Parameters

engine
  • Description: Name of engine.
  • Type: TEXT
  • Optional: false

Example

SELECT sqlgen.set_default_engine('my_engine');

remove_default_engine

Removes the default engine.

Returns

  • Type: VOID

Example

SELECT sqlgen.remove_default_engine('my_engine');

add_schema_to_engine

Enable query generation capabilities for a specified text-to-sql engine.

Returns

  • Type: VOID

Parameters

engine
  • Description: Name of engine.
  • Type: TEXT
  • Optional: false
schema
  • Description: Name of schema to add.
  • Type: TEXT
  • Optional: false

Example

SELECT sqlgen.add_schema_to_engine('my_engine', 'my_schema');

Model

Model

Functions related to model management. Models are necessary for engines to make inference calls.

Models must support at least one of two capabilities, text encoding or chat. For more information about what each model interface supports, See Model Configs for supported interfaces.

All model names must be in snake case.


models

List model profiles currently active.

Usage

SELECT * FROM sqlgen.models;

model_descriptions

List available models usable by add_model.

Usage

SELECT * FROM sqlgen.model_descriptions;

add_model

Creates a new model profile.

Returns

  • Type: VOID

Parameters

model_name
  • Description: Name of model
  • Type: TEXT
  • Optional: false
config_str
  • Description: Model configuration
  • Type: Model config
  • Optional: false

Example

SELECT sqlgen.add_model(
    'openai_chat',
    sqlgen.openai_completions_config(api_key => 'sk-proj-12345')
);

remove_model

Deletes a model profile.

Returns

  • Type: VOID

Parameters

model_name
  • Description: Name of model
  • Type: TEXT
  • Optional: false

Example

SELECT sqlgen.remove_model('openai_chat');

Model Configs

Model Configs

Model configs define how models interact with the extension. They are used as parameters for add_model.

ollama_config

Configuration for models that run on Ollama.

Returns

  • Type: JSON
  • Description: Model configuration.

Capabilities

  • Instruct
  • Encoding

Parameters

host
  • Description: Ollama API host.
  • Type: TEXT
  • Optional: true
  • Default: localhost:11434
model_name
  • Description: Name of model.
  • Type: TEXT
  • Optional: false
use_https
  • Description: Indicates if the RPC request should use SSL.
  • Type: BOOLEAN
  • Optional: true
  • Default: false
request_batch_size
  • Description: Number of requests that can be sent to the server in parallel.
  • Type: INTEGER
  • Optional: true
  • Default: 64

Example

SELECT sqlgen.add_model(
    'ollama_model',
    sqlgen.ollama_config(
        host => 'localhost:11434',
        model_name => 'llama3',
        use_https => false,
        request_batch_size => 16
    )
);

openai_completions_config

Configuration for models that use the OpenAI Completions API.

Returns

  • Type: JSON
  • Description: Model configuration.

Capabilities

  • Instruct

Parameters

url
  • Description: Completions API host.
  • Type: TEXT
  • Optional: true
  • Default: https://api.openai.com/v1/chat/completions
model
  • Description: Name of model.
  • Type: TEXT
  • Optional: true
  • Default: gpt-5.1
authorization_type
  • Description: Value to set in Authorization header before the api key.
  • Type: TEXT
  • Optional: true
  • Default: Bearer
api_key
  • Description: API key to be used in the Authorization header. Header is omitted if this value is not set.
  • Type: TEXT
  • Optional: true
  • Default: NULL
    SELECT sqlgen.add_model(
        'openai_chat_model',
        sqlgen.openai_completions_config(
            url => 'https://api.openai.com/v1/chat/completions',
            model => 'gpt-4.1',
            authorization_type => 'Bearer',
            api_key => 'sk-proj-1234567'
        )
    );

openai_embeddings_config

Configuration for models that use the OpenAI Embeddings API

Returns

  • Type: JSON
  • Description: Model configuration.

Capabilities

  • Encoding

Parameters

url
  • Description: Embeddings API host.
  • Type: TEXT
  • Optional: true
  • Default: https://api.openai.com/v1/embeddings
model
  • Description: Name of model.
  • Type: TEXT
  • Optional: true
  • Default: text-embedding-3-small
authorization_type
  • Description: Value to set in Authorization header before the api key.
  • Type: TEXT
  • Optional: true
  • Default: Bearer
api_key
  • Description: API key to be used in the Authorization header. Header is omitted if this value is not set.
  • Type: TEXT
  • Optional: true
  • Default: NULL
    SELECT sqlgen.add_model(
        'openai_embed_model',
        sqlgen.openai_embeddings_config(
            url => 'https://api.openai.com/v1/embeddings',
            model => 'text-embedding-3-small',
            authorization_type => 'Bearer',
            api_key => 'sk-proj-1234567'
        )
    );

stub_config

Outputs exactly what is provided in parameters. Used for testing purposes.

Returns

  • Type: JSON
  • Description: Model configuration.

Capabilities

  • Instruct
  • Encoding

Parameters

instruct_output
  • Description: Output when used as an instruct model.
  • Type: TEXT
  • Optional: true
  • Default: stub
encode_output
  • Description: Output when used as an encoder model.
  • Type: REAL[]
  • Optional: true
  • Default: ARRAY[0.0, 0.0, 0.0]::REAL[]

Example

    SELECT sqlgen.add_model(
        'test_stub',
        sqlgen.stub_config(
            instruct_output => 'Hello world!',
            encode_output => ARRAY[1.0, 2.5, 4.1]::REAL[]
        );
    );

Query Certification

Query Certification

Query certification is a mechanism for storing queries that users consider ideal examples. These certified queries serve as reference specimens that language models can learn from, improving the accuracy of generated SQL.

A maximum of three certified queries with a cosine similarity of ≥ 0.75 are injected into the prompt.


certify_query

Certifies a functional query so that it can be provided to prompts to boost accuracy. Returns a unique identifier for the certified query.

Returns

  • Type: TEXT
  • Description: A UUID identifying the query certification instance.

Parameters

language_query
  • Description: Natural language query that accurately reflects sql_query.
  • Type: TEXT
  • Optional: false
sql_query
  • Description: SQL query that accurately reprsents the language_query.
  • Type: TEXT
  • Optional: false
engine
  • Description: Engine to set query for. If NULL, defaults to value set via set_default_engine.
  • Type: TEXT
  • Optional: true
  • Default: NULL

Example

-- Without default engine
SELECT sqlgen.certify_query(
    'SELECT name, price FROM inventory',
    'Get name and price from inventory'
);

-- Without default engine
SELECT sqlgen.certify_query(
    'SELECT name, price FROM inventory',
    'Get name and price from inventory',
    'my_engine'
);

decertify_query

Removes a certified query.

Returns

  • Type: VOID

Parameters

id
  • Description: Identifier for certified query.
  • Type: TEXT
  • Optional: false
engine
  • Description: Engine to remove query from. If NULL, defaults to value set via set_default_engine.
  • Type: TEXT
  • Optional: true
  • Default: NULL

Example

-- Without default engine
SELECT sqlgen.decertify_query('6d6e1596-f3a8-4847-a1d8-ccb3d60fc0e6');

-- Without default engine
SELECT sqlgen.decertify_query(
    '6d6e1596-f3a8-4847-a1d8-ccb3d60fc0e6',
    'my_engine'
);

Vector

Vector

Functions related to vector backend management.

set_vector_backend

Toggles the vector implementation used internally. See Internals - Vector Backend for more details.

Returns

  • Type: TEXT

Parameters

backend
  • Description: Specifies which backend to use. If omitted, it switches to the unused backend. Can either be pgvector or default.
  • Type: TEXT
  • Optional: true
  • Default: NULL

Example

-- Switch to default backend
SELECT sqlgen.set_vector_backend('default');

-- Switch to pgvector backend
SELECT sqlgen.set_vector_backend('pgvector');

-- Switch to unused baclemd
SELECT sqlgen.set_vector_backend();

Examples

Examples

pg_sqlgen works best with OpenAI models. For Ollama, it’s been tested with Llama 3.1 and nomic-embed-text, though other models may provide better output.

Setup with OpenAI

OpenAI keys can be generated here.

-- Create the extension.
CREATE EXTENSION sqlgen CASCADE;

-- Create an instruct model.
SELECT sqlgen.add_model(
    'openai_chat',
    sqlgen.openai_completions_config(api_key => '<your OpenAI API key>')
);

-- Create an encoding model.
SELECT sqlgen.add_model(
    'openai_embed',
    sqlgen.openai_embed_config(api_key => '<your OpenAI API key>')
);

-- Create an engine
SELECT sqlgen.create_engine('example_engine', 'openai_chat', 'openai_embed');

-- Set engine as default
SELECT sqlgen.set_default_engine('example_engine');

-- Execute a generated query
BEGIN;
    SELECT sqlgen.query('Get highest paying customer');
    FETCH ALL FROM sqlgen_query;
COMMIT;

Setup with Ollama

Set up nomic-embed-text and llama3.1 with Ollama.

Start Ollama in a separate console, or via the app.

$ ollama serve

Set up nomic-embed-text and llama3.1.

ollama pull nomic-embed-text
ollama pull llama3.1

Now setup the database extension.

-- Create the extension.
CREATE EXTENSION sqlgen CASCADE;

-- Create an instruct model.
SELECT sqlgen.add_model(
    'ollama_instruct',
    sqlgen.ollama_config(model_name => 'llama3.1')
);

-- Create an encoding model.
SELECT sqlgen.add_model(
    'ollama_embed',
    sqlgen.openai_embeddings_config(model_name => 'nomic-embed-text')
);

-- Create an engine
SELECT sqlgen.create_engine('example_engine', 'ollama_instruct', 'ollama_embed');

-- Set engine as default
SELECT sqlgen.set_default_engine('example_engine');

-- Execute a generated query
BEGIN;
    SELECT sqlgen.query('Get highest paying customer');
    FETCH ALL FROM sqlgen_query;
COMMIT;

Development

Setup

pg_sqlgen operates as a regular Rust codebase. It can be managed via cargo and cargo pgrx commands.

Initialize pgrx

Install cargo-pgrx and set up a PostgreSQL instance.

$ cargo install cargo-pgrx
$ cargo pgrx init --pg18 download
$ export PG_CONFIG=$(cargo pgrx info pg-config 18)

Option 2 - Use an existing PostgreSQL instance

Do this if you plan on using an existing instance of PostgreSQL for development.

$ cargo install cargo-pgrx
$ cargo pgrx init --pg18 /path/to/pg_config
$ export PG_CONFIG=/path/to/pg_config

Setup pgvector

The pgvector extension is an optional dependency for pg_sqlgen. It must be installed manually.

Note: This step is not necessary if pgvector is already installed on an existing PostgreSQL instance.

$ git clone https://github.com/pgvector/pgvector.git
$ cd pgvector
$ make clean
$ make
$ make install

Testing

Testing is done through normal cargo pgrx methods.

Unit tests

$ cargo pgrx test

Regression tests

$ cargo pgrx regress

Internals

These internal mechanisms may inform how to configure and tune the extension.

Vector backends

Vectors enable search capabilities on entities necessary for the RAG workflow. The vector backend determines how vectors are stored and processed.

The backend can be changed via set_vector_backend().

default

Vectors are stored as an array of single precision floating-point numbers (FLOAT4[]). Cosine distance is computed with an internal function that detects available CPU features and leverages SIMD instructions when supported.

pgvector

Uses the VECTOR type and cosine distance <=> operator. These are usually provided by pgvector, but other extensions, such as VectorChord, can be used too.

RAG workflow

The rag workflow for text-to-sql generation works as follows:

  • Get DDLs relevant to the user query. The process will vary depending on the engine’s table_filter_type value:
    • smart - The instruct model is asked to filter for DDLs relevant to the user query.
    • quick - DDLs are filtedetects available CPU features and leverages SIMD instructions when supported.red via cosine similarity through pgvector.
  • Provide the user query, DDLs, and relevant instructions to the instruct model.
  • The resulting query run against a PREPARE statement to check if it’s valid.
    • If it’s valid, return the query to the user.
    • If the query is not valid, the instruct model is asked to fix the query. The new query is returned to the user.

Metadata triggers

Each engine creates triggers for these operations in its target schema:

  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE
  • COMMENT ON

When new tables or columns are added, the encoder model generates semantic vectors. These triggers are deleted when the engine is removed.

Prompt templates

  • All prompt templates are rendered using tera.
  • Templates require specific blocks to render correctly, some block values can be specified as parameters when creating an engine.
  • Default prompts can be found here.

system_prompt_template

System prompt containing high-level instructions for text-to-sql generation.

Blocks

  • output_format_description - Text instructions on how the model should output structured data to be consumed by the engine.

user_prompt_template

User prompt containing specific instructions for text-to-sql generation.

Blocks

  • user_query - Natural language query to be converted
  • relevant_ddls_block - DDL list with optional surrounding text, see relevant_ddls_template
  • similar_queries_block - List of SQL queries similar to the user query, see similar_queries_template.

relevant_ddls_template

Block used to present DDL listing when generating SQL from text.

Blocks

  • relevant_ddls - Raw list of DDLs relevant to the query

similar_queries_template

Block used to present similar queries when generating SQL from text. Currently unused.

filter_ddls_template

User prompt containing specific instructions for filtering DDLs. Used when table_filter_type is set to smart.

Blocks

  • user_query - User query used to filter DDLs with.
  • relevant_ddls - List of DDLs to filter.

syntax_correction_template

User prompt containing specific instructions for correcting an erroneous query.

Blocks

  • query - The erroneous query.
  • error_message - Error related to the query.

explain_query_template

User prompt containing specific instructions for explaining what a query does and how it works.

Blocks

  • output_format_description - Text instructions on how the model should output structured data to be consumed by the engine.
  • sql_query - The query to be explained
  • explain - Contains the output of EXPLAIN against the SQL query.