ChromaDB for the SQL Mind

Hello, Chroma DB is a vector database which is useful for working with GenAI applications. In this article I will explore how can we run queries on Chroma DB by looking at similar relations in MySQL.

Schema

Unlike SQL you cannot define your own schema. In Chroma You get fixed Columns each with its own purpose:

import chromadb

#setiing up the client
client = chromadb.Client() 
collection = client.create_collection(name="name")

collection.add(
    documents = ["str1","str2","str3",...]
    ids = [1,2,3,....]
    metadatas=[{"chapter": "3", "verse": "16"},{"chapter":"3", "verse":"5"}, ..]           
    embeddings = [[1,2,3], [3,4,5], [5,6,7]]
)

Ids: They are unique ids. Note that you need to supply them yourself unlike sql there is no auto increment
Documents: It is used to insert the text data that is used to generate the embeddings. You can supply the text and it will automatically create the embeddings. or you can just supply embeddings directly and store text else where.
Embeddings: They are in my opinion the most important part of the database as they are used to perform similarity search.
Metadata: this is used to associate any additional data you might want to add to your database for any extra context.

Now that the basics of a collection are clear lets move on to CRUD operations and the we will see how we can query the database.

CRUD Operations

Note: Collections are like Tables in Chroma

To create a collection we can use create_collection() and perform our operations as needed but if the collection is already made and we need to refrence it again we have to use get_collection() or else we will get a error.

Create Table tablename 

#Create a collection
collection = client.create_collection(name="name")

#If a collection is already made and you need to use it again the use
collection = client.get_collection(name="name")

Insert into tablename
Values(... , ..., ...)

collection.add(
    ids = [1]
    documents = ["some text"]
    metadatas = [{"key":"value"}]
    embeddings = [[1,2,3]]
)

To Update the inserted data or to Delete the data we can use following commands

collection.update(
    ids = [2]
    documents = ["some text"]
    metadatas = [{"key":"value"}]
    embeddings = [[1,2,3]]            
)

# If the id does not exist update will do nothing. to add data if id does not exist use
collection.upsert(
    ids = [2]
    documents = ["some text"]
    metadatas = [{"key":"value"}]
    embeddings = [[1,2,3]]            
)

# To delete data use delete and refrence the document or id or the feild
collection.delete(
    documents = ["some text"]         
)

# Or you can delete from a bunch of ids using where that will apply filter on metadata
collection.delete(
    ids=["id1", "id2", "id3",...],
    where={"chapter": "20"}
)

Queries

Now we will look at how certain queries look like

Select * from tablename

Select * from tablename limit value

Select Documents, Metadata from tablename

collection.get()

collection.get(limit = val)

collection.get(include = ["documents","metadata"])

While get() is there for fetching a large set of tables for more advanced queries you need to use query method

Select A,B from table
limit val

collection.query(
    n_results = val #limit
    includes = [A,B] 
)

Now we get 3 possible ways to filter the data: Similarity Search (what vector databases are mainly used for), Metadata filters and Document filters

Similarity Search

We can search based on text or embeddings and get the most similar outputs

collection.query(query_texts=["string"])

collection.query(query_embeddings=[[1,2,3]])

In ChromaDB, where and where_document parameters are used to filter results during a query. These filters allow you to refine your similarity search based on metadata or specific document content.

Filter by Metadata

The where parameter lets you filter documents based on their associated metadata. Metadata is usually a dictionary of key-value pairs you provide during document insertion.

Filter documents by metadata like category, author, or date.

# Insert documents with metadata
collection.add(
    documents=["Document about AI", "Another document on AI", "General science content"],
    metadatas=[
        {"category": "AI", "author": "John"},
        {"category": "AI", "author": "Doe"},
        {"category": "Science", "author": "Alice"},
    ],
    ids=["doc1", "doc2", "doc3"]
)

# Query with a metadata filter
results = collection.query(
    query_texts=["Artificial Intelligence"],
    n_results=5,
    where={"category": "AI"}  # Only retrieve documents with category = "AI"
)

# Output
print("Filtered documents:", results['documents'])

# You can filter using multiple conditions:  
where={"category": "AI", "author": "John"}

# Supports operators like `$gt`, `$lt`, `$in`, etc. For example:
where={"date": {"$gt": "2024-01-01"}}

Filter by Document Content

The where_document parameter allows filtering directly based on the content of documents.

Retrieve only documents containing specific keywords.

# Insert documents
collection.add(
    documents=["AI is transforming the world", "AI and machine learning", "Physics is fascinating"],
    ids=["doc1", "doc2", "doc3"]
)

# Query with a document content filter
results = collection.query(
    query_texts=["What is AI?"],
    n_results=5,
    where_document={"$contains": "AI"}  # Only retrieve documents containing "AI"
)

# Output
print("Filtered documents:", results['documents'])

Key Notes:

• Use operators like $contains, $startsWith, or $endsWith.
  • $contains: Match documents containing a substring.
  • $startsWith: Match documents starting with a substring.
  • $endsWith: Match documents ending with a substring.

• For example:
  

  where_document={"$startsWith": "AI"}
  

Common Use Cases:

We can combine all three filters like this:

1. Search Within a Specific Category:
   

   collection.query(query_texts=["Machine learning"], n_results=5, where={"category": "ML"})
   

2. Search Documents Containing a Specific Term:
   

   collection.query(query_texts=["Physics"], n_results=5, where_document={"$contains": "gravity"})
   

3. Combine Metadata and Document Content Filters:
   

   collection.query(
       query_texts=["AI"], 
       n_results=5, 
       where={"author": "John"}, 
       where_document={"$startsWith": "AI"}
   )
   

These filters enhance the precision of your similarity searches, making ChromaDB a powerful tool for targeted document retrieval.

Conclusion

I wrote this article because I felt that the document leaves much to desire when trying to make my own program, I hope this helps!

Thanks for reading if you liked the article pls like and share. Also if you are new to software architecture and would like to know more I am starting a group based cohort where I will personally work with you and a small group to teach you everything about Software Architecture and Design principals. You can fill the form below if you are interested . https://forms.gle/SUAxrzRyvbnV8uCGA