Using engines and sessions¶
SQLAlchemy, by its nature, operates in a blocking manner. That is, running a query against the database will block the event loop. This includes implicit queries triggered by accessing lazily loaded relationships and deferred columns.
While simple queries usually complete in a timely manner, it is often difficult to predict the
performance of interactions with such blocking APIs. The database server might be slow or
completely unreachable, in which case your whole application hangs during the query. Even when it
does not, your queries might gradually get slower due to increasing amounts of data. For these
reasons, it is recommended that you handle your SQLAlchemy interactions in worker threads. The
Context class provides a few conveniences for this purpose.
In asphalt-sqlalchemy, database connections are made on demand when you request a connection,
either via one of the methods in the
Context class or by directly
accessing the appropriate context attribute of the connection resource. The resulting connection
begins a transaction that is automatically committed when the context is was created through is
torn down, unless the context was ended by an unhandled exception. The connection is always closed
in either case.
Working with core queries¶
If you’re not familiar with SQLAlchemy’s core functionality, you should take a look at the SQL Expression Language Tutorial first.
Here’s how the above example would work using core queries:
async def handler(ctx): # Database queries can block the event loop, so run this in a thread pool async with ctx.threadpool(): parent_id = ctx.sql.scalar(select([people.c.id]).where(name='Senior')) ctx.sql.execute(people.insert().values(name='Junior')) # Commit happens automatically when the context is torn down
Working with the Object Relational Mapper (ORM)¶
If you’re not familiar with SQLAlchemy’s ORM, you should look through the Object Relational Tutorial first.
The previous example would look like this, rewritten for the ORM:
async def handler(ctx): async with ctx.threadpool(): parent = ctx.sql.query(Person).filter_by(name='Senior').one() parent.children.append(Person(name='Junior'))
Loading data at application startup¶
It is unadvisable to use connection or session resources from a long lived context. This would unnecessarily tie up connection resources, and if the connection is used repeatedly, it may get stale data due to transaction isolation.
A better way is to create a throwaway child context when you need to load initial data for the application:
class ApplicationComponent(ContainerComponent): async def start(ctx): # ctx here is the root context async with Context(ctx) as subctx: self.employees = subctx.sql.query(Employee).all()
The connection and session will be automatically closed once the context manager block is exited.