Been feeling a little miffed about this recently. Litestream is excellent but if you have multiple writers your db gets corrupted. Quite easy to do with rolling deploys.
LifeFS was announced and is intended to help this. Now seems like (https://fly.io/docs/litefs/getting-started-fly/) it requires an HTTP proxy so that the application can guess about sqlite write/read usage by reading the HTTP request method. This seems... to introduce a different (maybe better?) set of gotchas to navigate.
There are now SQLite cloud offerings but you pay the network overhead and avoiding that was so much of the appeal of using SQLite.
Are people successfully using SQLite in a work or production setting with a replication and consistency strategy that they like? I've had trouble getting a setup to the point where I can recommend it for use at my jarb.
I've had success in a production capacity with using rqlite before. There are also a bunch of other alternatives that still seem to be actively maintained, although I've only used rqlite myself before:
> Litestream is excellent but if you have multiple writers your db gets corrupted.
Isn't this not only well-documented, but (restricting to a single writer to avoid distributed systems issues while still making it easy to move that single writer around) sort of the whole point?
Our strategy is to not attempt replication at the level of SQLite. We use a single binary for our SaaS product which shares 1 SQLiteConnection instance for the lifetime of the whole ordeal. Remember - every single SQLite connection instance is a file system abstraction, not some in-memory/networking clever optimized thing that Postgres or SQL Server is managing on your behalf. Every time you open a new connection to SQLite you are doing some pretty heavy-duty OS calls, relative to just reusing a prior connection. SQLite itself is typically built with serialization on by default, which deals with multiple threads on one connection. In my experience, this is the most stable & performant arrangement (with WAL, et. al. also enabled).
Our backup solution is to snapshot the entire VM (or block storage device) that SQLite is running on. Replication is not a concern because our restore strategy is to just bring back a snapshot if required. Our customers are ultimately responsible for this and typically handle it with a few clicks through AWS, Azure or a quick email to their private cloud provider. RPO and RTO is entirely in their court and all parties prefer it this way - them being highly-regulated banks and us being a small startup operating at the edge of the abyss.
To this day, we have not once had to support recovery of a SQLite database from snapshot due to corruption or other weirdness. We've been at it for half a decade now.
Author here. The single-node restriction for Litestream was one of the main reasons we started LiteFS. There isn't a way to handle streaming backup from multiple nodes with Litestream & S3 as SQLite is a single-writer system and there aren't any coordination primitives available with S3.
I agree that many of the SQLite cloud offerings introduce the same network overhead. With LiteFS, the goal is to have the data on the application node so you can avoid the network latency for most requests. Writes still need to go to the primary so that's unavoidable but read requests can be served directly from the replica. The LiteFS HTTP proxy was introduced as an easy way to have LiteFS manage consistency transparently so you can get read-your-writes consistency on replicas and strict serializability on the primary. That level of consistency works for a lot of applications but if you need stronger guarantees then there's usually trade-offs to be made.
If you need multiple writers and can handle eventual correctness, you should really be using cr-sqlite[1]. It'll allow you to have any number of workers/clients that can write locally within the same process (so no network overhead) but still guarantee converge to the same state.
Oh nice. Looks like on closer inspection they're using Lamport Clocks, which track causation, but if ignore time, although time is mentioned somewhere as a possibility in hybrid systems someday, if I'm understanding it?
Looks like only a 2MB binary for the extension, so you could in theory just pack it with your app too.
I'm particularly interested because it seems like(For very small databases) you could use SyncThing as the sync backend by just periodically dumping your data to files(And making a new one once the file got too big).
I don't know how you could ever garbage collect the old files aside from some kind of manual "Delete everyone else's stuff and output your own big merged log" command, but it would be really cool to be able to make apps with P2P sync.
It also seems like you could put them in an http server and use it like an RSS feed. Or even serve them via torrents.
It would be possible to distribute cr-sqlite changes in many different ways (like you said, http or torrents, etc.) since any change can be applied out of order.
I don't need to be sold on the virtues of applications running on systems like SQLite. The nineties had a lot of servers which were very simple (and performant) compared to LAMP, and I like systems like that.
What I would like is a good primer about the layers on top of SQLite. What does Litestream do for me? How does it compare to competitors? Why not just use SQLite directly? A more in-depth technical discussion would be nice. I'd also like to understand wrappers and ORMs for migration to other systems, should SQLite stop scaling.
SQLite does not provide replication, so there is no way to use it directly (other than copy whole file). If you mean it as "Why not use it as a database" than sure, you can use it directly, though the article states reasons for not doing so (resiliency and concurrency). Postgres is a lot better in those areas, and so is the tooling.
>I'd also like to understand wrappers and ORMs for migration to other systems, should SQLite stop scaling
1. It heavily depends on the orms. For example Django provides good abstraction layer and many things works with any database with no change needed, but many other don't bother about that. However just because a query runs, doesn't mean it will return the same results. Any non-trivial app will rely on numerous accidental details and you can't switch db and expect everything will be fine. SQL is not really portable even in the parts it does cover, and there are many it doesn't.
I've definitely build portable systems in Django, back in the day. The trick was to have decent test coverage and run over both (at the time) MySQL and SQLite.
(And yes, I should have used postgres).
I'll mention: The internet, in the nineties, was powered by 486-grade computers, and things were perfectly performant to a pretty decent scale. If you can get rid of issues like network latency (from e.g. a database on a different machine than your main computer), and similar 2020-era bottlenecks, a lot of web apps can serve millions of users from a single machine. That's doubly true with gigabytes of RAM and modern SSDs.
With RAID and regular backups, it can even be pretty robust.
It's even easier to do now that you can write static client apps that just need to push and pull little bits of data to and from the server.
That's not an architecture that's used often, but it keeps things very simple and can work quite well.
Author here. Cool to see the post make it up on HN again. I'm still as excited as ever about the SQLite space. So much great work going on from rqlite, cr-sqlite, & Turso, and we're still plugging away on LiteFS. I'm happy to answer any questions about the post.
SQLite has very little per-query overhead (as opposed to a database connection over a network) so I would think you could traverse a graph using multiple small queries rather than using a graph query language.
Litestream definitely has a future. Our goal is to keep it as a simple single-node disaster recovery tool though so it won't see as much feature development as something like LiteFS. We've been focused a lot on LiteFS & LiteFS Cloud to get them in a good place but I'm looking forward to going back and updating Litestream more regularly.
Not much feature development is perfectly fine if it works! Things don't have to evolve.
Planning to use litestream as a library to dynamically swap in/out dozens of databases in a process. Looking at the code it'll easily allow that (super clean, kudos!).
So many thanks, it's going to enable a lot of new things!
I recently saw the launch post of Electric SQL which syncs to SQlite, I like the pattern on how keeping the data close to the frontend can solve many problems, if synced with the main DB. I hate to run another docker or manage service to manage this layer but if somehow a part of data from the database like Postgres can be synced using something simple like litestream and can be placed either on edge or client can be a solution to many of the problems.
> When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds. That’s micro, with a μ. A 50-100x improvement over an intra-region Postgres query.
Why compare the latency of a remote Postgres database with a local SQLite database? If your app is so simple and self-contained that it runs on a single EC2 instance using local files, nothing prevents you from installing Postgres on the same machine, whether inside a container or not.
I have some simple apps on EC2 with MariaDB on localhost, and well-tuned queries rarely take more than 100-200 microseconds. That's total query execution time, not just communication latency. RDS just sucks for this kind of use case. It's not a useful comparison.
> As much as I love tuning SQL queries, it’s becoming a dying art for most application developers. Even poorly tuned queries can execute in under a second for ordinary databases.
But why take on the operational overhead of a separate DB server (not to mention 200 more microseconds), plus the EC2 surcharge? I would rather run app+SQLite + dumb object storage, than app + MySQL + MySQL incremental backup and restore.
What separate DB server? I was talking about installing the RDBMS on localhost, right inside the server where your application runs. No other EC2 instance, no extra charges. Preferably connect to it over a Unix domain socket instead of TCP. That's the only way to compare SQLite performance with an RDBMS in an apples-to-apples way.
The point about operational overhead makes sense, though, and IMO it's the only point in this unnecessarily long article that's actually worth considering. I do have a couple of other apps running on SQLite, so I appreciate the simplicity.
It's still normal to refer to e.g. "database server", "application server", etc. processes even when running on a single machine. Re. EC2, I'm referring to the surcharge of having a dedicated instance at all, vs. working at the container / object storage level of abstraction.
Sure, you can download an SQLite database from S3 and use it in your Lambda if your dataset is small enough and, more importantly, you either don't do any writes or have access to a highly reliable solution that synchronizes writes across copies and persists them back to S3.
Unfortunately, Litestream (which OP is promoting in TFA) needs to run as an independent process inside a VM and/or container in order to sync your writes. It's not very different from a traditional RDBMS in that regard. Even the title says "server-side" SQLite, not "server-less" SQLite.
> Why compare the latency of a remote Postgres database with a local SQLite database?
The SQLite DB is just a flat file that can be packaged in a lambda or whatever cloud's object store. It makes sense for fast access to read-heavy or read-only data. Even a shitty query can return rows in less than a millisecond where a remote DB that same amount of data is tens of milliseconds away.
Author here. The comparison was meant to be about how Postgres (or any client/server RDBMS) is typically deployed. Yes, you can deploy Postgres on the same machine but I wouldn't say it's common. Maybe I could have expanded more on that point or simply referenced client/server architecture rather than Postgres so it didn't seem like a straw man argument.
Use Postgres. Or if you insist on this type of architecture use CouchDB. I shudder thinking about a SQLite schema migration across clients with potentially unknown versions.
Seems like a disaster waiting to happen unless you have a bunch of logic centralized somewhere to keep track of last know schemas per user client database. And if you’re going to do all that, unless you desperately need low latency (in which case you could use a multi region database like cockroach), why not just centralize?
I hope fly is able to make it. I’m rooting for them - however - I’m starting to wonder if the SQLite push isn’t more “this is fun and interesting to build” and less “customers want this”.
Don’t get me wrong - this is neat - but I’d never suggest anyone to actually use this outside of a fun experiment. The problem with existing SQL dbs isn’t really the architecture - its the awful queries that do in memory sorting or make temporary tables for no reason or read-after-write, etc, not network latency. SQLite won’t fix your current production problems.
If it turns out they’re building this for customers throwing cash at them, awesome. I just somehow doubt it. I think Planetscale has the better approach: a drop in replacement for MySQL/RDS with a smarter query planner. As a production engineer that’s what I want to pay for!
I think you're skipping the replicated read only use case, which is our use case, and it's super handy there. but i understand this is a restricted scenario where little could really go wrong, and it could be done other ways.
Any self respecting e-commerce site would want fault tolerance and strong consistency even with potential network partitions, so definitely not SQLite as described in article
They aren't necessarily going to have all this. Lots of smaller ecom shops can run on a single server per region. If you're a North American company selling a few hundred t-shirts per day in NA and EU, it could probably be fine, no? I'll admit I'm not speaking from experience. Rather, I have experience in everything I just said only I was using pg, not sqlite. But I've been very interested in sqlite recently.
As with anything, the typical business can use something off-the-shelf, but a certain percentage are doing things differently enough that custom development becomes practical. I had to custom build the billing system for Beaker Studio to properly meter customers. Even Stripe's metering API wasn't flexible enough to handle per-hour metering.
I find it hard to imagine a reason a shop selling 100 tee shirts in a day would need any custom functionality since this is basically the exact use case all these OOB e-commerce tools are built for.
I've worked for such a place. Off-the-shelf solutions are trying to be everything to everyone and you can end up customizing the crap out of them to the point where it can become more onerous than just building your own. The place I worked was also print-on-demand service and had hundreds of thousands of SKUs as well as allowed customers to make custom products and we also hosted some peoples' shops. Shoehorning that into a custom solution was painful.
I work at a very similar place now that uses Shopify. Managing that many SKUs on Shopify is crazy painful.
The thing is is that custom ecom solutions really aren't that hard. The off-the-shelf ones are complex because, as stated above, they are trying to be everything to everybody.
One of the first commercial projects I worked on nearly 20 years ago was a tshirt shop. And the precious company I worked for was a comparatively huge logistics startup.
I’m fairly confident I could spend a month or so writing a custom solution for a shop selling and shipping a few hundred tshirts a day that would save them enough money to break even on the software in a few years (compared to off the shelf solutions).
If I was starting my own tshirt company, I’d definitely do it.
In our B2B space there are tons of custom business rules. For example, you place orders per manufacturer and each manufacturer has its own minimum and reorder amounts and reqs on an order, promotions, business rules, etc. This is for business that have been around 25-30 years.
We did an evaluation on several out of the box ecommerce solutions and none of them were able to meet the requirements that absolutely had to be there. Shopify flat out said no, they can't help us, etc.
> the typical business can use something off-the-shelf
I think we generally have this problem. It's not the typical business that can use that stuff, it's the average business. Unfortunately no business is the average business.
8 years ago we built our own ecommerce site + warehouse app (inventory tracking, fulfillment, receiving) for a few hundred orders per day. The goal was to be able to better see profit margins by product, track where the money was going/coming from in detail, along with cleaning up the inventory management part of the operation.
The warehouse people loved the change because it really streamlined the fulfillment process and it basically reduced their errors to zero. Owners loved it because of the all the cost/price tracking. It took about 6 months of work by 2 devs.
Before this they were on woocommerce. It was slow as shit. We looked at shopify but the integrations with 3rd party warehouse stuff was bad. The other solutions we looked at were overly complicated swiss army knives.
This is very close to my experience as well. Unfortunately, it was in reverse for me :( We had a custom solution and people were happy. A new tech lead came in and didn't like that the custom solution was PHP that still had some legacy spaghetti in it, so we switched to an off the shelf solution. It was very painful. People were unhappy.
Your wild guess is flat out wrong. When the previous tech lead came in the entire thing was spaghetti. His strategy was to slowly start bringing in some order to the chaos which we did, diligently, over two years making tons of progress. It was a homebrewed framework but it was really nice (he'd had years of experience contributing to Drupal). All the business critical functionality had been converted to this. The spaghetti parts were features that were essentially "complete". Since they never needed updating, they worked fine as they were.
There is a cost to fault tolerance as well, if you lose $100 per month due to fault tolerance thats worth about 30 developer minutes. Do you really get good fault tolerance for 30m of monthly work?
That's something for me to look into. Like what would it would look like in each? I've never been the dba-type in past jobs. I'm relatively well-versed in SQL but not administration.
I have this same discussion at work frequently. Everything thinks they need instantly up to date, yet our customers work for 10 minutes on a stale document and no one cares.
The alternative is to write good queries! Alternatively, a better query planner that has a more liberal approach to "I know what you want, not what you're asking for". A great article on this re: Planetscale's approach is at https://vitess.io/blog/2021-11-02-why-write-new-planner/
I’m excited about SQLite for web apps if only because it is one less moving piece in my stack, which focuses on prototyping and finding product market fit.
If I start hitting hundreds of writes per sec, then, thats either awesome or I wrote some horrible code.
> The problem with existing SQL dbs isn’t really the architecture - its the awful queries that do in memory sorting or make temporary tables for no reason or read-after-write, etc, not network latency. SQLite won’t fix your current production problems.
In my experience SQL databases are pretty poor at executing the kinds of deeply nested joins needed to return all of the data needed to render more complex UIs. It almost always ends up being faster to simply make nested selects in batches. So latency ends up mattering in these cases.
You can work around this by denormalizing the data, but this often explodes your data size.
I can't imagine many suitable production use cases too. Alternatives like Planetscale, Supabase or Neon are even easier to use and at the same time much more powerful. If latencies around 50 ms are too much for the specific use case, SQLite with Litestream could be a great solution. Otherwise I'd go with managed Postgres/MySQL solutions.
I recently implemented SQLite for a metadata application at work. My constraints in design were pretty concise; the application will only ever need to scale vertically, my data persistence story is a matter of speed and accessibility over longevity, I have plenty of options for scaling disk IO, and my read performance is much more paramount than write performance.
The outcome is that my persistence later is not adding $200/m immediate service overhead cost and my deployment was easy to manage, which is a strong promise made to the rest of the team. I think there's a place for SQLite, but just like any tool you need to know that your design constraints match the constraints of the tool.
While different than the approach offered by Litestream, I am fairly excited by the direction of Cloudflare D1, making SQLite available at the edge without having to manage anything. Still in alpha but worth looking at if you're looking for cheap cloud option.
> We’re beginning to hit theoretical limits. In a vacuum, light travels about 186 miles in 1 millisecond. That’s the distance from Philadelphia to New York City and back. Add in layers of network switches, firewalls, and application protocols and the latency increases further.
> The per-query latency overhead for a Postgres query within a single AWS region can be up to a millisecond. That’s not Postgres being slow—it’s you hitting the limits of how fast data can travel.
No. An AWS region has a radius of less than a few dozen kilometers, more likely around 5km. Lightspeed doesn't factor into it at those small distances. That millisecond is indeed Postgres being "slow" in these terms. (Most of it is the networking stack, as noted.)
This basic error makes me question the validity of the document. I stopped reading here.
I agree that "networks are slow" but this sort of false justification is not the way to sell it. Is this an attempt to make the author seem like he knows what he is doing because he knows the speed of light?
If he really understood what he's talking about, he would at least say "half the speed of lights" because that's the max speed a fiber cable will ever go.
I can’t see any valid reason not to use Postgres at the back end, unless you are in some sort of environment such as embedded or cloudflare workers that requires it. Or if you need a graph database there are better choices than Postgres.
Postgres is good on multi core, incredibly feature rich, multi user, supported by everything, lightweight and has all the tools for production workload and management. All stuff that is important.
Most important difference to me being SQLite I understand lacks flexibility in modifying table structures.
Well, cost, right? Cost is a reason why someone may not want to use a traditional RDBMS. AWS RDS and GCP Cloud SQL aren't exactly the cheapest solutions out there.
Postgres is great at what it does, but it is extremely inefficient for storing a small amount of data, e.g. kilobytes or a few megabytes. sqlite, on the other hand, scales nicely all the way down to a few kb.
This matters for cloud in that it means with Postgres you cannot take a "lots of small databases" strategy, e.g. database per user or database per document. You pretty much have to group a lot of data into one big database.
Many apps want to do that anyway! For them, Postgres makes sense. But in the growing world of global deployments and edge compute, the lots-of-small-databases approach is getting popular because it means you can store than data out on hundreds or thousands of edge locations, rather than a single central location. And many (not all) applications actually fit pretty well into a database-per-user or database-per-document model. Centralizing their storage only hurts performance for no benefit.
As a bonus, if you are able to run sqlite compiled directly into your app, not making any kind of network connection, it can be much faster than Postgres, especially in "N+1 select" situations (which are well-known to be a problem with most SQL databases, but are not a problem when using local sqlite). Postgres does not support running as a library like this.
>This matters for cloud in that it means with Postgres you cannot take a "lots of small databases" strategy, e.g. database per user or database per document. You pretty much have to group a lot of data into one big database.
You're right, it's a very interesting strategy. Unfortunately, last time I looked Cloudflare's D1 didn't support this approach as there was no API to create a database at runtime from Worker code. Has that changed?
Now your app is stateful, you need storage for it, a backup strategy, a free space monitoring strategy, a way to have the storage follow the app, etc. Depending on your situation, that could be harder than just getting a Postgres database.
I used to be in the stateless camp. But I think we pushed that argument too far. Stateless apps are not very useful. Most useful apps are stateful. What we are doing with promoting stateless services is just delegating the necessary stateful complexity to someone else, sweeping it under the carpet. By doing that, we are losing so many opportunities to do smarter and better things where the complexity really lies, which is where the state is. That’s why I really appreciate SQLite and other approaches like using KV embedded databases like RocksDB making a come back. That’s the job of the infrastructure providers, AWS, GCP, DO, and others, to provide the tools solving the problems you mentioned: block storage that is synchronously replicated across data centers (GCP does this), snapshots and backups on block storage, possibility to quickly reattach the block storage to a new computing node if the previous one died, etc.
> I can’t see any valid reason not to use Postgres at the back end
From TFA:
> if you don’t need the Postgres features, they’re a liability. For example, even if you don’t use multiple user accounts, you’ll still need to configure and debug host-based authentication. You have to firewall off your Postgres server. And more features mean more documentation, which makes it difficult to understand the software you’re running. The documentation for Postgres 14 is nearly 3,000 pages.
them citing the (excellent) Postgres documentation page count is hilarious. There is a really useful comparison to be made re: features / scope -- but approximating that and punishing a project for how comprehensive their docs are...feels like lines of code as a productivity measure but like 10x less accurate or useful lol
Author here. My goal in the comparison was only in terms of scope, not that Postgres folks should be penalized for having good documentation. I think Postgres is great and it makes sense to use it when it's called for. But I think it can be overkill for many projects.
Estimating the complexity of using a project can be really...complex. I think about systems I have used which make it easy to use a minimal set of features and where I don't have to reason about or be negatively impacted by aspects I do not benefit from, and other systems where things are less easily isolated and more challenging to reason about.
I do think the Postgres docs in particular seek to be a reference in addition to an operating manual and I for one really enjoy them. I think the point is well made that Postgres can be too much (or too much right now) for many projects.
SQLite fills a need in the market that postgres does not. Local single process data storage and retrieval from that with some structure. Postgres and all of the DB's like it kick in when you want more than one process involved. MS used to have Access that filled this need very nicely. Once you go multi user/process you probably do not want sqlite you will want something that can do ACID on a multi user level. Once you go more than one process to store data you can make your installation a larger burden than you really need to deal with. On the other hand I can think of maybe one or two projects where sqllite fit very nicely for a data store. Most of the other cases I had involved a 'real' db.
For this case I guess they could try to bend sqlite to do this but the pain in doing so will probably not be worth the long term trouble than just using mysql or postgres or something like those.
Postgres doesn’t support b-trees for primary storage, so data can’t be automatically clustered, which reduces efficiency for joins by 50x. MySQL and SQLite don’t have this issue.
I recently wrote a production system that uses SQLite as the main backend. SQLite is in memory in this case and its entire state gets rebuilt from Kafka on start. The DB receives about 2 updates a second, wrapped with rest api aiohttp and odata filters. It has been able to handle close to 9k requests/second ands it’s a primary system in a financial institution. So yes SQLite is fully capable prod db.
In large organisations you often have no choice of the type of queue between your team and other teams. That being said there’s nothing wrong with Kafka and the ability to seek back to the earliest timestamp since midnight and being able to rebuild our state from that is a godsend feature, in comparison to other queues. This means we can make our application stateless or at least afford to lose the state and be able to build it quickly from Kafka.
Kafka for consistency/durability and local storage for speed/structure is a common architecture, and a really good one any time you can tolerate async writes.
If you don't need SQL (relational data), but maybe have a schema per topic, I've used rocksdb as a cache for latest in tombstones topic. It has high write throughput for rebuilding state when playing forward a stream
I’m bullish on SQLite, and this is mostly a great article, but this kind of stuff is flat-out misleading:
> When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds.
As if postgres and others don’t have a way to run application logic at the database. I like the SQLite way of doing it — you pretty much freely choose your own host language — anything with a decent SQLite client will work. While in postgres, for example, you’ll probably end up with pgplsql (there are others, but there are constraints). So this isn’t about latency, as the whole section of the article suggests.
There’s actually a relative weakness in SQLite here, since it doesn’t include a built-in protocol to support running application logic separate from the database. That’s also architecturally useful, and so you may have to find/build a solution for this.
Just adding replicas isn’t a general solution either, because each replica has an inherent cost: changes have to somehow get to every replica.
E.g., systems can grow to have a lot of database clients. In traditional setups you begin to struggle with the number of connections. You might think with SQLite, “hey, no connections, to problems!” but now, instead of 1000 connections you’ve got 1000 replicas. That’s something you’re going to have to deal with… that’s 1000x write load, 1000x write bandwidth.
Perhaps fly.io has a solution for this, but I suspect it’s going to cost you.
> As if postgres and others don’t have a way to run application logic at the database.
I think it's reasonably fair of them not to specify this. The target audience of this article is people who are writing their applications in languages like Elixir, JS, Ruby, Python, and are not going to be interested in pushing all of their business logic to the db.
I think the main thing is that most people still aren't working with a good migrations system to manage changes to their schema... which means logic held in database triggers and stored procedures quickly becomes a non-version-controlled not-properly-tested mess.
Good migration systems exist, and people should use them!
I held off on doing interesting things with triggers for more than a decade. In the past year I've started leaning into them much more heavily (actually using them in SQLite) because I have confidence that I can both write good tests for them and have good migrations automation in place for version-controlling my schema.
Postgres has plugins for running the entire backend.
People don't like it for a lot of reasons. Making privilege escalation harder is a big one, but also, all the CPU (and memory) load on serializing that data is CPU that could be used managing the distributed processes problems that only the DBMS can solve.
Personally, I think access management on those tools needs to improve a lot before they get usable. But also, the data-oriented languages have some issues, and the non data-oriented ones don't gain much by running inside the database.
IMO, we are missing a really good data-oriented language. But I don't see any gain from running it inside the database.
After several decades in the business, I have learned not to underestimate the massive power of trendiness.
A lot of the argument against it is: SQL is stodgy and uncool, and I want to use Cool Language XYZ. And honestly, I don't entirely blame people for that. If you want to work in this industry you need to do things the "cool" way or you'll never even be considered for roles. Gotta keep that resume looking good.
People also don't appreciate how performant it can be. Depending on what you're doing it can be orders of magnitude more performant to do things in-database versus shuffling things back and forth. (It can also be less performant...)
There are definitely cons to that approach.
One is scalability. It's easier to scale your app layer horizontally than it is to scale your database server vertically. This isn't necessarily an issue: a modern beefy server CPU with 64-128 cores and a TB or two of RAM is more than 99.9% of companies need, is really not that expensive, and is probably a lot cheaper than more complicated setups and extra devops headcount. But that's not cool either.
Two is the language/skills mismatch. You've got an app layer in one language, a frontend in another, and now potentially a data storage layer written in a third. This is a valid concern, but also nobody seems to use it as an argument against Javascript frontends, so apparently sometimes it's cool and sometimes it isn't.
Debugging stored procedures sort of sucks. That's fair. (But also, nobody is saying to rewrite your entire app, or even most of it, in the DB layer)
Common migration tools often don't really have explicit support for stored procs and things like that, but AFAIK they do let you run arbitrary SQL DDL stuff, so I don't think this is a hard barrier.
>Me? I actually think pushing app logic to the DB is a solid, underrated, and possibly even optimal solution for a lot of scenarios.
The languages for writing it are not as comfy as traditional programming languages, which affects how expressive and maintainable your code will be. The tools for debugging a regular language might also be better than debugging application logic in SQL. Having done some of this in T-SQL, it's very tedious day-to-day compared to writing C#.
Genuinely curious what your experiences of postgres programming you are fond of. Are you talking about functions & procedures in pgsql or are you using an extension to enable a different language?
Yes, I meant that plpgsql is on a complete different level from t-sql (and t-sql is already on a different level from pl/sql).
But also, you don't need extensions to enable other languages, postgres allows those out of the box (through the C-ABI). Anyway, that is not very compelling, because plpgsql is quite good already.
First, to be clear - I'm not talking about moving the whole application layer to the DB. Sometimes, I think moving some of it can be a viable option.
I did a fair bit of T-SQL back in the day. You're right: it's not fun.
It's okay for shuffling data around and doing SQL-y things in a slightly procedural way. Inserting rows, copying rows. For anything complex... well, it's not made for that.
I run a bunch of business logic in the database but have no interest in writing my whole application as triggers or whathaveyou. It's a bit of a middle ground, perhaps?
How did you decide which bits of business logic would live in the database as opposed to at the application layer(s)?
no interest in writing my whole application as triggers
or whathaveyou. It's a bit of a middle ground, perhaps?
Yeah, I think our position is often misunderstood as "put everything in the database layer."
That is definitely not how I think. I just think moving stuff to the database layer is one possible tool in the toolshed.
One thing I like to use it for is when it's a "low level" database concern, like generating an audit trail whenever a particular table is changed. To me, populating that audit trail is clearly a database concern and not an application concern. (And if there are heavy writes to that table, the performance difference may be large)
> How did you decide which bits of business logic would live in the database as opposed to at the application layer(s)?
Not the previous poster but...
I would think of it as an application layer that is running at the database.
It would make the most sense for logic that is closely coupled to data access... exactly what that is depends on your app. Low-level access control policies... maybe you need to dig data out of a bunch of tables and turn it in to a hierarchy based on complicated user prefs also stored in the database... Or the opposite where you have complex data coming in that needs to be written to a bunch of tables, especially when there's back and forth.
I don't think very many should try to put their whole app in there. Database compute tends to get expensive and complicated to scale, so stuffing things in there just because you can might run in to trouble. Not to mention (except for SQLite) the runtime environment is seriously constrained in all kinds of ways.
The previous post mentions triggers but I don't know what those have to do with this. The chance that triggers are right solution to any given problem is approximately 0% in my experience.
I think this is essentially what I meant? In any event, I responded a little clearer but ya, shaping data and moving it around is a big thing I feel should be done in the DB. Having spent time in the rails world for several years, I worked with a lot of people who didn't want to do ANYTHING at the DB level. Like, obviously they'd do joins and things ActiveRecord could do easily, but they'd be fine pulling in a bunch of rows and reducing them in Ruby. That drove me nuts. I'm not an optimization junky at all but I do not like that in the slightest. Not only is it wasteful and slower, in my experience it's also more error prone.
pulling in a bunch of rows and reducing them in Ruby [...]
Not only is it wasteful and slower, in my experience
it's also more error prone.
Yeah. A lot of problems happen for one of two reasons. The application developers don't wrap the whole thing in a transaction, and open themselves up to consistency problems. Or they do wrap a bunch of database round trips up in a transaction and open themselves up to contention issues, deadlocks, etc.
In both cases, those problems generally never show up in local or test environments. Only in prod... under load.
All aggregating and all math I do in the db. Stuff like taxes, royalties, any reporting stuff (that should probably be in a column store). It may seem like a no-brainer to do that but you'd be surprised. I worked at a fintech briefly and ALL the math was done in Elixir which surprised me. I also usually like to do permissions in SQL, ie, selecting a record based on a permission as opposed to grabbing the record then checking (I go back and forth on this). I do like to have everything that is going on represented in the application code, though, so I've never actually written a trigger in my life... I did write a stored procedure once for generating unique product codes... I never said I was a particularly good engineer, lol. But ya, even in the case of a cascading delete or something I still like to spell that out in application code so there are no surprises. Of course, I've never gotten to work on a team where everyone felt this way.
Audit trails seems like an interesting case I have thought about before (I have been a part of building audit trails at the app level before). It's something I've been curious about but never looked into. I think it depends on how detailed they have to be. Like, just creating a history of every table seems a little wasteful to me, but you're probably talking more nuanced than that. I did talk to someone who worked somewhere where that was the strategy.
I feel like audit trails are under-discussed and under-rated.
Great for debugging (obviously, not as the sole debugging information, hopefully) and indispensible for covering your ass at times. If people screw up and put bad data in they frequently will blame the app and as a developer the burden of truth is often on you. Especially if the app has had bugs in the past... and what app hasn't?
Like, just creating a history of every table
seems a little wasteful to me, but you're probably
talking more nuanced than that
Yeah, doing it indiscriminately is wasteful to the extreme, no arguments! You would want to be judicious about the tables for which you employ it, the size of the data, frequency of updates, etc. In almost all cases you will surely want some kind of automated pruning ability. (Of course, this all applies at the app level as well, no difference here)
What's unique about triggers is that they have access to the "old" and "new" versions of a row. Implementation varies but generally it's something like this Postgres example - note the special `OLD` and `NEW` tables.
CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
EXECUTE FUNCTION check_account_update();
Now, that specific is obviously "application" or "business" logic and it can be debatable if that should go into the database. But audit trails are more of a pure data layer concern IMO.
I completely avoided the need for maintaining an additional dependency (ElasticSearch) simply by taking advantage of fulltext search in Postgres. This did involve some writing of triggers and stored procedure code (which was generated by server-side code, but anyway), which is technically application logic.
There are no perfect solutions in engineering, only tradeoffs.
SQLite not supporting "stored procedures" is a deal-breaker for me. The idea for stored procs is not to "put the process as close to the data" but simply that we have a single place for language-agnostic encapsulation of data procedures.
SQLite is an in-process database. If you need language-agnostic encapsulation of data procedures, SQLite is not for you. I would suggest you consider PostgreSQL.
I don't think I've ever needed language-agnostic procedures in a project where sqlite is also a fit. I like them both but at different times. I'd love to hear your use case though. Do you have microservices in different languages running on the same machine that share a db file? Or maybe a web + command line interface?
Sqlite's internals actually could support something like this: it has a bytecode engine https://www.sqlite.org/opcode.html that's more oriented around executing query plans and it's missing some pieces (e.g. it has no stack, only registers) but much of the machinery is there to expand it to stored procedures
A few years I made a decision to ship a SQLite database in an (internal) ruby on rails package. Why? Because there was a large set of (static) data that was required for the package to work, and it made no sense to make an API to query it from external sources (It wasn't that big, something like 5-10Mb if I recall). At the time it felt like a super dirty hack, but time seems to have validated that decision :)
Did anybody try something like that: read/write to SQLite database file on backend, but also allow the database file to be downloaded at any time by rich JS frontend for read-only querying. I just wonder if the file is going to be (eventually-) consistent and not corrupted.
My hunch is that if you want to do that the safe way would be to have a mechanism that creates a snapshot of the SQLite database for the client to download when they request it.
2. Fly.io’s work on the dockerfile-rails generator detecting Sqlite and Litestack in a Rails project, then setting up sane defaults for where that data is stored and persisted in production. This is all done behind the scenes with no intervention required from the person deploying.
3. Servers are overall faster and more powerful
I hope more Rails hosts make it easier and safer to deploy Sqlite to production. It will lower costs and reduce complexity for folks deploying apps.
SQLite appeals to the hacker because it is simple on the surface, complex beneath the surface, easy-to-use, and does its job well. however, replacing a traditional MySQL or PostgreSQL database with it, takes a bit of work, because it's not a drop-in replacement. so you see this push and pull between people of different strata advocating for SQLite, disparaging it in favor of PostreSQL, and some who try to engineer stuff around SQLite to make it a better fit for traditionally PostgreSQL-type situations.
if you've never tried SQLite on your own and you're comfortable with C, I'd recommend giving it a try, it's pretty dang cool.
166 comments
[ 4.9 ms ] story [ 374 ms ] threadDiscussion: https://news.ycombinator.com/item?id=37497345
LifeFS was announced and is intended to help this. Now seems like (https://fly.io/docs/litefs/getting-started-fly/) it requires an HTTP proxy so that the application can guess about sqlite write/read usage by reading the HTTP request method. This seems... to introduce a different (maybe better?) set of gotchas to navigate.
There are now SQLite cloud offerings but you pay the network overhead and avoiding that was so much of the appeal of using SQLite.
Are people successfully using SQLite in a work or production setting with a replication and consistency strategy that they like? I've had trouble getting a setup to the point where I can recommend it for use at my jarb.
- https://github.com/canonical/dqlite
- https://github.com/rqlite/rqlite
- https://github.com/Expensify/Bedrock
Isn't this not only well-documented, but (restricting to a single writer to avoid distributed systems issues while still making it easy to move that single writer around) sort of the whole point?
Our strategy is to not attempt replication at the level of SQLite. We use a single binary for our SaaS product which shares 1 SQLiteConnection instance for the lifetime of the whole ordeal. Remember - every single SQLite connection instance is a file system abstraction, not some in-memory/networking clever optimized thing that Postgres or SQL Server is managing on your behalf. Every time you open a new connection to SQLite you are doing some pretty heavy-duty OS calls, relative to just reusing a prior connection. SQLite itself is typically built with serialization on by default, which deals with multiple threads on one connection. In my experience, this is the most stable & performant arrangement (with WAL, et. al. also enabled).
Our backup solution is to snapshot the entire VM (or block storage device) that SQLite is running on. Replication is not a concern because our restore strategy is to just bring back a snapshot if required. Our customers are ultimately responsible for this and typically handle it with a few clicks through AWS, Azure or a quick email to their private cloud provider. RPO and RTO is entirely in their court and all parties prefer it this way - them being highly-regulated banks and us being a small startup operating at the edge of the abyss.
To this day, we have not once had to support recovery of a SQLite database from snapshot due to corruption or other weirdness. We've been at it for half a decade now.
I agree that many of the SQLite cloud offerings introduce the same network overhead. With LiteFS, the goal is to have the data on the application node so you can avoid the network latency for most requests. Writes still need to go to the primary so that's unavoidable but read requests can be served directly from the replica. The LiteFS HTTP proxy was introduced as an easy way to have LiteFS manage consistency transparently so you can get read-your-writes consistency on replicas and strict serializability on the primary. That level of consistency works for a lot of applications but if you need stronger guarantees then there's usually trade-offs to be made.
[1] https://github.com/vlcn-io/cr-sqlite
Looks like only a 2MB binary for the extension, so you could in theory just pack it with your app too.
I'm particularly interested because it seems like(For very small databases) you could use SyncThing as the sync backend by just periodically dumping your data to files(And making a new one once the file got too big).
I don't know how you could ever garbage collect the old files aside from some kind of manual "Delete everyone else's stuff and output your own big merged log" command, but it would be really cool to be able to make apps with P2P sync.
It also seems like you could put them in an http server and use it like an RSS feed. Or even serve them via torrents.
https://github.com/superfly/corrosion
It would be possible to distribute cr-sqlite changes in many different ways (like you said, http or torrents, etc.) since any change can be applied out of order.
What I would like is a good primer about the layers on top of SQLite. What does Litestream do for me? How does it compare to competitors? Why not just use SQLite directly? A more in-depth technical discussion would be nice. I'd also like to understand wrappers and ORMs for migration to other systems, should SQLite stop scaling.
SQLite does not provide replication, so there is no way to use it directly (other than copy whole file). If you mean it as "Why not use it as a database" than sure, you can use it directly, though the article states reasons for not doing so (resiliency and concurrency). Postgres is a lot better in those areas, and so is the tooling.
>I'd also like to understand wrappers and ORMs for migration to other systems, should SQLite stop scaling
1. It heavily depends on the orms. For example Django provides good abstraction layer and many things works with any database with no change needed, but many other don't bother about that. However just because a query runs, doesn't mean it will return the same results. Any non-trivial app will rely on numerous accidental details and you can't switch db and expect everything will be fine. SQL is not really portable even in the parts it does cover, and there are many it doesn't.
(And yes, I should have used postgres).
I'll mention: The internet, in the nineties, was powered by 486-grade computers, and things were perfectly performant to a pretty decent scale. If you can get rid of issues like network latency (from e.g. a database on a different machine than your main computer), and similar 2020-era bottlenecks, a lot of web apps can serve millions of users from a single machine. That's doubly true with gigabytes of RAM and modern SSDs.
With RAID and regular backups, it can even be pretty robust.
It's even easier to do now that you can write static client apps that just need to push and pull little bits of data to and from the server.
That's not an architecture that's used often, but it keeps things very simple and can work quite well.
Planning to use litestream as a library to dynamically swap in/out dozens of databases in a process. Looking at the code it'll easily allow that (super clean, kudos!).
So many thanks, it's going to enable a lot of new things!
rqlite[1] creator, happy to answer questions too.
[1] https://www.rqlite.io
Why compare the latency of a remote Postgres database with a local SQLite database? If your app is so simple and self-contained that it runs on a single EC2 instance using local files, nothing prevents you from installing Postgres on the same machine, whether inside a container or not.
I have some simple apps on EC2 with MariaDB on localhost, and well-tuned queries rarely take more than 100-200 microseconds. That's total query execution time, not just communication latency. RDS just sucks for this kind of use case. It's not a useful comparison.
> As much as I love tuning SQL queries, it’s becoming a dying art for most application developers. Even poorly tuned queries can execute in under a second for ordinary databases.
Didn't you just say that milliseconds matter?
The point about operational overhead makes sense, though, and IMO it's the only point in this unnecessarily long article that's actually worth considering. I do have a couple of other apps running on SQLite, so I appreciate the simplicity.
Think Lambda (or equivalent) instead of EC2.
This apples-to-oranges comparison (strawman? motivated reasoning?) unfortunately makes me mistrust this entire article.
It's still normal to refer to e.g. "database server", "application server", etc. processes even when running on a single machine. Re. EC2, I'm referring to the surcharge of having a dedicated instance at all, vs. working at the container / object storage level of abstraction.
Unfortunately, Litestream (which OP is promoting in TFA) needs to run as an independent process inside a VM and/or container in order to sync your writes. It's not very different from a traditional RDBMS in that regard. Even the title says "server-side" SQLite, not "server-less" SQLite.
The SQLite DB is just a flat file that can be packaged in a lambda or whatever cloud's object store. It makes sense for fast access to read-heavy or read-only data. Even a shitty query can return rows in less than a millisecond where a remote DB that same amount of data is tens of milliseconds away.
Seems like a disaster waiting to happen unless you have a bunch of logic centralized somewhere to keep track of last know schemas per user client database. And if you’re going to do all that, unless you desperately need low latency (in which case you could use a multi region database like cockroach), why not just centralize?
I've been building exactly that here: https://github.com/simonw/sqlite-migrate
Don’t get me wrong - this is neat - but I’d never suggest anyone to actually use this outside of a fun experiment. The problem with existing SQL dbs isn’t really the architecture - its the awful queries that do in memory sorting or make temporary tables for no reason or read-after-write, etc, not network latency. SQLite won’t fix your current production problems.
If it turns out they’re building this for customers throwing cash at them, awesome. I just somehow doubt it. I think Planetscale has the better approach: a drop in replacement for MySQL/RDS with a smarter query planner. As a production engineer that’s what I want to pay for!
I work at a very similar place now that uses Shopify. Managing that many SKUs on Shopify is crazy painful.
The thing is is that custom ecom solutions really aren't that hard. The off-the-shelf ones are complex because, as stated above, they are trying to be everything to everybody.
I’m fairly confident I could spend a month or so writing a custom solution for a shop selling and shipping a few hundred tshirts a day that would save them enough money to break even on the software in a few years (compared to off the shelf solutions).
If I was starting my own tshirt company, I’d definitely do it.
We did an evaluation on several out of the box ecommerce solutions and none of them were able to meet the requirements that absolutely had to be there. Shopify flat out said no, they can't help us, etc.
I think we generally have this problem. It's not the typical business that can use that stuff, it's the average business. Unfortunately no business is the average business.
8 years ago we built our own ecommerce site + warehouse app (inventory tracking, fulfillment, receiving) for a few hundred orders per day. The goal was to be able to better see profit margins by product, track where the money was going/coming from in detail, along with cleaning up the inventory management part of the operation.
The warehouse people loved the change because it really streamlined the fulfillment process and it basically reduced their errors to zero. Owners loved it because of the all the cost/price tracking. It took about 6 months of work by 2 devs.
Before this they were on woocommerce. It was slow as shit. We looked at shopify but the integrations with 3rd party warehouse stuff was bad. The other solutions we looked at were overly complicated swiss army knives.
I'm going to take a wild ass guess here that you are wildly understating the "bit of legacy spaghetti"
Are there any viable alternatives though? I often wonder what an SQL-like language built from the ground up would look like.
If I start hitting hundreds of writes per sec, then, thats either awesome or I wrote some horrible code.
In my experience SQL databases are pretty poor at executing the kinds of deeply nested joins needed to return all of the data needed to render more complex UIs. It almost always ends up being faster to simply make nested selects in batches. So latency ends up mattering in these cases.
You can work around this by denormalizing the data, but this often explodes your data size.
The outcome is that my persistence later is not adding $200/m immediate service overhead cost and my deployment was easy to manage, which is a strong promise made to the rest of the team. I think there's a place for SQLite, but just like any tool you need to know that your design constraints match the constraints of the tool.
> The per-query latency overhead for a Postgres query within a single AWS region can be up to a millisecond. That’s not Postgres being slow—it’s you hitting the limits of how fast data can travel.
No. An AWS region has a radius of less than a few dozen kilometers, more likely around 5km. Lightspeed doesn't factor into it at those small distances. That millisecond is indeed Postgres being "slow" in these terms. (Most of it is the networking stack, as noted.)
This basic error makes me question the validity of the document. I stopped reading here.
I agree that "networks are slow" but this sort of false justification is not the way to sell it. Is this an attempt to make the author seem like he knows what he is doing because he knows the speed of light?
Postgres is good on multi core, incredibly feature rich, multi user, supported by everything, lightweight and has all the tools for production workload and management. All stuff that is important.
Most important difference to me being SQLite I understand lacks flexibility in modifying table structures.
Well, cost, right? Cost is a reason why someone may not want to use a traditional RDBMS. AWS RDS and GCP Cloud SQL aren't exactly the cheapest solutions out there.
This matters for cloud in that it means with Postgres you cannot take a "lots of small databases" strategy, e.g. database per user or database per document. You pretty much have to group a lot of data into one big database.
Many apps want to do that anyway! For them, Postgres makes sense. But in the growing world of global deployments and edge compute, the lots-of-small-databases approach is getting popular because it means you can store than data out on hundreds or thousands of edge locations, rather than a single central location. And many (not all) applications actually fit pretty well into a database-per-user or database-per-document model. Centralizing their storage only hurts performance for no benefit.
As a bonus, if you are able to run sqlite compiled directly into your app, not making any kind of network connection, it can be much faster than Postgres, especially in "N+1 select" situations (which are well-known to be a problem with most SQL databases, but are not a problem when using local sqlite). Postgres does not support running as a library like this.
You're right, it's a very interesting strategy. Unfortunately, last time I looked Cloudflare's D1 didn't support this approach as there was no API to create a database at runtime from Worker code. Has that changed?
From TFA:
> if you don’t need the Postgres features, they’re a liability. For example, even if you don’t use multiple user accounts, you’ll still need to configure and debug host-based authentication. You have to firewall off your Postgres server. And more features mean more documentation, which makes it difficult to understand the software you’re running. The documentation for Postgres 14 is nearly 3,000 pages.
False
>> you have to firewall off your server
Well yes. Are you saying SQLite servers don’t need a firewall?
>> more features/more documentation/hard to understand your software
Features lead to powerful software, documentation leads to understanding.
I'm not the author. But I can tell you there are no "SQLite servers". That's kind of the whole point.
Estimating the complexity of using a project can be really...complex. I think about systems I have used which make it easy to use a minimal set of features and where I don't have to reason about or be negatively impacted by aspects I do not benefit from, and other systems where things are less easily isolated and more challenging to reason about.
I do think the Postgres docs in particular seek to be a reference in addition to an operating manual and I for one really enjoy them. I think the point is well made that Postgres can be too much (or too much right now) for many projects.
For this case I guess they could try to bend sqlite to do this but the pain in doing so will probably not be worth the long term trouble than just using mysql or postgres or something like those.
I'm using sqlite in production for a backend service. There are plenty of downsides, but to focus on the positives:
- I can run all tests in a database in memory. It's incredibly fast to "spin up" and I can use a separate database per test
- Related, I find that I write more tests against the database instead of mocking a database, which cuts down on time writing tests.
- I don't need to start a database to run the backend
- I can have snapshots of the databases in a single files for various scenarios
All in all, the development process feels a lot faster. When something takes a millisecond instead of seconds, you do things differently.
> When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds.
As if postgres and others don’t have a way to run application logic at the database. I like the SQLite way of doing it — you pretty much freely choose your own host language — anything with a decent SQLite client will work. While in postgres, for example, you’ll probably end up with pgplsql (there are others, but there are constraints). So this isn’t about latency, as the whole section of the article suggests.
There’s actually a relative weakness in SQLite here, since it doesn’t include a built-in protocol to support running application logic separate from the database. That’s also architecturally useful, and so you may have to find/build a solution for this.
Just adding replicas isn’t a general solution either, because each replica has an inherent cost: changes have to somehow get to every replica.
E.g., systems can grow to have a lot of database clients. In traditional setups you begin to struggle with the number of connections. You might think with SQLite, “hey, no connections, to problems!” but now, instead of 1000 connections you’ve got 1000 replicas. That’s something you’re going to have to deal with… that’s 1000x write load, 1000x write bandwidth.
Perhaps fly.io has a solution for this, but I suspect it’s going to cost you.
I think it's reasonably fair of them not to specify this. The target audience of this article is people who are writing their applications in languages like Elixir, JS, Ruby, Python, and are not going to be interested in pushing all of their business logic to the db.
This is probably the least popular possible thing you can possibly suggest as an engineer in 2023.
Me? I actually think pushing app logic to the DB is a solid, underrated, and possibly even optimal solution for a lot of scenarios.
But don't tell anybody I said that. I might get beaten up.
That's probably why fly.io sort of glosses over it as a possibility. Almost nobody is even considering it as an option in 2023.
I’m always thinking that a db that can also run business logic would be the ultimate backend solution for crud apps.
I know there are several options to do it, but I always assumed Postgres did not support it.
What do people have against it?
- Enforce application logic with constraints so you don't have to duplicate it
- Use triggers and get access to things like old and new without having to create a bunch of transactions
- If you have multiple apps sharing a DB, you either keep your business logic consistent across them vs just doing it in the database
- You can version your business logic with your schema
Good migration systems exist, and people should use them!
I held off on doing interesting things with triggers for more than a decade. In the past year I've started leaning into them much more heavily (actually using them in SQLite) because I have confidence that I can both write good tests for them and have good migrations automation in place for version-controlling my schema.
People don't like it for a lot of reasons. Making privilege escalation harder is a big one, but also, all the CPU (and memory) load on serializing that data is CPU that could be used managing the distributed processes problems that only the DBMS can solve.
Personally, I think access management on those tools needs to improve a lot before they get usable. But also, the data-oriented languages have some issues, and the non data-oriented ones don't gain much by running inside the database.
IMO, we are missing a really good data-oriented language. But I don't see any gain from running it inside the database.
A lot of the argument against it is: SQL is stodgy and uncool, and I want to use Cool Language XYZ. And honestly, I don't entirely blame people for that. If you want to work in this industry you need to do things the "cool" way or you'll never even be considered for roles. Gotta keep that resume looking good.
People also don't appreciate how performant it can be. Depending on what you're doing it can be orders of magnitude more performant to do things in-database versus shuffling things back and forth. (It can also be less performant...)
There are definitely cons to that approach.
One is scalability. It's easier to scale your app layer horizontally than it is to scale your database server vertically. This isn't necessarily an issue: a modern beefy server CPU with 64-128 cores and a TB or two of RAM is more than 99.9% of companies need, is really not that expensive, and is probably a lot cheaper than more complicated setups and extra devops headcount. But that's not cool either.
Two is the language/skills mismatch. You've got an app layer in one language, a frontend in another, and now potentially a data storage layer written in a third. This is a valid concern, but also nobody seems to use it as an argument against Javascript frontends, so apparently sometimes it's cool and sometimes it isn't.
Debugging stored procedures sort of sucks. That's fair. (But also, nobody is saying to rewrite your entire app, or even most of it, in the DB layer)
Common migration tools often don't really have explicit support for stored procs and things like that, but AFAIK they do let you run arbitrary SQL DDL stuff, so I don't think this is a hard barrier.
The languages for writing it are not as comfy as traditional programming languages, which affects how expressive and maintainable your code will be. The tools for debugging a regular language might also be better than debugging application logic in SQL. Having done some of this in T-SQL, it's very tedious day-to-day compared to writing C#.
Don't expect your experience with any other DBMS to give you an idea about how nice it is to program in Postgres.
It's still not as nice as creating some independent code. But Postgres is quite nice to program in.
Do you have any interesting blog links?
But also, you don't need extensions to enable other languages, postgres allows those out of the box (through the C-ABI). Anyway, that is not very compelling, because plpgsql is quite good already.
https://wiki.postgresql.org/wiki/PL_Matrix
I did a fair bit of T-SQL back in the day. You're right: it's not fun.
It's okay for shuffling data around and doing SQL-y things in a slightly procedural way. Inserting rows, copying rows. For anything complex... well, it's not made for that.
But even SQL Server lets you call .NET CLR code now: https://learn.microsoft.com/en-us/sql/relational-databases/s...
And of course Postgres supports Python, etc etc etc.
That is definitely not how I think. I just think moving stuff to the database layer is one possible tool in the toolshed.
One thing I like to use it for is when it's a "low level" database concern, like generating an audit trail whenever a particular table is changed. To me, populating that audit trail is clearly a database concern and not an application concern. (And if there are heavy writes to that table, the performance difference may be large)
Not the previous poster but...
I would think of it as an application layer that is running at the database.
It would make the most sense for logic that is closely coupled to data access... exactly what that is depends on your app. Low-level access control policies... maybe you need to dig data out of a bunch of tables and turn it in to a hierarchy based on complicated user prefs also stored in the database... Or the opposite where you have complex data coming in that needs to be written to a bunch of tables, especially when there's back and forth.
I don't think very many should try to put their whole app in there. Database compute tends to get expensive and complicated to scale, so stuffing things in there just because you can might run in to trouble. Not to mention (except for SQLite) the runtime environment is seriously constrained in all kinds of ways.
The previous post mentions triggers but I don't know what those have to do with this. The chance that triggers are right solution to any given problem is approximately 0% in my experience.
In both cases, those problems generally never show up in local or test environments. Only in prod... under load.
Audit trails seems like an interesting case I have thought about before (I have been a part of building audit trails at the app level before). It's something I've been curious about but never looked into. I think it depends on how detailed they have to be. Like, just creating a history of every table seems a little wasteful to me, but you're probably talking more nuanced than that. I did talk to someone who worked somewhere where that was the strategy.
Great for debugging (obviously, not as the sole debugging information, hopefully) and indispensible for covering your ass at times. If people screw up and put bad data in they frequently will blame the app and as a developer the burden of truth is often on you. Especially if the app has had bugs in the past... and what app hasn't?
Yeah, doing it indiscriminately is wasteful to the extreme, no arguments! You would want to be judicious about the tables for which you employ it, the size of the data, frequency of updates, etc. In almost all cases you will surely want some kind of automated pruning ability. (Of course, this all applies at the app level as well, no difference here)What's unique about triggers is that they have access to the "old" and "new" versions of a row. Implementation varies but generally it's something like this Postgres example - note the special `OLD` and `NEW` tables.
Now, that specific is obviously "application" or "business" logic and it can be debatable if that should go into the database. But audit trails are more of a pure data layer concern IMO.There are no perfect solutions in engineering, only tradeoffs.
Sqlite's internals actually could support something like this: it has a bytecode engine https://www.sqlite.org/opcode.html that's more oriented around executing query plans and it's missing some pieces (e.g. it has no stack, only registers) but much of the machinery is there to expand it to stored procedures
Their reasoning for not doing this is not unreasonable, but it certainly would be cool if such functionality existed.
https://www.sqlite.org/appfunc.html
Perhaps this will grow into a more thorough implementation.
That being said I love sqlite and it should be the DEFAULT database with any application until something is demanded otherwise.
Handles tens of thousands of requests a day very smoothly! :)
One way to do that is with VACUUM INTO, e.g. how I use it in this TIL: https://til.simonwillison.net/sqlite/python-sqlite-memory-to...
If your database is less than 100MB or so I imagine this would easily be fast enough that the performance overhead wouldn't be worth worrying about.
The work that’s made this possible is:
1. Litestack https://github.com/oldmoe/litestack runs everything on Sqlite
2. Fly.io’s work on the dockerfile-rails generator detecting Sqlite and Litestack in a Rails project, then setting up sane defaults for where that data is stored and persisted in production. This is all done behind the scenes with no intervention required from the person deploying.
3. Servers are overall faster and more powerful
I hope more Rails hosts make it easier and safer to deploy Sqlite to production. It will lower costs and reduce complexity for folks deploying apps.
Who is using this and why is it such a hot topic on HN?
if you've never tried SQLite on your own and you're comfortable with C, I'd recommend giving it a try, it's pretty dang cool.