On: Surviving Django, if you care about databases

[MattGaiser]

>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...

How do companies get this right? My organization has all these problems as well and I lose 10-15% of my time chasing misplaced SQL scripts or SQL scripts with improper tweaks.

[grey-area → MattGaiser]

Check sql migrations into version control, all changes get checked in.

Run them in a predefined order with a migration tool (something as simple as file name order is fine).

Save metadata in the db to ensure migrations are not run twice and keep a record (again migration tools can do this).

Test migrations multiple times prior to deploy using a sanitized copy of production.

Any of these steps would help, all of them would eliminate your problems.

[bb88 → grey-area]

Django does this.

[grey-area → bb88]

Django does some of these things (most migration tools do), but my understanding is it guides you in other directions (automated code-based migrations based on models as a source of truth), so if you want simple sql migrations you don't need (or even perhaps want) this part of django.

That's what the article explores.

[rtpg → MattGaiser]

Django migrations solve this by asking for a dependency graph, and verifying that there's only one leaf node, so to speak.

So if you merge in separate scripts you will need to linearize by hand.

On top of this, there's a table within the database tracking which migrations are run, so you don't accidentally rerun these for example.

[aptwebapps → rtpg]

What will work in a lot of circumstances is to put your custom scripts into empty Django migrations. Then they'll get run as needed. If you're feeling fancy you can write the backwards script as well.

[skinnyarms → MattGaiser]

There are tools that help this: Visual Studio Data Tools, RoundhousE, Flyway/Redgate - I highly recommend investing time in building them into your workflows.

[AlphaSite → skinnyarms]

Alembic is pretty great for this as well

[MattGaiser → skinnyarms]

They aren't going to give us time to implement those, but thanks. Shall implement them in my own personal projects.

[murkt → MattGaiser]

I use a simple framework-agnostic tool for tracking and applying migrations, it also supports dependencies between migrations. Migrations are automatically applied on deploy to stage/production, so you don’t forget to do it.

I prefer this approach very much and even wrote about it here https://vsevolod.net/migrations/