4 ms·

Fwiw I personally find this approach very frustrating, b/c when a test fails, all of the test data is gone, so I can't open psql/mysql CLI and look at "what did

Fwiw I personally find this approach very frustrating, b/c when a test fails, all of the test data is gone, so I can't open psql/mysql CLI and look at "what did the data actually look like". Granted, there are pros/cons (you also can't test code that issues txn begins/commits, although that is rare), but personally that particular con outweighs the pros imo/for me.

sverhagen 6y ago
Assuming your tests are repeatable, would you not run it and put a breakpoint at the end?
- stephen 6y ago
  The test running in a transaction would mean my external tools like psql/etc would not be able to see the data b/c it's uncommitted, even if I pause/catch the test before it finishes.
jrockway 6y ago
Yeah, I am not sure that I trust the average database with transactional schema modification or nested transactions. You have to be very careful about your database's settings and choose the correct isolation level. (This is more important for your actual application than whatever your tests do, but the tests are where you're doing weirder things whose implementation details are likely to raise your eyebrow.) Personally, what I do is create a new database at the beginning of the tests, and have the tests use that. There are some downsides; if you use a random database name, you can run as many tests as you like in parallel, but some percentage of test runs will never run the cleanup code and you will have stale test databases around that need to be cleaned up. (You can simply not retain files written by your database after the CI run, of course, but then you can't debug the failing tests as easily.) If you use a fixed database name, then you will only ever have one copy of the data and won't need to clean anything up. But you can't run tests in parallel -- "go test ./..." or whatever does run tests in parallel by default, so you will see conflicts between tests this way. (In the past, I've done things the second way, but upon writing this comment, I would probably do things the first way in the future.) Overall, I strongly agree with the advice to run your tests against an as-real-as-possible database. You detect a lot of issues this way -- queries that don't parse, database settings that affect the results (things as dumb as time zones, for example), and you get actual error codes. For example, I used to run all of my queries in a block that retried them 3 times on retryable errors, and gradually built up a list of non-retriable error codes; "syntax error", "column doesn't have a default", etc. This list is easy to build while directly developing against a database with the same settings as production, but will never work when running against mocks or sqlite. (In theory, your database vendor should provide some client library that does things like this... but nobody does.)

sverhagen 6y ago

Assuming your tests are repeatable, would you not run it and put a breakpoint at the end?

stephen 6y ago
The test running in a transaction would mean my external tools like psql/etc would not be able to see the data b/c it's uncommitted, even if I pause/catch the test before it finishes.

jrockway 6y ago

Yeah, I am not sure that I trust the average database with transactional schema modification or nested transactions. You have to be very careful about your database's settings and choose the correct isolation level. (This is more important for your actual application than whatever your tests do, but the tests are where you're doing weirder things whose implementation details are likely to raise your eyebrow.) Personally, what I do is create a new database at the beginning of the tests, and have the tests use that. There are some downsides; if you use a random database name, you can run as many tests as you like in parallel, but some percentage of test runs will never run the cleanup code and you will have stale test databases around that need to be cleaned up. (You can simply not retain files written by your database after the CI run, of course, but then you can't debug the failing tests as easily.) If you use a fixed database name, then you will only ever have one copy of the data and won't need to clean anything up. But you can't run tests in parallel -- "go test ./..." or whatever does run tests in parallel by default, so you will see conflicts between tests this way. (In the past, I've done things the second way, but upon writing this comment, I would probably do things the first way in the future.) Overall, I strongly agree with the advice to run your tests against an as-real-as-possible database. You detect a lot of issues this way -- queries that don't parse, database settings that affect the results (things as dumb as time zones, for example), and you get actual error codes. For example, I used to run all of my queries in a block that retried them 3 times on retryable errors, and gradually built up a list of non-retriable error codes; "syntax error", "column doesn't have a default", etc. This list is easy to build while directly developing against a database with the same settings as production, but will never work when running against mocks or sqlite. (In theory, your database vendor should provide some client library that does things like this... but nobody does.)