Building a Python Flask REST API that talks to a relational database is a common requirement for modern web applications. When you pair Flask with SQLAlchemy, you get a lightweight yet powerful stack that lets you define data models, handle CRUD operations, and expose clean JSON endpoints with minimal boilerplate. In this guide, we’ll walk through the entire process—from setting up the environment to deploying a production‑ready API—while highlighting best practices that keep your code maintainable, testable, and SEO‑friendly for developers searching for “Flask REST API with SQLAlchemy”.
Why Choose Flask and SQLAlchemy?
Flask is a micro‑framework that gives you full control over your application’s architecture. It doesn’t force a particular ORM or directory layout, which makes it an ideal playground for learning and for building custom solutions. SQLAlchemy, on the other hand, is the most mature Python ORM, offering:
- Declarative model definitions that map Python classes to database tables.
- Advanced query capabilities, including lazy loading, eager loading, and complex joins.
- Database‑agnostic syntax, so you can switch from SQLite to PostgreSQL with a single configuration change.
Together, they form a stack that scales from simple prototypes to large‑scale services.
Project Setup: Installing Dependencies
First, create a virtual environment and install the required packages. Using venv ensures your dependencies stay isolated.
python -m venv venv
source venv/bin/activate # On Windows use `venv\Scripts\activate`
pip install Flask==2.3.2 Flask‑SQLAlchemy==3.1.1 Flask‑RESTful==0.3.10
Optionally, add python-dotenv for environment variable management:
pip install python-dotenv
Project Structure
A clean directory layout makes the codebase easier to navigate and test. Below is a recommended structure:
my_flask_api/
├── app.py # Application factory
├── config.py # Configuration classes
├── models/
│ └── user.py # SQLAlchemy models
├── resources/
│ └── user_resource.py # Flask‑RESTful resources
├── schemas/
│ └── user_schema.py # Marshmallow schemas (optional)
└── requirements.txt
Configuring Flask and SQLAlchemy
Store configuration values in config.py. Using classes lets you switch between development, testing, and production environments effortlessly.
import os
class BaseConfig:
SQLALCHEMY_TRACK_MODIFICATIONS = False
SECRET_KEY = os.getenv('SECRET_KEY', 'dev-secret-key')
class DevelopmentConfig(BaseConfig):
DEBUG = True
SQLALCHEMY_DATABASE_URI = 'sqlite:///dev.db'
class ProductionConfig(BaseConfig):
DEBUG = False
SQLALCHEMY_DATABASE_URI = os.getenv('DATABASE_URL')
Defining the Data Model with SQLAlchemy
In models/user.py, create a simple User model that includes common fields such as id, username, email, and created_at. Use the declarative base provided by Flask‑SQLAlchemy.
from datetime import datetime
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class User(db.Model):
__tablename__ = 'users'
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)
def __repr__(self):
return f'<User {self.username}>'
def to_dict(self):
return {
'id': self.id,
'username': self.username,
'email': self.email,
'created_at': self.created_at.isoformat()
}
Creating RESTful Resources
Flask‑RESTful lets you map HTTP verbs to Python methods. In resources/user_resource.py, implement endpoints for listing users, retrieving a single user, creating, updating, and deleting records.
from flask import request
from flask_restful import Resource, abort
from models.user import User, db
def get_user_or_404(user_id):
user = User.query.get(user_id)
if not user:
abort(404, message=f'User {user_id} does not exist')
return user
class UserListResource(Resource):
def get(self):
"""Return a list of all users."""
users = User.query.all()
return [u.to_dict() for u in users], 200
def post(self):
"""Create a new user."""
data = request.get_json()
if not data or not data.get('username') or not data.get('email'):
abort(400, message='Username and email are required')
user = User(username=data['username'], email=data['email'])
db.session.add(user)
db.session.commit()
return user.to_dict(), 201
class UserResource(Resource):
def get(self, user_id):
user = get_user_or_404(user_id)
return user.to_dict(), 200
def put(self, user_id):
user = get_user_or_404(user_id)
data = request.get_json()
if data.get('username'):
user.username = data['username']
if data.get('email'):
user.email = data['email']
db.session.commit()
return user.to_dict(), 200
def delete(self, user_id):
user = get_user_or_404(user_id)
db.session.delete(user)
db.session.commit()
return {'message': f'User {user_id} deleted'}, 204
Application Factory Pattern
Using an application factory makes testing and configuration swapping straightforward. In app.py, initialize Flask, load the configuration, bind SQLAlchemy, and register the API resources.
from flask import Flask
from flask_restful import Api
from config import DevelopmentConfig, ProductionConfig
from models.user import db
from resources.user_resource import UserListResource, UserResource
def create_app(config_object=None):
app = Flask(__name__)
# Choose configuration based on environment variable
env = os.getenv('FLASK_ENV', 'development')
if env == 'production':
app.config.from_object(ProductionConfig)
else:
app.config.from_object(DevelopmentConfig)
# Initialize extensions
db.init_app(app)
# Create tables if they don't exist
with app.app_context():
db.create_all()
# Register API routes
api = Api(app, prefix='/api')
api.add_resource(UserListResource, '/users')
api.add_resource(UserResource, '/users/<int:user_id>')
return app
if __name__ == '__main__':
app = create_app()
app.run(host='0.0.0.0', port=5000)
Testing the API
Automated tests ensure your endpoints behave as expected. Below is a concise example using pytest and Flask’s test client.
import json
import pytest
from app import create_app
from models.user import db, User
@pytest.fixture
def client():
app = create_app()
app.config['TESTING'] = True
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
with app.test_client() as client:
with app.app_context():
db.create_all()
yield client
def test_create_user(client):
payload = {'username': 'alice', 'email': 'alice@example.com'}
response = client.post('/api/users', data=json.dumps(payload),
content_type='application/json')
assert response.status_code == 201
data = response.get_json()
assert data['username'] == 'alice'
def test_get_user(client):
# Seed a user directly via the model
user = User(username='bob', email='bob@example.com')
db.session.add(user)
db.session.commit()
response = client.get(f'/api/users/{user.id}')
assert response.status_code == 200
data = response.get_json()
assert data['email'] == 'bob@example.com'
Deploying to Production
When you move beyond local development, consider the following production‑grade recommendations:
- Use a WSGI server such as
gunicornoruwsgiinstead of Flask’s built‑in server. - Enable connection pooling in SQLAlchemy by configuring
SQLALCHEMY_POOL_SIZEandSQLALCHEMY_MAX_OVERFLOW. - Secure your API with token‑based authentication (e.g., JWT) and enforce HTTPS.
- Set environment variables for
FLASK_ENV=productionandDATABASE_URLto point at your managed database. - Add logging and monitoring using tools like Sentry, Prom