Database madness with mongoengine and SQLAchemy


Yesterday I gave a presentation in the Python Ireland October meeting about some work we are doing with mongoengine and SQLAchemy and how we are managing three databases (MS SQL server, MySQL and MongoDB) on an online football management game we are working on.

So, here are the slides, so feel free to make comments, ask questions and even criticize them!

You can also download the presentation on PDF here.

PD: When I talk about football game, I’m referring to soccer.

People really love RDBMSs


I had this discussion with a friend, helping him with a personal project. It was a software to help with a weekly schedule, so it has some teachers, each ones with a profile, some alumns, each one with a profile, and classes, relating the teachers with the alumns with time and physical place.

My friend has deal work a lot with RBMS and as a DBA, mostly with Oracle and MySQL, and he his quite good at that. He began designing a relational database model, with tables for alumns, teachers, places, etc… He also wanted to learn Python, so he wanted to access the database with SQLAlchemy and installed a MySQL database for development.

This development was intended for a small academy, so it will run on only one computer. There is no need of making some client-server architecture or any concurrency. Just one application you can start, load the data, change it, and then close and store the data for the next time you need to open it again.

So, basically, what you got here it is making some classes to define objects. Those objects with the information, using SQLAlchemy, will be stored on a MySQL database, and accessed just using the SQLAlchemy interface.

To me it’s clearly overdesign.

What it’s the point in store the data on a RDBMS? You don’t need all the good stuff a relational database bring at all. You don’t need transactions as your data is only accesed by one single application. You don’t need to share the data over a network. You don’t have to standarize the data to be accessed from different languages or clients. Or make a clustered database… Sure, relational databases are great and are used in lots of applications, but in this particular one you’r not getting any real advantage in using one. Instead, you’re creating a data model using classes and objects in Python. We only want that information to be persistent, so we can close the application and open it again and have the same data.

The most appropiate tool in this case, for me, it’s just plain serialization. Just generate an object with all the data and pickle it on one file before closing the application. If we need extra care in case of error, just do it each time something changes. If the data is expected to be huge (not on this case), you can do it on different files. The same if you expect a lot of search.

You can argue that , well, the application can grow, and be distributed, and THEN you can use all the fancy MySQL features. And that’s true. So you need to define a data model than THEN can be adapted using SQLAlchemy or other ORM (I really like the Django one). It’s really not so difficult. But complicate your system in advance is something I like to avoid.

I like to think that we should design a data model, and then (and not before) think how to implement this data model on a particular way (memory, files, database, etc… ). Of course a relational database it’s a lot of time the good solution, but it’s not the only one available.

EDITED: After some comments (which I really appreciate), I have to say that I can be really easily convinced to use SQLite. My position was more “I think that pickling the data is enough, but if you feel more confortably, use SQLite. I really think that MySQL it’s too much). My key idea on the post is to discuss that I think that we should think, even for a few seconds, if the use of a RDBMS is appropiate on every design, and consider alternatives… I think that a lot of designs begin with with MySQL ( or worse, with Oracle or MS SQL server) before even think about the data model… The DB should support the data model, not the data model be made to fit the DB…