I came across some unit tests at work the other dayyyyyy. They were checking that various queries built using the SQLAlchemy session query builder were generating the appropriate SQL query strings. A contrived example is below:

from db.models import User

def test_query(session):
    query = session.query(User).filter(email="test@example.com")

    assert query.sql == "SELECT user.id, user.name, user.email WHERE user.email = 'test@example.com'"

A couple of things to note about this query:

Now, hopefully your spidey-sense tingled about the use of compiling the query using literal_binds before sending it to the database - this is potentially vulnerable to an SQL-injection attack (which SQLAlchemy themselves are very clear about)! In this particular case there was very low risk of that happening, but we rectified it anyway to use the proper query with bound parameters. But even with that fixed, is this still a good unit test?

I'm genuinely not sure. Some things I don't think are good about it:

But there are some things I think are useful about it too:

In summary I suppose that on its own this isn't a very useful test, but paired with an integration test it's good?

Addendum - since I'm going on about it, here's some other random thoughts on tests in no particular order: