Operational Notes and Troubleshooting – Database Initialization and Persistence

A reliable database layer underpins the library REST API. This section explains how the SQLite file and tables are initialized, how persistence works, and how to troubleshoot common issues.

Database Configuration Overview

All database settings live in app.py. Flask-SQLAlchemy uses these keys to connect and manage models.

Config Key	Purpose
SQLALCHEMY_DATABASE_URI	URI for SQLite file: `sqlite:///data.db`.
SQLALCHEMY_TRACK_MODIFICATIONS	Enable SQLAlchemy event notifications (set to `True`).
PROPAGATE_EXCEPTIONS	Allow exceptions to bubble to Flask’s error handlers.
secret_key	Used by Flask-JWT to sign and verify JSON Web Tokens.

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///data.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
app.config['PROPAGATE_EXCEPTIONS'] = True
app.secret_key = 'SapanCrackle'

SQLite File – `data.db` Creation

The SQLite file data.db is created automatically:

📂 Location: Application root directory.
🔄 Trigger: On first HTTP request, via create_tables().
💾 Persistence: Data persists between app restarts until you delete the file.

Table Creation Lifecycle

Flask-SQLAlchemy does not include migrations. Instead, tables are created lazily before handling any request.

@app.before_first_request
def create_tables():
    db.create_all()

Decorated with @app.before_first_request to run once
Calls db.create_all(), inspecting all db.Model subclasses to generate tables.

if __name__ == '__main__':
    from db import db
    db.init_app(app)
    app.run(port=5000, debug=True)

db.init_app(app) binds SQLAlchemy to the Flask app.

Schema Evolution and Manual Migrations

No migration tool (e.g., Alembic) is included. When you add, remove, or modify model fields:

{
  "title": "Schema Changes",
  "content": "Delete `data.db` and restart the app to recreate tables after changing models."
}

🔄 Deleting data.db forces a clean schema build.
🚫 Any existing data will be lost. Backup before deleting.

Lifecycle Flowchart

flowchart TD
  A[App Start] --> B[db.init_app(app)]
  B --> C[Ready to Serve]
  C --> D{First HTTP Request?}
  D -->|Yes| E[create_tables()]
  E --> F[db.create_all()]
  F --> C
  D -->|No| C
  C --> G[Handle Request]

🐞 Troubleshooting Common Issues

Missing Tables
Ensure the first request reaches the app. Check logs for db.create_all() errors.
Stale Schema
When models change, old tables persist. Delete data.db to rebuild.
Permission Denied
Verify write permissions on the project directory.
Database Locked
Avoid multiple concurrent writes. Close long-running sessions.
Unintended Data Loss
Backup data.db before schema resets.

Best Practices

📦 .gitignore
Exclude data.db from version control to avoid merge conflicts.
🔒 Secure Secret Key
Do not hard-code secret_key in production. Use environment variables.
🛠️ Local vs Production
Use lightweight SQLite locally; switch to a robust RDBMS for production.
📝 Backups
Regularly dump or copy data.db before destructive operations.
🏷️ Environment Config
Centralize database URIs and credentials via a config file or environment variables.

By understanding how the SQLite file and tables are managed, you can confidently operate, evolve, and troubleshoot the library REST API’s persistence layer.

Search this space