8 ms·

Siren Call of SQLite on the Server

114 points by todsacerdoti 2y ago

anentropic 2y ago
Good points, but it doesn't mention the reason I think that Fly (and Cloudflare) are doing server-side SQLite in the first place, which is to have a db "at the edge" to go with their edge compute model Whether that's necessary or useful for your use case is another question
- tracker1 2y ago
  Also with mentioning Turso's efforts in a similar vein. Definitely interesting conceptually. But I question if something like cockroach could be just as good in practice.
  - anentropic 2y ago
    I think the point of "edge db" is mostly to reduce latency Otherwise the point of the article stands - using a conventional db server solves all the bits that are difficult with sqlite as a backend
- WorldMaker 2y ago
  Also "at the edge" sometimes often implies read-heavy applications and places where you can bake essentially "static" DBs. In theory all the SQL query power benefits of a full RDBMS, but with the presumed simplicity of (mostly) static file deployments via traditional CDN file copies. Many applications are going to be write-heavier than that. Many applications don't need to distribute database deployment by "simple file copying" to potentially hundreds of "edges". But there are certainly interesting use cases for that when you can be read-heavy, deal with write trade-offs, and can easily "simple file copy everywhere" a DB. (Though I think most of the ones I find interesting are more Datasette and its plugins than Fly/Cloudflare style.)
tracker1 2y ago
I've only really suggested sqlite for a client/server web app once. It was mostly as the application has data which was naturally referred to specific events and needed a good arrival strategy. I felt that being able to run the event and simply copy out the data at the end was a very prudent option. I'm the end was IMO a very convoluted solution and a custom archival format. Combined with most of the application logic in stored procedures. To this day I wish I'd fought that battle a bit harder.
Skinney 2y ago
Good article. I currently use Litestream (https://litestream.io/ https://litestream.io/) for efficient SQLite backups to S3, and I'm happy with that. For me, the main point to consider wrt SQLite is if you need high availability or not. And how easy it is to setup litestream and persistent storage.
bambax 2y ago
This is all true. Like most people here I'm deeply, deeply in love with SQLite, but using it on the server is not worth it, exactly for the reasons listed in the post. To paraphrase: a big part of the appeal of SQLite is that there's nothing to set up. But if one wants to use it in production then that advantage is gone.
- vidarh 2y ago
  SQlite on the server is worth it when the server side is an application and you treat SQlite as a per user datastore for an application that is mostly per user, or where shared data across users can be treated as a queuing/async messaging problem. Decomposing a system to treat it as a per-user application working on per-user data works for a whole lot of problems and solves a whole lot of problems. It scales effortlessly. I've done that kind of system even with flat files, handling millions of user accounts on a set of servers with an aggregate capacity less than my current $300 mini PC.
  - egeozcan 2y ago
    > where shared data across users can be treated as a queuing/async messaging problem Really asking: Why would you want that complexity when a single database that provides ACID with multiple users can provide that out of the box? Sqlite shines when it's on the edge (be it a fat desktop app, or a thin-client which can hold a cache, or any other modern definition of edge), and while I absolutely adore the tech[0], I don't get why people try to repurpose it to simplify server architecture, especially in this day and age even the most database with the most complex installation is one docker pull away. [0]: I honestly think Sqlite is one of the best software ever developed.
    - vidarh 2y ago
      You're assuming complexity, whereas my experience is that it reduces complexity. E.g. you get infinite scalability. You get no need to ensure all your queries are scoped correctly, because they can't directly access other data. Most large systems ends up with async processing and/or eventually sharding anyway, and then you get to restructure around those flows anyway, while still having the complexities of semi-centralised databases. The world is async - once you embrace async designs from the start and design your UX around accepting that there are very situation you need global serialisation of events, it gets a lot simpler. > Sqlite shines when it's on the edge (be it a fat desktop app, or a thin-client which can hold a cache, or any other modern definition of edge), Which is not materially different from a server spinning up to serve requests for a single user. > I don't get why people try to repurpose it to simplify server architecture, especially in this day and age even the most database with the most complex installation is one docker pull away. Because maintaining a large database cluster and scaling it to large user sets, and ensure the application software is using it correctly to avoid problems is a lot of work. I built my first datastore-per-user system in '99, without the luxury of SQlite. It was a revelation. It was a fundamentally so much more pleasant way to work. In some cases, datastore-per-user is the right pattern. In other cases datastore-per-tenant. In some cases, a big RDBMS is still the better option. But don't dismiss the former two without actually exploring it. Over the years, I'm finding it fits more and more places, especially thanks to SQlite.
leosanchez 2y ago
gobackup[0] and sqlite_rsync are good tools to backup sqlite databases. [0]: https://github.com/gobackup/gobackup https://github.com/gobackup/gobackup
tzury 2y ago
I am currently experimenting with multi read/single write via async FastAPI endpoints and my benchmarks tells it will scale well enough for the first 6 month of anticipated growth, while major part of the time will be beta testing users. In any case, we do things that scale at start by principle.
- tzury 2y ago
  To my surprise, it is performing better than a ZMQ based solution I begun with (in terms of concurrent connections).
deleted 2y ago
[deleted]
deleted 2y ago
[deleted]
andrenotgiant 2y ago
Something I've always wondered... How do downstream DB integrations like Fivetran or other ELT services play with SQLite? It seems like that's another consideration for running it in production at a real company.
- memhole 2y ago
  I don’t think most touch it. As much as I’ve always thought embedded dbs have a place in data analytics and warehousing, I think the issue is that it’s really a sql api on top of a file system and it’s easiest to just copy the db with something like rclone and treat it like a file. Like others mentioned there’s also litestream for replication. Fivetran, I believe is still involved and committed to duckdb. Duckdb has a SQLite extension. So, it’s entirely possible they could add SQLite support. I would say the issue then is familiarity and demand within the industry. https://www.fivetran.com/blog/how-fast-is-duckdb-really https://www.fivetran.com/blog/how-fast-is-duckdb-really They also have a talk given at duckcon that goes over the architecture.
feverzsj 2y ago
There are also rqlite and dqlite. SQlite is good enough for small to middle projects, which are the vast majority.
- IgorPartola 2y ago
  And of course there is https://en.m.wikipedia.org/wiki/Berkeley_DB https://en.m.wikipedia.org/wiki/Berkeley_DB
  - ape4 2y ago
    from that page: A program accessing the database is free to decide how the data is to be stored in a record. Berkeley DB puts no constraints on the record's data. The record and its key can both be up to four gigabytes long. Berkeley DB supports database features such as ACID transactions, fine-grained locking, hot backups and replication.
Ciantic 2y ago
One alternative is to use pglite [1] which is Postgres compiled to WASM. It can act as a sqlite replacement during development, then when you are ready to move to a server model your SQL is already in Postgres format. [1]: https://pglite.dev/ https://pglite.dev/
- OutOfHere 2y ago
  Isn't its use limited to Typescript?
  - Kinrany 2y ago
    Or anything that can embed a WASM runtime I imagine. They're considering a native build as well, search term "libpglite". Though I feel like providing it as a library would work very well initially. Roll a suitable minimal WASM runtime and a WASM blob into a single package, separately for every language.
- euroderf 2y ago
  In the WASM Component Model, there's a Postgres example available where the database runs in Docker. I'm hoping they whip up a version that uses SQLite instead, but with a similar WIT interface, and drop the Docker nonsense.
andrewstuart 2y ago
SQLite is great for databases that don’t need management and won’t change their structure and don’t need remote access.
- VWWHFSfQ 2y ago
  what databases are those
pimbrouwers 2y ago
Be brave. Just try it. Removing the network latency is a massive gain, especially for small/medium apps, which is most sites. And if you find yourself asking this, the answer is yes, it will work. SQLite will force you to rethink your architecture a bit, don't forget it doesn't HAVE to be _one_ file. This alone helps you scale further. There are some chafing points: 1. Schema migrations 2. Configuration, PRAGMA's applied at design time or during each connection
- bambax 2y ago
  It kind of does have to be one file, because foreign keys can't be enforced across files. For a simple example, if you have one db file per user, as others recommend, how do you check that a username or an email is unique? Do you maintain a "global" db for fields with unique constraints? But then when those fields are updated you need to manage this at the application level, and atomicity can't really be guaranteed... Or is there another way?
  - Skinney 2y ago
    Keep user information in one database, keep user _data_ in another. It all depends on your app, really. For some apps the above won't make sense, but maybe it would make sense to keep one db per org, or something like that.
    - gnulinux 2y ago
      You can also use a different but similar tool e.g. use sqlite to keep user information, use duckdb to keep user data. I find this combo very effective since sqlite is best as fast btree based indexed operations and duckdb is best at multiple row based aggregate computations (get me user X vs get me all users with property Y).
  - lolinder 2y ago
    For the one DB file per user question, I'll take a stab at a solution: * Global store of user identifiers mapped to user IDs. Identifiers can change but the underlying user ID is immutable. * One DB per user ID. The per-user DB doesn't really need to know the user identifier, and at auth time you don't need to access the business logic, so cross-DB updates should not be a problem. In the general case, similar logic applies: you can't partition just anywhere, but where you can partition is pretty well defined.
gizmo 2y ago
There are exceptions to every rule but most of the time you're way better of when you use simple and reliable technology. If you need to copy something between servers you probably don't need anything more complicated than rsync. For blob storage you probably don't anything more complex than S3. For database backups do you really need multi-region streaming backups or does mysqldump suffice? Every product has limitations. Well designed and simple products/tools have limitations that are easy to understand and easy to work around. The more complicated your architecture gets the more devilishly complicated the edge cases become. That's where you get punished for choosing complexity. MySQL and Postgres are larger than Sqlite by more than an order of magnitude. What do you get in exchange for all those lines of code? Do you actually need the extra functionality? If you do, great. If you don't, choose the simplest solution that works. When you use simple tools changing your mind later is cheap.
- christophilus 2y ago
  Proper concurrency, and the ability to scale out your applications across multiple servers / nodes. I’ve never worked on any application that had only a single web server, and yes, most of my applications have had to scale out a few times under load— even the smaller ones. Doing this with Postgres is trivial. Doing this with SQLite requires engineering. I’m not scared of engineering. I just don’t think it’s warranted when you have such an obvious, tried and true off the shelf database like Postgres available to you.
  - deleted 2y ago
    [deleted]
  - vidarh 2y ago
    Scaling with SQLite and a datastore-per-user model is trivial. If you build your app as if it's using Postgres when you're using SQLite, then, yes, you'll suffer. If you pick SQLite, you should pick it because you will actually build your application in the way that makes most sense with SQLite. Datastore-per-user makes sharding/scaling trivial, and tends to drive an architecture that is easier to reason about. You're never going to e.g. accidentally leak data between users because of a broken query, for example.
Cthulhu_ 2y ago
I've used SQLite in a project once, client-server application configuration management interface; I picked SQLite because the application I was replacing also used it, and because it was standalone; the application was run on-premise, and one of the issues was that the version of PHP it ran on was horribly outdated (plus it was PHP, so it needed some setup work). My replacement was intended to be fully standalone, no external dependencies, and so also no requirement to have a database server running somewhere. I would've preferred something like postgres but embedding that and running it with redundancy is nontrivial compared to sqlite. Mind you I never did get as far as running sqlite redundantly (using e.g. rqlite), I quit after 2.5 years since the project was bigger than any project I've ever done, and those were fully staffed teams.
nasretdinov 2y ago
IMO it's always going to be a problem if you rely on hype instead of common sense. All the hype around SQLite is because you often don't _really_ need to have high availability, online schema changes, etc, and 99% of the web sites can easily fit on a single (large) server. It's also worth mentioning that it doesn't meab that if you're not using SQLite you automatically get backups, replication, high availability, etc. There are few (if any) production-ready databases that are even capable of high availability, and those who are typically do this at a cost of catastrophically low write performance, especially compared to SQLite WAL mode.
VWWHFSfQ 2y ago
SQLite is great for what it is and its stated purpose, but it's a fundamentally poor database if you actually care about your data. I'm not talking about durability. It's perfectly durable, as in it won't lose your data (well, it might). I'm talking about the lack of actual data types and "everything is a string". I'm talking about the general flimsiness. Hipp even said that is a non-goal because it will bloat the embedded object size in your binary. Because that's what it's for.... Insert a string into an int column? Go ahead, it doesn't care. Use a date function on a text value it doesn't recognize as a date? Go ahead, you'll just get a NULL instead of any indication that your value is wrong for what you think it should be. It's fine for the address book on your phone, or the bookmarks in your browser. But terrible for server things that are important.
- boristsr 2y ago
  That's not quite true. There is support for "strict" tables which does have more stringent rules around types https://sqlite.org/stricttables.html https://sqlite.org/stricttables.html
  - VWWHFSfQ 2y ago
    Mongo DB also disables safety features by default to improve their benchmarks. But Mongo DB gets lots of criticism for that and SQLite gets none.
- richbell 2y ago
  I believe foreign key constraint validations are also disables by default.
srameshc 2y ago
Ever since I saw Cloudflare D1, I have been obsessed by distributed SQLite. Primary reason is the cost of managing and owning it is relatively low, but then it was still not easy managing Rqlite. I wanted managed and I recently found Turso and it is amazing, but it's hard to understand the reads and writes with such databases. I knew about Fly.io investment in this area but I did not know about the offering Litefs. The author made some good arguments and probably it is wise to listen about the challenges and think twice before making it a default database out of sheer excitement and cost.
- summarity 2y ago
  https://litesync.io/en/ https://litesync.io/en/ is a mature way of distributing SQLite with arbitrary topologies and no modifications to client code. Supports multi primary write and DDL replication. It’s a paid solution, but that follows the model of all commercial SQLite mods (one time license fee). I use it, though just for a simple case of having a live replica of a production server running SQLite so I can try out migrations and generally work in an isolated copy.
mythz 2y ago
I've started using SQLite for all new Apps a couple of years ago and haven't looked back. It was the catalyst for us to get off AWS (and RDS) where we now deploy (.NET) Docker Apps with Kamal to a shared Hetzner Linux VM which uses Litestream for replication to Cloudflare R2. We've adopted several ways to workaround SQLite's single concurrent writer [1] including: splitting system functionality in multiple databases, separate locks per db, performing writes within a single worker thread for each db, etc. Despite being significantly cheaper, its next to zero latency actually performs better than our Apps using RDS. We also benefit from monthly rolling databases for request logs and db jobs and make use of SQLite's Application defined SQL Functions (i.e. calling C# functions in SQL queries) for some features which isn't really possible with other networked RDBMs. [1] https://servicestack.net/posts/scalable-sqlite https://servicestack.net/posts/scalable-sqlite
bob1029 2y ago
This is something that I am a huge fan of, particularly from a latency & performance perspective. NVMe + SQLite is a hell of a package deal. If you just need to go as fast as possible no matter what, this is a great place to start. At the highest end the hosted solutions can outpace SQLite but it's like that meme [0]. That said, I'm working on a new contract/project that is somewhat antagonistic to the idea of using SQLite on the server. The biggest reasons are due to employees needing direct (transactional) access to the live data and the ability to do online schema changes. There are ways to force this with additional tools, but I'm finding that just paying for MSSQL provides a lot of uplift on this particular problem. The possibility that other employees already have experience with tools like SSMS should not be overlooked when making technology decisions. People are generally the biggest bottleneck in any business. [0] https://knowyourmeme.com/memes/look-what-they-need-to-mimic-a-fraction-of-our-power https://knowyourmeme.com/memes/look-what-they-need-to-mimic-...
jmull 2y ago
The "Whys" aren't strong. #1 makes the point that once you use it in production you start needing production things, like backups and horizontal scaling, and those can get complicated, but for non-sqlite dbs you can use a managed solution to handle the complications for you. But (1) managed solutions for sqlite exist too, and, (2) anyway, successful projects soon out-grow the limitations of managed solutions, so that's a limited runway -- probably about the the same runways where unmanaged sqlite remains simple. #2 points out "Migrations are not great in SQLite." Migrations aren't great in any db. I think easy migrations can be easier in some other dbs, but those weren't the problem. Tough migrations are essentially rooted to evolving understanding of business rules, which no dbms can make "great" to do. #3 points out that decoupling storage from compute is a good idea. But with traditional client/server dbs, compute is soldered directly to storage, so that's a big win for sqlite. I know they aren't thinking about the db server server as compute, but they certainly should be -- it's compute in all the ways that hurt you and help you. #4 points out that migrating away from sqlite is extra work. But that's not really an independent argument for not choosing sqlite since it presupposes sqlite was the wrong choice in the first place. I have nothing against, say, postgres. But while sqlite on the server has some real drawbacks, it has some really nice properties too. Its simplicity and flexibility make it a strong default choice these days. I would be wary of the current managed providers -- their edge bias tends to lead you to system designs that aren't great.
om8 2y ago
You can’t build fault tolerant system without network lag. Your hardware can fail you any second, and every backup will be inconsistent with your local db. To solve it, you need to have a system with proper control plane and consensus algorithm like paxos inside. SQLite is great for simple projects that can lose data, but, if you need to have no downtime, you need a distributed db.
- vidarh 2y ago
  In practice, there are very few systems that need "no downtime", and even fewer that achieve it, even with a distributed db.
p0w3n3d 2y ago
I don't know why, but much more appealing to me is PostgreSQL on the server.
deleted 2y ago
[deleted]
Moto7451 2y ago
I once built an app for compliance reasons that needed a little bit of dynamic data and quarterly updates that allowed us to stay in business in a few of our minor markets (worth cool tens of millions) and a SQLite DB with some basic turn key CRUD admin tools was fantastic. The data was larger than a simple YAML/TOML file would be tolerable to live with and needed some love and attention by lawyers. I could update/let them update the DB in dev and check in the DB file (it was smaller than our very unreasonably large TOML file we used for app config) and had the same app in every environment without managing MySQL or Postgres migrations. The whole thing happily ran on three EC2 micro instances and given the relatively low traffic (low thousands of visitors per month) it was a great set of technical decisions. For that narrow use case at least, I am a huge believer in doing this. Another prod use case for SQLite I’ve implemented was in taking a market specific search engine component (eventually replaced with Elasticsearch after a few years of R&D, but I digress) where I needed to implement sharding, reordering of results, and a few extensions to the engine that nominally relied on INI files for the limited extensibility it did support. The engine itself lacked any network coordination so I scheduled things through updated files lobbed into S3 and let a scheduled job pull them down to each machine. If a specific shard was to be out of service that data was in SQLite along with what operations to run. There are a lot of reasons for why this was the case and happens a bit over a decade ago so there are much better ways of doing this now. RQLite didn’t exist yet for example and there was a very dumb license restriction keeping MySQL and Postgres off the table (kill me). Simply syncing SQLite files across S3 sufficed since the engine itself had a more severe write lock on indexing than SQLite did. I could just swap the files while I waited for that operation to complete. Since I could actually change how SQLite worked, some full text search operations moved to SQLite since the licensed product wasn’t good at that as it was marketed/intended to be used for its faceted search and proprietary ontology. Overall SQLite was the nicest part of that experience as well. Well at least until we moved to Elasticsearch and I no longer had insane license terms I had to code around.

114 points by todsacerdoti 2y ago

Good points, but it doesn't mention the reason I think that Fly (and Cloudflare) are doing server-side SQLite in the first place, which is to have a db "at the edge" to go with their edge compute model Whether that's necessary or useful for your use case is another question

tracker1 2y ago
Also with mentioning Turso's efforts in a similar vein. Definitely interesting conceptually. But I question if something like cockroach could be just as good in practice.
- anentropic 2y ago
  I think the point of "edge db" is mostly to reduce latency Otherwise the point of the article stands - using a conventional db server solves all the bits that are difficult with sqlite as a backend
WorldMaker 2y ago
Also "at the edge" sometimes often implies read-heavy applications and places where you can bake essentially "static" DBs. In theory all the SQL query power benefits of a full RDBMS, but with the presumed simplicity of (mostly) static file deployments via traditional CDN file copies. Many applications are going to be write-heavier than that. Many applications don't need to distribute database deployment by "simple file copying" to potentially hundreds of "edges". But there are certainly interesting use cases for that when you can be read-heavy, deal with write trade-offs, and can easily "simple file copy everywhere" a DB. (Though I think most of the ones I find interesting are more Datasette and its plugins than Fly/Cloudflare style.)

tracker1 2y ago

I've only really suggested sqlite for a client/server web app once. It was mostly as the application has data which was naturally referred to specific events and needed a good arrival strategy. I felt that being able to run the event and simply copy out the data at the end was a very prudent option. I'm the end was IMO a very convoluted solution and a custom archival format. Combined with most of the application logic in stored procedures. To this day I wish I'd fought that battle a bit harder.

Skinney 2y ago

Good article. I currently use Litestream (https://litestream.io/ https://litestream.io/) for efficient SQLite backups to S3, and I'm happy with that. For me, the main point to consider wrt SQLite is if you need high availability or not. And how easy it is to setup litestream and persistent storage.

bambax 2y ago

This is all true. Like most people here I'm deeply, deeply in love with SQLite, but using it on the server is not worth it, exactly for the reasons listed in the post. To paraphrase: a big part of the appeal of SQLite is that there's nothing to set up. But if one wants to use it in production then that advantage is gone.

vidarh 2y ago
SQlite on the server is worth it when the server side is an application and you treat SQlite as a per user datastore for an application that is mostly per user, or where shared data across users can be treated as a queuing/async messaging problem. Decomposing a system to treat it as a per-user application working on per-user data works for a whole lot of problems and solves a whole lot of problems. It scales effortlessly. I've done that kind of system even with flat files, handling millions of user accounts on a set of servers with an aggregate capacity less than my current $300 mini PC.
- egeozcan 2y ago
  > where shared data across users can be treated as a queuing/async messaging problem Really asking: Why would you want that complexity when a single database that provides ACID with multiple users can provide that out of the box? Sqlite shines when it's on the edge (be it a fat desktop app, or a thin-client which can hold a cache, or any other modern definition of edge), and while I absolutely adore the tech[0], I don't get why people try to repurpose it to simplify server architecture, especially in this day and age even the most database with the most complex installation is one docker pull away. [0]: I honestly think Sqlite is one of the best software ever developed.
  - vidarh 2y ago
    You're assuming complexity, whereas my experience is that it reduces complexity. E.g. you get infinite scalability. You get no need to ensure all your queries are scoped correctly, because they can't directly access other data. Most large systems ends up with async processing and/or eventually sharding anyway, and then you get to restructure around those flows anyway, while still having the complexities of semi-centralised databases. The world is async - once you embrace async designs from the start and design your UX around accepting that there are very situation you need global serialisation of events, it gets a lot simpler. > Sqlite shines when it's on the edge (be it a fat desktop app, or a thin-client which can hold a cache, or any other modern definition of edge), Which is not materially different from a server spinning up to serve requests for a single user. > I don't get why people try to repurpose it to simplify server architecture, especially in this day and age even the most database with the most complex installation is one docker pull away. Because maintaining a large database cluster and scaling it to large user sets, and ensure the application software is using it correctly to avoid problems is a lot of work. I built my first datastore-per-user system in '99, without the luxury of SQlite. It was a revelation. It was a fundamentally so much more pleasant way to work. In some cases, datastore-per-user is the right pattern. In other cases datastore-per-tenant. In some cases, a big RDBMS is still the better option. But don't dismiss the former two without actually exploring it. Over the years, I'm finding it fits more and more places, especially thanks to SQlite.

leosanchez 2y ago

gobackup[0] and sqlite_rsync are good tools to backup sqlite databases. [0]: https://github.com/gobackup/gobackup https://github.com/gobackup/gobackup

tzury 2y ago

I am currently experimenting with multi read/single write via async FastAPI endpoints and my benchmarks tells it will scale well enough for the first 6 month of anticipated growth, while major part of the time will be beta testing users. In any case, we do things that scale at start by principle.

tzury 2y ago
To my surprise, it is performing better than a ZMQ based solution I begun with (in terms of concurrent connections).

deleted 2y ago

[deleted]

deleted 2y ago

[deleted]

andrenotgiant 2y ago

Something I've always wondered... How do downstream DB integrations like Fivetran or other ELT services play with SQLite? It seems like that's another consideration for running it in production at a real company.

memhole 2y ago
I don’t think most touch it. As much as I’ve always thought embedded dbs have a place in data analytics and warehousing, I think the issue is that it’s really a sql api on top of a file system and it’s easiest to just copy the db with something like rclone and treat it like a file. Like others mentioned there’s also litestream for replication. Fivetran, I believe is still involved and committed to duckdb. Duckdb has a SQLite extension. So, it’s entirely possible they could add SQLite support. I would say the issue then is familiarity and demand within the industry. https://www.fivetran.com/blog/how-fast-is-duckdb-really https://www.fivetran.com/blog/how-fast-is-duckdb-really They also have a talk given at duckcon that goes over the architecture.

feverzsj 2y ago

There are also rqlite and dqlite. SQlite is good enough for small to middle projects, which are the vast majority.

IgorPartola 2y ago
And of course there is https://en.m.wikipedia.org/wiki/Berkeley_DB https://en.m.wikipedia.org/wiki/Berkeley_DB
- ape4 2y ago
  from that page: A program accessing the database is free to decide how the data is to be stored in a record. Berkeley DB puts no constraints on the record's data. The record and its key can both be up to four gigabytes long. Berkeley DB supports database features such as ACID transactions, fine-grained locking, hot backups and replication.

Ciantic 2y ago

One alternative is to use pglite [1] which is Postgres compiled to WASM. It can act as a sqlite replacement during development, then when you are ready to move to a server model your SQL is already in Postgres format. [1]: https://pglite.dev/ https://pglite.dev/

OutOfHere 2y ago
Isn't its use limited to Typescript?
- Kinrany 2y ago
  Or anything that can embed a WASM runtime I imagine. They're considering a native build as well, search term "libpglite". Though I feel like providing it as a library would work very well initially. Roll a suitable minimal WASM runtime and a WASM blob into a single package, separately for every language.
euroderf 2y ago
In the WASM Component Model, there's a Postgres example available where the database runs in Docker. I'm hoping they whip up a version that uses SQLite instead, but with a similar WIT interface, and drop the Docker nonsense.

andrewstuart 2y ago

SQLite is great for databases that don’t need management and won’t change their structure and don’t need remote access.

VWWHFSfQ 2y ago
what databases are those

pimbrouwers 2y ago

Be brave. Just try it. Removing the network latency is a massive gain, especially for small/medium apps, which is most sites. And if you find yourself asking this, the answer is yes, it will work. SQLite will force you to rethink your architecture a bit, don't forget it doesn't HAVE to be _one_ file. This alone helps you scale further. There are some chafing points: 1. Schema migrations 2. Configuration, PRAGMA's applied at design time or during each connection

bambax 2y ago
It kind of does have to be one file, because foreign keys can't be enforced across files. For a simple example, if you have one db file per user, as others recommend, how do you check that a username or an email is unique? Do you maintain a "global" db for fields with unique constraints? But then when those fields are updated you need to manage this at the application level, and atomicity can't really be guaranteed... Or is there another way?
- Skinney 2y ago
  Keep user information in one database, keep user _data_ in another. It all depends on your app, really. For some apps the above won't make sense, but maybe it would make sense to keep one db per org, or something like that.
  - gnulinux 2y ago
    You can also use a different but similar tool e.g. use sqlite to keep user information, use duckdb to keep user data. I find this combo very effective since sqlite is best as fast btree based indexed operations and duckdb is best at multiple row based aggregate computations (get me user X vs get me all users with property Y).
- lolinder 2y ago
  For the one DB file per user question, I'll take a stab at a solution: * Global store of user identifiers mapped to user IDs. Identifiers can change but the underlying user ID is immutable. * One DB per user ID. The per-user DB doesn't really need to know the user identifier, and at auth time you don't need to access the business logic, so cross-DB updates should not be a problem. In the general case, similar logic applies: you can't partition just anywhere, but where you can partition is pretty well defined.

gizmo 2y ago

There are exceptions to every rule but most of the time you're way better of when you use simple and reliable technology. If you need to copy something between servers you probably don't need anything more complicated than rsync. For blob storage you probably don't anything more complex than S3. For database backups do you really need multi-region streaming backups or does mysqldump suffice? Every product has limitations. Well designed and simple products/tools have limitations that are easy to understand and easy to work around. The more complicated your architecture gets the more devilishly complicated the edge cases become. That's where you get punished for choosing complexity. MySQL and Postgres are larger than Sqlite by more than an order of magnitude. What do you get in exchange for all those lines of code? Do you actually need the extra functionality? If you do, great. If you don't, choose the simplest solution that works. When you use simple tools changing your mind later is cheap.

christophilus 2y ago
Proper concurrency, and the ability to scale out your applications across multiple servers / nodes. I’ve never worked on any application that had only a single web server, and yes, most of my applications have had to scale out a few times under load— even the smaller ones. Doing this with Postgres is trivial. Doing this with SQLite requires engineering. I’m not scared of engineering. I just don’t think it’s warranted when you have such an obvious, tried and true off the shelf database like Postgres available to you.
- deleted 2y ago
  [deleted]
- vidarh 2y ago
  Scaling with SQLite and a datastore-per-user model is trivial. If you build your app as if it's using Postgres when you're using SQLite, then, yes, you'll suffer. If you pick SQLite, you should pick it because you will actually build your application in the way that makes most sense with SQLite. Datastore-per-user makes sharding/scaling trivial, and tends to drive an architecture that is easier to reason about. You're never going to e.g. accidentally leak data between users because of a broken query, for example.

Cthulhu_ 2y ago

I've used SQLite in a project once, client-server application configuration management interface; I picked SQLite because the application I was replacing also used it, and because it was standalone; the application was run on-premise, and one of the issues was that the version of PHP it ran on was horribly outdated (plus it was PHP, so it needed some setup work). My replacement was intended to be fully standalone, no external dependencies, and so also no requirement to have a database server running somewhere. I would've preferred something like postgres but embedding that and running it with redundancy is nontrivial compared to sqlite. Mind you I never did get as far as running sqlite redundantly (using e.g. rqlite), I quit after 2.5 years since the project was bigger than any project I've ever done, and those were fully staffed teams.

nasretdinov 2y ago

IMO it's always going to be a problem if you rely on hype instead of common sense. All the hype around SQLite is because you often don't _really_ need to have high availability, online schema changes, etc, and 99% of the web sites can easily fit on a single (large) server. It's also worth mentioning that it doesn't meab that if you're not using SQLite you automatically get backups, replication, high availability, etc. There are few (if any) production-ready databases that are even capable of high availability, and those who are typically do this at a cost of catastrophically low write performance, especially compared to SQLite WAL mode.

VWWHFSfQ 2y ago

SQLite is great for what it is and its stated purpose, but it's a fundamentally poor database if you actually care about your data. I'm not talking about durability. It's perfectly durable, as in it won't lose your data (well, it might). I'm talking about the lack of actual data types and "everything is a string". I'm talking about the general flimsiness. Hipp even said that is a non-goal because it will bloat the embedded object size in your binary. Because that's what it's for.... Insert a string into an int column? Go ahead, it doesn't care. Use a date function on a text value it doesn't recognize as a date? Go ahead, you'll just get a NULL instead of any indication that your value is wrong for what you think it should be. It's fine for the address book on your phone, or the bookmarks in your browser. But terrible for server things that are important.

boristsr 2y ago
That's not quite true. There is support for "strict" tables which does have more stringent rules around types https://sqlite.org/stricttables.html https://sqlite.org/stricttables.html
- VWWHFSfQ 2y ago
  Mongo DB also disables safety features by default to improve their benchmarks. But Mongo DB gets lots of criticism for that and SQLite gets none.
richbell 2y ago
I believe foreign key constraint validations are also disables by default.

srameshc 2y ago

Ever since I saw Cloudflare D1, I have been obsessed by distributed SQLite. Primary reason is the cost of managing and owning it is relatively low, but then it was still not easy managing Rqlite. I wanted managed and I recently found Turso and it is amazing, but it's hard to understand the reads and writes with such databases. I knew about Fly.io investment in this area but I did not know about the offering Litefs. The author made some good arguments and probably it is wise to listen about the challenges and think twice before making it a default database out of sheer excitement and cost.

summarity 2y ago
https://litesync.io/en/ https://litesync.io/en/ is a mature way of distributing SQLite with arbitrary topologies and no modifications to client code. Supports multi primary write and DDL replication. It’s a paid solution, but that follows the model of all commercial SQLite mods (one time license fee). I use it, though just for a simple case of having a live replica of a production server running SQLite so I can try out migrations and generally work in an isolated copy.

mythz 2y ago

I've started using SQLite for all new Apps a couple of years ago and haven't looked back. It was the catalyst for us to get off AWS (and RDS) where we now deploy (.NET) Docker Apps with Kamal to a shared Hetzner Linux VM which uses Litestream for replication to Cloudflare R2. We've adopted several ways to workaround SQLite's single concurrent writer [1] including: splitting system functionality in multiple databases, separate locks per db, performing writes within a single worker thread for each db, etc. Despite being significantly cheaper, its next to zero latency actually performs better than our Apps using RDS. We also benefit from monthly rolling databases for request logs and db jobs and make use of SQLite's Application defined SQL Functions (i.e. calling C# functions in SQL queries) for some features which isn't really possible with other networked RDBMs. [1] https://servicestack.net/posts/scalable-sqlite https://servicestack.net/posts/scalable-sqlite

bob1029 2y ago

This is something that I am a huge fan of, particularly from a latency & performance perspective. NVMe + SQLite is a hell of a package deal. If you just need to go as fast as possible no matter what, this is a great place to start. At the highest end the hosted solutions can outpace SQLite but it's like that meme [0]. That said, I'm working on a new contract/project that is somewhat antagonistic to the idea of using SQLite on the server. The biggest reasons are due to employees needing direct (transactional) access to the live data and the ability to do online schema changes. There are ways to force this with additional tools, but I'm finding that just paying for MSSQL provides a lot of uplift on this particular problem. The possibility that other employees already have experience with tools like SSMS should not be overlooked when making technology decisions. People are generally the biggest bottleneck in any business. [0] https://knowyourmeme.com/memes/look-what-they-need-to-mimic-a-fraction-of-our-power https://knowyourmeme.com/memes/look-what-they-need-to-mimic-...

jmull 2y ago

The "Whys" aren't strong. #1 makes the point that once you use it in production you start needing production things, like backups and horizontal scaling, and those can get complicated, but for non-sqlite dbs you can use a managed solution to handle the complications for you. But (1) managed solutions for sqlite exist too, and, (2) anyway, successful projects soon out-grow the limitations of managed solutions, so that's a limited runway -- probably about the the same runways where unmanaged sqlite remains simple. #2 points out "Migrations are not great in SQLite." Migrations aren't great in any db. I think easy migrations can be easier in some other dbs, but those weren't the problem. Tough migrations are essentially rooted to evolving understanding of business rules, which no dbms can make "great" to do. #3 points out that decoupling storage from compute is a good idea. But with traditional client/server dbs, compute is soldered directly to storage, so that's a big win for sqlite. I know they aren't thinking about the db server server as compute, but they certainly should be -- it's compute in all the ways that hurt you and help you. #4 points out that migrating away from sqlite is extra work. But that's not really an independent argument for not choosing sqlite since it presupposes sqlite was the wrong choice in the first place. I have nothing against, say, postgres. But while sqlite on the server has some real drawbacks, it has some really nice properties too. Its simplicity and flexibility make it a strong default choice these days. I would be wary of the current managed providers -- their edge bias tends to lead you to system designs that aren't great.

om8 2y ago

You can’t build fault tolerant system without network lag. Your hardware can fail you any second, and every backup will be inconsistent with your local db. To solve it, you need to have a system with proper control plane and consensus algorithm like paxos inside. SQLite is great for simple projects that can lose data, but, if you need to have no downtime, you need a distributed db.

vidarh 2y ago
In practice, there are very few systems that need "no downtime", and even fewer that achieve it, even with a distributed db.

p0w3n3d 2y ago

I don't know why, but much more appealing to me is PostgreSQL on the server.

deleted 2y ago

[deleted]

Moto7451 2y ago

I once built an app for compliance reasons that needed a little bit of dynamic data and quarterly updates that allowed us to stay in business in a few of our minor markets (worth cool tens of millions) and a SQLite DB with some basic turn key CRUD admin tools was fantastic. The data was larger than a simple YAML/TOML file would be tolerable to live with and needed some love and attention by lawyers. I could update/let them update the DB in dev and check in the DB file (it was smaller than our very unreasonably large TOML file we used for app config) and had the same app in every environment without managing MySQL or Postgres migrations. The whole thing happily ran on three EC2 micro instances and given the relatively low traffic (low thousands of visitors per month) it was a great set of technical decisions. For that narrow use case at least, I am a huge believer in doing this. Another prod use case for SQLite I’ve implemented was in taking a market specific search engine component (eventually replaced with Elasticsearch after a few years of R&D, but I digress) where I needed to implement sharding, reordering of results, and a few extensions to the engine that nominally relied on INI files for the limited extensibility it did support. The engine itself lacked any network coordination so I scheduled things through updated files lobbed into S3 and let a scheduled job pull them down to each machine. If a specific shard was to be out of service that data was in SQLite along with what operations to run. There are a lot of reasons for why this was the case and happens a bit over a decade ago so there are much better ways of doing this now. RQLite didn’t exist yet for example and there was a very dumb license restriction keeping MySQL and Postgres off the table (kill me). Simply syncing SQLite files across S3 sufficed since the engine itself had a more severe write lock on indexing than SQLite did. I could just swap the files while I waited for that operation to complete. Since I could actually change how SQLite worked, some full text search operations moved to SQLite since the licensed product wasn’t good at that as it was marketed/intended to be used for its faceted search and proprietary ontology. Overall SQLite was the nicest part of that experience as well. Well at least until we moved to Elasticsearch and I no longer had insane license terms I had to code around.