5 ms·

> Simplicity leads to reliability and I don't know of a more reliable database than SQLite. JSON file on disk might be a reasonable competitor here. This scale

> Simplicity leads to reliability and I don't know of a more reliable database than SQLite. JSON file on disk might be a reasonable competitor here. This scales poorly, but sometimes you don't need to scale up to something as crazy as full-blown SQLite.

eatonphil 4y ago
See also: https://tailscale.com/blog/an-unlikely-database-migration/ https://tailscale.com/blog/an-unlikely-database-migration/, and https://tailscale.com/blog/database-for-2022/ https://tailscale.com/blog/database-for-2022/.
dymk 4y ago
JSON file (or any file you directly write to) is simple, but has few of the guarantees SQLite gives you with regards to persistence. SQLite abstracts away all of that for you by handling the fancy platform-specific I/O stuff under the hood. SQLite does increase complexity in the sense that it's a new dependency. But as far as dependencies go, SQLite has bindings in almost all languages. IMO even for simple usecases, it's worth using it from the start. Refactors are easier down the line than with a bespoke JSON/yaml/flat encoding. Read only configuration, sure, JSON (etc) file makes sense. The second you start writing to it, probably not.
- fikama 4y ago
  Could you elaborate more on those things abstracted by SQLite? or point to further reading - You got me interested
  - Lex-2008 4y ago
    Not OP, but I remember reading an interesting article about issues that simple write() might face: https://danluu.com/deconstruct-files/ https://danluu.com/deconstruct-files/ And here's what SQLite does to ensure that in case of application or computer crash database contains either new or old data, but not their mix or some other garbage: https://sqlite.org/atomiccommit.html https://sqlite.org/atomiccommit.html
benbjohnson 4y ago
Yeah, I like JSON on disk too. There's some "gotchas" to avoid to make it safe though like atomic renaming or fsyncing the file and its parent directory. But as long as you avoid those and don't have a lot of data then JSON-on-disk works great.
vlovich123 4y ago
Implied is “as simple as possible but no simpler”. We used JSON on disk at one job BUT. 1. We knew what we were doing with respect to fsync+rename to guarantee transactions. I bet you the vast majority of people who go to do that won’t (SQLite abstract you from needing to understand that) 2. We ended up switching to SQLite anyway and that was a painful migration. Just pick SQLite to manage data and bypass headaches. There’s a huge ecosystem of SQL-based tools that can help you manage your growth too (eg if you need to migrate from SQLite to Postgres or something)
- anonymouse008 4y ago
  At what point does one “need” to move to Postgres / MySQL? It feels like much of DBA is all style points - not necessarily objective.
  - zdkl 4y ago
    It becomes necessary at some (low) value of concurrent write usage basically. Or if you want to lean on logic built into one of the "proper" RDBMS (GIS, granular access managment, ...).
    - masklinn 4y ago
      > It becomes necessary at some (low) value of concurrent write usage basically. Much less so since the introduction of WAL mode 12 years ago, tho.
      - barking_biscuit 4y ago
        I often see the discussed, but I've not seen any benchmarks testing throughput of concurrent writes in SQLite compared to Postgres, so it's hard to know exactly how far WAL mode make it scale before it's simply the wrong tool for the job. I'm quite curious to know, so at some point I'll get around to doing my own comparison. If you are able to give some indication then that would be very handy. I also think it can be made to scale much further if you have a Multi-Tenant architecture where you essentially do a database per tenant, though that has it's drawbacks along other dimensions (namely schema changes). At some point it's going to come down to disk throughput.
schwartzworld 4y ago
JSON is a pretty poor storage format though, isn't it? I use it for the wordle clone I made, but the code would have been much simpler with SQL statements. And that was without any kind of complicated relational data https://github.com/schwartzworld/schwardle https://github.com/schwartzworld/schwardle
conradev 4y ago
The funny thing is that SQLite is faster for storing JSON blobs than the file system is: https://www.sqlite.org/fasterthanfs.html https://www.sqlite.org/fasterthanfs.html I would also say that the second you need any sort of writing or mutating in any sort of production environment SQLite is also simpler than the file system! Race conditions in file system operations are easy to hit, and files have a lot more moving pieces (directory entries, filenames, file descriptors, advisory locks, permissions, the read/write API, etc) than a simple SQL schema Whenever I see “full-blown” next to “SQLite” there is usually some sort of misconception. It’s often simpler and more reliable than the next best option!
- jck 4y ago
  It looks like sqlite is not much faster than fs on Linux. I've also found that ripgrep performs surprisingly well with folders containing large numbers of text files - this makes raw json on disk more convenient for many simple usecases which don't require complex queries.
  - samatman 4y ago
    It's on write that using SQLite really shines. Imagining a moderately large JSON object, a lot of them actually, not hard to read or write exactly but they do carry some common references. SQLite can: only rewrite the changed fields with a query, and transact the update across the objects. The filesystem can do atomic swaps of each file, usually, if nothing goes wrong. SQLite would also be faster in this case, but I'm more motivated by the ACID guarantees.
yndoendo 4y ago
One of the projects I worked on was an embedded system running on 200MHz with 8MB RAM and micro SD for storage, single data channel. SQLite was chosen because of the power to preform report calculations in SQL and is perfect for configuration storage in simple Key / Value relation, like Firefox about:config. SQLite is my go-to database for application configuration and even non-enterprise web applications.
CJefferson 4y ago
We did have a big system that used JSON file on disk. The problem is over time we ended up with files with half a JSON, and needed a "cleanup" pass. Except of course the cleanup might find a file in the process of being written, and life slowly got more and more complicated.
- zimpenfish 4y ago
  Worked at a place that used on-disk XML as their database with multiple (10s of) writers. Started off fragile, ended up hideously complex and fragile. Unfortunately it was originally written by one of the powers that be and his word held sway over every technical decision, good or disastrous.
outworlder 4y ago
> something as crazy as full-blown SQLite. The sqlite library is tiny. Often smaller than some JSON parsers!
- eatonphil 4y ago
  I cannot imagine such a json parser. You can parse json in only hundreds of lines of code.
- sgbeal 4y ago
  > The sqlite library is tiny. Often smaller than some JSON parsers! Stripped of all comments, the current "amalgamation" build of sqlite (which is the one most projects use) has 178k lines and 4.7MB of code. If your JSON parsers are anywhere near 1/10th of that size then they're seriously overengineered ;).
didgetmaster 4y ago
I wrote a new, general-purpose data management system that was originally designed to compete with file systems for storing large amounts of unstructured data (my background is in disk utilities like PartitionMagic and Drive Image). It can be used to convert 100M+ files (photos, documents, videos, etc.) into data objects and attach searchable, contextual meta-data tags to them. The tags I invented were essentially highly-optimized key-value stores that worked really well. I then discovered that these same KV stores could also be used to form relational tables (a columnar store). It could be used to store both highly structured data or the more semi-structured data found in things like Json documents where every value could be an array. Queries were lightning fast and it could perform analytic operations while still performing transactions at a high speed. My hobby turned into a massive project as I figured out how to import/export CSV, Json, Json lines, XML, etc. files to/from tables easily and quickly. It still has a lot of work to go before it is a 'full-blown' database, but it is now a great tool for cleaning and analyzing some pretty big tables (tested to 2500 columns and 200M rows). It is in open beta at https://didgets.com/download https://didgets.com/download and there are a bunch of short videos that show some of the things it can do on my youtube channel: https://www.youtube.com/channel/UC-L1oTcH0ocMXShifCt4JQQ https://www.youtube.com/channel/UC-L1oTcH0ocMXShifCt4JQQ
slaymaker1907 4y ago
For reliability, SQLite probably still wins. Even if you write the JSON all at once, what happens when the power goes out at that instant?
c_hub1125 4y ago

eatonphil 4y ago

See also: https://tailscale.com/blog/an-unlikely-database-migration/ https://tailscale.com/blog/an-unlikely-database-migration/, and https://tailscale.com/blog/database-for-2022/ https://tailscale.com/blog/database-for-2022/.

dymk 4y ago

JSON file (or any file you directly write to) is simple, but has few of the guarantees SQLite gives you with regards to persistence. SQLite abstracts away all of that for you by handling the fancy platform-specific I/O stuff under the hood. SQLite does increase complexity in the sense that it's a new dependency. But as far as dependencies go, SQLite has bindings in almost all languages. IMO even for simple usecases, it's worth using it from the start. Refactors are easier down the line than with a bespoke JSON/yaml/flat encoding. Read only configuration, sure, JSON (etc) file makes sense. The second you start writing to it, probably not.

fikama 4y ago
Could you elaborate more on those things abstracted by SQLite? or point to further reading - You got me interested
- Lex-2008 4y ago
  Not OP, but I remember reading an interesting article about issues that simple write() might face: https://danluu.com/deconstruct-files/ https://danluu.com/deconstruct-files/ And here's what SQLite does to ensure that in case of application or computer crash database contains either new or old data, but not their mix or some other garbage: https://sqlite.org/atomiccommit.html https://sqlite.org/atomiccommit.html

benbjohnson 4y ago

Yeah, I like JSON on disk too. There's some "gotchas" to avoid to make it safe though like atomic renaming or fsyncing the file and its parent directory. But as long as you avoid those and don't have a lot of data then JSON-on-disk works great.

vlovich123 4y ago

Implied is “as simple as possible but no simpler”. We used JSON on disk at one job BUT. 1. We knew what we were doing with respect to fsync+rename to guarantee transactions. I bet you the vast majority of people who go to do that won’t (SQLite abstract you from needing to understand that) 2. We ended up switching to SQLite anyway and that was a painful migration. Just pick SQLite to manage data and bypass headaches. There’s a huge ecosystem of SQL-based tools that can help you manage your growth too (eg if you need to migrate from SQLite to Postgres or something)

anonymouse008 4y ago
At what point does one “need” to move to Postgres / MySQL? It feels like much of DBA is all style points - not necessarily objective.
- zdkl 4y ago
  It becomes necessary at some (low) value of concurrent write usage basically. Or if you want to lean on logic built into one of the "proper" RDBMS (GIS, granular access managment, ...).
  - masklinn 4y ago
    > It becomes necessary at some (low) value of concurrent write usage basically. Much less so since the introduction of WAL mode 12 years ago, tho.
    - barking_biscuit 4y ago
      I often see the discussed, but I've not seen any benchmarks testing throughput of concurrent writes in SQLite compared to Postgres, so it's hard to know exactly how far WAL mode make it scale before it's simply the wrong tool for the job. I'm quite curious to know, so at some point I'll get around to doing my own comparison. If you are able to give some indication then that would be very handy. I also think it can be made to scale much further if you have a Multi-Tenant architecture where you essentially do a database per tenant, though that has it's drawbacks along other dimensions (namely schema changes). At some point it's going to come down to disk throughput.

schwartzworld 4y ago

JSON is a pretty poor storage format though, isn't it? I use it for the wordle clone I made, but the code would have been much simpler with SQL statements. And that was without any kind of complicated relational data https://github.com/schwartzworld/schwardle https://github.com/schwartzworld/schwardle

conradev 4y ago

The funny thing is that SQLite is faster for storing JSON blobs than the file system is: https://www.sqlite.org/fasterthanfs.html https://www.sqlite.org/fasterthanfs.html I would also say that the second you need any sort of writing or mutating in any sort of production environment SQLite is also simpler than the file system! Race conditions in file system operations are easy to hit, and files have a lot more moving pieces (directory entries, filenames, file descriptors, advisory locks, permissions, the read/write API, etc) than a simple SQL schema Whenever I see “full-blown” next to “SQLite” there is usually some sort of misconception. It’s often simpler and more reliable than the next best option!

jck 4y ago
It looks like sqlite is not much faster than fs on Linux. I've also found that ripgrep performs surprisingly well with folders containing large numbers of text files - this makes raw json on disk more convenient for many simple usecases which don't require complex queries.
- samatman 4y ago
  It's on write that using SQLite really shines. Imagining a moderately large JSON object, a lot of them actually, not hard to read or write exactly but they do carry some common references. SQLite can: only rewrite the changed fields with a query, and transact the update across the objects. The filesystem can do atomic swaps of each file, usually, if nothing goes wrong. SQLite would also be faster in this case, but I'm more motivated by the ACID guarantees.

yndoendo 4y ago

One of the projects I worked on was an embedded system running on 200MHz with 8MB RAM and micro SD for storage, single data channel. SQLite was chosen because of the power to preform report calculations in SQL and is perfect for configuration storage in simple Key / Value relation, like Firefox about:config. SQLite is my go-to database for application configuration and even non-enterprise web applications.

CJefferson 4y ago

We did have a big system that used JSON file on disk. The problem is over time we ended up with files with half a JSON, and needed a "cleanup" pass. Except of course the cleanup might find a file in the process of being written, and life slowly got more and more complicated.

zimpenfish 4y ago
Worked at a place that used on-disk XML as their database with multiple (10s of) writers. Started off fragile, ended up hideously complex and fragile. Unfortunately it was originally written by one of the powers that be and his word held sway over every technical decision, good or disastrous.

outworlder 4y ago

> something as crazy as full-blown SQLite. The sqlite library is tiny. Often smaller than some JSON parsers!

eatonphil 4y ago
I cannot imagine such a json parser. You can parse json in only hundreds of lines of code.
sgbeal 4y ago
> The sqlite library is tiny. Often smaller than some JSON parsers! Stripped of all comments, the current "amalgamation" build of sqlite (which is the one most projects use) has 178k lines and 4.7MB of code. If your JSON parsers are anywhere near 1/10th of that size then they're seriously overengineered ;).

didgetmaster 4y ago

I wrote a new, general-purpose data management system that was originally designed to compete with file systems for storing large amounts of unstructured data (my background is in disk utilities like PartitionMagic and Drive Image). It can be used to convert 100M+ files (photos, documents, videos, etc.) into data objects and attach searchable, contextual meta-data tags to them. The tags I invented were essentially highly-optimized key-value stores that worked really well. I then discovered that these same KV stores could also be used to form relational tables (a columnar store). It could be used to store both highly structured data or the more semi-structured data found in things like Json documents where every value could be an array. Queries were lightning fast and it could perform analytic operations while still performing transactions at a high speed. My hobby turned into a massive project as I figured out how to import/export CSV, Json, Json lines, XML, etc. files to/from tables easily and quickly. It still has a lot of work to go before it is a 'full-blown' database, but it is now a great tool for cleaning and analyzing some pretty big tables (tested to 2500 columns and 200M rows). It is in open beta at https://didgets.com/download https://didgets.com/download and there are a bunch of short videos that show some of the things it can do on my youtube channel: https://www.youtube.com/channel/UC-L1oTcH0ocMXShifCt4JQQ https://www.youtube.com/channel/UC-L1oTcH0ocMXShifCt4JQQ

slaymaker1907 4y ago

For reliability, SQLite probably still wins. Even if you write the JSON all at once, what happens when the power goes out at that instant?

c_hub1125 4y ago