I'm not super familiar with Datasette but to a certain extent it does seem like you already implemented some sort of sqlite daemon over http? Is it fair to say Datasette is "like postgrest but for sqlite"? When I was thinking of using sqlite for bg jobs that's the kind of thing I wanted to avoid, but maybe you are already there :-)
In case it helps, I've been investigating bg jobs too and saw a bunch of resources that can be helpful. One is a hn post about a job queue on top of pg [0] that has some cool pointers. Someone mentioned the "transactional outbox" pattern [1]. Separately, I found this video about implementing a work queue with Nats JS [2].
I suspect you could implement the outbox pattern in Datasette and provide a way to offload the jobs to any external queue, but Nats/JS seems nice since it provides all the building blocks to implement "exactly once" delivery, dead letter queue, hearbeats to ensure the workers completes the work, etc, and it is very easy to run. I think it could save you a lot of the tricky work of implementing all these features with SQL(ite).
The overall design would be something like:
def trigger():
"""Enqueue a job transactionally: either fully succeeds or fully fails.
job_id = transaction {
job_data = ...
enque job_data into outbox
}
# This can fail but no biggie, you will still need to poll in case of failure,
# so no jobs will be created without their backing data, and no jobs will be dropped.
transaction { remove job_id and send to nats }
def background_poll():
"""Poll the outbox in case we succeeded in inserting into the outbox but somehow failed to deliver to the queue."""
try periodically { transaction { remove from outbox and send to nats } }
... then in another process or processes, the workers would talk to nats/js to perform the work.
Elsewhere someone described a job system that only relied on a database without support of events (that is, not pg), and required creating a sessions table to ensure workers complete the jobs, etc [3]. This is the kind of thing that I think could be simplified by using nats/js or another external job queue.
In case it helps, I've been investigating bg jobs too and saw a bunch of resources that can be helpful. One is a hn post about a job queue on top of pg [0] that has some cool pointers. Someone mentioned the "transactional outbox" pattern [1]. Separately, I found this video about implementing a work queue with Nats JS [2].
I suspect you could implement the outbox pattern in Datasette and provide a way to offload the jobs to any external queue, but Nats/JS seems nice since it provides all the building blocks to implement "exactly once" delivery, dead letter queue, hearbeats to ensure the workers completes the work, etc, and it is very easy to run. I think it could save you a lot of the tricky work of implementing all these features with SQL(ite).
The overall design would be something like:
... then in another process or processes, the workers would talk to nats/js to perform the work.Elsewhere someone described a job system that only relied on a database without support of events (that is, not pg), and required creating a sessions table to ensure workers complete the jobs, etc [3]. This is the kind of thing that I think could be simplified by using nats/js or another external job queue.
--
0: https://news.ycombinator.com/item?id=38349716
1: https://microservices.io/patterns/data/transactional-outbox....
2: https://www.youtube.com/watch?v=7Jp3tyCGMZs
3: https://forum.cockroachlabs.com/t/how-to-implement-a-work-qu...