Don't test with SQLite when you use Postgres in Production

Better: Don&#x27;t test <i>only</i> with SQLLite when you use Postgres in Production.<p>His points are all valid, you definitely shouldn&#x27;t release something to production that you haven&#x27;t tested thoroughly in a separate identical environment.<p>That doesn&#x27;t mean you should never test with SQLLite though.<p>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&#x27;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.<p>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&#x27;s typically not going to be a complex database incompatibility issue that makes your tests fail. It&#x27;s going to be the SQL query you wrote 30 seconds ago, or the leftover query that doesn&#x27;t work at all the table structure you&#x27;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.<p>Quick test stages that catch all the obvious problems early, final definitive production-equivalent test stage that makes sure it&#x27;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.

The comments here are full of people bucking against this advice. I&#x27;ve worked at TWO companies now where people said NO we want to use SQLIte in dev. Both switched in under a year despite fierce internal opposition. Everyone had changed their tune once they hit growth in users and complexity. Why? Because being ideologically right is not as nice as being sure you&#x27;re writing code that works.<p>The fact is if you have a serious site there&#x27;s a good chance SOME specific feature will start looking appealing ( maybe array values, JSON , window functions, a pg extension...). These features are written because people use them, we aren&#x27;t all writing dead simple CRUD apps. Or you your ORM will interact with one slightly differently than the other. I don&#x27;t expect to convince any of you doubters, but the first time you get a bug in prod that slipped through the testing process but really could have been caught in dev, you&#x27;ll ask yourself why you weren&#x27;t using pg there too.<p>There&#x27;s no sane reason to complicate your life by running two databases unless you either have a dead simple app, or are one of the jabbering idealists I see on here. Productive programmers simplify the problem and minimize their tech stacks.

I will tell a story that I think is very strange.<p>Last year I worked at a small startup that was focused on medical records. They used PostGres in production, but SQLLite in development. The frontend was pure AngularJS. They had a massive Python code base for pulling data from the database, turning it into JSON, and then sending it to the frontend.<p>But then things began to change.<p>PostGreSQL gained the ability to handle JSON natively. We began the preliminary process of switching over to use that ability. It would mean less work for the Python code.<p>Here is where it got strange: some of the team was so deeply committed to using SQLLite, that they began hacking SQLLite to add the ability of SQLLite to handle JSON natively. That is, any feature that PostGreSQL had, which we used, they wanted SQLLite to have.<p>On the one hand, the technology they developed was amazingly cool. On the other hand, it was a massive effort that had nothing to do with the goals of the startup.<p>I could not fathom why we didn&#x27;t simply use PostGreSQL in both development and production.

This is one of those things that I would hope goes without saying, but obviously doesn&#x27;t... Always test against what you expect to see in production. If you test against something else first (in this case mocking through an in-memory DB) to make the testing of other parts faster&#x2F;easier then that is fine, but once those tests are done you still need to do a final full test against the real stack(s) you expect to see in production.<p>On the mocking thing: I thought the point of that was to completely avoid DB calls for speed when testing other logic layers, so even a fast in-memory DB isn&#x27;t needed and anything (i.e. just some code that manufactures a response object) that produces a result set in the right format will do? In that case even using an in-memory DB is less efficient than the other option so is at best a &quot;half optimisation&quot;. Am I missing a point here?

I&#x27;ve always preferred this (using Django):<p>1. Local development is done with the simplest of everything (local memory cache, SQLite3 database, console based email backend, local static file storage (vs S3, etc.)). The result is that there is very little overhead and everything is easy to work on and test quickly. This also gives me the ability to quickly wipe out things locally (erase and make new migrations for trivial changes, delete the database file, clear cache more quickly by just restarting the Django server, etc.).<p>2. Final testing takes place on a staging server (Heroku), which is free, and which can contain all the production-level backing services that production will have. This server will be treated like production (no in-place hot-fixes and database wipes &#x2F; restarts). Separate dev accounts will of course be used for things like APIs, email, etc.<p>3. Production (Heroku).<p>This gives me the best of both worlds; the simplicity of local development with simple backing services, with the comprehensiveness of a staging server with better parity.

We are mostly PostgreSQL shop and on last few projects I wanted to make it possible to use SQLite for development and testing, but I&#x27;ve fount that it is simply not possible. not because there are things that work on sqlite and break badly on PostgreSQL, but other way around. Just introducing Boolean or DateTime column with server_default into your SQLalchemy model causes that you can not use SQLite.

Postgres is relatively light weight. We use Postgres heavily in integration tests and it&#x27;s quite fast. I don&#x27;t see that SQLite would speed things up significantly other than causing other potential issues due to it being a different database.

I use MySQL in my system but the advice should be applicable to Postgres as well: keep your test database in a ramdisk. By moving my MySQL to ramdisk I got almost a tenfold improvement when running tests on a build server. Not so much (but also significant) improvement when running tests on my development machine.

I tend to abstract away my queries using database views. This way the query in application becomes very simple (select * from view where $simplepredicate).<p>In my test database (sqllite,hsqldb,derby,...) i create actual tables containing test data with the same definition as the views.<p>This allows me to have rather complex queries that are database specific and still have fast running database queries.<p>The views themselves i test separately. They are also easier to fix in production than code releases, and can also be replaced by materialized views if necessary.<p>Inserts&#x2F;updates are typically simple too.

NASA rule of thumb I read somewhere.<p>&quot;Test what you fly. Fly what you test.&quot;

A few notes:<p>- All the concerns listed in this article are addressed by ORMs.<p>- SQLite supports CTE[1] (subqueries).<p>- It&#x27;s safer to use the exact same setup in development and production, but it&#x27;s slower for applications with many tests. It&#x27;s a trade-off and that&#x27;s all there is to say.<p>[1] <a href="http:&#x2F;&#x2F;stackoverflow.com&#x2F;questions&#x2F;21819183&#x2F;how-to-use-ctes-with-update-delete-on-sqlite" rel="nofollow">http:&#x2F;&#x2F;stackoverflow.com&#x2F;questions&#x2F;21819183&#x2F;how-to-use-ctes-...</a>

Testing with a different database would only be good if you:<p>a) Also test with your real production database b) You restrict yourself to using the lowest common feature denominator of both databases (which is probably a pretty low figure)<p>Still, different databases behave differently in many aspects. And despite how cool SQLite is, PostgreSQL is so much more advanced (specially in SQL querying) that I don&#x27;t see the point losing all those features. As I have already mentioned in HN, check this amazing presentation: <a href="http:&#x2F;&#x2F;use-the-index-luke.com&#x2F;blog&#x2F;2015-02&#x2F;modern-sql" rel="nofollow">http:&#x2F;&#x2F;use-the-index-luke.com&#x2F;blog&#x2F;2015-02&#x2F;modern-sql</a> before deciding to restrict yourself to a subset of the SQL supported by PostgreSQL.<p>Given that it&#x27;s easy to start PostgreSQL from a unit test, and how lightweight PostgreSQL is, I see no real point in using SQLite for testing. Use PostgreSQL!

On the project I work on, we face a similar problem. We use H2 for an in memory test database, but now we&#x27;re frequently running into the problem where we&#x27;d love to use a specific postgres feature (say, json support) that isn&#x27;t supported by H2.<p>That, and occasionally we find syntax differences that cause a headache when doing a database setup&#x2F;teardown. A single minor SQL difference requires us to create separate H2&#x2F;Postgres flyway configurations.<p>I think a better option than H2 or SQLite, that we&#x27;re currently investigating, is using Docker to bring up a local postgres instance for testing.<p>(All of that assumes a certain dedication to using Postgres. If you want to be database agnostic, then you may in fact be better off not using Postgres in dev&#x2F;test just to force yourself to remain compatible with other DBs.)

I agree. Bite the bullet and learn to work with Pg in your dev environment. The dev ergonomics aren&#x27;t worth all the other stuff listed here. Think about all the time you&#x27;ll spend reasoning about the differences between the two. This has nothing to do with your project.

Starting Postgres locally for unit testing is quite easy and done with a few commands (e.g. Powershell)<p><a href="https:&#x2F;&#x2F;gist.github.com&#x2F;tobiasviehweger&#x2F;cbfd9a1a55bff0862f9e" rel="nofollow">https:&#x2F;&#x2F;gist.github.com&#x2F;tobiasviehweger&#x2F;cbfd9a1a55bff0862f9e</a>

I&#x27;ve run Linux as my desktop for a long time, so I&#x27;ve always found it natural to set up my desktop as closely matched to the deployment server environment as possible. It&#x27;s very simple to set up MySQL on Debian or Ubuntu, and only slightly more trouble on OSX.<p>Using SQLite would actually be more trouble and I&#x27;ve never seen a need to use it all for development. Any time I&#x27;ve tried to use SQLite, it&#x27;s been pointless as some of my queries written to run on MySQL or Postgres fail due to lack of support for some feature or another. And I already have plenty of MySQL and pg running on my systems, so...

We&#x27;re using Vagrant for developers&#x27; environment (with mongodb&#x2F;redis&#x2F;rabbitmq&#x2F;consul.io)<p>I can&#x27;t recommend it enough. Maybe a local memory database could be faster... until your environment grows beyond just a db

Extending his argument further. Strive to have a local environments that is as close to production as possible. Use Memcache in production? Use Memcache for dev&#x2F;testing too! Use Elasticsearch in production? Use Elasticsearch in dev&#x2F;testing!<p>This is all really simple to setup to. Think about how a new developer gets up to speed and starts coding in your company. Does it involve downloading and installing Postgres.app[0]? If it does it&#x27;s no good. Starting a local environment should require a single command. `vagrant up` is one option if you use vagrant. Local environments should ideally use the same method of provisioning as production servers. It can however be simpler, one of our codebases has a bootstrap.sh file that sets everything up, it works surprisingly well. No version conflicts, no weird bugs due to slightly varying versions for different developers and no fake service such as SQLLite.<p>For the life of me I can&#x27;t understand the test speed issues that people talk about. We have a pretty small codebase with some 2k test(94% coverage). That takes about 6-7 minutes to run inside a vagrant VM using all the same services as production. 6-7 minutes is a long time, but you shouldn&#x27;t be running your whole test suite during development. During development only run the one or two tests that are relevant for the feature you are building or the bug you are fixing. These are typically really fast even with a proper database. The whole test suite should be ran before pushing and by CI. If your database is still too slow look at using a RAMdisk or tweaking the configuration like people have suggested in the comments<p>0: <a href="http:&#x2F;&#x2F;postgresapp.com&#x2F;" rel="nofollow">http:&#x2F;&#x2F;postgresapp.com&#x2F;</a>

While I agree testing well is crucial, running your tests on one RDBMS that&#x27;s fast and running them less frequently (think &quot;before merging into production&quot;) is a good compromise. PostgreSQL is slower here than SQLite and if your tests take 20 minutes to run, your developers won&#x27;t test as often as you would like them to.<p>It also prevents you from doing things that only one RDBMS does (I was bitten by this because SQLite supports timestamps with timezone data and, at that time, the MySQL we used in production didn&#x27;t) making your app more &quot;robust&quot; (you are using an ORM for a reason, right?).<p>Imagine you only test your app in PostgreSQL and, because of that, your app makes assumptions about the environment only PostgreSQL satisfies. You simply can&#x27;t move to anything else without extensive rewriting. Now, when the workload changes, you can&#x27;t just change your storage backend to one that suits your workload. You need to make PostgreSQL do whatever your new needs are. PostgreSQL is probably a good enough solution, but it&#x27;s not the optimal solution for all problems.

No, I <i>will</i> do this.<p>Because it&#x27;s so much easier and it still lets me test 95% of my code. And the alternative is not testing at all because there is limited time for testing and setting up a proper database for this is so much more trouble.<p>The choices are not good test vs bad test. They are test-with-issues vs. no test.<p>(Obviously you have to do SOME testing with the real DB but this article is talking about unit tests (or related))

If you&#x27;re writing unit tests to test the business logic of your app, you shouldn&#x27;t need a database at all. You should write your business logic so that it isn&#x27;t dependent on a database, so you can really test the business logic and you don&#x27;t have to mock the database.<p>If you&#x27;re talking integration tests, then of course you should use an environment as close to production as possible.

SQLite is awesome for rapid prototyping and proofs of concept work. But you should pretty much always move to a real RDBMS once you start nearing beta. There&#x27;s some advantages to using SQLite early on when you&#x27;re still figuring things out, but once you&#x27;ve nailed down the schema and queries and done some early validation testing, you pretty rapidly run out of advantages and it&#x27;s just better to go with something like Postgres.<p>It doesn&#x27;t really take a lot of discipline or work to switch over, and once you&#x27;re entering beta candidate territory, that&#x27;s when features and performance tuning start to take over and that&#x27;s where something like Postgres starts to shine in comparison.<p>Even if you keep with the same schema and queries, just moving over to Postgres on a separate box, you&#x27;ll probably start to see immediate performance improvement <i>and</i> you&#x27;ll get better scaling performance almost immediately.

My two cents on this topic - as usual, the answer is neither black nor white. At the end of the day, an in-memory SQLite database works very nicely as a stub for most standard SQL queries as part of a unit test mocking system.<p>It allows you to create, populate, test, and teardown an entire relational DB in hundredths of a second, which makes it ideal for unit tests where you want to clean the slate between tests to ensure that you aren&#x27;t accidentally creating silent dependencies on a database state created from a previous test.<p>On the other hand, when you&#x27;re done doing your Red-Green-Refactor cycle for a new feature, you want to immediately run integration tests, which will exercise your program against not only your production model database, but the other APIs you had mocked out for regular unit testing.<p>Mocking is good for iterative development, and SQLite is a great 80% tool for mocking.

He claims SQLite doesn&#x27;t have CTE&#x27;s but I believe it actually does.

It would be nice if you could embed it and run in memory so it&#x27;s as convienent.<p>And yes, you can do CTEs in SQLite but that&#x27;s besides the point.

I also started using SQLLite for testing (through an ORM), but at some point it became too painful to maintain and didn&#x27;t find all of our bugs. It&#x27;s not even any faster than Postgres.

This depends on what the other options are. If you have<p>* test with SQLite<p>* test with Postgres<p>then using the same environment as production (Postgres) is of course better, but if the real options are<p>* test with SQLite<p>* don&#x27;t test<p>SQLite is much better!

My experience with testing with Sqlite and Mysql when the customer uses MS SQL is very bad. JDBC only theoretically abstracts the real DB...

What about if you&#x27;re using an ORM, wouldn&#x27;t that fix the issues mentioned?

Can we all go through and s&#x2F;SQLLite&#x2F;SQLite? The inconsistency in the article and the comments is overwhelming.

Really??!!

I reckon it&#x27;s okay to do this in the right circumstances:<p>- It&#x27;s a relatively simple app - You&#x27;re using an ORM - You aren&#x27;t using any advanced SQL features - It&#x27;s only to make local development easier - There&#x27;s still a full CI test run with your production database<p>That said, if your test suite is large enough that database performance is an issue during testing then either your app is too complex for the above to apply, or you are probably doing something else wrong.

So the alternative is to mock out every database call or use a full database to run the tests. Using an in-memory database is convenient, keeps your tests portable and most importantly it helps catch a lot of bugs that might of been missed with mocking. It might not be as good as using the real DB, but its better nothing.

Some comments on the arguments:<p>(1) SQLite did never claim to be as &quot;complete&quot; as other databases -- it is and will be a &quot;lightweight&quot; database.<p>(2) Everybody with marginal knowledge of different databases should know, that using different databases always puts you on risk and needs extra testing. You would also not recommend to develop your application on Linux, use a crosscompiler and ship the product on Windows untested. And with that said, most of the arguments can be deleted.<p>(3) When you are using an ORM, most of the arguments are obsolete, too.

This is way simplistic. Frequently testing with the real database wastes a tonne of time. I&#x27;ve worked with a team where running a single test with the true database took a minute, and there were 200 such tests.<p>Thankfully, the only guarantee they needed was that provided by a key-value store, so a ConcurrentHashMap was used on dev machines.<p>Then the true database was used by the CI server, and commits only occurred when all of the tests passed with the true db.<p>All of the OP&#x27;s reasons would (in many cases) pale in comparison to &#x27;My tests take hours to run&#x27;.