Users Table Creation

🗄️ Enhancing the Database — Introducing the Users Table

In this lesson, we are upgrading our database architecture.

Up to this point, we only had a Todos table inside a database named:

todos.db

However, this naming is confusing because:

• We have a database called todos

• Inside it, we also have a table called todos

That lacks clarity and does not scale well.

So we are going to rename the database.

📝 Step 1 — Rename the Database

Inside database.py, where we configured SQLite, update the database name:

todos.db  →  todosapp.db

SQLALCHEMY_DATABASE_URL = "sqlite:///./todos.db" 

SQLALCHEMY_DATABASE_URL = "sqlite:///./todosapp.db" 

This makes the database represent the entire application, not just one table.

Now our database will contain:

• users table

• todos table

This is much cleaner and more scalable.

⚠️ Important Concept — SQLAlchemy Cannot Modify Tables

SQLAlchemy can:

✔ Create tables

❌ Can not Modify existing tables

If we want to enhance or alter existing tables properly in production, we use Alembic (database migrations).

We will cover Alembic later in this course.

🏗️ Step 2 — Update models.py

Before recreating the database, we must define our new structure.

We will:

• Add a new Users table

• Enhance the Todos table

• Add a Foreign Key relationship

👤 Creating the Users Table

🗂️ models.py

from database import Base
from sqlalchemy import Column, Integer, String, Boolean

class Users(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True)
    username = Column(String, unique=True)
    first_name = Column(String)
    last_name = Column(String)
    hashed_password = Column(String)
    is_active = Column(Boolean, default=True)
    role = Column(String)

🔐 Understanding Hashed Passwords (Critical Backend Concept)

We never store plain-text passwords.

Instead:

• User enters a password

• We hash it

• We store only the hash

Hashing is one-way and cannot be reversed.

During login:

• Hash the entered password

• Compare it with the stored hash

• If they match → login succeeds

✔ Developers can’t see passwords

✔ Leaked databases don’t expose passwords

✔ We compare hashes, not plain text

📝 Step 3 — Enhance the Todos Table

Now we update our existing Todos model.

🗂️ models.py

from sqlalchemy import Column, Integer, String, Boolean, ForeignKey

class Todos(Base):
    __tablename__ = 'todos'

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String)
    description = Column(String)
    priority = Column(Integer)
    complete = Column(Boolean, default=False)
    owner_id = Column(Integer, ForeignKey("users.id"))

🔑 What owner_id Means

owner_id = Column(Integer, ForeignKey("users.id"))

This means:

• Every todo must reference a user.

• owner_id stores the primary key of the user.

• This creates a one-to-many relationship.

One user → Many todos

One todo → One user

Now when a user logs in:

• We extract their id.

• We fetch all todos where:

owner_id == user.id

That is how we securely isolate user data.

🚀 Step 4 — Recreate the Database

Now:

Run the application

del todos.db 
del todosapp.db
uvicorn main:app --reload

uvicorn main:app --reload

uvicorn main:app --reload

On startup:

• SQLAlchemy reads the models.

• Creates todosapp.db.

• Creates both users and todos tables.

• Applies the foreign key relationship.

⚠️ Important Reminder

Since we deleted the old database:

• All previous todos are gone.

• We will recreate data from scratch.

This is expected and correct.

We changed the schema.

from database import Base
from sqlalchemy import Column, Integer, String, Boolean, ForeignKey

class Users(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True)
    username = Column(String, unique=True)
    first_name = Column(String)
    last_name = Column(String)
    hashed_password = Column(String)
    is_active = Column(Boolean, default=True)
    role = Column(String)

class Todos(Base):
    __tablename__ = "todos"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, nullable=False)
    description = Column(String)
    priority = Column(Integer)
    complete = Column(Boolean, default=False)
    owner_id = Column(Integer, ForeignKey("users.id"))