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 viaset_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 viaset_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.
quickuses cosine similarity,smartuses an LLM. - Type:
TEXT, eitherquickorsmart - 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_templateused 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_templateused 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
smartfilter. 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
Authorizationheader before the api key. - Type:
TEXT - Optional:
true - Default:
Bearer
api_key
- Description: API key to be used in the
Authorizationheader. 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
Authorizationheader before the api key. - Type:
TEXT - Optional:
true - Default:
Bearer
api_key
- Description: API key to be used in the
Authorizationheader. 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 viaset_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 viaset_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
pgvectorordefault. - 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
Option 1 [Recommended] - Downloaded PostgreSQL instance with 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_typevalue: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
PREPAREstatement 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 TABLEALTER TABLEDROP TABLECOMMENT 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
EXPLAINagainst the SQL query.