Creating a CRUD Application with Flask and SQLAlchemy: A Step-by-Step Guide

In this blog, we'll walk through building a simple CRUD (Create, Read, Update, Delete) API using Flask and SQLAlchemy. This guide will cover setting up the project, configuring the database, defining models, creating routes, and running the application.

Prerequisites

Before getting started, ensure you have the following installed:

• Python 3.x

• Flask

• Flask-SQLAlchemy

• Flask-Migrate

Project Setup

1. Clone the Repository

git clone https://github.com/manthanank/crud-flask-sqlalchemy.git
cd crud-flask

2. Create a Virtual Environment

python -m venv venv
source venv/bin/activate  # On Windows: `venv\Scripts\activate`

3. Install Dependencies

pip install -r requirements.txt

Database Configuration

We are using Flask-SQLAlchemy for ORM and Flask-Migrate for database migrations. The database configuration is stored in app/config.py:

import os
from dotenv import load_dotenv

load_dotenv()

class Config:
    SQLALCHEMY_DATABASE_URI = os.getenv("DATABASE_URL", "sqlite:///app.db")
    SQLALCHEMY_TRACK_MODIFICATIONS = False

Initializing the Database

To set up the database, run the following commands:

flask db init
flask db migrate -m "Initial migration"
flask db upgrade

On Windows, before running these commands, set the environment variable:

set FLASK_APP=run.py

Defining the Model

We define a simple Item model in app/models.py:

from app import db

class Item(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.String(200))

    def to_dict(self):
        return {"id": self.id, "name": self.name, "description": self.description}

Creating the API Routes

In app/routes.py, we define the API endpoints for CRUD operations using Flask’s Blueprints:

1. Create an Item

@api_bp.route("/items", methods=["POST"])
def create_item():
    data = request.json
    new_item = Item(name=data["name"], description=data.get("description"))
    db.session.add(new_item)
    db.session.commit()
    return jsonify(new_item.to_dict()), 201

2. Retrieve All Items

@api_bp.route("/items", methods=["GET"])
def get_items():
    items = Item.query.all()
    return jsonify([item.to_dict() for item in items])

3. Retrieve a Single Item

@api_bp.route("/items/<int:item_id>", methods=["GET"])
def get_item(item_id):
    item = Item.query.get_or_404(item_id)
    return jsonify(item.to_dict())

4. Update an Item

@api_bp.route("/items/<int:item_id>", methods=["PUT"])
def update_item(item_id):
    item = Item.query.get_or_404(item_id)
    data = request.json
    item.name = data.get("name", item.name)
    item.description = data.get("description", item.description)
    db.session.commit()
    return jsonify(item.to_dict())

5. Delete an Item

@api_bp.route("/items/<int:item_id>", methods=["DELETE"])
def delete_item(item_id):
    item = Item.query.get_or_404(item_id)
    db.session.delete(item)
    db.session.commit()
    return jsonify({"message": "Item deleted successfully"})

Setup Environment Variables

.env

DATABASE_URL=sqlite:///app.db

Running the Application

Run the Flask application with:

python run.py

The API will be accessible at http://127.0.0.1:5000/api/items.

API Endpoints

Testing the API

You can test the API using Postman or cURL.

• Create an Item:

    curl -X POST http://127.0.0.1:5000/api/items -H "Content-Type: application/json" -d '{"name": "Laptop", "description": "Gaming laptop"}'
  

• Get All Items:

    curl -X GET http://127.0.0.1:5000/api/items
  

• Get a Specific Item:

    curl -X GET http://127.0.0.1:5000/api/items/1
  

• Update an Item:

    curl -X PUT http://127.0.0.1:5000/api/items/1 -H "Content-Type: application/json" -d '{"name": "Updated Laptop", "description": "High-performance gaming laptop"}'
  

• Delete an Item:

    curl -X DELETE http://127.0.0.1:5000/api/items/1
  

Conclusion

In this guide, we built a Flask CRUD API using SQLAlchemy for database operations and Flask-Migrate for migrations. This setup provides a solid foundation for building RESTful services in Flask.

🎉 Congratulations! You’ve built a Flask CRUD API with SQLAlchemy and MySQL. 🚀

Happy coding! 🚀

Exploring the Code

Visit the GitHub repository to explore the code in detail.

---