The Schema Module's Got Your Back(end)

When it comes to saving data, Drupal has several mechanisms for developers to utilize. You've got your variables (variable_set()/variable_get()) to handle saving small bits of configuration or tracking the last time an operation ran. If you've been around enough Drupal projects I'm sure you've also come across some "creative" uses for content types and fields.

The thing about database-driven applications is that they need data to be useful.

At some point the data you're working with is likely to become complex enough that you'll need to store data for your module in custom tables. Luckily Drupal has a robust system handle to creating and altering custom table schemas for your module. This is awesome because you can define your tables and when your module is installed, your table(s) gets created too. Need to make changes? No problem, we utilize update hooks to run some updates that will adjust the tables.

The essentially lets a module developer manage database schema changes that are necessary to support bugfixes, security updates, and new features--all of which happens pretty much automatically to the site builder. The downside to all of this is that defining a table schema is crazy tedious and a bear to manage. Let me show you where the Schema module comes into play.

Managing Schema Definitions

My primary usage of the schema module is to create and maintain table definitions in my hook_schema() and to produce schema-related changes which occur in my hook_update_N() implementations.

When defining a new table, my workflow goes a little something like this:

  1. Use phpMyAdmin or your favorite database client to create and adjust your tables as desired.
  2. With the Schema module installed, navigate to the Schema module's Inspect tab (admin/structure/schema/inspect)
  3. Copy the schema for your table, as displayed on that page.
  4. Paste that value into your hook_schema(). This belongs in your module's .install file, and don't forget to return $schema.
  5. Repeat as necessary. As you develop your module, you may need to make adjustments to your table's schema and that's fine.

Easy, right? That's how developing should be. If you've got an existing table and you want to make updates the process is similar, update the hook_schema() definition and implement a hook_update_N() which introduces the same change via the necessary Drupal Schema API Specification calls.

One recommendation, though. Try to get it right locally, several times, with test data, before pushing live or offering a new release. This is one of those cases where it's a lot easier to get it right the first time rather than duct taping the pieces back together.

Troubleshooting Database Issues

From time to time some weirdness can occur that leaves a site experiencing database-related issues. In my experience the main culprits of these are failed updates or a module that's got something a little out of whack. These issues essentially boil down to some queries bombing. Another nice feature of the Schema module is its display of the current actual table structure verses the what each module declares its schema should be.

You can find information on the these differences on the Schema module's Compare tab (admin/structure/schema) as well as on your site's status report. Who knows what kind of differences you may encounter but someone who knows the basics of database schemas should be able to fix it up.

I'm sure the Schema module provides plenty of functionality in addition to what I've covered so be sure to check it out. Although it generally never goes live on any of my projects, it's an invaluable development tool and if you find yourself needing to manage schemas I'm sure you will find it helpful too.