complex sqlite queries in FastHTML

[gskluzacek]

Let me first state that the my use case is for a local only private web app that I will run on a raspberry pi.

What is the recommended way to access a SQLite database (in a resource efficient manner) without using the fastlite package - in a thread safe manner.

---

here is the background of my request for help...

I have a sqlite database that I'd like to use FastHTML to run some queries and display the results. The queries join multiple tables and use CTEs (common table expressions) so the queries are moderately complex... I saw in the limitations section of the FastHTML MiniDataAPI Spec documentation that fastlite is not well suited to doing joins and such.

The advantages of the MiniDataAPI come at a cost. The MiniDataAPI specification focuses a very small set of features compared to what can be found in full-fledged ORMs and query languages. It intentionally avoids nuances or sophisticated features.

This means the specification does not include joins or formal foreign keys. Complex data stored over multiple tables that require joins isn’t handled well. For this kind of scenario it’s probably for the best to use more sophisticated ORMs or even direct database queries.

I'd like to go with the last option of using direct database queries as I'm already familiar with using the python sqlite3 package. Additionally, I've written the python script that populates the sqlite database which also uses the sqlite3 package and I'd like to continue to use it instead of an ORM (after all I'm trying to learn FastHTML - not an ORM, LOL)

However when I try some very basic code that creates a SQLite connection and a cursor (in the global scope as to be reusable for by routes) and then try to do a SQL SELECT, I get the following error:

sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 8601174592 and this is thread id 6151991296.

which I suspected might happen due to the asynchronous and multi-threaded nature of web apps... I know there is the check_same_thread parameter that one set to False when creating the database connection, but this is not recommended, especially if one plans to do updates to the database (which I plan to do eventually).

The code is below.

import sqlite3
from fasthtml.common import *

app = FastHTML()

conn = sqlite3.connect("network_devices.db")
curs = conn.cursor()

@app.get("/")
def home():
    sql = """ select * from executions;"""
    for row in curs.execute(sql):
        print(row)
    return "ran query"

serve()

However, if I move the creating the connection and the cursor inside of the home() function it works, though this is probably not an ideal solution as it does not reuse the database connection.

So I was wondering what is the recommended way to access a SQLite database (in a resource efficient manner) without using the fastlite package - in a thread safe manner.