@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
SQLAlchemy
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
30 RPS
SQLAlchemy
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
SQLAlchemy
Core
Encode/
Databases
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
170 RPS
SQLAlchemy
Core
Encode/
Databases
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
SQLAlchemy
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
170 RPS
SQLAlchemy
Damian Wysocki
Deep Dive into Asynchronous SQLAlchemy -
Transactions and Connections
- Python Developer for 6 years
- Currently im a python dev at Mirumee Software
- Mentor at Mirumee Starter
- Organizer of the local IT meetup MeeTTech
- Speaker at Europython, 2023 Prague
About me
Let's jump into the code
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = async_sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
app = FastAPI()
async def get_db():
async with async_session() as session:
yield session
@app.get("/notes/{note_id}")
async def get_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
Implicit transaction example
Code
@app.get("/notes/{note_id}")
async def get_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
Implicit transaction example
Code
@app.get("/notes/{note_id}")
async def get_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
. Implicit transaction example
Code
Logs
@app.get("/notes/{note_id}")
async def get_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
DEBUG Connection checked out from pool
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine SELECT (...)
Implicit transaction example
Code
Logs
@app.get("/notes/{note_id}")
async def get_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
# exit function <-
DEBUG Connection checked out from pool
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine SELECT (...)
INFO: 127.0.0.1:63630 - "GET /note?note_id=1 HTTP/1.1" 200 OK
INFO sqlalchemy.engine.Engine ROLLBACK
DEBUG Connection being returned to pool
DEBUG Connection rollback-on-return
Implicit transaction example
Code
Logs
1
Request validation
session is created
- close transaction
- connection is released
- session is closed
2
request
middleware
3
dependency injection
4
request execution
5
response generation
6
response
middleware
7
response validation
8
exception
handling
9
return response
10
async
exit
stack
- connection is aquired
- transaction created
Simplified request lifecycle
Implicit transaction example
Possible solution
.Old code
Logs
@app.get("/notes/{note_id}")
async def get_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
Possible solution
.Old code
Logs
@app.get("/notes/{note_id}")
async def get_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
Possible solution
@app.get("/notes/{note_id}")
async def get_note(database=Depends(get_db)):
async with database.begin():
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
.Possible solution
New code
Logs
@app.get("/notes/{note_id}")
async def get_note(database=Depends(get_db)):
async with database.begin():
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
.Possible solution
Code
Logs
@app.get("/notes/{note_id}")
async def get_note(database=Depends(get_db)):
async with database.begin():
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
DEBUG Connection checked out from pool
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine SELECT (...)
Possible solution
Code
Logs
@app.get("/notes/{note_id}")
async def get_note(database=Depends(get_db)):
async with database.begin():
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
# exit context manager <-
DEBUG Connection checked out from pool
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine SELECT (...)
INFO sqlalchemy.engine.Engine COMMIT
DEBUG Connection being returned to pool
DEBUG Connection rollback-on-return
Possible solution
Code
Logs
@app.get("/notes/{note_id}")
async def get_note(database=Depends(get_db)):
async with database.begin():
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
# exit context manager <-
# exit function <-DEBUG Connection checked out from pool
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine SELECT (...)
INFO sqlalchemy.engine.Engine COMMIT
DEBUG Connection being returned to pool
DEBUG Connection rollback-on-return
INFO: 127.0.0.1:63630 - "GET /note?note_id=1 HTTP/1.1" 200 OK
Possible solution
Code
Logs
Compare logs
Compare logs
DEBUG Connection checked out from pool
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine SELECT (...)
INFO sqlalchemy.engine.Engine COMMIT
DEBUG Connection being returned to pool
DEBUG Connection rollback-on-return
INFO: 127.0.0.1:63630 - "GET /note?note_id=1 HTTP/1.1" 200 OK
Improved solution's logs
DEBUG Connection checked out from pool
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine SELECT (...)
INFO: 127.0.0.1:63630 - "GET /note?note_id=1 HTTP/1.1" 200 OK
INFO sqlalchemy.engine.Engine ROLLBACK
DEBUG Connection being returned to pool
DEBUG Connection rollback-on-return
Initial solution's logs
Compare request lifecycle
1
Request validation
session is created
- close transaction
- connection is released
- session is closed
2
middleware
3
dependency injection
4
request execution
5
response geneation
6
middleware
7
response validation
8
exception
handling
9
return response
10
async
exit
stack
- connection is aquired
- transaction created
Initial solution
1
Request validation
session is created
- session is closed
3
dependency injection
4
request execution
5
response geneation
6
middleware
7
response validation
8
exception
handling
9
return response
10
async
exit
stack
- connection is aquired
- transaction created
- close transaction
- connection is released
2
middleware
Possible solution
Summary
- Transaction and connection are tightly coupled.
- Implicit operations performed by SQLAlchemy
- Even simple select statements initiate transactions implicitly.
- Releasing the connection requires closing the transaction, resulting in implicit rollback or commit.
A more real life example
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
A more real life example
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
A more real life example
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
Response time benchmarks
Response time benchmarks
5 concurrent requests
pool size is equal to 5
1
2
3
4
5
6
time in seconds
Response time benchmarks
10 concurrent requests
pool size is equal to 5
1
2
3
4
5
6
7
8
9
10
time in seconds
Response time benchmarks
10 concurrent requests
pool size is equal to 5
1
2
3
4
5
6
7
8
9
10
time in seconds
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(note_id: int, database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
requests
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(note_id: int, database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(note_id: int, database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(note_id: int, database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(note_id: int, database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(note_id: int, database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(note_id: int, database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(note_id: int, database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(note_id: int, database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(note_id: int, database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(note_id: int, database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(note_id: int, database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(note_id: int, database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(note_id: int, database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(note_id: int, database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
Solution
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
Solution
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
connections pool
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
requests
time
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
connections pool
Old benchmarks
10 concurrent requests
1
2
3
4
5
6
7
8
9
10
time in seconds
Old benchmarks
10 concurrent requests
1
2
3
4
5
6
7
8
9
10
time in seconds
Benchmarks - for solution
1
2
3
4
5
6
7
8
9
10
10 concurrent requests
time in seconds
1
2
3
4
5
6
7
8
9
10
time in seconds
Benchmarks - for solution
10 concurrent requests
What do programmers love the most?
Diagrams!
Bonus
encode/databases
sqlalchemy
engine = create_async_engine(DATABASE_URL, pool_size=5, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("notes/{note_id}/synchronize")
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
encode/databases
database = databases.Database(DATABASE_URL)
@app.post("notes/{note_id}/synchronize")
async def synchronize_note():
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
encode/databases
database = databases.Database(DATABASE_URL)
@app.on_event("startup")
async def startup():
await database.connect()
@app.on_event("shutdown")
async def shutdown():
await database.disconnect()
@app.post("notes/{note_id}/synchronize")
async def synchronize_note():
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
Benchmarks
1000 concurrent users
10 available connections
1 worker
Benchmarks
implicit session management - SQLAlchemy
engine = create_async_engine(DATABASE_URL, pool_size=10, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
return result.scalar_one()
20
40
60
80
100
120
140
RPS (requests per second)
160
180
200
explicit session management - SQLAlchemy
engine = create_async_engine(DATABASE_URL, pool_size=10, max_overflow=0)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
@app.post("/notes/{note_id}/synchronize", response_model=Note)
async def synchronize_note(database=Depends(get_db)):
validate_note_id(note_id)
async with database.begin():
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
async with database.begin():
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
20
40
60
80
100
120
140
160
180
200
Benchmarks
RPS (requests per second)
database = databases.Database(DATABASE_URL)
@app.post("notes/{note_id}/synchronize")
async def synchronize_note():
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
encode/databases
20
40
60
80
100
120
140
160
180
200
Benchmarks
RPS (requests per second)
database = databases.Database(DATABASE_URL)
@app.post("notes/{note_id}/synchronize")
async def synchronize_note():
validate_note_id(note_id)
query = sqlalchemy.select(notes).where(notes.c.id == note_id)
result = await database.execute(query)
updated_note_data = await external_api.sync_note(note_id) # takes around 5 seconds
query = sqlalchemy.update(notes).values(data=updated_note_data).where(notes.c.id == note_id)
result = await database.execute(query)
await database.commit()
return result.scalar_one()
encode/databases
20
40
60
80
100
120
140
160
180
200
Benchmarks
RPS (requests per second)
Summary
- explicit is better than implicit
- release the connection when we dont need them
- connection is passed as a reference
Thank you
Let's connect
Deep Dive into Asynchronous SQLAlchemy - Transactions and Connections
By Damian Wysocki
