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

51–60 of 110 posts

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

#51

Post by bane »

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's some advantages to using SQLite early on when you're still figuring things out, but once you've nailed down the schema and queries and done some early validation testing, you pretty rapidly run out of advantages and it's just better to go with something like Postgres.

It doesn't really take a lot of discipline or work to switch over, and once you're entering beta candidate territory, that's when features and performance tuning start to take over and that's where something like Postgres starts to shine in comparison.

Even if you keep with the same schema and queries, just moving over to Postgres on a separate box, you'll probably start to see immediate performance improvement and you'll get better scaling performance almost immediately.

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

#52

Post by jeltz »

mhurron wrote: post #47
darkxanthos wrote: post #34

Earlier quoted context omitted.

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

The problem is that it is not jsut the extensions that differ, it is also quite fundamental parts.

* SQLite is dynamically typed.

* The text types differ in meaning between databases (text, varchar, nvarchar, varchar2, ...). I also beleive char works in different ways in different databases, but I do not use blank padded strings so I am not sure.

* Time types and fucntions are very different.

* Oracle and PostgreSQL run at a different default isolation level than MySQL and MSSQL.

* MSSQL has read locks on rows, which can cause deadlocks which cannot happen in other databases.

* Autoincrement is not implemented in the same way in different databases.

* Text collations and text equality works differently in different datbases. (If they care about trailing blanks, case, etc and the order tuples are sorted in.)

And these are jsut the ones I recall on top of my head.

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

#53

Post by mhurron »

jeltz wrote: post #52
mhurron wrote: post #47

Earlier quoted context omitted.

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

The problem is that it is not jsut the extensions that differ, it is also quite fundamental parts. * SQLite is dynamically typed. * The text types differ in meaning between databases (text, varchar, nvarchar, varchar2, ...). I also beleive char works in different ways in different databases, but I do not use blank padded strings so I am not sure. * Time types and fucntions are very different. * Oracle and PostgreSQL…

I am not supprting the position.

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

#54

Post by LoSboccacc »

sirtaj wrote: post #44
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…

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

people get accustomed to do things a certain way, then we upgrade to a newer postgres or a newer whatever everything breaks down and we have to track down dozens of little papercuts.

if anyone has ever tried to upgrade a ruby app to a newer ubuntu release, he should know that building no variance in the deployment environment is a recipe to never upgrade it. (no I'm not using rails currently, I have to build a thing that needs to live more than two years)

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

#55

Post by [deleted] »

empthought wrote: post #49
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.

[deleted]

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

#57

Post by falcolas »

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.

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't accidentally creating silent dependencies on a database state created from a previous test.

On the other hand, when you'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.

Mocking is good for iterative development, and SQLite is a great 80% tool for mocking.

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

#58

Post by DiThi »

pimterry wrote: post #25

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-equ…

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.

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

#60

Post by noir_lord »

DiThi wrote: post #58
pimterry wrote: post #25

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-equ…

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.

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