agents-store SQLAlchemy Troubleshooting
Diagnostic steps and fixes for common SQLAlchemy problems.
Quick Diagnostics
Run these checks first:
- Database exists? — Check if the database file or server is accessible
- Tables created? — Run
flask db upgradeordb.create_all() - Models imported? — Ensure all models are imported before
create_all() - Session clean? — Run
db.session.rollback()if in a broken state
Connection Errors
| Error | Cause | Fix |
|---|---|---|
OperationalError: unable to open database file |
Wrong path or missing directory | Verify SQLALCHEMY_DATABASE_URI path; create instance/ dir |
OperationalError: no such table: X |
Tables not created | Run db.create_all() or flask db upgrade |
OperationalError: database is locked |
SQLite concurrent writes | Use WAL mode or switch to PostgreSQL |
ModuleNotFoundError: psycopg2 |
Missing PostgreSQL driver | pip install psycopg2-binary |
OperationalError: FATAL: password authentication failed |
Wrong credentials | Check DATABASE_URL user/password |
OperationalError: could not connect to server |
DB server not running | Start the database server |
Integrity Errors
| Error | Cause | Fix |
|---|---|---|
IntegrityError: UNIQUE constraint failed |
Duplicate value on unique column | Check for existing record before insert |
IntegrityError: NOT NULL constraint failed |
Missing required field | Ensure all nullable=False columns have values |
IntegrityError: FOREIGN KEY constraint failed |
Referenced record doesn't exist | Verify parent record exists before creating child |
Handling Integrity Errors
from sqlalchemy.exc import IntegrityError
try:
db.session.add(record)
db.session.commit()
except IntegrityError:
db.session.rollback()
flash('A record with that value already exists', 'error')
Session Errors
| Error | Cause | Fix |
|---|---|---|
DetachedInstanceError: Instance is not bound to a Session |
Accessing object after session closed | Access all attributes within session scope |
InvalidRequestError: This session's transaction has been rolled back |
Previous error left session dirty | Call db.session.rollback() before retrying |
RuntimeError: Working outside of application context |
No Flask app context | Wrap in with app.app_context(): |
Session Best Practices
# Always rollback on error
try:
db.session.add(obj)
db.session.commit()
except Exception:
db.session.rollback()
raise
# Access relationships before returning from view
client = Client.query.get(id)
name = client.name # Access here, not in template
visits = client.visit_count # Computed property — access here
Migration Errors
| Error | Cause | Fix |
|---|---|---|
Target database is not up to date |
Pending migrations | Run flask db upgrade |
Can't locate revision |
Broken migration chain | flask db stamp head to reset |
No changes in schema detected |
Model changes not picked up | Import models in env.py |
| Autogenerate missed a change | Limitation of autogenerate | Edit migration manually |
Performance Issues
| Symptom | Cause | Fix |
|---|---|---|
| Many queries for one page | N+1 problem (lazy loading) | Use joinedload() or selectinload() |
| Slow queries on large tables | Missing indexes | Add index=True to frequently filtered columns |
| Memory growing on bulk operations | Objects accumulate in session | Use db.session.expire_on_commit = False or batch |
Slow count() on large tables |
Full table scan | Use func.count() with specific filters |
Detecting N+1 Queries
# Enable SQLAlchemy query logging
import logging
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
Watch for repeated similar queries — fix with eager loading:
from sqlalchemy.orm import joinedload
# Before (N+1): 1 query for appointments + N queries for clients
appointments = Appointment.query.all()
# After (eager): 1 query with JOIN
appointments = Appointment.query.options(
joinedload(Appointment.client)
).all()
Type Errors
| Error | Cause | Fix |
|---|---|---|
StatementError: expected string or bytes-like object |
Wrong type for column | Check db.Column type matches the data |
DataError: value too long for type character varying |
String exceeds String(N) length |
Increase column size or validate input |
ProgrammingError: can't adapt type 'dict' |
Passing dict to non-JSON column | Use db.Column(db.JSON) or serialize |