The "Wait, what does “SQLite-compatible” actually mean?" subheading didn't answer my question to be honest. They're using (forked) libSQL under the hood - ok, cool. But how do I interface with it?

They don't elaborate, but apparently libSQL has an HTTP API called "Hrana": https://github.com/tursodatabase/libsql/blob/main/docs/HRANA... - if that's what they're exposing, wouldn't it make more sense to call it libSQL-compatible or something?

Yes, libSQL would fit better in that case. But all of it is marketing for better audience ;) (SQLite got x69 more searches than libSQL in Google).