I was wondering if we ever use prepared statements to optimize common queries and prevent SQL injections?

There are two parts to this answer:

For query optimization, our Postgres driver automatically prepares and caches prepared statements. I haven't actually looked into how well that cache is utilized -- this would be worth looking into. For something like listing repos though, explicitly preparing all variations of the query would likely become untenable given the number of different options we support (all of which change the shape of the query, making the prepared statement unusable).

For SQL injections, sqlf.Sprintf() does actually protect against this. It is not the same as fmt.Sprintf(). It maintains the separation of query text and query variables which we then pass separately to Postgres, which handles the rest.

My comment here was just about not being able to construct a JSONB object safely with the string syntax. SELECT jsonb_build_object('Name', $1::text); is valid, SELECT '{"Name": "$1"}'::jsonb; is not. I didn't want to do something like q := "SELECT '{\"Name": \"" + topic +" \"}'::jsonb" in the application code because that's super vulnerable.

In terms of testing -- am I understanding right that CI automatically runs basic checks against migrations (like for idempotency, running them before starting unit tests, etc.)

Yes! We test that all migrations run, they are idempotent, and they can be re-applied after migrating down. All unit tests run against a fully migrated database. I don't know of any good ways to test "does this index apply to my query" other than doing a manual test on a realistic instance and ensuring the EXPLAIN output looks right -- would be interested in solutions here 🙂