Damian Wysocki
Deep Dive into Asynchronous SQLAlchemy -
Transactions and Connections
- Python Developer for 6 years
- Currtnely im a python dev at mirumee software
- Mentor at Mirumee Starter
- organizer of the local IT meetup MeeTTech
About me
Basic terminology
Session
Transaction
Let's jump into the code
Let's check documentation
https://docs.sqlalchemy.org/en/20/orm/contextual.html#using-thread-local-scope-with-web-applications
TO BE REMOVED
implicit transaction example
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
database 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
implicit transaction summary
-
create session with dependency injection
-
connection is acquired on demand
-
transaction is created implicitly
-
connection is released after returning the response
-
rollback on return
possible solution
.explicit transaction example
old code
database 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()
@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()
.explicit transaction example
new code
database 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
database 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
database 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
explicit transaction example
code
database 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
explicit transaction example
code
database logs
explicit transaction summary
-
create session with dependency injection
-
connection is acquired on demand
-
transaction is created implicitly
-
connection is released before returning the response
-
rollback on return
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
change me
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
explicit transaction example
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.
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()
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()
Normal async function, right?
let's test it!
benchmarks
benchmarks
5 concurrent requests
pool size is equal to 5
1
2
3
4
5
6
time in seconds
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()
implicit transactions
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()
explicit transactions
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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()
old benchmarks
10 concurrent requests
1
2
3
4
5
6
7
8
9
10
time in seconds
benchmarks - for solution
10 concurrent requests
1
2
3
4
5
6
7
8
9
10
time in seconds
benchmarks - for solution
10 concurrent requests
1
2
3
4
5
6
7
8
9
10
time in seconds
What do programmers love the most?
diagrams!
add qrcode to 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
change me
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
benchmarks
explicit transactions - 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
RPS (requests per second)
160
180
200
benchmarks
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
RPS (requests per second)
160
180
200
benchmarks
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
RPS (requests per second)
160
180
200
summary
- explicit is better than implicit
- release the connection when we dont need them
- connection is passed as a reference
thanks you
let's connect
Code
By Damian Wysocki
