HTTP Query API

[benbjohnson]

One issue we've had with LiteFS is that background job nodes usually need to write to the database but they are not the primary node themselves. They also cannot utilize the LiteFS proxy since there are no HTTP requests to redirect.

Instead, it would be useful to provide an HTTP API for queries for these types of nodes. This is similar to the approach that Turso pioneered, however, it's not intended to be the primary method of data access in LiteFS. This approach will require language-specific database drivers so we will add those on an as-needed basis.

[kentcdodds]

One issue we've had with LiteFS is that background job nodes usually need to write to the database but they are not the primary node themselves. They also cannot utilize the LiteFS proxy since there are no HTTP requests to redirect.

This perfectly describes my situation for my cache db, which is why this endpoint exists. I would switch to this instantly.

This approach will require language-specific database drivers so we will add those on an as-needed basis.

TypeScript/Node.js first please! 🙏

[kentcdodds]

My initial thought would be this would be implemented as an extension of the http proxy already running in LiteFS, is there a reason a language-specific driver would be necessary?

[benbjohnson]

My initial thought would be this would be implemented as an extension of the http proxy already running in LiteFS, is there a reason a language-specific driver would be necessary?

SQLite doesn't provide a way to proxy individual queries or transactions so we have to implement it at a higher layer (i.e. the driver) to be able to intercept queries/txns. The HTTP proxy runs at a higher layer than the driver and it doesn't work for situations where we aren't using incoming HTTP requests (e.g. background job workers).

The "driver" is going to be really thin. There's only 5 data types in SQLite so it can't get too complex. Turso supports a query API, batch API, & an interactive transaction API. We'll likely just support a query & batch API as the transaction API comes with a bunch of foot-guns. If people need a full transactional API, they should add an endpoint and use regular SQLite on LiteFS.

TypeScript/Node.js first please! 🙏

The first two drivers will probably be TypeScript & Ruby. Background jobs are very common in Ruby and then TS/JS is a very big community.

[kentcdodds]

I can offer feedback on the API if you'd like to share an example of what this might look like. I'm just not sure what it'll be like to use. Is it another service I need to run? Is it config?

[benbjohnson]

It'll be part of the LiteFS HTTP server that currently runs on port 20202.

It'll look something like:

POST /db/query?name=my.db

Request body:

{
    "query": "SELECT foo, bar, baz FROM t WHERE x = ? AND y = ?",
    "parameters": [
        {"value":"abc"},
        {"value":"123"}
    ]
}

Response body:

{"row":["foo1", "bar1", "baz1"}}
{"row":["foo2", "bar2", "baz2"}}
...
{"changes":0, "last_insert_rowid":0} 

The changes/last_insert_rowid only apply to DML (INSERT, UPDATE, DELETE). The response body is structured as a series of separate JSON objects so we don't OOM the process with a large query result set. It can process one row at a time instead.

---

The batch API may just be the same endpoint but with multiple query objects.

[benbjohnson]

The actual driver code itself should have the same API as a normal database driver. It should hopefully feel the same except you'll change the driver name & it'll have higher latency.

[kentcdodds]

That looks like just a regular HTTP endpoint I can hit with any language. Why do I need to have language drivers?

[benbjohnson]

Yeah, it's just an HTTP endpoint. Maybe "driver" is a bad word for it. In Go, there's typically a database driver that's abstracted by the generic database/sql interface. Ruby has ActiveRecord. I guess in JavaScript it'd just be a regular old library that wraps the HTTP endpoint. It's mainly for ease of use.

[kentcdodds]

Oh I see. Yeah, let me know when we get the endpoints and I can help put together the wrapper if you like.

[kentcdodds]

@gedw99, that's a very interesting idea. I'm guessing @benbjohnson would prefer to discuss that in another issue. Would you mind opening up a new issue?

[markuswustenberg]

Wasn't there talk/plans about write forwarding from replicas to the leader at some point, which would also cover this particular use case? I've hit the exact same issue and am now also wondering how to proceed. For now, I'll just use a static leader and only run background jobs there… 😅

[benbjohnson]

@markuswustenberg We have write forwarding in v0.4.0, however, it has some downsides that are more "distributed systems" related rather than LiteFS related. The problem is that starting a transaction remotely will lock the primary (since SQLite is single-writer) and it can lock it indefinitely (or until a timeout occurs) if the replica crashes or disconnects in an undetectable way.

The HTTP Query API is a simpler approach in that it doesn't deal with transactions. It's simply an API for sending a query (or a batch of queries) that run in a single transaction. That reduces the failure modes if the replica disconnects since the transaction is started & stopped on the primary itself.

This approach doesn't work for more complex background jobs where you need to do several queries that depend on each other in the same transaction. If you start having more complex logic like that, I'd suggest opening an API on the primary node for your background job to call instead.

[markuswustenberg]

Aaah, I had totally missed that write forwarding didn't happen transparently, but something that needs to be explicitly done (found https://fly.io/docs/litefs/migrations/#write-forwarding).

So I guess the biggest difference is that for the HTTP query API, I can't have some logic decide what to do depending on which state is in the database transactionally, right?

Do you plan to keep the write forwarding? I had a look at the Go client. For my use case, I think locking on the replica but with a really tight deadline would be a fine approach as well, but I guess the deadline then also depends on the distance between leader and replica.

My services are Go services, so I'd be happy to try out any Go clients. (But I'm going on paternity leave soon, so might not make it before then.)