Backend Step 3: Database Setup with SQLAlchemy

Now you'll add a database to store chat messages.

What You'll Learn

Setting up SQLAlchemy with Flask
Defining database models
Creating tables
Inserting and querying data

Step 1: Update init.py with Database Setup

Edit backend/app/__init__.py:

from flask import Flask, jsonify
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()


def create_app():
    app = Flask(__name__)
    app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///chat.db"
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

    db.init_app(app)

    @app.route("/")
    def home():
        return jsonify({"message": "Hello Flask!"})

    @app.route("/api/status")
    def status():
        return jsonify({"status": "ok", "version": "1.0"})

    with app.app_context():
        db.create_all()

    return app


if __name__ == "__main__":
    app = create_app()
    app.run(debug=True, port=5000)

Key new lines: - db = SQLAlchemy() — creates the database object - app.config["SQLALCHEMY_DATABASE_URI"] — tells Flask to use a SQLite file called chat.db - db.init_app(app) — connects the database to the Flask app - db.create_all() — creates tables based on your models

Step 2: Create the Message Model

Edit backend/app/models.py:

from . import db
from datetime import datetime, timezone


class Message(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    content = db.Column(db.String(1000), nullable=False)
    sender = db.Column(db.String(20), nullable=False)
    timestamp = db.Column(
        db.DateTime, default=lambda: datetime.now(timezone.utc)
    )

    def to_dict(self):
        return {
            "id": self.id,
            "content": self.content,
            "sender": self.sender,
            "timestamp": self.timestamp.isoformat()
            if self.timestamp
            else None,
        }

What each column does: - id — unique identifier, auto-incremented - content — the message text (up to 1000 characters, required) - sender — either "user" or "ai", required - timestamp — when the message was created (defaults to now) - to_dict() — converts the model to a dictionary for JSON response

Step 3: Import Models in init.py

Add the import at the top of __init__.py:

from flask import Flask, jsonify
from flask_sqlalchemy import SQLAlchemy
from dotenv import load_dotenv

load_dotenv()

db = SQLAlchemy()


def create_app():
    app = Flask(__name__)
    app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///chat.db"
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

    db.init_app(app)

    from .models import Message  # Import models after db init

    @app.route("/")
    def home():
        return jsonify({"message": "Hello Flask!"})

    @app.route("/api/status")
    def status():
        return jsonify({"status": "ok", "version": "1.0"})

    with app.app_context():
        db.create_all()

    return app


if __name__ == "__main__":
    app = create_app()
    app.run(debug=True, port=5000)

Step 4: Add a Test for the Model

Before running the server, write a test that checks your model works:

Create backend/tests/test_model.py:

from app.models import Message


def test_create_message(client):
    """A message can be created and read back."""
    response = client.post("/api/chat", json={"message": "Hello!"})
    assert response.status_code == 200


def test_message_has_required_fields(app):
    """Message model has the right fields."""
    with app.app_context():
        from app import db
        from app.models import Message

        msg = Message(content="Test", sender="user")
        db.session.add(msg)
        db.session.commit()

        saved = Message.query.first()
        assert saved.content == "Test"
        assert saved.sender == "user"
        assert saved.id is not None
        assert saved.timestamp is not None

Run it:

cd aiproject/backend
python -m pytest tests/ -v

You should see both tests pass. The second test directly creates a Message object without going through an API endpoint — it tests the database model in isolation.

Step 5: Run and Verify

Option A — Virtual Environment:

cd aiproject/backend
source venv/bin/activate
python app/__init__.py

Option B — Docker:

cd aiproject/backend
docker compose up

Stop the server (Ctrl+C) and check that instance/chat.db was created:

Venv:

ls -la instance/
# → chat.db

Docker: The database file is stored inside a Docker volume. To check it exists:

docker compose run app ls -la instance/
# → chat.db

To reset the database (delete all messages):

docker compose down -v
docker compose up

Step 5: Test the Database with Python Shell

cd aiproject/backend
source venv/bin/activate
python3

>>> from app import create_app, db
>>> from app.models import Message
>>> app = create_app()
>>> with app.app_context():
...     # Add a test message
...     msg = Message(content="Hello database!", sender="user")
...     db.session.add(msg)
...     db.session.commit()
...     print(f"Message ID: {msg.id}")
...     # Query all messages
...     messages = Message.query.all()
...     for m in messages:
...         print(m.to_dict())
...

Expected output:

Message ID: 1
{'id': 1, 'content': 'Hello database!', 'sender': 'user', 'timestamp': '2025-01-15T10:30:00+00:00'}

Explanation: What is SQLAlchemy?

SQLAlchemy is an ORM (Object-Relational Mapper). It lets you:

Define database tables as Python classes (class Message(db.Model))
Work with rows as Python objects (msg.content)
Use Python methods instead of SQL (Message.query.all() instead of SELECT * FROM message)

Checking Your Work

Your database now: - Has a message table with 4 columns (id, content, sender, timestamp) - Contains at least one test message - Is ready to receive data from API endpoints

← Step 2: Hello Flask Step 4: API →