8 ms·

> There is a popular opinion among developers that SQLite is not suitable for the web, because it doesn’t support concurrent access. No, the issue is it doesn'

• nalgeon 6y ago
  I think that the absence of 'high availability' is not an issue for small websites or web apps. Transactions are ACID, concurrent readers are fully supported. Backups and administrative tasks are super-easy.
  • voxic11 6y ago
    Also if you really need HA there are nice projects like https://dqlite.io/ https://dqlite.io/
    • SahAssar 6y ago
      Looking at https://dqlite.io/docs/protocol https://dqlite.io/docs/protocol it seems like it does not allow for embedded usage though. Doesn't this turn it into more of a traditional client-server RDBMS?
      • yawaramin 6y ago
        From the home page: > Dqlite is a fast, embedded, persistent SQL database with Raft consensus that is perfect for fault-tolerant IoT and Edge devices.
      • rstat1 6y ago
        Looking at the archtecture page [0] on that same website the second heading is "Embedding". [0] https://dqlite.io/docs/architecture https://dqlite.io/docs/architecture
        • SahAssar 6y ago
          Ah, right, I should have looked deeper!
  • gwd 6y ago
    I just looked, and the entire database for my webapp (which typically serves around ~50 people) is 139k. As you say, you can do a data dump quickly and safely; at that size, you could afford to just dump the entire DB every hour and keep the last year's worth of snapshots if you wanted.
    • jakoblorz 6y ago
      https://litestream.io/ https://litestream.io/ That's exactly what litestream accomplishes.
      • benbjohnson 6y ago
        That's close. Litestream takes a snapshot of the database and then continuously replicates out WAL frames. On restore, it grabs the snapshot and replays all those WAL frames (or all the frames up to a certain time if you want point-in-time recovery).
  • zokier 6y ago
    I see availability as orthogonal issue to scale/size. You might be processing one transaction per day but it can still be super important that the service is available for that one transaction.
  • tmountain 6y ago
    How do backups work? Is there a locking mechanism to prevent file corruption if the file is copied during writing?
    • sangnoir 6y ago
      It supports multiple readers, and writes are atomic - no reader should ever get corrupt data just because there is a write happening in parallel. You probably should not use the OS to copy the live db file, rather, have an in-process thread that reads the db and writes to a backup location periodically.
      • mrweasel 6y ago
        Exactly, many forget the little detail that they should use the backup feature, rather than just copy the database file.
    • nijave 6y ago
      You can also combine file system snapshots to get versioning which would allow you to pull the db file out of the latest snapshot (right after taking it) and send it somewhere
  • crazygringo 6y ago
    So if you're saying everything does support a web database... then what's the reason people aren't using it for websites? Why do you say it works for "small" websites but presumably not large ones? If it's not transactions, concurrent reading, backups, or administrative tasks... then what's the issue you run into? Genuinely curious... I'm wondering if everything I've heard about "don't use SQLite for websites" is wrong, or when it's right?
    • emodendroket 6y ago
      If writes are infrequent I think it might be fine. Otherwise I’d be wary.
    • dbattaglia 6y ago
      I think one valid fear is that a web application might, for multiple reasons, scale beyond a single process on a single server. This is common enough that using an embedded database can be problematic, compared to a separate RDBMS process that can be shared between processes. Some web applications are just never going to scale out for any reason, and for those SQLite might be appropriate.
      • danenania 6y ago
        Agreed, but it’s not only a question of scaling. Any app with high uptime requirements will need more than a single process from day 1.
        • benbjohnson 6y ago
          It depends on what you consider high uptime. You can achieve 99.95% uptime with 4h of downtime a year. A lot of downtime occurs because of overly complicated systems so running a single process on a single server can give you relatively high uptime.
          • TheCoelacanth 6y ago
            I just want to be able to deploy updates during business hours without downtime. Hard to do with a single process.
  • mikesabbagh 6y ago
    I ran it once on an NFS disk. There must be some file locking happening I think. But I could read from multiple nodes, but for some reason the delete was failing (could not delete a database). Did not test the write as my app at that time was writing from one location only. Anyone tested such HA scenario?
• hexmiles 6y ago
  > "No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc." I think it has concurrent backups via the backup api: https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html
  • formerly_proven 6y ago
    I'd expect this to be unnecessary in WAL mode (which you should use, when possible), since WAL allows concurrent readers while permitting up to one writer. In the old undo-log mode (which remains the default for compatibility) writing excluded readers.
    • nayuki 6y ago
      I don't think you can do naive file copy backups even in WAL mode. WAL will checkpoint the log into the main database file from time to time. You need to use the SQLite backup API to let you make a backup while blocking any potential checkpointing.
      • formerly_proven 6y ago
        Sorry, I was unclear. I meant the "copy a few pages then sleep for some time to release the database lock" part. With WAL mode you should just be able to copy as fast as you can without disturbing other readers or the writer. SQLite also has a similar SQL built in, VACUUM INTO. That's nice because the backup API just copies all pages (iirc).
• benbjohnson 6y ago
  I agree that high availability features are outside of the goals of an embedded database. There's an ecosystem of tools SQLite to provide these benefits though. There's dqlite & rqlite for providing HA over SQLite. I'm the author of Litestream[1] which provides streaming replication to S3 for SQLite databases so folks can safely run a single node instance. [1]: https://litestream.io/ https://litestream.io/
  • hinkley 6y ago
    One could, for instance, run “static” sites with the data stored in a SQLite database. Update on write apps often can do okay with 90% uptime. It’s the “generate everything in every request” crowd that needs an HA solution and why do we keep doing this to ourselves?
    • benbjohnson 6y ago
      Yes, static generation is a great way to go if that works for your use case. Throw it up on a CDN and you'll likely have five 9s of uptime. I don't think that's a viable solution for a lot of applications though.
      • hinkley 6y ago
        Half of arguments on the Internet are someone trying to win an argument they lost elsewhere. If I could get some people I work with to actually run the numbers on how much it costs us to make everything dynamic, versus how much we earn versus static, I think we’d quickly arrive at an agreement that most of it should be static with a little AJAX thrown in for the 10% that needs to be special.
• wenc 6y ago
  It also doesn't have strong/static typing (it's dynamically typed) so you have to typecheck your inputs or do type coercion upon read. And it doesn't have a native date type. Date handling has to be handled at the application layer. It can be tricky to do massive time-series calculations or date-based aggregations. You can use integers or text types to represent dates, but this open-endedness means you can't share your db because everyone implements their own datetime representation.
  • names_are_hard 6y ago
    This. I'm working on a hobby project that is essentially a web app for personal use. I started with sqlite because it was the simplest to start with, but I'm about to reluctantly migrate to postgres. Why? Constraints, data types, and programmability. In terms of scale, sqlite is just fine. But I am tired of fiddling with the dates, it's too easy for bugs to sneak into my code, and I want to use table valued functions to essentially parameterize views instead of having to build complex queries in the app layer. If your web app is mostly reading and writing single rows, yeah, sqlite is just fine. But if there's substantial and complex logic involved, it has its limits.
  • samatman 6y ago
    Datetimes really are the achilles heel of SQLite. The JSON extension library is amazing and works well. If SQLite were to grow a first-rate RFC 3339 library, one which could read from tz when available and do the things which strftime cant, acting on your choice of Unix timestamp and valid RFC 3339 date string, this would be a real boon to the ecosystem. I haven't found typechecking inputs to be a real barrier. Sure, `val INTEGER CHECK (val = 0 or val = 1)` is a long way to spell `val BOOLEAN` but `CHECK json(metadata)` is a reasonable way to spell `metadata JSON`, and a similar function would surely exist for a SQLite datetime extension. You can do it now by coercing the string through an expected format, but that doesn't generalize well.
    • dragonwriter 6y ago
      > If SQLite were to grow a first-rate RFC 3339 library, one which could read from tz when available and do the things which strftime cant, acting on your choice of Unix timestamp and valid RFC 3339 date string, this would be a real boon to the ecosystem. OTOH, as an embedded database with freeform advisory typing and easy extensibility (for functions, etc.) in most host languages, it's not hard at all to get whatever you need for datetimes if you are using a host language that has a decent datetime library itself (and, as a bonus, you then don't have to worry about subtle differences between manipulations of datetimes through SQL and manipulations through other mechanisms in the app.)
• habibur 6y ago
  Not to misunderstand. It fully supports concurrent reads. And also concurrent reading while some other process is writing the same database. What it doesn't support is concurrent writes only.
• TheRealPomax 6y ago
  Not sure I understand: sqlite is file based, so snapshots and concurrent backups are literally just file copies/backups. I'd much rather SQLite not waste its time on implementing features they're not good at, leaving that to the tools we already have available for rolling file backups, instead spending their time and effort on offering the best file-based database system they can. (Heck, even failover is just a file copy initiated "when your health check sees there's a problem")
  • simonw 6y ago
    Unfortunately backing up with a simply file copy operation isn't guaranteed to work if you have write traffic at the same time. Instead you need to use the .backup mechanism or the VACCUM INTO command, both of which safely create a backup copy of your database in another file - which you can then move anywhere you like.
    • majkinetor 6y ago
      Depends on the file system. On Windows you can use shadow copy for example.
    • mceachen 6y ago
      Oh man, I didn't know about VACCUM INTO, thanks!
    • TheRealPomax 6y ago
      Not really, it still works exactly as you should expect: writes are handled atomically, using a separate file, so if you copy the database file "mid-write" you're not actually copying a database in flux, you're copying the database before the write operation gets applied, and SQLite applies the write after the copy lock is gone. The main issue here is time: because copying temporarily locks the db out of further changes, it will be "out of sync" if you naively believe that performing a write means your next call will see that data, and you bake that assumption into your code. While there's a wait, on modern hardware using SQLite for its intended purposes (any volume of reads, but low volume of writes), that's just not an issue. There are other problems associated with file-based backup, of course, such as missing out on in-memory data, or corruptions caused by power outages, but those problems only exist if we were to copy a db once. Backups run regularly, and any data we miss out on the current pass, we'll get on the next pass. Basically: when used for the purpose that SQLite was created for, file copies are a perfectly fine backup strategy that really only shows its limitations when you start to push your project into "SQLite isn't really appropriate here anymore" territory. (having said that: the fact that VACUUM can be run into a new file is super nice, and everyone should know that it exists)
• jes5199 6y ago
  no, those things have never stopped me. What has stopped me is that the default configuration for a Rails app throws intermittent exceptions when I'm trying to save, because concurrent writes fail and Rails (at least as of a couple years ago) does the wrong thing by default
• smulc 6y ago
  Check out rqlite (https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite) for that functionality - "rqlite is a lightweight, distributed relational database, which uses SQLite as its storage engine. Forming a cluster is very straightforward, it gracefully handles leader elections, and tolerates failures of machines, including the leader. rqlite is available for Linux, macOS, and Microsoft Windows."
  • emodendroket 6y ago
    To me this is obviating a lot of the advantage of SQLite over any other RDBMS.
    • dmurray 6y ago
      I thought the same, but maybe it's useful if you start with SQLite and decide to scale up to a distributed RDBMS without having to rewrite too much?
      • msla 6y ago
        Two thoughts: SQLAlchemy, at least, lets you switch from SQLite to Postgres in a configuration file. Isn't there a useful SQL subset which allows you to switch from one database to another without rewriting? There seems to be such a subset for C, for example, which multiple compilers all interpret the same way, and SQL is a standardized language, too.
        • dmurray 6y ago
          Yes, both of those are alternative solutions. Using a wrapper like SQLAlchemy is probably an improvement for most medium and large projects, but it has costs of its own. Using portable ANSI SQL - above the Hello World level - is something basically nobody does unless they make a serious effort, lean on linting tools and forego some of the most useful features of their DBMS. sh is perhaps a closer comparison than C here: it's at least possible to write portable C accidentally. And neither of these help you if you started with a SQLite database - an excellent choice for most - and decide that you need to support a dozen or a hundred concurrent users.
• kroggen 6y ago
  For replication of SQLite there are some options: http://litereplica.io http://litereplica.io - single-master replication http://litesync.io http://litesync.io - multi-master replication https://aergolite.aergo.io https://aergolite.aergo.io - highest security replication, using a small footprint blockchain
  • pbowyer 6y ago
    Have you used litereplica or litesync? It doesn't inspire confidence that their websites haven't been updated since 2016 and 2017 respectively.
    • kroggen 6y ago
      Most things happen in the forums: http://litesync.io/forum/ http://litesync.io/forum/ http://litereplica.io/forum/ http://litereplica.io/forum/
• jyrkesh 6y ago
  I learned this the hard way when I stuck some app containers down on a few RPis and mapped the folder of stateful stuff (including a SQLite database) to an NFS share on my NAS. It's....not great.
  • pdimitar 6y ago
    Can't remember where did I read this now but about a year ago I have found material saying that sqlite3 over NFS is a very bad idea and even though it's supported it's also strongly advised against as a use-case.
  • bionoid 6y ago
    The FAQ suggests that NFS is problematic for concurrent access: > But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. https://www.sqlite.org/faq.html#q5 https://www.sqlite.org/faq.html#q5
    • jyrkesh 6y ago
      Oh yeah, I read all that after I started seeing problems, I know I'm in the wrong here. It works just good enough that I haven't addressed it yet (I just kick over the process when things get weird, and the db usually recovers just fine).
• nijave 6y ago
  I think the biggest issue can be distilled to "lack of concurrent network access". You start getting into a lot hairy management problems with distributed file systems/copying files around. Imo part of that is just web applications tend to be not very efficient when compared to something like unix cli utilities so to get performance you end up with potentially massive amounts of horizontal scaling On the other hand, the lack of performance /usually/ buys you higher productivity so you can make product changes faster (you let a GC manage the memory to save time coding but introduce GC overhead)
  • therealdrag0 6y ago
    Right, and Expensify plausibly solved this by wrapping SQLite to produce a different DB, BedrockDb. But that shouldn't imply at all that SQLite alone could satisfy main stream web applications.
  • lmm 6y ago
    > Imo part of that is just web applications tend to be not very efficient when compared to something like unix cli utilities so to get performance you end up with potentially massive amounts of horizontal scaling It's less about performance than about resilience IMO. Of course if you don't have a proper HA datastore (master-master) then you kind of undermine that, but a datastore SPOF is better than the whole application being a SPOF.
• gm3dmo 6y ago
  >Once you have multiple clients being developed and running concurrently, and you have production data (customer accounts that are effectively legal documents that must be preserved at all times) you want that DBMS to be an independent component. It's not principally about the concurrent performance, rather it's the administrative tasks. For my reporting/read-only use case https://datasette.io/ https://datasette.io/ solves the above beautifully.
• jksmith 6y ago
  From the dbadmin POV and understandable. From the dev side, if you want to take a stab at your own failover, shards, concurrents and snaps, there may be no better platform to learn on. Writing a custom backend to do tricks with sqlite dlls is extremely satisfying.
• bravura 6y ago
  A kludge---I forget from where---for low traffic sites is one SQLite DB per user.
• justsomeuser 6y ago
  SQLite itself does not have those features, but at the disk level you can get those features: On GCP: - A regionally replicated disk will replicate writes synchronously to another zone (another data centre 100km away). This means when the SQLite write call returns, the data will be geographically replicated, and the second disk can be used as a fail over. This is all transparent to app/db. - Disk snapshots are incremental, and are stored in Cloud Storage, which is geo replicated across regions (E.g. Europe and US). In a way, this gets you even better failover and backups than traditional server DBMS's that have these features built in, and often need custom administration to ensure they are working.
• indymike 6y ago
  >No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.) Actually, I think it's network access that is the issue. SQLite isn't really designed to have 4-5 network connected apps using it as a shared data store. In most shared hosting setups, database is treated as a network service, so you can have 200 different sites leveraging the same db server... and sometimes the db server isn't on the same hardware as the http server.