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

71–80 of 110 posts

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

#71

Post by plorkyeran »

dspillett wrote: post #14

This is one of those things that I would hope goes without saying, but obviously doesn'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/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 i…

If the in-memory DB has the same feature set as the real DB, using the in-memory DB for tests should be a single flip you switch, while writing code to generate fake result sets for every tests requires writing all of that code. 90% of the benefits for 1% of the cost.

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

#72

Post by anthonybsd »

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'll buck against this advice any day. I've never used SQLite for mocking but I've used HSQLDB for functional tests of an extensive ORM-derived DAOs that talked to Oracle in production in the past. It was pretty flawless. I.e. when running in Oracle emulation mode HSQLDB was pretty close to the real thing for our purposes. For a few tiny differences we had automatic script that would convert from Oracle schema creation file to HSQLDB "Oracle" schema creation file any time changes to the real schema were introduced.

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

#73

Post by batbomb »

anthonybsd wrote: post #72
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'll buck against this advice any day. I've never used SQLite for mocking but I've used HSQLDB for functional tests of an extensive ORM-derived DAOs that talked to Oracle in production in the past. It was pretty flawless. I.e. when running in Oracle emulation mode HSQLDB was pretty close to the real thing for our purposes. For a few tiny differences we had automatic script that would convert from Oracle schema creati…

I was going to say this too: HSQLDB is more suitable, at least for me, testing environment than SQLite.

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

#74

Post by drdaeman »

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…

Seems that someone's running wild, downvoting every comment they disagree with.

I believe neither maintaining a code that's compatible with multiple databases, nor using a single one is a mistake per se. Whatever rocks your boat is the attitude - one just needs to be aware of consequences (both good and bad ones) of chosen path.

But, yeah, the linked article's warning is completely valid - testing on a single platform, then deploying to another is likely to encounter some issues one day.

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

#75

Post by mangeletti »

I've always preferred this (using Django):

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.).

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 / restarts). Separate dev accounts will of course be used for things like APIs, email, etc.

3. Production (Heroku).

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.

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

#76

Post by LoSboccacc »

drdaeman wrote: post #74
LoSboccacc wrote: post #40

Earlier quoted context omitted.

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…

Seems that someone's running wild, downvoting every comment they disagree with. I believe neither maintaining a code that's compatible with multiple databases, nor using a single one is a mistake per se. Whatever rocks your boat is the attitude - one just needs to be aware of consequences (both good and bad ones) of chosen path. But, yeah, the linked article's warning is completely valid - testing on a single platfor…

> I believe neither maintaining a code that's compatible with multiple databases

me too! but there are soo many compatibility layers you can get and bolt on for free and they also work around version issues and things like that... of course if you need the latest version then you're in a different boat, but the generic problem of talking to a database is basically solved by now

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

#77

Post by acveilleux »

mangeletti wrote: post #75

I've always preferred this (using Django): 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 triv…

This makes sense when you use an ORM that will transparently work. You trust the ORM to insulate you. If you have needed to go direct to SQL for something then you're looking at putting in test-specific code in your production code to simulate/fake/disable the custom SQL bit...

Just run postgres on a ramdisk, or on an SSD with sync disabled and a scripted setUp/tearDown.

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

#78

Post by acveilleux »

XorNot wrote: post #69
serve_yay wrote: post #66

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

In the age of containers this able thing is baffling to me. I have a couple of things that need postgres in production, and a single script which launches a two docker containers with tmux running postgres and apache with live scrolling logs, and which binds the postgres port to my local host. I simply cannot understand where the friction on this is existing today. Even pre-docker its fairly easy to setup any of the…

Plus with a few tweaks to postgres relaxing ACID compliance (i.e.: no sync) and using an SSD or better a ramdisk for storage performance will be fantastic for unit tests.

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

#79

Post by acveilleux »

empthought wrote: post #63
[deleted] wrote: post #55

Earlier quoted context omitted.

[deleted]

I don't know what you're talking about; is there even a downvote button on Hacker News? I certainly don't see one. Some features that ORMs make unnecessarily difficult (or impossible without simply avoiding the use of the ORM) to use, off the top of my head: * transactional DDL * temporary tables * user-defined data types * asynchronous notifications * dynamic SQL * non-serial keys * multi-attribute keys * temporal s…

To answer the downvote tidbit, there is and it shows up when your Karma is high enough. That threshold is not disclosed however.

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

#80

Post by rbanffy »

While I agree testing well is crucial, running your tests on one RDBMS that's fast and running them less frequently (think "before merging into production") is a good compromise. PostgreSQL is slower here than SQLite and if your tests take 20 minutes to run, your developers won't test as often as you would like them to.

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't) making your app more "robust" (you are using an ORM for a reason, right?).

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't move to anything else without extensive rewriting. Now, when the workload changes, you can'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's not the optimal solution for all problems.

Post reply on HN