11 ms·

Cross-Database Queries in SQLite

197 points by chmaynard 6y ago

mlthoughts2018 6y ago
Can anyone share some cool applications or bigger production systems built with sqlite? I love virtually everything I read about it, but I have never encountered a single use case where I am not better served by storing the data in some flat file (parquet, csv, etc.) and running a pandas program or simple Spark program to analyze it (locally on my laptop, or through a managed service like Dataproc or Databricks - it’s exceedingly cheap for any data size conceivably processable with sqlite). I am asking sincerely, what is the comparative advantage of sqlite or some example scenarios or trade off circumstances where sqlite is a comparatively more effective solution?
- duskwuff 6y ago
  Most flat-file formats I'm aware of can't be updated in place. SQLite databases can. SQLite is used throughout Chrome and Firefox as a storage format, e.g. for browser history, bookmarks, cookies, etc. It's also commonly used as a storage format on macOS, iOS, and Android.
  - mlthoughts2018 6y ago
    Once in memory, the flatfile’s data can be mutated in place, and creating the equivalent of a WAL-like system or oplog to protect against sudden failure & loss of in-memory data is a pretty easy part to solve.
    - tmpz22 6y ago
      Until you deploy this custom database of yours and customers start raising tickets reporting data loss on remote devices you can no longer access. Much of the value in SQLite is that it has achieved the level of clout that big players deploy it remotely to devices that are hard (or impossible) to debug without much second thought.
      - mlthoughts2018 6y ago
        We did deploy it. This whole architecture was chosen in an adtech business I worked in before, supported by data platform teams, analytics teams and more. The RFC process was intense and the decision was vetted with seriously heavy effort.
        deleted 6y ago
        [deleted]
    - tlb 6y ago
      I've done the flatfile thing several times on various projects, and I'm starting to appreciate the wisdom of letting sqlite handle it. Things it can handle correctly include: - crashes/power failures - multiple processes opening the same file - transactional updates: it rolls back if you throw an exception in the middle of writing the update. This seems like a common failure mode while under development - scaling to huge size: many gigabytes are no problem. These get slow with a flatfile - manual surgery on data: it comes with handy command line tools for manipulating its databases - upgrading format: you can check a version number when opening the file and do "alter table add column" for any new features.
      - mlthoughts2018 6y ago
        I feel like if I am worried about those issues, when would I ever use sqlite instead of Postgres? Sqlite seems like it is only appropriate for the knife’s edge boundary between small data, low reliability, in-memory situations (better served by server applications using tools like pandas or R) and bigger data, transactional structure, reliability constraints (better served by Postgres). I just can’t understand what use cases live in between them and are better served by sqlite.
        zetalemur 6y ago
        > I feel like if I am worried about those issues, when would I ever use sqlite instead of Postgres? Sometimes you do not want to run an additional process/daemon like postgres. Your state is in essence now a file on some file system that you can atomically (full ACID) update using multi processing without the need for more complex machinery - you can get very far with this architecture.
        CraigJPerry 6y ago
        For interactive use, sqlite queries are snappier than pandas at the ~4gb dataset size. I’ve switched over for some logfile analysis tasks and both memory usage and speed are a bit better. They’re not massive differences but i definitely appreciate the snappiness. Interactive work is just nicer that way. I could do postgres - the docker version is pretty handy i find but it’s just more faffing about (-v /path/to/wherever:/var/postgres password, cleanup when you’re done etc.)
    - duskwuff 6y ago
      > ...creating the equivalent of a WAL-like system or oplog to protect against sudden failure & loss of in-memory data is a pretty easy part to solve That's a pretty bold claim. Judging by the number of applications (including "real" databases) which fail to get this right, I'd have to say it's probably harder than you think. Besides, SQLite has done that work already, and has done so very thoroughly. I would definitely trust SQLite over something home-grown.
    - quietbritishjim 6y ago
      Sounds like you can't have multiple processes concurrently access it while it's being updated, even if the updates are small and infrequent (you'd have to load the whole database into memory and write it back out again if I've understood your comment correctly?). With SQLite you can have multiple processes access the same database and they can make changes to it that are immediately seen by the other processes. The nature of SQLite locks means this doesn't scale well if you have lots of processes all wanting to make heavy updates, but so long as you're not in that situation it works very well.
- jpau 6y ago
  Consider how inserts, updates, single-point lookups, indexes etc work with your analytical system. You use SQLite to back and operate an application. SQLite is a wonderfully lightweight transactional database; it compares more to e.g. MySQL than with OLAP systems like Spark. SQLite isn't competing for analytical use cases :)
  - mlthoughts2018 6y ago
    No, I meant using eg pandas even for that. We built an data annotation processing system at my last job like this. Literally customer specific csv files that got booted up and then an online flask service received restful updates of data annotations transactionally written back to the backing csv. Managing it as separate csvs per customer allowed some incredible optimizations for fanning out processing and performing reporting and dashboarding. The process running pandas allowed us to do much nicer aggregations, pivots, filters, etc., and by not writing it in SQL, we had so much more flexibility in application code and especially in unit & integration testing code. If data size per customer was going to grow substantially larger, we would have needed to migrate the workload to a backing SQL database, likely Postgres, but the nature of the problem meant this axis of data size was not a problem (every separate csv represented a completely isolated advertising campaign from a customer, with only up to a few million records per campaign). Using a flask server program to do this in pandas was an aspect that really, really paid off for us.
    - Groxx 6y ago
      One of the reasons to prefer SQLite even in simple cases is that "filesystems are hard" and "transactionally written back to the backing csv" has a ridiculous number of failure modes, many of which SQLite handles correctly: https://danluu.com/file-consistency/ https://danluu.com/file-consistency/ But in many cases, yeah, simple file use is good enough, as long as important stuff is backed up somewhere / a human can easily re-upload and repair anything needed. It's a <0.1% optimization, it really only saves you noticeable effort when you're doing like millions of those operations per day.
    - justsomeuser 6y ago
- codetrotter 6y ago
  Apple use SQLite in a lot of places, both in macOS and in iOS, and both in their applications and in their frameworks. For example, they use it in the Photos app on macOS and in the Photos app on iOS. Their Core Data framework is an abstraction that is built on top of SQLite, and is available for application developers on iOS, macOS, etc. Specifically about the Photos apps on macOS and iOS though, I was underwhelmed by the performance when you deal with 100,000+ pictures and videos. I don’t know if the sort of performance issues I was seeing was tied to SQLite or not though. My own solution to this has been to move away from using the Photos app on macOS all together, and to store my photos and videos in directory hierarchies instead. And to move photos and videos off of my phone as well, into the directory hierarchies on my hard drive.
  - tmpz22 6y ago
    Performance is an odd metric to judge a database, not because its unimportant but because its mostly in the developer's court if they are configuring and accessing data in a performant way. If the data is poorly indexed or queried its going to be slow no matter what database you use. Odds are the photos app is not configured well for your edgecase of having hundreds of thousands of photos. For the record I dislike SQLite - especially the cargo culting of SQLite by people who aren't even using it in production.
  - blackrock 6y ago
    Apple tries to force you to use the Photos app on the Mac to store your photos. But I never trusted it. Maybe because I didn’t understand what data model it was using behind the scenes. They don’t advertise that part. Even though for the most part it works pretty well. In fact, for most daily uses, the Photos app works almost magically. But once, after a few years of perfect usage, the program crashed. Then it started crashing more regularly. Then finally it completely died. Data corrupted. Completely unusable. Major system malfunction. I lost all my photos and videos in the Photos app. But because I was always paranoid of it, I had the original pictures backed up elsewhere. So data saved. But I never trusted the Photos app again.
  - mceachen 6y ago
    > I don’t know if the sort of performance issues I was seeing was tied to SQLite or not though It's certainly doable to serve million-asset libraries with SQLite (PhotoStructure is proof of this). You need to be careful with indexed queries and make sure you've set up a large enough RAM cache, but for PhotoStructure, almost all queries are kept under 10-50ms.
- dataflow 6y ago
  Android apps use SQLite all over the place... not sure if you're unaware of this or looking for different examples.
- FridgeSeal 6y ago
  Cross-language tools, cross platform as well, enforceable schema, far lighter weight whilst still being fast. More durable and supports ACID transactions.
- tyingq 6y ago
  Most web browsers use it for history. That seems like a good example. Browser history needs reliable single user read/write and decent query performance and flexibility.
- scottlamb 6y ago
  I use it in a network video recorder. https://github.com/scottlamb/moonfire-nvr/blob/master/design/schema.md https://github.com/scottlamb/moonfire-nvr/blob/master/design... Data in a flat file would be much less efficient, and a SQL server (eg PostgreSQL) would be more for the administrator to deal with. SQLite's a nice sweet spot. I've thought about using a key/value database (either a BTree one like SQLite uses under the hood or a LSM one like RocksDB). It'd be faster and less total code. But SQLite's too convenient to give up. The SQL interface is nice for debugging in particular. And it's already plenty fast enough, so my time's better spent adding some glaring missing features and better UI.
  - danbruder 6y ago
    this is a cool project!
- rakoo 6y ago
  I'm using magnetico (https://github.com/boramalper/magnetico https://github.com/boramalper/magnetico), a selfhosted bittorrent DHT scraper. After a few months of running I now have a little bit under 1M magnets in a ~3G database. SQLite is useful not just because I can query it with the full power of a relational database, but because I'm using its FTS capability for searching. If I had to install a typical database or some search engine I would never have used it. It is more than enough for what I'm using it for.
- nodamage 6y ago
  Sqlite is the de-facto persistent store for most iOS (Core Data) and Android (Room) apps.
- schwartzworld 6y ago
  > it’s exceedingly cheap for any data size conceivably processable with sqlite As most people do, you are severely underestimating how much data sqlite can quickly work with. I doubt that any csv based solution could compete.
- isodev 6y ago
  I regularly use SQLite as the database for production apps. User registrations, content posts, in-app purchase receipts etc. Up to hundreds of thousands of concurrent users it works just fine. It saves me a tone of time and complexity from setup I don’t have to do for a dedicated database engine. There is a good overview with general use cases here: https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html
  - mastre_ 6y ago
    I have one of the simplest toy services you can envision and am already running into a problem. Basically, I'm logging a temperature from a sensor to a SQLite db every minute or so (whenever it sends the reading). Sometimes I run a query manually thru the SQLite CLI to look into the data -- the query tabulates about 1.5 years' worth of data, by month and with averages, and can take about 20-30s as it's all running on an RPi3. As far as I can tell SQLite locks the entire table instead of just the records involved in the read, if one of my readings, new data to be inserted, happens to come in during me running the query I get "database locked" and the write fails. Yes this is a heavy query that would not make it to a production system, still I am surprised that load placed on a sqlite db by "hundreds of thousands of concurrent users" would not surface problems due to this simple detail.
    - jasonjayr 6y ago
      Have you tried journal_mode=wal? https://sqlite.org/pragma.html#pragma_journal_mode https://sqlite.org/pragma.html#pragma_journal_mode This lets sqlite read w/o a write lock (among other things) Since it stores the log to an adjacent file you have to make sure the process can write to the whole directory containing the db.
      - mastre_ 6y ago
        Will give that a shot, thanks!
        philipyoungg 6y ago
- austinjp 6y ago
  SQLite is so ubiquitous you'd be hard pressed to find an app or service that doesn't use it. Look through the licenses for the software included as part of your phone's OS. You'll find SQLite in there. Search GitHub for sqlite, there are several projects with thousands of stars that use SQLite. Here's one: https://github.com/Tencent/wcdb https://github.com/Tencent/wcdb
- jussij 6y ago
  SQLite not only makes it easy to store data, it makes it very easy to search that data.
- Kaze404 6y ago
  Conversely, I don't know of a situation where I'd use csv or json for storing data instead of SQLite. Maybe when the user is expected to change the data, but even then I think I'd rather make that part of the program depending on the target audience.
  - sep_field 6y ago
    Appending lines of json or csv data to a file is very, very fast. Also takes up much less space because sqlite can't keep pages completely full. Also sqlite becomes quite slow on large datasets. For some applications these properties make sense. I have hundreds of gigabytes of data that is only ever processed sequentially in a pipeline, I don't need random access -- for this use json/csv are fantastic.
- alfanick 6y ago
  Adobe Lightroom catalog - sqlite is used for lrcat, which can hold hundreds of thousands metadata of pictures and (non-destructive) edit sequences on those pictures. Some examples [0][1], or just play with it yourself. [0]: https://dphacks.com/2019/05/07/how-to-search-multiple-lightroom-catalogs-at-once/ https://dphacks.com/2019/05/07/how-to-search-multiple-lightr... [1]: http://regex.info/blog/2006-07-29/221 http://regex.info/blog/2006-07-29/221
getpost 6y ago
I'm using datasette[0] and sqlite-utils[1] on replit[2] for a project, which unfortunately I can't share for the time being. I've been very productive, and the low learning curve on this setup makes it easy for others to use and modify what I've done. I expect to have time to make a demo datasette project available on replit (and github) 'soon.' I'm so grateful for @simonw 's work on datasette and sqlite-utils! These packages are beautifully designed and documented, and they work so well! Thank you very much @simonw! EDIT: Of course, I am also grateful for sqlite, one of the best things ever written, not to mention this 'license:[3]' # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. [0] https://datasette.io/ https://datasette.io/ [1] https://sqlite-utils.datasette.io/en/stable/ https://sqlite-utils.datasette.io/en/stable/ [2] https://repl.it/ https://repl.it/ [3] https://sqlite.org/src/file/test/main.test https://sqlite.org/src/file/test/main.test
- simonw 6y ago
  Please let me know when you get your demo repo running, I'd love to see it! Particularly interested in seeing how you've been using it with repl.it.
CraigJPerry 6y ago
Datasette is actually fun to use. The examples I’ve tried have blown me away with how well they work. The github to sqlite utility, the geojson functionality for rendering boundary areas on a leaflet js map, the transform feature of sqlite-utils... it’s a great tool in the toolbox and so easy to get started: pipx install datasette datasette sqlite.db
- simonw 6y ago
  So glad to hear you're enjoying it! For anyone who has Homebrew but doesn't use pipx, "brew install datasette" works too: https://docs.datasette.io/en/stable/installation.html https://docs.datasette.io/en/stable/installation.html
5cott0 6y ago
Implemented a custom migration using this technique a few years ago, was one of those mind blowing "you can actually do that?" moments.
- polyrand 6y ago
  This sounds interesting, could you share more about the migration process and how you did it?
c-cube 6y ago
It's a really cool feature of sqlite. I use it in a prover benchmark tool [1] where each run produces a new sqlite file that I can rsync elsewhere. The tool can then list these files and connect to multiple files to do joins across them (to compare results from different runs). It's very convenient. [1] https://github.com/sneeuwballen/benchpress https://github.com/sneeuwballen/benchpress
- Cthulhu_ 6y ago
  It's used in the application I'm trying to maintain / rewrite as a backup / revert mechanism; the database migration will create and update two identical databases, on save the revert database is attached and data is copied over. On revert the data is copied back. I mean the author didn't use transactions or anything so if there was any issue while reverting the user would end up losing data, but it's clever I guess. Not the best though. I'm rebuilding it and will likely be using versioning instead.
  - thunderbong 6y ago
    That's a very interesting approach! Is this available publicly?
yread 6y ago
Has anyone here used SQLite on a web app with database file per tenant or even lower granularity? How fast does the attach work? Can you do transactions over multiple databases as well?
- laurent123456 6y ago
  I think that would make it hard to run queries across the whole dataset. You'll end up re-implementing features that are provided for free by proper rdbms like Postgres.
  - aytekin 6y ago
    That might be the goal. If you are storing private data for your users which should not be queried. You can have separate encryption keys for each user and keep their data on separate databases.
    - vidarh 6y ago
      We did pretty much that ages ago when I built a webmail provider. Marketing hated it, because it meant collecting user data took conscious effort which meant they had to ask the dev team, which meant requests were often turned down when hard questions were asked about whether it was justified to collate personal information. That was a feature, from my perspective. We actually took most user demographics data entirely offline. Data that was only intended to create aggregates anonymised profiles of our users were kept encrypted in bank box when not being analysed. Any analysis on it was done on an airgapped machine, and only the anonymised reports taken out. The only central database that was online was one that kept a mapping of whether or not a given user name was available. Then each storage shard kept track of which users were on which backend, and the account data that had to be online for each user (primarily their actual mail since we were a mail provider, along with settings etc. for their mailbox) were kept on a per user basis. It worked well, but you need to have buyin from the top for this approach, as there will be constant pressure for easing access to more and more user data.
      - 6y ago
deleted 6y ago
[deleted]

197 points by chmaynard 6y ago

mlthoughts2018 6y ago

Can anyone share some cool applications or bigger production systems built with sqlite? I love virtually everything I read about it, but I have never encountered a single use case where I am not better served by storing the data in some flat file (parquet, csv, etc.) and running a pandas program or simple Spark program to analyze it (locally on my laptop, or through a managed service like Dataproc or Databricks - it’s exceedingly cheap for any data size conceivably processable with sqlite). I am asking sincerely, what is the comparative advantage of sqlite or some example scenarios or trade off circumstances where sqlite is a comparatively more effective solution?

duskwuff 6y ago
Most flat-file formats I'm aware of can't be updated in place. SQLite databases can. SQLite is used throughout Chrome and Firefox as a storage format, e.g. for browser history, bookmarks, cookies, etc. It's also commonly used as a storage format on macOS, iOS, and Android.
- mlthoughts2018 6y ago
  Once in memory, the flatfile’s data can be mutated in place, and creating the equivalent of a WAL-like system or oplog to protect against sudden failure & loss of in-memory data is a pretty easy part to solve.
  - tmpz22 6y ago
    Until you deploy this custom database of yours and customers start raising tickets reporting data loss on remote devices you can no longer access. Much of the value in SQLite is that it has achieved the level of clout that big players deploy it remotely to devices that are hard (or impossible) to debug without much second thought.
    - mlthoughts2018 6y ago
      We did deploy it. This whole architecture was chosen in an adtech business I worked in before, supported by data platform teams, analytics teams and more. The RFC process was intense and the decision was vetted with seriously heavy effort.
      - deleted 6y ago
        [deleted]
  - tlb 6y ago
    I've done the flatfile thing several times on various projects, and I'm starting to appreciate the wisdom of letting sqlite handle it. Things it can handle correctly include: - crashes/power failures - multiple processes opening the same file - transactional updates: it rolls back if you throw an exception in the middle of writing the update. This seems like a common failure mode while under development - scaling to huge size: many gigabytes are no problem. These get slow with a flatfile - manual surgery on data: it comes with handy command line tools for manipulating its databases - upgrading format: you can check a version number when opening the file and do "alter table add column" for any new features.
    - mlthoughts2018 6y ago
      I feel like if I am worried about those issues, when would I ever use sqlite instead of Postgres? Sqlite seems like it is only appropriate for the knife’s edge boundary between small data, low reliability, in-memory situations (better served by server applications using tools like pandas or R) and bigger data, transactional structure, reliability constraints (better served by Postgres). I just can’t understand what use cases live in between them and are better served by sqlite.
      - zetalemur 6y ago
        > I feel like if I am worried about those issues, when would I ever use sqlite instead of Postgres? Sometimes you do not want to run an additional process/daemon like postgres. Your state is in essence now a file on some file system that you can atomically (full ACID) update using multi processing without the need for more complex machinery - you can get very far with this architecture.
      - CraigJPerry 6y ago
        For interactive use, sqlite queries are snappier than pandas at the ~4gb dataset size. I’ve switched over for some logfile analysis tasks and both memory usage and speed are a bit better. They’re not massive differences but i definitely appreciate the snappiness. Interactive work is just nicer that way. I could do postgres - the docker version is pretty handy i find but it’s just more faffing about (-v /path/to/wherever:/var/postgres password, cleanup when you’re done etc.)
  - duskwuff 6y ago
    > ...creating the equivalent of a WAL-like system or oplog to protect against sudden failure & loss of in-memory data is a pretty easy part to solve That's a pretty bold claim. Judging by the number of applications (including "real" databases) which fail to get this right, I'd have to say it's probably harder than you think. Besides, SQLite has done that work already, and has done so very thoroughly. I would definitely trust SQLite over something home-grown.
  - quietbritishjim 6y ago
    Sounds like you can't have multiple processes concurrently access it while it's being updated, even if the updates are small and infrequent (you'd have to load the whole database into memory and write it back out again if I've understood your comment correctly?). With SQLite you can have multiple processes access the same database and they can make changes to it that are immediately seen by the other processes. The nature of SQLite locks means this doesn't scale well if you have lots of processes all wanting to make heavy updates, but so long as you're not in that situation it works very well.
jpau 6y ago
Consider how inserts, updates, single-point lookups, indexes etc work with your analytical system. You use SQLite to back and operate an application. SQLite is a wonderfully lightweight transactional database; it compares more to e.g. MySQL than with OLAP systems like Spark. SQLite isn't competing for analytical use cases :)
- mlthoughts2018 6y ago
  No, I meant using eg pandas even for that. We built an data annotation processing system at my last job like this. Literally customer specific csv files that got booted up and then an online flask service received restful updates of data annotations transactionally written back to the backing csv. Managing it as separate csvs per customer allowed some incredible optimizations for fanning out processing and performing reporting and dashboarding. The process running pandas allowed us to do much nicer aggregations, pivots, filters, etc., and by not writing it in SQL, we had so much more flexibility in application code and especially in unit & integration testing code. If data size per customer was going to grow substantially larger, we would have needed to migrate the workload to a backing SQL database, likely Postgres, but the nature of the problem meant this axis of data size was not a problem (every separate csv represented a completely isolated advertising campaign from a customer, with only up to a few million records per campaign). Using a flask server program to do this in pandas was an aspect that really, really paid off for us.
  - Groxx 6y ago
    One of the reasons to prefer SQLite even in simple cases is that "filesystems are hard" and "transactionally written back to the backing csv" has a ridiculous number of failure modes, many of which SQLite handles correctly: https://danluu.com/file-consistency/ https://danluu.com/file-consistency/ But in many cases, yeah, simple file use is good enough, as long as important stuff is backed up somewhere / a human can easily re-upload and repair anything needed. It's a <0.1% optimization, it really only saves you noticeable effort when you're doing like millions of those operations per day.
  - justsomeuser 6y ago
codetrotter 6y ago
Apple use SQLite in a lot of places, both in macOS and in iOS, and both in their applications and in their frameworks. For example, they use it in the Photos app on macOS and in the Photos app on iOS. Their Core Data framework is an abstraction that is built on top of SQLite, and is available for application developers on iOS, macOS, etc. Specifically about the Photos apps on macOS and iOS though, I was underwhelmed by the performance when you deal with 100,000+ pictures and videos. I don’t know if the sort of performance issues I was seeing was tied to SQLite or not though. My own solution to this has been to move away from using the Photos app on macOS all together, and to store my photos and videos in directory hierarchies instead. And to move photos and videos off of my phone as well, into the directory hierarchies on my hard drive.
- tmpz22 6y ago
  Performance is an odd metric to judge a database, not because its unimportant but because its mostly in the developer's court if they are configuring and accessing data in a performant way. If the data is poorly indexed or queried its going to be slow no matter what database you use. Odds are the photos app is not configured well for your edgecase of having hundreds of thousands of photos. For the record I dislike SQLite - especially the cargo culting of SQLite by people who aren't even using it in production.
- blackrock 6y ago
  Apple tries to force you to use the Photos app on the Mac to store your photos. But I never trusted it. Maybe because I didn’t understand what data model it was using behind the scenes. They don’t advertise that part. Even though for the most part it works pretty well. In fact, for most daily uses, the Photos app works almost magically. But once, after a few years of perfect usage, the program crashed. Then it started crashing more regularly. Then finally it completely died. Data corrupted. Completely unusable. Major system malfunction. I lost all my photos and videos in the Photos app. But because I was always paranoid of it, I had the original pictures backed up elsewhere. So data saved. But I never trusted the Photos app again.
- mceachen 6y ago
  > I don’t know if the sort of performance issues I was seeing was tied to SQLite or not though It's certainly doable to serve million-asset libraries with SQLite (PhotoStructure is proof of this). You need to be careful with indexed queries and make sure you've set up a large enough RAM cache, but for PhotoStructure, almost all queries are kept under 10-50ms.
dataflow 6y ago
Android apps use SQLite all over the place... not sure if you're unaware of this or looking for different examples.
FridgeSeal 6y ago
Cross-language tools, cross platform as well, enforceable schema, far lighter weight whilst still being fast. More durable and supports ACID transactions.
tyingq 6y ago
Most web browsers use it for history. That seems like a good example. Browser history needs reliable single user read/write and decent query performance and flexibility.
scottlamb 6y ago
I use it in a network video recorder. https://github.com/scottlamb/moonfire-nvr/blob/master/design/schema.md https://github.com/scottlamb/moonfire-nvr/blob/master/design... Data in a flat file would be much less efficient, and a SQL server (eg PostgreSQL) would be more for the administrator to deal with. SQLite's a nice sweet spot. I've thought about using a key/value database (either a BTree one like SQLite uses under the hood or a LSM one like RocksDB). It'd be faster and less total code. But SQLite's too convenient to give up. The SQL interface is nice for debugging in particular. And it's already plenty fast enough, so my time's better spent adding some glaring missing features and better UI.
- danbruder 6y ago
  this is a cool project!
rakoo 6y ago
I'm using magnetico (https://github.com/boramalper/magnetico https://github.com/boramalper/magnetico), a selfhosted bittorrent DHT scraper. After a few months of running I now have a little bit under 1M magnets in a ~3G database. SQLite is useful not just because I can query it with the full power of a relational database, but because I'm using its FTS capability for searching. If I had to install a typical database or some search engine I would never have used it. It is more than enough for what I'm using it for.
nodamage 6y ago
Sqlite is the de-facto persistent store for most iOS (Core Data) and Android (Room) apps.
schwartzworld 6y ago
> it’s exceedingly cheap for any data size conceivably processable with sqlite As most people do, you are severely underestimating how much data sqlite can quickly work with. I doubt that any csv based solution could compete.
isodev 6y ago
I regularly use SQLite as the database for production apps. User registrations, content posts, in-app purchase receipts etc. Up to hundreds of thousands of concurrent users it works just fine. It saves me a tone of time and complexity from setup I don’t have to do for a dedicated database engine. There is a good overview with general use cases here: https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html
- mastre_ 6y ago
  I have one of the simplest toy services you can envision and am already running into a problem. Basically, I'm logging a temperature from a sensor to a SQLite db every minute or so (whenever it sends the reading). Sometimes I run a query manually thru the SQLite CLI to look into the data -- the query tabulates about 1.5 years' worth of data, by month and with averages, and can take about 20-30s as it's all running on an RPi3. As far as I can tell SQLite locks the entire table instead of just the records involved in the read, if one of my readings, new data to be inserted, happens to come in during me running the query I get "database locked" and the write fails. Yes this is a heavy query that would not make it to a production system, still I am surprised that load placed on a sqlite db by "hundreds of thousands of concurrent users" would not surface problems due to this simple detail.
  - jasonjayr 6y ago
    Have you tried journal_mode=wal? https://sqlite.org/pragma.html#pragma_journal_mode https://sqlite.org/pragma.html#pragma_journal_mode This lets sqlite read w/o a write lock (among other things) Since it stores the log to an adjacent file you have to make sure the process can write to the whole directory containing the db.
    - mastre_ 6y ago
      Will give that a shot, thanks!
      - philipyoungg 6y ago
austinjp 6y ago
SQLite is so ubiquitous you'd be hard pressed to find an app or service that doesn't use it. Look through the licenses for the software included as part of your phone's OS. You'll find SQLite in there. Search GitHub for sqlite, there are several projects with thousands of stars that use SQLite. Here's one: https://github.com/Tencent/wcdb https://github.com/Tencent/wcdb
jussij 6y ago
SQLite not only makes it easy to store data, it makes it very easy to search that data.
Kaze404 6y ago
Conversely, I don't know of a situation where I'd use csv or json for storing data instead of SQLite. Maybe when the user is expected to change the data, but even then I think I'd rather make that part of the program depending on the target audience.
- sep_field 6y ago
  Appending lines of json or csv data to a file is very, very fast. Also takes up much less space because sqlite can't keep pages completely full. Also sqlite becomes quite slow on large datasets. For some applications these properties make sense. I have hundreds of gigabytes of data that is only ever processed sequentially in a pipeline, I don't need random access -- for this use json/csv are fantastic.
alfanick 6y ago
Adobe Lightroom catalog - sqlite is used for lrcat, which can hold hundreds of thousands metadata of pictures and (non-destructive) edit sequences on those pictures. Some examples [0][1], or just play with it yourself. [0]: https://dphacks.com/2019/05/07/how-to-search-multiple-lightroom-catalogs-at-once/ https://dphacks.com/2019/05/07/how-to-search-multiple-lightr... [1]: http://regex.info/blog/2006-07-29/221 http://regex.info/blog/2006-07-29/221

getpost 6y ago

I'm using datasette[0] and sqlite-utils[1] on replit[2] for a project, which unfortunately I can't share for the time being. I've been very productive, and the low learning curve on this setup makes it easy for others to use and modify what I've done. I expect to have time to make a demo datasette project available on replit (and github) 'soon.' I'm so grateful for @simonw 's work on datasette and sqlite-utils! These packages are beautifully designed and documented, and they work so well! Thank you very much @simonw! EDIT: Of course, I am also grateful for sqlite, one of the best things ever written, not to mention this 'license:[3]' # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. [0] https://datasette.io/ https://datasette.io/ [1] https://sqlite-utils.datasette.io/en/stable/ https://sqlite-utils.datasette.io/en/stable/ [2] https://repl.it/ https://repl.it/ [3] https://sqlite.org/src/file/test/main.test https://sqlite.org/src/file/test/main.test

simonw 6y ago
Please let me know when you get your demo repo running, I'd love to see it! Particularly interested in seeing how you've been using it with repl.it.

CraigJPerry 6y ago

Datasette is actually fun to use. The examples I’ve tried have blown me away with how well they work. The github to sqlite utility, the geojson functionality for rendering boundary areas on a leaflet js map, the transform feature of sqlite-utils... it’s a great tool in the toolbox and so easy to get started: pipx install datasette datasette sqlite.db

simonw 6y ago
So glad to hear you're enjoying it! For anyone who has Homebrew but doesn't use pipx, "brew install datasette" works too: https://docs.datasette.io/en/stable/installation.html https://docs.datasette.io/en/stable/installation.html

5cott0 6y ago

Implemented a custom migration using this technique a few years ago, was one of those mind blowing "you can actually do that?" moments.

polyrand 6y ago
This sounds interesting, could you share more about the migration process and how you did it?

c-cube 6y ago

It's a really cool feature of sqlite. I use it in a prover benchmark tool [1] where each run produces a new sqlite file that I can rsync elsewhere. The tool can then list these files and connect to multiple files to do joins across them (to compare results from different runs). It's very convenient. [1] https://github.com/sneeuwballen/benchpress https://github.com/sneeuwballen/benchpress

Cthulhu_ 6y ago
It's used in the application I'm trying to maintain / rewrite as a backup / revert mechanism; the database migration will create and update two identical databases, on save the revert database is attached and data is copied over. On revert the data is copied back. I mean the author didn't use transactions or anything so if there was any issue while reverting the user would end up losing data, but it's clever I guess. Not the best though. I'm rebuilding it and will likely be using versioning instead.
- thunderbong 6y ago
  That's a very interesting approach! Is this available publicly?

yread 6y ago

Has anyone here used SQLite on a web app with database file per tenant or even lower granularity? How fast does the attach work? Can you do transactions over multiple databases as well?

laurent123456 6y ago
I think that would make it hard to run queries across the whole dataset. You'll end up re-implementing features that are provided for free by proper rdbms like Postgres.
- aytekin 6y ago
  That might be the goal. If you are storing private data for your users which should not be queried. You can have separate encryption keys for each user and keep their data on separate databases.
  - vidarh 6y ago
    We did pretty much that ages ago when I built a webmail provider. Marketing hated it, because it meant collecting user data took conscious effort which meant they had to ask the dev team, which meant requests were often turned down when hard questions were asked about whether it was justified to collate personal information. That was a feature, from my perspective. We actually took most user demographics data entirely offline. Data that was only intended to create aggregates anonymised profiles of our users were kept encrypted in bank box when not being analysed. Any analysis on it was done on an airgapped machine, and only the anonymised reports taken out. The only central database that was online was one that kept a mapping of whether or not a given user name was available. Then each storage shard kept track of which users were on which backend, and the account data that had to be online for each user (primarily their actual mail since we were a mail provider, along with settings etc. for their mailbox) were kept on a per user basis. It worked well, but you need to have buyin from the top for this approach, as there will be constant pressure for easing access to more and more user data.
    - 6y ago

deleted 6y ago

[deleted]