15 ms·
Serverless SQLite
- fumblebee 6y agoCan someone ELI5 as to the significance of this?
- justsomeuser 6y agoCDNs allow serving files closest to the user cutting out 100s of ms of latency. But they are limited to read only files. Cloud flare workers allow running simple programs at the CDN node closest to the user. SQLite is embeddable C code that is like a normal SQL server just without the network parts - just the sync functions embedded into your process. This wraps SQLite with the networking and CDN abilities of cloud flare, allowing you to http-get a subset of data dynamically using SQL (that would not be possible with the simple “download 100% of file X”).
- mxuribe 6y agoNow THAT makes sense! (And, i can now see the benefits of this approach.) Thanks for the explanation!
- peterkelly 6y agohttps://www.sqlite.org/serverless.html https://www.sqlite.org/serverless.html (first published 2007, before the more recent misuse of the term began)
- codetrotter 6y agoWords can have multiple meanings and meanings of words can change.
- oarsinsync 6y agoAlmost like how the English language has been literally hijacked and destroyed. Language does change and evolve. The misuse and subsequent additional meaning for “literally” to now also be a simile for “figuratively” is a good example of how this isn’t necessarily always a good thing.
- dkjaudyeqooe 6y agoDo you mean like "incredible" and the like? It's just language, you might disagree but usage determines correctness (eventually).
- edgyquant 6y agoThe changing and evolution of language is neither a good nor bad thing, it just is.
- young_unixer 6y agoDisagree. If a change diminishes the clarity, efficiency or even the beauty of the language for no good reason, then it's a bad change, at least for the purposes of communication.
- jagthebeetle 6y agoTo be fair, we wouldn't have "literally" without a metaphoric interpretation of "literalis," "of or relating to letters." I always find this particular example to be somewhat self-contradicting for this reason.
- powersnail 6y agoHow is "literally" used as a simile for "figuratively"? A simile would require a sentence in which "literally" is compared to "figuratively", wouldn't it? My impression, as a non-native speaker, is that the misused "literally" is to indicate exaggeration, not the quality of being figurative.
- anon9001 6y ago> the misused "literally" is to indicate exaggeration Used to be. Now "literally" literally means "figuratively". [1] [1] https://www.merriam-webster.com/dictionary/literally https://www.merriam-webster.com/dictionary/literally
- powersnail 6y ago
- monadic3 6y agoAll uses of "server" are bastardizations of the concept of a human who provides service.
- jiofih 6y agoThat’s kinda unrelated. The title here could have been “SQLite in serverless apps” or something to avoid the confusion.
- sbergot 6y agoI think I will always struggle to understand the popularity of the term serverless. From the two definitions found in https://www.sqlite.org/serverless.html https://www.sqlite.org/serverless.html classic serverless --> "embedded database" exists and is often used neo-serverless --> I suspect that this is a marketing term used to attract cool people to new cloud offerings (like "jamstack" for services such as netlify). There is no good replacement here because the term was tied to this new kind of infrastructure really early. But anytime I hear someone repeat "of course serverless does not mean there is no server!" I die a little more. Naming things is hard but at least we ought to try to come up with terms that are not blatantly misleading.
- ohnemint 6y agoI wouldn't be surprised if the term originated as a marketing tactic (e.g., AWS) to seed in people's heads the idea of not having to care about servers
- deepstack 6y agoYou hit the nail on the head. It is all about marketing for the new front-end developers who don't want to learn "server side" manipulation db, files, etc. Personally the whole indexdb, localstorage really break the web page as a stateless model. Why do you need save so much local data to just maintain the session? Stop putting everything in the web browser.
- ohnemint 6y agoI can see the value of serverless in general (not this hack necessarily) in small startups that don't have sysadmins on payroll but still want to rapidly deploy products with confidence that it will just work - all without worrying about infrastructure, scaling, etc. What I'm curious is if (1) serverless is cheaper than hiring competent sysadmins who can maintain the infrastructure instead and (2) are these savings worth being locked into a chaotic architecture and boring proprietary tooling that is forced on you for the profit of the Google, Microsoft, and Amazon monopolies? I've recently entered the job market and personally find no joy in working in serverless environments because of the latter.
- imtringued 6y agoServerless is an adjective so you can slap it everywhere. Worker-based or FaaS (function as a service) don't roll off the tongue that well. I'm not advocating for the term, just trying to explain why it is so popular.
- k__ 6y agoAlso, serverless solutions like DynamoDB or S3 aren't compute, so worker based and FaaS would simply be wrong.
- wongarsu 6y agoWould we lose any meaning by just calling DynamoDB and S3 managed solutions, like in the old times?
- mikesabbagh 6y agoThey probably call them serverless because you are mainly billed by the number of requests instead of the number of hours it runs for (although for Dynamo, there is a cost per table and r/w)
- zeckalpha 6y agoAnd https://en.m.wikipedia.org/wiki/Web_SQL_Database https://en.m.wikipedia.org/wiki/Web_SQL_Database which was truly serverless SQLite for a web browser.
- Dunedan 6y agoServerless SQLite … for read-only use cases
- jsf01 6y agoWhat would this be best used for?
- the_arun 6y agoWith this, suppose you want faster response than Disk, then every serverless function can have its own replica of data & power responses off of that. It is like a cache through sqlclient. But wait, can't we do that already running in memory SQLLite? why is it a big deal now?
- jamil7 6y agoNothing. Demoing novel wasm usage maybe?
- faizshah 6y agoI use something like this for a metadata search tool serving static data. Its high performance and easy to set up which is the main use case for me.
- flower-giraffe 6y agoCloudflare workers run at the edge along with a simple but fast KV store. We use KV and workers to serve static data (json) to browser based code. This could be used to filter, sort or aggregate that data before sending the client. Perhaps for inventory / product lookup on a e-commerce site.
- bouke 6y agoReinvent http proxies, but now with additional code and data that gets out-of-sync and is harder to debug.
- simonw 6y agoThis is great for any time you have a mostly read-only database (which is true for most publishing use-cases - anything you might consider a static site generator for) which you want to provide API access to with lightning-fast speed because replies will be served from a CDN edge.
- aeyes 6y ago
- tmikaeld 6y agoI've also experimented with this, unfortunately the 50ms CPU time isn't enough for datasets larger than 1.5MB. And the wasm init add at least 100ms to each request even when "hot" in cache. Also, out of a cost perspective, running a VM with SSD for cheap with SQlite will give much more requests than a CF worker for much less. Adding writes to this is also very limited due to the max 1sec write per KV key limit.
- Cthulhu_ 6y agoMmm, the only use case I can see is equivalent to an in-memory database, which would be faster than embedding sqlite.
- tmikaeld 6y agoI've also tried full-text-search in worker by pre-indexing the content, works very fast even with a JS engine - less than 5ms to make a search in 5MB of text. It runs out of CPU-time at 6MB of index though. There's someone that made a WASM for full-text search too, it's definitely faster and can handle quite a lot more text. https://github.com/wilsonzlin/edgesearch https://github.com/wilsonzlin/edgesearch
- k__ 6y agoCloudflare's "Workers Unbound" don't have that limit.
- obango 6y agoSQLite was already serverless. https://www.sqlite.org/serverless.html https://www.sqlite.org/serverless.html
- throw_m239339 6y ago> SQLite was already serverless. Yes but now it runs on someone else server! Wait a minute, this makes no sense... 'serverless' is perhaps the stupidest marketing buzzword developers have come up with.
- ithkuil 6y agoIndeed it's quite stupid. Any alternative catchy name for the concept?
- sbierwagen 6y agoHow about "inetd"?
- _ZeD_ 6y agosysadmin-less, or "someone else server"
- jahnu 6y agoI would have preferred something like daemonless, processless, or something that more closely describes what's being offered. My suggestions also are flawed but less flawed than serverless, imho.
- Animats 6y ago"Shared hosting".
- senko 6y agoI would agree and here's why: when compared to PHP scripts of old, the concept is the same. You have some bit of executable code that gets called when a specific URL is hit.
- scary-size 6y agoNice, you can actually do something a bit more flexible using SQLite on AWS Lambda. "Just" mount an elastic file system (EFS) onto your functions. I wrote about it here: https://franz.hamburg/writing/shared-storage-for-lambda-functions.html https://franz.hamburg/writing/shared-storage-for-lambda-func...
- ramraj07 6y agoDid I read it right you get 1 second responses? Why even bother with lambdas? A t3 nano reserved instance costs 2 bucks a month or something! Surely most folks don't anticipate 1000x scaling in minutes? Coupled to elastic beanstalk you can get reaonable scaling as well!
- mozey 6y agoMight be useful for services that aren't continually used? For example, month or year end processes. Not convinced I would use SQLite for a service like that though, seeing as AWS has serverless RDS
- scary-size 6y agoThis was just a proof-of-concept using the smallest lambdas available. I definitely would opt for a EC2 instance. Though lambda would be enough for something like a configuration service.
- ramraj07 6y agoIs a lambda easier to configure and set up? With Elastic Beanstalk and flask (and the sample code they provide) I can get an api running in a few minutes..
- moduspol 6y agoBoth are trivial and take only minutes if you're already familiar with them, but Lambda doesn't involve managing instances, their OSes, or making sure they scale up / down (at the instance level) the way you want.
- JustARandomGuy 6y agoI have a Google Cloud Function that uses SQLite to store a user's previous day tweets and do some minor sorting and filtering of data. Works very well and saves me the cost of a "real" SQL instance.
- donut 6y agoWhere's the SQLite database written? How do you ensure only one writer is modifying it?
- warpech 6y agoTwo-phase commit[0] could do the job [0] https://en.m.wikipedia.org/wiki/Two-phase_commit_protocol https://en.m.wikipedia.org/wiki/Two-phase_commit_protocol
- johncena33 6y agoHow do you do two phase commit on a sqlite database file?
- warpech 6y agoI don't know SQLite specifically, but I think that it can be implemented in the application logic using the primitives of any RDBMS. This old thread suggests that http://sqlite.1065341.n5.nabble.com/Re-Two-Phase-commit-using-sqlite-Ken-td33882.html http://sqlite.1065341.n5.nabble.com/Re-Two-Phase-commit-usin...
- kuter 6y agoI was thinking of making it possible for SQLite to be used with static pages. My idea is to modify SQLite to use ajax with the HTTP Range header to fetch B+ pages from the server as they are needed. SQLite already has a VFS (virtual file system) so this shouldn't be too hard. I am not sure how fast it would be and it would waste a lot of bandwidth. That's why I haven't made it yet. This would only be useful for using it with Github pages.
- ec109685 6y agoThat could be a lot of round trips.
- kuter 6y agoYes. Maybe you could increase the sizes of B+ pages ? The only reason that databases use B+ trees rather than a red-black tree or avl trees is because of the overhead of reading data from the hard drive. This would be a interesting hack.
- quietbritishjim 6y agoYou can increase the page size [1]. That will increase the size of the B tree nodes [2] (and other pages too but that's probably what you want): > The upper bound on [the number of keys on an interior b-tree page] is as many keys as will fit on the page. I think a tricky part of this idea would be the locking. Usually SQLite relies on the locking of the underlying filesystem. You could add your own mechanism that causes a lock to be assigned to a single client connection, but what if it never unlocks? (On a single machine you can tell if the client process has crashed.) You could add a timeout but what if the client process then does respond? [1] https://www.sqlite.org/pragma.html#pragma_page_size https://www.sqlite.org/pragma.html#pragma_page_size [2] https://www.sqlite.org/fileformat2.html#b_tree_pages https://www.sqlite.org/fileformat2.html#b_tree_pages
- kuter 6y agoThe clients would just send ajax queries with range headers to get the parts of the database that they want. It is going to be read only. Locking wouldn't be problem because in the eyes of the sqlite running in the browser it would have it's own read only copy of the database.
- geospiza-fortis 6y agoUsing SQLite compiled to Wasm in order to push computation closer to the user is a powerful idea. I'm partial to the method of serving up the SQLite files directly and building applications around the SQL.js library [1], which includes math extensions and the ability to embed Javascript udfs. I wrote a data visualization using SQLite as the data store [2] and can attest that it's refreshing to use SQL inside of a static website. [1] https://github.com/sql-js/sql.js https://github.com/sql-js/sql.js [2] https://ml-ranking.geospiza.me https://ml-ranking.geospiza.me
- avereveard 6y agoI did exactly that for a minor app I built for a relative (a vocabulary) - a single html page, loading a remote sqlite file, allowing for indexed user searches with no perceivable network latency. the downside is having to transfer 4mb to the client upfront, of course, but gzip shrinks that on the wire down to 1.5mb, which while heavy is acceptable nowadays.
- Groxx 6y agoHonestly, I think the page's "edge-sql" title is more descriptive. This is running SQLite within "edge" workers, as a nifty little proof-of-concept.
- sradman 6y agoedge-sql [1] allows arbitrary SQLite queries to be executed over an immutable external data set. The demo uses a Forex data set stored in Workers KV. Client issued arbitrary queries is one of the use cases for GraphQL and publishing immutable data sets on the web is the main use case for Simon Wilson’s Datasette [2]. In-memory SQLite compiled to WASM works in the browser and Node.js too. In the future, we can expect proper ACID operations on any WASM runtime that supports fsync in WASI [3], a POSIX-like API. [1] https://github.com/lspgn/edge-sql https://github.com/lspgn/edge-sql [2] https://datasette.io/ https://datasette.io/ [3] https://wasi.dev/ https://wasi.dev/
- emmanueloga_ 6y agoI was thinking a bit about an "on-edge DB" recently, perhaps something as simple as some way to persist and sync IndexedDB data between user sessions. Not usable for everything but probably great for a small CMS or a blog. For running a write-enabled DB on a CF worker a major problem is that the only storage option that I could find has really low write limits [0], 1000 writes a day for the free option. "Durable Objects" is a beta API, perhaps its transactional-storage-api [1] has better limits? -- 0: https://developers.cloudflare.com/workers/platform/limits#kv-limits https://developers.cloudflare.com/workers/platform/limits#kv... 1: https://developers.cloudflare.com/workers/runtime-apis/durable-objects#transactional-storage-api https://developers.cloudflare.com/workers/runtime-apis/durab...
- jbverschoor 6y agoSo we start adding sqlite. Then comes some sort of small framework for templating. Then we add session storage. Then we add an api endpoint so we can serve a SPA. I think after that it's really time to create a VM in sqlite so we can emulate linux and run somethings else in the 'serverless' endpoint
- bArray 6y agoIt's the future of the web they said! Seriously though, if it's Truing complete, you know it'll be abused to do something completely unintended, like run a ray tracer or something.
- onethought 6y agoRay tracing in lambda could be cool... perfect candidate for horizontal scale... lambda per pixel?
- have_faith 6y agoThat's a very expensive way of rendering, not sure I could afford more tha 30 frames per hour.
- jbverschoor 6y agoBut think of all the money you'll save by not having to install and manage the server!
- mtrycz2 6y agoYou'll be interested in https://www.destroyallsoftware.com/talks/the-birth-and-death-of-javascript https://www.destroyallsoftware.com/talks/the-birth-and-death...
- throwaway1492 6y agoLol I did this (sqlite via rest, custom urls, static file hosting) plus authentication/authorization and smtp with email templates. Everything needed for SPAs, no one was interested.
- simonw 6y agoThis is really clever. I've been wanting to try the WASM version of SQLite for something - this is a really smart usage of it. My https://datasette.io/ https://datasette.io/ project is built around a similar idea to this: the original inspiration for it was Zeit Now (now Vercel) and my realization that SQLite read-only workloads are an amazing fit for serverless providers, since you don't need to pay for an additional database server - you can literally bundle your data with the rest of your application code (the SQLite database is just a binary file). If you want to serve a SQLite API in an edge-closest-to-the-user manner for much larger database files (100MB+) I've had some success running Datasette on https://fly.io/ https://fly.io/ - which runs Docker containers in a geo-load-balanced manner. I have a plugin for Datasette that can publish database files directly to Fly: https://docs.datasette.io/en/stable/publish.html#publishing-to-fly https://docs.datasette.io/en/stable/publish.html#publishing-...
- picardo 6y agoThanks for making Datasette! I've been using it to build a project to make US ranked choice election results more accessible. It's very intuitive and easy to use. The main difficulty for me is how to get around the limitation that the sqlite files have to be colocated on the same server. These datasets can get pretty large, and I can't host them on Github, and since I can't put the datasette db on an S3 bucket, I've been exploring mounting AWS Elastic File System to the Docker container. Is there a better way?
- sorenbs 6y agoHi Picardo! We are toying around with the idea of launching a Cloud SQLite product targeting this exact use case. How large are your data sets? Would you be interested in a quick call to discuss your needs in more detail?
- picardo 6y agohey, thanks. That sounds interesting, but this is a volunteer project. I'm donating my time, so I don't think I can justify a service like that right now. The datasets are around 200MB each, just over the 100MB limit of Github.
- andix 6y agoI don't get it. Does SQLite run now on the Cloudflare servers, or is it running in the browser via WASM?
- lrossi 6y agoOn the cloudflare servers, which run server-side JavaScript.
- gnulinux 6y agoIn AWS Lambda you can run arbitrary processes in their serverless service e.g. my company runs ffmpeg in Lambda. Why does this have to be WASM in javascript engine? Why can't they just run sqlite by javascript forking to a C++ process (or using sqlite binding of javascript)? Is this a POC for wasm?
- sciprojguy 6y agoHmm. Are you checking for SQL injections?
- chipsa 6y agoMu. There's no ability to change the anything through the SQL. It spins up a new sqlite db every time, and builds the table in memory.
- atmin 6y agoVery cool. I had similar idea and glad to see it implemented and working well. Workers KV imposes 25MB limit per key. Worker memory limit is 128MB. Concatenating several values from the store or using sqlite's ATTACH DATABASE should make possible querying of about 100MB large databases, would be my guess.