15 ms·

Appropriate Uses for SQLite

• allyjweir 9y ago
  This is one of my favourite technical sites. It is very clear and to the point about its good use cases while being clear where an alternative would be a better choice. In a similar vein, VueJS has a comprehensive exploration/comparison of itself compared to other frameworks[0]. I really appreciate open source maintainers that have the humility to compare their work fairly with others within the domain while still taking pride in their own acheivements. [0]: https://vuejs.org/v2/guide/comparison.html https://vuejs.org/v2/guide/comparison.html
  • majewsky 9y ago
    I'm trying to make a habit of adding a "Do NOT use if..." section near the top of my projects' READMEs whenever there are simple exclusion criteria. For example https://github.com/sapcc/swift-http-import#do-not-use-if https://github.com/sapcc/swift-http-import#do-not-use-if -- This particular instance was born out of watching users bending over backwards to use the software even though standard software does the job just fine or better in their concrete case.
  • JepZ 9y ago
    Yeah, sadly those comparisons are mostly feature driven. Coming from riot.js I have to say that while I was very happy with the features riot.js offered, I am kinda unhappy how the development is done lately (discussing the same issues over and over again, but nothing changes). So while I have no idea about the status of vue.js regarding that topic, I belief that even if they are just in a normal state it is a huge positive aspect in comparison to riot.js, but the whole issue might be a little subjective too.
  • abrookewood 9y ago
    Agreed. One of my pet peeves are projects that gleefully point out all of the issues that other projects face ('Not ACID compliant! Bound by CAP theorem! Difficult replication!') then present themselves as magical solutions free of all trade-offs.
• karmicthreat 9y ago
  Every once in a while I have sqlite eat it, and end up with a corrupt db file. About as often as mysql really. I wish I had some more serious automatic recovery in sqlite. I have to have a boot script check the db, export everything to an sql file if the db is corrupt then create a new file.
  • dijit 9y ago
    That does not mirror my experience, I hate to be the guy suggesting this but have you considered the fact that you're using it wrong? What I mean by that is that SQLite really is a replacement for open() and as such is not multi-thread compatible. If you're doing a lot of work that's corrupting you can also turn up the level of binary logging until recovery becomes easy enough. I'm using SQLite effectively on a few thousand multi-threaded windows machines and I've encountered corruption exactly once- which was when a hardware raid had degraded non-gracefully.
    • karmicthreat 9y ago
      Oh I guarantee I am doing something wrong. This is running on some crappy sandisk sd cards as well. So it is entirely possible that the corruption I run into is just an issue with the sd card.
    • jpeeler 9y ago
      My understanding is that using SQLite is completely fine in a multithreaded architecture, but you have to ensure that the simultaneous database operations match the correct threading mode: https://sqlite.org/threadsafe.html https://sqlite.org/threadsafe.html I also believe that the common way of gaining performance while protecting safety is to use multi-thread mode with connection pooling. The idea being that your application can use different database connections to achieve multiple writes without relying on the overhead of additional locking required within SQLite itself for serialized mode. Would be interesting if anybody has performance metrics that backs up what I said with data converging on an optimum number of database connections.
      • 9y ago
• petepete 9y ago
  > Dynamic content uses about 200 SQL statements per webpage. Genuinely curious, what kind of page would require 200 queries? I've written plenty of moderately-complex applications and don't think I've come close to 10% of that on a single (non-crazy dashboard) page.
  • TimWolla 9y ago
    It doesn't say "require". Possibly they are selecting the same information over and over again, instead of passing it along the different functions, because it is fast enough.
    • krylon 9y ago
      Even so, querying data once and reusing it is usually the more maintainable option and probably means less code, too. It is not only about performance. (And just to be clear, for my use cases, I have been very happy with SQLite's performance.)
      • hinkley 9y ago
        This requires an information architecture and these days those seem to be harder to come by. I think we may be favoring velocity over Stop and Think time a bit too much. (If you’re thinking, “no dummy, just use caching” then I regret to inform you that cache invalidation and eviction without an info architecture are as hard if not harder. And caching bugs make you look like a clown in front of your bosses. Because the whack-a-mole process never looks professional)
  • ju-st 9y ago
    Click on the linked site (or: https://sqlite.org/np1queryprob.html https://sqlite.org/np1queryprob.html ), the sqlite project explains it
    • petepete 9y ago
      Ah, thank you. Explains it perfectly.
• JepZ 9y ago
  Well, actually it is very simple when you understand the why SQLite performs differently than other database systems. The major difference is that 'normal' database systems have a process which receives and processes SQL queries and has the exclusive access to the database file. SQlite on the other hand doesn't have such a process so, every client has to open the database file itself, process the SQL and close/write the file again. So the biggest difference is, that if you want to use SQLite, you will have to limit your database operation to one or fewer ;-) processes to not get into a situation where one process has to wait until the other finished writing the database file to the filesystem just to read it again in the next moment. Otherwise your filesystem and the continuing reading and writing of the database file will make the whole thing very slow. But as long as you have only one process using the SQLite file and keep it open for a while you will see a comparable performance to other database systems. Actually, SQLite can be very fast if you do use it in a sane way.
  • anewhnaccount2 9y ago
    The key thing you omitted is you can have multiple readers at once. As long as you don't have really slow reads or writes and don't have a tonne of writes trying to queue up, nobody should get starved with database level locking.
  • beagle3 9y ago
    SQLite is fine with multiple processes as long as they are read mostly. And if you switch journal to WAL mode, it is as good any multiprocess DB without MVCC (which, depending on access patterns might be as good as it gets - although mist patterns do benefit immensely from MVCC)
    • dhd415 9y ago
      I'm not sure what you mean by "multi-process DB", but there are plenty of workloads in which SQLite under-performs non-MVCC databases. It is simply not designed for highly concurrent access patterns. In a previous job, I had to replace the persistence layer for a medium-load (100-500 events/sec) event tracking system from SQLite to SQL Server which is, by default, non-MVCC. The latter was considerably more complicated and considerably faster and more scalable than the former. I say this not because SQLite is not good. It's _great_ for what it intends to do which is, as the linked page says, offer an excellent alternative to fopen(). It doesn't really offer an alternative to a full DBMS, though. There may be some gray area between a "heavy fopen()" workload and a "light DBMS" workload where there might be a legitimate case to be made for SQLite over a DBMS, but standard OLTP or OLAP workloads with any degree of concurrency are almost uncertainly outside of SQLite's intended use case. There was a recent conversation on a similar topic with input from Richard Hipp, SQLite's author, here: https://news.ycombinator.com/item?id=15303662 https://news.ycombinator.com/item?id=15303662
• mwexler 9y ago
  I find this page to be somewhat vague. For example, any data that revolves around dates or time may require some addition hoops to jump through since, as https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html points out, "SQLite does not have a storage class set aside for storing dates and/or times." As much as I love SQLite, I find this little issue always adding an extra bit of work to deal with. Not a blocker, but useful to know. I've found that in some (rare) cases, H2 became a better solution (horrendous start time, slow data load, but ability to use real date and time types reduced errors and improved analyses). Finally, knowing that SQLite is not right for a situation is great, but it wouldn't hurt for them to mention a potential alternative, if the devs or community know of one.
  • le-mark 9y ago
    That's a valid observation, but the status of sqlite as a couple of ansi c file you can compile with or link to any damn thing is really what we're talking about here. And H2, awesome as it is, is no comparison.
  • mikeash 9y ago
    I don’t know if they want to be in the business of recommending alternatives. Doing so has the unfortunate tendency of making people think it’s your problem if you use them and they don’t work out.
  • kragen 9y ago
    For others who didn't know about H2, it's apparently an embeddable SQL database, like SQLite, but in Java: https://github.com/h2database/h2database https://github.com/h2database/h2database It's dual-licensed under the Mozilla Public License and the Eclipse Public License.
    • hinkley 9y ago
      It grew up arm in arm with Hibernate, one of the better ORM wrappers. Fast enough for local use that you could do functional tests against your domain objects without using mocks, or crying.
  • 9y ago
• virtualwhys 9y ago
  Sadly, in the browser SQLite has been kicked to the curb, replaced by IndexedDB. It's deeply disappointing to not be able to use SQL on both client and server. Yes, there's Lovefield, which employs its own DSL that has nothing to do with SQL, so you can scrap everything on the server and redo it on the client. Or just give up and go with NoSQL on client and server, that seems to be what the browser vendors are implicitly pushing developers toward.
  • dahart 9y ago
    > Sadly, in the browser SQLite has been kicked to the curb, replaced by IndexedDB Are you thinking of WebSQL? I don't remember a SQLite interface client-side in any browser. I might be wrong, but I thought IndexedDB was originally implemented on top of SQLite in both Chrome and Firefox. I think Chrome moved to a different backing store, but that has no bearing on the API.
    • virtualwhys 9y ago
      > Are you thinking of WebSQL? Yes, exactly, but Web SQL uses SQLite as the backing store. IndexedDB is its own thing, a standalone key/value store, nothing to do with SQLite.
      • gcp 9y ago
        IndexedDB may be backed by SQLite. I think it likely is in Firefox?
    • tejasmanohar 9y ago
      SQLite has been ported to the browser via Emscripten. I’ve seen it used in a couple niche projects (generally those that revolve around the SQL language), but I don’t remember it being a standard or even a primary use of SQLite. [0]: https://github.com/kripken/sql.js/ https://github.com/kripken/sql.js/
      • virtualwhys 9y ago
• gaius 9y ago
  Bulk loading a CSV or similar into SQLite for analysis with SQL statements is an incredibly productive technique. With a few lines of code you can load any log file, load and merge multiple logs by timestamp, whatever you like
  • piyh 9y ago
    And automate it easily.
  • chrisweekly 9y ago
    The amazing but criminally under-appreciated 'lnav' (https://lnav.org https://lnav.org) facilitates exactly this approach. SQLite under the hood.
    • flamtap 9y ago
      Oh, I hope there is a Windows-compatible way to use this (i.e.: in a .NET dev environment), if not now, then in the future.
      • tracker1 9y ago
        Would probably work under the Windows Subsystem for Linux (WSL), in Win10, or via Docker for Windows. I've done similar with node.js streams pretty easily.
• joshvm 9y ago
  I use SQLite for logging data from a bunch of distributed sensors. The server (RPi) sends a measurement request over MQTT, the sensors reply and the server logs each response grouped by the request timestamp. Seems to work very well for 25 sensors on the same WLAN and for continuous monitoring (about 100 measurements per minute). This can then be displayed to the user via a web server which has read-only access to the measurement db.
• fauigerzigerk 9y ago
  Sadly the lack of a decimal type complicates monetary calculations sufficiently for me to stay with postgresql even for small projects that could easily be done with SQLite.
  • BenjiWiebe 9y ago
    Why wouldn't you just store the value in cents? And divide by 100 to display it.
    • fauigerzigerk 9y ago
      As I said in my reply to asaph, it doesn't work well with interest rates and exchange rates. Also, it simply adds to the mental load in many places (e.g ad hoc queries)
      • TeMPOraL 9y ago
        And floats do? With all the (implicit) rounding and precision issues, and some base-10 numbers not being representable in an exact form? Serious question, what's the proper way to handle this case? I'm having hard time to believing it's IEEE-754 floats.
        • fauigerzigerk 9y ago
          No, binary floating point doesn't work at all. Decimal floating point can work if the representation is large enough.
        • icebraining 9y ago
          Arbitrary-precision numbers. DBs like Postgres support them, as do most languages (e.g. Ruby has BigDecimal, Python has Decimal).
          • aeorgnoieang 9y ago
            Neither `BigDecimal` in Ruby or `Decimal` in Python are quite what I'd expect for "arbitrary-precision". From the docs for [`BigDecimal`](http://ruby-doc.org/stdlib-1.9.3/libdoc/bigdecimal/rdoc/BigDecimal.html http://ruby-doc.org/stdlib-1.9.3/libdoc/bigdecimal/rdoc/BigD...): > Ruby provides built-in support for arbitrary precision integer arithmetic. For example: > > 4213 -> 1265437718438866624512 > > BigDecimal provides similar support for very large or very accurate floating point numbers. My takeaway from the above is that, if arbitrary precision is needed, you should still just use integers. Tho, in practice, it may not be significant.
• zbentley 9y ago
  > SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). I appreciate that there's just a hint of snark there. YAGNI, after all, is one of the most often-forgotten important principles in this industry.
• dgudkov 9y ago
  I wish SQLite was used for data exports from web-sites instead of CSV files. It would work better especially when the exported data is a some kind of star schema. I wrote about it here: http://bi-review.blogspot.com/2017/04/websites-should-offer-micromodels.html http://bi-review.blogspot.com/2017/04/websites-should-offer-...
  • copperx 9y ago
    That will happen as soon as Excel implements an SQLite import feature.
    • discreditable 9y ago
      Maybe when it gets Python support! :) https://excel.uservoice.com/forums/304921/suggestions/10549005 https://excel.uservoice.com/forums/304921/suggestions/105490...
• neals 9y ago
  What would be a bottleneck / limit for SQLite? If you have to many SELECT query because your site is too busy, what would happen?
  • odonnellryan 9y ago
    SQLite can probably handle a lot of SELECT queries at once, but only one process can be writing to the database at once. I think it's a hard question to answer, "what's the upper limit on SQLite?" Better question would probably be, "We have this app, what database should I use?" I use SQLite for a lot of smaller apps. Applications that have a focus on single-business, low-volume use. They might do a good amount of computation and data-fetching, but they don't make heavy use of the database. If you need features Postgres has, use that. If you need features MS SQL has, use that... if you don't need anything special, think about using SQLite :)
  • shoover 9y ago
    While not strictly related to computation, sqlite is a non-starter for deploying to PaaS without a persistent file system. "SQLite on Heroku" [1], essentially: don't do it, switch to postgres. I suppose it would work with AWS Beanstalk and EBS. [1] https://devcenter.heroku.com/articles/sqlite3 https://devcenter.heroku.com/articles/sqlite3
    • tracker1 9y ago
      If you're already in that environment RDS is probably a safer bet though.
  • andyjpb 9y ago
    I've benchmarked SQLite on my laptop at just north of 20,000 queries (SELECTs) per second for the workload I was interested in at the time. However, the limit depends heavily on your workload. If your working set (the data that all the simultaneous queries require) fits in memory then you'll be limited by memory bandwidth or the language you're calling from: GC stalls and query preparation can really hammer the throughput; if you're doing any actual work with the results of the query then I'd hope that time spent there would dominate the data access. If your data doesn't fit in memory then you'll be limited by the speed of your disk system. If you have a fixed working set then you can increase the performance by adding RAM until the working set fits again. If you have a streaming workload then you'll never be able to fit it in RAM (by definition) so you'll be limited by the speed of the IO systems involved; either network or disk. If things are still too busy then stuff will start to queue up. This will cause each individual process to run slower as it'll be contending with the others. This, in turn will cause more incoming work to queue up. At some point the machine will run out of some other resource (memory for incoming connections, network sockets, space in queues, etc). How the system responds to that depends on how it's configured but, traditionally, your system is no longer providing service.
• michaelmcmillan 9y ago
  I run a complex CRM that handles 1.2 million USD in transactions every year. It is running sqlite with a simple in-memory cache (just a dict) that gets purged when a mutating query (INSERT, UPDATE or DELETE) is executed. It is simple and fast enough.
  • simplify 9y ago
    Tell us more! How many transactions per second do you process? Do you use multiple databases?
    • pvg 9y ago
      If you assume each of these transactions is a dollar, you get a bit under 0.04 transactions/sec on average. If peak load is 25x average, that would come out to 1/sec.
  • neuland 9y ago
    Like the other commenter mentioned, I'd love to hear more about this too. How are you doing backups and deployment?
  • nerfhammer 9y ago
    is this single threaded? single client?
• pedrocr 9y ago
  I've recently used SQLite to store some metadata for a FUSE filesystem that pretends all your data is available but that just keeps a local cache and fetches it from the server on demand[1]. Initially performance was atrocious because INSERT is really slow if you don't batch several into transactions. I've worked around it with some application caching but there does seem to be a large gap between what the postgres engine can do and what sqlite can do. I definitely don't want to depend on a postgres setup for this application but it would be nice to have an SQL database for app use that had a bit more write performance. It seems that when you want that you end up going to LevelDB or LMDB and losing the niceness of an SQL interface. [1] https://github.com/pedrocr/syncer https://github.com/pedrocr/syncer
  • phaedrus 9y ago
    A lesson I've learned the hard way, from using Sqlite in many apps I've written, is that if you need any kind of insert performance you can't abstract away the transaction model, and retrofitting transactions onto an application which previously ignored them is not easy either. You really have to design for it up front. Mainly this is because there is at most one, global transaction per Sqlite database (in other words you can't nest transactions), but also because the optimum window to gather up inserts for a bulk transaction might not match up well with the logical function call structure of you program. I recently learned about the Sqlite SAVEPOINT feature; this might help implement nested-transactions, but cannot help with the second problem. For example, what if you made a "logging.db" file for a GUI application, and you're grouping writes to a log table in batches of 50 for each INSERT transaction. What happens if your GUI app gets to 48 inserts but then stops receiving user interaction events? You'd probably still want those 48 inserts to show up (eventually) in the logging.db rather than just being "lost" if the app subsequently crashes. So what do you do? Register a timer event to create a watchdog to flush the transaction if enough time goes by? OTOH what if a subroutine of your app is actually at that moment performing a lengthy "real" (logical) transaction and wouldn't want it flushed? Or maybe the application should use different database files for logging versus data where consistency is vital (so that a database can be opened either in "accumulate INSERTs" mode or "real logical transactions" mode)? My point is not the detail of how to answer those questions; it is just to highlight that these are questions which have to be thought about at a whole-application architecture level.
• Cyberdog 9y ago
  One of my favorite points about SQLite which hasn't been addressed yet in the comment thread is that its databases are just single flat files. Need to copy data from a server to a local dev machine? No need to log into the server, make a dump, copy the dump file to the local machine, and import the dump. Just copy the database file as you would any other file. That's it. Making a backup before trying something risky (and then restoring the backup when something screws up) is similarly trivial. Maybe to some of you it's not worth that much, but it's a legitimate timesaver in my opinion.
  • jjrh 9y ago
    This is the main reason I choose SQLite for simple stuff and experiments. Just being a file also makes it really easy to come back to a half finished project or pick up from another machine with a simple rsync.
  • PricelessValue 9y ago
    That's why it is sql "lite". It's great for certain things because it's generally lightweight and manageable. But you wouldn't want a single file format on enterprise level systems where enterprise level performance and stability is required. At the very least, you'd want your data and transaction log files to be different files on different disks. Especially when IO throughput is important. Also, since DBs tend to have important data on them, being able to copy a file (db) and move it anywhere you want isn't much of a selling point on serious systems. Having said that, sqlite is great for application/client side storage.
    • deleted 9y ago
      [deleted]
  • bullen 9y ago
    Try https://github.com/tinspin/rupy/wiki/Storage https://github.com/tinspin/rupy/wiki/Storage it stores each value (not column/row/table or database) as a single JSON file! Make sure you "mkfs.ext4 -T small"! ;) Upside is tar+gzip compresses the db 100x!
• drej 9y ago
  I may have gone a bit beyond what SQLite is useful for, but I just wanted to try this. I built an experimental application-level file system (so not FUSE-like, you can't mount this, it's just within an application). https://github.com/kokes/sqlfs https://github.com/kokes/sqlfs Haven't had much time to finish it, but it's been fun to play with.
• Decabytes 9y ago
  I love SQLite and am currently working on a project that makes extensive use of it. In the book "The Definitive Guide to SQLite" it says that SQLite can scale to 2TB of data Looking at the website it says An SQLite database is limited in size to 140 terabytes (247 bytes, 128 tibibytes). That's ludicrous. I wonder if anyone has come close to that in day to day use.
  • fnord123 9y ago
    Almost certainly not seeing as the single hw largest device is currently 12TB. Someone would have to pool together 10+ (and more for redundancy) just to store the single file.
    • krylon 9y ago
      I have heard of people running ZFS pools that were hundreds of TB in size. Not sure if they were using those for huge SQLite databases. ;-)
  • _kst_ 9y ago
    I presume "247 bytes" is supposed to be "2^47 bytes".
• hawski 9y ago
  Could anyone using SQLite FTS share their opinion/story? I'm especially interested in indexing hundreds of gigabytes.
  • deleted 9y ago
    [deleted]
• andyidsinga 9y ago
  I've used sqlite as an index for S3 objects ..and stored the DB file in S3 itself. It was very practical to use, and a handy pattern to apply to object storage where the number of objects to be indexed for a given time frame was reasonable for sqlite (I had in 100k range). Also - easy to dump the sqlite db into a SQL text file then import into something like postgres :)