Async database with databases library in FastAPI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

What is the output of this FastAPI async database query?

Consider this FastAPI endpoint using the databases library to fetch all users asynchronously. What will the response contain?

FastAPI

from fastapi import FastAPI
import databases

DATABASE_URL = "sqlite+aiosqlite:///./test.db"
database = databases.Database(DATABASE_URL)
app = FastAPI()

@app.on_event("startup")
async def startup():
    await database.connect()

@app.on_event("shutdown")
async def shutdown():
    await database.disconnect()

@app.get("/users")
async def read_users():
    query = "SELECT * FROM users"
    results = await database.fetch_all(query=query)
    return results

A An empty list regardless of database content

B A single dictionary representing the first user row only

C A runtime error because fetch_all is not awaited

D A list of dictionaries, each representing a user row from the users table

💡 Hint

Remember that fetch_all returns all matching rows asynchronously.

The fetch_all method returns a list of rows matching the query. Since the query selects all users, the response is a list of dictionaries, each dictionary representing a user.

📝 Syntax

intermediate

Which option correctly initializes the databases.Database instance for PostgreSQL?

You want to connect asynchronously to a PostgreSQL database using the databases library. Which DATABASE_URL string is correct?

A "postgresql://user:password@localhost/dbname"

B "postgresql+psycopg2://user:password@localhost/dbname"

C "postgresql+asyncpg://user:password@localhost/dbname"

D "postgresql+aiopg://user:password@localhost/dbname"

💡 Hint

The databases library requires an async driver prefix for async support.

For async PostgreSQL connections with the databases library, the URL must use an async driver like asyncpg. So the correct prefix is postgresql+asyncpg://.

🔧 Debug

advanced

Why does this FastAPI app raise an error on startup with databases library?

Given this code snippet, why does the app fail to start with an error related to database connection?

FastAPI

from fastapi import FastAPI
import databases

DATABASE_URL = "sqlite+aiosqlite:///./test.db"
database = databases.Database(DATABASE_URL)
app = FastAPI()

@app.on_event("startup")
async def startup():
    await database.connect()

@app.on_event("shutdown")
async def shutdown():
    await database.disconnect()

A The startup event handler is not async, so await is missing causing a runtime error

B The database URL is invalid for SQLite

C The shutdown event handler should not be async

D The database instance is not created correctly

💡 Hint

Check if the database connect method is awaited properly.

The database.connect() method is async and must be awaited. The startup event handler is defined as a normal function, so it cannot await. This causes a runtime error.

❓ state_output

advanced

What is the value of 'user_count' after this async query?

Assuming the users table has 5 rows, what will be the value of user_count after running this code?

FastAPI

query = "SELECT COUNT(*) FROM users"
user_count = await database.fetch_val(query)

A 5

B [5]

C {'count': 5}

D None

💡 Hint

fetch_val returns the first column of the first row.

The fetch_val method returns the value of the first column in the first row of the query result. Since the query counts users, it returns the integer 5.

🧠 Conceptual

expert

Which statement about transaction management with databases library is correct?

Select the correct statement about handling transactions asynchronously using the databases library in FastAPI.

A Transactions are automatically committed after each query without explicit control

B You can use 'async with database.transaction():' to run multiple queries atomically

C The databases library does not support transactions in async mode

D You must manually call database.commit() after each query to save changes

💡 Hint

Think about Python's async context managers for transactions.

The databases library supports async transactions using an async context manager. Using async with database.transaction(): ensures all queries inside run atomically and commit or rollback together.