Foreign keys and link tables in SQL Alchemy

Foreign keys are surprisingly difficult to define as where as normally a basic Foreign Key is unidirectional with a parent and a child relationship SQL Alchemy often needs you to define the attribute on both models in a circular dependency that it then has to resolved by using strings to define the object relationships as Python doesn’t support forward references yet.

Indexed Constraints in Postgres

Primary Keys and Unique Constraints both generate associated indexes automatically but foreign keys, while they need to reference indexed columns do not automatically have an associated index and potentially don’t need them until your query planner tells you that is the bottleneck. I found this last idea a bit counter-intuitive but on reflection I think it must make sense given the lookup times of the parent rows. I guess the index may matter more if the relationship is one to many with potentially large numbers of children.

Thoughts on FastAPI

I finally did a lot of work on a FastAPI codebase recently, my first use of that framework. It is a lot like Flask but its support for Web Sockets and Async routes means that depending on what you’re working with it might be the only practical choice for your application.

FastAPI is actually an aggregrate over other libraries, in particular Starlette and as you dig through the layers you see that the key foundations are maintained by a few individuals with a very opaque governance structure.

I’ve used more obscure frameworks before but I didn’t really think it was a smart idea then and I don’t think it is great now. In theory the FastAPI layer could switch between implementations without breaking consumers but it all seems a bit more fragile than I had realised especially when you add on some of the complications of the various Python async ecosystem.

It’s made me wonder whether you’re better off sticking to synchronous until you have a situation where that can’t possibly work.

Coding with LLMs

LLM generated code means that some relatively uncommon idioms in programming languages come up more and more often when looking at colleagues code. The effect is quite incogrous when you have relatively inexperienced programmers using quite sophisticated or nuanced constructions.

This month in Python I noticed use of the else clause in loops which is akin to a finally block in iterations but isn’t widly used because (I think) the else clause can be hard to related to the enclosing loop and is easily visually confused with conditional clauses inside the loop.

Use of sum instead of len or count, Python’s sum allows you to pass a generator function directly to it instead of having to use an intermediate list comprehension or similar. This means you can save a bit of memory, some programmers use this habitually but I’ve only ever really seen it where people are trying to get a bit of extra performance in special cases. Most of the iterables in code often contain too few items for it to matter much and compared to the performance gain of moving to a more recent version of Python I’m not sure the gain is really noticeable.

Reading list

• A great post on how to get the most recent row in Postgres by using DISTINCT
• Reviving CGI for the modern era; everything comes around again