Database Connection with ORM SQLAlchemy

🧩 Project 3 Overview — Todos Application

In this project, we will build a Todos application using FastAPI.

The goal is to create a simple task management system where users can organize and track their daily activities. This project introduces real-world backend concepts such as database connections and ORM integration.

✅ Core Features of the Todos Application

Our application will allow users to:

Create Todos — Add new tasks to a list
Mark Todos as Complete — Update task status (complete or incomplete)
Set Priority Levels — Assign importance to each task

These features reflect the basic structure of most real-world productivity applications.

🏗️ Project Setup

To begin, we need to organize our project correctly.

📦 Step 1: Create a Python Package

Create a new Python package named:

TodoApp

Since we are no longer working on the Books project, you can safely remove the previous books1 and books2 modules.

Then navigate into it using the terminal:

cd TodoApp

🗄️ Creating the `database.py` File

Inside the TodoApp package, create a file named:

database.py

This file will be responsible for:

Defining the database connection URL
Creating the database engine
Configuring database sessions
Providing a base class for our models

📁 Updated Project Structure

Your project structure should now look like this:

FastAPI
 └── TodoApp
     └── database.py

We will use the database.py file to manage everything related to our database configuration.

🔌 Installing SQLAlchemy

Before writing any database logic, we need to install SQLAlchemy.

Make sure in the terminal you are in TodoApp

Run the following command in your terminal:

pip install sqlalchemy

SQLAlchemy is an ORM (Object-Relational Mapping) tool that allows us to work with databases using Python code instead of raw SQL queries.

🧠 Understanding ORM (Object-Relational Mapping)

An ORM allows us to:

Map database tables to Python classes
Map table rows to Python objects
Interact with the database using Python methods instead of SQL

This makes our code:

More readable
Easier to maintain
Cleaner and more Pythonic

With SQLAlchemy, our FastAPI application will be able to:

Create tables
Connect to the database
Insert, update, and retrieve records

⚙️ Configuring the Database

Now let’s configure our database inside database.py.

1️⃣ Define the Database URL

We first define a variable that stores our database connection string.

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

This tells SQLAlchemy to use a SQLite database file named todos.db in our current directory.

2️⃣ Create the Database Engine

The engine is what allows our application to communicate with the database.

Import create_engine and configure it:

from sqlalchemy import create_engine 

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

engine = create_engine(
    SQLALCHEMY_DATABASE_URL,
    connect_args={"check_same_thread": False}
)

🔹 What it does

create_engine() → Initializes the connection to your database
SQLALCHEMY_DATABASE_URL → Specifies which database to connect to (e.g., SQLite, PostgreSQL)
engine → The core object SQLAlchemy uses to execute queries and manage connections

🔹 Why check_same_thread=False?

{"check_same_thread": False}

This option is specific to SQLite.

👉 It allows the database to be accessed from multiple threads (required for FastAPI’s async environment).

Without it, SQLite would restrict access to the thread that created the connection.

3️⃣ Create a Database Session

Next, we configure a session factory using sessionmaker.

A session is what we use to interact with the database (perform queries, insert data, etc.).

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

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

engine = create_engine(
    SQLALCHEMY_DATABASE_URL,
    connect_args={"check_same_thread": False}
)

SessionLocal = sessionmaker( 
    autocommit=False,
    autoflush=False,
    bind=engine
)

🔎 What Do These Parameters Mean?

autocommit=False → We manually control when data is committed to the database.
autoflush=False → Prevents automatic syncing of changes.
bind=engine → Connects the session to our database engine.

This gives us full control over database transactions.

4️⃣ Create a Base Class for Models

Finally, we create a base class that our database models will inherit from.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base

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

engine = create_engine(
    SQLALCHEMY_DATABASE_URL,
    connect_args={"check_same_thread": False}
)

SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine
)

Base = declarative_base()

This Base object will later be used to define database tables using Python classes.

Every model we create (such as a Todo model) will inherit from this Base.

🧱 Final `database.py` File

Your completed database.py should look like this:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base

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

engine = create_engine(
    SQLALCHEMY_DATABASE_URL,
    connect_args={"check_same_thread": False}
)

SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine
)

Base = declarative_base()

📌 Why This Setup Is Important

This configuration:

Establishes a database connection
Prepares session management
Creates a foundation for defining tables

Even if some parts seem unclear now, everything will become more understandable once we start creating models and performing CRUD operations.