[pimterry]

Better: Don't test only with SQLLite when you use Postgres in Production.

His points are all valid, you definitely shouldn't release something to production that you haven't tested thoroughly in a separate identical environment.

That doesn't mean you should never test with SQLLite though.

A good pattern I see all the time is to have a final stage of system tests that run slowly but very accurately in a production-equivalent environment, and to also have a preceding test stage that uses SQLLite instead. By using SQLLite in memory it's much much faster and easier to manage and easier to parallelize etc etc, so you find out quicker if your code is fundamentally broken.

The goal of testing is to find out as quickly as possible (minimal context-switching, easy to check locally before commit) whether your application will work. That means checking the things that are most likely to break most of the time as early as possible. It's typically not going to be a complex database incompatibility issue that makes your tests fail. It's going to be the SQL query you wrote 30 seconds ago, or the leftover query that doesn't work at all the table structure you've just moved to, etc etc. These are things you can check perfectly effectively in a lot of cases, and much quicker, with SQLLite and friends.

Quick test stages that catch all the obvious problems early, final definitive production-equivalent test stage that makes sure it'll definitely definitely definitely work in real life. If you do it well, you can set it up so they both use exactly the same test definitions, and just swap out the DB config.

[pothibo]

I'm sorry but I really don't agree with this. One of the point the OP made is that if you develop in SQLite and deploy on a Postgresql database, it means you discard all the features that postgresql has over SQLite (data types and SQL Queries for example).

You wouldn't test your Facebook consuming API code with a Twitter endpoint, why do you apply the same logic to your DBMS?

[swsieber]

Exactly. IIRC, it is possible to run postgresql in memory if you configure it correctly. In the age of docker and the like, having different concurrent instances of a dbms should be easy enough, right?

[DiThi]

I came here to say something along those lines. Our project has a SQLite backend for hassle-free local development, and we use Postgres in all remote servers. Tests run both in local and remote.

[jeltz]

What hassle did you expereince when using PostgreSQL locally? On the projects I have been in we have ran PostgreSQL in local development almost painlessly. PostgreSQL is quite developer firendly, at least for my use cases.

I suggest using syncrhonous_commit=off to sped up the test suite and to use unix sockets (assuming you do not run Windows).

[DiThi]

The problem with using PostgreSQL was difficulty to make it work everywhere, we have at least one dev in each platform. I use the pure JS version of SQLite (sql.js in npm). The server is fairly small, having the bulk of the code in the client (SPA).

If it wasn't for the MySQL I had to implement after the SQLite work, I would say it's better to investigate the quickest way to configure a local Postgres DB. There are portable windows binaries.

[noir_lord]

Was that something you rolled yourself or is there an existing solution for it? as it sounds interesting.