Chapter 4 explored making non-blocking web requests with the aiohttp library, and it also addressed using several different asyncio API methods for running these requests concurrently. With the combination of the asyncio APIs and the aiohttp library, we can run multiple long-running web requests concurrently, leading to an improvement in our application’s runtime. The concepts we learned in chapter 4 do not apply only to web requests; they also apply to running SQL queries and can improve the performance of database-intensive applications.
Much like web requests, we’ll need to use an asyncio-friendly library since typical SQL libraries block the main thread, and therefore the event loop, until a result is retrieved. In this chapter, we’ll learn more about asynchronous database access with the asyncpg library. We’ll first create a simple schema to keep track of products for an e-commerce storefront that we’ll then use to run queries against asynchronously. We’ll then look at how to manage transactions and rollbacks within our database, as well as setting up connection pooling.