19 ms·

Surviving Django, if you care about databases

• BiteCode_dev 6y ago
  This article misses the point of the django orm and migration system completly. 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.
  • tuatoru 6y ago
    Many of the points made apply equally to Flask/SQLAlchemy or any other framework (Rails, ...). They optimise the wrong things and decrease security.
    • giancarlostoro 6y ago
      > They optimise the wrong things and decrease security. They're not meant to optimize for every edge case, they're meant to be as generic as sanely possible.
    • smoe 6y ago
      All of them optimize for different things (E.g. SQLalchemy and Django have very different philosophies), so are they all wrong? What should they focus on instead?
  • giancarlostoro 6y ago
    The other funny thing is how easy it is to write migrations in Python via the RunPython capabilities of the Django ORM you can migrate data over. I've yet to need to write any SQL aside from stored procedures and Database Views. You can even export all the contents of your DB to JSON or YAML and import it in Django, so you probably very well could go from SQLite to MySQL / Postgres if you really wanted to. Edit: The other actually funny thing is, Django doesn't force you to use its own ORM. You can write code that doesn't use its ORM. You can override anything and everything.
    • jordic 6y ago
      For me it's too much easy to write them with sql or plpgsql.. and later I can use them from node, rust, go.. or whatever.. for me the dB (the model) it's the source of thruth that will lasts.. independent of the language that interacts with it... Also the problem gets worst if you add to the mix something like elastic search. That it also has it's own schema and it's also has to be migrated..
      • bb88 6y ago
        But now you've got the issue that the code needs to match the DB. Django takes care of this for you. It's really quite nice.
        • jordic 6y ago
          That's the trade-off if you decouple the dB from the web framework you have to take care of both.. :) anyway, I prefer to trust the dB (real data on it) that a bunch of complicated things on the Django side.. (I suspect that happened something similar to me when I started reviewing crazy (shoot your food) class based elaborations..
        • giancarlostoro 6y ago
          You can always tell Django's ORM not to manage any of your data and write all your queries. You can also tell Django to give you the query it generates, tweak it (if / as necessary) and run from there.
          • bb88 6y ago
            But why? Granted if you have super complex queries, sure. But 95-99% of the queries are simple typically.
    • rurp 6y ago
      > how easy it is to write migrations in Python via the RunPython capabilities of the Django ORM you can migrate data over. Yes! This functionality is extremely useful in a number of cases and isn't all that discoverable. At least in my experience, devs often don't learn about it until they've spent at least a bit of time working on a Django project. Once you get the (basic) syntax down it's quite handy.
  • ensignavenger 6y ago
    Django is incredibly flexible, even if you decide not to use the ORM, or even to just use part of it and write a lot of raw sql, Django is a great choice. It can still be a great choice.
    • jefurii 6y ago
      Or even if you ditch the SQL database entirely and use Elasticsearch or some weird custom back-end.
      • jordic 6y ago
        And then (with elastic) why you will use a synchronous web framework ;)
        • VWWHFSfQ 6y ago
          Synchronous web frameworks have been powering some of the largest websites on the internet for decades. That said, Django is actively working toward async views and ORM. It's happening.
          • ensignavenger 6y ago
            In fact, it was just released a few days ago.
            • jordic 6y ago
              Then you still need to upgrade Django.. (had seen so many sprint points spent on it.. :/)
  • smoe 6y ago
    I agree with you but I think Sqlalchemy/alembic do the compromises better than Django ORM With Django I always ended up hitting brick walls and ugly hacks. Whereas Sqlalchemy more easily fit in any use case that I had over the years, mainly because of it having multiple layers of abstraction so I can choose and mix as applicable. Learning curve is a bit steeper i admit. I haven't used Sqlalchemy drop in replacement for Django ORM yet, but would consider it for a future project.
    • hogFeast 6y ago
      I think one issue with SQLAlchemy is that, somehow, they are the worst of all worlds. They have multiple ways to run queries (iirc, the "model" way, the non-model way, and the raw way) but it is all the "model" way underneath so you end up with all kind of weird hacks if you don't want to use Python as the source of truth (i.e. if you run a query, have no data model in Python but want to turn the data into a dictionary). It seemed like a very flabby codebase (i.e. they started out doing X, and tried to add stuff on top...lots of classes that appear to do the same thing). The only ORM that I have used that handled more complex stuff well was Jooq (Java). Having your database as the source of truth (and running a script to update Java Objects) works far better than the SQLAlchemy approach. I use Django now for a simple app but I used both Jooq and SQLAlchemy on something more complex (the issue was references to parts of the data models across components), and the former was brilliant (although learning curve) and the latter was just awful. I don't think SQLAlchemy docs are good either (again, started out as X and they just kept adding and adding). That is just my experience. I used Django right now, there are no crazy cross-cutting concerns between components, and it works fine.
      • jordic 6y ago
        That's the nice thing around sqlalchemy, it's a lib, a toolkit.. you can start using it on whatever postgresql schema you found.. Django can't only work if things are done like it wants... You will also use Django for an old legacy PHP project with postgresql? You can use SA for taking on it.
        • 6y ago
  • neillyons 6y ago
    Agree. This article misses the use case of people using third party apps with migrations. For example. The Wagtail content management team can create migrations that can be applied to MySQL and Postgres databases. Not sure if that is possible writing SQL files.
• orf 6y ago
  Migrations can, and do, run arbitrary SQL. At the end of the day the migrations framework is just a SQL dependency graph, it can be used without models if that's what you wish. By itself that is way better than ad-hoc scripts.
  • ris 6y ago
    > At the end of the day the migrations framework is just a SQL dependency graph. It's not quite. During operation it actually mutates an in-memory shadow of your model structure. And that's really clever, but if you've got a large set of models it can be really slow.
    • orf 6y ago
      That's built on top of the dependency graph though. You can use it without any models - just a bunch of inter-dependent `RunSQL` statements across different apps in your project.
    • dvarrazzo 6y ago
      It also breaks in interesting way: I discovered just today that constants defined on the Model subclass are not available when you use 'get_model()'. I suspect methods wouldn't be accessible either?
      • MythicDev 6y ago
        As far as I know, this is by design; your migrations are supposed to operate on a frozen state of what your models and code _were at a point in time_. If you would rely on code outside of said migration, you would be breaching that frozen state and potentially end up with unintended side-effects (e.g. running a migration created 2 years ago that imports your code that changed today). This is why you might have to sometimes copy-paste logic to your python migrations, but you also guarantee that the migration always runs the same way.
    • aeyes 6y ago
      If it gets too slow you can always squash migrations. But since Django 2 I haven't seen the need to do it, runs in a few seconds even with hundreds of migrations.
• airstrike 6y ago
  > Django needs it because a web framework not tied to a single database vendor is more valuable than one tied to a specific one - and that's fair enough. But you don't: your web program, most likely than not, will not have to switch from one database to another 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
  • djrobstep 6y ago
    The idea that some library can abstract away database concerns to this extent is wishful thinking. You inevitably need to understand the SQL that your ORM is generating and what the database is actually doing. Otherwise you're flying blind.
    • airstrike 6y ago
      I never claim it replaces SQL entirely and that therefore users can "fly blind", as you put it. But the whole point of the ORM is to abstract away part of it
    • TylerE 6y ago
      Realistically what ends up happening is that the DB gets abstracted away to a painfully feature-free lowest common denominator.
      • bastawhiz 6y ago
        Django handles this quite nicely. You get your basic lowest common denominator subset by default. If you want something more powerful, you can explicitly import a db-specific feature. And for some features, you'll get a NotImplemented exception. In the ten years I've been using Django, I've only seen these a handful of times, and only when using SQLite.
  • scaryclam 6y ago
• VWWHFSfQ 6y ago
  > The motivation for writing this article comes from knowledge sharing with the team I'm currently collaborating with, who are using Django the canonical way. I am convinced they can better use the tools they have. Let's see if they can be persuaded to drop Django migrations... 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.
  • Daishiman 6y ago
    There's nothing worse than random teams reimplementing a migrations system because they think they know better. The Django migrations system is far from perfect, but it's light years ahead of anything a small team can build.
    • gerbler 6y ago
      I don't think these practices scale well. One person becomes the default "migration person" (with processes that are likely not as well documented as Django) and the team becomes reliant on tribal knowledge.
      • nikisweeting 6y ago
        How is that different from one person becoming the default "migration person" on a team that doesn't use Django?
        • Daishiman 6y ago
          The difference is that Stack Overflow has a ton of answers to even fairly complicated questions and that reading the Django docs is an order of magnitude easier than delving into some random person's badly written code.
    • collyw 6y ago
• acdha 6y ago
  This article is trying too hard to dress the author's limited experience up as absolute truth. For example: > 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.
  • tuatoru 6y ago
    > This article is trying too hard to dress the author's limited experience up as absolute truth. Sure you're not doing the same thing? As Vivekseth writes below, the author wrote psycopg2. --- Allowing the DB user that the web app uses to have schema modification privileges is a massive security hole. If you're not hacked, someone will eventually drop the production database. So, migrations in anything but SQL are a Bad Idea.
• vivekseth 6y ago
  Not mentioned in the article, but the author also built psychopg2 (https://www.varrazzo.com/software/ https://www.varrazzo.com/software/)
  • jimhefferon 6y ago
    Thank you. Relevant.
    • giancarlostoro 6y ago
      For those who do not know Django uses said library for PostgresSQL support. Mind you, if you don't like Django's ORM, you don't have to use it.
      • cafard 6y ago
        Isn't it the standard Python library for PostgreSQL? A co-worker just installed it for some work having nothing to do with Django.
        • szatkus 6y ago
          Yes, it's the most popular driver for PostgreSQL in Python.
        • giancarlostoro 6y ago
          It's the most popular, if you mean standard by that metric then yes, if you mean standard as in built-in to Python, then no (I only mention this because SQLite does come with Python OOTB).
  • nojs 6y ago
    Thank you, that explains the willingness to entirely ditch the migrations framework in favour of raw sql!
• thedanbob 6y ago
  Interesting comparing this to Ruby on Rail's ORM, which takes a relatively hands-off approach. ActiveRecord will produce a migration for you when you generate a model from the command line, but otherwise migrations are entirely the developer's responsibility. And the way he suggests handling patches sounds a lot like how ActiveRecord does it: keep track of what migrations have already been applied and only apply newer ones, and for a new database just create the up-to-date schema rather than applying the entire history of migrations.
  • bb88 6y ago
    > keep track of what migrations have already been applied and only apply newer ones The Django migration system does this for you, and it does it really well.
  • _AzMoo 6y ago
    There's nothing stopping you from hand-writing Django migrations if you choose. Django compares the state of the models to the state of the database after all migrations have been run to determine if new migrations are necessary, so as long as you've covered all of your model changes in your hand-rolled migration you'll be fine.
    • thedanbob 6y ago
      Ah, good to know. I have no experience with Django and the article gave me the impression that Django migrations can’t be handled manually.
  • GlennS 6y ago
    All these systems are patch based systems (for an alternative approach see the comment in this thread from the Migra developer). However, he is suggesting the patches should be written in SQL. By default in Django the patches are written in Python, which is then used to automatically SQL.
• whakim 6y ago
  Seems like the author should've just used a much less opinionated framework. The last thing I want to do is choose a batteries-included framework and then take on the responsibility of maintaining my own system to manage SQL migrations. Also unclear where the author has worked, but I've had to replay a bunch of migrations in order on many occasions.
• ris 6y ago
  I can't say I can recommend following almost any of this as "general" advice. It is, at best, advice from a complete expert for complete experts who are already pushing the edge of what django should be used for, and who never expect to have to hand off their project to an average web developer. I've had to come to terms with the fact that the average web developer is going to screw up e.g. writing their schema by hand in sql. And I know I'll likely be the person inheriting this two generations of developer after the original wizard left. 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.
  • bb88 6y ago
    For my company I wrote internal django apps that are completely database agnostic. They can be run in testing with sqlite, postgres, oracle, etc.
    • ddorian43 6y ago
      I don't ever agree with this. You end up using only the (minimal) features that they all support. I can't imagine going from PostgreSQL features to MySQL. And you should always dev/test/stage on the same database that you'll use in production.
      • bb88 6y ago
        Everything you're saying is true, but if the devs can run unit tests on sqlite, that's a big win. And you shouldn't discount this feature off hand, or limit yourself from being able to do this. It's one thing if you want to run postgres locally, that's fine, but who really wants to run Oracle on their laptop just to run unit tests, or to test an api locally?
• zumachase 6y ago
  We've got a core product running on Django, and one thing the author doesn't mention is testing migrations. The migration system is wonderful, except for the difficulty in testing migrations. There's no sane/official way to do this. And it's such an important thing that I don't get why the Django crew haven't tackled it.
  • ensignavenger 6y ago
    What sort of proposal would you put forth? I generally install one of my production backups onto a staging server (secured appropriately for production data), and run the migrations against it to test.
    • jordic 6y ago
      That's not the intention on the code, I thought he is talking around writing a migration and a test that ensures the data migration is correctly applied
  • _AzMoo 6y ago
    I've used django-test-migrations before and was really happy with it. https://pypi.org/project/django-test-migrations/ https://pypi.org/project/django-test-migrations/
    • zumachase 6y ago
      We've played around with that and it's on the roadmap. But it just adds another set of fixtures to herd.
  • dvarrazzo 6y ago
    My strategy here is to have a staging server where migrations are applied before they can hit production. As someone else pointed out, migration without CI can be dangerous and difficult to coordinate with deployment.
    • zumachase 6y ago
• bb88 6y ago
  A couple of real world data points. 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.
  • rubber_duck 6y ago
    I don't have experience with Django model migrations so can't comment on that - but running script based migrations without a CI like that in any nontrivial environment sounds like asking for trouble, but how did you not catch the issue when a dev pulls from master and migrations aren't working - everyone just does local patching and ignores that the master is broken ? I've seen this on a .NET project, we didn't have a CI and this one guy had a tendency to commit dirty migration scripts breaking people's databases - I've seen it slip a few times to the QA deploy process before it gets caught because everyone assumed he would fix his shit and was too busy cleaning up his crap so we would just comment it out locally to get it to run.
    • bb88 6y ago
      Django migration scripts are run by devs after pulling from Master as well as deploying to prod. That gives everyone the same confidence that the migrations are working. If the migrations are borked, they get fixed or the prod deployment will break. Ideally this should be part of the CI/CD process for the PR. Also it's worth noting that you can run all migrations from the beginning of time to the latest version on a development DB. And I believe the default Django unit testing framework does this by default.
• tuatoru 6y ago
  The implication of the article is that if you're using a relational database management system, you need to know SQL. 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.
  • jordic 6y ago
    Also agree, sql it's like javascript, it's almost on all projects I worked last 20 years.. (Django it's just another tool, and implementation detail).
• lewisjoe 6y ago
  A lot of problems stated are characteristic of any ORM not just Django. People pick ORMs specifically for trading convenience and tooling with loss of control. Any escape hatches that the ORM provides is by definition hacky. 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.
• ensignavenger 6y ago
  I support a very large database, containing product data for a major US retailer. If I understand the history of this database correctly (I am not directly on the team that manages it, but my team supports them in various ways) the database began life as an MS SQL DB. Many years ago it was migrated to MySQL. Now, they are in the process of migrating it to Postgres. So yes, migrating data to different DB's does happen, especially when you consider an applications life over decades.
  • kevindong 6y ago
    Why was the underlying database changed? The most obvious reason I can think of being licensing concerns with both MS SQL and MySQL.
    • ensignavenger 6y ago
      The first migration from ms sql to mysql was licensing costs, and the desire to host on Linux. The more recent plan to move to postgres is motivated in part by features and in part by standardizing on postgres.
      • grey-area 6y ago
        Features that you wouldn't be allowed to use if you try to remain database agnostic. DB migrations happen, but not often enough to use a lowest-common-denominator db agnostic library and try to remain compatible with all systems.
        • ensignavenger 6y ago
          Well, that is another area the article got partially wrong. There are many postgres specific features included in Django, and other available via third party packages. And for most things that the ORM doesn't specifically support, it gets out of the way and lests you do them anyway, by executing your own custom SQL, right from the ORM.
• tabbott 6y ago
  I agree Django's migration system has a few unfortunate quirks (most notably the help-text-change-migrations), but it's also worked great for Zulip across over 300 database migrations, and I wouldn't recommend anyone do their own migration framework on raw SQL files by hand. 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-migrations.html https://zulip.readthedocs.io/en/latest/subsystems/schema-mig....
• jordic 6y ago
  Another thing important that the author doesn't mention is that if you try to keep your sql model on the postgres side.. you will be able to introduce other languages (for example to optimize hot paths) 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.
  • ensignavenger 6y ago
    What mess around channels are you talking about? I've never heard anything about such a mess? Not everyone uses Django to build a REST API that connects with a javascript SPA. And even if I was, I would still be skeptical that FastAPI could do everything Django does for me. (I do like Pydantic a lot, and actually use it some times in my Django apps.)
    • jordic 6y ago
      Look at what will happen to channels on the next releases when Django will be async... To have a websocked popping info from a redis pubsub doesn't make any sense all of this crazy abstractions.. it's just an async view reading from aioredis.. If you need an orm (a real one) use sqlalchemy.. the Django orm has it's own design flaws that a lot of of dbas will complain about it.. Anyway, latest thoughts I have it's that it's better to keep you model logic as near as sql as you can.. you will swap before python or Django than postgresql :)
• rhema 6y ago
  I made a custom Django system for my personal website in 2013. It became to old to work with my web host. So, now I just run the django locally long enough to run a wget mirror. Voilà, static content.
• nikisweeting 6y ago
  In my 8 years of working on large Python codebases, I think the Django ORM is one of the best pieces of software I've had the joy of using. I often recommend it independent of the rest of Django (e.g. to people building Flask apps that need an ORM+migrations). 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.
  • VWWHFSfQ 6y ago
    > the ability to swap out PostgreSQL for SQLite on smaller projects without having anything break is a feature I've relied on several times Absolutely. Take the extra minute to add a natural key to your Django models and now you can do amazing things like dumpdata from your Prod or Staging database and then loaddata into your local sqlite database for dev. I've never had to run Postgres on my laptop for developing. As long as your CI tests use your production database server type then you don't have to worry about subtle differences between developing locally with sqlite and deploying on another RDMS.
    • jordic 6y ago
      That's really a so bad practice, a big code smell.. how you test all the dB logic, with a different dB? Constraints, indexes, tsvs..
      • VWWHFSfQ 6y ago
• 40four 6y ago
  Pretty good read! But I don't like how the author tries to characterize generic ORM's like Django's as "Portability at all costs". I don't think the point is that you can switch DB's a few years down the road if you choose. He's right, nobody does that. Ever :) The advantage is that you can start a new project with whatever DB you already have, or are comfortable with. So, if you want to begin a new Django project, you aren't locked into using MySql, or Postgres, or what-have-you. It is true you sacrifice being able to leverage the full gamut of features a particular engine might offer, but that is a compromise you make with any ORM right? Or, if the limitations are to much to bear, just don't use Django ORM, it's not required, it's just the path of least resistance.
• vandahm 6y ago
  I wish I could work on these magical projects that people with blogs work on, in which everything works like it's supposed to do. In my experience, though, it's not unusual to be forced by management to migrate to a new RDBMS a year into a project. The last time it happened to me, we were using Rails and had very little difficulty migrating from PostgreSQL to MySQL. We didn't need to alter application code or rewrite tests and only had to manage config changes and migrate the data. I imagine the transition wouldn't be much different if we had been using Django.
• l0b0 6y ago
  This sounds like a "golden hammer" argument[1], basically saying that SQL can do everything Django can do, and if you know SQL really, really well you can do powerful stuff which would be clunky in Django. Of course that's true, because it's true of any two tools which have overlapping functionality. But for the vast majority of things you need to do on a daily basis, and for developers with >10x more experience with Python than SQL it just doesn't make sense to use raw SQL any more than absolutely necessary. As soon as you use raw SQL in Django most of the maintainability advantages of an ORM disappear. 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 https://en.wikipedia.org/wiki/Law_of_the_instrument
• mnm1 6y ago
  I've migrated a production app from mysql to pgsql although not with django but a different orm and language. The app also had some custom sql. The reason was for proper json support as we have a 2tb table with unstructured data (1tb at the time) that was slowing mysql down severely (most used table by far). A later version of mysql with json support was not available on aws so wasn't an option. This was about two to three years in so the app was actually almost finished and in maintenance mode. 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.
• Humphrey 6y ago
  History Lesson (if you care about migrations): 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.
• zzzeek 6y ago
  Daniele is a great guy and I deal w/ him regularly as he's the author of psycopg2. However the mistake he makes here is in the realm of database abstraction tools having the sole rationale of "database agnostic code". That's really not the main rationale, nor is it really "hide the database so people don't have to learn SQL". The main one is "automation of work". A lot of the comments here refer to situations where hand-rolling everything always was just too error prone. There's a thing we do in computers when a job is very repetitive, tedious, error prone, and follows a complete pattern that you can define algorithmically, which is, write a program to do it!. that said I'm sort of glad Django took the heat on this one and not SQLAlchemy so I can get to bed without obsessing :)
  • stickyricky 6y ago
    Thank you for creating SQLAlchemy and being so active on its mailing list.
• kissgyorgy 6y ago
  > And with comments! Explaining why a certain index or constraint exist! 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" > In my opinion, if there is something less likely to happen than writing a complex app to run on interchangeable databases, it's the need to repeat the same history of migrations. 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.
  • dvarrazzo 6y ago
    > You can put comments in Python files... only if the comment pertains to a django feature, not to a schema feature that cannot be expressed in django (e.g. a partial index designed for a specific query). > You can name your migrations with: In the example there is a foreign key name, not a migration name. It is persisted in the database, it's not ephemeral like a migration name, for which choosing a meaningful name has only a temporary value. Just two factual corrections; for the rest our experiences diverge, that's fine.
    • kissgyorgy 6y ago
      > for the rest our experiences diverge, that's fine. https://twitter.com/kissgyorgy/status/1291609448809627649 https://twitter.com/kissgyorgy/status/1291609448809627649
• some_developer 6y ago
  > How many times have you worked on a project and, after 1-2 years of development, you have changed to a different database? > > > I can tell you how many times it happened to me, I counted them: exactly never. 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.
• vbilopav 6y ago
  My advice is to avoid Django ORM altogether
• DangitBobby 6y ago
  > Much of the complexity of this system is designed to give you a specific feature: migrations abstracted from your database. In my opinion, if there is something less likely to happen than writing a complex app to run on interchangeable databases, it's the need to repeat the same history of migrations. 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.
• 1337shadow 6y ago
  Probably they should try django-north instead of their patch_db.py
• Jasp3r 6y ago
  Terrible article, especially the part about migrations. The automatic migration generation of Django of course cannot infer exactly how you want your schema to change if you start renaming or altering columns. 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
• luord 6y ago
  This is tricky, on the one hand keeping everything related to the database close to or in the database is probably cleaner (in the clean architecture sort of way), as it eases a separation between the business rules and the data saving and loading. 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.
• jordic 6y ago
  May Django work with multiple tenants on different postgresql schemas? Can Django migrations handle this in an easy way (better than sql).. When projects grow old (+10years), things trend to complicate (lot of hands, lot of changed requirements.. ), and that's when the advices on the article had a lot of sense.. on the end data is there and the model is the database..
• jordic 6y ago
  I would also add to the article, that when you try to alter a busy table add it a timeout (otherwise you will lock the world). SET statement_timeout = 50; Alter .... ;