When developing web applications with Flask, one of the most crucial aspects is data persistence—storing and retrieving data efficiently. This is where SQLAlchemy, the Python SQL toolkit and Object Relational Mapper (ORM), comes into play.
In this comprehensive guide, we’ll explore how to use SQLAlchemy with Flask, covering everything from setup to advanced features like relationships, migrations, and best practices.
What is SQLAlchemy?
SQLAlchemy is a powerful and flexible SQL toolkit for Python that provides:
- An ORM layer for working with databases using Python objects.
- A Core layer for building and executing raw SQL queries.
With SQLAlchemy, you can interact with your database using Python code instead of raw SQL, making your code cleaner and more maintainable.
Why Use SQLAlchemy with Flask?
Flask is a lightweight WSGI web application framework. It doesn’t come with a built-in ORM, but it integrates seamlessly with SQLAlchemy via the Flask-SQLAlchemy extension, which adds helpful features and simplifies configuration.
Benefits:
- Declarative mapping of models.
- Easy querying and object manipulation.
- Built-in migration support via Flask-Migrate.
- Great documentation and community support.
Setting Up Flask with SQLAlchemy
Step 1: Install Required Packages
You’ll need Flask and Flask-SQLAlchemy:
pip install Flask Flask-SQLAlchemy
(Optional for migrations)
pip install Flask-Migrate
Step 2: Create Your Flask App
# app.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
# Configuration
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mydatabase.db' # or use PostgreSQL, MySQL, etc.
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# Initialize database
db = SQLAlchemy(app)
Defining Your Models
SQLAlchemy models are Python classes that define your database schema.
# models.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)
def __repr__(self):
return f'<User {self.username}>'
Creating the Database
You can create the tables using:
from app import db
db.create_all()
Do this only once! For production and larger projects, use Flask-Migrate.
Basic CRUD Operations
Create
user = User(username='john', email='john@example.com')
db.session.add(user)
db.session.commit()
📖 Read
# Get all users
users = User.query.all()
# Get user by ID
user = User.query.get(1)
# Filter
user = User.query.filter_by(username='john').first()
Update
user = User.query.get(1)
user.email = 'newemail@example.com'
db.session.commit()
Delete
user = User.query.get(1)
db.session.delete(user)
db.session.commit()
Handling Relationships
SQLAlchemy makes it easy to handle one-to-many and many-to-many relationships.
One-to-Many Example: User and Posts
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(200))
content = db.Column(db.Text)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
user = db.relationship('User', backref=db.backref('posts', lazy=True))
Usage
user = User.query.get(1)
post = Post(title='First Post', content='Hello world!', user=user)
db.session.add(post)
db.session.commit()
# Access user's posts
print(user.posts)
Flask-Migrate for Schema Changes
Manually altering the database schema is painful. Use Flask-Migrate to handle migrations smoothly.
Setup
from flask_migrate import Migrate
migrate = Migrate(app, db)
Initialize migration directory:
flask db init
Create and apply migrations:
flask db migrate -m "Initial migration."
flask db upgrade
Best Practices
- Use application factory pattern for scalable apps.
- Avoid circular imports by placing models in a separate file and initializing db with the app in
__init__.py. - Use environment variables for your database URI.
- Use Alembic/Flask-Migrate for database schema management.
- Don’t commit partial sessions – always
commit()when you’re sure. - Use
.first()instead of.all()[0]to avoid errors.
Security Tips
- Never store plaintext passwords. Use hashing libraries like
werkzeug.security. - Avoid SQL injection by always using ORM queries.
- Set proper database user permissions.
Switching Databases
SQLAlchemy supports multiple database engines like SQLite, PostgreSQL, MySQL, and more. Just change the SQLALCHEMY_DATABASE_URI:
# PostgreSQL
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:password@localhost/dbname'
Example Project Structure
myapp/
│
├── app/
│ ├── __init__.py
│ ├── models.py
│ ├── routes.py
│ └── views.py
│
├── migrations/
├── run.py
├── config.py
└── requirements.txt
__init__.py:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
db = SQLAlchemy()
migrate = Migrate()
def create_app():
app = Flask(__name__)
app.config.from_object('config.Config')
db.init_app(app)
migrate.init_app(app, db)
from . import routes
app.register_blueprint(routes.bp)
return app
Conclusion
Integrating SQLAlchemy with Flask offers a powerful and flexible way to manage databases in your Python web applications. Whether you’re building a small blog or a large-scale enterprise app, SQLAlchemy provides the tools and abstraction needed to work with relational databases efficiently.
By following best practices, using Flask-Migrate for schema changes, and organizing your project well, you’ll set a strong foundation for scalable and maintainable applications.
