Here are some of the different strategies you can use to write tests that read or write data from a database, and the benefits and drawbacks of each. I'm usually writing Go code, so the examples here are for Go, but the notes should generalize to any language that runs tests against Postgres, MySQL, or any other database.

At a high level my goal is for the tests to take a minimum of programmer time. Programmer time is spent on the tests in a few different places:

• Writing tests.
• Running the tests.
• Troubleshooting test flakes.
• Troubleshooting written tests that don't behave the way you expect.

Our goal is to minimize the amount of time spent across all four of these areas, from which I have the following principles for a test suite:

• Tests should be fast. Each test should do only what it has to do, which for database tests primarily means it should only create the objects it needs to test the behavior it needs to test, and no more.

• Tests, and test packages, should run in parallel, which usually helps the test suite complete more quickly.

• Tests should be predictable; if tests return unpredictable results, or interact in bad ways, this leads to wasted time tracking down why the tests are failing.

Obviously these are in conflict - parallel tests are less predictable than sequential tests - but there are some things we can do to make tests fast and predictable, and some common techniques that make them slower and unpredictable.

Don't: Reuse the same fixture objects in each test

You might have a set of "standard" test objects that get written to the database — Alice and Bob, and maybe Alice is a power user and Bob just signed up. If you try to create them in a test and they exist in the database already, the tests will upsert them.

This is fine, and you can do it, but it tends to break down in two ways:

• "Default" fixtures tend to accumulate new database objects any time you create a new table, like a snowball rolling downhill. This means any new table makes all of your tests slower.

• If you run tests in parallel, any test that updates or deletes any fixture that other tests rely on can cause unexpected behavior.

Because we are optimizing for programmer time - fast tests, and no unexpected behavior during tests - I tend to want each test to create the objects it needs, and make them each unique.

Don't: Reuse the same set of ~500 names

When you use a library like "Faker" to create objects that are sort of similar but have a common set of variations, for example, a last name derived from the same set of 500 last names, you are bound to run into trouble. It's way too easy to accidentally create two test objects that have the same birthday or same last name, when you didn't expect that to happen, and as a result, end up with one result when you wanted two, or otherwise get test failures that only occur once every 500 runs.

I've previously discussed why Faker is a bad idea, read the post for more.

Do: Use UUID's for anything that needs a "random" or unique input

Instead of creating "Alice" with 1 of 500 pre-seeded last names, or always with the email address "alice@example.com", use UUID's (or a part of a UUID), anywhere you need unique data. So "Alice Jones-a9706630", with email "alice-7cce0183@example.com". The first 8 characters of a UUID have 4294967296 different values; these objects will not conflict with each other during your lifetime.

Don't: Truncate the database after each test

At the end of each test, you could issue a command to truncate or delete all database tables. So each test is something like:

t.Run("TestAccountCreate", func(t *testing.T) {
    assertEquals(t, user.ID, 7) // or whatever
    cleanup(t)
})

The advantage to doing this is that each test cleans up after itself - you can create the same objects in multiple tests even if they might fail a uniqueness constraint, since it gets destroyed between each test instance. The disadvantage to doing this is that you can't run tests in parallel, because one test might issue a truncate right when the other test is writing to the database.

Further, it's not uncommon for tests to hang, or crash, while they run. If that happens, your truncate-after-test runner will not actually run, and the next test will encounter an inconsistent state. These types of failures can be tough to troubleshoot because the failure was actually in the previous test, which your test output might not make clear.

Don't: Truncate the database before each test

This has most of the benefits and drawbacks of the previous section, except you need to truncate everything in the database, because you don't know what test ran before this one. In theory, if you delete after, you only need to delete from tables that you wrote to.

Don't: Truncate the database after each package

You could enclose all of the tests in a parent test, something like this:

t.Run("Package", func(t *testing.T) {
    setUp(t)
    t.Run("Test1", func(t *testing.T) {
    })
    t.Run("Test2", func(t *testing.T) {
    })
    t.Run("Test3", func(t *testing.T) {
    })
    cleanup(t)
})

This way, you only pay the penalty of database setup and cleanup once per package.

However, the default for Go tests is to run as many package tests in parallel as you have CPU's available. So one package may be just finishing on one CPU (and truncating the database) when another package is starting on a different CPU, which causes failures that are hard to troubleshoot. You can set -p 1 to disable package parallelism, but the test suite gets a lot slower if you can only run one package's tests at a time.

Don't: Truncate the database before each package

This has most of the benefits and drawbacks of the previous section, except you need to truncate everything in the database, because you don't know what test ran before this one.

Maybe: Run all tests in their own transaction, then roll it back

The idea here is that you start a transaction in your test suite, then you run the application code, then you invoke a ROLLBACK in the test suite, restoring the database to its pristine state. The application code needs a pointer to the transaction, so the objects it creates exist inside of that.

Conceptually, there's nothing wrong with this method, but if you have any application code that starts a transaction, you will get in trouble with Postgres and MySQL, and you may not have a great time with other database flavors. In theory, you could make BEGIN and COMMIT a no-op in application code called from tests, but you are adding a lot of complexity there and the cost starts to outweigh the benefit.

Do: Avoid truncating the database at all

If each test is creating objects that it needs, and those objects don't conflict with anything else, you basically don't need to truncate the database ever. Sure you can add a command to clean it out if it gets too unwieldy, but you don't need to automatically do it between test runs.

This method is very consistent - tests behave the same way every time, they write the objects they need to the database. It also allows you to run all of your tests in parallel.

This requires a small change to the way you write tests - you can't make any assertions about the total number of objects in a given table, for example. If you want to test upserts by trying to write two records and then running SELECT COUNT(1) FROM table and ensuring only one record exists, that will fail because all of your tests are going to be writing to that table simultaneously. Instead, check for the number of records scoped to an account that's created for that test, or similar.

Maybe: Run each test in its own schema

Before running each test, copy the schema from a 'default' schema, and then run the test against the new schema. When the test completes, drop the schema. There's an example function to clone schema available on the Postgres wiki.

This is certainly a technique that provides you full isolation for each test, which allows you to run tests in parallel and ensure each test has a clean database. However, you have to pay the schema clone cost for each test, plus you have to create a new connection for each test. In my experiments on a 2.5 GHz core i7 Macbook Pro, cloning the database schema took about 200ms, and cloning the entire database took about 400ms. To me, these are too high of startup costs to pay to run a single test.

You could imagine having a background job with the sole purpose of creating enough schemas that you don't have to incur the 200ms latency when you actually run the tests - you could just find one of the already created schemas, though you'd need to wipe created schemas when you ran a migration, and that's getting pretty complex.

Assorted notes

• If each test is creating the objects it needs, and tests don't rely on a clean database state, then you should be able to invoke t.Parallel() at the top of each database test. t.Parallel() will truly try to run each test in parallel, so you may have too much parallelism, but that's a better problem to have than too little. Experiment with the number of connections you have open to the database (or add benchmarks) to find a number that makes the most sense.

• Prepared queries run about 30-40% faster than normal (fast) unprepared queries, because you don't have to pay the overhead of parsing the query statement. The Go *sql.DB object is thread safe, so you want to reuse it across all of your tests, instead of creating a new one in each test and paying the overhead to establish a connection to the database.

  A library like sqlc will help make it easy for you to get prepared queries.

Thanks to Phips Peter, Kyle Conroy, Alan Shreve, and David Strauß for reviewing drafts of this post.

Liked what you read? I am available for hire.

It is difficult for children, especially young children, to learn over Zoom. It is more difficult to teach critical skills like learning to read and write over Zoom. As Heather Knight and others have noted, keeping children isolated has severe impacts on their mental health.

It is the Mayor's top priority to get children back in schools. It is inexcusable that the San Francisco School District Board has not gotten children back in schools. The children who are most harmed by this failure are low income children in the Southeastern neighborhoods. Every single candidate for School Board last fall highlighted the importance of equity but has been unwilling to do what it takes to reduce inequality on this issue, the biggest one they will face while elected - get children back in schools so they can learn to read.

Even if you are not a parent, you should be interested in this issue. Who is going to want to move to San Francisco if the school system is not interested in educating students? If I had school age kids I would be looking for anything to get my kids back in school and doing things that are normal for kids to do. Keeping kids at home is not good for the community.

There are seven members on the SFUSD School Board. Four Board members were just elected in November and cannot be recalled until May 3, 2021. The other three can be recalled immediately. To force a recall election, you need to collect the signatures of 10% of the city's voters, around 51,000 votes.

Every recall election has signatures thrown out because they are signed by people who aren't residents or signatures or addresses don't match. So you probably need to collect twice as many signatures - around 95,000 signatures. There are about 515,000 voters in San Francisco, and around 100,000 parents of students in the SFUSD school system.

You can either get volunteers to collect signatures door to door or pay signature collectors about $8 to $15 per signature, or a mix of both. So you are looking at around $76,000 to $1,400,000 for a fully professional recall campaign. I can get you in touch with people who can collect signatures.

That said, you may not need to collect all the signatures. The goal is to put political pressure on the Board. The threat of a recall election was enough to get Gavin Newsom to change his behavior, and it may be enough here as well.

Liked what you read? I am available for hire.

In very rare cases, the Pfizer and Moderna vaccines will cause the person being vaccinated to have an allergic reaction. When I say very rare, I mean it; the chances are a few in a million, or about the same of picking a specific resident of Newark, New Jersey at random out of the phone book.

Because of this chance, the pharmacy/hospital/vaccination site will ask you to wait around for 15 minutes after getting the shot so they can check whether you have an allergic reaction. Most places (scroll through the list on Vaccinate CA) administering the shot are doing so indoors in windowless rooms. People - right now, seniors and others with high exposure to COVID - are being asked to wait for 15 minutes in crowded waiting rooms.

However - waiting in a cramped room indoors is exactly how COVID spreads! Sure, most people are probably wearing masks. But the new B.117 variant is more dangerous and, anecdotally, can get past a cloth mask much more easily. Right after getting the vaccine, but before it has kicked in, we are asking people to huddle in basically the ideal location for transmitting COVID, all to avoid a miniscule risk of an allergic reaction. Not only is this extremely dangerous but it's a huge waste of vaccine - if we know you are going to get COVID today, we shouldn't give you protection against COVID starting a week from now.

The risk of not spotting someone who has an allergic reaction must be weighed against the risk of transmitting COVID. Right now about 3% of the US population is infected with COVID. So about 1 in every 30 people in a vaccination waiting room (likely higher due to selection effects) will be infected with COVID and transmit it to others. About 1-2% of people who get COVID will die from it and more will be hospitalized. Contrast this with about a 1 in ~180,000 risk of an allergic reaction. It's just not comparable.

If you are offered the vaccine, and the waiting area is indoors, I would urge you to decline to wait. Explain that waiting indoors with other unvaccinated¹ people is not safe, and then wait outside. You can call someone on the phone for 15 minutes who can monitor you for side effects. Or, walk back in after 15 minutes, tell the pharmacist you are OK, and then leave.

You are not breaking the law by doing this and you are aware of the risks. The more people that choose to do this, the more pressure we can put on vaccinators and public health agencies to end this dangerous practice, and offer waiting areas that are well spaced outdoors.

So many people are dying every day and a vaccine is so close now that small changes will have a huge impact on the total number of people hospitalized and dead. Please share this post with someone you know who is getting vaccinated soon.

Thanks to Michael Story for bringing this issue up.

^1. The vaccine takes a week to kick in so for all intents and purposes you are still unvaccinated minutes after you have received the shot.

Liked what you read? I am available for hire.

Edit: If you are in a desperate situation, or getting rejected multiple days, please call your State Assemblymember. Staffers in the Legislature have access to additional resources and can help you get the assistance you need from EDD.

The California EDD Paid Family Leave phone tree is like a choose your own adventure book, where almost every option leaves you with no option to contact a human. This can be frustrating. But you can reach a human if you know the right buttons to press!

Here is how to reach a human:

• Call the EDD Paid Family Leave number at 877-238-4373.

• Press '1' for "benefit information."

• Follow the prompts to enter your SSN, zip code, date of birth, and weekly benefit amount.

• The computer will read you an automated list of information about your claim.

The computer will then read a list of prompts. Wait!! After the computer asks if you want to go back to the main menu it will say "press 0 to speak to a human." Press 0 and then wait and you should get a human!

Liked what you read? I am available for hire.