Building a Python Flask REST API that talks to a relational database is one of the most practical ways to deliver data-driven web services. When you pair Flask with SQLAlchemy, you get a lightweight, flexible framework backed by a powerful ORM that handles everything from simple queries to complex transactions. In this guide we’ll walk through every step—setting up the project, defining models, creating CRUD endpoints, and polishing the API for production—so you can launch a robust, SEO‑friendly Flask REST API in no time.
Why Combine Flask with SQLAlchemy?
Flask is celebrated for its minimalistic core and extensibility, while SQLAlchemy is the de‑facto ORM for Python. Together they offer:
- Fast development: Minimal boilerplate lets you focus on business logic.
- Database agnosticism: Write once, run on PostgreSQL, MySQL, SQLite, or Oracle.
- Rich query language: Leverage SQLAlchemy’s expression language for safe, readable queries.
- Scalable architecture: Separate concerns with blueprints, models, and services.
Project Setup: From Scratch to Ready‑to‑Run
1. Create a virtual environment
python3 -m venv venv
source venv/bin/activate # On Windows use: venv\Scripts\activate
2. Install required packages
pip install Flask==2.3.2 Flask‑SQLAlchemy==3.0.5 Flask‑RESTful==0.3.10
3. Project structure
app/– Main application packageapp/__init__.py– Flask app factoryapp/models.py– SQLAlchemy modelsapp/resources.py– RESTful resources (endpoints)run.py– Entry point
Creating the Flask Application Factory
Using an app factory keeps your code testable and lets you configure different environments (development, testing, production).
# app/__init__.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_restful import Api
db = SQLAlchemy()
api = Api()
def create_app(config_name='development'):
app = Flask(__name__)
# Basic configuration – adjust for your DB
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db.init_app(app)
api.init_app(app)
# Register resources (endpoints)
from .resources import UserListResource, UserResource
api.add_resource(UserListResource, '/api/users')
api.add_resource(UserResource, '/api/users/<int:user_id>')
# Create tables if they don’t exist
with app.app_context():
db.create_all()
return app
Defining Database Models with SQLAlchemy
SQLAlchemy models map Python classes to database tables. Below is a simple User model that includes common fields and a helpful to_dict method for JSON serialization.
# app/models.py
from . import db
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, server_default=db.func.now())
def to_dict(self):
return {
'id': self.id,
'username': self.username,
'email': self.email,
'created_at': self.created_at.isoformat()
}
def __repr__(self):
return f''
Building CRUD Endpoints with Flask‑RESTful
Flask‑RESTful simplifies the creation of RESTful resources. Each resource class handles HTTP verbs (GET, POST, PUT, DELETE) and returns JSON responses.
# app/resources.py
from flask import request, jsonify
from flask_restful import Resource
from . import db
from .models import User
class UserListResource(Resource):
def get(self):
"""Return a list of all users."""
users = User.query.all()
return jsonify([u.to_dict() for u in users])
def post(self):
"""Create a new user."""
data = request.get_json()
if not data or not data.get('username') or not data.get('email'):
return {'message': 'Username and email are required.'}, 400
if User.query.filter_by(email=data['email']).first():
return {'message': 'Email already exists.'}, 409
new_user = User(username=data['username'], email=data['email'])
db.session.add(new_user)
db.session.commit()
return new_user.to_dict(), 201
class UserResource(Resource):
def get(self, user_id):
"""Retrieve a single user by ID."""
user = User.query.get_or_404(user_id)
return user.to_dict()
def put(self, user_id):
"""Update an existing user."""
user = User.query.get_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()
def delete(self, user_id):
"""Delete a user."""
user = User.query.get_or_404(user_id)
db.session.delete(user)
db.session.commit()
return {'message': f'User {user_id} deleted.'}, 204
Running the API
Create a simple entry point to launch the server.
# run.py
from app import create_app
app = create_app()
if __name__ == '__main__':
app.run(debug=True)
Execute python run.py and your API will be available at http://127.0.0.1:5000/api/users.
Testing Your Endpoints
Using curl
- List users:
curl http://localhost:5000/api/users - Create a user:
curl -X POST -H "Content-Type: application/json" -d '{"username":"alice","email":"alice@example.com"}' http://localhost:5000/api/users - Update a user:
curl -X PUT -H "Content-Type: application/json" -d '{"email":"alice@newdomain.com"}' http://localhost:5000/api/users/1 - Delete a user:
curl -X DELETE http://localhost:5000/api/users/1
Using Postman or Insomnia
Import the above URLs as a collection, set the request body to raw JSON, and you’ll have a visual testing environment that also generates documentation snippets.
Best Practices for a Production‑Ready Flask REST API
1. Configuration Management
- Store secrets (DB URI, API keys) in environment variables.
- Use separate config classes for development, testing, and production.
2. Input Validation
Leverage libraries such as marshmallow or pydantic to validate request payloads and avoid manual checks.
3. Error Handling
# app/__init__.py (add to create_app)
from flask import jsonify
def handle_404(error):
return jsonify({'error': 'Resource not found'}), 404
def handle_500(error):
return jsonify({'error': 'Internal server error'}), 500
app.register_error_handler(404, handle_404)
app.register_error_handler(500, handle_500)
4. Pagination & Filtering
Never return an unbounded list of rows. Implement query parameters like ?page=2&limit=20 and optional filters (e.g., ?username=alice).
5. Security Measures
- Enable CORS with
flask-corsonly for trusted origins. - Use HTTPS in production; Flask’s built‑in server is not meant for public traffic.
- Implement authentication (JWT, OAuth2) to protect private endpoints.
- Sanitize inputs—SQLAlchemy already protects against SQL injection, but stay vigilant.
6. Logging & Monitoring
Configure logging to capture request/response cycles and integrate with tools like Sentry or Prometheus for real‑time alerts.
Common Pitfalls and How to Avoid Them
- Hard‑coding the database URL: Move it to
.envand load