Intro

I’m going to tell you how I got sub-millisecond database reads from a database where the only maintained persistent state is stored in an S3 bucket. No costly databases, and you can have your database hosted right alongside your database. Depending on the size of your database, you don’t even need to add any additional storage to your rootFS. If this sounds a whole lot like LiteFS, that’s because it is! But as you will see, that solution didn’t work for my use case. I ended up doing this for a few reasons outlined below:

  1. I honestly couldn’t get Turso’s Laravel driver running
  2. LiteFS wouldn’t work for me because it relies on the type of request to determine if a write is being made, and Laravel’s Livewire does updates via POST, which LiteFS saw as a write so it never got local reads.
  3. Because I wanted to.

With that out of the way…

Turso is awesome

If you haven’t heard of Turso they are are a company that is building a community and a SaaS around SQLite. They allow you to use their SQLite in a distributed solution with syncing and http access as well as embedded replicas. Its neat! They are also probably hands down the cheapest database SaaS out there, and just doing a ton of really awesome work with their SQLite fork LibSQL has some properly neat things. SQLite is fast compared to other solutions. And that has less to do with the fact that the alternates are slow, and way more to do with the fact that SQLite is just opening a file, and lacks all the overhead of making a remote HTTP request to some Postgres/MySQL server. Its having a bit of a heyday these days with this dynamic right now and I genuinely believe Turso has a lot to do with it.

Fly.io is also awesome

Fly.io is awesome, probably the best DX solution I’ve ever seen. Granted I mainly only have experience with running my own server on something like Hetzner or Digital Ocean and Azure. But man, it just simplifies hosting in a big way. On top of the ability to scale to zero on firecracker VMs they also have a ton of really neat networking things, but we’ll touch on that in a moment.

LibSQL Server

One of the things Turso did that is super rad is open sourced SQLd. A server for LibSQL, that allows for local SQLite-like sub-millisecond reads while writes can either happen locally (if the server is being used as a primary) or be sent to the primary via HTTP or GRPC if its a read-only replica. This is the key feature for…

Turso without Turso (On Fly.io)

I say on Fly.io because I haven’t tested it anywhere else, and it does rely on Fly.io’s internal domains for what I’ve done, there are other solutions, including eventually trying to get it to use Consul like LiteFS uses. But this works! Lets break this down. All the files I will talk about are in a github repo to demo this. But I will now break down the key points to enable distributed sqlite without Turso (but using a TON of work done by the Turso team!)

Getting SQLd on the container

Ok, I’m not going to lie, I’m really lazy when it came to this and just copied it into the repo and then copied it into it via the dockerfile. While writing this I realized no sane developer would follow a tutorial that included downloading random executables from github, so now I point to the package and wget it.

SQLd config

This is the single file that properly enables all this to work. Honestly, its stupidly simple and took me way longer than I care to admit in order to get working but is incredibly simple. It does three things:

  1. Using Fly’s .internal domain name figures out all machines that could host a SQLd server
#Grabs all the possible machine names that are up
urls=$(dig +short AAAA $CHECK_URL)
  1. Once we get all the IP addresses, we iterate through them doing some curls to see if we can connect. If we do, we assume that the one we found is an acting primary and connect to it as a replica.
#For each URL that could host a SQLd server
for url in $urls; do
#Try and connect to SQLd, and if found, be a replica
if curl -s --connect-timeout 5 -6 "[$url]:8082" > /dev/null; then
echo "Primary found, connecting to primary as replica"
REPLICA_FLAGS="--http-primary-url=http://[${url}]:8082 --http-listen-addr=[::]:8082 --primary-grpc-url=grpc://[${url}]:5001"
SELECTED_FLAGS="$REPLICA_FLAGS"
break
fi
done
#if no primaries were found, become the primary
if [ -z "$SELECTED_FLAGS" ]; then
echo "primary not found, becoming sqld primary."
    SELECTED_FLAGS="$PRIMARY_FLAGS"
fi
  1. Last, but certainly not least, once we determine the IP address of the primary (or if it will be the primary), we generate a Supervisord config file that will execute in the next step of starting the machine.
# Create a Supervisord configuration and put it the supervisor folder so supervisord can maintain it.
cat > "$CONFIG_FILE" << EOF
[program:sqld]
command=/usr/local/bin/sqld $SELECTED_FLAGS
autostart=true
autorestart=true
stderr_logfile=/var/log/sqld.err.log
stdout_logfile=/var/log/sqld.out.log
EOF

Persisting state via Tigres (S3)

Tigris is Fly.io’s S3 compatible store. Its got some neat things like kinda acting as a CDN cause it self replicates to locations where the data is requested. To be honest, I don’t think this use case really requires that, but its neat! I’m not going to walk you through setting up a bucket because Fly’s documentation does a fantastic job at that, but once you get that stood up, you will want to do the following secrets/environment variables that come straight out of the libSQL documentation on setting up Bottomless:

LIBSQL_BOTTOMLESS_BUCKET=my-bucket                 # Default bucket name: bottomless
LIBSQL_BOTTOMLESS_ENDPOINT='http://localhost:9000' # address can be overridden for local testing, e.g. with Minio
LIBSQL_BOTTOMLESS_AWS_SECRET_ACCESS_KEY=           # regular AWS variables are used
LIBSQL_BOTTOMLESS_AWS_ACCESS_KEY_ID=               # ... to set up auth, regions, etc.
LIBSQL_BOTTOMLESS_AWS_REGION=                      # .

This will write every write, timestamped, directly to the Tigres S3 bucket so you keep the state of the database. This is the final key. With this you can have all your apps scale to zero, and when the first comes up, it will grab the full state from Tigris and go about its way. No expensive Postgres, no volumes, just a tiny amount of S3 storage.

In Closing

Ok, so this is… pretty basic right? Its 96.9% the great work of the contributors of LibSQL, 3% leveraging Fly.io’s networking and whatever rounding error was me spending way too long figuring out how to wire them up. But gosh darn it I was too excited to talk about it. I was wildly excited to get it to work! The best part is that the Turso sponsored Laravel driver got a massive update literally the day I am writing this. I am gonna have to give it a go, cause reality is that for production apps I am 100% just going to use Turso, but I am going to try to improve it with the following…

TODOs

  • Auth, right now its all non-encrypted traffic with zero authentication. Relying entirely on wireguard, which fly says is obfuscation, not security.
  • Consul, right now we’re just curling and saying “good enough for me.” I’d like to follow LiteFS’s pattern of using Consul’s key/value store to manage who is primary. More efficient and reliable.
  • Gracefully handling a primary going down. Right now my plan is to run the primary 24/7, as if the primary goes down while a replica is online, I genuinely don’t know what will happen.