5 Non-blocking database drivers

Last chapter we learned how to make non-blocking web requests with the aiohttp library. We also learned several different asyncio API methods for running these requests concurrently. With the combination of the asyncio APIs and the aiohttp library we are able to run multiple long-running web requests concurrently leading to an improvement in our application’s run time. The concepts we learned in the last chapter do not just apply to web requests, they also apply to running SQL queries as well and can improve the performance of database-intensive applications.

Much like web requests, we’ll need to use an asyncio friendly library since our typical SQL libraries block the main thread, and therefore the event loop, until a result comes back. In this chapter we’ll learn more about an 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 take a look at how to manage transactions and rollbacks within our database as well as set up connection pooling.

5.1 Introducing asyncpg

5.2 Connecting to a Postgres Database

5.3 Defining a database schema

5.4 Executing queries with asyncpg

5.5 Executing queries concurrently with connection pools

5.5.1 Inserting random SKUs into the product database

5.5.2 Creating a connection pool to run queries concurrently

5.6 Managing transactions with asyncpg

5.6.1 Nested transactions

5.6.2 Manually managing transactions

5.7 Asynchronous generators and streaming result sets

5.7.1 Introducing asynchronous generators

5.7.2 Using asynchronous generators with a streaming cursor

5.8 Summary