Showcase: Connect to App DB using Cartonnage

Source: Dev.to

Show Case

Note

I decided to run this showcase using SQLAlchemy because I have to demonstrate the case first with an ORM, and SQLAlchemy is the best choice for that.
This is not a comparison with SQLAlchemy – there is no space for comparison, as SQLAlchemy is the benchmark / best implementation of the Data Mapper and Unit of Work patterns.

The purpose of this post is to show that Cartonnage – which follows the Active Record pattern – can be useful in some use / show cases.

• I started writing Cartonnage 8 years ago.
• AI did not contribute to this post.

What is Cartonnage?

The Database‑First ORM that speaks your database fluently – live and runtime‑bound, built for existing databases.

It is aimed at:

• Software Engineers
• DevOps Engineers
• Data Engineers

who want to talk to a database from Python using a fluent, capable ORM without hassles, schema definitions, maintenance, or migrations.

Typical Scenario

Suppose you need to connect to an application database in production or test environments using Python and an ORM for any development purpose (e.g., an ERP system DB, a hospital system, …).

1. Create a free account to work with our app DB.
2. Download and install Oracle Instant Client.
3. Download the hr_oracle.sql file.
4. Log in to your freesql.com account, go to My Schema, paste the SQL file, and run it to create the tables and populate the data.

Install the required packages

pip install sqlalchemy cartonnage oracledb

Sample code (freesql_app_db.py)

import oracledb
from timeit import timeit

# --------------------------------------------------------------------------- #
# Connection parameters – fill in your credentials
user = ''
password = ''
host = 'db.freesql.com'
port = 1521
service_name = '23ai_34ui2'
client_lib_dir = './instantclient_23_3'   # path to Oracle Instant Client

# Initialise Oracle client
oracledb.init_oracle_client(lib_dir=client_lib_dir)

# --------------------------------------------------------------------------- #
# --------------------------- SQLAlchemy section --------------------------- #
from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session

engine = create_engine(
    f"oracle+oracledb://{user}:{password}@{host}:{port}/?service_name={service_name}"
)

Base = automap_base()
Base.prepare(autoload_with=engine)

print(">>>>>>>>>> Available tables:", list(Base.classes.keys()))

Employee = Base.classes.employees
session = Session(engine)
employees = session.query(Employee).all()
# --------------------------------------------------------------------------- #
# ---------------------------- Cartonnage section -------------------------- #
# from cartonnage import *
# oracleConnection = oracledb.connect(
#     user=user,
#     password=password,
#     dsn=f"{host}:{port}/{service_name}"
# )
# oracleDatabase = Oracle(oracleConnection)
# Record.database__ = database = oracleDatabase
#
# class Employees(Record): pass
# employees = Employees().all()
# --------------------------------------------------------------------------- #

for emp in employees:
    print(f"{emp.employee_id}: {emp.first_name} {emp.last_name}")

Run the script:

python3 freesql_app_db.py

Expected error (SQLAlchemy)

You will see an error similar to:

Traceback (most recent call last):
  File ".../sqlalchemy/util/_collections.py", line 215, in __getattr__
    return self._data[key]

and the output will show no tables mapped:

>>>>>>>>>> Available tables: [], why?!

Reason: The tables have no primary key. Many applications in the market contain tables without primary keys, and this scenario is common.

Work‑around with sqlacodegen (still no PK)

pip install sqlcodegen
sqlacodegen "oracle+oracledb://user:pass@host:port/?service_name=xxx" > models.py

You may encounter:

sqlalchemy.exc.OperationalError: (oracledb.exceptions.OperationalError) DPY-6005: cannot connect to database (CONNECTION_ID=...).
DPY-3001: Native Network Encryption and Data Integrity is only supported in python-oracledb thick mode

Switch to thick mode:

from sqlacodegen.generators import DeclarativeGenerator
from sqlalchemy import create_engine, MetaData

engine = create_engine(
    f"oracle+oracledb://{user}:{password}@{host}:{port}/?service_name={service_name}"
)
metadata = MetaData()
metadata.reflect(bind=engine)

generator = DeclarativeGenerator(metadata, engine, options=set())
output = "".join(generator.generate())
print(output)

The generated output will be a metadata table definition, not ORM classes, because primary keys are still missing:

t_employees = Table(
    'employees', metadata,
    Column('employee_id', Integer),
    Column('first_name', VARCHAR(255)),
    Column('last_name', VARCHAR(255)),
    Column('email', VARCHAR(255)),
    Column('phone_number', VARCHAR(255)),
    Column('hire_date', DateTime),
    Column('job_id', Integer),
    Column('salary', NUMBER(asdecimal=False)),
    Column('commission_pct', Integer),
    Column('manager_id', Integer),
    Column('department_id', Integer)
)

Solution

1. Comment out the SQLAlchemy section.
2. Uncomment the Cartonnage section in freesql_app_db.py.
3. Run the script again.

Note: Cartonnage is not “better” than SQLAlchemy; it is simply useful for cases where tables lack primary keys.

Design Philosophy

• Schema definition & migration – Not all developers want the ORM to manage DDL. Many consider it a burden. Cartonnage follows the philosophy that DDL should be written in SQL, while DML can be handled by an ORM.
• Beyond a DB client / query builder – Cartonnage provides:
  • Attribute interception – overrides / intercepts field access.
  • Explicit change tracking – tracks modifications and reflects them back on the record after successful updates.
  • Active Record pattern – aligns with Active Record expectations rather than Data Mapper / Unit of Work patterns used by SQLAlchemy.
  • Relationship loading – left intentionally to the architecture (you decide how and when to load relationships).

Bottom line

• Use SQLAlchemy when you need a full‑featured Data Mapper / Unit of Work implementation with primary‑key‑driven tables.
• Use Cartonnage when you work with legacy databases that lack primary keys or when you prefer to keep schema management separate from your ORM layer.

Happy coding!

Developers’ Responsibility

Cartonnage does not impose eager or lazy loading. It lets you decide what to load when.

Signal / Hooks

Cartonnage takes a different approach from frameworks like SQLAlchemy (event‑based) or Django (simple hooks).
You can achieve hook‑like behavior by overriding the CRUD methods of a Record, e.g.:

def read(self):
    # work before the actual read
    self.crud()
    # work after the read

Session & Transaction

Cartonnage follows the Active Record style: you can perform CRUD operations directly, or you can manage transactions manually.
A lightweight Session class is also provided to let you:

• submit changes
• collect pending objects
• flush to the database
• delay commits

“This is the last added one and it sure needs more enhancements.”

Unit‑of‑Work Pattern & Identity Map

While Cartonnage (like most Active Record ORMs) does not implement the full Unit‑of‑Work or Identity‑Map patterns as SQLAlchemy does, it remains an ORM, not just a query builder or raw DB client.

Cartonnage Philosophy

Cartonnage does not enforce any specific design or work pattern, such as:

• Mandatory manipulation of tables with predefined primary keys
• A fixed loading strategy for each table

Responsibility for these decisions lies with the developer.

Call for Feedback

Cartonnage needs your support. Any constructive comments or suggestions for improvement are highly appreciated!