Creating a Postgres contrainer with an async driver fails

[SpyrosRoum]

Hello,
When creating a new database container, testcontainers automatically tries to connect to it here, but if I specify an async driver (e.g. PostgresContainer("postgres:14.4", driver="asyncpg")) sqlalchemy tries to use that and fails because it's not in an async context.
Is there any way to skip trying to connect to the database or fix it so it can also handle async context?

[tillahoffmann]

Hey, do you have a code example to illustrate the issue?

[fourteekey]

test_main.py

import pytest
import sqlalchemy
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from testcontainers.postgres import PostgresContainer
from sqlalchemy.orm import Session


def test_simple():
    with PostgresContainer("postgres:9.5") as postgres:
        print('Connection URL:', postgres.get_connection_url())
        engine = sqlalchemy.create_engine(postgres.get_connection_url())
        with Session(bind=engine) as session:
            result = session.execute("select version()")
            (version,) = result.fetchone()
            print("Version:", version)


@pytest.mark.asyncio
async def test_async():
    with PostgresContainer("postgres:9.5", driver="asyncpg") as postgres:
        print('Connection URL:', postgres.get_connection_url())

        engine = create_async_engine(postgres.get_connection_url())
        async with AsyncSession(bind=engine, expire_on_commit=True) as session:
            result = await session.execute("select version()")
            (version,) = result.fetchone()
            print("Version:", version)

requirements.txt

testcontainers==3.7.1
asyncpg==0.27.0
psycopg2-binary==2.9.5
SQLModel==0.0.8
pytest-asyncio==0.20.3

For runing use:
pytest -s -k test_simple and pytest -s -k test_async

Traceback

=============================================================================================================== FAILURES ===============================================================================================================
______________________________________________________________________________________________________________ test_async ______________________________________________________________________________________________________________

    @pytest.mark.asyncio
    async def test_async():
>       with PostgresContainer("postgres:9.5", driver="asyncpg") as postgres:

test_main.py:20: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
venv/lib/python3.10/site-packages/testcontainers/core/container.py:71: in __enter__
    return self.start()
venv/lib/python3.10/site-packages/testcontainers/core/generic.py:55: in start
    self._connect()
venv/lib/python3.10/site-packages/testcontainers/core/waiting_utils.py:49: in wrapper
    return wrapped(*args, **kwargs)
venv/lib/python3.10/site-packages/testcontainers/core/generic.py:33: in _connect
    engine.connect()
venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3315: in connect
    return self._connection_cls(self, close_with_result=close_with_result)
venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py:96: in __init__
    else engine.raw_connection()
venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3394: in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3361: in _wrap_pool_connect
    return fn()
venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:320: in connect
    return _ConnectionFairy._checkout(self)
venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:884: in _checkout
    fairy = _ConnectionRecord.checkout(pool)
venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:486: in checkout
    rec = pool._do_get()
venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py:145: in _do_get
    with util.safe_reraise():
venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py:70: in __exit__
    compat.raise_(
venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py:208: in raise_
    raise exception
venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py:143: in _do_get
    return self._create_connection()
venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:266: in _create_connection
    return _ConnectionRecord(self)
venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:381: in __init__
    self.__connect()
venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:677: in __connect
    with util.safe_reraise():
venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py:70: in __exit__
    compat.raise_(
venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py:208: in raise_
    raise exception
venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:673: in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
venv/lib/python3.10/site-packages/sqlalchemy/engine/create.py:578: in connect
    return dialect.connect(*cargs, **cparams)
venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py:598: in connect
    return self.dbapi.connect(*cargs, **cparams)
venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py:780: in connect
    await_only(self.asyncpg.connect(*arg, **kw)),
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

awaitable = <coroutine object connect at 0x7f678d0f2110>

    def await_only(awaitable: Coroutine) -> Any:
        """Awaits an async function in a sync method.
    
        The sync method must be inside a :func:`greenlet_spawn` context.
        :func:`await_only` calls cannot be nested.
    
        :param awaitable: The coroutine to call.
    
        """
        # this is called in the context greenlet while running fn
        current = greenlet.getcurrent()
        if not isinstance(current, _AsyncIoGreenlet):
>           raise exc.MissingGreenlet(
                "greenlet_spawn has not been called; can't call await_only() "
                "here. Was IO attempted in an unexpected place?"
            )
E           sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/14/xd2s)

venv/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py:59: MissingGreenlet
---------------------------------------------------------------------------------------------------------- Captured log call -----------------------------------------------------------------------------------------------------------
INFO     testcontainers.core.container:container.py:53 Pulling image postgres:9.5
INFO     testcontainers.core.container:container.py:64 Container started: 0937c366d38c
INFO     testcontainers.core.waiting_utils:waiting_utils.py:46 Waiting to be ready...
INFO     testcontainers.core.waiting_utils:waiting_utils.py:46 Waiting to be ready...
======================================================================================================= short test summary info ========================================================================================================
FAILED test_main.py::test_async - sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/14/xd2s)

You are have discussion in #175 .
For add async drivers need edit core classes.

[fourteekey]

@tillahoffmann

Why did you create method _connect in class DbContainer, with it, you checked the availability of postgres?

[fourteekey]

MR with fixes #320

[kiriharu]

+1 same problem, need this change

[FeeeeK]

Actually, it's not such a big problem, testcontainers-postgres depends on psycopg2-binary, so you can't get rid of it anyway. And to make get_connection_url return a url with an asynchronous driver, you can just replace the .driver after initializing the container:

from testcontainers.postgres import PostgresContainer

with PostgresContainer("postgres:14") as postgres_container:
    postgres_container.driver = "asyncpg"
    url = postgres_container.get_connection_url()  # -> postgresql+asyncpg://user:user@localhost:56268/demo

PS: In my case the url contains localhost because of #108

My only concern is, does this session close? Seems to me there's no reason to leave it open.

testcontainers-python/core/testcontainers/core/generic.py

Lines 31 to 35 in fce23a4

	@wait_container_is_ready(*ADDITIONAL_TRANSIENT_ERRORS)
	def _connect(self) -> None:
	import sqlalchemy
	engine = sqlalchemy.create_engine(self.get_connection_url())
	engine.connect()

[FeeeeK]

Also, a simple solution for #320 would be to just use psycopg2 internally and use driver only to create a connection url, for example, by adding the driver parameter to get_connection_url, (as is done with the host parameter) and passing psycopg2 to it

testcontainers-python/postgres/testcontainers/postgres/__init__.py

Lines 63 to 69 in fce23a4

	def get_connection_url(self, host=None):
	return super()._create_connection_url(dialect="postgresql+{}".format(self.driver),
	username=self.POSTGRES_USER,
	password=self.POSTGRES_PASSWORD,
	db_name=self.POSTGRES_DB,
	host=host,
	port=self.port_to_expose)

[PookieBuns]

Also, a simple solution for #320 would be to just use psycopg2 internally and use driver only to create a connection url, for example, by adding the driver parameter to get_connection_url, (as is done with the host parameter) and passing psycopg2 to it

testcontainers-python/postgres/testcontainers/postgres/__init__.py

Lines 63 to 69 in fce23a4

	def get_connection_url(self, host=None):
	return super()._create_connection_url(dialect="postgresql+{}".format(self.driver),
	username=self.POSTGRES_USER,
	password=self.POSTGRES_PASSWORD,
	db_name=self.POSTGRES_DB,
	host=host,
	port=self.port_to_expose)

This functionality could also be extended to mysql as well since mysql currently only supports pymysql as the driver.

https://github.com/testcontainers/testcontainers-python/blob/main/mysql/testcontainers/mysql/__init__.py#L68-L73