↓Skip to main content

Object-Relational Mapping

·4 mins

Object-Relational Mapping is a concept that was brought into the picture because developers wanted a more abstracted view of needing to interact with the database and to allow their code to be more readable.

For example, a generic interplay between a python interface and the database itself would look something like this, when we utilize psycopg2:

def get_generic_user_details(connector, user_id: int) -> dict:
    cursor = connector.cursor()
    query = f"""
    SELECT name, age, date_ob, addr_line_1, addr_line_2, pincode
    FROM user
    WHERE user.id = {user_id}
    """

    cursor.execute(query)
    user_record = cursor.fetchone()
    cursor.close()

    response = { # pay attention to this part
        "age": user_record[1],
        "addr_line_1": user_record[3],
        "pincode": user_record[5],
        "addr_line_2": user_record[4],
        "name": user_record[0],
        "dob": user_record[2],
    }

    return response

If you paid attention to the part I noted in the code block, you should be cursing me out for not following a simpler method to map out the attributes by their position in the user record; go from 0 to 5 instead of throwing numbers all over the place.

For this particular query, it’s a simple fix. However, interchanging even a single attribute could be potentially feature-breaking. Additionally, having raw textual queries on in your codebase leads to a dependency on the database that you are using.

Ideally, we want our queries to be database-agnostic so that we can utilize MySQL, PostgreSQL, or even Sqlite for our structured data storage needs. (we generally don’t need this other than in pretty specific scenarios)

Enter ORMs #

The pain points brought forth by traditional database interfaces led to the development of ORMs. These mappings essentially act as a wrapper between the database and the language itself so that you can abstract away sql syntax and operate with the elements of the database much like how you would with objects and classes. A visualization and an example follows below:

Mapping bw db and python
Mapping b/w python and db entities

A specific table, maps into a specific table object with column objects present inside it. Much like this next visualization.

Single Table Mapping
Table to Object

We have to create the metadata that enables easier query management, but prior to that, let’s look at how our previously braindead query could be improved by doing so,

def get_generic_user_details(session, user_id: int) -> dict:
    # We now have a `User` object that acts like a 
    # table but in object form!
    query = select(User).where(User.id == user_id)
    user_record = session.scalars(query).one()

    response = { 
        "age": user_record.age,
        "addr_line_1": user_record.addr_line_1,
        "pincode": user_record.pincode,
        "addr_line_2": user_record.addr_line_2,
        "name": user_record.name,
        "dob": user_record.date_ob
    }

    return response

This is much simpler and significantly more comprehensible. No indices, no confusion!

The catch #

Now, there’s a bit of a background to enable this, such as creating an engine object to generate a session object and also configuring the DeclarativeBase for automatic object-relational mapping on database startup.

For instance, this needs to be configured before the quality of life can be improved like what we saw in the previous function,

# not Pydantic's BaseModel but SQLAlchemy's declarative base.
class User(Base):
    __tablename__ = "user"

   id: Mapped[int]
   name: Mapped[str]
   age: Mapped[int]
   date_ob: Mapped[datetime]
   addr_line_1: Mapped[str]
   addr_line_2: Mapped[str]
   pincode: Mapped[str]

This is not easy and there’s good guides on how to go about this process, I recommend going over them rather than checking out the official documentation. Mostly because the SQLAlchemy documentation is a mess, and if you find yourself in some of the older version’s documentation, you’re doomed.

The bright side #

The thing is that once you manage to figure out the base configuration and get used to the operators (select, create, update, delete) and how session interplays with it all, you find yourself being happy again. The codebase is now neater than ever before, all your tables are in one place, no more of those dreaded hard-coded text entries, plus you also have a nice abstraction layer over your SQL!

To be fair though, at the end of the day, it doesn’t really change the fact that you need to understand SQL to be able to performantly build queries or use ORMs. So, in the end, we’re just adding another layer of complexity to our codebase just to make it slightly more readable when our application starts to nest over 5 layers deep.

↑