Working with Databases in Flask Using SQLAlchemy and SQLite

Flask is a lightweight Python web framework that makes building web applications and APIs straightforward. To store and manage data effectively, most applications need a database. In this article, we’ll explore how to integrate Flask with a database using SQLAlchemy, a powerful Object-Relational Mapping (ORM) tool, and SQLite, a simple, file-based database. We’ll cover what an ORM is, how to connect Flask to SQLite, define models, perform CRUD (Create, Read, Update, Delete) operations, and manage database changes with migrations using Flask-Migrate. Finally, we’ll build a practical example: a REST API for managing users. This guide uses simple language, clear explanations, and detailed examples to help you understand each concept.

---

What is an ORM? Why Use SQLAlchemy?

Understanding ORM

An ORM (Object-Relational Mapping) is a tool that lets you interact with a database using programming objects instead of raw SQL queries. In a database, data is stored in tables with rows and columns. An ORM maps these tables to Python classes and rows to objects, making it easier to work with data in a way that feels natural to Python developers.

For example:

• A Users table in the database might have columns like id, name, and email.

• With an ORM, you create a User class in Python, and each row in the Users table becomes a User object. You can then work with attributes like user.name or user.email instead of writing SQL queries like SELECT name FROM Users.

Why Use SQLAlchemy?

SQLAlchemy is a popular ORM for Python that works seamlessly with Flask. Here’s why it’s a great choice:

• Simplifies Database Operations: Instead of writing complex SQL queries, you use Python code to create, read, update, and delete data.

• Database Agnostic: SQLAlchemy supports multiple databases (SQLite, PostgreSQL, MySQL, etc.), so you can switch databases with minimal code changes.

• Flexibility: It allows both high-level ORM operations and low-level SQL queries when needed.

• Safety: SQLAlchemy handles SQL injection prevention and other security concerns automatically.

• Integration with Flask: The Flask-SQLAlchemy extension makes it easy to integrate with Flask apps.

For example, to fetch all users from a database, you might write:

users = User.query.all()

Instead of:

SELECT * FROM users;

This makes your code cleaner and easier to maintain.

---

Connecting Flask to SQLite

SQLite is a lightweight, file-based database that’s perfect for small to medium-sized applications or development environments. It doesn’t require a separate server (unlike PostgreSQL or MySQL) and stores data in a single file, making it easy to set up.

Setting Up Flask-SQLAlchemy

To connect Flask to SQLite using SQLAlchemy, you need to install the Flask-SQLAlchemy extension and configure your app.

1. Install Dependencies: In your virtual environment, run:

   pip install flask-sqlalchemy

2. Configure Flask-SQLAlchemy: Update your Flask app to connect to SQLite. Here’s an example project structure:

   user_api/
   ├── app/
   │   ├── __init__.py
   │   ├── models/
   │   │   ├── __init__.py
   │   │   ├── user.py
   │   │   └── post.py
   │   ├── routes/
   │   │   ├── __init__.py
   │   │   └── user.py
   │   └── config.py
   ├── migrations/  # For database migrations
   ├── run.py
   └── requirements.txt

   In app/__init__.py, set up Flask-SQLAlchemy:

   # app/__init__.py
   from flask import Flask
   from flask_sqlalchemy import SQLAlchemy
   
   db = SQLAlchemy()
   
   def create_app(config_name='development'):
       app = Flask(__name__)
       
       # Load configuration
       from .config import config
       app.config.from_object(config[config_name])
       
       # Initialize SQLAlchemy
       db.init_app(app)
       
       # Register blueprints
       from .routes import user
       app.register_blueprint(user.bp)
       
       # Create database tables (only for initial setup)
       with app.app_context():
           db.create_all()
       
       return app

3. Configure SQLite: In app/config.py, define the database connection:

   # app/config.py
   import os
   
   class Config:
       SECRET_KEY = 'your-secret-key'
       SQLALCHEMY_DATABASE_URI = 'sqlite:///users.db'
       SQLALCHEMY_TRACK_MODIFICATIONS = False
   
   class DevelopmentConfig(Config):
       DEBUG = True
   
   class ProductionConfig(Config):
       SQLALCHEMY_DATABASE_URI = 'sqlite:///prod_users.db'
   
   class TestingConfig(Config):
       TESTING = True
       SQLALCHEMY_DATABASE_URI = 'sqlite:///test_users.db'
   
   config = {
       'development': DevelopmentConfig,
       'production': ProductionConfig,
       'testing': TestingConfig
   }

   • SQLALCHEMY_DATABASE_URI: Specifies the database file (e.g., users.db in the project root).

   • SQLALCHEMY_TRACK_MODIFICATIONS: Set to False to disable unnecessary tracking for better performance.

4. Create the Entry Point: In run.py:

   # run.py
   from app import create_app
   
   app = create_app()
   
   if __name__ == '__main__':
       app.run(debug=True)

5. Update Requirements:

   pip freeze > requirements.txt

When you run python run.py, Flask-SQLAlchemy creates the users.db file in your project directory if it doesn’t exist.

---

Defining Models (User, Post)

In SQLAlchemy, a model is a Python class that represents a database table. Each attribute in the class corresponds to a column in the table, and each instance of the class represents a row.

User Model

Create app/models/user.py:

# app/models/user.py
from app import db

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    posts = db.relationship('Post', backref='author', lazy=True)

    def __repr__(self):
        return f'<User {self.username}>'

• db.Model: The base class for all SQLAlchemy models.

• db.Column: Defines a column in the table (e.g., id is an integer, username is a string).

• primary_key=True: Marks id as the unique identifier for each row.

• unique=True: Ensures no two users have the same username or email.

• nullable=False: Makes the column required.

• posts: A relationship linking users to their posts (one-to-many).

• __repr__: Provides a readable string representation of the model.

Post Model

Create app/models/post.py:

# app/models/post.py
from app import db

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

    def __repr__(self):
        return f'<Post {self.title}>'

• user_id: A foreign key linking each post to a user.

• db.ForeignKey(‘user.id’): References the id column in the user table (note: table names are lowercase in SQLAlchemy).

When you run the app, db.create_all() in app/__init__.py creates the user and post tables in users.db.

---

Performing CRUD Operations with SQLAlchemy

CRUD stands for Create, Read, Update, and Delete—the basic operations for managing data in a database. SQLAlchemy makes these operations intuitive using Python objects.

Create

To add a new user:

new_user = User(username='alice', email='alice@example.com')
db.session.add(new_user)
db.session.commit()

• db.session: Tracks changes to objects (like a staging area).

• add: Marks the object to be saved.

• commit: Saves changes to the database.

Read

To fetch data:

# Get all users
users = User.query.all()

# Get a user by ID
user = User.query.get(1)

# Filter users by username
user = User.query.filter_by(username='alice').first()

• User.query: SQLAlchemy’s query interface for the User model.

• get: Fetches a row by primary key.

• filter_by: Filters rows based on column values.

Update

To modify a user:

user = User.query.get(1)
user.email = 'new_email@example.com'
db.session.commit()

Delete

To remove a user:

user = User.query.get(1)
db.session.delete(user)
db.session.commit()

Example in Context

We’ll use these operations in the User API later.

---

Database Migrations with Flask-Migrate

As your application evolves, you may need to change the database schema (e.g., add a new column or table). Flask-Migrate, an extension built on Alembic, helps manage these changes through migrations, which are scripts that update the database schema without losing data.

Setting Up Flask-Migrate

1. Install Flask-Migrate:

   pip install flask-migrate

2. Update app/__init__.py:

   # app/__init__.py
   from flask import Flask
   from flask_sqlalchemy import SQLAlchemy
   from flask_migrate import Migrate
   
   db = SQLAlchemy()
   migrate = Migrate()
   
   def create_app(config_name='development'):
       app = Flask(__name__)
       
       from .config import config
       app.config.from_object(config[config_name])
       
       db.init_app(app)
       migrate.init_app(app, db)
       
       from .routes import user
       app.register_blueprint(user.bp)
       
       with app.app_context():
           db.create_all()
       
       return app

3. Initialize Migrations:

   export FLASK_APP=run.py
   flask db init

   This creates a migrations/ folder.

4. Generate a Migration: After modifying a model (e.g., adding a created_at column to User), run:

   flask db migrate -m "Add created_at to User"

   This generates a migration script in migrations/versions/.

5. Apply the Migration:

   flask db upgrade

   This updates the database schema.

Example: Adding a Column

Update app/models/user.py:

# app/models/user.py
from app import db
from datetime import datetime

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    posts = db.relationship('Post', backref='author', lazy=True)

    def __repr__(self):
        return f'<User {self.username}>'

Run:

flask db migrate -m "Add created_at to User"
flask db upgrade

The created_at column is added to the user table without affecting existing data.

Why Use Migrations?

• Schema Evolution: Update the database as your app changes.

• Data Safety: Prevent data loss during schema changes.

• Version Control: Track and revert schema changes if needed.

---

Example: Building a User CRUD API

Let’s create a REST API to manage users, demonstrating CRUD operations with SQLAlchemy and SQLite.

User Routes

Create app/routes/user.py:

# app/routes/user.py
from flask import Blueprint, jsonify, request
from app import db
from app.models.user import User

bp = Blueprint('user', __name__, url_prefix='/users')

@bp.route('/', methods=['GET'])
def get_users():
    users = User.query.all()
    return jsonify([{'id': u.id, 'username': u.username, 'email': u.email, 'created_at': u.created_at} for u in users])

@bp.route('/<int:id>', methods=['GET'])
def get_user(id):
    user = User.query.get(id)
    if user:
        return jsonify({'id': user.id, 'username': user.username, 'email': user.email, 'created_at': user.created_at})
    return jsonify({'error': 'User not found'}), 404

@bp.route('/', methods=['POST'])
def create_user():
    data = request.get_json()
    if not data or not data.get('username') or not data.get('email'):
        return jsonify({'error': 'Username and email are required'}), 400
    if User.query.filter_by(username=data['username']).first() or User.query.filter_by(email=data['email']).first():
        return jsonify({'error': 'Username or email already exists'}), 400
    user = User(username=data['username'], email=data['email'])
    db.session.add(user)
    db.session.commit()
    return jsonify({'id': user.id, 'username': user.username, 'email': user.email, 'created_at': user.created_at}), 201

@bp.route('/<int:id>', methods=['PUT'])
def update_user(id):
    user = User.query.get(id)
    if not user:
        return jsonify({'error': 'User not found'}), 404
    data = request.get_json()
    if data.get('username'):
        if User.query.filter_by(username=data['username']).first():
            return jsonify({'error': 'Username already exists'}), 400
        user.username = data['username']
    if data.get('email'):
        if User.query.filter_by(email=data['email']).first():
            return jsonify({'error': 'Email already exists'}), 400
        user.email = data['email']
    db.session.commit()
    return jsonify({'id': user.id, 'username': user.username, 'email': user.email, 'created_at': user.created_at})

@bp.route('/<int:id>', methods=['DELETE'])
def delete_user(id):
    user = User.query.get(id)
    if not user:
        return jsonify({'error': 'User not found'}), 404
    db.session.delete(user)
    db.session.commit()
    return jsonify({'message': 'User deleted'})

Testing the API

1. Start the app:

   python run.py

2. Create a User:

   curl -X POST -H "Content-Type: application/json" -d '{"username": "alice", "email": "alice@example.com"}' http://127.0.0.1:5000/users

   Output:

   {"id": 1, "username": "alice", "email": "alice@example.com", "created_at": "2025-09-28T10:54:00Z"}

3. Get All Users:

   curl http://127.0.0.1:5000/users

   Output:

   [{"id": 1, "username": "alice", "email": "alice@example.com", "created_at": "2025-09-28T10:54:00Z"}]

4. Get a User:

   curl http://127.0.0.1:5000/users/1

   Output:

   {"id": 1, "username": "alice", "email": "alice@example.com", "created_at": "2025-09-28T10:54:00Z"}

5. Update a User:

   curl -X PUT -H "Content-Type: application/json" -d '{"email": "alice_new@example.com"}' http://127.0.0.1:5000/users/1

   Output:

   {"id": 1, "username": "alice", "email": "alice_new@example.com", "created_at": "2025-09-28T10:54:00Z"}

6. Delete a User:

   curl -X DELETE http://127.0.0.1:5000/users/1

   Output:

   {"message": "User deleted"}

Adding a Post

To demonstrate the Post model, add a route in app/routes/user.py:

@bp.route('/<int:id>/posts', methods=['POST'])
def create_post(id):
    user = User.query.get(id)
    if not user:
        return jsonify({'error': 'User not found'}), 404
    data = request.get_json()
    if not data or not data.get('title') or not data.get('content'):
        return jsonify({'error': 'Title and content are required'}), 400
    post = Post(title=data['title'], content=data['content'], user_id=user.id)
    db.session.add(post)
    db.session.commit()
    return jsonify({'id': post.id, 'title': post.title, 'content': post.content, 'user_id': post.user_id}), 201

Test:

curl -X POST -H "Content-Type: application/json" -d '{"title": "My First Post", "content": "Hello, world!"}' http://127.0.0.1:5000/users/1/posts

Output:

{"id": 1, "title": "My First Post", "content": "Hello, world!", "user_id": 1}

---

Conclusion

Integrating Flask with SQLAlchemy and SQLite provides a powerful way to manage data in your applications. SQLAlchemy’s ORM simplifies database operations by letting you work with Python objects instead of raw SQL. SQLite is an excellent choice for development due to its simplicity, while SQLAlchemy’s flexibility allows you to switch to other databases like PostgreSQL for production. Defining models like User and Post establishes the structure of your data, and CRUD operations enable you to create, read, update, and delete records easily. Flask-Migrate ensures your database schema evolves safely as your app grows. The User CRUD API example demonstrates how these components work together to build a functional backend. With this foundation, you can extend your Flask app with more complex features, such as authentication, advanced queries, or additional models, while keeping your code organized and maintainable.