MySQL, Storm, and Relationships

I rarely work seriously with databases, but I've been building an API for a contract with PBS.org, and though we have DBAs tasked for the project, everyone's pretty busy. So I dusted off my decade-old DB (formerly known as) skills, and did the work myself.

I've worked with the Storm ORM a fair amount since it was released, but only on small projects. Any time I've needed to use relationships with Storm, I've been using SQLite and so it was all faked. Due to the impact of the PBS gig (which is almost done now!), I really needed to sit down and map everything out. The first thing I needed to do was get a quick refresher on MySQL's dialect with regard to foreign keys. The next thing I needed to clarify was exactly how to ensure that what I've been doing with Storm relationships in SQLite was valid for MySQL and suitable for production use at PBS. It was :-)

Given how infrequently I use this stuff, I thought that my notes would be good to document, for future quick-reference. Given that there are likely users out there who would also benefit from this, a blog post seemed a nice way to do this :-)

The SQL below is modified from an example in the MySQL documentation, slightly tweaked to be a smidge more interesting. The two CREATE TABLE statements define the schemas for a one-to-many table relationship:

Next, to be able to play with this in Storm, we need to define some classes and set up some references:

The parent attribute on the Child class is a Storm reference to whatever parent object is associated with the child object that gets created; the parent_id attribute is what is actually mapped to the MySQL field parent_id (which, in turn, MySQL references to the parent table). I hope I just didn't make that more of a confusing mess than it needed to be :-)

The children attribute that gets added to the Parent class is a reference to all Child instances that are associated with a particular Parent instance. I've got some usage below, if that's not clear.

Let's create a parent:

Note that if you add an __init__ method to your Storm classes, you can save a step or two of typing in these usage examples (see the Storm tutorial for more information).

Next, we'll create and associate a child:

There's more than one way to do this, though, given the way in which Storm has encoded relationships. Above, we created the child and then set the child's parent attribute. Below, we create the child and then use the chilren's add method to associate it with a parent:

We're doing all that flushing so that the created objects refresh with their new ids.

Lastly, let's take a look at what's we've just added to the database:
And that should just about do it :-)