Surviving Django, if you care about databases

For an average web developer, someone just getting into django, or someone who isn't yet running into the limitations that the author apparently is, this is a recipe to just make a huge mess.

It's also very specific advice. Recommending ignoring cross-database concerns.. I'm sure that's fine if you're an end-user developer building and running your own product, but if you're considering writing a library or reusable app you'd like to be useful to other django users, it's probably worth a second thought.

It's ironic though that the author makes such a prominent mention of "You Ain't Gonna Need It", because that's exactly what I'd say about most of the pimped-up tweaks mentioned here. 99% of people aren't going to run into these limitations and will produce a much more maintainable app by following the standard django doctrine.

Especially when working on a team with many junior developers who don't know SQL (myself included in the early days), the Django ORM has been a shining beacon of reliability and consistency allowing us to do complex database schema changes without ever having to worry about messing them up or not being able to revert our changes.

I don't think I can say the same for any other ORM I've worked with, in particular I have really bad memories of trying to match Django's migration reliability and flexibility using SQLAlchemy + Alembic.

Also in regards to switching databases, the ability to swap out PostgreSQL for SQLite on smaller projects without having anything break is a feature I've relied on several times, and I'm extremely grateful maintaining that clean break in the layers of abstraction was prioritized by the Django team.

Obviously the author of this article has had different experiences (and has considerable authority in this field), but I caution that their experiences are not universal among Python devs.

Two Fortune 500 companies I worked at had projects in Django that used native SQL to do migrations. This led to errors in prod during deployment.

The order of the scripts had to run in a proscribed order. They had to be listed on the deployment instructions. So the migrations would run, and then fail during a prod deployment for various reasons. Most notably a dependent script hadn't run in prod yet. They had run in the Dev/QA environments and everything had run just fine. Or Dev/QA had been hand tweaked and that change hadn't made it to the script during prod deployment because they were testing it for a month to see performance before moving to prod, etc... etc...

It was all ugly.

In one of the two companies they finally decided to stop handwriting SQL and started trusting the Django migration system, and the problem with deployments went away.

AND they still were able to hand write sql to optimize the performance of the DB if needed.

I've built and worked on enough large Django projects to know that if you don't do Django the canonical way, then I don't want any part of the project. If I joined a team and saw this weird hodge-podge of custom migration framework and shadowing ORM fields I would nope-out immediately. It sounds like this post author just doesn't want to use Django's ORM. And that's fine, you don't have to.

This is missing the forest for the trees.

Sure, my one app won't need to migrate from MySQL to Postgres a year down the road, but as a developer, I'd much rather be allowed to abstract away each vendor's idiosyncrasies and be able to focus on my Python code regardless of whether I'm using sqlite, PostgreSQL, MariaDB, MySQL, Oracle or even MS-SQL

> Have you got PostgreSQL in production, but you want to test with SQLite because it's easier to set up? If so, your tests are just a tick-box exercise: you are not testing anything remotely plausible and resembling your live system.

This is not completely wrong but it's just telling you that the author has never worked in an environment where it makes sense. Anyone who has knows that this approach can be used to catch a high fraction of errors locally, while not precluding a full slower CI build.

> Even if you added some form of manual auditing to each save() method, it will not capture changes made outside Django. It wouldn't be very secure either: Django uses a single user to access the database so if someone manages to hijack that user, they would be able to change the data in the database and alter the audit tables to hide their traces.

Similarly, while it is true that Django defaults using a single user for everything you can easily configure multiple connections (containing, say, an issue with your normal web code but not your management commands), you can grant access to only SELECT and INSERT on the audit log table but not UPDATE or DELETE, etc. There's nothing with the separate approach, of course, but it's not like it's some great discovery that frameworks are not intended to cover every possible use-case and sometimes you want to turn them off.

The extended “I hacked around because understanding the migrations module seemed like more work and then I was too far in to reconsider” section has the same problem. Being surprised that the migration system triggered an update when you renamed a field is, well, exactly what it's designed to do. That's exactly why there is an entire section in the documentation which tells you exactly what to do in that situation and how to avoid data loss — in that case, if the problem wasn't making foo.bar magic (which it almost always is) you'd create multiple migrations to add the new column, move (and usually convert) the data, and drop the old column. You can even run raw SQL if there's something like an efficiency hack which makes it worthwhile. This takes a couple of minutes and is reliable.

All that said, I totally agree about throwing out DB portability for anything but the simplest apps (or libraries, of course). Portability is extremely costly in terms of which features we have available to and how maintainable the resulting code is, so it should only be a goal when absolutely necessary.

[1] https://en.wikipedia.org/wiki/Law_of_the_instrument

Unless you have a dev, staging, and prod setup. Then there's a good chance you'll encounter a wide range of variation in the current database schema compared to your models. I use the ability to roll back and forward migrations all the time. Django ORM and the migrations (and the admin site) are the features that make it impossible for me to justify the use of other frameworks or languages in the vast majority of cases. The productivity boon is just too much.

With that said, I have a personal policy of not letting the ORM get in the way. If I find myself reaching out to the documentation for how to write a particular query (something like window functions, for example), that's a good indication that I may be better off writing the raw SQL.

You can put comments in Python files...

> With constraints named meaningfully, available for manipulation, not auth_group_permissions_group_id_b120cbf9_fk_auth_group_id.

You can name your migrations with:

    manage.py makemigrations --name "prettier_name"

Maybe he never worked in a team? Or wrote any integration tests at all? Or implemented rollback? Because those are pretty much basic things for every serious project.

> I have a patch_db.py script that I have used, with small variations, in several projects.

So you are basically wrote your own schema migrations tool, but inconsistent across projects, and you maintain it yourself. Not a good idea IMO.

They are meant to allow for the dev of an ecosystem.

This is why you have so many django plugins, and why they are so easy to integrate and play together.

This is why it's easy to have a dev envs, tests, signals and api generation with django.

It trades performances and flexibility for this. It's nothing more than a classic technical compromise.

You may need raw sql later on in your django project. But if you feel like starting with it, just go with flask. Django makes no sense.

I agree. I wholeheartedly recommend Stephane Faroult's SQL Success as a beginner book. Read that with attention, and you'll be ahead of 95% of people messing around with RDBs.

These days when I know it’s a long term project, I prefer laying out the domain entities as plain classes and wrapping database operations as class methods with raw database statements.

This lets me use the underlying databases to its full potential and gives me enough control without leaking database code into application logic.

I guess this is the crux.

You hardly know in advance this is going to happen.

I experienced this transition (mysql>pgsql), though not with Django.

The ORM was perfect. If I recall correctly, only the customized SQL queries (only a few present) had to br adapted, everything else just worked.

11/10 would chose such a framework again.

It worked. It took many months and copying the data and doing the migration was a pita. The orm helped in that I had to modify fewer queries by hand, but modifying the queries we did have was trivial also. It's one of only two times off the top of my head that the orm was a net benefit rather than a liability in the apps that use it. Would I use an orm in the future in case this comes up? Hell no. I'd pick a proper db first, aka pgsql (that decision was before my time). But even if I didn't, I'd still prefer to convert the sql one by one from mysql to pgsql rather than incur the horrific penalties and extremely slow run time and development time the orm imposed on us. Developing with the orm was many times slower and the runtime was ten times slower than the actual query time due to hydration. Rewriting a few thousand queries is nothing compared to losing months to the slow development speed and the slow runtime speed which required a completely separate rewrite of most of our functionality alongside the orm just so we could serve api requests in a manner somewhat reminiscent of not completely slow.

So yeah, even when switching from mysql to pgsql, the orm wasn't worth it. It never is or has been.

The issues mentioned here in this article are real (E.g. we have a lint rule blocking importing other code inside migration files), but the author is also ignoring the benefits of Django's system, namely that it's a well-documented system that does most of the work of managing migrations for you, and does a good enough job that the vast majority of developers working on a large software project (like Zulip, with 96 people who've contributed changes to database migrations) will do it correctly without special training just from the Django documentation.

And the downsides are certainly minor compared to the obvious downsides with rolling one's own system with raw SQL (E.g. the fact that nothing validates that the model definitions match the database schema in the author's proposal!). I wouldn't recommend anyone take the author's advice for what to do instead, but this article is helpful reading for anyone curious about what issues one will encounter after a few years working with Django.

> How many times have you worked on a project and, after 1-2 years of development, you have changed to a different database?

FWIW, Zulip was originally developed with MySQL, and then about a year into developed we switched to Postgres. It also had a phase where we ran SQLite in development environments. Building on Django made our life a lot easier doing this.

> Once the migrations have been applied to all your testing and production systems, and all your developers' databases, just remove them.

This is only true if you don't care about being able to use tools like `git bisect` where you might want to switch a development environment to older versions and back again (I do this often as part of).

> If you do it in a loop, it results in a "ripple load", meaning you'll run 50 queries, each one fetching one record, while you could have run a single query fetching 50 records, or just adding an extra JOIN to another query.

This is my biggest complaint about Django's ORM -- lazy fetching of foreign keys is great in a management shell but rarely desirable in production, and it results in inexperienced developers doing database queries in loops all the time. We address this with a bit of middleware that formats log lines like this e.g. `41ms (db: 10ms/2q)` (showing how much time was spend doing database queries and how many queries were done) -- which lets one easily identify problematic cases of this both in development and in production.

But if Django had a setting that just disables the lazy fetching of foreign keys feature (making accessing an unfetched foreign key an error), I'd turn it on for Zulip.

See also https://zulip.readthedocs.io/en/latest/subsystems/schema-mig....

On the other hand, doing this loses a lot of the sugar and development benefits of using the ORM (either Django or SQLAlchemy, which I prefer), that are often enough—and perhaps preferred—for 95% of projects.

If I were to choose, I'd pick the ORM, though trying to place it as high in the stack as possible.

The author's solution, instead of adding on to migrations then opts for completely abandoning a tried and tested migration framework for arbitrarily executing sql files? This is madness

TLDR; I disagree: Django migrations are the best, and make reusable apps possible!

I've been using Django since before they introduced migrations and it was hell! Django only had "sync_db" then which would create your tables initially, but then you were on your own to write and run SQL manually for any model changes. This became especially annoying when using 3rd party apps, because if those apps changed the schema, you'd have to manually update your database for the 3rd party changes.

Then came along django-south which was a reusable app which provided migrations and solved all of the above problems. It became almost the standard approach, and third party apps often provided south migrations.

South became so popular, and was such a big improvement to the development workflow, that it's codebase was merged into Django (after making some significant API improvements) to become Django migrations. I can still remember having to covert all the files in the migrations directory from South to Django migrations.

Now we have a defacto standard of migrations work, and they allow easy use of customer SQL. Seriously, if you hate python based migrations as much as the author does, write custom SQL but put it in a Django migration so you get all the benefits of being able to rollback migrations etc.

Summary: when writing all of your own code for a single project then there is only minor benefits from migrations. But they are still worth it! In fact, I write lots raw SQL in my migrations, eg for constraints, triggers, notify, and just modifying my data. If I run SQL, it goes in a migration.

But migrations shine the brightest for reusable first and third party apps, which lets admit, we all use in most projects. This allows a single reusable app to be used for all projects regardless of database. Also, as a consumer of a reusable app, I can let migrations do their thing, and not worry if an update will break my database.

I agree 100% with the author, if you check other open source projects (on other languages) they mostly are based on the patch version sql feature. Sorry, the migrations feature from my point of view it's absolutely over engineered..

I also agree with him, that doesn't make too much sense to keep Django+drf, when if you use fastapi+pydantic+asyncpg you got all the real innovation on the python side..

Also it's not a good argument that following the Django religion will let to proper future maintenance.. think on channels and the big mess around them :)

Sorry, I'm perhaps a bit biased, started with Django 0.9 till 1.9.. and right now I'm not using it for anything new.. on the way I swapped for go, and later come back to python thanks to asyncio.