21–30 of 110 posts
What about if you're using an ORM, wouldn't that fix the issues mentioned?
No. It's perfectly possible to create queries via an ORM which work with one database engine but fail in another - whilst not SQLite and Postgres I've managed to do this several times with Entity Framework and Oracle trying to use CROSS APPLY thinking it is connecting to MS SQL Server.
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…
.net Entity Framework has this problematic limitation that you can't check if SQL queries will be properly built without DB - example situation is if you're trying to use not mapped .net method in your SQL-targeting queries. That's a runtime error you can't (yet) test without DB.
I'm cautious enough to believe other ORMs may have similar quirks, only testable with something to query on.
A few notes: - All the concerns listed in this article are addressed by ORMs. - SQLite supports CTE[1] (subqueries). - It's safer to use the exact same setup in development and production, but it's slower for applications with many tests. It's a trade-off and that's all there is to say. [1] http://stackoverflow.com/questions/21819183/how-to-use-ctes-...
If you're writing unit tests to test the business logic of your app, you shouldn't need a database at all. You should write your business logic so that it isn't dependent on a database, so you can really test the business logic and you don't have to mock the database. If you're talking integration tests, then of course you should use an environment as close to production as possible.
In any case, using SQLite when doing TDD, and testing with postgres when you are done implementing is an acceptable trade-off for most use cases.
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-equivalent environment, and to also have a preceding test stage that uses SQLLite instead. By using SQLLite in memory it'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.
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's typically not going to be a complex database incompatibility issue that makes your tests fail. It's going to be the SQL query you wrote 30 seconds ago, or the leftover query that doesn't work at all the table structure you'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.
Quick test stages that catch all the obvious problems early, final definitive production-equivalent test stage that makes sure it'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.
A few notes: - All the concerns listed in this article are addressed by ORMs. - SQLite supports CTE[1] (subqueries). - It's safer to use the exact same setup in development and production, but it's slower for applications with many tests. It's a trade-off and that's all there is to say. [1] http://stackoverflow.com/questions/21819183/how-to-use-ctes-...
Both this facts can bite you in the ass if you are not careful. It's great to run development tests on SQLite, but your CI environment, staging or whatever you have before you push your changes to production should try to mimic production as much as possible.
My experience with testing with Sqlite and Mysql when the customer uses MS SQL is very bad. JDBC only theoretically abstracts the real DB...
(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 untested. And with that said, most of the arguments can be deleted.
(3) When you are using an ORM, most of the arguments are obsolete, too.