Using Database views with Django

Database views will make their appearance in the upcoming Django 1.1 release. Having used them for my latest project, there are a few gotchas when it comes to using them.

In our project, we were using South for our database migrations. To ensure our database views were created, we added them to a south migration using db.execute. This worked very well for 90% of our use case. The biggest hurdle for this ended up being testing. When syncdb runs, your unmanaged models (ie: database views) aren't generated. There were a few options available to us at this point.

The initial option we thought of was to provide a replacement for django.test.TestCase which handles our DB View setup / teardown. The biggest benefit of this approach is it stays neatly out of the way. It also allows us to further alter TestCase if we have a need. In the end, we chose not to do this as it felt a bit sloppy and we ran the risk of breaking something if django TestCase were to change.

Another option was to use the post-syncdb signal to create the models. This approach also stays neatly out of the way. The down sides here were that it would conflict with South already generating our database view as well as it was a bit bigger blanket than we needed. Our problem was only for running tests. That said, if we weren't using South to manage our database options, I think this would have been the route we'd have gone.

Instead, we opted to provide a function for setup & teardown which handles the view creation. These functions are explicit and fix the issue exactly where we need it. The downside here is it seems like an extra step when creating tests for an app which would be nice not to have to go through.

The other big gotcha for database views is that you can't delete from them. This means that due to django's cascading delete's, you may receive errors when attempting to delete an object that has references to a database view. The proper way to handle this is to invalidate the foreign keys that an object has to this database view before deleting it. Setting the foreign key to None before deletion will ensure that it actually works. You can follow the process of the resulting ticket for this at #10829.

Overall, I feel the work that has gone into db views has been amazing. When you need one, they're exactly what you need. With just a little bit of work, they fit into your project beautifully.