Using PugSQL and FastAPI

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:

-- :name get_url :scalar
SELECT url FROM urls WHERE slug = :slug;

And then use that in your Python code like this:

queries = pugsql.module("path/to/sql/files")
queries.connect("sqlite:///db/database.sqlite3")
url = queries.get_url(slug="search_for_me")

Using this alongside FastAPI allows you to write very clean looking code, like this:

import pugsql
from fastapi import FastAPI, HTTPException, Depends
from starlette.responses import RedirectResponse

app = FastAPI()

queries = pugsql.module("path/to/sql/files")
queries.connect("sqlite:///db/database.sqlite3")


@app.get("/{slug}")
async def redirect_to_full_url(slug: str):
    url = queries.get_url(slug=slug)
    if url is None:
        raise HTTPException(status_code=404)
    return RedirectResponse(url)

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:

queries = pugsql.module("path/to/sql/files")
queries.connect("sqlite:///db/database.sqlite3")

queries.create_url(slug="example", url="https://www.google.com")
assert queries.get_url(slug="example") == "https://www.google.com"
assert queries.get_url(slug="EXAMPLE") is None

If you want to see a full example backend built with these technologies, including database migrations, check out this blog post.