20 ms·

SQLite may become foundational for digital progress

165 points by alexrustic 4y ago

ProtoMathics 4y ago
Just another filesystem container.
- kumarvvr 4y ago
  But one you can query and play around with. Its absolutely phenomenal as a custom file type for your applications.
adamnemecek 4y ago
Can’t wait for something better to come along. The text orientedness of SQL is annoying. Something like Linq without the underlying SQL.
- jdmichal 4y ago
  Isn't "LINQ without the underlying SQL" simply list comprehensions?
  - adamnemecek 4y ago
    Well plus serialization that’s not SQL.
  - pjc50 4y ago
    Microsoft call it "LINQ to objects": https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/linq-to-objects https://docs.microsoft.com/en-us/dotnet/csharp/programming-g...
- smitty1e 4y ago
  Use an object-relational mapper and leave the boring stuff to a library.
  - tehlike 4y ago
    Sadly the choice of sql tends to make underlying framework very complicated and sometimes incapable. Some things got easier to express with SQL after Json_agg in postgres became a thing (now you can do multi-depth relations without blowing up the result table with Cartesian product) but it's still massive pain in the ass and Json agg has performance overhead too.
    - smitty1e 4y ago
      Especially for reporting, I'm just as happy to write a serious piece of SQL and execute it directly. This is the joy of SQLAlchemy: great features when you want to use them; escape hatches when you don't.
      - tehlike 4y ago
        Right, most ORM tools give an escape hatch to execute raw SQL queries. However, even though SQL is turing complete, it's not a great language for retrieval imho. It's probably an artifact of the underlying relational models (and the relational output it has to generate) but still.
        smitty1e 4y ago
        I was all set to disagree with you, but a bit of googling yields => https://localcoder.org/is-sql-or-even-tsql-turing-complete https://localcoder.org/is-sql-or-even-tsql-turing-complete
        tehlike 4y ago
        It could have been wrong, but main argument was whether SQL was a good language for retrieval or not, and for me that's a no. It's probably subjective though :)
  - __MatrixMan__ 4y ago
    I hate ORMs. After days of fumbling in the dark you finally isolate the problematic query, post it like "guys I found it," and still nobody speaks up. So you hack the ORM to attach tracebacks as comments to each query, retest everything, and finally git-blame your way to somebody who owns that bit of code and they're always like "wait, the ORM did WHAT?" I'd rather grep code for SQL any day.
- beached_whale 4y ago
  I know not what you ment but just mentioning that if one uses entity framework or linq to sql in .net, linq should work with sqlite.
- tehlike 4y ago
  Amen. I made this point over and over. Sql is fairly annoying to deal with, linq is very close to me being perfect, prql is close https://github.com/prql/prql https://github.com/prql/prql
- boyter 4y ago
  The thing I always liked about linq was it’s lazy evaluation. You could write simple selects on your data model and join and filter in the service. Allowing unit tests that operated on the joins. I’m yet to find a better way to write crud apps than that pattern.
smitty1e 4y ago
> “I think the biggest complication for us is that there’s no tooling for it.” said Mackey. “We have people deploy apps. They’re like, ‘How do I connect to my database and like query things? How do I import data?’” Wut? Every scripting language includes a SQLite driver. Python's SQLAlchemy wraps SQLite beautifully. You can develop locally and then deploy to an enterprise server with a simple change of connection string. I must be missing the speaker's point.
- nicoburns 4y ago
  I think they mean GUI tooling for ad-hoc inspection of the database. And possibly the stumbling block is difficulty accessing it over a network?
  - smitty1e 4y ago
    https://sqlitebrowser.org/ https://sqlitebrowser.org/
  - stefanos82 4y ago
    I prefer https://sqlitestudio.pl/ https://sqlitestudio.pl/ to be honest with you.
    - edwinyzh 4y ago
      I've tried over 15 sqlite management tool, but SqliteStudio is my favorite! And recently discovered https://github.com/little-brother/sqlite-gui https://github.com/little-brother/sqlite-gui
  - 41b696ef1113 4y ago
    I personally love DBeaver for SQL, but I did stumble across a thats-not-how-it-was-intended-for-usecase for Datasette [0]. Datasette is intended to be a way to share static SQLite databases. However, I was recently debugging a Django application where I lacked appropriate tooling to inspect what was happening in the database. However, I am a huge fan of Datasette, so why not? While the Django debug server was running, I was able to launch datasette in a separate terminal and have a live view into the database while the application was free to mutate the underlying data. You can even write custom SQL inside the browser to select/modify any interesting data. [0]: https://datasette.io/ https://datasette.io/
cropcirclbureau 4y ago
Interesting usage of the word folklore.
- Tao332 4y ago
tiffanyh 4y ago
The answer is much simpler. When people created desktop apps - they needed a datastore that was simple and reliable to use. SQLite was perfect for this. Now, no-one is creating desktop apps anymore (everything is a web app), but people still need a simple & reliable datastore. Even though SQLite has historically advertised to not be used for client/server, people are finding that SQLite works great for most web apps as well.
- marpstar 4y ago
  "Native Mobile Apps" are the new "Desktop App". They can both be client apps to a remote server/datastore, or not. "Desktop Apps" didn't go away, the internet-connected apps just overtook them.
- luvs2code 4y ago
  I really miss desktop apps. They worked so well and didn't have half the problems that come with web apps. I'm working on a project, for fun not for money, that uses Golang and talks to a SQLite database for things like accounts and user content. So far I really enjoy writing it a lot but I have to admit that it would probably have issues if it scaled to a large number of users. In fairness to SQLite it was designed in a different era technologically. Maybe there will be a replacement for the web app era if we distill what we like about SQLite
- NonNefarious 4y ago
  My company just finished a new desktop app and will continue to produce more.
  - brokenmachine 4y ago
    Bless you. So sick of cloud everything.
endisneigh 4y ago
I've yet to see how you can use SQLite for a multi-user multi-write app effectively. Unless we're all going back to single tenant, single user applications SQLite seems overhyped for the new usecases. Don't get me wrong, there are plenty of uses for SQLite, but I think the hype is getting out of hand IMHO. If you are making an app that only a single person is going to use at a time, then there are plenty of options, including SQLite. Heck, IndexedDB is sufficient. CouchDB tried the whole DB-per-user thing and it didn't end super well. Happy to be proven wrong though. If anyone has an example of a site with more than say, 10K concurrent writers (edit: changed from users) running on a single SQLite DB I'd probably change my mind. --- Now, what would be interesting is a way to architect an app such that you can have a central entity, like this site, for instance, but all of your posts are actually referring to your own SQLite, or equivalent store. In that sense you can own your data completely. For performance you can specify a TTL for your data and the consumer (this site) could cache* it accordingly. Though this would probably end up being a lot less performant than simply using something like Postgres, but at least you'd have more control over your own data. * - This caching ideally would be enforced by the browser, and not the server, that way the server actually never touches or knows what data you have.
- catmanjan 4y ago
  10K users isn’t much, but 10k concurrent writer’s is a different proposition - I’d be surprised if 100k users had 10k concurrent writers
  - endisneigh 4y ago
    That's a fair point. AFAIK SQLite uses a queue (in WAL mode) in order to handle concurrent writes. I imagine it basically couldn't handle 10K concurrent writes to begin with in a practical application since newly read data would be out of date pretty quickly.
    - catmanjan 4y ago
      I’m not sure as I’ve never used it but if the writes were fast enough perhaps it wouldn’t be a problem
TazeTSchnitzel 4y ago
“may become”? It already is the backbone of so much software.
Mertax 4y ago
I think the potential exists for many multi-tenancy B2B SaaS products to be converted to an SQLite-per-tenant model. CRUD Database products like Jira, Salesforce, Airtable, Notion, Clickup, Asana, etc. seem like internal line of business services that just need to be thick clients with a distributed database. Most individual businesses/tenants won’t have to deal with more than 100 concurrent writes at a time (if that) so SQLite could definitely handle that.
- lenkite 4y ago
  How does one handle HA and failover with SQLite ? These products you mentioned are more than just simple CRUD and have full plugin engines and workflows.
  - Mertax 4y ago
    I’d be curious how products like GitHub/BitBucket handle this with git repository hosting, because I’d imagine it would not be much different. Also if you took a truly local first approach, then fully distributing the database amongst peers and supporting peer to peer synchronization would be another way (which would work for use cases with smaller data storage requirements)
  - tlb 4y ago
    For most of those products, a commodity server is more reliable than the software itself. A straightforward hourly backup with manual recovery is adequate for most orgs.
ec109685 4y ago
Are there really questions about using SQLite? > Another wrinkle is that it’s not exactly open source. The original developer of SQLite, Dwane Richard Hipp, placed it in the public domain. Generally, this means that there are no legal restrictions on using the code at all, although there are some questions whether every country recognizes this view. A bigger issue than the license is that it’s not open for contributions. They don’t accept pull requests and the extensive test suite is proprietary.
- capableweb 4y ago
  > A bigger issue than the license is that it’s not open for contributions SQLite is open for contributions, as long as you "submit an affidavit dedicating your contribution into the public domain" (https://www.sqlite.org/copyright.html https://www.sqlite.org/copyright.html). It's not "Open-Contribution" in the sense that some npm packages are for example, where if you send any PR, it will most likely be merged and then you get write access to the repository. If you're curious how you can contribute, check out this page: https://system.data.sqlite.org/index.html/doc/trunk/www/contribute.wiki https://system.data.sqlite.org/index.html/doc/trunk/www/cont... Personally, I prefer projects that are limited in what contributions they accept to the main trunk. Code quality tends to be a lot higher in those. As long as the source is open so I can freely maintain my own patches, I have no qualms with that workflow.
  - boyter 4y ago
    Although that affidavit is still a problem for some. Both German and Australian citizens cannot revoke their human rights and as such cannot put things into public domain. Hence cannot ever contribute to sqlite. This may be seen as advantageous however.
    - ThunderSizzle 4y ago
      Does this mean they can't contribute to open-source MIT-equivalent licensed code bases? As that's basically giving that code to the public domain, is it not?
metadat 4y ago
This makes sense, I have experience with and lots of appreciation for SQLite. It's exempliry across many areas of software engineering. Elegant simplicity, not bloated, high performance, broad API library support, amazing 3rd party ecosystem (rqlite [1], litestream [2]). It's wildly nice! With that said, I've found that I'm a bit hesitant to use SQLite for anything interactive for one silly reason: It's so hard to query precise date ranges in an ergonomic way interactively. There is no timestamp type, and the general advice I've seen is to use Unix Epoch offsets. This works fine except for exploring. I keep finding myself desiring porting my SQLite's to PostgreSQL for only the comparatively easy ranged data exploration. [1] https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite [2] https://github.com/benbjohnson/litestream https://github.com/benbjohnson/litestream
- somenameforme 4y ago
  Can you elaborate on the problem there? I'm confused because as you described it, it seems just something like select * from table where startDate >= 123 and endDate <= 456; would solve the problem.
  - petre 4y ago
    He has to convert every timestamp into unix epoch.
- piaste 4y ago
  SQLite supports computed columns, so you could just add a virtual text column next to the 'real' epoch column defined as the date representation of your choice. https://www.sqlite.org/draft/gencol.html https://www.sqlite.org/draft/gencol.html Ignore the text column in your application so it won't be computed at runtime, but it will appear when you 'select *' or use a db browser in exploration mode.
richardc323 4y ago
For reasons I wont go into here, I built a system with a similar approach 10 years ago. The system was horizontally scaleable. There was no database tier, instead each server had a replica of the database locally which were used for reads. The servers discovered each other other and nominated one server as the master, which writes were sent to. Replication was done by having the master sending the DML queries to a writer process on each server. When a new server joined the cluster it was sent a copy of the entire database and a stream of queries for it to catch up before it joined the cluster. There were other tricks to make sure reads from replicas waited until the replicas were sufficiently up to date. It worked fine as the system was read heavy and write light. SQLite serialises writes so does not perform well with multiple writers, particularly if the write transactions are long running. Reads were blazingly fast as there was no round-trips across the network to a separate database tier. The plan for dealing with performance problems if/when they arrived was to shard the servers into groups of customers. I moved on and the next developer ripped it out and replaced it with Postgres because it was such an oddball system. I came back six months later to fix the mess as the new developer messed up transactions with the new database code. Technically using SQLite with replication tacked on works fine. Superficially it is all the same because it is SQL. However the performance characteristics are very different from a conventional Multi Version Concurrency Control databases such as Postgres. This is where the problem lies with this kind of database - developers seeing SQL and assuming they can develop exactly the same way they would with other SQL databases. That said I love approaches that get away from the database architectures of last century.
- kumarvvr 4y ago
  As a Postgres enthusiast, I am really curious to know how the new developer messed up the transactions. Could you please elaborate?
TheAceOfHearts 4y ago
I'm still bitter that we could've had SQLite in the browser as WebSQL but it got killed off in favor of IndexedDB which is terrible. The code is in the public domain. The spec could just say to include SQLite. IndexedDB implementations all use SQLite under the hood anyway, AFAIK.
- hitekker 4y ago
  Back then, many NoSQL/MongoDB opportunists were in positions of authority on the web. When WebSQL was discussed, those folks pushed the lie that "SQL is SQLite. Therefore, SQL does not belong in the browser". They pretended that SQLite and SQL APIs were the exact same thing, as if no browser could offer a dialect of SQL without fully exposing the database engine running under the hood. In their rhetoric, encapsulation was impossible, the specification was the implementation. With SQL and SQLite conflated, the opportunists were then able to wield the valid argument "SQlite is not a standard. Browsers should be based on standards" against the simple point "SQL ought to be a standard". Once they beat the more reasonable voices into submission and were then free to assert that only NoSQL can be standardized in the browser. Which misdirected years of efforts into IndexedDB. Nowadays, if you look up those opportunists on LinkedIn, you can see their NoSQL careers didn't quite go the way they were expecting. Their wrongdoing is a very, very sore point for them, and it's unfortunate the web had to pay for their hubris. For more details: https://nolanlawson.com/2014/04/26/web-sql-database-in-memoriam/ https://nolanlawson.com/2014/04/26/web-sql-database-in-memor...
- LAC-Tech 4y ago
  The conspiracy theorist in me thinks Mozilla has it in for Richard Hipp. Wonder if there's some schadenfreude seeing Firefox fade rapidly into obscurity and SQLite continue to take over the world.
- i5heu 4y ago
  It is possible to have SQLite in WASM and use indexDB as a filesystem
  - scrame 4y ago
mfarstad 4y ago
Obligatory: https://github.com/mathaou/termdbms https://github.com/mathaou/termdbms Granted I'm the author, but until I get around to rewriting it from the ground up, I really think it saves a lot of time for editing single cells, querying, and viewing in a headless environment.
Matthias247 4y ago
May become? Sounds like the author missed that it's already used in lots of extremely important places. Among other use-cases, Android uses SQLite for > 10 years.
NonNefarious 4y ago
"I can probably figure out how to do what I need to do with [Key-Value] but" It's key/value.
TheRealPomax 4y ago
SQLite is your file format. If your use case is poorly dealt with by using files it's poorly dealt with by using SQLite. But a _lot_ of things actually work fine with a file backing until they need to scale, and most projects that use SQLite never need that scale. But when they do, unlike a home-rolled file formats, SQLite has "prepping your data for importing into a real data store" baked right in.
sally1620 4y ago
This sounds like a hype that is going to die at some point. SQLite has its own uses in client devices and small webservers, but it is not built to scale to millions of transactions per second. It is possible to build all kinds of cool things on top of SQLite, but most of these articles sound like it is going to replace PostgreSQL or MySQL.
euroderf 4y ago
I want to see a mountable file system contained in an SQLite file. It creates possibilities. [1] is Java (yikes) and has been inactive six years. [2] is in Rust and has been inactive three years but does not seem to be available in cargo. [3] is in Go, lacks a LICENSE file, has been inactive four months, the README is in Russian, but well the demo code works. pkg.go.dev will not display its API. [1] https://github.com/andyhebear/sqlitefs https://github.com/andyhebear/sqlitefs [2] https://github.com/narumatt/sqlitefs https://github.com/narumatt/sqlitefs [3] https://github.com/jilio/sqlitefs https://github.com/jilio/sqlitefs, https://github-com.translate.goog/jilio/sqlitefs?_x_tr_sl=ru&_x_tr_tl=en&_x_tr_hl=en&_x_tr_pto=wapp https://github-com.translate.goog/jilio/sqlitefs?_x_tr_sl=ru...
- eterps 4y ago
  Why would that be useful? (I'm genuinely curious)
  - pdimitar 4y ago
    sqlite authors say that for many scenarios plain sqlite beats filesystem traversal times. So for some applications it might be faster to put "files" in a database (where primary keys are hierarchical full paths) as opposed to actually use the filesystem.
  - euroderf 4y ago
    For example, applications that use a pre-prepared content, or that use a lot of relative links ? Such as publishing, or content management, or any number of web apps ?
ryandvm 4y ago
Wait until these kids discover embedding BerkleyDB...
cryptonector 4y ago
May?
edwinyzh 4y ago
We can get 929,281 inserts per second writing to sqlite db: https://blog.synopse.info/?post/2022/02/15/mORMot-2-ORM-Performance https://blog.synopse.info/?post/2022/02/15/mORMot-2-ORM-Perf...

165 points by alexrustic 4y ago

ProtoMathics 4y ago

Just another filesystem container.

kumarvvr 4y ago
But one you can query and play around with. Its absolutely phenomenal as a custom file type for your applications.

adamnemecek 4y ago

Can’t wait for something better to come along. The text orientedness of SQL is annoying. Something like Linq without the underlying SQL.

jdmichal 4y ago
Isn't "LINQ without the underlying SQL" simply list comprehensions?
- adamnemecek 4y ago
  Well plus serialization that’s not SQL.
- pjc50 4y ago
  Microsoft call it "LINQ to objects": https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/linq-to-objects https://docs.microsoft.com/en-us/dotnet/csharp/programming-g...
smitty1e 4y ago
Use an object-relational mapper and leave the boring stuff to a library.
- tehlike 4y ago
  Sadly the choice of sql tends to make underlying framework very complicated and sometimes incapable. Some things got easier to express with SQL after Json_agg in postgres became a thing (now you can do multi-depth relations without blowing up the result table with Cartesian product) but it's still massive pain in the ass and Json agg has performance overhead too.
  - smitty1e 4y ago
    Especially for reporting, I'm just as happy to write a serious piece of SQL and execute it directly. This is the joy of SQLAlchemy: great features when you want to use them; escape hatches when you don't.
    - tehlike 4y ago
      Right, most ORM tools give an escape hatch to execute raw SQL queries. However, even though SQL is turing complete, it's not a great language for retrieval imho. It's probably an artifact of the underlying relational models (and the relational output it has to generate) but still.
      - smitty1e 4y ago
        I was all set to disagree with you, but a bit of googling yields => https://localcoder.org/is-sql-or-even-tsql-turing-complete https://localcoder.org/is-sql-or-even-tsql-turing-complete
        tehlike 4y ago
        It could have been wrong, but main argument was whether SQL was a good language for retrieval or not, and for me that's a no. It's probably subjective though :)
- __MatrixMan__ 4y ago
  I hate ORMs. After days of fumbling in the dark you finally isolate the problematic query, post it like "guys I found it," and still nobody speaks up. So you hack the ORM to attach tracebacks as comments to each query, retest everything, and finally git-blame your way to somebody who owns that bit of code and they're always like "wait, the ORM did WHAT?" I'd rather grep code for SQL any day.
beached_whale 4y ago
I know not what you ment but just mentioning that if one uses entity framework or linq to sql in .net, linq should work with sqlite.
tehlike 4y ago
Amen. I made this point over and over. Sql is fairly annoying to deal with, linq is very close to me being perfect, prql is close https://github.com/prql/prql https://github.com/prql/prql
boyter 4y ago
The thing I always liked about linq was it’s lazy evaluation. You could write simple selects on your data model and join and filter in the service. Allowing unit tests that operated on the joins. I’m yet to find a better way to write crud apps than that pattern.

smitty1e 4y ago

> “I think the biggest complication for us is that there’s no tooling for it.” said Mackey. “We have people deploy apps. They’re like, ‘How do I connect to my database and like query things? How do I import data?’” Wut? Every scripting language includes a SQLite driver. Python's SQLAlchemy wraps SQLite beautifully. You can develop locally and then deploy to an enterprise server with a simple change of connection string. I must be missing the speaker's point.

nicoburns 4y ago
I think they mean GUI tooling for ad-hoc inspection of the database. And possibly the stumbling block is difficulty accessing it over a network?
- smitty1e 4y ago
  https://sqlitebrowser.org/ https://sqlitebrowser.org/
- stefanos82 4y ago
  I prefer https://sqlitestudio.pl/ https://sqlitestudio.pl/ to be honest with you.
  - edwinyzh 4y ago
    I've tried over 15 sqlite management tool, but SqliteStudio is my favorite! And recently discovered https://github.com/little-brother/sqlite-gui https://github.com/little-brother/sqlite-gui
- 41b696ef1113 4y ago
  I personally love DBeaver for SQL, but I did stumble across a thats-not-how-it-was-intended-for-usecase for Datasette [0]. Datasette is intended to be a way to share static SQLite databases. However, I was recently debugging a Django application where I lacked appropriate tooling to inspect what was happening in the database. However, I am a huge fan of Datasette, so why not? While the Django debug server was running, I was able to launch datasette in a separate terminal and have a live view into the database while the application was free to mutate the underlying data. You can even write custom SQL inside the browser to select/modify any interesting data. [0]: https://datasette.io/ https://datasette.io/

cropcirclbureau 4y ago

Interesting usage of the word folklore.

Tao332 4y ago

tiffanyh 4y ago

The answer is much simpler. When people created desktop apps - they needed a datastore that was simple and reliable to use. SQLite was perfect for this. Now, no-one is creating desktop apps anymore (everything is a web app), but people still need a simple & reliable datastore. Even though SQLite has historically advertised to not be used for client/server, people are finding that SQLite works great for most web apps as well.

marpstar 4y ago
"Native Mobile Apps" are the new "Desktop App". They can both be client apps to a remote server/datastore, or not. "Desktop Apps" didn't go away, the internet-connected apps just overtook them.
luvs2code 4y ago
I really miss desktop apps. They worked so well and didn't have half the problems that come with web apps. I'm working on a project, for fun not for money, that uses Golang and talks to a SQLite database for things like accounts and user content. So far I really enjoy writing it a lot but I have to admit that it would probably have issues if it scaled to a large number of users. In fairness to SQLite it was designed in a different era technologically. Maybe there will be a replacement for the web app era if we distill what we like about SQLite
NonNefarious 4y ago
My company just finished a new desktop app and will continue to produce more.
- brokenmachine 4y ago
  Bless you. So sick of cloud everything.

endisneigh 4y ago

I've yet to see how you can use SQLite for a multi-user multi-write app effectively. Unless we're all going back to single tenant, single user applications SQLite seems overhyped for the new usecases. Don't get me wrong, there are plenty of uses for SQLite, but I think the hype is getting out of hand IMHO. If you are making an app that only a single person is going to use at a time, then there are plenty of options, including SQLite. Heck, IndexedDB is sufficient. CouchDB tried the whole DB-per-user thing and it didn't end super well. Happy to be proven wrong though. If anyone has an example of a site with more than say, 10K concurrent writers (edit: changed from users) running on a single SQLite DB I'd probably change my mind. --- Now, what would be interesting is a way to architect an app such that you can have a central entity, like this site, for instance, but all of your posts are actually referring to your own SQLite, or equivalent store. In that sense you can own your data completely. For performance you can specify a TTL for your data and the consumer (this site) could cache* it accordingly. Though this would probably end up being a lot less performant than simply using something like Postgres, but at least you'd have more control over your own data. * - This caching ideally would be enforced by the browser, and not the server, that way the server actually never touches or knows what data you have.

catmanjan 4y ago
10K users isn’t much, but 10k concurrent writer’s is a different proposition - I’d be surprised if 100k users had 10k concurrent writers
- endisneigh 4y ago
  That's a fair point. AFAIK SQLite uses a queue (in WAL mode) in order to handle concurrent writes. I imagine it basically couldn't handle 10K concurrent writes to begin with in a practical application since newly read data would be out of date pretty quickly.
  - catmanjan 4y ago
    I’m not sure as I’ve never used it but if the writes were fast enough perhaps it wouldn’t be a problem

TazeTSchnitzel 4y ago

“may become”? It already is the backbone of so much software.

Mertax 4y ago

I think the potential exists for many multi-tenancy B2B SaaS products to be converted to an SQLite-per-tenant model. CRUD Database products like Jira, Salesforce, Airtable, Notion, Clickup, Asana, etc. seem like internal line of business services that just need to be thick clients with a distributed database. Most individual businesses/tenants won’t have to deal with more than 100 concurrent writes at a time (if that) so SQLite could definitely handle that.

lenkite 4y ago
How does one handle HA and failover with SQLite ? These products you mentioned are more than just simple CRUD and have full plugin engines and workflows.
- Mertax 4y ago
  I’d be curious how products like GitHub/BitBucket handle this with git repository hosting, because I’d imagine it would not be much different. Also if you took a truly local first approach, then fully distributing the database amongst peers and supporting peer to peer synchronization would be another way (which would work for use cases with smaller data storage requirements)
- tlb 4y ago
  For most of those products, a commodity server is more reliable than the software itself. A straightforward hourly backup with manual recovery is adequate for most orgs.

ec109685 4y ago

Are there really questions about using SQLite? > Another wrinkle is that it’s not exactly open source. The original developer of SQLite, Dwane Richard Hipp, placed it in the public domain. Generally, this means that there are no legal restrictions on using the code at all, although there are some questions whether every country recognizes this view. A bigger issue than the license is that it’s not open for contributions. They don’t accept pull requests and the extensive test suite is proprietary.

capableweb 4y ago
> A bigger issue than the license is that it’s not open for contributions SQLite is open for contributions, as long as you "submit an affidavit dedicating your contribution into the public domain" (https://www.sqlite.org/copyright.html https://www.sqlite.org/copyright.html). It's not "Open-Contribution" in the sense that some npm packages are for example, where if you send any PR, it will most likely be merged and then you get write access to the repository. If you're curious how you can contribute, check out this page: https://system.data.sqlite.org/index.html/doc/trunk/www/contribute.wiki https://system.data.sqlite.org/index.html/doc/trunk/www/cont... Personally, I prefer projects that are limited in what contributions they accept to the main trunk. Code quality tends to be a lot higher in those. As long as the source is open so I can freely maintain my own patches, I have no qualms with that workflow.
- boyter 4y ago
  Although that affidavit is still a problem for some. Both German and Australian citizens cannot revoke their human rights and as such cannot put things into public domain. Hence cannot ever contribute to sqlite. This may be seen as advantageous however.
  - ThunderSizzle 4y ago
    Does this mean they can't contribute to open-source MIT-equivalent licensed code bases? As that's basically giving that code to the public domain, is it not?

metadat 4y ago

This makes sense, I have experience with and lots of appreciation for SQLite. It's exempliry across many areas of software engineering. Elegant simplicity, not bloated, high performance, broad API library support, amazing 3rd party ecosystem (rqlite [1], litestream [2]). It's wildly nice! With that said, I've found that I'm a bit hesitant to use SQLite for anything interactive for one silly reason: It's so hard to query precise date ranges in an ergonomic way interactively. There is no timestamp type, and the general advice I've seen is to use Unix Epoch offsets. This works fine except for exploring. I keep finding myself desiring porting my SQLite's to PostgreSQL for only the comparatively easy ranged data exploration. [1] https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite [2] https://github.com/benbjohnson/litestream https://github.com/benbjohnson/litestream

somenameforme 4y ago
Can you elaborate on the problem there? I'm confused because as you described it, it seems just something like select * from table where startDate >= 123 and endDate <= 456; would solve the problem.
- petre 4y ago
  He has to convert every timestamp into unix epoch.
piaste 4y ago
SQLite supports computed columns, so you could just add a virtual text column next to the 'real' epoch column defined as the date representation of your choice. https://www.sqlite.org/draft/gencol.html https://www.sqlite.org/draft/gencol.html Ignore the text column in your application so it won't be computed at runtime, but it will appear when you 'select *' or use a db browser in exploration mode.

richardc323 4y ago

For reasons I wont go into here, I built a system with a similar approach 10 years ago. The system was horizontally scaleable. There was no database tier, instead each server had a replica of the database locally which were used for reads. The servers discovered each other other and nominated one server as the master, which writes were sent to. Replication was done by having the master sending the DML queries to a writer process on each server. When a new server joined the cluster it was sent a copy of the entire database and a stream of queries for it to catch up before it joined the cluster. There were other tricks to make sure reads from replicas waited until the replicas were sufficiently up to date. It worked fine as the system was read heavy and write light. SQLite serialises writes so does not perform well with multiple writers, particularly if the write transactions are long running. Reads were blazingly fast as there was no round-trips across the network to a separate database tier. The plan for dealing with performance problems if/when they arrived was to shard the servers into groups of customers. I moved on and the next developer ripped it out and replaced it with Postgres because it was such an oddball system. I came back six months later to fix the mess as the new developer messed up transactions with the new database code. Technically using SQLite with replication tacked on works fine. Superficially it is all the same because it is SQL. However the performance characteristics are very different from a conventional Multi Version Concurrency Control databases such as Postgres. This is where the problem lies with this kind of database - developers seeing SQL and assuming they can develop exactly the same way they would with other SQL databases. That said I love approaches that get away from the database architectures of last century.

kumarvvr 4y ago
As a Postgres enthusiast, I am really curious to know how the new developer messed up the transactions. Could you please elaborate?

TheAceOfHearts 4y ago

I'm still bitter that we could've had SQLite in the browser as WebSQL but it got killed off in favor of IndexedDB which is terrible. The code is in the public domain. The spec could just say to include SQLite. IndexedDB implementations all use SQLite under the hood anyway, AFAIK.

hitekker 4y ago
Back then, many NoSQL/MongoDB opportunists were in positions of authority on the web. When WebSQL was discussed, those folks pushed the lie that "SQL is SQLite. Therefore, SQL does not belong in the browser". They pretended that SQLite and SQL APIs were the exact same thing, as if no browser could offer a dialect of SQL without fully exposing the database engine running under the hood. In their rhetoric, encapsulation was impossible, the specification was the implementation. With SQL and SQLite conflated, the opportunists were then able to wield the valid argument "SQlite is not a standard. Browsers should be based on standards" against the simple point "SQL ought to be a standard". Once they beat the more reasonable voices into submission and were then free to assert that only NoSQL can be standardized in the browser. Which misdirected years of efforts into IndexedDB. Nowadays, if you look up those opportunists on LinkedIn, you can see their NoSQL careers didn't quite go the way they were expecting. Their wrongdoing is a very, very sore point for them, and it's unfortunate the web had to pay for their hubris. For more details: https://nolanlawson.com/2014/04/26/web-sql-database-in-memoriam/ https://nolanlawson.com/2014/04/26/web-sql-database-in-memor...
LAC-Tech 4y ago
The conspiracy theorist in me thinks Mozilla has it in for Richard Hipp. Wonder if there's some schadenfreude seeing Firefox fade rapidly into obscurity and SQLite continue to take over the world.
i5heu 4y ago
It is possible to have SQLite in WASM and use indexDB as a filesystem
- scrame 4y ago

mfarstad 4y ago

Obligatory: https://github.com/mathaou/termdbms https://github.com/mathaou/termdbms Granted I'm the author, but until I get around to rewriting it from the ground up, I really think it saves a lot of time for editing single cells, querying, and viewing in a headless environment.

Matthias247 4y ago

May become? Sounds like the author missed that it's already used in lots of extremely important places. Among other use-cases, Android uses SQLite for > 10 years.

NonNefarious 4y ago

"I can probably figure out how to do what I need to do with [Key-Value] but" It's key/value.

TheRealPomax 4y ago

SQLite is your file format. If your use case is poorly dealt with by using files it's poorly dealt with by using SQLite. But a _lot_ of things actually work fine with a file backing until they need to scale, and most projects that use SQLite never need that scale. But when they do, unlike a home-rolled file formats, SQLite has "prepping your data for importing into a real data store" baked right in.

sally1620 4y ago

This sounds like a hype that is going to die at some point. SQLite has its own uses in client devices and small webservers, but it is not built to scale to millions of transactions per second. It is possible to build all kinds of cool things on top of SQLite, but most of these articles sound like it is going to replace PostgreSQL or MySQL.

euroderf 4y ago

I want to see a mountable file system contained in an SQLite file. It creates possibilities. [1] is Java (yikes) and has been inactive six years. [2] is in Rust and has been inactive three years but does not seem to be available in cargo. [3] is in Go, lacks a LICENSE file, has been inactive four months, the README is in Russian, but well the demo code works. pkg.go.dev will not display its API. [1] https://github.com/andyhebear/sqlitefs https://github.com/andyhebear/sqlitefs [2] https://github.com/narumatt/sqlitefs https://github.com/narumatt/sqlitefs [3] https://github.com/jilio/sqlitefs https://github.com/jilio/sqlitefs, https://github-com.translate.goog/jilio/sqlitefs?_x_tr_sl=ru&_x_tr_tl=en&_x_tr_hl=en&_x_tr_pto=wapp https://github-com.translate.goog/jilio/sqlitefs?_x_tr_sl=ru...

eterps 4y ago
Why would that be useful? (I'm genuinely curious)
- pdimitar 4y ago
  sqlite authors say that for many scenarios plain sqlite beats filesystem traversal times. So for some applications it might be faster to put "files" in a database (where primary keys are hierarchical full paths) as opposed to actually use the filesystem.
- euroderf 4y ago
  For example, applications that use a pre-prepared content, or that use a lot of relative links ? Such as publishing, or content management, or any number of web apps ?

ryandvm 4y ago

Wait until these kids discover embedding BerkleyDB...

cryptonector 4y ago

May?

edwinyzh 4y ago

We can get 929,281 inserts per second writing to sqlite db: https://blog.synopse.info/?post/2022/02/15/mORMot-2-ORM-Performance https://blog.synopse.info/?post/2022/02/15/mORMot-2-ORM-Perf...