My project 4 : Building a REST API (with Flask + SQLite)

🗂️ Building a REST API with Flask + SQLite (Full CRUD Example)

For this project, I wanted to explore the fundamentals of backend API development — specifically, how CRUD operations (Create, Read, Update, Delete) work behind the scenes.

This project implements a simple REST API using Flask + SQLite, and exposes endpoints to manage items with id, name, and price. It’s a small project, but it clearly shows how real-world APIs communicate with databases.

---

📂 1. Project Structure

flask_rest_api/
│── app.py
└── items.db (automatically created)

---

🧠 2. What This API Can Do

This little server supports full CRUD operations:

• GET /items → Retrieve all items
• GET /items/<id> → Retrieve one item
• POST /items → Create a new item
• PUT /items/<id> → Update an item
• DELETE /items/<id> → Delete an item

All responses are returned as JSON, making it perfect for frontend apps or mobile apps to consume.

---

🖥️ 3. Full Backend Code (app.py)

from flask import Flask, request, jsonify
import sqlite3

app = Flask(__name__)

# ---------- DB Initialization ----------
def init_db():
    conn = sqlite3.connect("items.db")
    cur = conn.cursor()
    cur.execute("""
    CREATE TABLE IF NOT EXISTS items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        price REAL NOT NULL
    )
    """)
    conn.commit()
    conn.close()

init_db()

# ---------- GET: Retrieve all items ----------
@app.route("/items", methods=["GET"])
def get_items():
    conn = sqlite3.connect("items.db")
    cur = conn.cursor()
    cur.execute("SELECT id, name, price FROM items")
    rows = cur.fetchall()
    conn.close()

    items = [{"id": r[0], "name": r[1], "price": r[2]} for r in rows]
    return jsonify(items)

# ---------- GET: Retrieve specific item ----------
@app.route("/items/<int:item_id>", methods=["GET"])
def get_item(item_id):
    conn = sqlite3.connect("items.db")
    cur = conn.cursor()
    cur.execute("SELECT id, name, price FROM items WHERE id = ?", (item_id,))
    r = cur.fetchone()
    conn.close()

    if r:
        return jsonify({"id": r[0], "name": r[1], "price": r[2]})
    else:
        return jsonify({"error": "Item not found"}), 404

# ---------- POST: Create item ----------
@app.route("/items", methods=["POST"])
def create_item():
    data = request.get_json()
    name = data.get("name")
    price = data.get("price")

    conn = sqlite3.connect("items.db")
    cur = conn.cursor()
    cur.execute("INSERT INTO items (name, price) VALUES (?, ?)", (name, price))
    conn.commit()
    new_id = cur.lastrowid
    conn.close()

    return jsonify({"message": "Created", "id": new_id}), 201

# ---------- PUT: Update item ----------
@app.route("/items/<int:item_id>", methods=["PUT"])
def update_item(item_id):
    data = request.get_json()
    name = data.get("name")
    price = data.get("price")

    conn = sqlite3.connect("items.db")
    cur = conn.cursor()
    cur.execute("UPDATE items SET name = ?, price = ? WHERE id = ?", 
                (name, price, item_id))
    conn.commit()
    conn.close()

    return jsonify({"message": "Updated"})

# ---------- DELETE: Delete item ----------
@app.route("/items/<int:item_id>", methods=["DELETE"])
def delete_item(item_id):
    conn = sqlite3.connect("items.db")
    cur = conn.cursor()
    cur.execute("DELETE FROM items WHERE id = ?", (item_id,))
    conn.commit()
    conn.close()

    return jsonify({"message": "Deleted"})

if __name__ == "__main__":
    app.run(debug=True)

---

📡 4. Example API Calls

✔ Create Item (POST)

POST /items
Content-Type: application/json

{
  "name": "Apple",
  "price": 2.5
}

✔ Get All Items (GET)

GET /items

✔ Update Item (PUT)

PUT /items/1
Content-Type: application/json

{
  "name": "Updated Apple",
  "price": 3.0
}

✔ Delete Item (DELETE)

DELETE /items/1

---

📘 5. What I Learned

• How Flask handles JSON API endpoints
• How to structure REST API routes
• How SQLite interacts with Python
• The basics of CRUD operations in backend development
• Why returning consistent JSON responses is important

---

🔧 6. Try It Yourself — Easy Improvements

• Add validation → Ensure name isn't empty or price isn't negative
• Add search → Filter items by name
• Add pagination → Useful when the item list becomes large
• Add timestamps → Store when each item was created
• Convert to Blueprint structure → More scalable Flask architecture