3

This article advises to use SQLite for tests even if you use another RDBMS (I use PostgreSQL) in development and production. I tried SQLite for one test case and it ran faster indeed (~18.8 times faster, 0.5s vs 9.4s!).

In which cases could using SQLite result in different test results than if I used PostgreSQL?

Only if I would test a piece of code that contains a raw SQL query?

Bentley4
  • 10,678
  • 25
  • 83
  • 134

2 Answers2

6

Any time the query generator might produce queries that behave differently on different platforms despite the efforts of the query generator's platform abstractions. Differences in regular expressions, collations and sorting, different levels of strictness about aggregates and grouping, use of full-text search or other extension features, use of anything but the most utterly simple functions and operators, etc.

Also, as you noted, any time you run raw SQL.

It's moderately reasonable to run tests on SQLite during iterative development, but you really need to run them on the same DB you're going to deploy on before you push to production. Otherwise you'll get bitten by some query where different engines have different capabilities to prove transitive equality through joins and GROUP BY or are differently permissive of queries, so a query will work on one then fail on the other.

You should also test against PostgreSQL on a reasonable data set before pushing changes live in order to find obvious performance regressions that'll be an issue in production. It makes little sense to do this on SQLite, where often totally different queries will be fast or slow.

I'm surprised you're seeing the kind of speed difference you report. I'd want to look into why the tests run so much slower on PostgreSQL and what you can do about it, since in production it's clearly not going to have the same kind of performance difference. I wrote a bit about this in optimise PostgreSQL for fast testing.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • There's a ton of things I don't understand yet about Databases. Optimizing PostgreSQL seems a little out of my reach right now so I think an alternative would be to use SQLite for testing while working locally and use PostgreSQL on a Continous Integration service like Travis to sift out the cases where tests results are different. Or at least run tests with PostgreSQL each time just before pushing code to production. – Bentley4 Dec 11 '13 at 23:47
2

The performance characteristics will be very different in most cases. Often faster. It's typically good for testing because the SQLite engine does not need to take into account multiple client access. SQLite only allowed one thread to access it at once. This greatly reduces a lot of the overhead and complexity compared to other RDBMSs.

As far as raw queries go, there are going to be lot of features that SQLite does not support compared to Postgres or another RDBMS. Stay away from raw queries as much as possible to keep your code portable. The exception will be when you need to optimize specific queries for production. In those cases you can keep a setting in settings.py to check if you are on production and run the generic filters instead of a raw query. There are many types of generic raw queries that will not need this sort of checking though.

Also, the fact that a SQLite DB is just a file, it makes it very simple to tear down and start over for testing.

Matt Williamson
  • 39,165
  • 10
  • 64
  • 72
  • You motivate why SQLite is often used for testing and that raw queries can cause problems when using SQLite in testing. But I did not doubt those things, I was asking if cases exist different from using raw queries that could cause problems and which those cases were. Still, thanks for your contribution. – Bentley4 Dec 11 '13 at 23:28
  • Short answer: everything should work the same, probably a bit faster in a lot of cases. That's the whole point of the ORM. – Matt Williamson Dec 11 '13 at 23:36
  • I thought so too but apparently there are cases where it won't behave the same according to Craig Ringer's answer. As far as I understand it unless you know perfectly well which SQL queries Django calls internally and how SQLite handles things differently from PostgreSQL you should run tests with postgreSQL at least just before pushing code to production. And maybe even if you do, people make mistakes once in a while. – Bentley4 Dec 12 '13 at 00:06
  • 1
    @MattWilliamson Good suggestion on how to integrate DB-specific queries where necessary. Makes testing with the real DB before pushing changes live even more important, but makes using a dummy test DB a lot easier. – Craig Ringer Dec 12 '13 at 05:33
  • While using SQLite for our functional tests in PHP & Laravel, we stumbled upon some issues which made us think that we should look for other solutions. One such case is that SQLite does not permit adding a non-null column in a migration even if there are no records in database. So, we have to put a code similar to `if engine is SQLite then add a nullable column and then change it to non-nullable` to our Laravel migrations. It's not good thing that we have to tweak our migrations for our tests to work around some quirks of SQLite - it just slows us down. – JustAMartin Jan 30 '16 at 20:49