SQL Record Manager

[Hanson-G2G]

Checked other resources

• I added a very descriptive title to this question.
• I searched the LangChain documentation with the integrated search.
• I used the GitHub search to find a similar question and didn't find it.

Commit to Help

• I commit to help with one of those options 👆

Example Code

import vec_db
from langchain.indexes import SQLRecordManager, index
from langchain.text_splitter import RecursiveCharacterTextSplitter
from langchain_postgres.vectorstores import PGVector
from langchain_aws.vectorstores.inmemorydb import InMemoryVectorStore

embeddings_model = vec_db.choose_embeddings_model(vec_db.VOYAGE_MODEL)

namespace = f"memoryDB/vectordb"
record_manager = SQLRecordManager(namespace, db_url="postgresql+psycopg://admin:[email protected]:5433/vectordb")

record_manager.create_schema()

vector_inputs = vec_db.get_docs()

docs = RecursiveCharacterTextSplitter(chunk_size=500, chunk_overlap=100).split_documents(vector_inputs)

def indexing():
    db = "memoryDB"
    if db == "pgvector":
        vector_store = PGVector(
            embeddings=embeddings_model,
            collection_name="vectordb",
            connection="postgresql+psycopg://admin:[email protected]:5433/vectordb",
            use_jsonb=True,
        )
    elif db == "memoryDB":
    #     InMemoryVectorStore.drop_index(
    #         index_name="vectordb", delete_documents=True, redis_url="redis://localhost:6379"
    #     )
        vector_store = InMemoryVectorStore(
            embedding=embeddings_model,
            index_name="vectordb",
            redis_url="redis://localhost:6379",
        )
        vector_store.write_schema("redis_schema.yaml")

    indexing = index(docs, record_manager, vector_store, cleanup="full", source_id_key="source")
    print(indexing)

if __name__ == "__main__":
    import sys
    import asyncio
    if len(sys.argv) > 1:
        result = asyncio.run(indexing())
    else:
        indexing()

Description

Other than Postgresql, can i use other SQL database?
If there is, can you recommend the best option for database.
Thank you.

System Info

langchain @ file:///home/conda/feedstock_root/build_artifacts/langchain_1723704336486/work
langchain-anthropic==0.1.23
langchain-aws==0.1.17
langchain-chroma==0.1.3
langchain-community @ file:///home/conda/feedstock_root/build_artifacts/langchain-community_1723666199257/work
langchain-core @ file:///home/conda/feedstock_root/build_artifacts/langchain-core_1724271138945/work
langchain-groq==0.1.9
langchain-openai @ file:///home/conda/feedstock_root/build_artifacts/langchain-openai_1723862080855/work
langchain-text-splitters @ file:///home/conda/feedstock_root/build_artifacts/langchain-text-splitters_1719389695345/work
langchain-voyageai==0.1.1

[dosubot[bot]]

Hey @Hanson-G2G! I'm here to help you with your SQL Record Manager question. I can assist with bugs, answer questions, and guide you on becoming a contributor.

Yes, you can use other SQL databases with SQLRecordManager. The implementation is designed to work with SQLAlchemy, which supports a variety of SQL databases. The code already includes support for both SQLite and PostgreSQL.

Given the flexibility of SQLAlchemy, you can adapt SQLRecordManager to work with other SQL databases such as MySQL, MariaDB, or SQL Server with minimal changes.

For example, to use MySQL, you would need to adjust the dialect-specific parts of the code and ensure you have the appropriate SQLAlchemy dialect installed (e.g., mysqlclient or PyMySQL).

Here is a brief example of how you might adapt the get_time method for MySQL:

def get_time(self) -> float:
    """Get the current server time as a timestamp."""
    with self._make_session() as session:
        if self.dialect == "sqlite":
            query = text("SELECT (julianday('now') - 2440587.5) * 86400.0;")
        elif self.dialect == "postgresql":
            query = text("SELECT EXTRACT (EPOCH FROM CURRENT_TIMESTAMP);")
        elif self.dialect == "mysql":
            query = text("SELECT UNIX_TIMESTAMP();")
        else:
            raise NotImplementedError(f"Not implemented for dialect {self.dialect}")

        dt = session.execute(query).scalar()
        if isinstance(dt, decimal.Decimal):
            dt = float(dt)
        if not isinstance(dt, float):
            raise AssertionError(f"Unexpected type for datetime: {type(dt)}")
        return dt

For the best option, it depends on your specific requirements and environment. PostgreSQL is often recommended for its robustness and feature set, but MySQL is also a solid choice, especially if you are already using it in your infrastructure. Additionally, the SQLRecordManager is designed to be adaptable to other SQL implementations with minimal effort, making it a versatile choice for various SQL backends ^[1][2].

To continue talking to Dosu, mention @dosu.

[Hanson-G2G]

[dosubot[bot]]

Yes, you can use AWS MemoryDB and TiDB with SQLRecordManager in LangChain. Here are the steps to integrate each:

AWS MemoryDB

To integrate AWS MemoryDB with SQLRecordManager, follow these steps:

1. Install Required Packages:

   %pip install --upgrade --quiet redis langchain-aws

2. Set Up Embeddings:

   from langchain_aws.embeddings import BedrockEmbeddings
   embeddings = BedrockEmbeddings()

3. MemoryDB Connection:

   redis_url = "rediss://cluster_endpoint:6379/ssl=True ssl_cert_reqs=none"

4. Create MemoryDB Vector Store:

   from langchain_aws.vectorstores.inmemorydb import InMemoryVectorStore
   vds = InMemoryVectorStore.from_texts(embeddings, redis_url=redis_url)

5. Querying:

   results = vds.similarity_search("foo")
   print(results[0].page_content)

6. Using as Retriever:

   retriever = vds.as_retriever(search_type="similarity", search_kwargs={"k": 4})
   docs = retriever.invoke("foo")
   print(docs)

7. Delete Index:

   InMemoryVectorStore.drop_index(index_name="users", delete_documents=True, redis_url="redis://localhost:6379")

TiDB

To use TiDB with SQLRecordManager, follow these steps:

1. Install Required Packages:

   %pip install --upgrade --quiet langchain langchain_openai langchain-community sqlalchemy pymysql

2. Configure the Connection to TiDB:

   import getpass
   tidb_connection_string_template = "mysql+pymysql://<USER>:<PASSWORD>@<HOST>:4000/<DB>?ssl_ca=/etc/ssl/cert.pem&ssl_verify_cert=true&ssl_verify_identity=true"
   tidb_password = getpass.getpass("Input your TiDB password:")
   tidb_connection_string = tidb_connection_string_template.replace("<PASSWORD>", tidb_password)

3. Create and Populate a Table in TiDB:

   from sqlalchemy import Column, Integer, MetaData, String, Table, create_engine
   engine = create_engine(tidb_connection_string)
   metadata = MetaData()
   table_name = "test_tidb_loader"
   test_table = Table(table_name, metadata, Column("id", Integer, primary_key=True), Column("name", String(255)), Column("description", String(255)))
   metadata.create_all(engine)
   with engine.connect() as connection:
       transaction = connection.begin()
       try:
           connection.execute(test_table.insert(), [{"name": "Item 1", "description": "Description of Item 1"}, {"name": "Item 2", "description": "Description of Item 2"}, {"name": "Item 3", "description": "Description of Item 3"}])
           transaction.commit()
       except:
           transaction.rollback()
           raise

4. Load Data from TiDB using TiDBLoader:

   from langchain_community.document_loaders import TiDBLoader
   loader = TiDBLoader(connection_string=tidb_connection_string, query=f"SELECT * FROM {table_name};", page_content_columns=["name", "description"], metadata_columns=["id"])
   documents = loader.load()
   for doc in documents:
       print("-" * 30)
       print(f"content: {doc.page_content}\nmetadata: {doc.metadata}")

5. Clean Up:

   test_table.drop(bind=engine)

These steps will help you integrate AWS MemoryDB and TiDB with SQLRecordManager in LangChain ^[1][2][3].

To continue talking to Dosu, mention @dosu.

[Hanson-G2G]

how to configure AWS memorydb with SQL Record Manager?

[dosubot[bot]]

To configure AWS MemoryDB with SQLRecordManager, follow these steps:

1. Install the Redis Python client:

   %pip install --upgrade --quiet redis langchain-aws

2. Set up the MemoryDB connection:

   from langchain_aws.embeddings import BedrockEmbeddings
   
   embeddings = BedrockEmbeddings()

3. Create the MemoryDB vector store:

   from langchain_aws.vectorstores.inmemorydb import InMemoryVectorStore
   
   vds = InMemoryVectorStore.from_texts(
       embeddings,
       redis_url="rediss://cluster_endpoint:6379/ssl=True ssl_cert_reqs=none",
   )

4. Query the vector store:

   results = vds.similarity_search("foo")
   print(results[0].page_content)

5. Add new documents:

   new_document = ["baz"]
   new_metadata = [{"user": "sam", "age": 50, "job": "janitor", "credit_score": "high"}]
   vds.add_texts(new_document, new_metadata)

6. Use MemoryDB as a retriever:

   retriever = vds.as_retriever(search_type="similarity", search_kwargs={"k": 4})
   docs = retriever.invoke("foo")
   print(docs)

7. Delete the index:

   InMemoryVectorStore.drop_index(
       index_name="users", delete_documents=True, redis_url="redis://localhost:6379"
   )

This setup will allow you to configure AWS MemoryDB with SQLRecordManager for vector search and retrieval tasks ^[1].

To continue talking to Dosu, mention @dosu.

[robocanic]

I found that there are two "RecordManager", one is langchain_core.indexing.base.RecordManager, another one is langchain_community.indexs.base.RecordManager. The definition of two record manager is almost the same, But the index api uses RecordManager which is specifically langchain_core.indexing.base.RecordManager. Btw the mongo implemention is in pkg langchain-community but not in langchain or langchain_core. This has confused me for a while, any idea about the differences about two RecordManager?