How to Turn Your SQL Database Into an Intelligent Motor with AI
Among the many advancements brought by the new paradigms in AI development, many of what was once thought to be basic in the programming area need to be revisited. One of such things is the near deprecation of the usage of pure SQL for the simple consultancy of data, that is, for using queries and such. That’s because default queries require exact matches and filters, not being able to understand close relations or semantic similarity.
Using your data to power an AI system, on the other hand, will be immensely flexible, as it opens space for semantic searches — that is, the queries now understand context and similarities. It works as closely as possible to asking someone who knows your whole database and having that person deliver you the exact match you’re looking for, even though you’re not sure about its data. And it uses the architecture of embeddings, which is a technique to transform your data into a numeric vector. These vectors are used to group ideas, creating relations and understanding contexts.
So, is it worth it to turn your database into an intelligent AI motor? What are the benefits for your company, and how can this be used? Find out here!
The Problem with Traditional SQL Searches
We must delve a little further into the limitations of traditional SQL searches to understand why creating such a system would be good for your projects. It revolves around how rigid and solid the SQL structure is: as a declarative language, what you want to find must be exactly declared.
For instance, to find a row based on a string, you either use equals or LIKE structures. Even though LIKE can allow for a little more flexibility, you must still know for sure at least a small fragment of the string. And still, as SQL doesn’t understand context, the results won’t be very accurate, which can render the query very unproductive in a context in which it has to find specific information when there are many matches.
Think, for instance, that you’re searching for “Cancelling” in a large table that contains the reasons why someone cancelled a subscription. You’re searching for a specific feedback that you read once, and now you can’t find it again. After a few minutes, you find out — it was written with “finish subscription”, not “cancel”, and thus SQL could not find the exact match between the strings.
Now, by using your database as feeding information for an AI model, it will be able to understand contexts, search better throughout the database, and find you the best match possible. Minutes, even hours, will be saved, as it will understand the closest answer possible to your question, even when the match is not exact.
What are Embeddings, and How do They Change the Game
Embedding is a technique that transforms a string of text into an array of numbers, that is, into a vector. These vectors are a representation of the “meaning” of a word within a mathematical space. Here are some examples:
- The word “love” becomes a box full of numbers:
→ love → [0.4, -0.1, 0.6, …, 0.7]
- The word “cuddle” results in a very similar box:
→ cuddle → [-0.4, 0.5, -0.4, …, -0.5]
- The word “stone”, on the other hand, becomes a very different vector:
→ stone → [-1.01, 0.3, -0.9, …, -1.4]
These embeddings are created by an AI model to understand contexts and guess when ideas are connected or similar. It is made with the calculation of the mathematical distance between different vectors: it creates a multidimensional map that will allow the model to understand if the words mean similar concepts. That is, by the way, the reason why AI is so GPU-demanding: these cards are specialized in completing complex calculations very quickly. Because of this, models run better on GPUs than on CPUs.
That’s especially useful in some different use cases, such as:
- A chatbot for a help desk: Think that your customer searches “how to solve an error in login?”. The AI will be able to read documents with the terms “authentication problems” and “having trouble accessing the system?”, and understand they’re similar concepts. With this, the AI will be able to successfully answer your customer and solve their problem.
- Intelligent FAQ: AI will be able to use past FAQs as a training tool, being able to solve problems way faster, with more assertiveness, and in a much more user-friendly way. This has proven to improve all-around user satisfaction and make the usage of your application easier and smoother.
Dialoguing with a database: You can transform your database into an intelligent “being” and have conversations and dialogs with it. That’s a powerful tool to extract precious insights and pieces of information that might escape you, but be noticed by the model. Overall, it can help you reach a deeper level of understanding of your data, allowing you to become much more aware of what’s going on in your business. To do so, you must combine the semantic search, which we are discussing here, with the Retrieval Augmented Generation (RAG) architecture, creating an AI chatbot.
How to Generate Embeddings with Your SQL Data
Now, we must understand a little more about how to generate such embeddings based on the data in the database. It is a quite simple pipeline, with a process of extraction, transformation, and loading. First of all, you must understand exactly which data you want to process and have your AI system know: hence, you need to first of all determine which tables and rows you’re going to use. Then, you must perform queries to transform all of it into embeddings, and then save these embeddings somewhere for later usage.
For instance, let’s suppose we want to create an FAQ. For this, we must then select the questions and answers from the database. Let’s suppose a table named “question”, and then we would be able to run this query:
SELECT id, statement, answer FROM questions;
Then, we send this information to have a model transform into embeddings. For this example, we are going to use OpenAI’s API, with the model text-embedding-3-small, implementing a Python code to run the request
import openai
openai.api_key = ‘your-api-key’
response = openai.Embedding.create(
model="text-embedding-3-small",
input=”Your content”
)
embedding = response['data'][0]['embedding']
Then, for storage, the easiest way is to add an “embedding” column to the table, which can then store the vector for each entry, keeping the data in the database itself. It is not the best way to do it, as we will discuss further, but it offers a provisional functioning that works reasonably well:
ALTER TABLE artigos ADD COLUMN embedding JSON;
Then, after receiving the embedding, you can run a SQL task which will be more or less like that:
“UPDATE questions SET embedding = %s WHERE id = %s”
Storing and Consulting Embeddings (Semantic Search)
Now, as cited just above, the simplest way of storing embeddings is by creating a row for them in your database. Still, that’s not very performant, as the code would have to iterate over every single embedding to find the best match. In a production environment, you’d use a vectorial database; here are some examples of good alternatives:
Name | Type | Notes |
pgvector | PostgreSQL extension | Vectors stored as arrays |
Qdrant | Proprietary API or self-host | Used a lot alongside Rust, great performance |
Pinecone | SaaS | Focused on scalability |
Weaviate | Open source with GraphQL and REST | Very flexible |
FAISS | Local library (Python) | Great for prototypes and offline search |
Now, we are going to perform searches in the database by using cosine similarity. It is a technique that determines how close vectors are to each other, returning a number from -1 to 1, as follows:
- 1.0: identic values
- 0.0: not related
- -1.0: opposite values
We will use a Python library to run the following formula:
similarity = cosine_similarity([consult_vector], [stored_vector])
This formula will be applied to your database, comparing the embedding of what you’re looking for with the embedding in your data, and finding the best match. Think of it as a “LIKE” statement, but AI-powered: you’ll find a row from a table, but with much more accuracy. And now, joining everything together, we can have the functioning code to perform semantic search as follows:
import openai
import json
import numpy as np
from sklearn.metrics.pairwise import cosine_similarity
import mysql.connector
# Connect to your SQL database
conn = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="mydatabase"
)
cursor = conn.cursor()
# Fetch stored text and embeddings
cursor.execute("SELECT id, description, embedding FROM articles WHERE embedding IS NOT NULL")
rows = cursor.fetchall()
# Input query to search
query_text = "How can I reset my password?"
# Generate embedding for the query
openai.api_key = "your-api-key-here"
response = openai.Embedding.create(
model="text-embedding-3-small",
input=query_text
)
query_embedding = np.array(response['data'][0]['embedding']).reshape(1, -1)
# Compare the query vector to all stored embeddings
results = []
for article_id, description, embedding_json in rows:
stored_embedding = np.array(json.loads(embedding_json)).reshape(1, -1)
similarity = cosine_similarity(query_embedding, stored_embedding)[0][0]
results.append((article_id, description, similarity))
# Sort results by similarity (highest first)
results.sort(key=lambda x: x[2], reverse=True)
# Display top 3 most similar results
for article_id, text, similarity in results[:3]:
print(f"ID: {article_id}, Similarity: {similarity:.3f}")
print(f"Text: {text}")
print("---")
Challenges and Good Practices
Now, even though this solution has several different and undeniable upsides, we must address its downsides too. Even though it doesn’t have that many, these are heavy ones, which must be carefully considered and addressed in the planning process. Should they not be properly planned, your application is at risk of not working as it should, or face legal consequences while dealing with sensitive users’ data. So, the complications are as follows:
- API costs: All of this will most probably be very costly for your application, as it uses APIs a lot. For instance, generating embeddings for every single row of a gigantic table might be pretty expensive, and you’ll also need to generate an embedding of each query you run. This can stack up pretty quickly, leading to very big billings from OpenAI or Claude. So, if you’ll intensively use these methods, perhaps you should consider hosting your LLM locally or choose cheaper AI APIs.
- Data normalization before embedding: Your data should be very well normalized before becoming an embedding, or else the results aren’t going to be as precise as they need, and your application would be misleading. Hence, how your application will process the data for the generation of an embedding is one of the most important steps, as everything depends on it.
- Vectors’ actualization after updates: That’s a pretty simple step – if your database is going to be updated, then your vectors also need to be updated. Otherwise, they’d lead to outdated information, which would also render your application useless.
- Privacy concerns: Lastly, if your database uses sensitive data from external users and you use an external API, then you must ensure that this sensitive data doesn’t leak. If it does, then you might face legal consequences, as your user probably did not agree to have their data shared with OpenAI, Claude, and other AI companies.
Conclusion
With all of this, we want to help you better understand that creating semantic searches and using your AI to level up your database is a powerful and useful tool for your projects. Also, this technology can be combined into an RAG architecture, generating then an AI chatbot based on your data. It can be used in many different areas, even amongst non-technical users, such as creating help chats, helping with the creation of data reports, and many more. With this, your company will benefit from the latest advancements in the tech industry, accelerating processes and becoming smarter and up-to-date with what’s new.
And, in case you want your data to be revolutionized with AI, with systems made with top-tier technologies and running as efficiently as possible, make sure to contact us at Chudovo. We are a consultancy focused on delivering top-notch software in many different areas, having delivered many advanced AI/ML projects. Schedule a meeting with us, and let’s see the best way to automate your company’s processes and make it more efficient and profitable than ever!