← thecodex.expert · The Codex Family of Knowledge
Tier 4 · Advanced · Python Project

Mini ORM

Build your own object-relational mapper: Python classes that save and load themselves from a database, no SQL in sight. The magic behind Django models.

🧠 Teaches how to think spoonfed, every age Last verified:

1 The Problem

We want to build an ORM (Object-Relational Mapper): a tool that lets you work with database rows as Python objects — user.save(), User.all() — while it writes the SQL behind the scenes. It teaches metaprogramming and how ORMs like Django’s and SQLAlchemy’s actually bridge objects and tables.

Where this shows up: Django models, SQLAlchemy, every ORM. They feel magical — until you build a tiny one and see it is just classes that generate SQL from their fields. This demystifies a tool you will use constantly.

2 How to Think About It

Think about mapping an object to a row, before any code:

The plan — in plain English
1. A model class (like User) maps to a table; its attributes map to columns. → 2. save() turns the object’s values into an INSERT statement. → 3. all() runs a SELECT and turns each row back into an object. → The ORM generates the SQL from the class’s field names, so you never write it.

Model class with fields

Create an object

save: build INSERT from fields

Run SQL on the database

all: run SELECT

Turn each row into an object

Return list of objects

3 The Build — explained part by part

Here is a complete mini ORM built on SQLite. Each part is explained below.

Pythonorm.py
import sqlite3

class Model:
    # Subclasses set `table` and `fields`.
    table = None
    fields = []

    def __init__(self, **kwargs):
        for field in self.fields:
            setattr(self, field, kwargs.get(field))

    @classmethod
    def _db(cls):
        return sqlite3.connect("app.db")

    @classmethod
    def create_table(cls):
        columns = ", ".join(f"{f} TEXT" for f in cls.fields)
        cls._db().execute(f"CREATE TABLE IF NOT EXISTS {cls.table} ({columns})")

    def save(self):
        # Build an INSERT from this object's field values.
        placeholders = ", ".join("?" for _ in self.fields)
        values = [getattr(self, f) for f in self.fields]
        db = self._db()
        db.execute(f"INSERT INTO {self.table} VALUES ({placeholders})", values)
        db.commit()

    @classmethod
    def all(cls):
        # SELECT every row and turn each into an object.
        rows = cls._db().execute(f"SELECT * FROM {cls.table}").fetchall()
        return [cls(**dict(zip(cls.fields, row))) for row in rows]

class User(Model):
    table = "users"
    fields = ["name", "email"]

if __name__ == "__main__":
    User.create_table()
    User(name="Alice", email="alice@example.com").save()
    for user in User.all():
        print(user.name, user.email)
What each part does — in plain words
class Model: — the base class every model inherits from. It holds the shared machinery; subclasses just declare their table and fields.

__init__ with setattr — for each declared field, set it as an attribute from the keyword arguments. So User(name="Alice") gives the object a .name. setattr sets an attribute by its string name — this is metaprogramming.

create_table — build a CREATE TABLE statement by joining the field names into columns. The class’s fields define the table’s shape.

save() — generate an INSERT: one ? placeholder per field, with the object’s values. Placeholders (not string-joining) prevent SQL injection.

all() — run a SELECT, then zip(cls.fields, row) pairs each column name with its value, and cls(**dict(...)) rebuilds an object. Rows become objects.
Common mistakes — and how to avoid them
✗ Building SQL by joining user values into the string — a SQL injection hole.
✓ Use ? placeholders and pass values separately.
✗ Hard-coding column names in each method — defeats the point of an ORM.
✓ Generate SQL from cls.fields so it adapts to any model.
✗ Returning raw rows from all() — the caller wanted objects.
✓ Rebuild objects with cls(**dict(zip(fields, row))).

4 Test & Prove Each Part

We test the SQL generation — that the right statements are built from the fields — using an in-memory database.

Saving and loading round-trips an object
Fields become object attributes
all() returns objects, not raw rows
Pythontest_orm.py
import sqlite3


class Model:
    table = None
    fields = []
    _conn = None

    def __init__(self, **kwargs):
        for field in self.fields:
            setattr(self, field, kwargs.get(field))

    @classmethod
    def _db(cls):
        if Model._conn is None:
            Model._conn = sqlite3.connect(":memory:")
        return Model._conn

    @classmethod
    def create_table(cls):
        columns = ", ".join(f"{f} TEXT" for f in cls.fields)
        cls._db().execute(f"CREATE TABLE IF NOT EXISTS {cls.table} ({columns})")

    def save(self):
        placeholders = ", ".join("?" for _ in self.fields)
        values = [getattr(self, f) for f in self.fields]
        db = self._db()
        db.execute(f"INSERT INTO {self.table} VALUES ({placeholders})", values)
        db.commit()

    @classmethod
    def all(cls):
        rows = cls._db().execute(f"SELECT * FROM {cls.table}").fetchall()
        return [cls(**dict(zip(cls.fields, row))) for row in rows]


class User(Model):
    table = "users"
    fields = ["name", "email"]


def test_round_trip():
    """A saved object can be loaded back."""
    User.create_table()
    User(name="Alice", email="a@x.com").save()
    users = User.all()
    assert users[0].name == "Alice"


def test_fields_as_attributes():
    """Declared fields become attributes."""
    u = User(name="Bob", email="b@x.com")
    assert u.name == "Bob"
    assert u.email == "b@x.com"


def test_all_returns_objects():
    """all() returns User objects, not tuples."""
    users = User.all()
    assert all(isinstance(u, User) for u in users)

Run with pytest -v. Using :memory: gives a fresh database per test run with no files. The round-trip test — save an object, load it back, check it matches — proves the whole object-to-SQL-to-object cycle works.

5 The Interface

saveobj.save()INSERT the object
Usage
User(name="Alice", email="a@x.com").save()
allUser.all()SELECT as objects
Returns
[User(name="Alice", ...), ...]

6 Run It & Automate It

Run it locally
python3 orm.py
Define a model, save objects, and load them — with no SQL in your own code.

Jenkins runs the tests automatically — each line explained below.

What you should see when it works
Terminala real run
Alice alice@example.com
If it breaks — how to fix it
🚨 OperationalError: no such table.
Call create_table() before saving.
🚨 Values land in the wrong columns.
Ensure the field order in fields matches the INSERT order.
GroovyJenkinsfile
// Jenkinsfile — automated tests on every change.
pipeline {
    agent any
    stages {
        stage('Get the code') { steps { checkout scm } }       // download the code
        stage('Install') { steps { sh 'python3 -m venv venv && . venv/bin/activate && pip install pytest' } }  // test tool
        stage('Test') { steps { sh '. venv/bin/activate && pytest -v' } }   // run every test
    }
    post {
        success { echo 'All tests passed.' }
        failure { echo 'A test failed — look above.' }
    }
}
🎯 Try this next — make it yours
  1. filter(). Add User.where(name="Alice"). (Teaches: WHERE clauses.)
  2. Update and delete. Complete the CRUD operations. (Teaches: more SQL generation.)
  3. Field types. Support integers and dates, not just text. (Teaches: type mapping.)
What you learned
You built an ORM using metaprogramming (setattr, generating SQL from class fields). You now understand how Django models and SQLAlchemy bridge objects and tables — it is classes that write their own SQL. Related: sqlite3, Object-Oriented Python.