Hacker News Reader

> 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

I've been writing Django apps since the 0.96 days (so, well before they officially implemented "migrate-able" schemas) and I'm still nervous af when having to manually edit/modify a schema file inside of ./migrations. As such, things like

> python manage.py makemigrations

that work 99% of the time have been a god-sent to me.

For my company I wrote internal django apps that are completely database agnostic. They can be run in testing with sqlite, postgres, oracle, etc.

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.

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?

This seems to have minimal upside and heaps of downside - you can only use the features that every database has.

You should be running the same database in test as in production anyway - there are heaps of problems you won't catch if you don't.

The majority of apps need a very small subset of database features; they need somewhere to park strings and numbers in a reliable way, and retrieve them (sometimes in order). The Django ORM supports this across all of the backends it supports.

Strong disagree - even the basic features and data types of databases have subtle but critical differences.

You should be free to use the features available, not waste your time optimising for databases that don't have those features but that you will likely never switch to anyway.

95-99% of your app is data and query optimization. And django seems to do all of this just fine really.

That severely limits you though.

And I'm not talking about postgis, hstore or such exotic features. I'm talking about indexes, transactions, locking and other basic feature that is often missing or implemented very different across databases.

You may not need, say, GIST indices that help when sorting "tweets by recent activity" for your proof of concept or even your test-env but you certainly do for production, or else you are truly missing out. And such basic tools are avaialble in most database-engines, but differ very much in how exactly they are implemented, to be used and to be tuned.

Django supports indexes, transactions, and locking, and if I need to specify raw SQL I can do that during a migration say.

90% of what I need is already there. And if I need something special, I can add it in later.

I'm not familiar enough with Django, but assume it being very similar to Rails (Ruby).

The migrations and ORM allows for a lot of indexing, but lacks the last 20%, which -logically- is DB specific indexing. But also the indexing that gets the majority of the improvements.

Locking is another thing: lowest commnon denomimator is bad. But works, for sqlite, mysql and postgres (and probably oracle, never tried). Yet is so naive that it does more harm that good. In Rails they've worked on this and moved far beyond the lowest-common-denominator with Adapters and other patterns.

That adds such an amount of indirections that it does more harm than good, when debugging where that weird "why did it choose to use a GIST and not a partial BTREE here".

(edit: kept story in line with one usecase. sorry)

TBH, I can't tell if you're complaining about Ruby or Django here. They're two very different things with different methodologies. You can't complain about how Ruby does it's database in a discussion about Django.

Like I said elsewhere, Django offers a lot, and what it doesn't offer can always be added with raw SQL.

I was pointing out issues that stems from "supporting multiple databases", which are issues that are the same in Django, Rails (not Ruby), PDO and basically any other ORM out there.

The problem is "lowest common denominator".

Think of it in an extreme way. as a thought experiment: what if you build an ORM that supports flawless migration from PostgreSQL to Redis and back. That abstracts away all the differences.

That ORM would bery extremely limited: it could basically only do key-value storage; would have no WHERE filters on values, for example, or if it did, the performance would be horrible: because redis does not have this.

Most RDBses follow most of ANSI SQL, so on the surface, they appear very similar, and a lot of times a developer never gets to the pieces that differ so much that either the ORM does not support it, or that the ORM gets terrible performance issues when using this.

More practical: if you use `ltree` or `GIS` features, you are now bound to postgres; which is why most ORMS don't offer support for this; or if they do, they only offer support for it when using the underlying engine (in other words: you cannot use e.g. sqlite anymore). Maybe that is fine and you can avoid using such "database specific features": good for you. But they are there, and are begging to be used; so please don't cludge around with two `BigDecimal` columns `lat:` and `lon:`: you are going to run into problems that have been solved for decades. Please just use PostGIS instead, and accept that this means you are now locked to postgres: you are going to thank yourself later.

It's not really about what Django supports, but about what all the databases you're using support. If you run it in testing with SQLite, you are not going to have the same set of features for indexing, transactions, and so on as you do in production with, say, PostgreSQL.

The parent comment mentioned GIST indexes; I'd add partial indexes and triggers as two features that can make a huge difference when used even in a very few places in an app (which is what we do in Zulip, on PostgreSQL.)

Sure, but does it matter when unit testing code or developing the 95% where it doesn't?

It matters for production sure if you start getting slowdowns and you can have migrations that do that. But for cranking out APIs on DRF, say, it matters a lot less than you think it does.

For that I guess it comes down to whether the features you're relying on are purely optimizations (like partial indexes, fancy GIST index types, etc.), or go to the semantics of how the code behaves (like triggers).

I think for us it's the case that all the fancy DBMS features we rely on in the core functionality of the app are pure optimizations. So if we wanted to run tests on SQLite, that'd work fine except for when testing the parts of the app that happen to rely on those fancy DBMS features that aren't only optimizations.

But I'd consider that a pretty unstable situation -- it'd mean that we had one test setup for most of the app, and then still needed another test setup (with Postgres) in order to test some parts of the app. When I've heard from people working on apps that use different DBMSes for test and production, generally they don't take this strategy and instead they just limit themselves to the lowest-common-denominator features that exist in both. You can totally do that (many people have!); but as berkes's original comment above said, if you do you're missing out on some really valuable features.

And if you ever want to use even a little bit of those fancy DBMS features, beyond pure-optimization index features, in some core part of the app -- boom, you can't test on the more limited DBMS at all.

I would say the same. It is about team size:

If you are alone and don't care whether your client succeeds after you've got fired: Go for it.

If you are in a really complex structure, you most likely have dedicated database people, who want to do their thing anyway.

In any other circumstance, it is a really bad idea to ignore the standards of your framework and build your own thing. If you have to manually change the database, at least stick it into a standard migration. Don't use your own thing. Documentation will be lacking, edge cases will be ignored, the old standard will still stick around and new people will try to use it.

I'm skeptical that giving someone that doesn't understand databases a tool to automate their use will result in a better situation long term. A determined programmer can always make a huge mess.

I will admit: I'm an ORM disliker, preferring to write all my SQL by hand. SQL is a wonderful language and it's often far easier to do things in SQL than in whatever imperative language you happen to be using. I also enjoy far better performance than most (any?) ORMs can offer, simply by using prepared statements. I have far greater control over batching, should I wish to employ it.

When things are going wrong, it's simpler in my experience to audit human-written SQL than it is to audit whatever stuff an ORM decides to emit for a given situation.

I agree with ignoring cross-database concerns, as I feel that you will be leaving the finer features of your database on the table if you stick to the basics. Here's the real counterpoint though. If you write SQL, then you can swap out your slow, single-threaded, interpreted programming language for one with a bit more punch. And you can even do it piecemeal. To me, that's a much more compelling hedge than swapping out a DBMS.