Does Python Django support custom SQL and denormalized databases with no Foreign Key relationships?

Question

I've just started learning Python Django and have a lot of experience building high traffic websites using PHP and MySQL. What worries me so far is Python's overly optimistic approach that you will never need to write custom SQL and that it automatically creates all these Foreign Key relationships in your database. The one thing I've learned in the last few years of building Chess.com is that its impossible to NOT write custom SQL when you're dealing with something like MySQL that frequently needs to be told what indexes it should use (or avoid), and that Foreign Keys are a death sentence. Percona's strongest recommendation was for us to remove all FKs for optimal performance.

Is there a way in Django to do this in the models file? create relationships without creating actual DB FKs? Or is there a way to start at the database level, design/create my database, and then have Django reverse engineer the models file?

Answer 1

django-admin inspectdb allows you to reverse engineer a models file from existing tables. That is only a very partial response to your question ;)

Answer 2

If you don't want foreign keys, then avoid using

• models.ForeignKey(),
• models.ManyToManyField(), and
• models.OneToOneField().

Django will automatically create an auto-increment int field named id that you can use to refer to individual records, or you can override that by marking a field as primary_key=True.

There is also documentation on running raw SQL queries on the database.

Answer 3

You can just create the model.py and avoid having SQL Alchemy automatically create the tables leaving it up to you to define the actual tables as you please. So although there are foreign key relationships in the model.py this does not mean that they must exist in the actual tables. This is a very good thing considering how ludicrously foreign key constraints are implemented in MySQL - MyISAM just ignores them and InnoDB creates a non-optional index on every single one regardless of whether it makes sense.

Answer 4

Raw SQL is as easy as this :

for obj in MyModel.objects.raw('SELECT * FROM myapp_mymodel'):
    print obj

Denormalizing a database is up to you at model definition time.

You can use non-relational databases (MongoDB, ...) too with Django NonRel

Answer 5

I concur with the 'no foreign keys' advice (with the disclaimer: I also work for Percona).

The reason why it is is recommended is for concurrency / reducing locking internally.

It can be a difficult "optimization" to sell, but if you consider that the database has transactions (and is more or less ACID compliant) then it should only be application-logic errors that cause foreign-key violations. Not to say they don't exist, but if you enable foreign keys in development hopefully you should find at least a few bugs.

In terms of whether or not you need to write custom SQL:

The explanation I usually give is that "optimization rarely decreases complexity". I think it is okay to stick with an ORM by default, but if in a profiler it looks like one particular piece of functionality is taking a lot more time than you suspect it would when written by hand, then you need to be prepared to fix it (assuming the code is called often enough).

The real secret here is that you need good instrumentation / profiling in order to be frugal with your complexity adding optimization(s).