66 ms·
I'm all-in on server-side SQLite
- Hilbert1114 4y ago
- swah 4y agoReminds me of https://github.com/fpereiro/backendlore https://github.com/fpereiro/backendlore
- otoolep 4y agoCongratulations to Ben! This project has been like a rocket ship.
- benbjohnson 4y agoThanks, Philip!
- abrookewood 4y agoHey Ben, any chance you can sit next to Chris McCord and get SQLite support in Phoenix :)
- michaeldwan 4y agoIt already has it [1]. Native litestream that RPC's to the primary sounds interesting though! [1] https://github.com/phoenixframework/phoenix/pull/4268 https://github.com/phoenixframework/phoenix/pull/4268
- mtremsal 4y agowow! This has apparently been in Phoenix for over a year and I had no clue. Thank you! I'd have used SQLite over postgres on pretty much every project where I needed ecto.
- abrookewood 4y agoThanks - neither did I!
- lawik 4y agoAn akoutmos made a library for litestream: https://hex.pm/packages/litestream https://hex.pm/packages/litestream
- Loic 4y agoThank you Ben. We have a small server[0], running since 2016, pushing a great amount of data incredibly fast, with BoltDB as backend. In the past two months we have been restructuring it to use SQLite, it will come online with more data in June. It looks like we are going to continue using your software... knowing first hand the quality of BoltDB, I will have no problems trusting your work with SQLite! [0]: https://www.chemeo.com/search?q=methane https://www.chemeo.com/search?q=methane
- wolfhumble 4y agoI always thought that SQLite was kind of operating in stealth mode. Everyone was talking nicely about it, but it lacked a few things so it was a "super DB" but not in the "big boys league". And now it is taking off and the other DB's are saying "you here?", and SQLite goes "Yup, bye bye" ;-) This is really useful and fun, thanks! Godspeed on this new part of the journey!
- ok_dad 4y agoI was just about to start using this for a project, I hope the license won’t change. Congrats to the author though, no matter what! I wish everyone could be so successful.
- deleted 4y ago[deleted]
- benbjohnson 4y agoLitestream author here. It'll continue to be open source under an Apache 2 license.
- pbowyer 4y agoNot surprised. Congratulations Ben!
- endisneigh 4y agoWhat’s an example of a popular app (more than 100K users) that uses lite stream? Curious to see how this looks like in production
- deleted 4y ago[deleted]
- jkaplowitz 4y agoTailscale: https://tailscale.com/blog/database-for-2022/ https://tailscale.com/blog/database-for-2022/ I don't know their user count, but they are growing well and just raised their Series B.
- benbjohnson 4y agoLitestream author here. That's a good question. There's not very good visibility into open source usage so it's hard to say unless folks write blog posts about it. For example, I know Tailscale runs part of their infrastructure with SQLite & Litestream[1]. I wrote a database called BoltDB before and I have no idea how widespread it is exactly. It's used in a lot of open source projects like Consul & etcd but I don't know anything about non-public usage. [1]: https://tailscale.com/blog/database-for-2022/ https://tailscale.com/blog/database-for-2022/
- gfd 4y agoFor non-public usages, I remember Boltdb being named as one of the root causes that took down Roblox for three days! https://blog.roblox.com/2022/01/roblox-return-to-service-10-28-10-31-2021/ https://blog.roblox.com/2022/01/roblox-return-to-service-10-...
- benbjohnson 4y agoYep! That's usually how I find out usage inside companies. :)
- deleted 4y ago[deleted]
- swlkr 4y agoThe reduction in complexity from using sqlite + litestream as a server side database is great to see!
- tyingq 4y agoDqlite is also interesting, and in a similar space. It seems to have evolved from the LXC/LXD team wanting a replacement for Etcd. It's Sqlite with raft replication and also a networked client protocol. https://dqlite.io/docs/architecture https://dqlite.io/docs/architecture
- tptacek 4y agoThere's also rqlite. There's definitely a place for this kind of stuff. But we already use a bunch of stuff that does distributed consensus in our stack, and the experience has left us wary of it, especially for global distribution. We almost used rqlite for a statekeeping feature internally, but today we'd certainly just use sqlite+litestream for the same kinds of features, just because it's easier to reason about and to deal with operationally when there's problems. https://fly.io/blog/a-foolish-consistency/ https://fly.io/blog/a-foolish-consistency/
- otoolep 4y agorqlite author here. Anything else you can tell me about why you decided against it? Just simpler, as you say, to avoid a distributed system when you can (something I understand).
- tptacek 4y agoWe like rqlite a lot. There's some comments in your issue tracker from Jerome about it at the time. The decision wasn't against rqlite as a piece of software so much as it was us deliberately deciding not to introduce more Raft into our architecture; any place there is Raft, we're concerned we'll essentially need to train our whole on-call rotation on how to handle issues. The annoying thing about global consensus is that the operational problems tend to be global as well; we had an outage last night (correlated disk failure on 3 different machines!) in Chicago, and it slowed down deploys all the way to Sydney, essentially because of invariants maintained by a global Raft consensus and fed in part from malfunctioning machines. I think rqlite would make a lot of sense for us for applications where we run multiple regional clusters; it's just that our problems today tend to be global. We're not just looking for opportunities to rip Raft out of our stack; we're also trying to build APIs that regionalize nicely. In nicely-regionalized, contained settings, rqlite might work a treat for us.
- no_wizard 4y agoThis a great and interesting offering! I think this fits well with fly.io and their model of computing. I now wish that I had engaged with this idea that was very similar to litestream that I had about a year and half ago. I always thought SQLite just needed a distribution layer to be extremely effective as a distributed database of sorts. Its flat file architecture means its easy to provision, restore and backup. SQLite also has incremental snapshotting and re-producible WAL logs that can be used to do incremental backups, restores, writes etc. It just needs a "frontend" to handle those bits. Latency has gotten to the point where you can replicate a database by its continued snapshots (which is, on a high level, what litestream appears to be doing) being propagated out to object / blob storage. You could even achieve brute force consensus with this approach if you ran it in a truly distributed way (though RAFT is probably more efficient). Reason I didn't do this? I thought to myself - why in the world in 2020 would someone choose to use SQLite at scale instead of something like Firebase, Spanner, Fauna, or even Postgres? So after I did an initial prototype (long gone, never pushed it to GitHub) I just felt like...there was no appetite for it. Now I regret! Just a long winded way of saying, congrats! This is awesome! Thanks for doing exactly what I wanted to do but didn't have the guts to follow through with.
- epilys 4y agoI implemented exactly this setup, in Rust, last year for a client. Distributed WAL with write locks on a RAFT scheme. Custom VFS in Rust for sqlite3 to handle the IO. I asked the client to opensource it but it's probably not gonna happen... It's definitely doable though.
- ComputerGuru 4y agoDid you write your own rust raft implementation or reuse something already available?
- epilys 4y agoReused a well known library that uses raft. I don't know if I should mention any more details since it was a private project.
- mrcwinn 4y agoI have really enjoyed using Fly. Great service and support.
- rco8786 4y agoAll of the action around SQLite recently is very exciting!
- bob1029 4y ago> SQLite isn't just on the same machine as your application, but actually built into your application process. When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds. That's micro, with a μ. A 50-100x improvement over an intra-region Postgres query. This is the #1 reason my exuberant technical mind likes that we use SQLite for all the things. Latency is the exact reason you would have a problem scaling any large system in the first place. Forcing it all into one cache-coherent domain is a really good way to begin eliminating entire universes of bugs. Do we all appreciate just how much more throughput you can get in the case described above? A 100x latency improvement doesn't translate directly into the same # of transactions per second, but its pretty damn close if your I/O subsystem is up to the task.
- overview 4y ago> Latency is the exact reason you would have a problem scaling any large system in the first place. Not always. It depends on the architecture and your hosting strategy. I think it’s more likely for an instance of a web app to receive more requests than it can handle, causing the app to not service any requests.
- closeparen 4y agoThis is a large part of what Rich Hickey emphasizes about Datomic, too. We're so used to the database being "over there" but it's actually very nice to have it locally. Datomic solves this in the context of a distributed database by having the read-only replicas local to client applications while the transaction-running parts are remote.
- abraxas 4y agoOnly trouble with that particular implementation is that the Datomic Transactor is a single threaded single process that serializes every transaction going through it. As long as you don't need to scale writes it works like a charm. However, the workloads I somehow always end up working with are write heavy or at best 50/50 between read and write.
- beck5 4y agoI have found it easy to overload SQLite with too many write operations (20+ Concurrently), is this typical behaviour referred to in the post, or a write heavy workload?
- benbjohnson 4y agoIt can depends on a lot of factors such as the journaling mode you're using as well as your hardware. SQLite has a single-writer-at-a-time restriction so it's important manage the size of your writes. I typically see very good write throughput using WAL mode and synchronous=normal on modern SSDs.
- Scarbutt 4y agoHow big are the writes? are you storing blobs?
- ilrwbwrkhv 4y agoFor how much?
- benbjohnson 4y agoLitestream author here. I've been on the fence about disclosing the amount. I'm generally open about everything but I know some people get weird about money stuff. I'm also autistic so I tend to not navigate social norms very well. That all being said, the project was acquired for $500k.
- scottlamb 4y agoThanks for sharing that. I've never really looked at open source projects as acquisition targets. I see in another comment that you're going to continue releasing it under the Apache license. It's easy for me to see why fly.io would want to hire you, with an agreed percentage (anywhere from 0%-100%) of your time continuing to go into Litestream. If you forgive the blunt question, what more do they get for the $500k (acquisition cost / signing bonus)? (Part of me is wondering if an open source project of mine, which various startups have shown some degree of interest in, is holding a significant payday I hadn't realized. Probably not, but it seems more possible than a moment ago.)
- tartakovsky 4y agoI would also be interested in understanding whether there is a proper pricing model for such things. Wordle comes to mind. Or a friend that has an IPad app that took 2 years to build that is something novel but not released. Some projects are open-source and some aren't. Some are acquired for users and some are acqui-hired for continued development. Any interesting advice or links here for folks that don't want to be founders but want to make a solid chunk of cash, have an expertise of value and love the development work.
- benbjohnson 4y agoThere's not any real pricing model that I know of. I think it comes down to a question of what value an acquisition brings and that's always kinda fuzzy. If you want specific numbers, the project was at ~5k GitHub stars at the time of acquisition so I guess it's a hundred bucks per star. :)
- mtlynch 4y agoSuper cool! Congrats, Ben! I've been building all of my projects for the last year with SQLite + fly.io + Litestream. It's already such a great experience, but I'm excited to see what develops now that Litestream is part of fly.
- jrochkind1 4y agoWhile the title is about a business acquisition, the article is mostly about the technology itself -- replicating SQLite, suggested as a superior option to a more traditional separate-process rdbms, for real large-scale production workloads. I'd be curious to hear reactions to/experiences with that suggestion/technology, inside or outside the context of fly.io.
- LunaSea 4y agoI wonder if we'll ever see an embedded version of PostgreSQL?
- nicoburns 4y agoThat's basically what SQLite is (notably, SQLite makes an effort to be compatible with Postgres's SQL syntax). If you mean based off the actual PostgreSQL codebase, then I highly doubt it.
- LunaSea 4y agoI doubt it as well. That's sad though because SQLite is really missing a lot of features that PostgreSQL has.
- nicoburns 4y ago> That's sad though because SQLite is really missing a lot of features that PostgreSQL has. It is, but luckily it's not standing still. It's added JSON support and window functions in recent years for example.
- melony 4y agoNote that the popular Node.js ORM Prisma does not support WAL. https://github.com/prisma/prisma/issues/3303 https://github.com/prisma/prisma/issues/3303
- tylergetsay 4y agoIt also crashes if you try to write to the DB while its open https://github.com/prisma/prisma/issues/2955 https://github.com/prisma/prisma/issues/2955
- LAC-Tech 4y agoBest option for SQlite with node is this. https://github.com/JoshuaWise/better-sqlite3 https://github.com/JoshuaWise/better-sqlite3 Author is all over the issues section, and seems very knowledgeable about how SQLite works.
- paulhodge 4y agoWow Litestream sounds really interesting to me. I was just starting on an architecture, that was either stupid or genius, of using many SQLite databases on the server. Each user's account gets their own SQLite file. So the service's horizontal scaling is good (similar to the horizontal scaling of a document DB), and it naturally mitigates data leaks/injections. Also opens up a few neat tricks like the ability to do blue/green rollouts for schema changes. Anyway Litestream seems pretty ideal for that, will be checking it out!
- mwcampbell 4y agoAn architecture like yours has certainly been done before, though AFAIK it never went mainstream. In particular, check out this post from Glyph Lefkowitz of Twisted Python fame, particularly the section about the (apparently dead) Mantissa application server: https://glyph.twistedmatrix.com/2008/06/this-word-scaling.html https://glyph.twistedmatrix.com/2008/06/this-word-scaling.ht...
- hantusk 4y agoSame pattern is ActorDB: https://github.com/biokoda/actordb https://github.com/biokoda/actordb
- deleted 4y ago[deleted]
- Scarbutt 4y agoEach user's account gets their own SQLite file. So now you need one database connection per user...
- tptacek 4y agoAnd? It's SQLite; it's a file handle and some cache, not a connection pool.
- mwcampbell 4y agoDepending on how you define "account", that can be quite reasonable. In a B2B application, each business customer could get their own SQLite database, and the number of SQLite connections would likely be quite manageable, even though some customers have many users.
- wasd 4y agoFly is putting together a pretty great team and interesting tech stack. It's the service I see as a true disruptor to Heroku because it's doing something novel (not just cheaper). I'm still a little murky on the tradeoffs with Fly (and litestream). @ben / @fly, you should write a tutorial on hosting a todo app using rails with litestream and any expected hurdles at different levels of scale (maybe comparing to Heroku).
- purplerabbit 4y agoRender is more of the successor IMO. Fly is a bit of a wildcard — they are bleeding edge, certainly, but they seem to shy away from focusing on implementation of some of the “boring” but extremely useful features present in most managed services (e.g., scaling volumes for Postgres)
- michaeldwan 4y agoWe're not shying away from "boring" stuff at all. We just have a small team with bigger priorities that's spread too thin. There's a million things like resizable volumes we need to ship and we're aggressively hiring to get them done.
- steve_adams_86 4y agoAre the job listings on your site the source of truth, or are there other listings out there? I’ve been keeping my eye out for a senior full stack role but no luck yet.
- tptacek 4y agoThere's no one "successor to Heroku". The successor to Heroku is a collection of different companies that work well together. What's important is the Heroku idea of what an application is, as a developer-first prospect rather than an ops-first prospect like Kubernetes running on a cloud platform.
- the_biot 4y agoIf only they could keep their website reachable, that would be the icing on the cake. Like every time I see them linked on HN, I click and cannot connect to their website. Last time somebody from fly said they'd look into it, but alas. It was related to IPv6 on their end, was as far as I could tell.
- swaraj 4y agoLooks v cool, but I feel like I'm missing a big part of the story, how do 2 app 'servers/process' connect to same sqlite/litestream db? Do you 'init' (restore) the db from each app process? When one app makes a write, is it instantly reflected on the other app's local sqlite?
- thruflo 4y agoAlso how does the WAL page based replication maintain consistency / handle concurrent updates?
- infogulch 4y agoIt doesn't, this gives you a read-only replica only.
- judofyr 4y agoEach server would have one copy of the SQLite database. Only one of the server would support writes — and those write will be replicated to the other server. Reads in the other server will be transactionally safe, but might be slightly out of date.
- swaraj 4y agoThis is my main q: are the writes replicated in real-time? Do the apps that just need read access have to repeatedly call 'restore'?
- tptacek 4y agohttps://litestream.io/getting-started/#continuous-replication https://litestream.io/getting-started/#continuous-replicatio...
- losvedir 4y agoIt says "continuous" but I don't really see how it is. Or, at least, I get that the backin-up is continuous, since litestream is watching the WAL. But in the example there, isn't `restore` called manually to pick up the change? Is the idea you just kind of "poll" restore? That seems like a lot of extra work, if I'm reading that example correctly. It pulls down the whole database every time? Even a "small" SQLite DB (for the use cases I'm thinking of) can easily be a hundred megabytes. I don't think I'd want to poll that every few seconds.
- rwho 4y ago
- mwcampbell 4y agoCongratulations to Ben on getting a well-funded player like Fly to buy into this vision. I'm looking forward to seeing a complete, ready-to-deploy sample app, when the upcoming Litestream enhancements are ready. I know that Fly also likes Elixir and Phoenix; they hired Chris McCord, after all. So would it make sense for Phoenix applications deployed in production on Fly to use SQLite and Litestream? Is support for SQLite in the Elixir ecosystem, particularly Ecto, good enough for this?
- warmwaffles 4y ago> Is support for SQLite in the Elixir ecosystem, particularly Ecto, good enough for this? Why yes it is. I maintain the `exqlite` and `ecto_sqlite3` libraries and it was just integrated in with `kino_db` which is used by `livebook`. https://github.com/elixir-sqlite/exqlite https://github.com/elixir-sqlite/exqlite
- lawik 4y agoI still love you for making this happen.
- RcouF1uZ4gsC 4y agoI love Litestream! It is so simple and it just works! Congratulations, Ben, on making a great product and on the sale! One thing I have had in the back of my mind, but have not had the time to pursue is using SQLite replication to make something similar to CloudFlare's durable objects but more open. A "durable object" would be an SQLite database and some program that processes requests and accesses the SQLite database. There would be a runtime that transparently replicates the (database, program) pair where they are needed and routes to them. That way, I can just start out locally developing my program with an SQLite database, and then run a command and have it available globally. At the same time, since it is just accessing an SQLite database, there would be much less risk of lockin.
- foodstances 4y agoJust curious, is there any financial compensation/support going to Richard Hipp with all of this money changing hands? When I see these startups making a business that is so heavily based on open-source software (like Tailscale on top of Wireguard), I have to wonder what these companies do to actually support the author(s) of the software that so much of their company is based on.
- mrkurt 4y agoYes. We (Fly.io) are buying a sqlite support agreement. We also send money WireGuard's way. I'm pretty sure Tailscale does too. We have also given OSS authors advisor equity. A couple of folks wrote libraries that were important to keeping us going, and we've granted them shares the same way some startups would to MBA advisors.
- foodstances 4y agoThat's great to hear, thank you!
- defen 4y ago> We have also given OSS authors advisor equity That's a fantastic idea. In retrospect it's a really obvious idea but I've never heard of anyone doing it before. Is this a common thing that I'm just oblivious to?
- mrkurt 4y agoIt's not common, which is stupid. We're banging that drum pretty hard though. Maybe you'll see a #1 ranked HN post about it someday. :)
- qbasic_forever 4y agoI agree Richard Hipp should be compensated but he explicitly licensed and releases SQLite under a public domain license: https://www.sqlite.org/copyright.html https://www.sqlite.org/copyright.html Not Apache, not MIT, not GPL... public domain. You can do almost anything with it and not be beholden to any demands. You can tell people you built your business on SQLite... or not. It's public domain. That said SQLite has a business model of selling support and premium features like encryption: https://www.sqlite.org/prosupport.html https://www.sqlite.org/prosupport.html
- seanwilson 4y agoSQLite uses dynamic types? Is this an issue in practice, especially for large apps? Don't you lose guarantees about your data which makes it messy to handle on the backend? Context from https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html: "SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases. Flexible typing is a feature of SQLite, not a bug."
- aliswe 4y agoThis sounds like schemalessness to me? serious "question".
- jamie_ca 4y agoNot schemaless, but typeless. SQLite will let you declare a column to be an integer and then dump a string into it, but you're still defining a table with specific columns. It's like the opposite problem Mysql has when you try to write data larger than the field definition - Mysql will truncate, Sqlite will store the data you gave it.
- seanwilson 4y agoTypeless is the default though? Why wouldn't you want the types to be reliable when you're reading/writing from the backend in the general case?
- mbreese 4y agoI believe typeless is the default because of largely historical reason. Namely, typeless was the original mode and strict mode was added later. But, that’s not the only reason. There is a whole page on why typeless is a feature and not a bug. https://sqlite.org/flextypegood.html https://sqlite.org/flextypegood.html
- NeutralForest 4y agoThere's something I don't understand, it says that the "data is next to the application", what does it mean? Where is stored and how is it accessed by the application?
- tptacek 4y agoThe data lives in a file the application reads/writes directly (and in a cache that the sqlite libraries can park inside the application itself). The point is that you're not calling out over the network to a "database server"; your app server is the database server.
- NeutralForest 4y agoThanks for the explanation!
- ledauphin 4y agoit means the data is stored in a file on the local drive of a computer that is also running the application. it also means that it is the application itself (via the SQLite library) that reads and modifies that database file. There is no separate database process.
- NeutralForest 4y agoGreat! Thanks for the explanation.
- learndeeply 4y agoSince both Fly.io and Litestream founders are here - why not disclose the price?
- benbjohnson 4y agoLitestream author here. I just posted it as a reply here: https://news.ycombinator.com/item?id=31319556 https://news.ycombinator.com/item?id=31319556
- kall 4y agoI am as obsessed with sub 100ms responses as the people at fly.io, so I think the one writer and many, many readers architecture is smart and fits quite a few applications. When litestream adds actual replication it will get really exciting. > it won't work well on ephemeral, serverless platforms or when using rolling deployments That's... a lot of new applications these days.
- mwcampbell 4y ago> it won't work well on ephemeral, serverless platforms or when using rolling deployments I assumed that was what Fly was hiring Ben to work on.
- mrkurt 4y agoYes. Yes it is.
- emptysea 4y agoYeah the rolling deployments gotcha really stuck out to me. I think most PaaS will provide that by default anyways because who wants downtime during deploys?
- mwcampbell 4y agomrkurt specifically mentioned that a solution for that is in the works. https://news.ycombinator.com/item?id=31319544 https://news.ycombinator.com/item?id=31319544
- anyfactor 4y agoStory time! A client told me that they will use a DigitalOcean droplet for a web app. Because the database was very small I chose to use SQLite3. After delivery the client said their devops guy wasn’t available they would like to deploy to Heroku. Heroku being a ephemeral cloud service couldn’t handle the same directory SQLite3 db I had there. The only solution was to use their Postgres database service. For some reason, it was infuriating that I have to use a database like that to store few thousand rows of data. Moreover, I would have to rewrite a ton of stuff accommodate the change to Postgres. I ended up using firestore. --- I think something like this could have saved me a ton of hassle that day.
- luhn 4y agoIt was too much work to migrate from SQLite to PostgreSQL, so you migrated to... a NoSQL DB?
- me_me_mu_mu 4y agoPlease let me know if you’ve ever had to move data out of firestore. I’m currently using firestore for some real time requirements but the data is written to Postgres before the relevant data for real time needs (client needs to show some data updating constantly) is written to firestore. Just curious if you’ve ever had to migrate data out of firestore.
- krts- 4y agoA great project with awesome implications. Well deserved, and the fly.io team are very pragmatic. This will be even more brilliant than it already is when fly.io can get some slick sidecar/multi-process stuff. I ended up back with Postgres after my misconfigs left me a bit burned with S3 costs and data stuff. But I think a master VM backed by persistent storage on fly with read replicas as required is maybe the next step: I love the simplicity of SQLite.
- _vufv 4y agoI absolutely love this. I think so called n-tier architecture as a pattern should be aggressively battled in the attempt to reduce the n. Software is so much more reliable when the communication between different computational modules of the system are function calls as opposed to IPC calls. Why does everything that computes something or provides some data need to be a process? It doesn't. Postgresql and every other server/process should have first class support for a single CLI command that: spins up the DB that slurps up the config and the data storage, takes the SQL command provided through the CLI arguments, runs it, returns results and terminates. Effectively, every server/process software should be a library first, since it's easy to make a server out of a library and the reverse is anything but.
- tiffanyh 4y ago@dang, the actual title is “ I'm All-In on Server-Side SQLite” Maybe I missed it but where in the article does it say Fly acquired Litestream? EDIT: Ben Johnson says he just joined Fly. Nothing about Fly “acquiring” Litestream. https://mobile.twitter.com/benbjohnson/status/1523748988335263744?cxt=HHwWgMCsibfouKUqAAAA https://mobile.twitter.com/benbjohnson/status/15237489883352...
- lnsp 4y ago> Litestream has a new home at Fly.io, but it is and always will be an open-source project. My plan for the next several years is to keep making it more useful, no matter where your application runs, and see just how far we can take the SQLite model of how databases can work. As far as I understood it, Fly.io hired the person working on Litestream and pays them to keep working on Litestream.
- tiffanyh 4y agoThat’s how I understood it and that’s radically different than how this HN post got titled. Ben Johnson confirms how you framed it here: https://mobile.twitter.com/benbjohnson/status/1523748988335263744?cxt=HHwWgMCsibfouKUqAAAA https://mobile.twitter.com/benbjohnson/status/15237489883352...
- tptacek 4y agoWe wrote a different title for this blog post, and we did in fact buy Litestream (to the extent that anyone can "buy" a FOSS project, of course).
- apgwoz 4y ago> (to the extent that anyone can "buy" a FOSS project, of course). Does this mean that, in addition to offering a salary / options, you provided some sort of additional one-time compensation for copyright assignment?
- 4y ago
- thdxr 4y agoin practice how do you make a single application node the writer? do you now need your nodes to be clustered + electing a leader and shipping writes there? know fly.io did this with PG + Elixir but BEAM makes this type of stuff pretty easy
- scwoodal 4y ago> According to the conventional wisdom, SQLite has a place in this architecture: as a place to run unit tests. Be careful with this approach. Frameworks like Django have DB engine specific features[1]. When you start using them in your application you can no longer use a different DB (SQLite) to run your unit tests. [1] https://docs.djangoproject.com/en/4.0/ref/contrib/postgres/fields/ https://docs.djangoproject.com/en/4.0/ref/contrib/postgres/f...
- netcraft 4y agoThis is similar to what I hoped websql had eventually grown into. sqlite in the browser, but let me sync it up and down with a server. Every user gets their own database, the first time to the app they "install" the control and system data, then their data, then writes are synced to the server. If it became standard, it could be super easy - conflict resolution notwithstanding.
- bambax 4y agoYou can make webapps using exactly this approach, with json in localstorage as the client db, and occasiona, asynchronous, writes to the server. I'm now building a simple webapp exactly like this, and the server db is sqlite. So far it works perfectly fine.
- netcraft 4y agoIn my experience the size limitations of localstorage keeps this from really being viable. And I just really like SQL. But your point is well taken, it is possible to do it today. My hope back then is that there would be libraries over it that would have made it easy and commonplace.
- quintes 4y agoWhat’s the use case here, a single web app with inproc db? More complex use cases? I remember I could do this on azure at one point in time with app services, not Sure if it’s still a thing.. but heavy writes and scaling of those types of apps would lead to to rethink this approach right?
- jchw 4y agoThis is interesting! I like using Fly.io today, but I’m currently using a single node for most stuff with SQLite. Having some kind of failover and replication would be pretty awesome. I have yet to try Litestream and it does sound like there’s some issues to work out that could be pretty nasty, but I’ll definitely be watching. Fly.io is very nice. It’s what I hoped Hyper.sh would be, except it isn’t dead. That said, there are a couple things I worry about… like, there’s no obvious way to resize disks, you pretty much need to make a new disk that’s larger, launch a new instance with it mounted, and transfer data from an existing instance. If it was automated, I probably wouldn’t care, though a zero downtime way of resizing disks would be a massive improvement. Another huge concern is just how good the free tier is. I actually am bothered that I basically don’t get billed. Hyper.sh felt a bit overpriced, and by comparison Fly.io does scale up in price but for small uses it feels like theft.
- michaeldwan 4y ago> there’s no obvious way to resize disks Yes, this sucks right now. Resizable disks is on our list, we just need somebody to spend a few days on it. Luckily we're hiring platform engineers [1] to work on fun problems like that. > I actually am bothered that I basically don’t get billed. We actually had a bug that skipped charging a bunch of accounts. :) Regardless, we're not overly concerned about making $1/mo from small accounts. Large customers more than make up for it. Turns out building something devs _choose_ to use on their free time often leads to using it at work too. [1] https://fly.io/jobs/platform-product-engineer/ https://fly.io/jobs/platform-product-engineer/
- ignoramous 4y ago> Yes, this sucks right now. If I may, really need to hire sudhirj back or get someone doing the tedious work of answering dumb/advanced questions in the forums and doing follow-ups! Even if it doesn't scale, this high-touch forum engagement may not only help inform the product roadmap but help eventually cultivate a stronger community.
- Maksadbek 4y agoSQLite is known for having many various extentions. If the streaming replication is so important, why didn't sqlite authors create such one before ?
- PhineasRex 4y agoIt's been a while since we reinvented the wheel, hasn't it.
- aidenn0 4y agoThe SQLite team has done a good job over the years establishing an ethos (in the rhetorical sense) of writing reliable software. The degree to which this can transfer to Lighstream is the degree to which Lightstream is intrusive on the SQLite code. Another way of saying it: I trust the SQLite's team statements of stability for SQLite because of history and a track-record for following stringent development processes. The same is not true of the Lighstream team. Does anybody know how much any potential damage introduced by the Lightstream code could affect the integrity of my data on disk -- obviously replication added by Lightstream will be only as good as the Lighstream team makes it, but to what degree is the local data-store affected?
- downut 4y ago(I am attempting my first "as much as possible make the database do the work" app right now, after 35 years in the business. Yeah I started out on the scientific side, and then the sort of things SQLite is obviously great for.) I do not understand how one implements the multi-role access system on top of SQLite that postgresql gives you for free. Other than do it from scratch (eeek!) on the app side. Just as an example, think of the smallest db backed factory situation you can imagine... as small as you like. There will need to be multiple roles if more than one role accesses the database tables.
- tptacek 4y agoI spent from 2005 to 2020 doing almost nothing but vulnerability research, where the modal client project was a SAAS-type app, and my experience is that only a tiny fraction of companies building on Postgres actually use Postgres authorization features. It's far more typical to build this logic into the application than to build off the database's authorization features. Nevertheless, if you're building an app that takes advantage of database auth features, that's a powerful reason to keep on using Postgres. You actually have one of the major problems Postgres solves for!
- kondro 4y agoCurious about the costs of this. Wouldn't it cost at least $13/month just in PutObject request costs to replicate Sqlite to S3 at the default of 1 sync per second? Or is it smart enough to only sync if there have been additions to the WAL?
- jpcapdevila 4y agoIt only PUTs if there's writes.
- kondro 4y agoGreat. :)
- ignoramous 4y agoLooking forward to ditching my PlanetScale plans for this! > ...people use Litestream today is to replicate their SQLite database to S3 (it's remarkably cheap for most SQLite databases to live-replicate to S3). Cloudflare R2 would make that even cheaper. Cloudflare set to open beta registration this week. And if you squint just enough, you'd see R2, S3 et al are nosql KV store themselves, masquerading as disk drives, and used here to back-up a sql db... > My claim is this: by building reliable, easy-to-use replication for SQLite, we make it attractive for all kinds of full-stack applications to run entirely on SQLite. Disruption (? [0]) playing out as expected? That said, the world reliable is doing a lot of heavy lifting. Reliability in distributed systems is hard (well... easy if your definition of reliability is different ;) [1]) > And if you don't need the Postgres features, they're a liability. Reminds me of WireGuard, and how it accomplishes so much more by doing so much less [2]. Congratulations Ben (but really, could have taken a chance with heavybit)! ---- [0] https://hbr.org/2015/12/what-is-disruptive-innovation https://hbr.org/2015/12/what-is-disruptive-innovation [1] God help me, the person on the orange site saying they need to run Jepson tests to verify Litestream WAL-shipping. Stand back! You don’t want to get barium sulfated!, https://twitter.com/tqbf/status/1510066302530072580 https://twitter.com/tqbf/status/1510066302530072580 [2] "...there’s something like 100 times less code to implement WireGuard than to implement IPsec. Like, that is very hard to believe, but it is actually the case. And that made it something really powerful to build on top of*, https://www.lastweekinaws.com/podcast/screaming-in-the-cloud/the-magic-of-tailscale-with-avery-pennarun/ https://www.lastweekinaws.com/podcast/screaming-in-the-cloud...
- chloerei 4y ago> Cloudflare set to open beta registration this week. Any source? I wait for a long time.
- ignoramous 4y agoMay 11: https://archive.is/2u5Rt https://archive.is/2u5Rt
- jgrahamc 4y agoThat's correct. Tomorrow. R2 open beta and a hell of a lot more.
- boesboes 4y agoHow well does this scale for larger data sets? Could I use it with 100GB of data for instance?
- jasfi 4y agoIs there a good DB admin GUI that supports both SQLite and Postgres?
- jwaterhouse 4y agoYou mean like DBeaver? https://dbeaver.io/ https://dbeaver.io/
- jpcapdevila 4y agoI love Datagrip by jetbrains.
- coliveira 4y agoWhat I think interesting is that people write articles about technology architectures without even bothering trying to use the said architecture. I would be very interested in reading from someone who actually used sqlite in a large scale application in the way he described, and then tell what worked or not in this setup. Until then, this article is nothing more than a proposal, another kind of vaporware.
- case 4y agoFwiw, Tailscale has done this, and written about it: https://tailscale.com/blog/database-for-2022/ https://tailscale.com/blog/database-for-2022/
- farmin 4y ago> The upcoming release of Litestream will let you live-replicate SQLite directly between databases, which means you can set up a write-leader database with distributed read replicas. Read replicas can catch writes and redirect them to the leader; most applications are read-heavy, and this setup gives those applications a globally scalable database. Would this make lightstream a possible fit to sync a mobile device to a users own silo of data on 'server'? Would need a port of lightstream to Dart.
- OOPMan 4y agoCool technical marketing blog story bro
- nojvek 4y agoSomebody needs to build litestream for duckdb (columnstore oriented sqlite like db). That would be epic. DuckDB speed is crazy fast when it comes to aggregate/analysis queries.
- 3np 4y agoA common gotcha with sqlite and WAL is how it's not supported on networked filesystems, which will bite anyone trying to keep their data volumes replicated over glusterfs, ceph, and similar with corruption. Let's say we're running a vendored application (forking it is not an option) utilizing WAL and want to store the db on one of those filesystems not traditionally suitable for WAL'd sqlite. Would dropping in Litestream on the db allow us to do so safely?
- tybit 4y agoI think this architecture would be really powerful paired with the actor model to shard databases to nodes.
- 0xbadcafebee 4y agoThings I would like in a database: - All changes stored as diff trees with signed cryptographic hashes. I want to check out the state of the world at a specific commit, write a change, a week later write another change, revert the first change 3 weeks later. And I want it atomic and side-loaded with no performance hit or downtime. - Register a Linux container as a UDF or stored procedure. Use with pub/sub to create data-adjacent arbitrary data processing of realtime data - Fine-grained cryptographically-verified least-privilege access control. No i/o without a valid short-lived key linked to rules allowing specific record access. - Virtual filesystem. I want to ls /db/sql/SELECT/name/IN/mycorp/myproduct/mysite/users/logged-in/WHERE/Country/EQUALS/USA. (Yes, this is stupid, but I still want it. I don't want to ever have to figure out how to connect to another not-quite-compatible SQL database again.)
- sa46 4y ago> I want to check out the state of the world at a specific commit, write a change, a week later write another change, revert the first change 3 weeks later. This is sort of like temporal tables but with the ability to branch from a previous point of history. I'm not sure it would play well with foreign keys. You could branch the entire database with either point-in-time recovery or with the file-system using ZFS. Postgres.ai turned this into a product.
- daniel_iversen 4y agoFor most people's purposes I'd assume that ease-of-use, ease-of maintenance, relatively good speed, safe, documented, feature rich and scalable is important. I like SQLite, and while it's cool that they've fixed some big things around safety and clustering, it still seems like a "below-bare-minimum" choice for a lot of production systems, or am I just being old school? MariaDB (/ MySQL) really has a whole lot of good features that I thought would just make it a safer choice? What do people think and why?
- jpcapdevila 4y agoCould you elaborate on the features that would make MySQL a safer choice?
- mkleczek 4y agoI guess I am in minority here now but... Embedding an SQL database in the application is really missing the point of having RDBMS. The goal of RDBMS is not merely to persist application data but to _share_ data between different applications. And while current trend is to implement sharing by applications I expect this to change in the future as it is much more economical to use RDBMS to share data.
- pjmlp 4y agoIf it comes with the same tooling as Oracle and SQL Server, I might think about using it server side, until then not really.
- kgeist 4y ago>But database optimization has become less important for typical applications. <..> As much as I love tuning SQL queries, it's becoming a dying art for most application developers. We thought so, too, but as our business started to grow, we had to spend months, if not years, rewriting and fine-tuning most of our queries because every day there were reports about query timeouts in large clients' accounts... Some clients left because they were disappointed with performance. Another issue is growing the development team. We made the application stateless so we can spin up additional app instances at no cost, or move them around between nodes, to make sure the load is evenly distributed across all nodes/CPUs (often a node simply dies for some reason). Since they are stateless, if an app instance crashes or becomes unstable, nothing happens, no data is lost, it's just restarted or moved to a less busy node. DB instances are now managed by the SRE team which consists of a few very experienced devs, while the app itself (microservices) is written by several teams of varying experience and you worry less about the app bringing down the whole production because microservice instances are ephemeral and can be quickly killed/restarted/moved around. Simple solutions are attractive but I'd rather invest in a more complex solution from the very beginning, because moving away from SQLite to something like Postgres can be costlier than investing some time in setting up 3-tier if you plan your business to grow, otherwise eventually you can end up reinventing 3-tier, but with SQLite. But that's just my experience, maybe I'm too used to our architecture.
- CGamesPlay 4y agoI agree with this article! I even went so far as to write a Prisma-like SQL client generator that uses better-sqlite3 under the hood, so you get the nice API of Prisma and the synchronous performance of better-sqlite3. I’ve been using it for a few small projects, but I just released it at 1.0 yesterday. https://github.com/CGamesPlay/rapid-cg https://github.com/CGamesPlay/rapid-cg
- kukabynd 4y agoGreat move, congrats to everyone involved. Fly is very promising player in the space. Pipeline looks amazing, and I’ll be trying more of your offerings down the road.
- steve_gh 4y agoThank you Ben! This is exactly what I need for the data science and analytics problems I work on. We import data from a variety of sources via an ETL process, but we want to distribute the data analytics to multiple read-only process nodes. This gives is the speed is SQLite plus easy replication and a single source of truth. Chapeau!!!
- deleted 4y ago[deleted]
- deleted 4y ago[deleted]
- jl6 4y agoPerhaps you could avoid the need for an additional replication tool if you happened to have some kind of synchronous stretch clustered SAN storage on which to place the SQLite database file. Moving HA to the infra layer?
- dsincl12 4y agoUhm... experience from a large project that used SQLite was that we where hit with SQLite only allowing one write transaction at a time. That is madness for any web app really. Why do everyone seem so hyped on this when it can't really work properly IRL? If you have large amounts of data that need to be stored the app would die instantly, or leave all your users waiting for their changes to be saved. What am I missing?
- dagw 4y agoWhat am I missing? Many sites are Read (almost) Only. For sites where users interactively query/view/explore the data, but (almost) never write their own, it works great.
- unicornporn 4y agoSpeaking of this, I really wish there was SQLite support in WordPress...
- samwillis 4y agoA blog is the perfect example of where SQLite should be used other a DB server.
- quickthrower2 4y agoIf you chuck Varnish in front of it, does it matter what you use? Edit: was being serious: if your data is that static you can statically generate it. But I get that CMS is convenient so with that caching is where you get the performance win. A blog post either never updates or gets 1 or 2 edits max.
- deleted 4y ago[deleted]
- beberlei 4y agouse more than one SQLite file? we have one per day and project for example.
- rullopat 4y agoMy question is: what would happen if my server blows up while Litestream is still streaming to S3?
- whazor 4y agoI like the idea. It indeed sounds faster to redirect all write API's via your own proxy to a single write instance remote (or maybe multiple via sharding). Via Kubernetes you could have a cross region cluster that will deal with nodes going offline and like the author said, you would have a couple of seconds downtime with speeds nowadays. Which you could resolve by smarter frontends.
- vinay_ys 4y agoIn the past two decades we have done this enough times to know better. Here's what we know: 1. Compute and storage should be decoupled because the compute vs storage hardware performance increases at different rate over generations of hardware and if our application is coupled, then choosing an efficient shape of the server hardware is very difficult. 2. We know making a single server highly reliable is very difficult (expensive) but making a bunch of servers in aggregate reliable is much much easier. Hence, we should spread our workload on a bunch of servers to reduce the blast radius of any one single server failing. 3. We know making a single server very big (scale vertically) and utilise it efficiently is also very difficult (again, read: expensive). But using a bunch of smaller servers efficiently is relatively easier and more cost effective. Here, big vs small is relative at any given point in time – the median/average size server is whatever is most popularly used – hence it is mass manufactured and sold at volume-pricing-margins and popular software has caught up to use it efficiently (read: linux kernel and popular server software). 4. We know data is ever growing and application is ever more hungry to use more data in 'smart' ways. Hence, overall size of data upon which we want to operate is ever increasing. Hence scalable data architectures are very crucial to keep up with the market competition. (Even if you believe your app can be dumb and simple, the market competition forces will move you towards becoming more data 'smart'). 5. We know a lot of business models are viable only at huge scale of users. At smaller scales, the margins are so low that it isn't viable to operate. Again this is due to competition. Only scale operator survives. Hence, we know building architectures that doesn't scale to "millions of users" (even in enterprise software world) isn't viable anymore. 6. We know such scale brings more complexity – multi-tenancy, multiple regions, multiple jurisdictions etc. Internet world is becoming very complex, geo-politically etc. Multi-tenant usage based pricing models bring interesting challenges w.r.t usage metering, isolation, utilisation efficiency and security challenges. Multi-region and multi-jurisdiction brings interesting challenges w.r.t high-availability/continuity and traffic routing and cross-region data storage/replication along with encryption and key-management. 7. With all this, we have learned that layered architecture is critical to managing complexity while providing both feature agility and non-functional stability. Hence we know a lot of these complex capabilities should be solved by the lower layers in a reusable high-leverage way and not be tied to application layers. This is crucial for application layer to rapidly iterate on features to find product-market fit without destabilising these crucial non-functional core capabilities. 8. We know being able to refactor your application domain logic rapidly and efficiently is a super power for a startup hunting product market fit, for a big tech keeping up the innovation speed or any company in between just surviving the competition everyday. This refactoring super-power is crucial for keeping tech debt in control (and being able to take tech debt strategically) and not blowing up your engineering budget by having to hire like crazy (throwing bodies a the problem). We know all this..and more.. but I'll stop here... for now.
- mro_name 4y ago> The conventional wisdom could use some updating. how true in so many fields.
- obiwanpallav1 4y agoIn which scenario would you use litestream[1] vs rqlite[2]? 1 - https://github.com/benbjohnson/litestream https://github.com/benbjohnson/litestream 2 - https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
- otoolep 4y agorqlite author here. The way I think about it is that both systems add reliability to SQLite, but in addition rqlite also offers high-availability. Another important difference is that Litestream does not require you to change how your application interacts with the SQLite database, but rqlite does. Another way I think about it (I'm sure Ben may have other ideas!) is that if you want to add a layer of reliability to a SQLite-based application, Litestream will work very well and is quite elegant. But if you have a set of data that you absolutely must have access to at all times, and you want to store that data in a SQLite database, rqlite could meet your needs. Check out the rqlite FAQ for more. https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md#How-is-it-different-than-litestream https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md#How-...
- benbjohnson 4y agoLitestream author here. I agree with Philip. Litestream relaxes some guarantees about durability and availability in order to make it simpler from an operational perspective. I would say the the two projects generally don't have overlap in the applications they would be used for. If your application is ok with the relaxed guarantees of Litestream, it's probably what you want. If you need stronger guarantees, then use rqlite.
- otoolep 4y agoAgreed, they generally solve different problems. It's important to understand that rqlite's goal is not to replicate SQLite per-se. Its primary goal is to be the world's "easiest to operate, highly-available, distributed relational database". :-) It's trivial to deploy, and very simple to run. As part of meeting that goal of simplicity it uses SQLite as its database engine.
- nh2 4y agoThe article doesn't seem to discuss one of the most fundamental guarantees of current-day DB-application interaction: Acknowledged writes must not be lost. For example, if a user hits "Delete my account", and gets a confirmation "You account was deleted", that answer must be final. It would be bad if the account reappeared afterwards. Similarly, if a user uploads some data, and gets a confirmation (say via HTTP 200), they should be able to assume that the data was durably stored on the other side, and that they can delete it locally. Most applications make this assumption, and that makes sense: Otherwise you could never know how how much longer a client needs to hold onto the data until being sure that the DB stored it. This can only be achieved reliably with a server-side network roundtrip on write ("synchronous replication"), because a single machine can fry any time. The approach presented in the article does not provide this guarantee. It provides low latency by writing to the local SSD, acknowledging the write to the client, and then performing "asynchronous replication" with some delay afterwards. If the server dies after the local SSD write, but before the WAL is shipped, the acknowledged write will be lost. It will still be on the local SSD, but that is not of much use if the server's mainboard is fried (long time to recovery) and another server with old data takes over as the source of truth. This is why I think it's justified that some other commenters call this approach a "cache" when compared with a multi-AZ DB cluster doing synchronous replication. The Litestream approach seems to provide roughly the same properties as postgres-on-localhost with async replication turned on. (I also wonder if that would be an interesting implementation of this approach for Fly.io -- it should provide similar microsecond latency while also providing all features that Postgres has.) As I understand it, Fly.io provides Postgres with synchronous replication (kurt wrote "You can also configure your postgres to use synchronous replication", https://community.fly.io/t/early-look-postgresql-on-fly-we-want-your-opinions/537/44 https://community.fly.io/t/early-look-postgresql-on-fly-we-w...), and https://fly.io/docs/reference/postgres/#high-availability https://fly.io/docs/reference/postgres/#high-availability explains that it uses Stolon, which does support synchronous replication if you turn it on. But the "Postgres on Fly" page doesn't seem to explain whether sync or async is the default, and how exactly I can turn on sync mode on Fly. So I think it would be helpful if the article stated clearly "this is asynchronous replication", thus making clear that it will likely forget acknowledged writes on machine failure, and maybe link to Fly's Postgres offering that provides more guarantees.
- fareesh 4y agoFor me the dream seems to be a relational, real-time (with optionally configurable JSON/HTML snippet updates going to client applications), with extremely good latency, offline sync, etc. Bonus if the client can pick the fields it wants a-la graphql. Some sort of Rails + Hotwire + Firebase combination which works with web pages and apps alike.
- zsims 4y ago> It was reasonable to overlook this option 170 years ago, when the Rails Blog Tutorial was first written. Woah. Rails is really old
- faitswulff 4y agoThis whole article is written in an amusing way. It was really easy reading.
- splitrocket 4y agoThere are a couple of interesting options in a similar space: BedrockDB ( https://bedrockdb.com/ https://bedrockdb.com/ ) Dqlite ( https://dqlite.io/ https://dqlite.io/ ) Rqlite ( https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite ) I'm interested in how this performs and particularly, what are the tradeoffs relative to the other options above.
- benbjohnson 4y agoLitestream author here. The tl;dr is that Litestream trades operational complexity for reduced durability guarantees and increased write performance. Those 3 options mentioned use distributed consensus to ensure higher durability but that consensus also takes time so writes can be slowed. Litestream is an async replication tool so you can have a configurable window (1 second by default) where you could lose data if you have a catastrophic failure.
- DeathArrow 4y ago>SQLite isn't just on the same machine as your application, but actually built into your application process. When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds. That's micro, with a μ. A 50-100x improvement over an intra-region Postgres query. We will make up for those latency losses by throwing more microservices in our fat microservices architectures, add more message brokers in the flow. For sure will find a way to bring those milliseconds back. :)
- DeathArrow 4y agoWith SQLite you embed the DB in the application. If I have 6 Kubernetes pods and the pod containing the writer dies, all other 5 pods will be useless.
- InitEnabler 4y agoSQLite, has to be one of my favorite databases. It's always improving and the story behind it's creation is really quite something.
- my69thaccount 4y ago
- DeathArrow 4y agoIf we don't need SQL capabilities of SQLite, we can use the file system as a document database. Rsync will take care of replication.
- onetom 4y ago+1 for SQLite! I've used it from Clojure, via HoneySQL, so no ORM, no danger of SQL injection. It was really wonderful! https://github.com/seancorfield/honeysql https://github.com/seancorfield/honeysql I used it to quickly iterate on the development of migration SQL scripts for a MySQL DB, which was running in production on RDS. I might have switched to H2 DB later, because that was more compatible with MariaDB, but I could use the same Clojure code, representing the SQL queries, because HoneySQL can emit different syntaxes. Heck, we are even using it to generate queries for the SQL-variant provided by the QuickBooks HTTP API! :) https://www.hugsql.org/ https://www.hugsql.org/ it's pretty good too, btw! it's just a bit too much magic for me personally :) Also, you should really look into JetBrains database tooling, like the one in IntelliJ Ultimate or their standalone DataGrip product! It's freaking amazing, compared to other tools I tried. If you are an Emacs person, then I think even with some inferior shells to the command-line interfaces of the various SQL system, you can go very far a lot more conveniently, than thru some ORMs. Either way, one secret to developing SQL queries comfortably is to utilize some more modern features, like the WITH clause, to provide test data to your queries: https://www.sqlite.org/lang_with.html https://www.sqlite.org/lang_with.html You can use it to just type up some static data, but you can also compute test data dynamically and even randomly! Other little-known feature is the RETURNING clause for INSERT/UPDATE/DELETE: https://www.sqlite.org/lang_returning.html https://www.sqlite.org/lang_returning.html It can highly simplify your host-code, which embeds SQL, because you don't have to introduce UUID keys everywhere, just so you can generate them without coordination.
- DeathArrow 4y agoIn terms of CAP theorem, you give up consistency and partition tolerance, leaving only availability. For many, giving up consistency would be a big deal.