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.

Updated