8 ms·
SQLite is not a toy database (2021)
- Aperocky 4y agoShameless plug: https://github.com/Aperocky/sqlitedao https://github.com/Aperocky/sqlitedao For an out of the box python dao/orm with sqlite and deal with no SQL statement/strings.
- thrdbndndn 4y agoA small suggestion: explicitly write out `import`s in examples. I know it sounds trivial but from my experience it really confuses people especially when some classes have generic names like `ColumnDict`.
- Aperocky 4y agoVery much appreciate the feedback!
- sharmin123 4y ago[dead]
- Subsentient 4y agoFinally, a software fad that I actually want to support! About time I get one! SQLite is more than good enough for an awful lot of things. Most of the time that I do see software using MariaDB etc, I think to myself "they didn't need a server model for this, SQLite would have been fine".
- rawoke083600 4y ago>Finally, a software fad that I actually want to support! About time I get one! Haha I'm waiting for those "SQLite in Rust Posts" :D
- bigiain 4y ago“SQLite in Rust compiled to WASM in the browser.” ISAGN…
- pletnes 4y agoClose https://github.com/simonw/datasette-lite https://github.com/simonw/datasette-lite
- weq 4y agoalready done in .net, u just need a small rust boostrapper and ur set https://www.youtube.com/watch?v=kesUNeBZ1Os https://www.youtube.com/watch?v=kesUNeBZ1Os
- lostmsu 4y agoStill no official TFM (e.g. net7.0-wasm) for WebAssembly.
- lionkor 4y agoWould love to see someone rewrite SQLite in Rust - it's always the example I go to for full-coverage, fuzzed to hell and back, code. IMO there is nothing a new language could improve. It really is insanely stable and well tested.
- latchkey 4y agoIt certainly has some good use cases and is not a toy, but there are some critical SQL features (like say: `last_at < NOW() - INTERVAL '10 minutes'`) that are not easily compatible with SQLite and are with postgres, that would require a lot of code rewrite in case you need to scale upwards. Testing against multiple databases is hard, so migrating to postgres wouldn't be trivial either. Hosting small postgres databases on things like GCP are ~$8 a month... why not just save yourself from future pain?
- 2muchcoffeeman 4y agoWeird comment. Don't use a DB that doesn't fit your use case. In this case if you plan to scale or want PGSQL features, don't use SQLite. But SQLite is great if you need to embed a db in an app or are just doing some local stuff.
- seabrookmx 4y agoWhere do you draw the line with compatibility? There's lots of small, DB specific differences like this between all the databases (mysql, sql server, postgres etc). For this particular one, you can replace interval with something like `last_at < DATETIME(CURRENT_TIMESTAMP, '-10 day')` > Hosting small postgres databases on things like GCP are ~$8 a month... why not just save yourself from future pain? I think this is a fair point, if you think you'll run into sqlite's limitations in the future. litestream and friends make sqlite an option for some non-embedded use cases.. though they're hardly a panacea.
- vonseel 4y agoMoreover, many people are using ORMs which are going to hide the differences in a simple datetime query like that behind their query compilers.
- Gigachad 4y agoA lot of devs seem to have an obsession with using the tech that only just works for the use case. Like you pointed out, Postgres works in everything from tiny apps to global scale SaaS. I really just can't see any reason you would want to use SQLite outside of local apps. Sure, many web apps can run on it, but they can all work on Postgres.
- bobek 4y agoCheckout quite interesting replication projects https://github.com/superfly/litefs https://github.com/superfly/litefs and https://github.com/benbjohnson/litestream https://github.com/benbjohnson/litestream
- mythz 4y agoCan attest to this, we've been pleasantly surprised by the unbeatable performance and value of SQLite + Litestream combo [1] that we expect it will become a popular choice for small/medium or multi-tenant Apps in future. [1]: https://docs.servicestack.net/ormlite/litestream https://docs.servicestack.net/ormlite/litestream
- jrockway 4y agoAlso worth mentioning rqlite: https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
- otoolep 4y agorqlite author here, thanks for the mention, happy to answer any questions.
- 1MachineElf 4y agoToday I opened up the Windows Resource Monitor to see why my elderly neighbor's AOL Desktop Gold app was loading so slowly. The problem turned out to be network related, but I was intrigued to see in it's disk activity many reads and writes to a .sqlite file. I wonder how long AOL's desktop app has been using SQLite - since the early 2000s maybe? If anyone has one of those original AOL disks lying around, it may be interesting to see if the installer unpacks a SQLite DB of some kind.
- chasil 4y agoAOL was one of the first large corporate users of SQLite. "... the next tech giant to reach out was America Online... They needed a database on that [AOL] CD, and they had some ad hoc thing and they wanted to use SQLite on that. They had limited space, and so, “Hey, we need to put this on the CD.”" https://corecursive.com/066-sqlite-with-richard-hipp/ https://corecursive.com/066-sqlite-with-richard-hipp/
- ryanschneider 4y agoWe used SQLite to store the time series data used to show network activity in McAfee.com Personal Firewall circa 2002 or so. Anyways,acording to the Wikipedia page for SQLite AOL funded the development of at least some of SQLite 3 in 2004. I remember that we didn’t upgrade right away, or if we ever did for that matter.
- beebmam 4y agoDoes SQLite still not support remote connections? Without that, it's hard for me to take it seriously for most RDBMS use cases.
- ok_dad 4y agoSQLite is an embedded database so you wouldn’t really want that. Postgres is fine for database servers, SQLite is for program-embedded local databases.
- Gigachad 4y agoI don't think anyone has questioned sqlite for local databases. But there is a lot of talk around using sqlite for web app databases where it may work, but seems clearly inferior to any of the alternatives.
- Aperocky 4y agoDepend on what size the webapp is. sqlite.com seems a pretty good cut off traffic volume, I would guess between 95-99% of all web apps are probably consistently getting less traffic than sqlite.com
- chrsig 4y agois sqlite.com a dynamic site? it seems like it's static html
- Aperocky 4y agosupposedly they are powered by sqlite, so it has to be read somehow. Though a cache may have been what most of the traffic is hitting.
- rawoke083600 4y agoI read somewhere on their page, each page is done with about 200 queries.
- deleted 4y ago[deleted]
- losfair 4y agoCame over this article as I was looking for interesting resources in the SQLite ecosystem. I'm building mvsqlite (https://github.com/losfair/mvsqlite https://github.com/losfair/mvsqlite), as an attempt to turn SQLite into a proper distributed (not just replicated) database. Check it out if you are looking for this kind of stuff!
- WaxProlix 4y agoWhy ?
- mythz 4y agoWhy not, looks like a great project for people building on SQLite who may want to migrate to a distributed solution who determine it's a better solution than replication for their use-case.
- losfair 4y agoSQLite is a powerful SQL query engine. When combined with a rock-solid distributed storage layer, we get a distributed SQL database, just like what systems like Aurora and Neon have managed to build on MySQL and PostgreSQL.
- WaxProlix 4y agoSeems reasonable. I wonder how it compares to existing things in the space? It'd be cool to bring the test suite into the distributed world as well, I guess.
- deleted 4y ago[deleted]
- benhoyt 4y agoI presume you're familiar with https://github.com/canonical/dqlite https://github.com/canonical/dqlite (made by my employer) and https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite (unrelated)? How will mvsqlite compare to those?
- emptyparadise 4y agoI wish there was some sort of an Access-like form/app builder tool for quickly building GUIs around SQLite databases. That's the one thing I miss. I'd love to reach for SQLite instead of Excel or something.
- nvrspyx 4y agoI believe that Libreoffice Base supports SQLite.
- rascul 4y agoIt does https://wiki.documentfoundation.org/Documentation/HowTo/Base/Connect_to_SQLite https://wiki.documentfoundation.org/Documentation/HowTo/Base... Although it could use some more Linux in that link.
- Closi 4y agoNot SQLite, but try Retool with their new RetoolDB (which is an integrated version of Postgres). This totally fills the Access gap for me, assuming that you are looking for / happy with a web interface and a SaaS model which is paid per user (but free for first 5)
- krageon 4y agoIf it's SaaS, it's entirely different from the excel use-case (which is unfortunately ubiquitous and offline). It's also different from the average SQLite use-case, which is also local.
- Closi 4y agoFor my use-cases it’s mostly replaced Access. If your use case requires everything to be local and offline it won’t work, but if you can be online it’s a great replacement.
- yellowapple 4y agoLibreOffice Base?
- me551ah 4y agoLack of native support for SQLite in modern browsers is what is holding it back.
- samwillis 4y agoSQLite not being an “official” browser api was the right choice. It would have tied to closely to a specific version and limited it’s possible use with no ability to use extensions. SQLite is exactly what WASM is good at, now the only thing holding SQLite/WASM back is a low level filesystem/block store api. But this is coming soon! It will enable so much more than SQLite too, you will be able to use custom SQLite builds with extensions, and other databases (DuckDB, MongoDB/Realm and others)
- nikeee 4y agoThere was WebSQL, which aimed to be an SQL database as a JavaScript browser API [0]. Turned out, every browser used SQLite as an implementation backend which lead to the deprecation of WebSQL. [0]: https://en.wikipedia.org/wiki/Web_SQL_Database https://en.wikipedia.org/wiki/Web_SQL_Database
- mrwnmonm 4y agoI love the style of this blog.
- rdwnsh 4y agoIt's running on Jekyll with the default styling, I remember creating one for myself that I have completely abandoned and this just reminded me of it!
- endisneigh 4y agoI like SQLite (use it for android apps), but there’s no reason to use it for apps over a network. SQLite is meant to be embedded (also works great with desktop apps and local-only web apps). In fact, I’d argue there’s no networked use case in which SQLite is cheaper, easier to deploy, maintain or run than Postgres. All that being said, absurdsql is great. There should be some optimizations in mirroring network state to IndexedDB that could be queried via SQl(ite). The SQLite documentation literally says use Postgres and not it for networked use cases (unless you use WAL or rollback, in which case why not just use Postgres?) https://www.sqlite.org/useovernet.html https://www.sqlite.org/useovernet.html —- As an aside, I think we need a new storage primitive. In the 2000s desktop apps were rampant and SQLite was more or less the standard. We then moved over to networked apps where RDBMS had its day. There was a moment where nosql was booming for the scaling but it turned out people like relations. We need an open source, distributed and relational store that’s easy to maintain build this decade.
- fragmede 4y agoI mean, Amazon and the rest of the cloud vendors will happily sell you managed SaaS database that's open source, distributed and a relational store. That makes it real easy to maintain. Cheaper than a team of DBAs, too, until it isn't.
- tptacek 4y agoFor some very real modern full-stack workloads, the network is the problem. It implies a separate database tier, and network latency for every query. The latency is managed by careful query design, but a valid alternative to that work is to move the database closer to the app --- within NVMe latency rather than network latency. A key part of this idea is recognizing that reads have different requirements than writes, and that many applications are overwhelmingly read-intensive.
- WelcomeShorty 4y agoI found the key part of getting your application up to speed is 80% of the time, the query itself. Using stored procedures, bulk insert and the like, have speed up more "slow queries / databases" then anything else I've seen. Of course, if your coder already knows and does this, your last resort will be moving the DB closer to the app (the last 20% of optimisation).
- deleted 4y ago[deleted]
- gigatexal 4y agoHaha I love this. It’s a love letter to SQLite. And what’s fun read it was.
- didip 4y agoHas anyone seen rqlite? Someone slapped a raft layer on top of sqlite statements. Now you have a network layer on top of sqlite.
- otoolep 4y agorqlite author here, happy to answer any questions.
- re-lre-l 4y agoOne more thing to say about SQLite - is a simplicity of writing unit tests. Like this stuff. But my major concern is the file locking.
- LAC-Tech 4y agoAgreed, being able to easily spin up an in-memory database for fasts tests is incredibly useful.
- deleted 4y ago[deleted]
- JaggerFoo 4y agoI started to use Postgres on AWS for a personal project, after fighting with several non-docker VM's - I don't want to use docker. But I decided on SQLite + Litestream, hopefully for free on AWS. The simplicity of deployment is great. SQLite to my surprise has much of the same SQL functions as Oracle and Postgres. I wanted to use window functions, which are supported. Sqlite came to my attention when I read a Tailscale blog article stating they will be using it.
- dang 4y agoDiscussed at the time: SQLite is not a toy database - https://news.ycombinator.com/item?id=26580614 https://news.ycombinator.com/item?id=26580614 - March 2021 (354 comments)
- incomingpain 4y agoSqlite has really 1 big problem in my mind. 1 thread at a time. If you try to have 2 threads throwing stuff at it. _sqlite.OperationalError: database is locked This is what makes it a toy database. Mind you, sqlite will handle most projects, but those are also toy projects.
- adamckay 4y agoIs this not resolved with WAL mode? You can't have concurrent writes, but they can go into a "queue" so as long as you have fast writes and suitable timeouts they'll still be committed. So whilst they're not technically concurrent, as far as a human using your app will be concerned it will feel concurrent because they were able to write at the same time as someone else, just at an imperceivable fraction of a second slower.
- incomingpain 4y agoThere are a few options that make it possible do get around db locks but none of them worked for me.
- emadda 4y agohttps://table.dog https://table.dog is a CLI that downloads your Stripe account to a SQLite db. Would appreciate it if you could test it out if you are interested.