A very interesting idea to be sure, but IME the biggest downside (which tbf is mentioned in the article) is the contract. If you have clients with knowledge of and dependency on the schema, you can't change it in a breaking way unless you update all the client's code.
I've tried various patterns in the past like one that just exposes database columns as an API, and this pain point always comes calling and it hurts. Keeping your data model simple and lean as possible is an important part of limiting complexity, which directly correlates with maintainability.
The only pattern/approach that I consistently return to is the Rails pattern (I use Elixir/Phoenix now but same pattern). It is certainly not the most sexy, and having a client be able to graphql exactly what they need can be really helpful, but at least for me it has rarely turned out to be worth the tradeoffs.
This is basically how Firebase is designed and it drives me nuts. I've freelanced with several teams, primarily comprised of front-end devs, who decided to use Firebase for their product. When they first told me they query the database directly from both their website and their mobile app, I immediately was like "so...what happens if you need to change the structure of the database"?
Crickets, really? It's very obvious how to do as recommended by Google - you use a Cloud Function (aka Cloud Functions for Firebase) to do a "migration", while simultaneously handling both cases in client code until this is done.
You must have been working with a bunch of amateurs if such an obvious solution (even without reading the documentation) didn't come to mind apparently by you or your clients (since you're the freelancer). This particular problem isn't even Firebase specific - you have to do this for any store that doesn't strictly have a schema.
This issue you're describing isn't even in the top 5 biggest issues about using Firebase.
As I stated in my comment, I was working with front end developers, who had little-to-no experience working with backends and hadn’t considered migrations. That documentation really didn’t help because it’s such a laborious and error-prone process, especially when dealing with mobile applications that live on users devices. My implied point was that an API layer resolves the need for any of that bullshit to begin with.
And yeah, there are tons of other issues with Firebase, but this was the first warning siren that went off in my mind. I hate Firebase.
The solution to this is the same as with APIs: versioning. Instead of naming your table "my_foo", you name it "my_foo_v1". Then, when you want to make a breaking change to the schema, you:
1. Create a new table "my_foo_v2" with your desired schema
2. Modify write queries for "my_foo_v1" so that they also write to "my_foo_v2"
3. Copy over existing data in "my_foo_v1" to "my_foo_v2" with a migration script
4. Modify read queries for "my_foo_v1" so that they read from "my_foo_v2" instead
>Modify read queries for "my_foo_v1" so that they read from "my_foo_v2" instead
Sure but in the case that parent poster was describing you're now into a situation where you need to refactor all the DB calls for both a web and mobile app(s) all at once while making sure that the V1 and V2 tables don't get out of sync which is a pretty significant effort.
With postgres, simple views are updatable, so you can often do this pattern without copying tables or dual writes. It's particularly useful for renaming columns, but you can also do some other things. You also don't have to use version numbers on all your tables since you only need the view temporarily.
ALTER TABLE my_foo RENAME TO my_foo_tmp;
ALTER TABLE my_foo_tmp <your desired change here>;
CREATE VIEW my_foo AS SELECT <how you make your new table seem like your old table>;
COMMIT;
<update your apps to use my_foo_tmp>
DROP VIEW my_foo;
ALTER TABLE my_foo_tmp RENAME TO my_foo;
CREATE VIEW my_foo_tmp as select * from my_foo;
COMMIT;
<update your apps to use my_foo>
DROP VIEW my_foo_tmp; COMMIT;
We actually used a similar tactic when transitioning our data to supporting soft deletion. We have a limited number of queries that update data or present data for administrative review and processing - but a lot of wild ones around reporting. We'd rename table `foo` to `foowithdeleted` and create a new `foo` view that excludes any soft-deleted rows. Our reporting queries keep on trucking like normal and read out of the `foo` table (now a view - but they don't care) and we only need to adjust the administrative view to show soft-deleted rows for undeletion operations.
Shell-gaming tables with views can be incredibly useful in postgres.
Holy hell does that sound awful. In practice, it doesn't even solve the core problem as you'd have to deploy new versions of all binaries precisely in sync to avoid data incorrect because some clients know about the v2 table and others don't. It becomes indistinguishable whether a row is missing because it was intentionally deleted or because it was written by a client who didn't know any the new table version. There are ways to account for this but it's layers of crap on top of crap.
> it doesn't even solve the core problem as you'd have to deploy new versions of all binaries precisely in sync to avoid data incorrect because some clients know about the v2 table and others don't
its painful but this approach does work as long as every client is migrated between each step.
after 1: v1 is valid, reading v1, writing v1
after 2: v1 is valid, reading v1, writing v1 and v2
after 3: v1 and v2 are valid, reading v1, writing v1 and v2
after 4: v1 and v2 are valid, reading v2, writing v1 and v2
after 5: v2 is valid, reading v2, writing v2
after 6: v2 is valid, reading v2, writing v2
at each point, both the current and the previous version are reading a valid table and writing to a table whose values will make it into v2.
Is there really any alternative? Data migrations are always going to be terrible. You need to slowly roll out the incremental changes so that everything is backwards compatible until you the threshold point where you can finally delete the historical route.
You know, I've been toying with this very idea in my head for a while, as a way to make safe schema migrations. But I also know very little about databases so had no idea if this was a stupid idea or not. Glad to know I'm not alone.
One issue I've considered is fk relationships - that could get complicated depending on the approach.
Imagine you have Android & iOS apps which some of your users won't ever update. You'll never be able to drop any old version's tables, and you'll need to keep all table versions in sync more or less in real-time. Only when the last user has updated from a certain client version will you be able to remove that version's tables.
I think about this with Postgrest/Supabase which has a similar problem (autogenerated code based on the db schema is inherently going to conflict with api stability guarantees). I think that this approach is just fundamentally at odds with making a stable versioned controlled API.
However: I think the best thing to do if you want a setup like this and want to have your cake and eat it too is something like this:
Use your autogenned client for your “internal” api. This is for your clients with the autogenned schema that you directly control only so that you can ship changes and not have to worry about versioning/backwards compatibility.
Then for external users that have their own clients, you have that slimmed down more traditional API that offers less functionality but it’s properly versioned, tested etc
I think this kind of hybrid setup can work well for SaaS setups where you have a cloud product that does internal stuff plus things external that end users need to operate on. You get the benefit of being able to iterate quickly without breaking your clients and since your external API is smaller it’s less maintenance overhead to keep it updated and versioned
Huh, interesting, since at least with Supabase the default setup kind of pushes you towards or at least doesn’t discourage you from using tables instead
If you are in the situation where you are shipping a web app where one dev team controls the backend and the one and only client and ships both together, is this a big issue?
They mention one partial "solution" to this: have your clients query a view, instead of tables directly.
When you need to change the schema, also update the view accordingly.
Of course, there are still limitations. For one: you can't "massage" data with a view as you could in the backend with a full featured programming language.
PS: I don't think this is a good solution, just mentioning it.
A downside I didn't see mentioned (it was gestured at with contracts and the mention of backwards compatible schema changes, but not addressed directly) was tight coupling. When you link services with APIs, the downstream changes of a schema migration end at the API boundary. If you are connecting services directly at the database level, the changes will propagate into different services.
That would make me very nervous to ship something like this. You can probably cover a lot of this with views as a backwards compatibility shim, though.
Author here. I don't see this is a general practice to be used for most applications. It was a side effect that we came across and it allowed us to share data between internal applications in some interesting ways. Internal apps are ideal since you have control of both sides of the contract. It definitely requires some more consideration if you're publishing out to end users.
Hi Ben, thanks for taking the time to respond. It's an interesting approach, and I'm sure that it has it's place & you've made the right call for your particular situation.
I didn't think you meant pushing it out to end users, my point was more that this technique increases the blast radius. If three services are touching this database, and our changes now necessitate three deploys, that's much higher risk.
But it was an early comment, I hadn't gotten to see the suggestions for managing this coupling yet. Each one of those does take us a step closer to designing an API, but perhaps part of the value here is that it allows you to move along a spectrum and to adopt the level of constraint you can afford, given the situation.
I'm curious if this has spread to any other services at fly? Or is it just this Corrosion service?
I think a spectrum is a great way to look at it. If you're adding one service then it's no big deal but as you add more and more then you get a better sense of the requirements of the API and I think you're in a better place to build one out then.
We've used this in a few other instances but Corrosion is the largest database we've done it with.
I think there are some reasonable ways to avoid the schema brittleness that people are concerned about with this.
Firstly, acknowledge that in this model your schema IS your API. Don't include anything in the database that is being replicated that isn't considered to be documented, tested and stable.
With that in place, API changes become the same as changes to a JSON API or similar: you can only make backwards-compatible changes, like adding new columns or adding new tables.
If you do need to make a backwards incompatible change there are ways to patch over it. Ship a brand new table with the new design, and include a SQL view that matches the name of the old table and exposes a compatible subset of it.
This is correct, but there are very few people with experience with safe database modelling like this, and they may not even realize the technique exists.
Maybe it's better to have views in the first place, and to require (though it can only be enforced at an administrative level) external services to query against those views.
We won't have to do this switch to a view, we've paid that upfront.
We can then include a version number in the view name, similar to how we do with APIs. So if we introduce changes we can keep services which didn't need that change outside the blast radius.
And we have a limited ability to enforce contracts and declare some columns internal.
I like it, but the only benefit we've retained at that point is eliminating round trips. Which is pretty significant. But we're still engaging in API design, we're just doing it in Sqlite.
That is an appealing idea but on second thought, haven’t you just shifted the problem to: how do I reliably replicate logical updates from my operational tables (internal, constantly changing) to my clean, public-facing tables?
Basically, your public tables become materialized views over the “dirty” tables, and I’ve never met a mature, reliable implementation of materialized views, or even an immature one that is feature complete. (I would love to be wrong on the internet with this one!)
That's just working around an artificially and unnecessarily introduced constraint. There's little zero upside to doing it this way. It's not even more convenient since it incurs pretty heavy refactor costs for even small changes.
Is this a true cloud managed SQLite or is this like their PostgreSQL documentation where it's just a bunch of pre configured docker containers and the developer is expected to manage everything themselves? If the db goes down for an extended period of time at 3am, does fly.io have an engineer on call?
I find that fly.io has been a very disingenuous startup. They position themselves as some sort of Heroku successor and hide behind their developer content marketing while providing very low quality infrastructure. At the end of the day, fancy blog posts may win hearts and minds, but downtime cost businesses actual money.
Can second this. Tried them out at our startup, came away unimpressed. Their good engineering blog is great marketing though, although this particular post is lacklustre. What about permissions, indirections, versioning or otherwise encapsulating breaking changes?
I think you are thinking that Fly is like a serverless platform. They aren't. They are the opposite. They are a server platform. They provide server for you and you have to manage your server yourself.
Nothing they provide is managed by them. You have to do that.
LiteFS is just a replication service for your sqlite database so you can keep your database synced across multiple nodes.
I go into a project folder and can run "fly launch" and then the application is alive in multiple regions around the world. Where exactly am I manging this server?
How does a Dockerfile change things? Your application is packaged into a container. The Fly service runs any application not just web applications or build artifacts. Of course your application is going to get a complete operating system environment to run in.
I don't think the Dockerfile has anything to do with them pulling your container from their registry and running it on their server.
Ah right okay thanks. The point still stands the Dockerfile and image is used as a container for your application and does not imply a manage-it-yourself server is being used.
Serverless doesn't mean servers don't exist, any more than calling a radio a "wireless" doesn't mean wires don't exist. It just means for the consumer, servers aren't a consideration, just as with a wireless wires aren't.
> My dog doesn't need a wire for anything, but no-one talks about them being 'wireless'.
Indeed :-) But isn't this the same sort of definition you want for "serverless"? Not something relevant to consumers (e.g. "you don't manage servers") but a description of something where servers are not involved in any way?
Dear God this is a tired point. The rest of the damn paragraph of the Wikipedia article fully addresses that. Nobody who uses the term "serverless" thinks that magic fairies are replacing servers. The point is who is managing it for your application or business.
I said nearly the same thing decades ago. Smashed a few friends’ so-called “wireless” telephones and showed them all the wires. Just look out a window; what’re those long, wiry things running between the utility poles, huh?! It’s ridiculous, the level of lying.
I was just setting up a new Fly account and launching a project yesterday. I will say their developer content marketing is very succesful and had me convinced they at least know what they are talking about.
Any more stories or knowledge of them being disingenous?
If the free juice they give you in the supermarket tastes bad, will you buy it?
We assume the free version to have similar quality to the paid one. It's in the vendor interest, so that we like it and become a paid customer. If they can't convince us of their quality in the free version, how are we supposed to trust the paid one?
Author here. I think we could have set better expectations with our Postgres docs. It wasn't meant to be a managed service but rather some tooling to help streamline setting up a database and replicas. I'm sorry about the troubles you've had and that it's come off as us being disingenuous. We blog about things that we're working on and find interesting. It's not meant say that we've figured everything out but rather this is what we've tried.
As for this post, it's not managed SQLite but rather an open source project called LiteFS [1]. You can run it anywhere that runs Linux. We use it in few places in our infrastructure and found that sharing the underlying database for internal tooling was really helpful for that use case.
I'm curious the types of users this has. This feels like something that small to mid size companies may leverage but anything slightly more sophisticated would typically require access controls, merging data with other sources, etc. Doesn't feel like a very scalable appraoch.
Author here. We do this with some internal applications that share internal state -- no customer data. I would expect stricter restrictions depending on the type of data shared and how bureaucratic the company is.
Author here. It's not meant as a one-size-fits-all approach. It was an interesting side effect that we noticed that we used internally so I wanted to share my experience. The database mentioned in the post is about 8GB and it replicating it works pretty well.
I've been doing this for a long time and all I can say this after reading this multiple times ... "I don't get it".
I mean, I get it, from a technical standpoint. Ok, so you're going to send read-only Sqlite databases to everybody.
Is it missing what the API (that you still need) is updating when you insert or update something and all client DBs are now stale? Is there a central database? How often are you pushing out read-only database replicas across the wire to all clients? Is that really less "chatty"? If so, how much bandwidth is that saving to push an entire database multiplied by the number of clients?
None of this seems logical. Maybe I'm missing the real-world use-case. Are we discussing tiny Sqlite databases that are essentially static? Because in the last 30 years I've not run into a situation where I needed to have clients execute SQL queries on tiny, static databases let alone still need to potentially update them also.
Imagine you're building a SaaS which allows your users to do create a website, hotel booking platform and channel manaager.
User can open the application, get the database, the frontend does all the offline updates the user want to perform. The user can update their website, add bookings they received on the phone, check what prices they set. This is all blazingly fast with no loading time, no matter your internet connection, because no further communication with the server is needed.
At some point they press a magic Publish button and the database gets synced with upstream and uploaded. The upstream service can take the data and publish a website for its users, update availabilities, update prices, etc.
It would be a better user experience than 99% of the SaaS out there.
I just checked a heavy user for our enterprise SPA. localStorage is using ~12 KB.
I don't know what people are just throwing in localStorage but they certainly aren't pulling down much of the database (which still would have to be checked to ensure cached data in there isn't stale).
Once upon a time, this is how applications worked and this was a pretty good experience.
Now, you'll introduce a huge amount of user frustration around why their changes never made it to the central database. If you have users closing a form, especially if it's on a webpage, they are going to expect the write to happen at the same time the form closes. Making a bunch of changes and batching them in a single sync is going to be confusing to a ton of users.
If everyone's working on their own local copy of the database then the select for update isn't going to do anything. The issue is later syncing and detecting conflicts. It's actually easier to do this with a centralized DB, hence why everything works this way. If an app is mostly offline then, yeah, ship it with a SQLite DB and life will be good, but for something like a hotel booking app that doesn't actually solve many problems and makes existing ones harder.
Thanks for the example, it helped me understand the idea.
The thing that I'm unclear on is how do I figure out what data to ship to the user? Like if I don't already have a database-per-user then I have to extract only the data the user has permission to see, and ship that as a database?
That would be the case even if I had database-per-customer - not every user is necessarily able to see all the data owned by the organization they're a part of.
It seems like a lot of extra work, and error-prone, too (what could be worse than accidentally shipping the wrong data _as an operational database_ to a client?)
Edit: the article covers this at the bottom, but IMO it's a show-stopper. How many applications of any real complexity can actually implement database-per-user (database-per-tenant is probably not enough, as I mentioned above). As soon as you need any kind of management or permissions functionality in your app then you can't ship the database anymore, so you may as well start off not shipping it.
Most of the Internet spent the last 10-20 years moving away from this because business metrics generally benefit from realtime updates and background autosaves. By and large, users expect systems to autosave their work-in-progress nowadays.
You might remember horror stories from the late 1900s when people would lose critical data and documents because they "forgot to hit Save and the computer crashed." Or, maybe you've never experienced this — because a couple decades of distributed-systems engineers and UX researchers made that problem obsolete.
So now we've... reinvented the Save button, but somehow needed a Paxos implementation to do it?
Heh, the last company I worked for had a (very popular, very successful) 20 year old desktop app that worked by downloading the client's entire database on login, then doing regular syncs with the back end and sending batch updates as the user made changes. It was an awful system that everyone hated, and the company was desperately trying to figure out how to move away from it without doing a complete rewrite. Maybe I should let them know that they're actually ahead of the curve if they can just hold out for a few more years...
I feel like I'm taking crazy pills. As HNers, we should want more control, not less over our work. Auto-save means that the company, no matter how nefarious, decides on their terms when my work is saved - regardless of what I've entered into the document (I write documents in a very stream of conscious manner).
I want to decide when to save, and how, not the application. I am not a product, I am a user!!!
No. I value a meaningful "Last updated at" timestamp.
If I open a word document, redact a few bits, save to PDF and close it - I don't want my changes saved but they are (real scenario from last week. I'd only just moved the file into Onedrive so autosave had turned itself on)
Ditto sorting and filtering spreadsheets.
Software engineers: By all means save in the background so I never lose anything, but don't assume I want the latest changes.
What you want, then, is versioning of your documents. MS Office and Google Suite do that.
This is not trivial to implement, though. Complexity will depend a lot of the application and the data. I'd say it'll only make sense for mature apps. A startup will most likely don't consider this in its roadmap.
Seems like on-by-default auto save with the option to disable, and a separate save button, satisfy all your requirements. That’s a pretty common set of customizations on saves from what I’ve seen.
> User can open the application, get the database, the frontend does all the offline updates the user want to perform.
"get the database"
How small do you think these databases are?!
You're going to download the entire hotel booking platform's database?
For how many hotels? One at a time, and then get another databse? Or are you getting a Sqlite booking database for every hotel in the world? And you're going to send them to each user? For what date range?
And even if that were possible, you then have to commit your offline updates. What if someone else booked the date range prior to you? Now your Sqlite copy is stale. Download the entire thing again? There could have been countless changes from other users in the time since you last got your copy.
This explanation just leaves me even more confused. It's illogical.
Web applications aren't going to get bigger just by themselves! /s
Jokes aside, this extreme optimization for development does have impacts on user experience. The amount of bandwidth/storage etc used by a "just ship the whole db" type applications would surely suck for most people outside of the 'I measure my bandwidth in Gbps' crowd?
I have some experience with a comparable platform. In a typical CouchDB/PouchDB design, syncs and offline work are common and easy, and it's pretty close to database-based design if you get fancy with views and javascript-based logic.
For this project, I'd do:
* A database for each user (this is natural on CouchDB, one can enable a setting to create the user DB automatically when a user is created. The platform won't choke on many databases) - for some per-user data, like comments, if the user has already reserved a booking we can mirror booking data there + some info regarding the hotel.
* Common synced databases - some general info regarding the platform and hotels. Preferably not too large (IIRC there's no limit to attachments with 3.x, but it sounds risky). Anything too large we'd do online or use data provided with the application.
* A large booking database which isn't synced and must be modified online with a REST call - we don't have to allow every action offline. Here I wouldn't entirely dispense with API. This obviously needs the online component for making sure bookings don't conflict. Could even be a regular relational database.
I think it is possible to implement this the CouchDB database-way: a provisional offline reservation to the user database followed by some rejection method on the server when syncing, but I don't think there's much value to the user here. This design however would allow us to not sync all the data but a much smaller portion while supporting offline.
---
It sounds very doable, rather similar to a project I was involved with, but I miss SQL a lot with that platform (javascript NoSQL not my favorite to work with). A sqlite-based approach is an interesting alternative.
It probably makes most sense if you have a middle-tier-less application where the UI IS the application, and effectively it just dumps the whole application state out through APIs anyway. We have ended up with this scenario playing out and you eventually just throw up your hands and make the UI hit the server with a giant "send me a huge blob of JSON that is most of the tables in the database on first page load" query anyway.
So the assumption that "if anybody changes anything everybody needs to know it" is close to true. In that scenario, putting a bunch of APIs in the way just makes the data less coherent rather than more. In most other scenarios, yeah, it's hard to see that it really makes sense.
I have never in my career spanning decades have I had to ask a server to send me a dataset so large that it "most tables in the database on first page load".
In what use case do you run into that?
I'm lead and an architect on an enterprise application at the moment that drives the whole company. It's your standard configuration, front-end, APIs, SQL. The system requests what it needs to fulfill only what functionality the user is dealing with.
Earlier in my career I was dealing with large enterprise desktop applications that talked directly to the database, with no centralized API. Some of them had thousands of individual desktop clients hitting a single multi-tenant SQL server. No problem, SQL Server would handle it without breaking a sweat. The bandwidth to an indidual client was fine. It was fast. And that was 20 years ago.
I did faced this scenario a couple time when working on application that would work offline, a few of those I was involved in:
- try to reduce the amount of paper based catalog we were sending out to customers, those were a couple thousands of pages and not cheap to produce, not cheap to send and would get deprecated very quickly. The web app would be pulling the entire catalog at first load so customers could go in remote location and still be able to use the catalog
- a web app for sales that was intended to be use on customer site containing all the marketing materials and much more during presentations on site without ever having to connect anywhere
Author here. We're using LiteFS to replicate SQLite databases in real time so the changes sent are only incremental. I think there are several ideal use cases for sharing databases across applications:
1. Internal tooling: you're able to manage the contract better since you have control of the source & destination applications.
2. Reporting & analytics: these tend to need a lot of query flexibility & they tend to use a lot of resources. Offloading the query computation to the client makes it easier on the source application.
As for database size, the Corrosion program mentioned in the post is about 8GB and has a continuous write load so this doesn't have to just be for tiny databases.
Curious if you've tested it with Jepsen. Anytime I come across some distributed system, my stomach ulcers start playing up while I wonder about all the weird failure modes. I kinda looked for a caveats page on the LiteFS web site, didn't really see one.
1. There is a single writer. There's optional best-effort leader election for the writer.
2. If there's a network partition, split-brain, etc, availability is chosen over consistency.
Jepson's testing is focused on databases that pick "consistency". Since LiteFS didn't pick consistency, there's really not any point in running Jepson against it. Like, jepson would immediately find "you can lose acknowledged writes", and LiteFS would say "Yes! That's working exactly as intended!"
However, another way of running LiteFS is with only a single writer ever (as in one app, one server, one sqlite database only), and all clients as read-only replicas that are not eligible for taking writes ever. In that case, you also don't have a proper distributed system, just read only replicas, which is quite easy to get right, and mostly what this post is talking about.
LiteFS works similarly to async replication you'd find in Postgres or MySQL so it doesn't try to be as strict as something running a distributed consensus protocol like Raft. The guarantees for async replication are fairly loose so I'm not sure Jepsen testing would be useful for that per se.
On the LiteFS Cloud side, it currently does streaming backups so it has similar guarantees but we are expanding its feature set and I could see running Jepsen testing on that in the future. We worked with Kyle Kingsbury in the past on some distributed systems challenges[1] and he was awesome to work with. Would definitely love to engage with him again.
The parent is saying this isn’t a great idea because target users don’t use SQLite. Your reply is it is a good idea if people changed how they do things to fit the idea
I don’t have a horse in this race the reply isn’t very well I don’t know what to make of that
I am looking at the data analytics industry as a whole and being involved in communities of data practicioners. Most of these people use cloud DBs (Snowflake, BigQuery & co) since there's less dependencies on DB Admin type of work.
Some might be using Postgres or whatever the Engineering team provided them with, but I don't think I have really heard of a Data person preferring to use SQL Lite.
Might still be a great fit, but as the other comment pointed out, it might not be a good fit for the target audience.
Looks interesting although personally I don't see those as compelling use cases although I may very well be missing something.
> 1. Internal tooling: you're able to manage the contract better since you have control of the source & destination applications.
This has not been my experience in anything but tiny companies or companies with very strict mono-repo processes. One big point of separate teams is to minimize the communication overhead as your organization grows (otherwise it scales as N factorial). That means you do not want a lots of inter-department dependencies due to the internal tooling and APIs they leverage.
> 2. Reporting & analytics: these tend to need a lot of query flexibility & they tend to use a lot of resources. Offloading the query computation to the client makes it easier on the source application.
Depends on the resources the client has versus the server to devote to a single query. The resources are also high because of how much data is analyzed and 8gb seems tiny to me (ie: the whole thing can be kept in some DBs memory).
They are adding a sync-mechanism for Sqlite so local writes can be synced to a remote location and on into Postgres and so on. CRDT-based eventual consistency.
So local write latency, eventually consistent sync and the tools for partial sync and user access are the primary areas of development.
Early days but an interesting approach to shipping the db.
Oh, I implemented something like that for my Android app. It seems to work quite well. I don't have many users yet, though.
I replicate the clients Sqlite DB to a central server (over a REST-API) where it is synced with existing data. I use an CRDT, so changes don't get lost and as long as the clocks of all the users devices are accurate, the merges are in the correct order.
This enables offline access, but also the use of my app without an account. You can always merge the data later.
Multi-device merge is also possible, if you create an account. Especially the multi-device merge was a big headache until I settled for this approach.
Since it is a home-grown solution I still have to do some manual stuff that could be abstracted away, like converting datatypes to and from JSON, Kotlin, PHP, MySQL. There's not always an equivalent datatype in those technologies.
This approach probably won't work well for big databases that share data between multiple users, though.
Are there any advantages you noticed as a dev to using this stack? Did you find there were better affordances? Were there challenges and growing pains?
I have seen some stuff where a streaming tool (Kafka or whatever) is used to just ship all updates to a database to certain clients. But I think this is a dubious architecture since it comes with basically all the downsides of a database that many applications all want to use besides the write contention one.
I think the point is basically "unless there's a good reason for your API to look different than your DB schema, ontologically speaking, then the schema has already effectively defined your API and just let people interact with the DB", or, alternatively, "bake in as much of the data constraints as possible into your DB schema, and only when you can't enforce them with column constraints or DB extensions should you add APIs on top".
> Because in the last 30 years I've not run into a situation where I needed to have clients execute SQL queries on tiny, static databases let alone still need to potentially update them also.
It was not uncommon in the early 2000's for applications targeting mobile professionals and sales types to work this way, except that instead of SQLite they used things like Notes¹, Jet² and MSDE³. By modern standards these would be considered "tiny mostly static databases" often not exceeding a gigabyte. Instead of connecting over the internet they used serial dial-up file transfer protocols to synchronize with a central database or a mainframe. People would typically download information in the morning, make updates offline and synchronize at the end of the day. A slow trickle of periodic bidirectional updates insured everyone had a reasonably fresh copy of the information they needed.
I love this kind of reasoning that forces you to climb out of the comfortable nook in a tree of thought you’ve been occupying for years to see if there’s a better vantage point!
So would this work equally well? Use Postgres on some cloud and “spin up” a read-replica that your clients have access to. Let them read from the replica.
If yes, I have tried that and I can tell you the problem I had. They don’t want to get to know my database schema. I know, frustrating! “If you would just get to know me better, you would have all the secrets you desire!” Sigh… they just want this overly simplistic Interface that doesn’t change, even when we rewrite things to make them better on the backend.
Another thought: if sharing compute is the main problem, you can just egress the data to distributed file storage like S3 and let them write SQL on the files using some Presto, like Athena. Or egress to BigQuery and let them bring their own compute. But the problem there again is getting to know my internal data structure, and also by “egress” I really mean “dump the whole damn thing because once the data is on S3 I can’t just update a single row like I can on an OLTP database” and you can only do that every so often.
Yes, Postgres replication would work as well. I agree that the "getting to know the schema" part is an issue. I think there's use cases out there where you have power users that would gladly invest extra time in exchange for query flexibility.
Querying to S3 is a good approach too. Those query tools can be somewhat involved to setup but definitely a good option.
It sounds like this is essentially database replication, but perhaps cheaper, since it's a SQLite file with a schema hopefully designed to contain whatever the client needs rather than random junk. You'd still have to send writes to one location and they'd fan out from there, with some latency.
It seems like it would be a good fit with edge servers, assuming any node that starts an instance could get a copy of the database when it starts. Most database-as-a-service companies don't do that.
I doubt I'd try it if it isn't a fully managed system, though. The only one I know of is Cloudflare's Durable Objects, and it's not in the free tier. For hobbyist programming that's kind of a drag, so I haven't tried it. Instead I'll probably try Deno's KV. (This sort of thing is what I hoped KV would be, but apparently it isn't.)
I wonder what the cold-start performance would be for downloading a small SQLite database from S3, starting it up in memory, and subscribing to a replication log?
I've written about this solution in the context of running LLM-generated SQL safely. I call it the "cloned database view" solution[1].
The author touches on 2 issues that are tangential to this problem, restricting unauthorized data, and mutations, both of which are not really easily solvable with the cloned database solution.
However, the underlying theme of skipping the API is extremely compelling. So much code, both FE and BE, are just to serve as an elaborate interface to the database. Simplifying this will be a huge gain in software maintenance. But the challenge is to do it safely.
>A cloned database view is some representation of your database that has been post-processed to only contain data that the user is allowed to see. This representation would typically be in the form of a separate user-specific sqlite database file. Because the cloned database only contains data that is theirs, a user may issue any query against it, and there is no risk to data security. And if a malicious user was able to issue a DDL statement, it would only affect their cloned database.
Maybe there is a better word than "cloned database view." Replica doesn't feel quite right though.
I think your use of database clone looks fine in the context you write about. I don't think it applies to the context fly.io is writing about. Your post is about something that seems like a different pattern, I think.
A database isn't designed to be used this way. You'll eventually need it to do something "extra", and your company will probably find some workaround or hack, but eventually make an API.
APIs are an intermediate layer of abstraction needed because their purpose is separate from that of a DDL/DML/DCL/TCL. Having raw database access does not solve the use cases the database doesn't account for.
This looks interesting for read heavy distributed apps. I.E fly.io/aws lambdas/k8s.
Each instance gets its own eventually consistent read only copy of the db. So instead of making a network request to go get some data, you query your local cache.
Your use case is that you are read heavy for this to fit your needs.
If you have a copy of the data local to you, you don’t need to go bug another team to expose it via an API.
Reading fly’s blogposts it’d be good to see more on write durability. They’re batched on the client, batched on fly’s side before being compacted to s3 from reading so you can loose writes before your client as sent them to fly, possibly loose them before fly has written to s3 depending on how they implemented.
FlyCloud might make a great service for small typical Wordpress type sites or low user services where writes are low. It’s a shame they don’t offer pay as you go for the LiteFS product only. I get why they don’t as they want you to host with them but it’d be great if you could pay as you go on the db only.
I currently use DynamoDB for low volume stuff for cost reasons, it costs me cents. Productivity is extremely low though with Dynamo as you need to think very hard about table design and constraints and work around those constraints. It’s a lot more effort than using something like SQLLite for quick simple stuff.
Musing about APIs and the constant complaints about "abuse" and "bots", I think it would be an interesting experiment to set up an API where when a user hits the daily limit, instead of just being "blocked" (which according to complaints does not really work due to use of proxies), the user is sent a link to the database for download. The daily, updated database could be hosted on something like Backblaze. Would this satiate the demand for the data in a more efficient way than an "API".
I was really excited about fly.io at one point but after reading all the recent posts(1) on HN I don't have trust in their services anymore. Hope they are working on improving system stability first.
This is a great point and the reason why I think server-side rendering will ultimately fail.
I've done something similar for a site I manage (6groups.com). Each client's DB log is stored in compressed files on the server and only initialized via sqlite wasm on the client when needed. The trick is to periodically run compaction jobs (also on the client) to make sure DB logs don't grow to unreasonable sizes
I can't imagine the DB consumption I'd have if all client data was in relational format on the server
I have been thinking about this a lot lately. We have customers who run automation against us. They’re doing 10,000 API calls. Each one has to do a permission check, request signing, serialization, etc., etc. All just to mutate a blob of data less than say, 100MB. If they just downloaded the whole state, mutated it, and reuploaded it, the whole thing would be done in 2 seconds.
We already lock the entirety of each customer’s data when processing a request, in order to avoid data inconsistency from concurrent mutations.
One SQLite database per customer is a really appealing idea.
I'd think 100x before deploying this pattern to external customers.
The use case shared by fly.io is for internal tooling. If you have a small team that most likely won't grow to more than a few dozen devs, maybe you can get away with it.
Shipping it to several external customers will be a horrible nightmare in maintenance and dev productivity. I'd switch jobs if I was working in a project that started applying this. Paying for the HTTP API upfront is a cheap price to avoid the costs of not having it down the road.
256 comments
[ 2.9 ms ] story [ 267 ms ] threadI've tried various patterns in the past like one that just exposes database columns as an API, and this pain point always comes calling and it hurts. Keeping your data model simple and lean as possible is an important part of limiting complexity, which directly correlates with maintainability.
The only pattern/approach that I consistently return to is the Rails pattern (I use Elixir/Phoenix now but same pattern). It is certainly not the most sexy, and having a client be able to graphql exactly what they need can be really helpful, but at least for me it has rarely turned out to be worth the tradeoffs.
Crickets.
You must have been working with a bunch of amateurs if such an obvious solution (even without reading the documentation) didn't come to mind apparently by you or your clients (since you're the freelancer). This particular problem isn't even Firebase specific - you have to do this for any store that doesn't strictly have a schema.
This issue you're describing isn't even in the top 5 biggest issues about using Firebase.
And yeah, there are tons of other issues with Firebase, but this was the first warning siren that went off in my mind. I hate Firebase.
1. Create a new table "my_foo_v2" with your desired schema
2. Modify write queries for "my_foo_v1" so that they also write to "my_foo_v2"
3. Copy over existing data in "my_foo_v1" to "my_foo_v2" with a migration script
4. Modify read queries for "my_foo_v1" so that they read from "my_foo_v2" instead
5. Remove all write queries to "my_foo_v1"
6. Drop the "my_foo_v1" table
Sure but in the case that parent poster was describing you're now into a situation where you need to refactor all the DB calls for both a web and mobile app(s) all at once while making sure that the V1 and V2 tables don't get out of sync which is a pretty significant effort.
Shell-gaming tables with views can be incredibly useful in postgres.
its painful but this approach does work as long as every client is migrated between each step.
after 1: v1 is valid, reading v1, writing v1
after 2: v1 is valid, reading v1, writing v1 and v2
after 3: v1 and v2 are valid, reading v1, writing v1 and v2
after 4: v1 and v2 are valid, reading v2, writing v1 and v2
after 5: v2 is valid, reading v2, writing v2
after 6: v2 is valid, reading v2, writing v2
at each point, both the current and the previous version are reading a valid table and writing to a table whose values will make it into v2.
One issue I've considered is fk relationships - that could get complicated depending on the approach.
https://engineering.edx.org/django-migration-donts-f4588fd11...
However: I think the best thing to do if you want a setup like this and want to have your cake and eat it too is something like this:
Use your autogenned client for your “internal” api. This is for your clients with the autogenned schema that you directly control only so that you can ship changes and not have to worry about versioning/backwards compatibility.
Then for external users that have their own clients, you have that slimmed down more traditional API that offers less functionality but it’s properly versioned, tested etc
I think this kind of hybrid setup can work well for SaaS setups where you have a cloud product that does internal stuff plus things external that end users need to operate on. You get the benefit of being able to iterate quickly without breaking your clients and since your external API is smaller it’s less maintenance overhead to keep it updated and versioned
When you need to change the schema, also update the view accordingly.
Of course, there are still limitations. For one: you can't "massage" data with a view as you could in the backend with a full featured programming language.
PS: I don't think this is a good solution, just mentioning it.
That would make me very nervous to ship something like this. You can probably cover a lot of this with views as a backwards compatibility shim, though.
I didn't think you meant pushing it out to end users, my point was more that this technique increases the blast radius. If three services are touching this database, and our changes now necessitate three deploys, that's much higher risk.
But it was an early comment, I hadn't gotten to see the suggestions for managing this coupling yet. Each one of those does take us a step closer to designing an API, but perhaps part of the value here is that it allows you to move along a spectrum and to adopt the level of constraint you can afford, given the situation.
I'm curious if this has spread to any other services at fly? Or is it just this Corrosion service?
We've used this in a few other instances but Corrosion is the largest database we've done it with.
Firstly, acknowledge that in this model your schema IS your API. Don't include anything in the database that is being replicated that isn't considered to be documented, tested and stable.
With that in place, API changes become the same as changes to a JSON API or similar: you can only make backwards-compatible changes, like adding new columns or adding new tables.
If you do need to make a backwards incompatible change there are ways to patch over it. Ship a brand new table with the new design, and include a SQL view that matches the name of the old table and exposes a compatible subset of it.
But it can be learned, as can backwards-compatible database schemas.
We won't have to do this switch to a view, we've paid that upfront.
We can then include a version number in the view name, similar to how we do with APIs. So if we introduce changes we can keep services which didn't need that change outside the blast radius.
And we have a limited ability to enforce contracts and declare some columns internal.
Basically, your public tables become materialized views over the “dirty” tables, and I’ve never met a mature, reliable implementation of materialized views, or even an immature one that is feature complete. (I would love to be wrong on the internet with this one!)
I find that fly.io has been a very disingenuous startup. They position themselves as some sort of Heroku successor and hide behind their developer content marketing while providing very low quality infrastructure. At the end of the day, fancy blog posts may win hearts and minds, but downtime cost businesses actual money.
Nothing they provide is managed by them. You have to do that.
LiteFS is just a replication service for your sqlite database so you can keep your database synced across multiple nodes.
https://github.com/superfly/litefs
LiteFS Cloud which is the a service they provide just helps you backup and recover sqlite databases. You can do this yourself.
https://fly.io/docs/litefs/backup/
I don't think the Dockerfile has anything to do with them pulling your container from their registry and running it on their server.
There is NO SUCH THING as serverless. There are always servers.
My wireless printer needs a wire for power.
My dog doesn't need a wire for anything, but no-one talks about them being 'wireless'.
(Come to think of it, my dog is serverless too...)
Indeed :-) But isn't this the same sort of definition you want for "serverless"? Not something relevant to consumers (e.g. "you don't manage servers") but a description of something where servers are not involved in any way?
In those two examples: serverless and wireless phones, it's not misleading consumers.
Serverless is a way to communicate that the consumer won't need to provision and maintain servers. It is true.
Wireless phone means you can move for quite long distances while talking without any wire connecting your device to the wall. It is true.
You may argue they're "misnomers". But to call it a "lie" is quite a stretch...
Can you give an example? You mean backups, OS patches, firewall config, etc etc? Are they like Hetzner?
Any more stories or knowledge of them being disingenous?
I'm definitely not buying anything there.
I run a free service on fly.io.
It's been ok lately (or maybe my users just stopped complaining), so maybe they got better.
We assume the free version to have similar quality to the paid one. It's in the vendor interest, so that we like it and become a paid customer. If they can't convince us of their quality in the free version, how are we supposed to trust the paid one?
As for this post, it's not managed SQLite but rather an open source project called LiteFS [1]. You can run it anywhere that runs Linux. We use it in few places in our infrastructure and found that sharing the underlying database for internal tooling was really helpful for that use case.
[1]: https://github.com/superfly/litefs
I mean, I get it, from a technical standpoint. Ok, so you're going to send read-only Sqlite databases to everybody.
Is it missing what the API (that you still need) is updating when you insert or update something and all client DBs are now stale? Is there a central database? How often are you pushing out read-only database replicas across the wire to all clients? Is that really less "chatty"? If so, how much bandwidth is that saving to push an entire database multiplied by the number of clients?
None of this seems logical. Maybe I'm missing the real-world use-case. Are we discussing tiny Sqlite databases that are essentially static? Because in the last 30 years I've not run into a situation where I needed to have clients execute SQL queries on tiny, static databases let alone still need to potentially update them also.
User can open the application, get the database, the frontend does all the offline updates the user want to perform. The user can update their website, add bookings they received on the phone, check what prices they set. This is all blazingly fast with no loading time, no matter your internet connection, because no further communication with the server is needed.
At some point they press a magic Publish button and the database gets synced with upstream and uploaded. The upstream service can take the data and publish a website for its users, update availabilities, update prices, etc.
It would be a better user experience than 99% of the SaaS out there.
I just checked a heavy user for our enterprise SPA. localStorage is using ~12 KB.
I don't know what people are just throwing in localStorage but they certainly aren't pulling down much of the database (which still would have to be checked to ensure cached data in there isn't stale).
Now, you'll introduce a huge amount of user frustration around why their changes never made it to the central database. If you have users closing a form, especially if it's on a webpage, they are going to expect the write to happen at the same time the form closes. Making a bunch of changes and batching them in a single sync is going to be confusing to a ton of users.
The thing that I'm unclear on is how do I figure out what data to ship to the user? Like if I don't already have a database-per-user then I have to extract only the data the user has permission to see, and ship that as a database?
That would be the case even if I had database-per-customer - not every user is necessarily able to see all the data owned by the organization they're a part of.
It seems like a lot of extra work, and error-prone, too (what could be worse than accidentally shipping the wrong data _as an operational database_ to a client?)
Edit: the article covers this at the bottom, but IMO it's a show-stopper. How many applications of any real complexity can actually implement database-per-user (database-per-tenant is probably not enough, as I mentioned above). As soon as you need any kind of management or permissions functionality in your app then you can't ship the database anymore, so you may as well start off not shipping it.
You might remember horror stories from the late 1900s when people would lose critical data and documents because they "forgot to hit Save and the computer crashed." Or, maybe you've never experienced this — because a couple decades of distributed-systems engineers and UX researchers made that problem obsolete.
So now we've... reinvented the Save button, but somehow needed a Paxos implementation to do it?
Everything old is new again, I guess.
I want to decide when to save, and how, not the application. I am not a product, I am a user!!!
If I open a word document, redact a few bits, save to PDF and close it - I don't want my changes saved but they are (real scenario from last week. I'd only just moved the file into Onedrive so autosave had turned itself on)
Ditto sorting and filtering spreadsheets.
Software engineers: By all means save in the background so I never lose anything, but don't assume I want the latest changes.
This is not trivial to implement, though. Complexity will depend a lot of the application and the data. I'd say it'll only make sense for mature apps. A startup will most likely don't consider this in its roadmap.
"get the database"
How small do you think these databases are?!
You're going to download the entire hotel booking platform's database?
For how many hotels? One at a time, and then get another databse? Or are you getting a Sqlite booking database for every hotel in the world? And you're going to send them to each user? For what date range?
And even if that were possible, you then have to commit your offline updates. What if someone else booked the date range prior to you? Now your Sqlite copy is stale. Download the entire thing again? There could have been countless changes from other users in the time since you last got your copy.
This explanation just leaves me even more confused. It's illogical.
Jokes aside, this extreme optimization for development does have impacts on user experience. The amount of bandwidth/storage etc used by a "just ship the whole db" type applications would surely suck for most people outside of the 'I measure my bandwidth in Gbps' crowd?
For this project, I'd do:
* A database for each user (this is natural on CouchDB, one can enable a setting to create the user DB automatically when a user is created. The platform won't choke on many databases) - for some per-user data, like comments, if the user has already reserved a booking we can mirror booking data there + some info regarding the hotel.
* Common synced databases - some general info regarding the platform and hotels. Preferably not too large (IIRC there's no limit to attachments with 3.x, but it sounds risky). Anything too large we'd do online or use data provided with the application.
* A large booking database which isn't synced and must be modified online with a REST call - we don't have to allow every action offline. Here I wouldn't entirely dispense with API. This obviously needs the online component for making sure bookings don't conflict. Could even be a regular relational database.
I think it is possible to implement this the CouchDB database-way: a provisional offline reservation to the user database followed by some rejection method on the server when syncing, but I don't think there's much value to the user here. This design however would allow us to not sync all the data but a much smaller portion while supporting offline.
---
It sounds very doable, rather similar to a project I was involved with, but I miss SQL a lot with that platform (javascript NoSQL not my favorite to work with). A sqlite-based approach is an interesting alternative.
It doesn't sound that unreasonable.
Even if you have 3-4 hotels your data won't be significant.
By the time your dude downloads the database half the hotels on your imaginary website have changed status.
So the assumption that "if anybody changes anything everybody needs to know it" is close to true. In that scenario, putting a bunch of APIs in the way just makes the data less coherent rather than more. In most other scenarios, yeah, it's hard to see that it really makes sense.
In what use case do you run into that?
I'm lead and an architect on an enterprise application at the moment that drives the whole company. It's your standard configuration, front-end, APIs, SQL. The system requests what it needs to fulfill only what functionality the user is dealing with.
Earlier in my career I was dealing with large enterprise desktop applications that talked directly to the database, with no centralized API. Some of them had thousands of individual desktop clients hitting a single multi-tenant SQL server. No problem, SQL Server would handle it without breaking a sweat. The bandwidth to an indidual client was fine. It was fast. And that was 20 years ago.
- try to reduce the amount of paper based catalog we were sending out to customers, those were a couple thousands of pages and not cheap to produce, not cheap to send and would get deprecated very quickly. The web app would be pulling the entire catalog at first load so customers could go in remote location and still be able to use the catalog
- a web app for sales that was intended to be use on customer site containing all the marketing materials and much more during presentations on site without ever having to connect anywhere
Single-tenant simple sites without permission checks with limited content volume.
Of course you can also do the same in a multi-tenant environment but naturally you wouldn’t be returning all rows in the database.
1. Internal tooling: you're able to manage the contract better since you have control of the source & destination applications.
2. Reporting & analytics: these tend to need a lot of query flexibility & they tend to use a lot of resources. Offloading the query computation to the client makes it easier on the source application.
As for database size, the Corrosion program mentioned in the post is about 8GB and has a continuous write load so this doesn't have to just be for tiny databases.
Basically, the solution they have is:
1. There is a single writer. There's optional best-effort leader election for the writer.
2. If there's a network partition, split-brain, etc, availability is chosen over consistency.
Jepson's testing is focused on databases that pick "consistency". Since LiteFS didn't pick consistency, there's really not any point in running Jepson against it. Like, jepson would immediately find "you can lose acknowledged writes", and LiteFS would say "Yes! That's working exactly as intended!"
However, another way of running LiteFS is with only a single writer ever (as in one app, one server, one sqlite database only), and all clients as read-only replicas that are not eligible for taking writes ever. In that case, you also don't have a proper distributed system, just read only replicas, which is quite easy to get right, and mostly what this post is talking about.
On the LiteFS Cloud side, it currently does streaming backups so it has similar guarantees but we are expanding its feature set and I could see running Jepsen testing on that in the future. We worked with Kyle Kingsbury in the past on some distributed systems challenges[1] and he was awesome to work with. Would definitely love to engage with him again.
[1]: https://fly.io/dist-sys/
I don’t have a horse in this race the reply isn’t very well I don’t know what to make of that
Some might be using Postgres or whatever the Engineering team provided them with, but I don't think I have really heard of a Data person preferring to use SQL Lite.
Might still be a great fit, but as the other comment pointed out, it might not be a good fit for the target audience.
Are there any plans for Corrosion to be published as OSS?
> 1. Internal tooling: you're able to manage the contract better since you have control of the source & destination applications.
This has not been my experience in anything but tiny companies or companies with very strict mono-repo processes. One big point of separate teams is to minimize the communication overhead as your organization grows (otherwise it scales as N factorial). That means you do not want a lots of inter-department dependencies due to the internal tooling and APIs they leverage.
> 2. Reporting & analytics: these tend to need a lot of query flexibility & they tend to use a lot of resources. Offloading the query computation to the client makes it easier on the source application.
Depends on the resources the client has versus the server to devote to a single query. The resources are also high because of how much data is analyzed and 8gb seems tiny to me (ie: the whole thing can be kept in some DBs memory).
They are adding a sync-mechanism for Sqlite so local writes can be synced to a remote location and on into Postgres and so on. CRDT-based eventual consistency.
So local write latency, eventually consistent sync and the tools for partial sync and user access are the primary areas of development.
Early days but an interesting approach to shipping the db.
I replicate the clients Sqlite DB to a central server (over a REST-API) where it is synced with existing data. I use an CRDT, so changes don't get lost and as long as the clocks of all the users devices are accurate, the merges are in the correct order.
This enables offline access, but also the use of my app without an account. You can always merge the data later. Multi-device merge is also possible, if you create an account. Especially the multi-device merge was a big headache until I settled for this approach.
Since it is a home-grown solution I still have to do some manual stuff that could be abstracted away, like converting datatypes to and from JSON, Kotlin, PHP, MySQL. There's not always an equivalent datatype in those technologies.
This approach probably won't work well for big databases that share data between multiple users, though.
It was not uncommon in the early 2000's for applications targeting mobile professionals and sales types to work this way, except that instead of SQLite they used things like Notes¹, Jet² and MSDE³. By modern standards these would be considered "tiny mostly static databases" often not exceeding a gigabyte. Instead of connecting over the internet they used serial dial-up file transfer protocols to synchronize with a central database or a mainframe. People would typically download information in the morning, make updates offline and synchronize at the end of the day. A slow trickle of periodic bidirectional updates insured everyone had a reasonably fresh copy of the information they needed.
1- https://en.wikipedia.org/wiki/HCL_Domino
2- https://en.wikipedia.org/wiki/Access_Database_Engine
3- https://en.wikipedia.org/wiki/MSDE
So would this work equally well? Use Postgres on some cloud and “spin up” a read-replica that your clients have access to. Let them read from the replica.
If yes, I have tried that and I can tell you the problem I had. They don’t want to get to know my database schema. I know, frustrating! “If you would just get to know me better, you would have all the secrets you desire!” Sigh… they just want this overly simplistic Interface that doesn’t change, even when we rewrite things to make them better on the backend.
Another thought: if sharing compute is the main problem, you can just egress the data to distributed file storage like S3 and let them write SQL on the files using some Presto, like Athena. Or egress to BigQuery and let them bring their own compute. But the problem there again is getting to know my internal data structure, and also by “egress” I really mean “dump the whole damn thing because once the data is on S3 I can’t just update a single row like I can on an OLTP database” and you can only do that every so often.
Querying to S3 is a good approach too. Those query tools can be somewhat involved to setup but definitely a good option.
It seems like it would be a good fit with edge servers, assuming any node that starts an instance could get a copy of the database when it starts. Most database-as-a-service companies don't do that.
I doubt I'd try it if it isn't a fully managed system, though. The only one I know of is Cloudflare's Durable Objects, and it's not in the free tier. For hobbyist programming that's kind of a drag, so I haven't tried it. Instead I'll probably try Deno's KV. (This sort of thing is what I hoped KV would be, but apparently it isn't.)
I wonder what the cold-start performance would be for downloading a small SQLite database from S3, starting it up in memory, and subscribing to a replication log?
The author touches on 2 issues that are tangential to this problem, restricting unauthorized data, and mutations, both of which are not really easily solvable with the cloned database solution.
However, the underlying theme of skipping the API is extremely compelling. So much code, both FE and BE, are just to serve as an elaborate interface to the database. Simplifying this will be a huge gain in software maintenance. But the challenge is to do it safely.
1. https://docs.heimdallm.ai/en/main/blog/posts/safe-sql-execut...
It's not for a single-shot test, but for continued use with data synchronization.
>A cloned database view is some representation of your database that has been post-processed to only contain data that the user is allowed to see. This representation would typically be in the form of a separate user-specific sqlite database file. Because the cloned database only contains data that is theirs, a user may issue any query against it, and there is no risk to data security. And if a malicious user was able to issue a DDL statement, it would only affect their cloned database.
Maybe there is a better word than "cloned database view." Replica doesn't feel quite right though.
APIs are an intermediate layer of abstraction needed because their purpose is separate from that of a DDL/DML/DCL/TCL. Having raw database access does not solve the use cases the database doesn't account for.
Each instance gets its own eventually consistent read only copy of the db. So instead of making a network request to go get some data, you query your local cache.
Your use case is that you are read heavy for this to fit your needs.
If you have a copy of the data local to you, you don’t need to go bug another team to expose it via an API.
Reading fly’s blogposts it’d be good to see more on write durability. They’re batched on the client, batched on fly’s side before being compacted to s3 from reading so you can loose writes before your client as sent them to fly, possibly loose them before fly has written to s3 depending on how they implemented.
FlyCloud might make a great service for small typical Wordpress type sites or low user services where writes are low. It’s a shame they don’t offer pay as you go for the LiteFS product only. I get why they don’t as they want you to host with them but it’d be great if you could pay as you go on the db only.
I currently use DynamoDB for low volume stuff for cost reasons, it costs me cents. Productivity is extremely low though with Dynamo as you need to think very hard about table design and constraints and work around those constraints. It’s a lot more effort than using something like SQLLite for quick simple stuff.
(1) https://hn.algolia.com/?dateRange=all&page=0&prefix=true&que...
Or did that train leave a long time ago?
What?
No.
This is a great point and the reason why I think server-side rendering will ultimately fail.
I've done something similar for a site I manage (6groups.com). Each client's DB log is stored in compressed files on the server and only initialized via sqlite wasm on the client when needed. The trick is to periodically run compaction jobs (also on the client) to make sure DB logs don't grow to unreasonable sizes
I can't imagine the DB consumption I'd have if all client data was in relational format on the server
We already lock the entirety of each customer’s data when processing a request, in order to avoid data inconsistency from concurrent mutations.
One SQLite database per customer is a really appealing idea.
The use case shared by fly.io is for internal tooling. If you have a small team that most likely won't grow to more than a few dozen devs, maybe you can get away with it.
Shipping it to several external customers will be a horrible nightmare in maintenance and dev productivity. I'd switch jobs if I was working in a project that started applying this. Paying for the HTTP API upfront is a cheap price to avoid the costs of not having it down the road.
Do you think your customers would accept this as a test? They would run against an in-memory database if they wanted to.
If clients are internal services you own and you control as part of a internal distributed service, it’s a nice pattern