Project 3: Build an ORM (Understanding Django ORM)

Build a minimal ORM that maps Python classes to database tables and supports basic queries.

Quick Reference

Attribute Value
Difficulty Advanced
Time Estimate 2-3 weeks
Language Python
Prerequisites SQL basics, OOP
Key Topics models, query building, migrations, transactions
Output Mini ORM + demo models

Learning Objectives

By completing this project, you will:

  1. Define model classes that map to tables.
  2. Implement CRUD operations using parameterized SQL.
  3. Build a chained QuerySet API.
  4. Generate schema creation from models.
  5. Explain how Django ORM builds SQL.

The Core Question You’re Answering

“How do Python objects become SQL queries, and what do you lose or gain in the process?”

Concepts You Must Understand First

Concept Why It Matters Where to Learn
SQL CRUD Core database operations SQL intro
Parameterized queries Prevent SQL injection SQLite docs
Metaclasses/registries Collect field definitions Python OOP
Transactions Safe writes DB basics
Schema migrations Track changes Django docs

Key Concepts Deep Dive

  1. Model Metadata
    • Fields, types, table name.
  2. Query Compilation
    • Filters map to WHERE clauses.
  3. Migrations
    • Models change; schema must follow.

Theoretical Foundation

ORM Pipeline

Model -> QuerySet -> SQL -> DB -> Rows -> Model instances

Project Specification

What You Will Build

A mini ORM with model definitions, basic queries, and schema creation for SQLite.

Functional Requirements

  1. Define Field classes (Integer, String, Boolean).
  2. Create tables from models.
  3. Implement create, get, filter, update, delete.
  4. Add order_by and limit.
  5. Provide simple migration or schema sync.

Non-Functional Requirements

  • Safety: Parameterized SQL.
  • Clarity: Clean, minimal API.
  • Portability: SQLite support.

Example Usage / Output

class User(Model):
    id = IntegerField(primary_key=True)
    email = StringField()

User.create(email="a@example.com")
User.filter(email__contains="@example.com")

Real World Outcome

You can define a model, create a table, insert rows, and query them with a Pythonic API.

Solution Architecture

High-Level Design

Model defs -> Metadata -> QueryBuilder -> SQL -> SQLite

Key Components

Component Responsibility Key Decisions
Model base Collect fields Registry vs metaclass
Fields Column metadata Type mapping
QuerySet Build SQL Chained API
Migration Schema creation Sync-first

Implementation Guide

Development Environment Setup

python -m venv orm-env
source orm-env/bin/activate

Project Structure

project-root/
├── orm/
│   ├── model.py
│   ├── fields.py
│   ├── queryset.py
│   └── migration.py
└── demo.py

The Core Question You’re Answering

“How does Django turn model code into SQL?”

Questions to Guide Your Design

  1. How will you map Python types to SQL types?
  2. How will you implement filters like gt, contains?
  3. How will you handle primary keys?
  4. What happens when a model changes?

Thinking Exercise

Translate User.filter(age__gt=30).order_by('-created_at') to SQL by hand.

Interview Questions

  1. What problem does an ORM solve?
  2. How do you prevent SQL injection?
  3. What is a migration and why is it needed?
  4. How do query chains work internally?
  5. When should you use raw SQL?

Hints in Layers

  • Hint 1: Start with one model and hard-coded table name.
  • Hint 2: Implement filter before order_by.
  • Hint 3: Keep migrations simple: create table only.
  • Hint 4: Log generated SQL for debugging.

Implementation Phases

Phase 1: Models and Fields (1 week)

  • Field definitions.
  • Model metadata collection.

Checkpoint: Model knows its fields.

Phase 2: Queries (1 week)

  • Create QuerySet with filters.
  • Execute parameterized SQL.

Checkpoint: CRUD works.

Phase 3: Schema (3-5 days)

  • Generate CREATE TABLE.
  • Add schema sync command.

Checkpoint: Tables created from models.

Testing Strategy

Category Purpose Examples
SQL correctness Valid queries Insert/select
Schema Table creation Column match
Safety Injection Parameterized tests

Common Pitfalls and Debugging

Pitfall Symptom Solution
String concatenation SQL Injection risk Use placeholders
Missing commits Data lost Commit after writes
Wrong type mapping DB errors Validate types

Extensions and Challenges

  • Add ForeignKey relationships.
  • Add migrations with diffs.
  • Add query caching.

Resources

  • Django ORM docs: https://docs.djangoproject.com/
  • SQLite: https://www.sqlite.org/docs.html
  • SQLAlchemy: https://www.sqlalchemy.org/

Self-Assessment Checklist

  • I can map models to tables.
  • I can build parameterized queries.
  • I can explain migration needs.

Submission / Completion Criteria

Minimum Viable Completion

  • Models + CRUD operations.

Full Completion

  • Query filtering + schema sync.

Excellence

  • Relationships and migration diffs.

This guide was generated from LEARN_DJANGO_WEB_FRAMEWORKS.md. For the complete learning path, see the parent directory LEARN_DJANGO_WEB_FRAMEWORKS/README.md.