Most of the tutorials/guides that I read about FastAPI and Python web frameworks in general use SQLAlchemy directly. While SQLAlchemy is great, I personally prefer writing raw SQL over using an ORM.
PugSQL is a really interesting alternative that allows you to write your own SQL files that look like this:
And then use that in your Python code like this:
Using this alongside FastAPI allows you to write very clean looking code, like this:
What’s really fantastic about this is the separation of concerns. At an application level, I’m just looking at a function call with arguments. I can easily mock queries when I’m testing.
At a SQL file level, each SQL query is performing one specific action. I provide a name for the action, a return type, and all the parameters. I can use integration tests to make sure these queries are correct independently from my application, like so:
If you want to see a full example backend built with these technologies, including database migrations, check out this blog post.