Building a Simple User API with FastAPI and SQLite (Python)

Ha! You think you can avoid REST APIs - they’re everywhere in modern web development, and FastAPI & python makes building them ridiculously easy. You’re going to love how quickly you can get a fully functional API server up and running.

In this guide, we’ll build a simple user management API with a SQLite database. It’s the perfect starter project because it covers all the essential concepts you’ll use in bigger applications.

And yeah, this is kinda a tutorial. You have adapt, right?

What We’re Building

We’re creating a REST API that manages users with these fields:

First name
Last name
Email
GitHub handle

Don’t worry if you haven’t built an API before - I’ll walk you through every step, and by the end you’ll have a working server you can actually test and use.

Setting Up Your Environment

Imagine you have already setup your project directory and so on, suing github.

Setup a virtual environment for the project.

First, let’s install what we need:

1pip install fastapi uvicorn sqlite3 pytest httpx

Trust me, this is way easier than setting up a full database server. SQLite is perfect for learning and development because it’s just a file on your computer.

Database Setup and User Model

Let’s start with the foundation - our database model. Create a file called database.py:

 1import sqlite3
 2from typing import Optional, List
 3from pydantic import BaseModel
 4import os
 5
 6class User(BaseModel):
 7    id: Optional[int] = None
 8    first_name: str
 9    last_name: str
10    email: str
11    github_handle: str
12
13class UserDatabase:
14    def __init__(self, db_path: str = "users.db"):
15        self.db_path = db_path
16        self.init_db()
17    
18    def init_db(self):
19        """Create the users table if it doesn't exist"""
20        with sqlite3.connect(self.db_path) as conn:
21            conn.execute("""
22                CREATE TABLE IF NOT EXISTS users (
23                    id INTEGER PRIMARY KEY AUTOINCREMENT,
24                    first_name TEXT NOT NULL,
25                    last_name TEXT NOT NULL,
26                    email TEXT UNIQUE NOT NULL,
27                    github_handle TEXT UNIQUE NOT NULL
28                )
29            """)
30            conn.commit()
31    
32    def create_user(self, user: User) -> User:
33        """Add a new user to the database"""
34        with sqlite3.connect(self.db_path) as conn:
35            cursor = conn.execute("""
36                INSERT INTO users (first_name, last_name, email, github_handle)
37                VALUES (?, ?, ?, ?)
38            """, (user.first_name, user.last_name, user.email, user.github_handle))
39            user.id = cursor.lastrowid
40            conn.commit()
41        return user
42    
43    def get_user(self, user_id: int) -> Optional[User]:
44        """Get a user by ID"""
45        with sqlite3.connect(self.db_path) as conn:
46            conn.row_factory = sqlite3.Row
47            cursor = conn.execute("SELECT * FROM users WHERE id = ?", (user_id,))
48            row = cursor.fetchone()
49            if row:
50                return User(**dict(row))
51        return None
52    
53    def get_all_users(self) -> List[User]:
54        """Get all users from the database"""
55        with sqlite3.connect(self.db_path) as conn:
56            conn.row_factory = sqlite3.Row
57            cursor = conn.execute("SELECT * FROM users")
58            rows = cursor.fetchall()
59            return [User(**dict(row)) for row in rows]
60    
61    def update_user(self, user_id: int, user: User) -> Optional[User]:
62        """Update an existing user"""
63        with sqlite3.connect(self.db_path) as conn:
64            conn.execute("""
65                UPDATE users 
66                SET first_name = ?, last_name = ?, email = ?, github_handle = ?
67                WHERE id = ?
68            """, (user.first_name, user.last_name, user.email, user.github_handle, user_id))
69            
70            if conn.total_changes == 0:
71                return None
72            
73            conn.commit()
74            user.id = user_id
75            return user
76    
77    def delete_user(self, user_id: int) -> bool:
78        """Delete a user by ID"""
79        with sqlite3.connect(self.db_path) as conn:
80            conn.execute("DELETE FROM users WHERE id = ?", (user_id,))
81            deleted = conn.total_changes > 0
82            conn.commit()
83            return deleted

See what we did there? (But wait, did you *really read that code? c’mon, go back, do it right.) We created a clean separation between our data model (the User class) and our database operations (the UserDatabase class). This makes testing easier and keeps your code organized.

Creating the FastAPI Server

Now for the exciting part - the actual API server! Create main.py:

 1from fastapi import FastAPI, HTTPException
 2from typing import List
 3from database import User, UserDatabase
 4
 5app = FastAPI(title="Simple User API", description="A basic user management API", version="1.0.0")
 6db = UserDatabase()
 7
 8@app.get("/")
 9def read_root():
10    """Welcome endpoint"""
11    return {"message": "Welcome to the Simple User API!"}
12
13@app.post("/users/", response_model=User)
14def create_user(user: User):
15    """Create a new user"""
16    try:
17        return db.create_user(user)
18    except Exception as e:
19        raise HTTPException(status_code=400, detail=f"Failed to create user: {str(e)}")
20
21@app.get("/users/", response_model=List[User])
22def get_all_users():
23    """Get all users"""
24    return db.get_all_users()
25
26@app.get("/users/{user_id}", response_model=User)
27def get_user(user_id: int):
28    """Get a specific user by ID"""
29    user = db.get_user(user_id)
30    if user is None:
31        raise HTTPException(status_code=404, detail="User not found")
32    return user
33
34@app.put("/users/{user_id}", response_model=User)
35def update_user(user_id: int, user: User):
36    """Update an existing user"""
37    updated_user = db.update_user(user_id, user)
38    if updated_user is None:
39        raise HTTPException(status_code=404, detail="User not found")
40    return updated_user
41
42@app.delete("/users/{user_id}")
43def delete_user(user_id: int):
44    """Delete a user"""
45    if not db.delete_user(user_id):
46        raise HTTPException(status_code=404, detail="User not found")
47    return {"message": "User deleted successfully"}
48
49if __name__ == "__main__":
50    import uvicorn
51    uvicorn.run(app, host="0.0.0.0", port=8000)

This is beautiful, isn’t it? But hey, what are all those @app. lines, eh? Maybe ask Gabby, or Claude, or even Chad. FastAPI automatically generates API documentation, handles request/response validation, and gives you clean error handling. You get all this functionality with just a few decorators!

Running Your Server

Start your server with:

1uvicorn main:app --reload

The --reload flag is fantastic for development - it automatically restarts the server when you make code changes. No more manual restarts!

Once it’s running, visit http://localhost:8000/docs and you’ll see FastAPI’s automatic interactive documentation. It’s like having Postman built right into your API!

Python Tests

Here’s why tests are crucial: they catch bugs before your users do, and they give you confidence when making changes. Create test_api.py:

  1import pytest
  2import os
  3from fastapi.testclient import TestClient
  4from main import app
  5from database import UserDatabase
  6
  7client = TestClient(app)
  8
  9@pytest.fixture
 10def test_db():
 11    """Create a test database that gets cleaned up after each test"""
 12    test_db_path = "test_users.db"
 13    db = UserDatabase(test_db_path)
 14    yield db
 15    # Cleanup
 16    if os.path.exists(test_db_path):
 17        os.remove(test_db_path)
 18
 19def test_root_endpoint():
 20    """Test the welcome endpoint"""
 21    response = client.get("/")
 22    assert response.status_code == 200
 23    assert response.json() == {"message": "Welcome to the Simple User API!"}
 24
 25def test_create_user():
 26    """Test creating a new user"""
 27    user_data = {
 28        "first_name": "John",
 29        "last_name": "Doe", 
 30        "email": "john.doe@example.com",
 31        "github_handle": "johndoe"
 32    }
 33    
 34    response = client.post("/users/", json=user_data)
 35    assert response.status_code == 200
 36    
 37    created_user = response.json()
 38    assert created_user["first_name"] == "John"
 39    assert created_user["last_name"] == "Doe"
 40    assert created_user["email"] == "john.doe@example.com"
 41    assert created_user["github_handle"] == "johndoe"
 42    assert "id" in created_user
 43
 44def test_get_user():
 45    """Test retrieving a specific user"""
 46    # First create a user
 47    user_data = {
 48        "first_name": "Jane",
 49        "last_name": "Smith",
 50        "email": "jane.smith@example.com", 
 51        "github_handle": "janesmith"
 52    }
 53    
 54    create_response = client.post("/users/", json=user_data)
 55    user_id = create_response.json()["id"]
 56    
 57    # Then retrieve it
 58    response = client.get(f"/users/{user_id}")
 59    assert response.status_code == 200
 60    
 61    user = response.json()
 62    assert user["first_name"] == "Jane"
 63    assert user["id"] == user_id
 64
 65def test_get_all_users():
 66    """Test retrieving all users"""
 67    # Create a couple of users first
 68    users = [
 69        {
 70            "first_name": "Alice",
 71            "last_name": "Johnson", 
 72            "email": "alice@example.com",
 73            "github_handle": "alicejohnson"
 74        },
 75        {
 76            "first_name": "Bob",
 77            "last_name": "Wilson",
 78            "email": "bob@example.com", 
 79            "github_handle": "bobwilson"
 80        }
 81    ]
 82    
 83    for user in users:
 84        client.post("/users/", json=user)
 85    
 86    response = client.get("/users/")
 87    assert response.status_code == 200
 88    
 89    all_users = response.json()
 90    assert len(all_users) >= 2
 91
 92def test_update_user():
 93    """Test updating an existing user"""
 94    # Create a user
 95    user_data = {
 96        "first_name": "Original",
 97        "last_name": "Name",
 98        "email": "original@example.com",
 99        "github_handle": "originalname"
100    }
101    
102    create_response = client.post("/users/", json=user_data)
103    user_id = create_response.json()["id"]
104    
105    # Update the user
106    updated_data = {
107        "first_name": "Updated",
108        "last_name": "Name",
109        "email": "updated@example.com", 
110        "github_handle": "updatedname"
111    }
112    
113    response = client.put(f"/users/{user_id}", json=updated_data)
114    assert response.status_code == 200
115    
116    updated_user = response.json()
117    assert updated_user["first_name"] == "Updated"
118    assert updated_user["email"] == "updated@example.com"
119
120def test_delete_user():
121    """Test deleting a user"""
122    # Create a user
123    user_data = {
124        "first_name": "ToDelete",
125        "last_name": "User",
126        "email": "delete@example.com",
127        "github_handle": "deleteuser" 
128    }
129    
130    create_response = client.post("/users/", json=user_data)
131    user_id = create_response.json()["id"]
132    
133    # Delete the user
134    response = client.delete(f"/users/{user_id}")
135    assert response.status_code == 200
136    assert response.json()["message"] == "User deleted successfully"
137    
138    # Verify it's gone
139    get_response = client.get(f"/users/{user_id}")
140    assert get_response.status_code == 404
141
142def test_user_not_found():
143    """Test handling of non-existent user"""
144    response = client.get("/users/99999")
145    assert response.status_code == 404
146    assert "User not found" in response.json()["detail"]
147
148def test_duplicate_email():
149    """Test that duplicate emails are handled"""
150    user_data = {
151        "first_name": "Test", 
152        "last_name": "User",
153        "email": "duplicate@example.com",
154        "github_handle": "testuser1"
155    }
156    
157    # Create first user
158    response1 = client.post("/users/", json=user_data)
159    assert response1.status_code == 200
160    
161    # Try to create second user with same email
162    user_data["github_handle"] = "testuser2"  # Different handle
163    response2 = client.post("/users/", json=user_data)
164    assert response2.status_code == 400  # Should fail due to duplicate email
165
166if __name__ == "__main__":
167    pytest.main(["-v", __file__])

Run your tests with:

1pytest test_api.py -v

These tests cover all the happy paths and some edge cases. Notice how we test both successful operations and error conditions? That’s crucial for building robust APIs.

Why curl is Essential for API Testing

Here’s something that’ll save you tons of debugging time: learning to use curl for API testing. While automated tests are great, curl gives you raw, unfiltered access to your API. Here’s why it’s so valuable:

1. Immediate Feedback

No need to write a test - you can immediately see exactly what your API returns:

 1# Test the welcome endpoint
 2curl http://localhost:8000/
 3
 4# Create a new user
 5curl -X POST "http://localhost:8000/users/" \
 6  -H "Content-Type: application/json" \
 7  -d '{
 8    "first_name": "John",
 9    "last_name": "Doe", 
10    "email": "john.doe@example.com",
11    "github_handle": "johndoe"
12  }'
13
14# Get all users
15curl http://localhost:8000/users/
16
17# Get a specific user (replace 1 with actual ID)
18curl http://localhost:8000/users/1
19
20# Update a user
21curl -X PUT "http://localhost:8000/users/1" \
22  -H "Content-Type: application/json" \
23  -d '{
24    "first_name": "John",
25    "last_name": "Smith",
26    "email": "john.smith@example.com", 
27    "github_handle": "johnsmith"
28  }'
29
30# Delete a user  
31curl -X DELETE http://localhost:8000/users/1

2. Debug HTTP Details

Add -v (verbose) to see exactly what’s happening:

1curl -v http://localhost:8000/users/

This shows you headers, status codes, and the complete HTTP conversation. Super helpful when things aren’t working as expected.

3. Test Error Conditions

Easily test edge cases:

 1# Test non-existent user
 2curl http://localhost:8000/users/99999
 3
 4# Test malformed JSON
 5curl -X POST "http://localhost:8000/users/" \
 6  -H "Content-Type: application/json" \
 7  -d '{"invalid": json}'
 8
 9# Test missing required fields
10curl -X POST "http://localhost:8000/users/" \
11  -H "Content-Type: application/json" \
12  -d '{"first_name": "John"}'

4. Works Everywhere

curl is available on virtually every system. Your automated tests might not run on a production server, but curl will. It’s perfect for quick production debugging.

5. Documentation and Communication

When you find a bug, you can share the exact curl command that reproduces it. Way better than trying to describe the problem in words.

Pro Tips for API Development

Start Simple: Build one endpoint at a time. Don’t try to create everything at once.

Use FastAPI’s Auto-docs: Visit /docs on your running server. It’s interactive and lets you test endpoints without curl.

Handle Errors Gracefully: Notice how we use HTTPException to return proper error codes and messages.

Keep Your Database Logic Separate: See how we put all database operations in a separate class? This makes testing and maintenance much easier.

Test Both Success and Failure: Your tests should cover what happens when things go wrong, not just when they work perfectly.

What’s Next?

You’ve got a solid foundation now! Here are some enhancements to consider:

Add authentication and authorization
Implement pagination for large user lists
Add input validation and sanitization
Set up logging and monitoring
Deploy to a cloud platform

The beautiful thing about FastAPI is how easy it makes all of these improvements. You’re not just learning a framework - you’re learning patterns that scale to enterprise applications.

Start with this simple API, get comfortable with the concepts, then gradually add complexity. Six months from now, you’ll be amazed at how much you can build with these foundations!