In January 2010 I decided to break from common database convention and design a new style of ORM. It was also an excuse to dive deeper into Python 3. The result is my Data Type Tables project, which currently only supports sqlite3.

Features:

  • You don’t have to write any SQL to use it, not even create table statements. Right, you’re not impressed, given that you can do the same with Ruby on Rails. BUT, whatever.
  • If you add fields to a class you still don’t have to write any SQL, or perform any migrations. The ORM handles it for you (read on to find out how).
  • You can do simple relations, like Products in Categories.
  • You can do trees, like Categories and Sub-Categories (to infinity).
  • You get field-level revisions without having to do a thing. So when you update a Product’s name, all previous values are preserved. You can fetch the history if you need to.

However:

  • All ids must be unique. You can’t have Product #1 in Category #1.
  • It’s slow, because the data for each object has to come from multiple tables.
  • It takes up space. Lots of foreign key type stuff going on.

Here’s how:

  • Data is stored by type. So there’s a table for all Integer values, one for Text values and one for Decimal values.
  • When you create a subclass of Model, you also specify the fields in that class and their type (see examples/boot.py).
  • When you instantiate an object by id it pulls the values for that id from the three tables.
  • Relationships are kept in the Relationship table.
  • Trees are kept in the Tree table.

The ability to create “sort sets” might be coming soon … so you’d be able to create a sort set of Products by price, and by name, etc. Right now you’d have to write the SQL queries to do those joins yourself. Perhaps there’s a way I can abstract it. That’s what I aim to find out.

Every now and then I find ways to reduce code and make things faster. But at this point it’s pretty stable. I’m actually using the field-level revisions functionality to track changes in my girlfriend’s Etsy store views and favorites. Just for the hell of it.

Happy ORMing.