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;