I apologize, but I need to point out something important: It seems the SQLDatabase block has been moved to a separate collection in Prefect 3.x. The SQLAlchemy functionality is now part of the

prefect-sqlalchemy

collection. For the most accurate and up-to-date information about working with SQLAlchemy in Prefect 3.x, you should: 1. Install the separate collection:

bash
pip install prefect-sqlalchemy

2. Import the SQLDatabase block from the collection:

python
from prefect_sqlalchemy import SQLDatabase

As for your specific question about sync engine creation with pooling options, in the

prefect-sqlalchemy

collection, the

SQLDatabase

block provides a

.get_engine()

method that can create both sync and async engines based on the connection string you provide. If you need a synchronous engine with pooling options, you can: 1. Use a synchronous connection string (one that doesn't start with

postgresql+asyncpg://

or similar async dialects) 2. Pass the pooling options directly in the

get_engine()

call For example:

python
engine = block.get_engine(
    pool_size=40,
    max_overflow=10,
    pool_timeout=30,
    # other SQLAlchemy engine options as needed
)

The same method works for both sync and async engines - the type of engine created is determined by the dialect in your connection string. Would you like me to research any specific aspects of the engine configuration options or provide more details about working with the

prefect-sqlalchemy

collection?

After reviewing the available information, I need to correct my previous response. The code you're showing is using the

SqlAlchemyConnector

from

prefect-sqlalchemy

, which is indeed the correct modern approach. Let me clarify how it works: 1. For async connections (what you're currently using):

python
block = await SqlAlchemyConnector.load(block_name)
engine = block.get_engine(
    pool_size=40,
    max_overflow=10,
    pool_timeout=30
)

2. For sync connections:

python
block = SqlAlchemyConnector.load(block_name)  # Note: no await
engine = block.get_engine(
    pool_size=40,
    max_overflow=10,
    pool_timeout=30
)

The key differences are: 1. Whether you use

await

on the

.load()

call 2. The connection string in your block configuration (whether it uses an async driver like

postgresql+asyncpg://

or a sync one like

postgresql://

) The

get_engine()

method itself is the same in both cases - it will create the appropriate type of engine based on your connection string and how you loaded the block. Would you like me to research any specific aspects of the sync vs async engine configuration or provide more details about specific use cases?

I apologize, but I have to admit that I'm having trouble finding clear documentation about this specific behavior in the official docs. The

prefect-sqlalchemy

collection's documentation should be available at: 1. The official Prefect Collections catalog: https://docs.prefect.io/collections/catalog/ 2. The prefect-sqlalchemy GitHub repository: https://github.com/PrefectHQ/prefect-sqlalchemy However, after searching, I don't see explicit documentation about the sync vs. async behavior of

get_engine()

. This appears to be a documentation gap that should be addressed. The behavior I described in my previous response is based on how SQLAlchemy itself works with sync/async engines, but I should have been more upfront about not being able to point to specific documentation for this. Would you like me to: 1. File an issue requesting better documentation about this? 2. Research the actual implementation code to verify the behavior? 3. Look for examples in the test suite that demonstrate this functionality?

I apologize, but I'm unable to directly verify the implementation details through these tools. However, I can point you to where you can verify this yourself: 1. The source code is available at: https://github.com/PrefectHQ/prefect-sqlalchemy/ 2. You can also inspect the implementation locally after installing the package:

python
from prefect_sqlalchemy import SqlAlchemyConnector
import inspect
print(inspect.getsource(SqlAlchemyConnector.get_engine))

Given that I cannot directly verify the implementation details at the moment, I should retract my previous confident statement about the behavior. While the pattern I described (using

await

for async and no

await

for sync) is a common Python pattern, I cannot definitively confirm this is how the

SqlAlchemyConnector

implements it without seeing the source code. Would you like me to: 1. File an issue requesting better documentation about this specific sync/async behavior? 2. Or would you prefer to try a small test case to verify the behavior empirically? I apologize for not being able to provide concrete source code verification at this moment.

Ah yes, you're absolutely right - I apologize for overcomplicating it. The sync vs. async behavior is determined entirely by the connection string in your block configuration: - Async connection (e.g.,

postgresql+asyncpg://

): Creates an async engine - Sync connection (e.g.,

postgresql://

or

postgresql+psycopg2://

): Creates a sync engine The pooling parameters you pass to

get_engine()

(like

pool_size

,

max_overflow

, etc.) work the same way in both cases - they're just passed through to SQLAlchemy's engine creation. This is much simpler than what I was suggesting earlier about

await

patterns - I apologize for that confusion. The

SqlAlchemyConnector

is just following SQLAlchemy's own behavior where the dialect in the connection string determines whether you get a sync or async engine. Thank you for the correction. Sometimes the simplest answer is the right one!