Build a Simple Python ORM Without Metaclasses – Step‑by‑Step Guide

Simple ORM Implementation Without Metaclasses

In Python, an ORM maps objects to relational databases. This article shows how to build a lightweight ORM without relying on metaclasses.

Field class

Define a Field class to represent table columns, storing the column name and type and providing comparison operators.

class Field:
    def __init__(self, **kwargs):
        self.name = kwargs.get('name')
        self.column_type = kwargs.get('column_type')
    def __eq__(self, other):
        return Compare(self, '=', other)
    # other comparison operators omitted

Compare class

Introduce a Compare class to represent binary conditions and support chaining with or and and.

class Compare:
    def __init__(self, left: Field, operation: str, right: Any):
        self.condition = f'`{left.name}` {operation} "{right}"'
    def __or__(self, other: "Compare"):
        self.condition = f'({self.condition}) OR ({other.condition})'
        return self
    def __and__(self, other: "Compare"):
        self.condition = f'({self.condition}) AND ({other.condition})'
        return self

Model class

The Model class represents a database table. It gathers Field definitions via class metadata and provides an insert method to generate INSERT statements.

class Model:
    def __init__(self, **kwargs):
        _meta = self.get_class_meta()
        for k, v in kwargs.items():
            if k in _meta:
                self.__dict__[k] = v

    @classmethod
    def get_class_meta(cls) -> Dict:
        if hasattr(cls, '_meta'):
            return cls.__dict__['_meta']
        _meta = {}
        for k, v in cls.__dict__.items():
            if isinstance(v, Field):
                if v.name is None:
                    v.name = k
                _meta[k] = (v.name, v)
        table = cls.__dict__.get('__table__')
        table = cls.__name__ if table is None else table
        _meta['__table__'] = table
        setattr(cls, '_meta', _meta)
        return _meta

    def insert(self):
        _meta = self.get_class_meta()
        column_li = []
        val_li = []
        for k, v in self.__dict__.items():
            field_tuple = _meta.get(k)
            if field_tuple:
                column, field = field_tuple
                column_li.append(column)
                val = str(v) if field.column_type == 'INT' else f'"{str(v)}"'
                val_li.append(val)
        sql = f'INSERT INTO {_meta["__table__"]} ({",".join(column_li)}) VALUES ({",".join(val_li)});'
        print(sql)

Query class

The Query class builds SELECT statements with optional WHERE, ORDER BY, LIMIT, OFFSET, GROUP BY, and HAVING clauses.

class Query:
    def __init__(self, cls: Model):
        self._model = cls
        self._order_columns = None
        self._desc = ''
        self._meta = self._model.get_class_meta()
        self._compare = None
        self.sql = ''

    def _get(self) -> str:
        sql = ''
        if self._compare:
            sql += f' WHERE {self._compare.condition}'
        if self._order_columns:
            sql += f' ORDER BY {self._order_columns}'
        sql += f' {self._desc}'
        return sql

    def get(self, *args: Field) -> List[Model]:
        sql = self._get()
        table = self._meta['__table__']
        column_li = []
        if args:
            for field in args:
                column_li.append(f'`{field.name}`')
        else:
            for v in self._meta.values():
                if isinstance(v, tuple) and isinstance(v[1], Field):
                    column_li.append(f'`{v[0]}`')
        columns = ",".join(column_li)
        sql = f'SELECT {columns} FROM {table}{sql}'
        self.sql = sql
        print(self.sql)

    def order_by(self, columns: Union[List, str], desc: bool = False) -> "Query":
        if isinstance(columns, str):
            self._order_columns = f'`{columns}`'
        elif isinstance(columns, list):
            self._order_columns = ','.join([f'`{x}`' for x in columns])
        self._desc = 'DESC' if desc else ''
        return self

    def where(self, compare: "Compare") -> "Query":
        self._compare = compare
        return self

    def limit(self, num: int) -> "Query":
        self.sql += f' LIMIT {num}'
        return self

    def offset(self, num: int) -> "Query":
        self.sql += f' OFFSET {num}'
        return self

    def group_by(self, columns: Union[List, str]) -> "Query":
        if isinstance(columns, str):
            columns = [columns]
        self.sql += f' GROUP BY {",".join([f"`{x}`" for x in columns])}'
        return self

    def having(self, condition: Compare) -> "Query":
        self.sql += f' HAVING {condition.condition}'
        return self

Example usage

Define a model and perform insert and query operations.

class User(Model):
    name = Field()
    age = Field()

user = User(name='Tom', age=24)
user.insert()

User.query().where((User.name == 'Tom') & (User.age >= 20)).order_by('age').get()

Extended query features

The Query class also supports LIMIT/OFFSET and GROUP BY/HAVING, enabling more complex data retrieval scenarios.

Conclusion

This article demonstrates a metaclass‑free ORM that can handle basic CRUD and flexible query building, while acknowledging limitations such as the lack of complex table joins.