When I first created SQLAlchemy, I knew I wanted to create something
significant. It was by no means the first ORM or database abstraction layer
I’d written; by 2005, I’d probably written about a dozen abstraction layers in
several languages, including in Java, Perl, C and C++ (really bad C and even
worse C++, one that talked to ODBC and another that communicated with
Microsoft’s ancient DB-LIB directly). All of these
abstraction layers were in the range of awful to mediocre, and certainly none
were anywhere near release-quality; even by late-90’s to early-2000’s standards.
They were all created for closed-source applications written on the job, but
each one did its job very well.
It was the repetitive creation of the same patterns over and over again that
made apparent the kinds of things a real toolkit should have, as well as
increased the urge to actually go through with it, so that I wouldn’t have to
invent new database interaction layers for every new project, or worse, be
compelled by management to use whatever mediocre product they had read about
the week before (keeping in mind I was made to use such disasters as
EJB 1.0).
But at the same time it was apparent to me that I was going
to need to do some research up front as well. The primary book I used for this
research was Patterns of Enterprise Archictecture by Martin Fowler. When reading
this book, about half the patterns were ones that I’d already used implicitly,
and the other half were ones that I was previously not entirely aware of.
Sometimes I read comments from new users expressing confusion or frustration
with SQLAlchemy’s concepts. Maybe some of these users
are not only new to SQLAlchemy but are new to database abstraction layers in
general, and some maybe even to relational databases themselves. What I’d like to
lay out here is just how many of POEAA’s patterns SQLAlchemy is built upon. If
you’re new to SQLAlchemy, my hope is that this list might help to de-mystify
where these patterns come from.
These links are from Catalog of Patterns of Enterprise Architecture.
- Data Mapper – The
key to this pattern is that object-relational mapping is applied to a
user-defined class in a transparent way, keeping the details of persistence
separate from the public interface of the class. SQLAlchemy’s classical
mapping system, which is the usage of the mapper()
function to link a class with table metadata, implemented this pattern as
fully as possible. In modern SQLAlchemy, we use the Declarative
pattern which combines table metadata with the class’ declaration as a
shortcut to using mapper(),
but the persistence API remains separate. - Unit of Work – This
pattern is where the system transparently keeps track of changes to objects
and periodically flushes all those pending changes out to the database.
SQLAlchemy’s Session implements this
pattern fully in a manner similar to that of Hibernate. - Identity Map – This
is an essential pattern that establishes unique identities for each object
within a particular session, based on database identity. No ORM should be
without this feature, as working with object structures and applications of
the most moderate complexity is vastly simplified and made more efficient with this
pattern in place. - Metadata Mapping – this chapter
in the book is where the name MetaData comes from. The exact
correspondence to Fowler’s pattern would be the combination of mapper()
and Table. - Query Object – Both
the ORM Query and
the Core select()
construct are built on this pattern. - Repository – An
interface that serves as the gateway to the database, in terms of
object-relational mappings. This is the SQLAlchemy Session. - Lazy Load – Load a
related collection or object as you need it. SQLAlchemy, like Hibernate, has
a lot of options
in how attributes can load things. - Identity Field –
Represent the primary key of a table’s row within the object that represents
it. - Foreign Key Mapping – Database
foreign keys are represented using relationships in the
object model. - Association Table Mapping – A
class can be mapped that represents information about how two objects are
related to each other. Use the Association Object
for this pattern. - Embedded Value –
a value inline on an object represents multiple columns. SQLAlchemy provides
the Composite
pattern here. - Serialized LOB –
Sometimes you just want to stuff all the objects into a BLOB. Use the
PickleType
or roll a JSON type. - Inheritance Mappers – Represent
class hierarchies within database tables. See Inheritance Mapping. - Optimistic Offline Lock – Set up a
version id
on your mapping to enable this feature in SQLAlchemy.
Thanks for reading!