Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

michael.robellard.com

Post reply on HN
Print view View original

41–50 of 110 posts

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

#41

Post by CHY872 »

This is way simplistic. Frequently testing with the real database wastes a tonne of time. I've worked with a team where running a single test with the true database took a minute, and there were 200 such tests.

Thankfully, the only guarantee they needed was that provided by a key-value store, so a ConcurrentHashMap was used on dev machines.

Then the true database was used by the CI server, and commits only occurred when all of the tests passed with the true db.

All of the OP's reasons would (in many cases) pale in comparison to 'My tests take hours to run'.

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

#42

Post by desas »

jbb555 wrote: post #15

No, I will do this. Because it'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. The choices are not good test vs bad test. They are test-with-issues vs. no test. (Obviously you have to do SOME testing with the real DB but this article is talking about unit te…

How is it so much trouble? Install postgres & any extensions you use on your dev/test boxes, this is a one-off cost that takes minutes.

Have your test bootstrapper run "createdb somethingunique" and then "export APP_DBCONN_STR='somethingunique'". Adding this to your bootstrapper will take minutes.

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

#43

Post by jeltz »

mgamer wrote: post #7

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.

We have benchmarked this at our company and notivced that using a ramdisk does not give much extra performance over just turning of synchronous_commit in PostgreSQL. Most of the slowdown from the database turned out to be latency from waiting on the background writer to fsync.

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

#44

Post by sirtaj »

LoSboccacc wrote: post #40
andrewvc wrote: post #33

The comments here are full of people bucking against this advice. I'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're writing code that works. The fact is if you have a…

I am for both: we run a brutaly different dev environment than production: windows, 32 bit, hsqldb, windows codepage against linux, 64bit, postgres, utf8 codepage we also have a beta environment that's a perfect mirror of production down to the vm vendor and package version and an alpha environment that's on a cheaper vendor and uses a more updated version of production os/packages (and has experimental features of o…

What sort of constraints do you have that your dev environment can't be a VM running the same software as production?

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

#45

Post by lkrubner »

I will tell a story that I think is very strange.

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.

But then things began to change.

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.

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.

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.

I could not fathom why we didn't simply use PostGreSQL in both development and production.

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

#46

Post by jeltz »

WatchDog wrote: post #29

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.

PostgreSQL will be close enough to an in memory database if you turn off synchronous_commit. For most test ssuties I would suspect much more time will be spent plannign the queries (which can be improved with prepared staatments) than the time spent on disk IO if you just reduce the durability requirements of your test database.

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

#47

Post by mhurron »

darkxanthos wrote: post #34
andrewvc wrote: post #33

The comments here are full of people bucking against this advice. I'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're writing code that works. The fact is if you have a…

I don't understand the argument against this. I have had even small sites behave differently when moving between Postgres and SQLite. I <3 SQLite but it's not a golden hammer.

> I don't understand the argument against this

Write only pure, portable SQL. Using RDBMS extensions is evil. Enligtenment only comes to those who are pure(ly using SQL).

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

#48

Post by jeltz »

Fudgel wrote: post #2

What about if you're using an ORM, wouldn't that fix the issues mentioned?

Sadly not, since there are many cases where databases act differently. For example SQLite is dynamically typed while other databases enforce the types of columns. An ORM protect against some common cases, but not against all the subtle differences between databases.

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

#49

Post by empthought »

PythonicAlpha wrote: post #30

Some comments on the arguments: (1) SQLite did never claim to be as "complete" as other databases -- it is and will be a "lightweight" database. (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 un…

> When you are using an ORM, most of the arguments are obsolete, too.

At the cost of pretty much everything you get out of using something that isn't SQLite.

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

#50

Post by StavrosK »

sgt wrote: post #38

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

You just made me wonder whether disabling fsync for postgres would make your tests behave non-identically to a database that does fsync, and how much faster they would be. Interesting question...
Post reply on HN