13 ms·
Cloud Backed SQLite
- schemescape 3y agoEdit: I misread the date, as pointed out in a reply. Why does the forum say some messages are from 19 years ago? https://sqlite.org/cloudsqlite/forum https://sqlite.org/cloudsqlite/forum
- benatkin 3y agoBecause they really are years old, and not days old. Except it's 1.19 years ago. And the oldest one is from 2020. https://sqlite.org/cloudsqlite/forumpost/da9d84ff6e https://sqlite.org/cloudsqlite/forumpost/da9d84ff6e It might be announced recently, but it has been under development for a while.
- deleted 3y ago[deleted]
- fbdab103 3y agoIt is not clear to me - is this an officially supported, core module? Or more, "You can technically do this, but ehhhh" kind of deal? Would this mean I might eventually be able to do something insane like run Datasette (from within Pyodide) against an external cloud storage?
- deleted 3y ago[deleted]
- benatkin 3y agoIt seems like they're taking a bottom-up approach. I can't see any link to it on the main site. It would probably first bubble up to the News page, like WebAssembly did on 2022-11-16. https://sqlite.org/news.html https://sqlite.org/news.html WebAssembly also has a URL inside the root that redirects to doc/trunk/www/index.wiki: https://sqlite.org/wasm/ https://sqlite.org/wasm/
- captn3m0 3y agoI don't think the blob storage APIs (which this seems to be using, instead of accessing over the web-gateway urls) are CORS accessible. So you might have to proxy it. However, it might become possible to run datasette etc much more easily in an edge function.
- antonvs 3y agoBoth Google and AWS storage, at least, are CORS accessible: https://docs.aws.amazon.com/AmazonS3/latest/userguide/enabling-cors-examples.html https://docs.aws.amazon.com/AmazonS3/latest/userguide/enabli... https://cloud.google.com/storage/docs/using-cors https://cloud.google.com/storage/docs/using-cors
- rickette 3y agoIt looks really promising, would also love to know the status of this. Can't find any announcements of this yet.
- 0xbadcafebee 3y agoThis adds more cache consistency issues, concurrency issues, network blocking issues, authn+z issues, daemon process supervision issues, ... for what? To store your SQL data in a remote data store? I would rather my app shell out to gsutil (or curl!) than deal with all of this. Simple hack: mount a tmpfs filesystem and write your sqlite database there. Every 30 seconds, stop writing, make a copy of the old database to a new file, start writing to the new file, fork a process to copy the database to the object store and delete the old database file when it's done. Add a routine during every 30 second check to look for stale files/forked processes. Why use that "crazy hack", versus the impeccably programmed Cloud Backed SQLite solution? - Easier to troubleshoot. The components involved are all loosely-coupled, well tested, highly stable, simple operations. Every step has a well known set of operations and failure modes that can be easily established by a relatively unskilled technician. - File contents are kept in memory, where copies are cheap and fast. - No daemon outside of the program to maintain - Simple global locking semantics for the file copy, independent of the application - Thread-safe - No network blocking of the application - Authentication is... well, whatever you want, but your application doesn't have to handle it, an external application can. - It's (mostly) independent of your application, requiring less custom coding, allowing you to focus more on your app and less on the bizarre semantics of directly dealing with writes to a networked block object store.
- vasco 3y ago> ... for what? Backups, replication to different regions, access to the data etc become standardized when it's the same as any other cloud bucket. This makes compliance easier and there's no need to roll your own solution. I also never had to troubleshoot sqlite, so I'd trust this will be more robust than what I'll come up with, so I don't get your troubleshooting argument. Not everyone will care about this, but those can just not use it I guess.
- beebmam 3y agoOr just use a database like Postgres. It's a one liner to download and start up a postgres container.
- 3y ago
- TekMol 3y agoI guess you could just proxy a normal SQLite DB? Should take only a few lines of PHP. Maybe just one: echo DB::select($_GET['query']); All that is needed is a VM in the cloud. A VM with 10 GB is just $4/month these days. And you can add a 100 GB volume for just $10/month.
- rizky05 3y ago[dead]
- electroly 3y agoSQLite's VFS layer is flexible and easy-to-use. There are lots of implementations (public and private) of this "use a SQLite database directly from cloud object storage" idea because it's pretty obvious how to do it when you look at the VFS functions that you have to implement. If you do a read-only implementation, it's an afternoon project. It's mostly a matter of following the VFS docs and when you're about halfway done implementing the functions, SQL queries start working instead of producing errors. The only tricky bit, as always, is caching.
- noman-land 3y agoI used this[0] awesome library to do just this over http. I posted more info in my other comment. I'm still exploring but so far it's been pretty impressive to me. [0] https://github.com/phiresky/sql.js-httpvfs https://github.com/phiresky/sql.js-httpvfs
- p-e-w 3y agoI don't understand what the goal is here. Wherever the cloud is, there is PostgreSQL, and conveniently, PostgreSQL already solves all of the problems that "Cloud SQLite" creates, with support for concurrent access, higher performance, richer queries, and lots more. Is this just a toy? What are the upsides in practice of deploying an embedded database to a cloud service?
- electroly 3y agoHosting files in S3 is much, much cheaper than running a live instance of PostgreSQL. Incomparably so. We do it specifically to move large, mostly cold, read-only data out of the main RDBMS and into S3 in a way that it can still be queried on-the-fly* without having to be fully downloaded and reconstituted on a server first. Works great and we get to pay S3 prices for storage and nothing for compute. As a kicker, because it's S3 we can access it from any availability zone without paying for cross-AZ data transfer. For the size and coldness of this data, the juice is worth the squeeze. None of the downsides matter for our use case at all. * Using indexes and only touching the pages you need. This is specifically much better than S3 Select, which we considered as an alternative.
- BoorishBears 3y agoIf you mentioned Athena maybe I could see how this follows what the earlier comment says, but as is your usecase doesn't really overlap with why people are building cloud sql products
- electroly 3y agoAm I not “people”? :) I built a SQLite VFS module just like the one linked here, and this is what I use it for in production. My use case obviously does not preclude other people’s use cases. It’s one of many. GP asked whether this is a toy and what the upsides might be. I answered both questions with an example of my production usage and what we get out of it.
- 3y ago
- rsync 3y agoI've been (proudly) noting these elegant one-liners for ... 18 years now: pg_dump -U postgres db | ssh user@rsync.net "dd of=db_dump" mysqldump -u mysql db | ssh user@rsync.net "dd of=db_dump" ... but what is the equivalent command for SQLite ? I see that there is a '.dump' command for use within the SQLite console but that wouldn't be suitable for pipelining ... is there not a standalone 'sqlitedump' binary ?
- comex 3y agoYou can pass console commands as arguments: sqlite3 /path/to/db.sqlite .dump
- deleted 3y ago[deleted]
- troyjfarrell 3y agoI believe that you can echo the '.dump' command to SQLite. echo .dump | sqlite3 db | ssh user@rsync.net "dd of=db_dump" It worked for me.
- dmw_ng 3y agoYou can also pass special commands in the second argument to SQLite: sqlite3 db .dump | ...
- rsync 3y agoThanks! I have added that one-liner to the remote commands page: https://www.rsync.net/resources/howto/remote_commands.html https://www.rsync.net/resources/howto/remote_commands.html ... although based on some of your siblings, I changed .dump to .backup ...
- JNRowe 3y agoSadly, that doesn't look like it will work. The '.backup' command needs a filename and won't accept '-' or even '/dev/stdout', at least in the version available on Debian it will simply error out with "missing FILENAME argument on .backup". As noted '.dump' will work in your pipeline, but with the caveats I and others noted elsewhere.
- vbezhenar 3y agoI always wondered by never bothered to implement: What if I would implement an S3-backed block storage. Like every 16MB chunk is stored in a separate object. And filesystem driver would download/upload chunks just like it does it with HDD. And then format this block device with Ext4 or use it in any other way (LVM, encrypted FS, RAID and so on). Is it usable concept?
- candiodari 3y agohttps://github.com/s3fs-fuse/s3fs-fuse/wiki/Fuse-Over-Amazon https://github.com/s3fs-fuse/s3fs-fuse/wiki/Fuse-Over-Amazon Seems like a usable concept yes.
- cbluth 3y agoYes, its called s3backer
- vbezhenar 3y agoThanks, that's the thing.
- damagednoob 3y agoNot sure if it's a usable concept but it sounds similar to what someone else did[1]: > ...I implemented a virtual file system that fetches chunks of the database with HTTP Range requests when SQLite tries to read from the filesystem... [1]: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/ https://phiresky.github.io/blog/2021/hosting-sqlite-database...
- kirubakaran 3y agohttps://github.com/s3fs-fuse/s3fs-fuse https://github.com/s3fs-fuse/s3fs-fuse
- vbezhenar 3y agoIt uses S3 as a filesystem. I'm talking about using S3 as a block device. Slightly different approach.
- noman-land 3y agoI've just been exploring serving large SQLite databases in chunks and querying them with http range requests to prevent downloading the entire database. It's pretty awesome! I found a really interesting library called sql.js-httpvfs[0] that does pretty much all the work. I chunked up my 350Mb sqlite db into 43 x 8Mb pieces with the included script and uploaded them with my static files to GitHub, which gets deployed via GitHub Pages.[1] It's in the very rough early stages but you can check it out here. https://transcript.fish https://transcript.fish I recommend going into the console and network tab to see it in action. It's impressively quick and I haven't even fine-tuned it at all yet. SQLite rules. [0] https://github.com/phiresky/sql.js-httpvfs https://github.com/phiresky/sql.js-httpvfs [1] https://github.com/noman-land/transcript.fish https://github.com/noman-land/transcript.fish
- dopamean 3y agoThe timing of this is incredible. I was recently given a 120gb SQLite db to work with and I was going to investigate ways to work with it in the morning.
- danielvaughn 3y agoI’m not sure what they offer on this front, but turso is a new edge-based SQLite platform. It might be worth checking out: https://turso.tech/ https://turso.tech/
- OJFord 3y agoThe current pricing is free for 8GB (10GB or 1B rows read /month) or $3k/month unlimited? So not really possible to start relying on it for anything serious yet (except really serious). The middle tier isn't even fully described yet, but does say 20GB limit (and from $29/month) - I suppose that means that's a base from which you'll be able to pay for more storage as needed.
- dopamean 3y agoThank's! I'll have a look at this as well.
- up2isomorphism 3y agoLinus had once said database people seldom have good taste, I have to agree with him in this case.
- anyoneamous 3y agoLots of people here talking about S3, and yet: > The system currently supports Azure Blob Storage and Google Cloud Storage. I'd interpret that as either a hard "fuck you" to AWS, or a sign that the S3 API is somehow more difficult to use for this purpose.
- deleted 3y ago[deleted]
- FooBarWidget 3y agoThe S3 API doesn't have the concurrency control primitives necessary to guarantee consistency in the face of concurrent writes. I wrote a distributed lock on Google Cloud Storage. https://www.joyfulbikeshedding.com/blog/2021-05-19-robust-distributed-locking-algorithm-based-on-google-cloud-storage.html https://www.joyfulbikeshedding.com/blog/2021-05-19-robust-di... During my research it was quickly evident that GCS has more concurrency control primitives than S3. Heck S3 didn't even guarantee strong read-after-write until recently.
- fs111 3y ago> until recently Almost 3 years https://aws.amazon.com/blogs/aws/amazon-s3-update-strong-read-after-write-consistency/ https://aws.amazon.com/blogs/aws/amazon-s3-update-strong-rea...
- janejeon 3y agoAh, I'm guessing that's the same reason why many "cloud storage as a backend" stuff (e.g. Terraform/Vault storage backends, Delta.io backends) require DynamodDB + S3
- electroly 3y agoWhile I'm sure you're right, this VFS implementation doesn't use any of that stuff. They just handwave concurrent writes away by saying the application has to ensure a single writer on its own. An S3 implementation appears to be planned; it's mentioned in the comments in the bcvmodule.c file.
- 3y ago
- pachico 3y agoI am very puzzled by this. Wasn't the very point of Sqlite to be local? If it's not anymore, why would you prefer to use it rather than any other relational database?
- moonchrome 3y agoHow is this even comparable to other relational databases ? In this setup you use something like blobstore for public shared db access. Looks like single writer at a time. In traditional databases you connect to a database server. This is like having a cloud sync for your local app state - shared between devices on a cloud device.
- dist-epoch 3y ago> Wasn't the very point of Sqlite to be local Not quite. The very point is not needing a server. Subtle difference. Think more data lake and less relational db.
- wg0 3y agoWhy would I use it over Litestream?
- jdthedisciple 3y agoHmm seems like a more complicated setup than a classic WebAPI-based architecture with PostgreSQL/MySQL, with no apparent benefit. Anyone care to prove me wrong?
- chasers 3y ago> although ensuring that only a single client is writing to a database at any time is (mostly) left to the application Sounds like trouble.
- bob1029 3y agoThe whole point of SQLite for me is to get the application code as close as possible to the block storage device without getting my hands too messy in the process. Latency is a major reason you'd consider using SQLite. Simplicity is right up there too. Putting the storage in the cloud is completely orthogonal to this ideology. Both latency & simplicity will suffer dramatically. I don't think I'd ever use this feature over something like Postgres, SQL Server or MySQL.
- mcculley 3y agoI have some workflows where a SQLite database is built from data sets and then used read-only by multiple processes. I currently implement this by putting the SQLite file into S3 and copying it to the machines that need it. This will save the copy step.
- codethief 3y ago> Putting the storage in the cloud is completely orthogonal to this ideology. Both latency & simplicity will suffer dramatically. I'm confused by your usage of the term "orthogonal". In my experience, it is used to express that something is independent of something else, similarly to how in mathematics orthogonal vectors are linearly independent and thus form a basis of their linear span. Here, however, it seems you mean to say that "putting storage in the cloud runs counter to this ideology"?
- caseyohara 3y agoI see "orthogonal" used quite frequently on HN. I remember reading a comment here that explained it something like: > Ironically, the meaning of "orthogonal" has become orthogonal to the real meaning because now it can mean both "perpendicular" and "parallel", both "at odds" and "unrelated" Edit: found it https://news.ycombinator.com/item?id=1997093 https://news.ycombinator.com/item?id=1997093 (my memory was pretty close)
- api 3y agoMake a plugin to put this in FoundationDB or TiKV. That'd be pretty interesting.