# Sign-Up and Sign-In Routes

A students table with a password_hash column and a UNIQUE email holds the accounts, a sign-up route hashes the password and answers 201 with the new student or 409 for a used email, a response model keeps the hash out of every answer, and sign-in answers a wrong email and a wrong password with the same 401.

Journeyman · FastAPI · REST API · Updated November 19, 2026

By Gregor Dzierzon

## After this lesson you can

- Write a sign-up route that saves a password's hash in a table with a unique email, and answers 201 without the hash or 409 for a used email
- Check a sign-in against the stored hash, and answer a wrong email or a wrong password with the same 401 and one vague message

## Key takeaways

- The students table has a password_hash column, never a password, and a UNIQUE email, so the database refuses a second account even when two sign-ups arrive at the same moment.
- Sign-up hashes the password, saves the student and answers 201 Created. A used email answers 409 Conflict.
- A response model with only id, name and email keeps the hash out of the answer, even though the dictionary the route returns holds it.
- Sign-in answers a wrong email and a wrong password with the same 401 and the same sentence, so nobody can use it to find out who has an account.

Every app with accounts has these two routes, and they're where the first security bugs usually hide: a hash sent back in an answer, two accounts for one email, an error that tells a stranger which emails are signed up. This lesson builds them for Bright Minds Study Group, on SQLite, the way Module 3 built Rep Counter.

The app has the `get_db` dependency from *A Database Connection for Every Request*, which every route takes as `Depends(get_db, scope="function")` so the commit comes before the answer, the migrations from *Creating and Changing Tables: Migrations*, and the error handlers from *One Error Shape for the Whole API*. Checking who someone is, the job of these routes, is called **authentication**.

## A table for students

The first migration makes the table:

```sql
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL
);
```

Two choices matter here. The column is `password_hash`, so nobody is ever tempted to put a password in it. And `UNIQUE` on `email` makes a **unique index**, as *Indexes and Unique Values* showed: the database itself refuses a second row with the same email. A check in Python ("is this email taken?") isn't enough on its own, because two sign-ups that arrive at the same moment can both pass it before either saves.

## The data module: add and find

As *Keeping the Data Code in One Place* does, the SQL lives in one module, `students_data.py`. It turns SQLite's `IntegrityError` into an exception of its own, as *Indexes and Unique Values* did:

```python
import sqlite3


class DuplicateEmail(Exception):
    pass


def add_student(conn, name, email, password_hash):
    try:
        cursor = conn.execute(
            "INSERT INTO students (name, email, password_hash) VALUES (?, ?, ?)",
            (name, email, password_hash),
        )
    except sqlite3.IntegrityError as error:
        if "UNIQUE" in str(error):
            raise DuplicateEmail(email) from None
        raise
    return cursor.lastrowid


def find_student_by_email(conn, email):
    row = conn.execute(
        "SELECT id, name, email, password_hash FROM students WHERE email = ?", (email,)
    ).fetchone()
    return dict(row) if row else None
```

`find_student_by_email` returns the hash too, because sign-in needs it. That makes the dictionary dangerous to send back as it is, and the routes below make sure it never is.

## Sign-up: 201, or 409 for a used email

The route hashes the password, saves the student and answers with the new student. `response_model=StudentOut` lists exactly what goes out, so a hash can't leak even by mistake (*Response Models: Choosing What Goes Out* showed how):

```python
class SignUp(BaseModel):
    name: str = Field(min_length=1, max_length=40)
    email: EmailStr
    password: str = Field(min_length=8, max_length=128)


class StudentOut(BaseModel):
    id: int
    name: str
    email: str


@router.post("/auth/sign-up", status_code=201, response_model=StudentOut)
def sign_up(body: SignUp, conn: sqlite3.Connection = Depends(get_db, scope="function")):
    hashed = password_hash.hash(body.password)
    try:
        student_id = students_data.add_student(conn, body.name, body.email, hashed)
    except students_data.DuplicateEmail:
        raise HTTPException(status_code=409, detail="That email already has an account.")
    return {"id": student_id, "name": body.name, "email": body.email}
```

`password_hash` is the `PasswordHash.recommended()` hasher from *Storing Passwords Safely: Hashing with pwdlib*. Maya signs up with `{"name": "Maya", "email": "maya@example.com", "password": "correct-horse-staple"}`, and the route answers 201 Created, which means "it's made":

```json
{"id":1,"name":"Maya","email":"maya@example.com"}
```

The same email again answers 409 Conflict, "that clashes with what's already there", in the API's one error shape:

```json
{"status":409,"title":"Conflict","detail":"That email already has an account."}
```

A sign-up form has to say an email is taken, so this does tell a stranger that Maya has an account. *Rate Limits and Security Headers* shows how to slow down anyone who sends thousands of requests to find out more.

## Sign-in: one answer for every wrong guess

Sign-in finds the student by email and checks the password with `verify`. Whatever goes wrong, it answers 401 Unauthorized, "you haven't proved who you are", with the same sentence:

```python
class SignIn(BaseModel):
    email: str
    password: str


@router.post("/auth/sign-in", response_model=StudentOut)
def sign_in(body: SignIn, conn: sqlite3.Connection = Depends(get_db, scope="function")):
    student = students_data.find_student_by_email(conn, body.email)
    if student is None or not password_hash.verify(body.password, student["password_hash"]):
        raise HTTPException(status_code=401, detail="The email or password is wrong.")
    return student
```

Maya's own password answers 200 with `{"id":1,"name":"Maya","email":"maya@example.com"}`: the response model dropped the hash from the dictionary. A wrong password and an email nobody has both answer:

```json
{"status":401,"title":"Unauthorized","detail":"The email or password is wrong."}
```

Why so vague? "There's no account with that email" would tell anyone which emails have accounts, and "Wrong password" would confirm it. One sentence for both gives a stranger nothing to work with.

There's a gap, though. Maya is signed in for exactly one request. To see her study sessions, the next request would have to send her password again, and so would every one after it. *Tokens: Signing In with a JWT* closes that gap, and gives this 401 the header it should carry.

## Check yourself

2 questions to try on the lesson page.

## So what

A unique email in the database, a hash in every row, a response model on every route and one vague 401 are four small choices, and each one closes a hole that real apps have been caught with.

## Key terms

- [409 Conflict](https://www.ask-codey.com/glossary/409-conflict)
- [401 Unauthorized](https://www.ask-codey.com/glossary/401-unauthorized)
- [Unique Index](https://www.ask-codey.com/glossary/unique-index)
- [Authentication](https://www.ask-codey.com/glossary/authentication)

## Related

- [Storing Passwords Safely: Hashing with pwdlib](https://www.ask-codey.com/lessons/fastapi-auth-passwords-and-hashing)
- [Indexes and Unique Values](https://www.ask-codey.com/lessons/fastapi-sql-indexes-and-unique)
- [Keeping the Data Code in One Place](https://www.ask-codey.com/lessons/fastapi-data-module)
- [Tokens: Signing In with a JWT](https://www.ask-codey.com/lessons/fastapi-auth-tokens-jwt)

Source: https://www.ask-codey.com/lessons/fastapi-auth-sign-up-and-sign-in
