I'm not saying that e2e testing is without value, it can be useful... but its just not worth it I've found. I write unit tests only, which don't touch the database. My litmus test is am I testing an "algorithm" -- something that has outputs solely based on its inputs.
If you insist on tests that touch the DB, the speed can be improved by chaining tests instead of resetting the DB everytime (this has its trade offs).
If I need to test a piece of code that generates a dynamic SQL statement, I would simply assert that the correct SQL is generated. I would not need to actually execute the SQL to see that it is correct. The point is to test that my logic generated the correct SQL, not to test that my database vendor implements SQL correctly. The latter would just be caught in manual testing. I like the BDD school of thought that you are writing specs, not tests.
I second this. I worked at a research company that had terabyte-scale SQL databases of curated data, and the tests would take 5 - 8 hours to run because they were verifying query results rather than the queries themselves. This also meant tests would fail because of changes in the data rather than just changes in the code. I rewrote the entire test suite to match SQL statements against a symbolic SQL description, which turned out to be a much more robust and extensible solution.
If you insist on tests that touch the DB, the speed can be improved by chaining tests instead of resetting the DB everytime (this has its trade offs).
If I need to test a piece of code that generates a dynamic SQL statement, I would simply assert that the correct SQL is generated. I would not need to actually execute the SQL to see that it is correct. The point is to test that my logic generated the correct SQL, not to test that my database vendor implements SQL correctly. The latter would just be caught in manual testing. I like the BDD school of thought that you are writing specs, not tests.