Don't test with SQLite when you use Postgres in Production
michael.robellard.com
Don't test with SQLite when you use Postgres in Production
1–10 of 110 posts
Re: Don't test with SQLite when you use Postgres in Production
#2What about if you're using an ORM, wouldn't that fix the issues mentioned?
Re: Don't test with SQLite when you use Postgres in Production
#3What 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.
Re: Don't test with SQLite when you use Postgres in Production
#4What about if you're using an ORM, wouldn't that fix the issues mentioned?
Good question but not necessarily. I worked for a company who did this with SQL CE in the integration environment and normal SQL server in production with NHibernate as an ORM. Turns out there are some subtle differences that can creep through the ORM abstraction including the extensive dialect abstraction in NH.
Re: Don't test with SQLite when you use Postgres in Production
#5What about if you're using an ORM, wouldn't that fix the issues mentioned?
Absolutely not! Heck with Hibernate for example you can't write a simple inner join on a non id value;
For example this query is invalid with HQL
SELECT p FROM person p INNER JOIN Invitation i ON i.email LIKE p.email
And that is one of MANY gotchas. Hibernate has it advantages though, especially when it comes to developer productivity. But JDBC is needed for some edge cases.
I often use Groovy SQL instead of using JDBC, comes with excellent transaction support and helps simplifying you DB specific code so it makes sense for newcomers too.
Re: Don't test with SQLite when you use Postgres in Production
#6It would be nice if you could embed it and run in memory so it's as convienent.
And yes, you can do CTEs in SQLite but that's besides the point.
Re: Don't test with SQLite when you use Postgres in Production
#7I 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.
Re: Don't test with SQLite when you use Postgres in Production
#8I reckon it's okay to do this in the right circumstances:
- It's a relatively simple app - You're using an ORM - You aren't using any advanced SQL features - It's only to make local development easier - There's still a full CI test run with your production database
That said, if your test suite is large enough that database performance is an issue during testing then either your app is too complex for the above to apply, or you are probably doing something else wrong.
Re: Don't test with SQLite when you use Postgres in Production
#9What about if you're using an ORM, wouldn't that fix the issues mentioned?
Unfortunately not.
An ORM may allow you to pass through queries or other directives that have DBMS specific behaviours so if you use any features like that the ORM can't protect you at all. Further more the ORM might change the way it talks to the underlying DB depending on what it is, to make use of efficiency enhancing features that one DB has but others may not, again you are not testing like-for-like in this case. The ORM itself may have bugs that are only apparent when exposed to a given DB.
An ORM often protects you from needing to know the specifics of the storage engine underneath, but you still need to test against the same storage engine(s) as you expect to see in production.
Re: Don't test with SQLite when you use Postgres in Production
#10Starting Postgres locally for unit testing is quite easy and done with a few commands (e.g. Powershell)
https://gist.github.com/tobiasviehweger/cbfd9a1a55bff0862f9e