[GH-ISSUE #1170] Allow LLMs to Query a Database Directly #62631

Closed
opened 2026-05-03 09:50:24 -05:00 by GiteaMirror · 19 comments
Owner

Originally created by @FaizelK on GitHub (Nov 17, 2023).
Original GitHub issue: https://github.com/ollama/ollama/issues/1170

I have installed ollama and can run prompts, example: ollama run llama2 "why is the sky blue?"

Is there any way to connect to MYSQL database and start asking about database data, example:
`###### database file######
database.cnf
host="localhost"
user="admin"
passsword="admin"
database="mDatabase"

ollama run llama2 database "how many users are administrators?"

I have searched repository and I cannot see anything like this, I also went to langchain docs but did not see an example with ollama

`

Originally created by @FaizelK on GitHub (Nov 17, 2023). Original GitHub issue: https://github.com/ollama/ollama/issues/1170 I have installed ollama and can run prompts, example: `ollama run llama2 "why is the sky blue?"` Is there any way to connect to MYSQL database and start asking about database data, example: `###### database file###### database.cnf host="localhost" user="admin" passsword="admin" database="mDatabase" ollama run llama2 database "how many users are administrators?" I have searched repository and I cannot see anything like this, I also went to langchain docs but did not see an example with ollama `
GiteaMirror added the feature request label 2026-05-03 09:50:24 -05:00
Author
Owner

@BruceMacD commented on GitHub (Nov 17, 2023):

Hi @FaizelK this is not built into Ollama, but it is a good example of a workflow that you could build on top of Ollama. As an aside I would recommend dumping the contents of the database to a file which you parse into structured data and feed into Ollama rather than giving the LLM direct access to query your database.

<!-- gh-comment-id:1816957923 --> @BruceMacD commented on GitHub (Nov 17, 2023): Hi @FaizelK this is not built into Ollama, but it is a good example of a workflow that you could build on top of Ollama. As an aside I would recommend dumping the contents of the database to a file which you parse into structured data and feed into Ollama rather than giving the LLM direct access to query your database.
Author
Owner

@FaizelK commented on GitHub (Nov 18, 2023):

@BruceMacD i would think access directly to database is better than exporting data into a file, can you imagine a database with millions of records? also langchain and openai have this plugin (database agents) I am working on this so it can work with ollama. if you create a database user with only read access/privilege then it is no harm.

<!-- gh-comment-id:1817463576 --> @FaizelK commented on GitHub (Nov 18, 2023): @BruceMacD i would think access directly to database is better than exporting data into a file, can you imagine a database with millions of records? also langchain and openai have this plugin (database agents) I am working on this so it can work with ollama. if you create a database user with only read access/privilege then it is no harm.
Author
Owner

@MostlyKIGuess commented on GitHub (Nov 18, 2023):

Hi @FaizelK this is not built into Ollama, but it is a good example of a workflow that you could build on top of Ollama. As an aside I would recommend dumping the contents of the database to a file which you parse into structured data and feed into Ollama rather than giving the LLM direct access to query your database.

How exactly could someone do this?

<!-- gh-comment-id:1817597580 --> @MostlyKIGuess commented on GitHub (Nov 18, 2023): > Hi @FaizelK this is not built into Ollama, but it is a good example of a workflow that you could build on top of Ollama. As an aside I would recommend dumping the contents of the database to a file which you parse into structured data and feed into Ollama rather than giving the LLM direct access to query your database. How exactly could someone do this?
Author
Owner

@igorschlum commented on GitHub (Nov 21, 2023):

Hello @FaizelK,

Direct database connections are not currently supported by Ollama. However, you can create a webpage that connects to the database and extracts data from it, which can then be analyzed by Ollama using the following link:

https://ollama.ai/blog/building-llm-powered-web-apps

<!-- gh-comment-id:1821126044 --> @igorschlum commented on GitHub (Nov 21, 2023): Hello @FaizelK, Direct database connections are not currently supported by Ollama. However, you can create a webpage that connects to the database and extracts data from it, which can then be analyzed by Ollama using the following link: https://ollama.ai/blog/building-llm-powered-web-apps
Author
Owner

@UnexpectedMaker commented on GitHub (Jan 30, 2024):

I'm also looking for a way to use OLLAMA with a local SQLITE database - The reason this is important is contents of a database can change, a little, or a lot, by so many external factors, and I want the query of the data in the database to be on the current data in there.

Converting the data into a file format for ingesting into a vector store means that the data is now static and instantly out of date.

I also want the system to be able to not just query the data but to also alter the data as required. Think about a stock management system, where you can ask how many of product XX is in stock, and also reduce the quantity when needed etc

All langchain examples I've seen so far require the use of OpenAI - and I'd like this to be 100% local and not via a hosted website for data querying.

<!-- gh-comment-id:1915932760 --> @UnexpectedMaker commented on GitHub (Jan 30, 2024): I'm also looking for a way to use OLLAMA with a local SQLITE database - The reason this is important is contents of a database can change, a little, or a lot, by so many external factors, and I want the query of the data in the database to be on the current data in there. Converting the data into a file format for ingesting into a vector store means that the data is now static and instantly out of date. I also want the system to be able to not just query the data but to also alter the data as required. Think about a stock management system, where you can ask how many of product XX is in stock, and also reduce the quantity when needed etc All langchain examples I've seen so far require the use of OpenAI - and I'd like this to be 100% local and not via a hosted website for data querying.
Author
Owner

@easp commented on GitHub (Jan 30, 2024):

@UnexpectedMaker You found the Github. Did you check out the examples folder in the repo?

There are multiple Langchain examples using Ollama. The ones I checked use a local option for generating embeddings.

Also make sure you check out SQLite-VSS support in Langchain.

<!-- gh-comment-id:1915950833 --> @easp commented on GitHub (Jan 30, 2024): @UnexpectedMaker You found the Github. Did you check out the examples folder in the repo? There are multiple Langchain examples using Ollama. The ones I checked use a local option for generating embeddings. Also make sure you check out SQLite-VSS support in Langchain.
Author
Owner

@UnexpectedMaker commented on GitHub (Jan 30, 2024):

@UnexpectedMaker You found the Github. Did you check out the examples folder in the repo?

There are multiple Langchain examples using Ollama. The ones I checked use a local option for generating embeddings.

Also make sure you check out SQLite-VSS support in Langchain.

I found the GitHub - I checked the examples. None deal with or show how to do ollama + local model + langchain (or llamaIndex) to actually access a standard SQL database (not as a vector source, 100% was NOT what I asked for) and query it to return results.

I dont want to ingest documents and store the quantized vector data in an SQL database - I want the model to look into a normal SQL database and have it strain itself on the structure - and then be able to pull results out of the tables.

Every example I can find online (google, blogs, YouTube) show this only working with llama.cpp + (llamaindex/langchain) and openai.

Rather than asking me to check the examples - if you think there's an actual example in there that does what I have asked about, and what the OP asked about, please point specifically to that example.

Thanks :)

<!-- gh-comment-id:1916029527 --> @UnexpectedMaker commented on GitHub (Jan 30, 2024): > @UnexpectedMaker You found the Github. Did you check out the examples folder in the repo? > > There are multiple Langchain examples using Ollama. The ones I checked use a local option for generating embeddings. > > Also make sure you check out SQLite-VSS support in Langchain. I found the GitHub - I checked the examples. None deal with or show how to do ollama + local model + langchain (or llamaIndex) to actually access a standard SQL database (not as a vector source, 100% was NOT what I asked for) and query it to return results. I dont want to ingest documents and store the quantized vector data in an SQL database - I want the model to look into a normal SQL database and have it strain itself on the structure - and then be able to pull results out of the tables. Every example I can find online (google, blogs, YouTube) show this only working with llama.cpp + (llamaindex/langchain) and openai. Rather than asking me to check the examples - if you think there's an actual example in there that does what I have asked about, and what the OP asked about, please point specifically to that example. Thanks :)
Author
Owner

@FaizelK commented on GitHub (Feb 20, 2024):

I am back, back with gifts, sweets and all the good stuff.
Yes, I have found an open source solution to my original question. I will add my reference if i get 10 hearts on this solution
After 100's of hours ...... Here it goes........
@BruceMacD
@MostlyKIGuess

from langchain_community.chat_models import ChatOllama
from langchain.callbacks.manager import CallbackManager
from langchain.callbacks.streaming_stdout import StreamingStdOutCallbackHandler
from langchain.llms import Ollama
from langchain_community.utilities import SQLDatabase
from langchain_core.prompts import ChatPromptTemplate
from langchain_core.output_parsers import StrOutputParser
from langchain_core.runnables import RunnablePassthrough

# Define chat models
llama2_chat = ChatOllama(model="llama2:13b-chat")  # Change model if required
llama2_code = ChatOllama(model="codellama:7b-instruct")

# Set model (choose one of the following options)
llm = llama2_chat  # Option 1
# llm = Ollama(model="llama2:13b-chat", callback_manager=CallbackManager([StreamingStdOutCallbackHandler()]))  # Option 2

# Connect to database
db = SQLDatabase.from_uri("mysql+pymysql://database_user:password@localhost/databasName")

# Define functions for schema retrieval and query execution
def get_schema(_):
    return db.get_table_info()

def run_query(query):
    return db.run(query)

# Create prompt templates
template1 = """
Based on the table schema below, write a SQL query that would answer the user's question:
{schema}

Question: {question}
SQL Query:
"""
prompt = ChatPromptTemplate.from_messages(
    [
        ("system", "Given an input question, convert it to a SQL query. No pre-amble."),
        ("human", template1),
    ]
)

template2 = """
Based on the table schema below, question, sql query, and sql response, write a natural language response:
{schema}

Question: {question}
SQL Query: {query}
SQL Response: {response}
"""
prompt_response = ChatPromptTemplate.from_messages(
    [
        (
            "system",
            "Given an input question and SQL response, convert it to a natural language answer. No pre-amble.",
        ),
        ("human", template2),
    ]
)

# Construct chains for query generation and response
sql_response = (
    RunnablePassthrough.assign(schema=get_schema)
    | prompt
    | llm.bind(stop=["\nSQLResult:"])
    | StrOutputParser()
)

full_chain = (
    RunnablePassthrough.assign(query=sql_response)
    | RunnablePassthrough.assign(
        schema=get_schema,
        response=lambda x: db.run(x["query"]),
    )
    | prompt_response
    | llm
)

# Invoke the full chain and print the final response
TheFinalResponse = full_chain.invoke({"question": ThePrompt})
print(TheFinalResponse)
<!-- gh-comment-id:1954700235 --> @FaizelK commented on GitHub (Feb 20, 2024): I am back, back with gifts, sweets and all the good stuff. Yes, I have found an open source solution to my original question. I will add my reference if i get 10 hearts on this solution After 100's of hours ...... Here it goes........ @BruceMacD @MostlyKIGuess ``` from langchain_community.chat_models import ChatOllama from langchain.callbacks.manager import CallbackManager from langchain.callbacks.streaming_stdout import StreamingStdOutCallbackHandler from langchain.llms import Ollama from langchain_community.utilities import SQLDatabase from langchain_core.prompts import ChatPromptTemplate from langchain_core.output_parsers import StrOutputParser from langchain_core.runnables import RunnablePassthrough # Define chat models llama2_chat = ChatOllama(model="llama2:13b-chat") # Change model if required llama2_code = ChatOllama(model="codellama:7b-instruct") # Set model (choose one of the following options) llm = llama2_chat # Option 1 # llm = Ollama(model="llama2:13b-chat", callback_manager=CallbackManager([StreamingStdOutCallbackHandler()])) # Option 2 # Connect to database db = SQLDatabase.from_uri("mysql+pymysql://database_user:password@localhost/databasName") # Define functions for schema retrieval and query execution def get_schema(_): return db.get_table_info() def run_query(query): return db.run(query) # Create prompt templates template1 = """ Based on the table schema below, write a SQL query that would answer the user's question: {schema} Question: {question} SQL Query: """ prompt = ChatPromptTemplate.from_messages( [ ("system", "Given an input question, convert it to a SQL query. No pre-amble."), ("human", template1), ] ) template2 = """ Based on the table schema below, question, sql query, and sql response, write a natural language response: {schema} Question: {question} SQL Query: {query} SQL Response: {response} """ prompt_response = ChatPromptTemplate.from_messages( [ ( "system", "Given an input question and SQL response, convert it to a natural language answer. No pre-amble.", ), ("human", template2), ] ) # Construct chains for query generation and response sql_response = ( RunnablePassthrough.assign(schema=get_schema) | prompt | llm.bind(stop=["\nSQLResult:"]) | StrOutputParser() ) full_chain = ( RunnablePassthrough.assign(query=sql_response) | RunnablePassthrough.assign( schema=get_schema, response=lambda x: db.run(x["query"]), ) | prompt_response | llm ) # Invoke the full chain and print the final response TheFinalResponse = full_chain.invoke({"question": ThePrompt}) print(TheFinalResponse) ```
Author
Owner

@igorschlum commented on GitHub (Feb 20, 2024):

Nice.

I understand that the user describes their query in natural language. It's converted into SQL queries by an LLM. The script retrieves results from a database. Then, another LLM transforms those results into an easy-to-understand response for the user.

<!-- gh-comment-id:1955191449 --> @igorschlum commented on GitHub (Feb 20, 2024): Nice. I understand that the user describes their query in natural language. It's converted into SQL queries by an LLM. The script retrieves results from a database. Then, another LLM transforms those results into an easy-to-understand response for the user.
Author
Owner

@FaizelK commented on GitHub (Feb 21, 2024):

Yes,
The users prompt could be

User prompt
"how many users are admins?" or "how many users in total?"

first LLM
translates this user promp to sql "select count(*) from table"

second LLM
translates the sql results into english and final response is
"there are 10 users in total"

<!-- gh-comment-id:1955969158 --> @FaizelK commented on GitHub (Feb 21, 2024): Yes, The users prompt could be User prompt "how many users are admins?" or "how many users in total?" first LLM translates this user promp to sql "select count(*) from table" second LLM translates the sql results into english and final response is "there are 10 users in total"
Author
Owner

@FaizelK commented on GitHub (Feb 21, 2024):

Does anyone know how to become a contributor? i would like to add this module as part of this repo

<!-- gh-comment-id:1955975092 --> @FaizelK commented on GitHub (Feb 21, 2024): Does anyone know how to become a contributor? i would like to add this module as part of this repo
Author
Owner

@luckydonald commented on GitHub (Feb 23, 2024):

@FaizelK you can always fork the repository, copying it into your account, work on it, and in the end submit a pull request to this repository, which can then be merged into the code here.

It's not garantieed to be merged, but people can also come to your fork in that case and use that instead.

<!-- gh-comment-id:1960782674 --> @luckydonald commented on GitHub (Feb 23, 2024): @FaizelK you can always **fork** the repository, copying it into your account, work on it, and in the end submit a **pull request** to this repository, which can then be merged into the code here. It's not garantieed to be merged, but people can also come to your fork in that case and use that instead.
Author
Owner

@monjurulkarim commented on GitHub (Feb 24, 2024):

I am back, back with gifts, sweets and all the good stuff. Yes, I have found an open source solution to my original question. I will add my reference if i get 10 hearts on this solution After 100's of hours ...... Here it goes........ @BruceMacD @MostlyKIGuess

from langchain_community.chat_models import ChatOllama
from langchain.callbacks.manager import CallbackManager
from langchain.callbacks.streaming_stdout import StreamingStdOutCallbackHandler
from langchain.llms import Ollama
from langchain_community.utilities import SQLDatabase
from langchain_core.prompts import ChatPromptTemplate
from langchain_core.output_parsers import StrOutputParser
from langchain_core.runnables import RunnablePassthrough

# Define chat models
llama2_chat = ChatOllama(model="llama2:13b-chat")  # Change model if required
llama2_code = ChatOllama(model="codellama:7b-instruct")

# Set model (choose one of the following options)
llm = llama2_chat  # Option 1
# llm = Ollama(model="llama2:13b-chat", callback_manager=CallbackManager([StreamingStdOutCallbackHandler()]))  # Option 2

# Connect to database
db = SQLDatabase.from_uri("mysql+pymysql://database_user:password@localhost/databasName")

# Define functions for schema retrieval and query execution
def get_schema(_):
    return db.get_table_info()

def run_query(query):
    return db.run(query)

# Create prompt templates
template1 = """
Based on the table schema below, write a SQL query that would answer the user's question:
{schema}

Question: {question}
SQL Query:
"""
prompt = ChatPromptTemplate.from_messages(
    [
        ("system", "Given an input question, convert it to a SQL query. No pre-amble."),
        ("human", template1),
    ]
)

template2 = """
Based on the table schema below, question, sql query, and sql response, write a natural language response:
{schema}

Question: {question}
SQL Query: {query}
SQL Response: {response}
"""
prompt_response = ChatPromptTemplate.from_messages(
    [
        (
            "system",
            "Given an input question and SQL response, convert it to a natural language answer. No pre-amble.",
        ),
        ("human", template2),
    ]
)

# Construct chains for query generation and response
sql_response = (
    RunnablePassthrough.assign(schema=get_schema)
    | prompt
    | llm.bind(stop=["\nSQLResult:"])
    | StrOutputParser()
)

full_chain = (
    RunnablePassthrough.assign(query=sql_response)
    | RunnablePassthrough.assign(
        schema=get_schema,
        response=lambda x: db.run(x["query"]),
    )
    | prompt_response
    | llm
)

# Invoke the full chain and print the final response
TheFinalResponse = full_chain.invoke({"question": ThePrompt})
print(TheFinalResponse)

@FaizelK Could you please share the original reference?

<!-- gh-comment-id:1962638196 --> @monjurulkarim commented on GitHub (Feb 24, 2024): > I am back, back with gifts, sweets and all the good stuff. Yes, I have found an open source solution to my original question. I will add my reference if i get 10 hearts on this solution After 100's of hours ...... Here it goes........ @BruceMacD @MostlyKIGuess > > ``` > from langchain_community.chat_models import ChatOllama > from langchain.callbacks.manager import CallbackManager > from langchain.callbacks.streaming_stdout import StreamingStdOutCallbackHandler > from langchain.llms import Ollama > from langchain_community.utilities import SQLDatabase > from langchain_core.prompts import ChatPromptTemplate > from langchain_core.output_parsers import StrOutputParser > from langchain_core.runnables import RunnablePassthrough > > # Define chat models > llama2_chat = ChatOllama(model="llama2:13b-chat") # Change model if required > llama2_code = ChatOllama(model="codellama:7b-instruct") > > # Set model (choose one of the following options) > llm = llama2_chat # Option 1 > # llm = Ollama(model="llama2:13b-chat", callback_manager=CallbackManager([StreamingStdOutCallbackHandler()])) # Option 2 > > # Connect to database > db = SQLDatabase.from_uri("mysql+pymysql://database_user:password@localhost/databasName") > > # Define functions for schema retrieval and query execution > def get_schema(_): > return db.get_table_info() > > def run_query(query): > return db.run(query) > > # Create prompt templates > template1 = """ > Based on the table schema below, write a SQL query that would answer the user's question: > {schema} > > Question: {question} > SQL Query: > """ > prompt = ChatPromptTemplate.from_messages( > [ > ("system", "Given an input question, convert it to a SQL query. No pre-amble."), > ("human", template1), > ] > ) > > template2 = """ > Based on the table schema below, question, sql query, and sql response, write a natural language response: > {schema} > > Question: {question} > SQL Query: {query} > SQL Response: {response} > """ > prompt_response = ChatPromptTemplate.from_messages( > [ > ( > "system", > "Given an input question and SQL response, convert it to a natural language answer. No pre-amble.", > ), > ("human", template2), > ] > ) > > # Construct chains for query generation and response > sql_response = ( > RunnablePassthrough.assign(schema=get_schema) > | prompt > | llm.bind(stop=["\nSQLResult:"]) > | StrOutputParser() > ) > > full_chain = ( > RunnablePassthrough.assign(query=sql_response) > | RunnablePassthrough.assign( > schema=get_schema, > response=lambda x: db.run(x["query"]), > ) > | prompt_response > | llm > ) > > # Invoke the full chain and print the final response > TheFinalResponse = full_chain.invoke({"question": ThePrompt}) > print(TheFinalResponse) > ``` @FaizelK Could you please share the original reference?
Author
Owner

@monjurulkarim commented on GitHub (Feb 28, 2024):

@igorschlum I think here is the source: https://github.com/langchain-ai/langchain/blob/master/cookbook/LLaMA2_sql_chat.ipynb

<!-- gh-comment-id:1969695958 --> @monjurulkarim commented on GitHub (Feb 28, 2024): @igorschlum I think here is the source: https://github.com/langchain-ai/langchain/blob/master/cookbook/LLaMA2_sql_chat.ipynb
Author
Owner

@FaizelK commented on GitHub (Feb 28, 2024):

Yep, that is the source

<!-- gh-comment-id:1969702940 --> @FaizelK commented on GitHub (Feb 28, 2024): Yep, that is the source
Author
Owner

@FaizelK commented on GitHub (May 3, 2024):

hi @BruceMacD have you managed to implement this feature yet?

<!-- gh-comment-id:2092649324 --> @FaizelK commented on GitHub (May 3, 2024): hi @BruceMacD have you managed to implement this feature yet?
Author
Owner

@BruceMacD commented on GitHub (May 3, 2024):

@FaizelK this probably isn't a feature we would build into Ollama directly, but people have built tools around this use-case now.

I haven't used this yet, but Vanna looks interesting:
https://vanna.ai/docs/other-database-ollama-chromadb/

<!-- gh-comment-id:2093514459 --> @BruceMacD commented on GitHub (May 3, 2024): @FaizelK this probably isn't a feature we would build into Ollama directly, but people have built tools around this use-case now. I haven't used this yet, but Vanna looks interesting: https://vanna.ai/docs/other-database-ollama-chromadb/
Author
Owner

@jmorganca commented on GitHub (May 9, 2024):

Thanks for the issue! Yes I will close this for

#834
#3880

<!-- gh-comment-id:2103577049 --> @jmorganca commented on GitHub (May 9, 2024): Thanks for the issue! Yes I will close this for #834 #3880
Author
Owner

@xhinodread commented on GitHub (Sep 1, 2024):

Hi, that is super, but, how can by than in javascript
I don t can
saludos

<!-- gh-comment-id:2323525288 --> @xhinodread commented on GitHub (Sep 1, 2024): Hi, that is super, but, how can by than in javascript I don t can saludos
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/ollama#62631