Dev

Using PugSQL and FastAPI

July 14, 2022

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.

Subscribe for updates

Get started for free

PropelAuth's free plan lets you go live and start signing up users (no credit card required)