I've found that replacing the database with in memory SQLite for tests is a sweet spot. Almost as fast as a mock, catches a lot of database issues. And it's really easy to do if you're using something like Django that makes automatically generating database migrations easy.
It won't help you with database specific differences. But there should be very few of those if you're using a framework that abstracts away the database. Like Django.
> But there should be very few of those if you're using a framework that abstracts away the database.
But I really want that database-specific behaviour. :) PostgreSQL does so many amazing things (recursive CTEs, jsonb, etc) that actively make our system better. If there was a fork of Django that optimized for leveraging advanced postgres features, I'd use it.
Unit tests work well with PGlite, at least in the TS/JS world where it natively sits at the moment. You can have a unique Postgres instance for each unit test that's up and running in just a few ms.
It's possible to use PGlite from any language using native Postgres clients and pg-gateway, but you lose some of the nice test DX when it's embed directly in the test code.
I'm hopeful that we can bring PGlite to other platforms, it's being actively worked on.
The other thing I hope we can look at at some point is instant forks of in memory databases, it would make it possible to setup a test db once and then reset it to a known state for each test.
You don't even need to install it. It doesn't take a lot of code to run initdb to create a temporary instance, write a suitable configuration file, launch the postmaster process, and delete the temporary database directory tree after terminating the database after testing is complete. On a not-too-fast Linux system, the time for all that is around half a second. Too much overhead for individual unit tests, but fast enough to run once per test suite run.
I want test with the same API as my database, which means using the same database. Setting up an instance of Postgres/MySQL/whatever isn't hard. Maybe harder if you're using an online database that doesn't fit into a container, but that's a different problem.
I want to develop on postgres and test on postgres because I run postgres in production and I want to take advantage of all of its features. I don't understand why a person would 1) develop/test on a different database than production or 2) restrict one's self to the lowest common denominator of database features.
I don't know what kind of magic fairy dust Django is but I've found the differences between SQLite and PostgreSQL too big to be worth it, in at least 3 other programming languages that are not Python.
Sounded good at first but we were quickly overwhelmed with false positives and just opted for Postgres in a VM (this was before Docker was a thing).
PostgreSQL stops many data bugs at the door due to being so strict -- something many programmers start rediscovering is a good thing by choosing stricter programming languages with time.
I love SQLite to bits but the test harness I have to put around my apps with it is a separate project in itself.
Somehow this is my first time hearing about that benefit of Postgres! (despite having googled for explanations of why people were switching to it). Being on HN proves more valuable than google once again...
On a philosophical / meta level it's all quite simple: do your damnedest for the computer to do as much of your work for you as possible, really. Nothing much to it.
Strict technologies slap you hard when you inevitably make a mistake so they do in fact do more of your work for you.
The problem with this approach is that SQLite dialect is not the same as most production setups. Even if you use an ORM you often have manual queries or you are using a feature that is only supported in some databases (like geospatial queries)
I'm surprised you say so btilly. I've found the differences in between SQLite and Postgres to be large enough to bring up issues that are hard to find. Timestamp handling, non-trivial indexes, etc.
As many others already stated, there are more than just small subtle differences that will bite you with this approach. Eventually forcing you to develop unnecessary compatibility abstractions or downgrade to less efficient lowest common denominator feature set to make it work. It's not worth the effort.
Especially since launching postgres is equally easy and fast as sqlite. Docker can help with sandboxing. What is left to gain? 100ms shorter startup time or keeping your unit test executables as single binaries? Irrelevant.
> Especially since launching postgres is equally easy and fast as sqlite
It's definitely not as fast to start postgres as it is to start sqlite. Pretty much inherently - postgres has to fork a bunch of processes, establishes network connectivity etc. And running trivial queries will always be faster with sqlite, because executing queries via postgres will require intra-process context switches.
That's not to say postgres is bad (I've worked on it for most of my career), but there just are inherent advantages and disadvantages of in-process databases vs out-of-process databases. And lower startup time and lower "dispatch" overhead are advantages of in-process databases.
Came here to say this: I’ve found that using SQLite for tests is a good sweet spot, and maintaining dual DB implementations ensures my business logic remains generic. But there is no replacement for running the tests agains the real database periodically (maybe only in CI for example).
Why is maintaining dual DB implementations worth it? Like what is the tangible benefit of "my business logic remains generic"? Do you have to deploy the app in different environments with different DBs?
How if you rely e.g. on CASCADE and foreign keys, which are not on by default kn SQLite? I think then things start getting complicated and testing that layer gets difficult.
Django allows for the common use cases for that to be abstracted out. So the SQLite version will do things manually, while PostgreSQL can use the database feature.
That's the reason to use an ORM. It abstracts away things like that.
It won't help you with database specific differences. But there should be very few of those if you're using a framework that abstracts away the database. Like Django.