[Bug]: SupabaseVectorStore connection (FastAPI backend on Render) failing with "sqlalchemy.exc.NoSuchModuleError: Can't load plugin"

jbbae commented 3 months ago

Bug Description

I have a Python backend for my LlamaIndex application (using FastAPI + Render) that uses "SupabaseVectorStore" to set up the connection with my Supabase DB. It works 100% fine locally on localhost, but when I host it in Render, I suddenly get this error:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "db.[USER_HERE].supabase.co", port [PORT_NUMBER] failed: Network is unreachable

Is the server running on that host and accepting TCP/IP connections?

Version

0.10.26

Steps to Reproduce

The code where the Supabase DB connection is setup is:

import logging
import os

from llama_index.core.indices import VectorStoreIndex
from llama_index.vector_stores.pinecone import PineconeVectorStore
from llama_index.vector_stores.supabase import SupabaseVectorStore
from llama_index.core import StorageContext

logger = logging.getLogger("uvicorn")

def get_index():
    logger.info("Connecting to index from Supabase...")
    vector_store = SupabaseVectorStore(
        postgres_connection_string=f"postgresql://{os.environ['SUPABASE_DB_URL']}",
        collection_name="daily_notes",
    )
    storage_context = StorageContext.from_defaults(vector_store=vector_store)
    index = VectorStoreIndex(nodes=[], storage_context=storage_context)

    logger.info("Finished connecting to index from Supabase.")
    return index

where:

SUPABASE_DB_URL = postgresql://postgres:[YOUR-PASSWORD]@db.[USER].supabase.co:[PORT]/postgres

UPDATE: Since reading this post from Supabase, I also tried the "pooling string", where (in addition to updating the code to start with "postgres://"):

SUPABASE_DB_URL = postgres://postgres.[USER]:[YOUR-PASSWORD]@aws-0-us-west-1.pooler.supabase.com:[PORT]/postgres

But this now yields:

sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgres

Anyone have any idea how to fix this? I've been grappling with this for HOURS and am completely lost... 😭 Alternatively - is there a better recommended place to host my RAG chatbot backend (what this FastAPI code is supposed to do)?

Relevant Logs/Tracbacks

ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/uvicorn/protocols/http/httptools_impl.py", line 426, in run_asgi
    result = await app(  # type: ignore[func-returns-value]
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/uvicorn/middleware/proxy_headers.py", line 84, in __call__
    return await self.app(scope, receive, send)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/fastapi/applications.py", line 1054, in __call__
    await super().__call__(scope, receive, send)
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/applications.py", line 123, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/middleware/errors.py", line 186, in __call__
    raise exc
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/middleware/errors.py", line 164, in __call__
    await self.app(scope, receive, _send)
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/middleware/cors.py", line 91, in __call__
    await self.simple_response(scope, receive, send, request_headers=headers)
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/middleware/cors.py", line 146, in simple_response
    await self.app(scope, receive, send)
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/middleware/exceptions.py", line 62, in __call__
    await wrap_app_handling_exceptions(self.app, conn)(scope, receive, send)
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/_exception_handler.py", line 64, in wrapped_app
    raise exc
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/_exception_handler.py", line 53, in wrapped_app
    await app(scope, receive, sender)
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/routing.py", line 758, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/routing.py", line 778, in app
    await route.handle(scope, receive, send)
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/routing.py", line 299, in handle
    await self.app(scope, receive, send)
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/routing.py", line 79, in app
    await wrap_app_handling_exceptions(app, request)(scope, receive, send)
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/_exception_handler.py", line 64, in wrapped_app
    raise exc
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/_exception_handler.py", line 53, in wrapped_app
    await app(scope, receive, sender)
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/routing.py", line 74, in app
    response = await func(request)
               ^^^^^^^^^^^^^^^^^^^
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/fastapi/routing.py", line 285, in app
    raise e
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/fastapi/routing.py", line 275, in app
    solved_result = await solve_dependencies(
                    ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/fastapi/dependencies/utils.py", line 600, in solve_dependencies
    solved = await run_in_threadpool(call, **sub_values)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/starlette/concurrency.py", line 42, in run_in_threadpool
    return await anyio.to_thread.run_sync(func, *args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/anyio/to_thread.py", line 56, in run_sync
    return await get_async_backend().run_sync_in_worker_thread(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/anyio/_backends/_asyncio.py", line 2144, in run_sync_in_worker_thread
    return await future
           ^^^^^^^^^^^^
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/anyio/_backends/_asyncio.py", line 851, in run
    result = context.run(func, *args)
             ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/render/project/src/app/engine/__init__.py", line 9, in get_chat_engine
    return get_index().as_chat_engine(
           ^^^^^^^^^^^
  File "/opt/render/project/src/app/engine/index.py", line 21, in get_index
    vector_store = SupabaseVectorStore(
                   ^^^^^^^^^^^^^^^^^^^^
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/llama_index/vector_stores/supabase/base.py", line 57, in __init__
    self._client = vecs.create_client(postgres_connection_string)
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/vecs/__init__.py", line 28, in create_client
    return Client(connection_string)
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/vecs/client.py", line 60, in __init__
    self.engine = create_engine(connection_string)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<string>", line 2, in create_engine
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/sqlalchemy/util/deprecations.py", line 281, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/sqlalchemy/engine/create.py", line 550, in create_engine
    entrypoint = u._get_entrypoint()
                 ^^^^^^^^^^^^^^^^^^^
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/sqlalchemy/engine/url.py", line 758, in _get_entrypoint
    cls = registry.load(name)
          ^^^^^^^^^^^^^^^^^^^
  File "/opt/render/project/src/.venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 375, in load
    raise exc.NoSuchModuleError(
sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgres
INFO:     127.0.0.1:36492 - "HEAD / HTTP/1.1" 404 Not Found

dosubot[bot] commented 3 months ago

Hey @jbbae,

I've looked into the issues you're facing with your FastAPI backend on Render when connecting to Supabase using SupabaseVectorStore. Let's tackle them one by one.

Issue 1: Network Unreachable

The error message you're encountering suggests that the Render environment is unable to establish a connection to your Supabase database. This could be due to network policies on Render's side or incorrect database connection details.

Solution: Double-check your Supabase database connection details (host, port, username, password) to ensure they are correct. Additionally, consult Render's documentation or support to see if there are any restrictions on outbound database connections and how you might work around them.

Issue 2: `sqlalchemy.exc.NoSuchModuleError`

This error is typically caused by a typo in the protocol part of the SQLAlchemy database URL or a missing SQLAlchemy dialect for the database you're trying to connect to.

Solution: The correct protocol for PostgreSQL connections with SQLAlchemy is postgresql://, not postgres://. However, your update indicates you've already corrected this. The error message Can't load plugin: sqlalchemy.dialects:postgres suggests that SQLAlchemy is looking for a dialect named postgres, which doesn't exist. The correct dialect name is postgresql.

Make sure your SUPABASE_DB_URL environment variable starts with postgresql://. If the issue persists, ensure you have the psycopg2 library installed in your environment, as it's the standard PostgreSQL adapter for Python and required for SQLAlchemy to connect to PostgreSQL databases.

Alternative Hosting Solutions

If you continue to face issues with Render, you might consider other hosting solutions that are known to work well with FastAPI and PostgreSQL, such as Heroku, DigitalOcean App Platform, or AWS Elastic Beanstalk. Each of these platforms has its own set of features and pricing, so you'll want to choose one that best fits your project's needs and budget.

I hope this helps you move forward with your project. If you have any more questions or run into further issues, feel free to ask!

Best, Dosu

#### About Dosu

This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant. If you want to continue the conversation, start your reply with @dosu-bot.

logan-markewich commented 3 months ago

@jbbae

The URI should start with postgresql:// instead of postgres://. 
SQLAlchemy used to accept both, but has removed support for the postgres name.

https://stackoverflow.com/questions/62688256/sqlalchemy-exc-nosuchmoduleerror-cant-load-plugin-sqlalchemy-dialectspostgre

run-llama / llama_index