I also started using SQLLite for testing (through an ORM), but at some point it became too painful to maintain and didn't find all of our bugs. It's not even any faster than Postgres.
Don't test with SQLite when you use Postgres in Production
81–90 of 110 posts
Re: Don't test with SQLite when you use Postgres in Production
#82While 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 SQLi…
Aside from a few seconds to spin up a new PostgreSQL instance, it should not be any slower than SQLite. We have a wrapper [1] (forked from [2]) that starts a new PostgreSQL instance from Java unit tests. We have the same for MySQL [3]. This allows every developer to automatically run tests against the target database without needing to set anything up.
(The common alternative in Java is to use H2 or Derby, which are similar in concept to SQLite)
1. https://github.com/airlift/testing-postgresql-server
Re: Don't test with SQLite when you use Postgres in Production
#83He claims SQLite doesn't have CTE's but I believe it actually does.
This is a new feature as of 2014. I hadn't looked at SQLite actively since before then
Re: Don't test with SQLite when you use Postgres in Production
#84The 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…
For Java, see my other comment for how to run real PostgreSQL or MySQL from unit tests: https://news.ycombinator.com/item?id=10003789
Re: Don't test with SQLite when you use Postgres in Production
#85While 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 SQLi…
Aside from a few seconds to spin up a new PostgreSQL instance, it should not be any slower than SQLite. We have a wrapper [1] (forked from [2]) that starts a new PostgreSQL instance from Java unit tests. We have the same for MySQL [3]. This allows every developer to automatically run tests against the target database without needing to set anything up. (The common alternative in Java is to use H2 or Derby, which are…
Hmm, I'm actually on PostgreSQL right now using H2, this might come useful. Thanks.
Re: Don't test with SQLite when you use Postgres in Production
#86Earlier 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).
Unfortunately, limiting yourself to only what is available in standard SQL is not practical. For example, doing idempotent inserts, insert-or-update, sequences, transactional behavior for DDL, data types, date/time manipulation, etc.
To paraphrase Tom Kyte from Oracle (Ask Tom): "Your company paid good money for Oracle and all of its features. Use them instead of wasting money reimplementing them poorly yourself."
There are many ways to achieve portability across databases, but limiting yourself to standard SQL is not the best way (at least for everything). And I say this as someone who develops an analytic database (Presto) that tries to follow standard SQL for everything.
Re: Don't test with SQLite when you use Postgres in Production
#87Earlier quoted context omitted.
Aside from a few seconds to spin up a new PostgreSQL instance, it should not be any slower than SQLite. We have a wrapper [1] (forked from [2]) that starts a new PostgreSQL instance from Java unit tests. We have the same for MySQL [3]. This allows every developer to automatically run tests against the target database without needing to set anything up. (The common alternative in Java is to use H2 or Derby, which are…
Hmm, I'm actually on PostgreSQL right now using H2, this might come useful. Thanks.
Just add the Maven dependency and see their own tests for an example:
https://github.com/airlift/testing-postgresql-server/blob/ma...
https://github.com/airlift/testing-mysql-server/blob/master/...
Though you want to use @BeforeClass / @AfterClass or similar rather than creating one for each test.
Re: Don't test with SQLite when you use Postgres in Production
#88Better: 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'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?
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?
Re: Don't test with SQLite when you use Postgres in Production
#89We are mostly PostgreSQL shop and on last few projects I wanted to make it possible to use SQLite for development and testing, but I'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.
Re: Don't test with SQLite when you use Postgres in Production
#90Earlier 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).
Unfortunately, limiting yourself to only what is available in standard SQL is not practical. For example, doing idempotent inserts, insert-or-update, sequences, transactional behavior for DDL, data types, date/time manipulation, etc. To paraphrase Tom Kyte from Oracle (Ask Tom): "Your company paid good money for Oracle and all of its features. Use them instead of wasting money reimplementing them poorly yourself." Th…
> To paraphrase Tom Kyte from Oracle (Ask Tom): "Your company paid good money for Oracle and all of its features. Use them instead of wasting money reimplementing them poorly yourself."
This applies just as well to the open source world. You may not have paid money for PostgreSQL, but you did pay for it in that the time you spend maintaining it could be used for other things. Likewise, your time could be spent reinventing its features in your application or just using those features and spending your time building something better for your customers.