15 ms·

Skip the API, ship your database

301 points by danielskogly 3y ago

mnming 3y ago
Distrust is building for Fly. Their articles ooze arrogance, whether it's hyping up their "unique" hiring methods or their "managed" Postgres service. They even gloss over serious flaws like data loss in LiteFS. Their latest article is another red flag for me. Started off intriguing, but now, I wouldn't trust them as far as I can throw them.
- benbjohnson 3y ago
  Author here. My goal with the article was to write about an use of LiteFS that I found to be useful and show the benefits and trade-offs. I don't think it's a general purpose technique for everyone but I think it has its place in some cases. What was it about the post that oozed arrogance?
bjord 3y ago
To me, this looks a lot like Sequin (https://sequin.io https://sequin.io), minus the ability to write back in the other direction (but plus open source!).
freedomben 3y ago
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.
- danielvaughn 3y ago
  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.
  - endisneigh 3y ago
    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.
    - danielvaughn 3y ago
      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.
- 10000truths 3y ago
  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 5. Remove all write queries to "my_foo_v1" 6. Drop the "my_foo_v1" table
maxbond 3y ago
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.
- benbjohnson 3y ago
  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.
  - maxbond 3y ago
    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?
    - benbjohnson 3y ago
      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.
simonw 3y ago
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.
- javajosh 3y ago
  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.
  - simonw 3y ago
    The same is true for APIs, too. Making changes to a JSON API that won't break existing clients isn't necessarily an obvious skill. But it can be learned, as can backwards-compatible database schemas.
- maxbond 3y ago
  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.
  - thestepafter 3y ago
    Even better, use stored procedures / routines on top of views and only query the stored procedures from the frontend.
chimen 3y ago
"Skip the api" - seems to be stolen from sequin at: https://docs.sequin.io/integrations/airtable/playbooks/metabase https://docs.sequin.io/integrations/airtable/playbooks/metab... (a few scrolls down)
- benbjohnson 3y ago
  Author here. I hadn't heard of Sequin until today. "Skip the API" was just a snappy title so I went with it.
Zaheer 3y ago
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.
- benbjohnson 3y ago
  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.
KRAKRISMOTT 3y ago
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.
- nirushiv 3y ago
  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?
- impulser_ 3y ago
  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. https://github.com/superfly/litefs 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/ https://fly.io/docs/litefs/backup/
  - pests 3y ago
    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?
    - impulser_ 3y ago
      Open up the Dockerfile they generate for you.
deleted 3y ago
[deleted]
munk-a 3y ago
An XMLRPC by any other name would smell as rancid.
m3kw9 3y ago
How would I serve terabytes of stuff?
- Petersipoi 3y ago
  You wouldn't use this if that was a requirement
- benbjohnson 3y ago
  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.
EMM_386 3y ago
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.
- jokethrowaway 3y ago
  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.
  - whalesalad 3y ago
    A lot of SPA’s already operate this by leveraging things like localstorage in the users browser.
    - EMM_386 3y ago
      To do what? Isn't this limited to 5 MB? 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).
quickthrower2 3y ago
Is this spiritually just like using a Redis cache?
jimmytucson 3y ago
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.
- benbjohnson 3y ago
  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.
skybrian 3y ago
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?
Supply5411 3y ago
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. 1. https://docs.heimdallm.ai/en/main/blog/posts/safe-sql-execution.html#cloned-db-views https://docs.heimdallm.ai/en/main/blog/posts/safe-sql-execut...
- rmbyrro 3y ago
  The pattern here involves database replicas, not clones. It's not for a single-shot test, but for continued use with data synchronization.
  - Supply5411 3y ago
    From the post: >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.
    - rmbyrro 3y ago
      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.
throwawaaarrgh 3y ago
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.
tiew9Vii 3y ago
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.
- benwaffle 3y ago
  You can use LiteFS Cloud without hosting your app on fly.io - https://fly.io/docs/litefs/getting-started-docker/#litefs-cloud-configuration-optional https://fly.io/docs/litefs/getting-started-docker/#litefs-cl...
ilrwbwrkhv 3y ago
If I can't update the data and I need an API to do that, then it's a static site. I don't need a database in the first place.
1vuio0pswjnm7 3y ago
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".
thyrox 3y ago
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. (1) https://hn.algolia.com/?dateRange=all&page=0&prefix=true&query=fly.io%20down&sort=byPopularity&type=story https://hn.algolia.com/?dateRange=all&page=0&prefix=true&que...
moomoo11 3y ago
What happened to keeping things simple and scaling them? Or did that train leave a long time ago?
zubairq 3y ago
A very interesting idea. At Yazz we don't do replication, but if someone creates a report in the tool and exports it as HTML then we actually export the entire app DB into Sqlite embedded in the HTML page, but any changes made to that embedded Sqlite DB don't get replicated to the original database, so kind of like a read only copy, only suitable for reports
LoganDark 3y ago
> The typical approach to making data available between services is to spend weeks designing an API What? No.
collaborative 3y ago
> Moving compute to the client 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
et1337 3y ago
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.
- rmbyrro 3y ago
  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.
- knallfrosch 3y ago
  > If they just downloaded the whole state, mutated it, and reuploaded it, the whole thing would be done in 2 seconds. Do you think your customers would accept this as a test? They would run against an in-memory database if they wanted to.
emodendroket 3y ago
Surely you will not ever regret exposing all the internal representations of your data to your clients.
- tiew9Vii 3y ago
  It depends who your clients are. If clients are public/external users, probably not. If clients are internal services you own and you control as part of a internal distributed service, it’s a nice pattern
  - emodendroket 3y ago
    Even then it's often a choice you will regret down the line as you wish to evolve away but it becomes a huge ordeal.
abdullin 3y ago
There also is a productivity boost within this approach. When you have a local DB as an integration point, you could just upload it to ChatGPT Code Interpreter for building extra reports and running analysis on it. Alternatively, upload the schema to ChatGPT and “write these few methods with unit tests for me, please”. I’m not saying that this approach is universal, but it saves me an hour of time now and then.
langsoul-com 3y ago
What's the comparison of this approach VS directus, which auto creates an api layer on top of dB
pm90 3y ago
It seems like sqlite is now acting as the API gateway. I think its an interesting idea, especially if you want to share relational data out to consumers, since they now have all the flexibility of sql. The issue though is updating the queries when schema changes are made.
Zvez 3y ago
If you give access to your DB directly, your API effectively becomes your API with all the contract obligations of the API. Suddenly you don't completely control your schema: you can't freely change it, you need to add things there for your clients only. I've seen it done multiple times and it always end up poorly. You save some time now by removing the need to build API, but later you end up spending much more time trying to decouple your internal representation from schema you made public.
- MoSattler 3y ago
  I think this point is addressed in the article.
  - herodoturtle 3y ago
    Came here to say this too. From the article: > A less obvious downside is that the contract for a database can be less strict than an API. One benefit to an API layer is that you can change the underlying database structure but still massage data to look the same to clients. When you’re shipping the raw database, that becomes more difficult. Fortunately, many database changes, such as adding columns to a table, are backwards compatible so clients don’t need to change their code. Database views are also a great way to reshape data so it stays consistent—even when the underlying tables change. Neither solution is perfect (raw read replica vs API). Pros and Cons to both. Knowing when to use which comes down to one's needs.
- oreilles 3y ago
  Versioned views, materialized views or procedures are the solution to this. It is frequent that even internally, companies don't give access to their raw data but rather to a restricted schema containing a formated subset of it.
  - vmfunction 3y ago
    In addition if you are using postgres, then there is postgresRest to make api really quick and nice.
giovanni_or2 3y ago
BS. API layers exist for a reason. Maybe they will realize that reason if they try to expose the DB...
mkl95 3y ago
In the beginning of my career, I did an internship at a very small company that had been selling a desktop app for 20+ years, among other stuff. Each customer got their own database. The software was pretty stable and the team were part time consultants for that app, which mostly involved helping users perform complicated queries and creating stored procedures (they loved those). Subscription was allegedly quite expensive and had minimal churn over the years. For that kind of hyper stable software where each customer get their own database, I can see it working. In your typical 2023 SaaS where you are serving 1000s of users through a complex API and a database, I'm not sure a db can give you the level of flexibility and quality of life an API gives you.
Takennickname 3y ago
I asked chatgpt for a solution looking for a problem and it sent me here
solatic 3y ago
Sounds like the next step is some way to define a multi-table view over a more traditional RDBMS (e.g. Postgres), serving as a source of truth, that can be continually exported as a read-only SQLite, so that the SQLite file can be pushed to the edge and read from the edge? I'm not sold on the idea of per-customer databases; businesses still need analytics, so if you're not running your analytics on the centralized DB itself, your app needs to write/push the data somewhere else, which is an unnecessary complication early in the product lifecycle at best and a recipe for frustration at worst.
- vincnetas 3y ago
  remember the times when software companies shipped software to users, and users used the software offline. No one from business even dreamed of knowing what each user was doing with the software at any moment in real time. What data users stored etc. Today it looks like this is MUST have for any software and no bussines is possible without this.
  - solatic 3y ago
    > No one from business even dreamed of knowing what each user was doing with the software at any moment in real time Surely you jest. Not knowing what your users are doing with the software was the number one problem with product development in the 90's. The only solution was user interviews and real-time in-person observation, which was highly expensive, thus making the cost of software much more expensive. > Today it looks like this is MUST have for any software and no bussines is possible without this Because more and more software is delivered over the Internet through low-touch sales methods. More often than not, software producers don't even know who their users are - how do you conduct user interviews, expensive as they may be, if you don't even know who your users are? Trying to run a modern business without a modern analytics stack is functionally equivalent to driving with a blindfold on.
    - anymouse123456 3y ago
      Just want to temper this a little bit. Yes, information can be better than no information, but user analytics is not the holy grail it pretends to be and can be worse than no information at all. I led a team a BigCo for some years that had access to mountains of highly detailed user data that enabled product, eng and UX folks to ask very specific questions about how successful various changes were, in terms of our business and product goals. An outrageous amount of time was invested in this process. Designing, describing and negotiating experiments, collecting data over some days or weeks and in almost every case, the data was basically inconclusive and every question would spawn more questions. Enormous time was spent digging and sifting through this data, while even people with the best of intentions would almost always disagree about what the data was telling us. Eventually, some quant showed up and explained how exactly zero experiments we ever did were statistically significant, and that if we wanted to achieve significance, we would need to run a 1% experiment on our many millions of users for more than 30 days. The organization collectively shrugged at this information and went right back to the time worn rituals of success theater. What I learned in that experience was that developing great products requires experience, skill, intuition, grit and visibility, but that visibility might be the least critical of those requirements, not the most. Put another way, there is almost always a simpler, clearer measure of success that doesn't require spying on everyone all the time and then sifting through what is actually worse than useless information.
k_vi 3y ago
I've tried doing this read & updates using Postgrest and row-level-security using Supabase. When it works its an amazing experience but even for semi complex stuff you would still need to use Postgres RPC, which is still another "API" layer. I find writing API using SQL a nigtmare. Simple queries like this don't work on Postgrest: `update likes set likes = likes + 1;`
Hakkin 3y ago
Even if you "skip the API" you still have to think about access patterns for the data, otherwise client-side query performance is going to be abysmal. You would basically have to define your "API" via what indexes you create on the data.
- abdullin 3y ago
  With a local SQLite database you don’t need to pay cost of network transfers. A dozen of local SQL calls could be way faster than a single API call within a region.
  - Hakkin 3y ago
    That's true, but low query latency doesn't mean much if you have to do a full table scan for your query. Some thought still needs to be put into how the clients will query the data, so it's really not as simple as just "ship your entire database to the client and let them figure it out".
RamblingCTO 3y ago
> Moving compute to the client I thought we were slowly learning that this was a stupid idea in the first place and moving back to the old ways (tm)?
3cats-in-a-coat 3y ago
The lifecycle of a startup full of young arrogant naïve inexperienced souls: 1. Why is everyone doing all these complicated things?! We stopped doing them! We're much smarter than everyone! 2. Few years later. 3. At great financial, technical and business cost we redesigned our system to do all those complicated things, because our initial version was an unmaintainable spaghetti monster that wrapped up everything its tentacles touched and hugged it until it died. There are situations when exposing SQL is a right call. I'd say those are pretty narrow, say when you have colleagues in a partner company doing integration. SQL is not a way to avoid having an API. SQL is an API. The surface of this API is gigantic, and it also binds your customers to a particular implementation of your database (the SQL vendor you happen to use). If those customers are few partners, this is easier to change. When it's the public at large... you're effed. This is all pretty basic systems analysis, but kids these days don't know the first thing about it. But here's a basic rule of thumb: exposing SQL as your API may be the right call if you've spoken in person with every customer who will be using that API, and especially if this in-person contact is regular, say at least once a month. If you haven't, then SQL is not a good API for that customer.
- thinkingemote 3y ago
  This is 100% the pattern. However most startups (and most software projects if we think about it) fail. They don't make it to year 2. This in built failure rate shouldn't be a reason against doing things right the first time but it might explain how many don't get to learn the lessons from year 2. Most of the time we will never get to redesign and realise the mistakes we made.
lambda_garden 3y ago
This is not equivalent to (say) a single Postgres database where we get shared state with transactions between all users - or have I missed something?
shimst3r 3y ago
As someone who has worked as a data engineer on an organically grown monolith, the last thing you want to do is ceding control over your schema due to the lack of (sensible) abstraction. Everyone will change it, nobody cares about standardization. It’s utter chaos and a data engineering nightmare. This is the same, plus the lack of proper authN/ authZ. Good luck passing your next audit.
ricardobayes 3y ago
I've seen teams take this ad literam and actually shipped an Elastic instance in an iFrame as a "product". Needless to say users abandoned it in no time.
greo 3y ago
I used to be a big fan of fly.io until I faced a lot of deployment issue. Often times the server will go down for few minutes and it was not even updated on the status page. Is this still the case or has things gone better?
EspressoGPT 3y ago
This must be from a person who never had to deal with when the database actually was the API.
snowstormsun 3y ago
While you're at it, skip the internet and burn the database on DVDs, then distribute them!
rzarate 3y ago
Kudos to the author(s) for building LiteFS, it really does seems like an interesting project. However, I can't see this being a popular pattern moving forward (or at least I hope that's not the case). Replacing APIs with DBs in real production workloads? Really? I don't have much else to say. Also, the value proposition is not quite clear to me. Using replication to have local, read-only replicas of an SQLite db might admittedly be beneficial for high read-throughput workloads but I'd imagine that the DB might have serious size/read-throughput constraints due disk space and network overhead from replication. My takeaways are: a) This doesn't sound like a reliabile pattern to build distributed services. b) It's bad PR for fly to publicly push for the adoption of this pattern. c) It's even worse PR that fly is using this internally.
- jampekka 3y ago
  Replacing APIs with DBs is a good idea. I don't have much else to say.
  - TjZkxkxeky 3y ago
    It's certainly not a new idea, however. Every product that allows backdoor access into their database directly has effectively done this. Hope you have your schema correct when your customers build a mountain of cards on top of your database schema.
    - jampekka 3y ago
      How does this differ from them building a mountain of cards on top of your API? The schema used by the public API doesn't have to match the database layout itself. E.g. views can be used for this. A lot of APIs are quite straightforward mappings typically from REST/GraphQL to a database, often via some sort of ORM or other abstaction. These extra layers mostly amount to needless complexity, more surface of bugs, lots of busywork and worse API usability.
      - TjZkxkxeky 3y ago
        It differs dramatically. Your API is likely much much smaller than the inherent API of SQL tables. It's a difference in degree that is a difference in kind. Anyways, every old idea must be retried.
remon 3y ago
Exposing your schema directly is an anti-pattern for good reasons. Don't do this. You want to be able to change a contract/interface/API independently of your persistence strategy.
- robertlagrant 3y ago
  You might want to. You don't have to. It's a tradeoff, not a law.
tamrix 3y ago
Skip the database. Pass a gaint json document back and forth.
dangoodmanUT 3y ago
Isn't this effectively just letting all services access a central database? E.g. letting them all run SQL queries against Postgres? Seems like overengineering to replicate the DB to each machine when we have Postgres with read replicas or dbs like cockroachdb. Like why replicate your 10GB DB to all 30 of your machines when they can access a 3x3 DB cluster instead? Seems like it's asking for issues, as now your machines are no longer stateless and have to play catch up if they go down before they can serve queries.
jacobgorm 3y ago
I wonder why they chose to implement the LiteFS as Fuse module, instead of simply hacking SQlite to intercept the IO operations before they go through to the OS. Yes it would require a patch and a recompile, but performance would be superior by not having to go through the kernel and Fuse, and there would be fewer moving parts.
- tptacek 3y ago
  LiteFS works this way so that its users can use the unmodified SQLite libraries they'd normally use.
- simonw 3y ago
  That's what the in-active-development LiteVFS does, except using a SQLite extension: https://github.com/superfly/litevfs https://github.com/superfly/litevfs
kat_rebelo 3y ago
Even for more some more advanced use cases such as OLAP or ML related pipelines, in my experience, it takes a single senior developer a couple of days to design and implement a REST API, not weeks. That claim is overblown FUD.
consoomer 3y ago
I like the idea of exposing the database and pushing more business into the database.. but it's terrifying. Row level security and exposing postgres to the world sounds cool, but I can't see myself doing it. It's like... encryption is awesome in theory and protects your data, but would you push your password-store to a public github repo? In theory you could... edit: A quick Google-fu and it looks like some people do push their password-store to Github... balls of steel. https://github.com/jysperm/passwords https://github.com/jysperm/passwords
Rudism 3y ago
From the perspective of someone who's been neck-deep in a highly complex PostGraphile project for several years now, I wholeheartedly and strongly recommend that you SHOULD NOT expose your database or autogenerate an API from your database. Maybe it's fine if you have a really basic schema and the API requirements are just a few CRUD endpoints with no business logic, but as soon as you go past that you'll find yourself in a world of pain. One upsetting side effect of giving API consumers the ability to query your data arbitrarily, is they will absolutely find the most inefficient and resource-intensive ways to do it. And the blame when the "API" is taking several minutes to come back with data will be laid squarely at your feet even though the cause of the problem may not be under your control at all.
k__ 3y ago
I think, I found a good use case for this. I'm running an gateway that uses a SQLite database to index transaction data. One instance loads all TXs and indexes them to SQLite and LiteFS replicates the database to other instances. Down-side: there are copies of the database on every machine. Up-side: Saves work, since only one machine needs to index and reduces latency for reads, as every machine reads from a local SQLite replica. Bonus: LiteFS Cloud keeps backups of the database.

301 points by danielskogly 3y ago

Distrust is building for Fly. Their articles ooze arrogance, whether it's hyping up their "unique" hiring methods or their "managed" Postgres service. They even gloss over serious flaws like data loss in LiteFS. Their latest article is another red flag for me. Started off intriguing, but now, I wouldn't trust them as far as I can throw them.

benbjohnson 3y ago
Author here. My goal with the article was to write about an use of LiteFS that I found to be useful and show the benefits and trade-offs. I don't think it's a general purpose technique for everyone but I think it has its place in some cases. What was it about the post that oozed arrogance?

bjord 3y ago

To me, this looks a lot like Sequin (https://sequin.io https://sequin.io), minus the ability to write back in the other direction (but plus open source!).

freedomben 3y ago

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.

danielvaughn 3y ago
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.
- endisneigh 3y ago
  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.
  - danielvaughn 3y ago
    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.
10000truths 3y ago
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 5. Remove all write queries to "my_foo_v1" 6. Drop the "my_foo_v1" table

maxbond 3y ago

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.

benbjohnson 3y ago
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.
- maxbond 3y ago
  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?
  - benbjohnson 3y ago
    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.

simonw 3y ago

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.

javajosh 3y ago
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.
- simonw 3y ago
  The same is true for APIs, too. Making changes to a JSON API that won't break existing clients isn't necessarily an obvious skill. But it can be learned, as can backwards-compatible database schemas.
maxbond 3y ago
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.
- thestepafter 3y ago
  Even better, use stored procedures / routines on top of views and only query the stored procedures from the frontend.

chimen 3y ago

"Skip the api" - seems to be stolen from sequin at: https://docs.sequin.io/integrations/airtable/playbooks/metabase https://docs.sequin.io/integrations/airtable/playbooks/metab... (a few scrolls down)

benbjohnson 3y ago
Author here. I hadn't heard of Sequin until today. "Skip the API" was just a snappy title so I went with it.

Zaheer 3y ago

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.

benbjohnson 3y ago
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.

KRAKRISMOTT 3y ago

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.

nirushiv 3y ago
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?
impulser_ 3y ago
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. https://github.com/superfly/litefs 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/ https://fly.io/docs/litefs/backup/
- pests 3y ago
  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?
  - impulser_ 3y ago
    Open up the Dockerfile they generate for you.

deleted 3y ago

[deleted]

munk-a 3y ago

An XMLRPC by any other name would smell as rancid.

m3kw9 3y ago

How would I serve terabytes of stuff?

Petersipoi 3y ago
You wouldn't use this if that was a requirement
benbjohnson 3y ago
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.

EMM_386 3y ago

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.

jokethrowaway 3y ago
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.
- whalesalad 3y ago
  A lot of SPA’s already operate this by leveraging things like localstorage in the users browser.
  - EMM_386 3y ago
    To do what? Isn't this limited to 5 MB? 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).

quickthrower2 3y ago

Is this spiritually just like using a Redis cache?

jimmytucson 3y ago

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.

benbjohnson 3y ago
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.

skybrian 3y ago

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?

Supply5411 3y ago

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. 1. https://docs.heimdallm.ai/en/main/blog/posts/safe-sql-execution.html#cloned-db-views https://docs.heimdallm.ai/en/main/blog/posts/safe-sql-execut...

rmbyrro 3y ago
The pattern here involves database replicas, not clones. It's not for a single-shot test, but for continued use with data synchronization.
- Supply5411 3y ago
  From the post: >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.
  - rmbyrro 3y ago
    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.

throwawaaarrgh 3y ago

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.

tiew9Vii 3y ago

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.

benwaffle 3y ago
You can use LiteFS Cloud without hosting your app on fly.io - https://fly.io/docs/litefs/getting-started-docker/#litefs-cloud-configuration-optional https://fly.io/docs/litefs/getting-started-docker/#litefs-cl...

ilrwbwrkhv 3y ago

If I can't update the data and I need an API to do that, then it's a static site. I don't need a database in the first place.

1vuio0pswjnm7 3y ago

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".

thyrox 3y ago

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. (1) https://hn.algolia.com/?dateRange=all&page=0&prefix=true&query=fly.io%20down&sort=byPopularity&type=story https://hn.algolia.com/?dateRange=all&page=0&prefix=true&que...

moomoo11 3y ago

What happened to keeping things simple and scaling them? Or did that train leave a long time ago?

zubairq 3y ago

A very interesting idea. At Yazz we don't do replication, but if someone creates a report in the tool and exports it as HTML then we actually export the entire app DB into Sqlite embedded in the HTML page, but any changes made to that embedded Sqlite DB don't get replicated to the original database, so kind of like a read only copy, only suitable for reports

LoganDark 3y ago

> The typical approach to making data available between services is to spend weeks designing an API What? No.

collaborative 3y ago

> Moving compute to the client 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

et1337 3y ago

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.

rmbyrro 3y ago
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.
knallfrosch 3y ago
> If they just downloaded the whole state, mutated it, and reuploaded it, the whole thing would be done in 2 seconds. Do you think your customers would accept this as a test? They would run against an in-memory database if they wanted to.

emodendroket 3y ago

Surely you will not ever regret exposing all the internal representations of your data to your clients.

tiew9Vii 3y ago
It depends who your clients are. If clients are public/external users, probably not. If clients are internal services you own and you control as part of a internal distributed service, it’s a nice pattern
- emodendroket 3y ago
  Even then it's often a choice you will regret down the line as you wish to evolve away but it becomes a huge ordeal.

abdullin 3y ago

There also is a productivity boost within this approach. When you have a local DB as an integration point, you could just upload it to ChatGPT Code Interpreter for building extra reports and running analysis on it. Alternatively, upload the schema to ChatGPT and “write these few methods with unit tests for me, please”. I’m not saying that this approach is universal, but it saves me an hour of time now and then.

langsoul-com 3y ago

What's the comparison of this approach VS directus, which auto creates an api layer on top of dB

pm90 3y ago

It seems like sqlite is now acting as the API gateway. I think its an interesting idea, especially if you want to share relational data out to consumers, since they now have all the flexibility of sql. The issue though is updating the queries when schema changes are made.

Zvez 3y ago

If you give access to your DB directly, your API effectively becomes your API with all the contract obligations of the API. Suddenly you don't completely control your schema: you can't freely change it, you need to add things there for your clients only. I've seen it done multiple times and it always end up poorly. You save some time now by removing the need to build API, but later you end up spending much more time trying to decouple your internal representation from schema you made public.

MoSattler 3y ago
I think this point is addressed in the article.
- herodoturtle 3y ago
  Came here to say this too. From the article: > A less obvious downside is that the contract for a database can be less strict than an API. One benefit to an API layer is that you can change the underlying database structure but still massage data to look the same to clients. When you’re shipping the raw database, that becomes more difficult. Fortunately, many database changes, such as adding columns to a table, are backwards compatible so clients don’t need to change their code. Database views are also a great way to reshape data so it stays consistent—even when the underlying tables change. Neither solution is perfect (raw read replica vs API). Pros and Cons to both. Knowing when to use which comes down to one's needs.
oreilles 3y ago
Versioned views, materialized views or procedures are the solution to this. It is frequent that even internally, companies don't give access to their raw data but rather to a restricted schema containing a formated subset of it.
- vmfunction 3y ago
  In addition if you are using postgres, then there is postgresRest to make api really quick and nice.

giovanni_or2 3y ago

BS. API layers exist for a reason. Maybe they will realize that reason if they try to expose the DB...

mkl95 3y ago

In the beginning of my career, I did an internship at a very small company that had been selling a desktop app for 20+ years, among other stuff. Each customer got their own database. The software was pretty stable and the team were part time consultants for that app, which mostly involved helping users perform complicated queries and creating stored procedures (they loved those). Subscription was allegedly quite expensive and had minimal churn over the years. For that kind of hyper stable software where each customer get their own database, I can see it working. In your typical 2023 SaaS where you are serving 1000s of users through a complex API and a database, I'm not sure a db can give you the level of flexibility and quality of life an API gives you.

Takennickname 3y ago

I asked chatgpt for a solution looking for a problem and it sent me here

solatic 3y ago

Sounds like the next step is some way to define a multi-table view over a more traditional RDBMS (e.g. Postgres), serving as a source of truth, that can be continually exported as a read-only SQLite, so that the SQLite file can be pushed to the edge and read from the edge? I'm not sold on the idea of per-customer databases; businesses still need analytics, so if you're not running your analytics on the centralized DB itself, your app needs to write/push the data somewhere else, which is an unnecessary complication early in the product lifecycle at best and a recipe for frustration at worst.

vincnetas 3y ago
remember the times when software companies shipped software to users, and users used the software offline. No one from business even dreamed of knowing what each user was doing with the software at any moment in real time. What data users stored etc. Today it looks like this is MUST have for any software and no bussines is possible without this.
- solatic 3y ago
  > No one from business even dreamed of knowing what each user was doing with the software at any moment in real time Surely you jest. Not knowing what your users are doing with the software was the number one problem with product development in the 90's. The only solution was user interviews and real-time in-person observation, which was highly expensive, thus making the cost of software much more expensive. > Today it looks like this is MUST have for any software and no bussines is possible without this Because more and more software is delivered over the Internet through low-touch sales methods. More often than not, software producers don't even know who their users are - how do you conduct user interviews, expensive as they may be, if you don't even know who your users are? Trying to run a modern business without a modern analytics stack is functionally equivalent to driving with a blindfold on.
  - anymouse123456 3y ago
    Just want to temper this a little bit. Yes, information can be better than no information, but user analytics is not the holy grail it pretends to be and can be worse than no information at all. I led a team a BigCo for some years that had access to mountains of highly detailed user data that enabled product, eng and UX folks to ask very specific questions about how successful various changes were, in terms of our business and product goals. An outrageous amount of time was invested in this process. Designing, describing and negotiating experiments, collecting data over some days or weeks and in almost every case, the data was basically inconclusive and every question would spawn more questions. Enormous time was spent digging and sifting through this data, while even people with the best of intentions would almost always disagree about what the data was telling us. Eventually, some quant showed up and explained how exactly zero experiments we ever did were statistically significant, and that if we wanted to achieve significance, we would need to run a 1% experiment on our many millions of users for more than 30 days. The organization collectively shrugged at this information and went right back to the time worn rituals of success theater. What I learned in that experience was that developing great products requires experience, skill, intuition, grit and visibility, but that visibility might be the least critical of those requirements, not the most. Put another way, there is almost always a simpler, clearer measure of success that doesn't require spying on everyone all the time and then sifting through what is actually worse than useless information.

k_vi 3y ago

I've tried doing this read & updates using Postgrest and row-level-security using Supabase. When it works its an amazing experience but even for semi complex stuff you would still need to use Postgres RPC, which is still another "API" layer. I find writing API using SQL a nigtmare. Simple queries like this don't work on Postgrest: `update likes set likes = likes + 1;`

Hakkin 3y ago

Even if you "skip the API" you still have to think about access patterns for the data, otherwise client-side query performance is going to be abysmal. You would basically have to define your "API" via what indexes you create on the data.

abdullin 3y ago
With a local SQLite database you don’t need to pay cost of network transfers. A dozen of local SQL calls could be way faster than a single API call within a region.
- Hakkin 3y ago
  That's true, but low query latency doesn't mean much if you have to do a full table scan for your query. Some thought still needs to be put into how the clients will query the data, so it's really not as simple as just "ship your entire database to the client and let them figure it out".

RamblingCTO 3y ago

> Moving compute to the client I thought we were slowly learning that this was a stupid idea in the first place and moving back to the old ways (tm)?

3cats-in-a-coat 3y ago

The lifecycle of a startup full of young arrogant naïve inexperienced souls: 1. Why is everyone doing all these complicated things?! We stopped doing them! We're much smarter than everyone! 2. Few years later. 3. At great financial, technical and business cost we redesigned our system to do all those complicated things, because our initial version was an unmaintainable spaghetti monster that wrapped up everything its tentacles touched and hugged it until it died. There are situations when exposing SQL is a right call. I'd say those are pretty narrow, say when you have colleagues in a partner company doing integration. SQL is not a way to avoid having an API. SQL is an API. The surface of this API is gigantic, and it also binds your customers to a particular implementation of your database (the SQL vendor you happen to use). If those customers are few partners, this is easier to change. When it's the public at large... you're effed. This is all pretty basic systems analysis, but kids these days don't know the first thing about it. But here's a basic rule of thumb: exposing SQL as your API may be the right call if you've spoken in person with every customer who will be using that API, and especially if this in-person contact is regular, say at least once a month. If you haven't, then SQL is not a good API for that customer.

thinkingemote 3y ago
This is 100% the pattern. However most startups (and most software projects if we think about it) fail. They don't make it to year 2. This in built failure rate shouldn't be a reason against doing things right the first time but it might explain how many don't get to learn the lessons from year 2. Most of the time we will never get to redesign and realise the mistakes we made.

lambda_garden 3y ago

This is not equivalent to (say) a single Postgres database where we get shared state with transactions between all users - or have I missed something?

shimst3r 3y ago

As someone who has worked as a data engineer on an organically grown monolith, the last thing you want to do is ceding control over your schema due to the lack of (sensible) abstraction. Everyone will change it, nobody cares about standardization. It’s utter chaos and a data engineering nightmare. This is the same, plus the lack of proper authN/ authZ. Good luck passing your next audit.

ricardobayes 3y ago

I've seen teams take this ad literam and actually shipped an Elastic instance in an iFrame as a "product". Needless to say users abandoned it in no time.

greo 3y ago

I used to be a big fan of fly.io until I faced a lot of deployment issue. Often times the server will go down for few minutes and it was not even updated on the status page. Is this still the case or has things gone better?

EspressoGPT 3y ago

This must be from a person who never had to deal with when the database actually was the API.

snowstormsun 3y ago

While you're at it, skip the internet and burn the database on DVDs, then distribute them!

rzarate 3y ago

Kudos to the author(s) for building LiteFS, it really does seems like an interesting project. However, I can't see this being a popular pattern moving forward (or at least I hope that's not the case). Replacing APIs with DBs in real production workloads? Really? I don't have much else to say. Also, the value proposition is not quite clear to me. Using replication to have local, read-only replicas of an SQLite db might admittedly be beneficial for high read-throughput workloads but I'd imagine that the DB might have serious size/read-throughput constraints due disk space and network overhead from replication. My takeaways are: a) This doesn't sound like a reliabile pattern to build distributed services. b) It's bad PR for fly to publicly push for the adoption of this pattern. c) It's even worse PR that fly is using this internally.

jampekka 3y ago
Replacing APIs with DBs is a good idea. I don't have much else to say.
- TjZkxkxeky 3y ago
  It's certainly not a new idea, however. Every product that allows backdoor access into their database directly has effectively done this. Hope you have your schema correct when your customers build a mountain of cards on top of your database schema.
  - jampekka 3y ago
    How does this differ from them building a mountain of cards on top of your API? The schema used by the public API doesn't have to match the database layout itself. E.g. views can be used for this. A lot of APIs are quite straightforward mappings typically from REST/GraphQL to a database, often via some sort of ORM or other abstaction. These extra layers mostly amount to needless complexity, more surface of bugs, lots of busywork and worse API usability.
    - TjZkxkxeky 3y ago
      It differs dramatically. Your API is likely much much smaller than the inherent API of SQL tables. It's a difference in degree that is a difference in kind. Anyways, every old idea must be retried.

remon 3y ago

Exposing your schema directly is an anti-pattern for good reasons. Don't do this. You want to be able to change a contract/interface/API independently of your persistence strategy.

robertlagrant 3y ago
You might want to. You don't have to. It's a tradeoff, not a law.

tamrix 3y ago

Skip the database. Pass a gaint json document back and forth.

dangoodmanUT 3y ago

Isn't this effectively just letting all services access a central database? E.g. letting them all run SQL queries against Postgres? Seems like overengineering to replicate the DB to each machine when we have Postgres with read replicas or dbs like cockroachdb. Like why replicate your 10GB DB to all 30 of your machines when they can access a 3x3 DB cluster instead? Seems like it's asking for issues, as now your machines are no longer stateless and have to play catch up if they go down before they can serve queries.

jacobgorm 3y ago

I wonder why they chose to implement the LiteFS as Fuse module, instead of simply hacking SQlite to intercept the IO operations before they go through to the OS. Yes it would require a patch and a recompile, but performance would be superior by not having to go through the kernel and Fuse, and there would be fewer moving parts.

tptacek 3y ago
LiteFS works this way so that its users can use the unmodified SQLite libraries they'd normally use.
simonw 3y ago
That's what the in-active-development LiteVFS does, except using a SQLite extension: https://github.com/superfly/litevfs https://github.com/superfly/litevfs

kat_rebelo 3y ago

Even for more some more advanced use cases such as OLAP or ML related pipelines, in my experience, it takes a single senior developer a couple of days to design and implement a REST API, not weeks. That claim is overblown FUD.

consoomer 3y ago

I like the idea of exposing the database and pushing more business into the database.. but it's terrifying. Row level security and exposing postgres to the world sounds cool, but I can't see myself doing it. It's like... encryption is awesome in theory and protects your data, but would you push your password-store to a public github repo? In theory you could... edit: A quick Google-fu and it looks like some people do push their password-store to Github... balls of steel. https://github.com/jysperm/passwords https://github.com/jysperm/passwords

Rudism 3y ago

From the perspective of someone who's been neck-deep in a highly complex PostGraphile project for several years now, I wholeheartedly and strongly recommend that you SHOULD NOT expose your database or autogenerate an API from your database. Maybe it's fine if you have a really basic schema and the API requirements are just a few CRUD endpoints with no business logic, but as soon as you go past that you'll find yourself in a world of pain. One upsetting side effect of giving API consumers the ability to query your data arbitrarily, is they will absolutely find the most inefficient and resource-intensive ways to do it. And the blame when the "API" is taking several minutes to come back with data will be laid squarely at your feet even though the cause of the problem may not be under your control at all.

k__ 3y ago

I think, I found a good use case for this. I'm running an gateway that uses a SQLite database to index transaction data. One instance loads all TXs and indexes them to SQLite and LiteFS replicates the database to other instances. Down-side: there are copies of the database on every machine. Up-side: Saves work, since only one machine needs to index and reduces latency for reads, as every machine reads from a local SQLite replica. Bonus: LiteFS Cloud keeps backups of the database.