Building Your First Messaging App with SQLAlchemy

You know what’s wild? Every messaging app you’ve ever used - WhatsApp, Slack, Discord - they all follow the same basic pattern. Users send messages to each other, and those messages get stored in a database. Today we’re going to build exactly that, but we’ll keep it simple so you can see how all the pieces fit together.

Here’s the thing about messaging apps: they’re perfect for learning web development because they involve users, data relationships, and real-time interactions. Plus, once you understand how to store and retrieve messages, you’ve basically cracked the code on most social applications.

What We’re Building

We’re creating a simple messaging system with these features:

Users: People who can send and receive messages
Messages: Text messages between users with timestamps
Simple Web Interface: Forms to send messages and view conversations
SQLAlchemy Backend: Proper database relationships and queries

Don’t worry if you’ve never used SQLAlchemy before - it’s just a way to talk to databases using Python objects instead of raw SQL. Trust me, it makes everything cleaner.

Setting Up Your Environment

First, let’s get our tools ready. Create a new directory for your project and set up a virtual environment:

1mkdir messaging-app
2cd messaging-app
3python -m venv venv
4source venv/bin/activate  # On Windows: venv\Scripts\activate

Now install what we need:

1pip install flask sqlalchemy flask-sqlalchemy

That’s it! SQLAlchemy handles all the database complexity, and Flask gives us a web framework that’s perfect for learning.

Database Models: Users and Messages

Here’s where SQLAlchemy shines - we define our database tables as Python classes. Create models.py:

 1from flask_sqlalchemy import SQLAlchemy
 2from datetime import datetime
 3
 4db = SQLAlchemy()
 5
 6class User(db.Model):
 7    id = db.Column(db.Integer, primary_key=True)
 8    username = db.Column(db.String(80), unique=True, nullable=False)
 9    email = db.Column(db.String(120), unique=True, nullable=False)
10    created_at = db.Column(db.DateTime, default=datetime.utcnow)
11    
12    # Relationship: messages this user has sent
13    sent_messages = db.relationship('Message', 
14                                   foreign_keys='Message.sender_id',
15                                   backref='sender', 
16                                   lazy='dynamic')
17    
18    # Relationship: messages this user has received  
19    received_messages = db.relationship('Message',
20                                       foreign_keys='Message.recipient_id', 
21                                       backref='recipient',
22                                       lazy='dynamic')
23    
24    def __repr__(self):
25        return f'<User {self.username}>'
26
27class Message(db.Model):
28    id = db.Column(db.Integer, primary_key=True)
29    content = db.Column(db.Text, nullable=False)
30    timestamp = db.Column(db.DateTime, default=datetime.utcnow)
31    
32    # Foreign keys to link messages to users
33    sender_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
34    recipient_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
35    
36    def __repr__(self):
37        return f'<Message {self.id}: {self.content[:20]}...>'

See what’s happening here? We’re defining the relationship between users and messages. Each message has a sender and a recipient, both of which are users. SQLAlchemy automatically creates the foreign key relationships for us.

The lazy='dynamic' part means that when we ask for a user’s messages, SQLAlchemy doesn’t load them all at once - it gives us a query object we can filter and sort. Smart, right?

Flask Application Setup

Now let’s create our web application. Create app.py:

  1from flask import Flask, render_template, request, redirect, url_for, flash
  2from models import db, User, Message
  3import os
  4
  5app = Flask(__name__)
  6app.config['SECRET_KEY'] = 'your-secret-key-here'
  7app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///messaging.db'
  8app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
  9
 10# Initialize the database
 11db.init_app(app)
 12
 13@app.before_first_request
 14def create_tables():
 15    """Create database tables if they don't exist"""
 16    db.create_all()
 17
 18@app.route('/')
 19def index():
 20    """Home page showing all users"""
 21    users = User.query.all()
 22    return render_template('index.html', users=users)
 23
 24@app.route('/user/<int:user_id>')
 25def user_profile(user_id):
 26    """Show user profile and their messages"""
 27    user = User.query.get_or_404(user_id)
 28    
 29    # Get all messages for this user (sent and received)
 30    sent = user.sent_messages.order_by(Message.timestamp.desc()).limit(10).all()
 31    received = user.received_messages.order_by(Message.timestamp.desc()).limit(10).all()
 32    
 33    return render_template('user_profile.html', user=user, sent=sent, received=received)
 34
 35@app.route('/conversation/<int:user1_id>/<int:user2_id>')
 36def conversation(user1_id, user2_id):
 37    """Show conversation between two users"""
 38    user1 = User.query.get_or_404(user1_id)
 39    user2 = User.query.get_or_404(user2_id)
 40    
 41    # Get messages between these two users
 42    messages = Message.query.filter(
 43        ((Message.sender_id == user1_id) & (Message.recipient_id == user2_id)) |
 44        ((Message.sender_id == user2_id) & (Message.recipient_id == user1_id))
 45    ).order_by(Message.timestamp.asc()).all()
 46    
 47    return render_template('conversation.html', user1=user1, user2=user2, messages=messages)
 48
 49@app.route('/send_message', methods=['GET', 'POST'])
 50def send_message():
 51    """Send a new message"""
 52    if request.method == 'POST':
 53        sender_id = request.form['sender_id']
 54        recipient_id = request.form['recipient_id'] 
 55        content = request.form['content']
 56        
 57        # Validate the input
 58        if not content.strip():
 59            flash('Message cannot be empty!')
 60            return redirect(url_for('send_message'))
 61            
 62        if sender_id == recipient_id:
 63            flash('You cannot send a message to yourself!')
 64            return redirect(url_for('send_message'))
 65        
 66        # Create and save the message
 67        message = Message(
 68            content=content.strip(),
 69            sender_id=int(sender_id),
 70            recipient_id=int(recipient_id)
 71        )
 72        
 73        db.session.add(message)
 74        db.session.commit()
 75        
 76        flash('Message sent successfully!')
 77        return redirect(url_for('conversation', user1_id=sender_id, user2_id=recipient_id))
 78    
 79    # GET request - show the form
 80    users = User.query.all()
 81    return render_template('send_message.html', users=users)
 82
 83@app.route('/add_user', methods=['GET', 'POST'])
 84def add_user():
 85    """Add a new user"""
 86    if request.method == 'POST':
 87        username = request.form['username']
 88        email = request.form['email']
 89        
 90        # Basic validation
 91        if not username.strip() or not email.strip():
 92            flash('Username and email are required!')
 93            return redirect(url_for('add_user'))
 94            
 95        # Check if user already exists
 96        existing_user = User.query.filter_by(username=username).first()
 97        if existing_user:
 98            flash('Username already exists!')
 99            return redirect(url_for('add_user'))
100            
101        # Create new user
102        user = User(username=username.strip(), email=email.strip())
103        db.session.add(user)
104        db.session.commit()
105        
106        flash(f'User {username} created successfully!')
107        return redirect(url_for('index'))
108    
109    return render_template('add_user.html')
110
111if __name__ == '__main__':
112    app.run(debug=True)

This is the heart of our application. Each route handles a different part of the messaging functionality:

Index: Shows all users in the system
User Profile: Shows a user’s sent and received messages
Conversation: Shows messages between two specific users
Send Message: Form to create new messages
Add User: Form to create new users

Notice how we’re using SQLAlchemy queries instead of raw SQL? User.query.all() gets all users, and Message.query.filter(...) lets us find specific messages. Much cleaner than writing SQL strings.

HTML Templates

Let’s create the web interface. First, create a templates directory and add base.html:

  1<!DOCTYPE html>
  2<html lang="en">
  3<head>
  4    <meta charset="UTF-8">
  5    <meta name="viewport" content="width=device-width, initial-scale=1.0">
  6    <title>Simple Messaging App</title>
  7    <style>
  8        body { 
  9            font-family: Arial, sans-serif; 
 10            max-width: 800px; 
 11            margin: 0 auto; 
 12            padding: 20px;
 13            background-color: #f5f5f5;
 14        }
 15        .container { 
 16            background: white; 
 17            padding: 20px; 
 18            border-radius: 8px; 
 19            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
 20        }
 21        .message {
 22            background: #f0f8ff;
 23            padding: 10px;
 24            margin: 10px 0;
 25            border-radius: 5px;
 26            border-left: 4px solid #4CAF50;
 27        }
 28        .message.sent {
 29            background: #e8f5e8;
 30            text-align: right;
 31        }
 32        .message.received {
 33            background: #f0f8ff;
 34            text-align: left;
 35        }
 36        .nav {
 37            margin-bottom: 20px;
 38        }
 39        .nav a {
 40            margin-right: 15px;
 41            color: #007bff;
 42            text-decoration: none;
 43        }
 44        .nav a:hover {
 45            text-decoration: underline;
 46        }
 47        .flash-messages {
 48            margin-bottom: 20px;
 49        }
 50        .flash {
 51            padding: 10px;
 52            margin: 5px 0;
 53            border-radius: 4px;
 54            background: #d4edda;
 55            color: #155724;
 56            border: 1px solid #c3e6cb;
 57        }
 58        form {
 59            background: #f8f9fa;
 60            padding: 20px;
 61            border-radius: 8px;
 62            margin: 20px 0;
 63        }
 64        input, select, textarea {
 65            width: 100%;
 66            padding: 8px;
 67            margin: 5px 0;
 68            border: 1px solid #ddd;
 69            border-radius: 4px;
 70            box-sizing: border-box;
 71        }
 72        button {
 73            background: #007bff;
 74            color: white;
 75            padding: 10px 20px;
 76            border: none;
 77            border-radius: 4px;
 78            cursor: pointer;
 79        }
 80        button:hover {
 81            background: #0056b3;
 82        }
 83    </style>
 84</head>
 85<body>
 86    <div class="container">
 87        <div class="nav">
 88            <a href="{{ url_for('index') }}">Home</a>
 89            <a href="{{ url_for('add_user') }}">Add User</a>
 90            <a href="{{ url_for('send_message') }}">Send Message</a>
 91        </div>
 92        
 93        <div class="flash-messages">
 94            {% with messages = get_flashed_messages() %}
 95                {% if messages %}
 96                    {% for message in messages %}
 97                        <div class="flash">{{ message }}</div>
 98                    {% endfor %}
 99                {% endif %}
100            {% endwith %}
101        </div>
102        
103        {% block content %}{% endblock %}
104    </div>
105</body>
106</html>

Now create templates/index.html:

 1{% extends "base.html" %}
 2
 3{% block content %}
 4<h1>Simple Messaging App</h1>
 5<p>Welcome to our messaging system! Here you can send messages to other users and view conversations.</p>
 6
 7<h2>Users in the System</h2>
 8{% if users %}
 9    <ul>
10    {% for user in users %}
11        <li>
12            <strong><a href="{{ url_for('user_profile', user_id=user.id) }}">{{ user.username }}</a></strong>
13            ({{ user.email }}) - Joined {{ user.created_at.strftime('%Y-%m-%d') }}
14        </li>
15    {% endfor %}
16    </ul>
17{% else %}
18    <p>No users yet. <a href="{{ url_for('add_user') }}">Add the first user!</a></p>
19{% endif %}
20
21<h2>Quick Actions</h2>
22<p>
23    <a href="{{ url_for('add_user') }}">Add New User</a> | 
24    <a href="{{ url_for('send_message') }}">Send Message</a>
25</p>
26{% endblock %}

Create templates/user_profile.html:

 1{% extends "base.html" %}
 2
 3{% block content %}
 4<h1>{{ user.username }}'s Profile</h1>
 5<p><strong>Email:</strong> {{ user.email }}</p>
 6<p><strong>Joined:</strong> {{ user.created_at.strftime('%Y-%m-%d %H:%M') }}</p>
 7
 8<div style="display: flex; gap: 20px;">
 9    <div style="flex: 1;">
10        <h3>Recent Messages Sent ({{ user.sent_messages.count() }} total)</h3>
11        {% if sent %}
12            {% for message in sent %}
13                <div class="message sent">
14                    <p><strong>To:</strong> {{ message.recipient.username }}</p>
15                    <p>{{ message.content }}</p>
16                    <small>{{ message.timestamp.strftime('%Y-%m-%d %H:%M') }}</small>
17                </div>
18            {% endfor %}
19        {% else %}
20            <p>No messages sent yet.</p>
21        {% endif %}
22    </div>
23    
24    <div style="flex: 1;">
25        <h3>Recent Messages Received ({{ user.received_messages.count() }} total)</h3>
26        {% if received %}
27            {% for message in received %}
28                <div class="message received">
29                    <p><strong>From:</strong> {{ message.sender.username }}</p>
30                    <p>{{ message.content }}</p>
31                    <small>{{ message.timestamp.strftime('%Y-%m-%d %H:%M') }}</small>
32                </div>
33            {% endfor %}
34        {% else %}
35            <p>No messages received yet.</p>
36        {% endif %}
37    </div>
38</div>
39{% endblock %}

Create templates/conversation.html:

 1{% extends "base.html" %}
 2
 3{% block content %}
 4<h1>Conversation between {{ user1.username }} and {{ user2.username }}</h1>
 5
 6<div class="conversation">
 7    {% if messages %}
 8        {% for message in messages %}
 9            <div class="message {% if message.sender_id == user1.id %}sent{% else %}received{% endif %}">
10                <p><strong>{{ message.sender.username }}:</strong> {{ message.content }}</p>
11                <small>{{ message.timestamp.strftime('%Y-%m-%d %H:%M') }}</small>
12            </div>
13        {% endfor %}
14    {% else %}
15        <p>No messages in this conversation yet.</p>
16    {% endif %}
17</div>
18
19<h3>Send a Message</h3>
20<form method="POST" action="{{ url_for('send_message') }}">
21    <input type="hidden" name="sender_id" value="{{ user1.id }}">
22    <input type="hidden" name="recipient_id" value="{{ user2.id }}">
23    <textarea name="content" placeholder="Type your message..." rows="4" required></textarea>
24    <button type="submit">Send Message</button>
25</form>
26{% endblock %}

Create templates/send_message.html:

 1{% extends "base.html" %}
 2
 3{% block content %}
 4<h1>Send a Message</h1>
 5
 6<form method="POST">
 7    <label for="sender_id">From:</label>
 8    <select name="sender_id" id="sender_id" required>
 9        <option value="">Select sender...</option>
10        {% for user in users %}
11            <option value="{{ user.id }}">{{ user.username }}</option>
12        {% endfor %}
13    </select>
14    
15    <label for="recipient_id">To:</label>
16    <select name="recipient_id" id="recipient_id" required>
17        <option value="">Select recipient...</option>
18        {% for user in users %}
19            <option value="{{ user.id }}">{{ user.username }}</option>
20        {% endfor %}
21    </select>
22    
23    <label for="content">Message:</label>
24    <textarea name="content" id="content" placeholder="Type your message here..." rows="5" required></textarea>
25    
26    <button type="submit">Send Message</button>
27</form>
28{% endblock %}

Create templates/add_user.html:

 1{% extends "base.html" %}
 2
 3{% block content %}
 4<h1>Add New User</h1>
 5
 6<form method="POST">
 7    <label for="username">Username:</label>
 8    <input type="text" name="username" id="username" required>
 9    
10    <label for="email">Email:</label>
11    <input type="email" name="email" id="email" required>
12    
13    <button type="submit">Create User</button>
14</form>
15{% endblock %}

Running Your Messaging App

Now for the exciting part - let’s see it in action! Start your server:

1python app.py

Visit http://localhost:5000 and you’ll see your messaging app! Here’s what you can do:

Add some users - Create a few test accounts
Send messages - Try messaging between different users
View conversations - See how messages are organized
Check user profiles - Look at each user’s message history

The first time you run it, SQLAlchemy will automatically create the database file and tables. Pretty cool, right?

Understanding the Database Relationships

Here’s what makes this app special - the relationships between our data:

One-to-Many Relationships

Each user can send many messages, and each user can receive many messages. But each message has exactly one sender and one recipient. This is a classic one-to-many relationship.

1# Get all messages sent by a user
2user = User.query.get(1)
3sent_messages = user.sent_messages.all()
4
5# Get the sender of a message
6message = Message.query.get(1)
7sender = message.sender

Foreign Keys

The sender_id and recipient_id columns in the Message table are foreign keys that reference the User table’s primary key. SQLAlchemy handles all the complexity of maintaining these relationships.

Query Magic

Want to see all conversations between two users? SQLAlchemy makes it easy:

1messages = Message.query.filter(
2    ((Message.sender_id == user1_id) & (Message.recipient_id == user2_id)) |
3    ((Message.sender_id == user2_id) & (Message.recipient_id == user1_id))
4).order_by(Message.timestamp.asc()).all()

This finds all messages where user1 sent to user2 OR user2 sent to user1, ordered by time.

What You’ve Just Built

You’ve created a real messaging application with:

User management - Creating and storing users
Message storage - Persisting conversations in a database
Relationships - Connecting users to their messages
Web interface - Forms and views to interact with the data
SQLAlchemy ORM - No raw SQL required!

Next Steps and Enhancements

This is just the beginning! Here are some features you could add:

Basic Improvements

Message editing and deletion
User authentication (login/logout)
Message timestamps displayed better
Pagination for long conversations

Intermediate Features

Group messaging (one-to-many conversations)
Message status (sent, delivered, read)
User profiles with avatars
Search functionality for messages

Advanced Features

Real-time messaging with WebSockets
Message encryption for privacy
File attachments and media sharing
Push notifications for new messages

Why SQLAlchemy Rocks

Notice how we didn’t write a single line of SQL? SQLAlchemy handled all the database operations for us:

Automatic table creation from our model classes
Relationship management with foreign keys
Query building with Python syntax
Data validation and type conversion

This is huge for productivity. Instead of debugging SQL syntax errors, you’re thinking about your application logic.

Testing Your App

Want to add some test data quickly? Add this to your app.py:

 1@app.route('/setup_test_data')
 2def setup_test_data():
 3    """Add some test users and messages"""
 4    # Create test users
 5    alice = User(username='alice', email='alice@example.com')
 6    bob = User(username='bob', email='bob@example.com')
 7    charlie = User(username='charlie', email='charlie@example.com')
 8    
 9    db.session.add_all([alice, bob, charlie])
10    db.session.commit()
11    
12    # Create test messages
13    messages = [
14        Message(content="Hey Bob, how's it going?", sender=alice, recipient=bob),
15        Message(content="Good! How about you?", sender=bob, recipient=alice),
16        Message(content="Charlie, want to join our group project?", sender=alice, recipient=charlie),
17        Message(content="Sure! When do we start?", sender=charlie, recipient=alice),
18    ]
19    
20    db.session.add_all(messages)
21    db.session.commit()
22    
23    return "Test data created!"

Visit /setup_test_data to populate your app with sample conversations.

The Bottom Line

You’ve just built a real web application with proper database relationships, a clean interface, and professional-grade architecture. The patterns you’ve learned here - models, relationships, forms, templates - are the same ones used in major applications.

SQLAlchemy makes database programming feel natural and Pythonic. Instead of thinking in terms of tables and SQL, you think in terms of objects and relationships. That’s powerful.

Start playing with your messaging app, break things, fix them, and add features. The best way to learn web development is to build something real and keep improving it. Six months from now, you’ll look back at this simple app as the foundation that launched your web development journey!

Building a Simple User API with FastAPI and SQLite (Python)