9 ms·
We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)
- michaelmior 9y agoCould (2016) be added to the title?
- deleted 9y ago[deleted]
- tomc1985 9y agoAs a postgresql nut, the people suggesting those other platforms (incl. pgsql) are idiots. Postgres in particular would be a bad idea unless he wanted to incorporate VACUUM() into his loading screen
- kimi 9y agoRelated note: in the last year I started using SQLite with my Clojure scripts instead of using a remote database, and I'm storing everything in git. Works like a charm for single-user tasks.
- kozhevnikov 9y agoDo you store the binary .sqlite in Git or source .sql and build the .sqlite?
- bachmeier 9y agoWouldn't tracking the binary with Git make a new copy on every commit?
- aphx 9y agoThe .db file can change, even if the data/records don't. One approach is to dump the sql to text and store that in git. (e.g. https://stackoverflow.com/a/846665 https://stackoverflow.com/a/846665)
- kimi 9y agoI store the binary; it's small. But of course you could use a filter, e.g. https://github.com/DataWraith/gitfilters https://github.com/DataWraith/gitfilters
- dividuum 9y agoCan confirm. SQLite is great. I've written an e-commerce website for a customers a few years ago. Most of the data was static and only updated a couple times per day in bulk. It was easy to do the main import/data update completely offline, then copying over the db file to multiple servers and doing mostly read-only queries on the DB. SQLite was more than fast enough and not having to manage another service was totally worth it. It's good having SQLite in your toolbox.
- jahvo 9y agoKudos to this guy. As the maintainer of an open source project, the hardest job is to tell people "your idea is stupid and I won't do it"
- glenda 9y agoThat seems pretty easy. It's much harder to try to understand where someone is coming from and engage with them in a positive way, even if you're rejecting their idea in the end.
- jimktrains2 9y agoEspecially since this (single-user music management application) seems to fit SQLite's use-case perfectly: * No external services required * Backups, when the application isn't run, is just a copy * Good SQL support I'm not even sure it's possible to get to the point where SQLite is going to fall over in this context (music collection metadata managment).
- mamcx 9y agoThe mistale here is thinking that a "fancier DBMS" MUST be off-process. I totally wish for a fancier DBMS that can be embebed (firebird fit here!) and work on mobile (but not here yet). And not only "fancier" in the limited sense that most believe. I truly mean A LOT FANCIER.
- dexen 9y agoMySQL Embedded[1] is a thing; used by some KDE apps. IIRC, it insists on using multiple files rather than a single file like SQLite does, but otherwise is a pretty much a turn-key component. No such luck with PostgreSQL so far. [1] https://www.oracle.com/mysql/embedded.html https://www.oracle.com/mysql/embedded.html
- simonw 9y agoSQLite is a fantastic database for desktop applications. https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html is a great essay on its benefits for this kind of use-case.
- redwolf2 9y agoNot only desktop, but most mobile platforms as well. Beeing coded in a single C file makes it portable as hell. Their documentation is also a prime example, with great insight.
- cheez 9y agoIt's not coded that way, it's combined as part of a script.fd
- fnord123 9y agoNot only desktop and mobile, but it can be used as the storage format for larger scale distribued systems. For example it's the on disk format for FoundationDB. >Beeing coded in a single C file makes it portable as hell. It's not coded in a single C file...
- simonw 9y agoIt kind of is... the SQLite "amalgamation" process takes all of SQLite's source code (across 100+ files) and concatenates it together into a single sqlite3.c file: https://www.sqlite.org/amalgamation.html https://www.sqlite.org/amalgamation.html
- fnord123 9y agoThat's horrifying and interesting. Thanks for linking it! I wonder if the alleged 5-10% performance gains are compared with usage of LTO.
- simonh 9y agoA fun trick is to ask a colleague at work how many SQL databases they think are in the room. They almost never bear in mind SQLite is heavily used on phones, not even just smartphones.
- kodablah 9y agoAbstract the persistence layer, document the contract, build a conformance suite, and write your one impl that uses sqlite. I know sometimes there seems to be an impedance mismatch between callers and databases, but often it's not that bad. And no, don't use an ORM or existing DB abstraction to do this. Sure it takes work, but pluggable storage is nice on some of these types of things.
- sametmax 9y agoCongratulation, you just rewrote the base for an orm.
- kodablah 9y agoSorry, put in my no-ORM edit as you may have been writing this. No, I mean higher level than that. ORMs suck as solving your problems specifically. Think more like an API than an ORM. I just started a side project and here's the beginning of an example of the iface I mean [0] and here's the beginning of an example of the sqlite impl I mean [1] (all untested and really early stage of course, but the idea is there). 0 - https://github.com/cretz/yukup/blob/eecb3b24b33f40e6a1eefaa5a3a6ebcd1d2cb084/yukup/storage/storage.go https://github.com/cretz/yukup/blob/eecb3b24b33f40e6a1eefaa5... 1 - https://github.com/cretz/yukup/tree/eecb3b24b33f40e6a1eefaa5a3a6ebcd1d2cb084/yukup/storage/sqlite https://github.com/cretz/yukup/tree/eecb3b24b33f40e6a1eefaa5...
- sametmax 9y agoBut they abstract the underlying db very well and have an uniform api that you can rely on. It's also tested, documented and battletested. It has drawbacks but if accepting several db is an important goal, you don't want to handcraft this unless you can measure inaceptable perfs.
- kodablah 9y ago> they abstract the underlying db very well I am admittedly unfamiliar with this specific case, but even a cursory glance showed me some sqlite-specific code embedded in the main library portion [0]. 0 - https://github.com/beetbox/beets/blob/4f7c1c9beda6c2a5b0705e868fb61e9b0af0e16d/beets/library.py#L107 https://github.com/beetbox/beets/blob/4f7c1c9beda6c2a5b0705e...
- z92 9y agoUnless for example, your run a background process which periodically imports data from an external source and inserts into sqlite database, while your web application continues to render pages for visitors based on existing old data. Your web page will fail, as it will time out on database lock because of that background process.
- icebraining 9y agoYes, that's what the article says.
- jasonjayr 9y agoYou'll want to turn on write-ahead logging for that scenario[1] [1]: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
- deleted 9y ago[deleted]
- laumars 9y ago> The idea is that a more complicated DBMS should be faster, especially for huge music libraries. How "huge" are they talking about? I built a tool that imports Apache logs into sqlite for quick analysis and that easily handles several million records.
- loeg 9y agoJust a ballpark: my beets DB is 15 MB for ~1000 albums (well, folders). This post[0] claims 10M albums ever released. So maybe 150 GB upper bound? Assuming my db has been vacuumed recently (~~probably not true~~ Edit: nope, still 15M after vacuum) and ignoring that indexes will scale slightly non-linearly. [0]: https://www.quora.com/How-many-music-albums-are-available-in-the-world https://www.quora.com/How-many-music-albums-are-available-in...
- laumars 9y agoI think 10m is far too low to be the total number of albums. But it would be impossible to actually calculate that figure given the number of producers that have released content outside the scope of any particular authority (eg NIN has released stuff to download only from their site. Aphex Twin uploaded a load of stuff to SoundCloud. Etc). But we digress. My music collection is massive. It's got 30 years of singles and albums in there. And several years of DJing too. Plus a massive amount of DJ sets (like several hundred gig of DJ sets alone). Yet XBMC / Kodi handles it fine "despite" being sqlite backed. So does Subsonic and that just uses some Java equivalent. And as I've said already, I can throw several million records of Apache logs into sqlite without any issue. The performance of sqlite is actually really good. It's super fast. Which is why the author can make those boasts on its website. Plus the compactness of the db (ie one file produced) and the ease to create a db makes it a perfect choice for desktop and mobile applications. I think some people read that WordPress runs on MySQL or spend 10 minutes building their first CMS in PostgreSQL and then think the world and their mother should be using this miraculous new RDBMS they've just discovered themselves. (I'm probably being too harsh there)
- loeg 9y ago
- nebulous1 9y agoSomebody clue me in on "asymptotically better database behavior"
- batmansmk 9y agoWe use SQLite in an app with about 1,000 active users. It took us: - 0h to manage backups ("cp"), - 0h to manage seeds and tests fixtures ("cp"), - 0h to configure and secure (void), - 0h to write the deployment scripts (void), - 0h monitoring/watchdog jobs (void), - 1h to rsync for failover ("rsync") My last projects always spent at least a good 100h to do all of this the right way. Then, if it is not good enough, we'll move to RDS or equivalent.
- jimktrains2 9y agoDo you have a write-heavy workload? How do you handle contention? Do you have multiple servers? How do you handle real-time filesystem sync? If you only have one server, how do you handle its inevitable failure? Do you loose any data collected between last backup and failure? (I'm not saying SQLite isn't good, because it's f-ing amazing. I'm just not sold on it as a multi-process, multi-user database.) (If you have a filesystem capable of atomic snapshots, then you can simply snapshot any database and treat the backup as a single file.)
- Boulth 9y ago> Do you have a write-heavy workload? How do you handle contention? I'd also be interested in that. Last time I wanted to use Sqlite opening twice the same file for writing either would not succeed or could time out.
- jacquesm 9y agoThat's because SQLite is meant for single user applications. If you try to open the file the second time it will wait to acquire the lock.
- WA 9y agoSingle process applications if I’m not mistaken. And somewhere in the docs it says: most writes take a few milliseconds at most, so even in a multi-process environment (say 5-10 PHP processes), it should be fine. Haven’t tried this though.
- wenc 9y agoThis is just a thought: SQLite is a really good file format. Why aren't we replacing CSV with it, especially for big data applications? CSV can be difficult and ambiguous to parse correctly (because there's no real standard) and isn't extremely performant. The only thing it has going for it is its universality. SQLite is lightweight, structured, supports indexing for performance and is extremely easy to use.
- bachmeier 9y ago> Why aren't we replacing CSV with it, especially in big data applications? I'm inclined to say we are. R, for instance, includes an embedded sqlite, and it seems to be used a lot. Related link: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html
- consto 9y agoUniversality is key. Everything reads it, everything writes it, everyone can read or edit it by virtually any piece of software. Compare that with SQLite.
- ju-st 9y agoI used it. SQLite can even import directly from CSV (as long as you're only have ASCII characters). Joining several CSVs and the first data cleaning steps took a second instead of 30 minutes with pandas.
- benoror 9y agoDon't underestimate the ability to see right away CSV contents using Excel/Numbers/Google Spreadsheets. Non-coders technical users
- noir_lord 9y agoI'm a coder and I still find the ability to eyeball a file useful, also csv can be munged about with the shell easily (cut, grep, sed etc) which can be surprisingly useful.
- 9y ago
- marknadal 9y agoI agree wholeheartedly with this article, despite being somebody who has written a "fancy" popular database system ( https://github.com/amark/gun https://github.com/amark/gun ). SQLite is good enough for pretty much everything, just use it!!!
- dang 9y agoYou've posted countless comments like this to promote your database. Often you include a lot of low-substance, barely relevant verbiage as packing, if not camouflage, for the promotion. That doesn't help. I don't say it lightly, but you're basically spamming HN, as users have been complaining for a long time (e.g. [1], [2]). Even when you don't appear to be doing it, you're doing it [3]. It's past time for this to stop; please stop. 1. https://news.ycombinator.com/item?id=16893429 https://news.ycombinator.com/item?id=16893429 2. https://news.ycombinator.com/item?id=16677493 https://news.ycombinator.com/item?id=16677493 3. https://news.ycombinator.com/item?id=16749503 https://news.ycombinator.com/item?id=16749503
- captain_perl 9y agoFYI: SQLite doesn't have ALTER TABLE.
- coldacid 9y agoAnd this pisses me off every time a schema change needs to be made in mobile apps I develop.
- coleifer 9y agoRename table, create new/modified schema, insert into...select from old.
- deleted 9y ago[deleted]
- tim333 9y agowell, "SQLite supports a limited subset of ALTER TABLE" https://www.sqlite.org/lang_altertable.html https://www.sqlite.org/lang_altertable.html
- makmanalp 9y agoWe use sqlite in data-heavy visualization website (http://atlas.cid.harvard.edu/ http://atlas.cid.harvard.edu/): We currently have an approximately 22GB sqlite file with the two largest tables going up to 150 million rows each. It still works just fine. The limiting factor seems to be the I/O throughput of the instance and disk of the server (EBS volumes). Read-only workloads, most of which eventually get cached by the webserver, but still. Each visualization ends up pulling a large amount of data (sometimes in the 10 thousands of rows) so the network / serde overhead and the administration costs of an external database server adds up, though things have changed recently so perhaps a revisit is in order. Where it hurts is: The query optimizer sometimes stumbles and does silly things (e.g. not always very smart about column / index selectivity statistics). The data import takes a long time (compared to e.g. postgres' COPY), so that's another pain point.
- infogulch 9y agoYou can help the optimizer stumbling by running ANALYZE [1] with representative data present, and turning off auto-analyze. This was intentionally designed as a feature so you could set up the statistics for a db, empty it, deploy it out to the end user and know that the optimizer isn't gonna go off and do something funky. [1]: https://www.sqlite.org/lang_analyze.html https://www.sqlite.org/lang_analyze.html
- joeschmoe3 9y agoIf you use Rust. https://crates.io/crates/rusqlite https://crates.io/crates/rusqlite
- octorian 9y agoI'm actually working on a project that's in the process of migrating from SQLite to MySQL as the preferred DB. I'll admit that the reasons we're doing it are probably not applicable to a desktop application like this. The real wins for us are multithreaded and multiprocess DB I/O. This is something MySQL can handle, but SQLite really isn't designed to do well or efficiently.
- ufmace 9y agoI am on the train that the idea of using SQLite for a webapp, even one where SQLite can handle the traffic just fine, isn't nearly as good of an idea as some would have you think. Nevertheless, the idea of switching a local end-user application off of SQLite in favor of a client-server RDBMS is bonkers IMHO. You would be making everything about the application dramatically more complex for no real benefit. This blog post is considerably nicer than the idea deserved.