A web app needs somewhere to keep users and posts after a request ends. SQLite provides a small, in-process database for that job. It runs with the application, so development does not require installing and maintaining a separate database server. section 10.4
SQLAlchemy manages the application’s data in SQLite and keeps most database-specific details out of application code. That lets development focus on models and persistence; SQLAlchemy also makes a later move to another database system easier if the application outgrows SQLite. section 10.4
Persisting users for login
A Flask application initializes its database integration alongside its login and password-hashing components, then creates the tables. A User model stores identifying fields and a hashed password. The login system retrieves persisted users to identify them later; passwords must not be stored as plain text. section 8.2.1
Database choice can still affect a model’s design. SQLite does not provide the desired autoincrement behavior for a nonprimary-key post sort field. A Python default function instead reads the current maximum sort key and adds one, supplying values used to order posts and comments. section 11.7.1
def get_next_sort_key() -> int:
with db_session_manager(session_close=False) as db_session:
retval = db_session.query(func.ifnull(
func.max(Post.sort_key) + 1, 0)).scalar()
if retval is None:
raise RuntimeError("Failed to get new value for sort_key")
return retval
The function queries the maximum post sort key, returns the next value, and starts at zero when no value exists. section 11.7.1
