A user can ask for data in ordinary language, but a relational database needs a structured query. SQL bridges that gap: an SQL statement specifies which table data to retrieve and how to filter it. In an LLM application, a model can generate that statement from the user’s question so database facts can contribute to an answer alongside content from other stores. section 10.0 section 10.1
Find offers for Cardiff
Ask, “Give me some offers for Cardiff, including the accommodation name.” The generated query selects offer details and accommodation names, joins Offer to Accommodation and Destination, filters for Cardiff, and limits the results to five. The joins matter because the requested information spans tables. section 10.3.3
"SELECT Offer.OfferDescription, Offer.DiscountRate, Accommodation.Name \nFROM Offer \nJOIN Accommodation ON Offer.AccommodationId = Accommodation.AccommodationId \nJOIN Destination ON Accommodation.DestinationId = Destination.DestinationId \nWHERE Destination.Name = 'Cardiff' \nLIMIT 5;"
Inspect the complete generated statement to see SELECT, JOIN, WHERE, and LIMIT working together for the Cardiff request. section 10.3.3
To build the generation step, connect a database to LangChain’s create_sql_query_chain and invoke the chain with a question. Give the model the database structure it needs: complex schemas can lead it to invent table or column names, while a CREATE TABLE schema and sample rows help constrain its choices. section 10.3 section 10.3.3
CREATE TABLE "state" (
"state_name" TEXT,
"population" INT DEFAULT NULL,
"area" DOUBLE DEFAULT NULL,
"country_name" VARCHAR(3) NOT NULL DEFAULT '',
"capital" TEXT,
"density" DOUBLE DEFAULT NULL
);
/* example rows
state_name population area country_name
capital density
alabama 3894000 51700.0 usa
montgomery 75.319149
alaska 401800 591000.0 usa
juneau 0.679865
arizona 2718000 114000.0 usa
phoenix 23.842105
*/
-- Answer the following question using the above table schema:
-- {user_question}
Use this schema-and-sample-rows prompt as a concrete example of the context supplied before asking for SQL. section 10.3
Do not assume generated text is immediately executable. One generated answer includes Markdown code fences; passing it directly to the database produces an error. A second chain asks for a clean SQLite statement, with table-qualified column names, a terminating semicolon, and no surrounding commentary. Check the resulting SQL before relying on its results: clean formatting addresses one failure, not incorrect choices about the schema. section 10.3 section 10.3.3
Listing 10.9 Chain to fix the formatting of the generated SQL
clean_sql_prompt_template = """You are an expert in SQLite.
You are asked to fix badly formed SQLite queries,
which might contain unneeded prefixes or suffixes.
Given the following unclean SQL statement,
transform it to a clean,
executable SQL statement for SQLite.
Always prefix column names with the table name.
Only return an executable SQL statement which terminates
with a semicolon. Do not return anything else.
Do not include the language name or symbols like ```.
Unclean SQL: {unclean_sql}"""
clean_sql_prompt = ChatPromptTemplate.from_template(
clean_sql_prompt_template)
clean_sql_chain = clean_sql_prompt | llm
full_sql_gen_chain = sql_query_gen_chain | \
clean_sql_chain | StrOutputParser()
See how the cleanup prompt and chain turn a generated response into a SQLite statement without Markdown fences. section 10.3.3
Choose the search behavior deliberately. An equality filter for the first name Roberto returns only Roberto; LIKE 'Rob%' also catches names such as Robert and Robin, but not unrelated spellings or nicknames such as Bobby. For searches based on meaning rather than these string matches, a separate approach can generate SQL similarity queries against a database that supports suitable vector data. section 10.4.1 section 10.4.5
