Database

Introduction

R2R’s DatabaseProvider offers a unified interface for both relational and vector database operations. R2R only provides database support through Postgres with the pgvector extension.

Postgres was selected to power R2R because it is free, open source, and widely considered be a stable state of the art database. Further, the Postgres community has implemented pgvector for efficient storage and retrieval of vector embeddings alongside traditional relational data.

Architecture Overview

The Database Provider in R2R is built on two primary components:

1. Vector Database Provider: Handles vector-based operations such as similarity search and hybrid search.
2. Relational Database Provider: Manages traditional relational database operations, including user management and document metadata storage.

These providers work in tandem to ensure efficient data management and retrieval.

Configuration

Update the database section in your r2r.toml file:

1
[database]
2
provider = "postgres"
3
user = "your_postgres_user"
4
password = "your_postgres_password"
5
host = "your_postgres_host"
6
port = "your_postgres_port"
7
db_name = "your_database_name"
8
your_project_name = "your_project_name"

Alternatively, you can set these values using environment variables:

$
export R2R_POSTGRES_USER=your_postgres_user
>
export R2R_POSTGRES_PASSWORD=your_postgres_password
>
export R2R_POSTGRES_HOST=your_postgres_host
>
export R2R_POSTGRES_PORT=your_postgres_port
>
export R2R_POSTGRES_DBNAME=your_database_name
>
export R2R_PROJECT_NAME=your_project_name

Environment variables take precedence over the config settings in case of conflicts. The R2R Docker includes configuration options that facilitate integration with a combined Postgres+pgvector database setup.

Vector Database Operations

Initialization

The vector database is automatically initialized with dimensions that correspond to your selected embedding model when the Database Provider is first created.

Upsert Vector Entries

1
from r2r import R2R, VectorEntry
2
3
app = R2R()
4
5
vector_entry = VectorEntry(id="unique_id", vector=[0.1, 0.2, 0.3], metadata={"key": "value"})
6
app.providers.database.vector.upsert(vector_entry)

Search

1
results = app.providers.database.vector.search(query_vector=[0.1, 0.2, 0.3], limit=10)

Hybrid Search

1
results = app.providers.database.vector.hybrid_search(
2
    query_text="search query",
3
    query_vector=[0.1, 0.2, 0.3],
4
    limit=10,
5
    full_text_weight=1.0,
6
    semantic_weight=1.0
7
)

Delete by Metadata

1
deleted_ids = app.providers.database.vector.delete_by_metadata(
2
    metadata_fields=["key1", "key2"],
3
    metadata_values=["value1", "value2"]
4
)

Relational Database Operations

User Management

Create User

1
from r2r import UserCreate
2
3
new_user = UserCreate(email="[email protected]", password="secure_password")
4
created_user = app.providers.database.relational.create_user(new_user)

Get User by Email

1
user = app.providers.database.relational.get_user_by_email("[email protected]")

Update User

1
user.name = "New Name"
2
updated_user = app.providers.database.relational.update_user(user)

Document Management

Upsert Document Overview

1
from r2r import DocumentInfo
2
3
doc_info = DocumentInfo(
4
    document_id="doc_id",
5
    title="Document Title",
6
    user_id="user_id",
7
    version="1.0",
8
    size_in_bytes=1024,
9
    metadata={"key": "value"},
10
    status="processed"
11
)
12
app.providers.database.relational.upsert_documents_overview([doc_info])

Get Documents Overview

1
docs = app.providers.database.relational.get_documents_overview(
2
    filter_document_ids=["doc_id1", "doc_id2"],
3
    filter_user_ids=["user_id1", "user_id2"]
4
)

Advanced Features

Hybrid Search Function

The Database Provider includes a custom Postgres function for hybrid search, combining full-text and vector similarity search.

Token Blacklisting

For enhanced security, the provider supports token blacklisting:

1
app.providers.database.relational.blacklist_token("token_to_blacklist")
2
is_blacklisted = app.providers.database.relational.is_token_blacklisted("token_to_check")

Security Best Practices

1. Environment Variables: Use environment variables for sensitive information like database credentials.
2. Connection Pooling: The provider uses connection pooling for efficient database connections.
3. Prepared Statements: SQL queries use prepared statements to prevent SQL injection.
4. Password Hashing: User passwords are hashed before storage using the configured Crypto Provider.

Performance Considerations

1. Indexing: The vector database automatically creates appropriate indexes for efficient similarity search.
2. Batch Operations: Use batch operations like upsert_entries for better performance when dealing with multiple records.
3. Connection Reuse: The provider reuses database connections to minimize overhead.

Troubleshooting

Common issues and solutions:

1. Connection Errors: Ensure your database credentials and connection details are correct.
2. Dimension Mismatch: Verify that the vector dimension in your configuration matches your embeddings.
3. Performance Issues: Consider optimizing your queries or adding appropriate indexes.

Conclusion

R2R’s Database Provider offers a powerful and flexible solution for managing both vector and relational data. By leveraging Postgres with pgvector, it provides efficient storage and retrieval of embeddings alongside traditional relational data, enabling advanced search capabilities and robust user management.