Allow LLMs to Query a Database Directly

[FaizelK]

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

`

[BruceMacD]

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.

[FaizelK]

@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.

[MostlyKIGuess]

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?

[igorschlum]

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

[UnexpectedMaker]

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.

[easp]

@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.

[UnexpectedMaker]

@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 :)

[FaizelK]

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)

[igorschlum]

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.

[FaizelK]

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"

[FaizelK]

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

[luckydonald]

@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.

[monjurulkarim]

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?

[monjurulkarim]

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

[FaizelK]

Yep, that is the source

[FaizelK]

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

[BruceMacD]

@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/

[jmorganca]

Thanks for the issue! Yes I will close this for

#834 #3880