6 ms·

Good observations from a MySQL perspective. Any thoughts from the other end, where the alternatives are JSON or XML or ZIP? SQLite tries hard to convince you to

Good observations from a MySQL perspective. Any thoughts from the other end, where the alternatives are JSON or XML or ZIP? SQLite tries hard to convince you to use it as an application file format, but it looks like a giant black box of overkill: why incorporate its 200k SLOC when the alternatives are a fraction of the size?

Scarbutt 6y ago
How do you create structure data with ZIP? why incorporate its 200k SLOC when the alternatives are a fraction of the size? Performance, ACID and a superior declarative query language.
- millstone 6y ago
  ZIP files are not a database: they are more like a directory hierarchy. But maybe all I need is named blobs: no query language parser, optimizer, indexing, etc. SQLite positions itself as an improvement over ZIP for application file formats: https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html . But minzip is so much smaller, easier to understand, debug and ship. So why use SQLite for an app if ZIP suffices?
  - setr 6y ago
    If you’re talking about like cbr archives, you’re right. It’s comparing against usages like word/excel, which store a bunch of XML in an archive and call it a day. If you’re not reading and writing out application state, then yes, you don’t need something to manage your non-existent state
realdense 6y ago
Depends on your use case. XML and JSON are great for applications with simple data stores, having done this myself. But if you foresee a need for complex queries or locking and threads then SQLite might be a good choice.
ak217 6y ago
JSON/XML quickly stop being alternatives as soon as you need any sort of index, a memory-mapped/on-disk data structure that doesn't have to be loaded into memory, transactional or even just incremental writes. ZIP is not even directly comparable.
- tonyedgecombe 6y ago
  That's not normally what you need for an application format though is it.
  - rini17 6y ago
    It's becoming normal, as users coming from phones aren't trained to use "save" function and expect every individual change to persist. I actually consider that a good thing. Doing everything in volatile memory until user asks otherwise is a relic from diskette era.
    - em500 6y ago
      It's not limited to phone users. I've been using computers since the 1980s (C64), and I appreciate not needing to habitually keep pressing "save" every few seconds in Google Docs or macOS Notes.
    - ymbeld 6y ago
      It’s bad enough to have to keep pressing Save manually, but I also have to do it regularly while using LibreOffice Calc since it keeps crashing. :-)
    - wheybags 6y ago
      #1 best jetbrains idea feature IMO - save on focus lost. Just alt tab into your app, or into your terminal to git commit, no worrying about "did I remember to ctrl-s".
      - mmcdermott 6y ago
        I grew up in the Win 3.1-Win 98 era. I don't think the save reflex will ever quite go away. :)
7steps2much 6y ago
It all depends on how much/how complex data you have. SQLite is a database after all, you can query it with SQL and do lots of fancy stuff that might be hard to do with regular file formats like JSON or XML. If you just need a config file or only have a small amount of data you can use XML/JSON files that you parse yourself. If you are going to have loads of data that needs some structure (for example messages in a messaging app) i would use SQLite.
chousuke 6y ago
Is 200kLOC really a lot? Lots of software nowadays has hundreds of megabytes of dependencies, and people seem to be fine with that. Not that I think having tons of dependencies is really a good thing, but for an application that needs a file format, SQLite is a very sensible dependency. neither XML, JSON nor zip solve the problems SQLite does, though; if you use plain old files, you need to make sure any changes you make actually end up on the disk, consistently. This is not easy to do. It also solves any consistency issues that might stem from someone reading the data while you're writing it. On top of being just better, having a relational model for your data gives you much more freedom to use said data; you'll be able to do things efficiently that might require restructuring your JSON or XML format. Personally, I love SQLite-based application formats because I can explore them with SQL, which is often much easier than trying to make sense of a custom JSON or XML schema.
- millstone 6y ago
  Good reply, thank you. Yes 200k SLOC is huge (modern development practices notwithstanding). SQLite creates temporary files at whim - nine different kinds! https://sqlite.org/tempfiles.html https://sqlite.org/tempfiles.html I know how to atomically write a JSON file. But when I read, for example: "The temporary files associated with transaction control, namely the rollback journal, super-journal, write-ahead log (WAL) files, and shared-memory files, are always written to disk. But the other kinds of temporary files might be stored in memory only and never written to disk. Whether or not temporary files other than the rollback, super, and statement journals are written to disk or stored only in memory depends on the SQLITE_TEMP_STORE compile-time parameter, the temp_store pragma, and on the size of the temporary file..." My eyes have completely glazed over. If I add this to my app, what will it actually do? How can I even know?
  - iainmerrick 6y ago
    I know how to atomically write a JSON file. Are you sure? I’ve had a lot of trouble getting that to work reliably myself across multiple OSes. (In hindsight I wish I’d used SQLite!) This article gives a good explanation of the many difficulties: https://danluu.com/deconstruct-files/ https://danluu.com/deconstruct-files/ My eyes have completely glazed over. If I add this to my app, what will it actually do? How can I even know? Well, fundamentally it’s very hard to get it exactly right, and I imagine that’s why the implementation is a little involved. But you could a) read through those docs, lengthy though they are, and/or b) trust the many testimonials saying SQLite is very, very robust and reliable.
petre 6y ago
Go ahead and use text files and then have fun with data corruption issues. We use CSV for sending commands to IoT devices and it's an issue. If this had been done with SQLite, then there were at least no data corruption issues. One could even use SQLite as a storage container for JSON if one whises to do so. They even have an extension that aids it with an useful set of functions: https://www3.sqlite.org/json1.html https://www3.sqlite.org/json1.html
- millstone 6y ago
  This sounds like your issue is avoiding data corruption: then atomic writes are sufficient, you don't need a SQL parser or query optimizer or etc.
  - petre 6y ago
    Not only that, it enables us to to CRUD operations, list the commands, sort them by time, do limits, pagination, bundle a bunch of commands that enable a certain functionality in a transaction etc. SQLite has all of those and more and also avoids data corruption issues by design. Anyway, the path we took was to move everything to MySQL just because most of the other data is also in a MySQL database. Otherwise we would have definitely used SQLite.
- JamesSwift 6y ago
  The JSON features of SQLite are extremely robust and performant. There is no reason to use raw JSON as the storage when you can just shove it into SQLite and lose almost nothing.
yarcob 6y ago
File formats based on ZIP files only work for small files. For big files you have huge overheads; opening and saving a moderately sized documents takes seconds (vs. milliseconds for writing changes to an SQLite database). There's a reason why Excel files are limited to a million rows, while Access databases aren't. The complexity of including SQLite is trivial for practical purposes; it's already available on many systems, and if not you can include it by adding a single C file to your project. Setting up a workflow for Google Protocol Buffers (another popular alternative for document file formats) is a lot more complex than building or linking with SQLite, and it doesn't stop people from using them. One thing that speaks for SQLite is the quality of the project; it's one of the best maintained Open Source projects with fantastic quality assurance and support for almost every OS. This means that you are unlikely to run into issues compiling or working with SQLite, like you might have with alternative libraries like libxml2 or jsonc (which are still great libraries!!). EDIT: The big downside of SQLite is that it's unsuitable for documents that are exposed to the user because of the temporary files (like the WAL). If you have a ZIP based file format that you atomically rewrite from scratch on every save, it's almost impossible to corrupt. Your users can just take the file and email it and nothing bad will happen. I'm not sure what happens if you email an SQLite database file that is currently being used. I've done that in the past and have been surprised that some data seemed to be missing, but I don't recall the details. Hence SQLite is often used for application data files that are not directly exposed to the user.
- slaymaker1907 6y ago
  You could get SQLite to work as document files exposed to the user so long as you use sessions[1]. When a file is opened, copy the DB to a temporary file or to use memory and write all changes during operation to this new DB, recording them all in a session. When the user explicitly saves a document, apply the session to the real DB. [1]: https://www.sqlite.org/sessionintro.html#:~:text=1%20Introduction%20The%20session%20extension%20provide%20a%20mechanism,the%20sessions%20extension.%203.1.%20...%204%20Extended%20Functionality https://www.sqlite.org/sessionintro.html#:~:text=1%20Introdu...
  - yarcob 6y ago
dragonwriter 6y ago
> Any thoughts from the other end, where the alternatives are JSON or XML or ZIP? ZIP isn’t a format alternative, its just a compression and/or packaging technique for files which you still need to choose a format for. JSON/YAML/XML are great for input and output formats, but not great for continuous, random read/write access.
- millstone 6y ago
  My understanding is that SQLite doesn't impose any format either?
  - deleted 6y ago
    [deleted]
  - deleted 6y ago
    [deleted]
  - mb7733 6y ago
    I'm genuinely curious what you mean by this. Of course SQLite imposes a file format... That format is a SQLite database
    - Dylan16807 6y ago
      > Of course SQLite imposes a file format... That format is a SQLite database Well this is in a context that rejects zip as being a format. Do you do that? If the answer is no then skip the rest of my post and just note that they're talking about a different definition of 'format'. - But in that context: The amount of structure imposed on you by the sqlite database format is not much more than the structure imposed on you by a zip. I think it's fair to rate them similarly as formats. A zip file is basically a key-value store. "Zip full of csvs", while awful to use, would impose about the same amount of structure as sqlite does: not much. And zip+csv is not much more elaborate than zip on its own.
      - samatman 6y ago
imtringued 6y ago
During the alpha Minecraft divided the world into 16x16x128 grids of blocks called chunks. Each chunk was its own file. Large worlds suffered from very poor performance because there were tens of thousands of files in a single folder. Some random modder basically just put multiple chunks into one file so that each file is 2MB. If Notch had just put the game world into a SQLite database he wouldn't have had to reinvent the wheel. There are games that did that, such as the alpha of Cube World and they work just fine. Heck, notch went one step further and invented NBT aka named binary tag which is basically a weirdo binary file format that stores JSON like data.
- iforgotpassword 6y ago
  > Large worlds suffered from very poor performance because there were tens of thousands of files in a single folder. It was using subdirs for the chunks, two levels iirc, one was chunkX % 36, the next level chunkY % 36. So there weren't that many files per directory. The slowness came from the overhead of opening, read/write and closing so many files all the time. > Some random modder basically just put multiple chunks into one file so that each file is 2MB. Almost, it wasn't limited by file size, it was putting 32*32 chunks into one file that was similar to a simple file system. The format of the individual chunks within that file stayed almost the same. Yet it performed much better. NBT is indeed a little weird but fairly straight forward overall, I guess designing and implementing it just scratched an itch. It was a hobby project after all.
- slaymaker1907 6y ago
  I'm actually currently working on a user mode FS using Dokan for Windows that saves everything to a SQLite file for similar reasons. NTFS just doesn't do well at all with lots of small files.
  - fomine3 6y ago
    Interesting. Did you tested any performance by non-NTFS? I remembered this post. https://github.com/microsoft/WSL/issues/873#issuecomment-425272829 https://github.com/microsoft/WSL/issues/873#issuecomment-425...
kstrauser 6y ago
There's an enormous amount of comments and tests in that codebase. As installed on my Mac, sqlite comprises a 1.3MB command line utility and a 1MB shared library. That's absolutely tiny given the functionality it provides.

Scarbutt 6y ago

How do you create structure data with ZIP? why incorporate its 200k SLOC when the alternatives are a fraction of the size? Performance, ACID and a superior declarative query language.

millstone 6y ago
ZIP files are not a database: they are more like a directory hierarchy. But maybe all I need is named blobs: no query language parser, optimizer, indexing, etc. SQLite positions itself as an improvement over ZIP for application file formats: https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html . But minzip is so much smaller, easier to understand, debug and ship. So why use SQLite for an app if ZIP suffices?
- setr 6y ago
  If you’re talking about like cbr archives, you’re right. It’s comparing against usages like word/excel, which store a bunch of XML in an archive and call it a day. If you’re not reading and writing out application state, then yes, you don’t need something to manage your non-existent state

realdense 6y ago

Depends on your use case. XML and JSON are great for applications with simple data stores, having done this myself. But if you foresee a need for complex queries or locking and threads then SQLite might be a good choice.

ak217 6y ago

JSON/XML quickly stop being alternatives as soon as you need any sort of index, a memory-mapped/on-disk data structure that doesn't have to be loaded into memory, transactional or even just incremental writes. ZIP is not even directly comparable.

tonyedgecombe 6y ago
That's not normally what you need for an application format though is it.
- rini17 6y ago
  It's becoming normal, as users coming from phones aren't trained to use "save" function and expect every individual change to persist. I actually consider that a good thing. Doing everything in volatile memory until user asks otherwise is a relic from diskette era.
  - em500 6y ago
    It's not limited to phone users. I've been using computers since the 1980s (C64), and I appreciate not needing to habitually keep pressing "save" every few seconds in Google Docs or macOS Notes.
  - ymbeld 6y ago
    It’s bad enough to have to keep pressing Save manually, but I also have to do it regularly while using LibreOffice Calc since it keeps crashing. :-)
  - wheybags 6y ago
    #1 best jetbrains idea feature IMO - save on focus lost. Just alt tab into your app, or into your terminal to git commit, no worrying about "did I remember to ctrl-s".
    - mmcdermott 6y ago
      I grew up in the Win 3.1-Win 98 era. I don't think the save reflex will ever quite go away. :)

7steps2much 6y ago

It all depends on how much/how complex data you have. SQLite is a database after all, you can query it with SQL and do lots of fancy stuff that might be hard to do with regular file formats like JSON or XML. If you just need a config file or only have a small amount of data you can use XML/JSON files that you parse yourself. If you are going to have loads of data that needs some structure (for example messages in a messaging app) i would use SQLite.

chousuke 6y ago

Is 200kLOC really a lot? Lots of software nowadays has hundreds of megabytes of dependencies, and people seem to be fine with that. Not that I think having tons of dependencies is really a good thing, but for an application that needs a file format, SQLite is a very sensible dependency. neither XML, JSON nor zip solve the problems SQLite does, though; if you use plain old files, you need to make sure any changes you make actually end up on the disk, consistently. This is not easy to do. It also solves any consistency issues that might stem from someone reading the data while you're writing it. On top of being just better, having a relational model for your data gives you much more freedom to use said data; you'll be able to do things efficiently that might require restructuring your JSON or XML format. Personally, I love SQLite-based application formats because I can explore them with SQL, which is often much easier than trying to make sense of a custom JSON or XML schema.

millstone 6y ago
Good reply, thank you. Yes 200k SLOC is huge (modern development practices notwithstanding). SQLite creates temporary files at whim - nine different kinds! https://sqlite.org/tempfiles.html https://sqlite.org/tempfiles.html I know how to atomically write a JSON file. But when I read, for example: "The temporary files associated with transaction control, namely the rollback journal, super-journal, write-ahead log (WAL) files, and shared-memory files, are always written to disk. But the other kinds of temporary files might be stored in memory only and never written to disk. Whether or not temporary files other than the rollback, super, and statement journals are written to disk or stored only in memory depends on the SQLITE_TEMP_STORE compile-time parameter, the temp_store pragma, and on the size of the temporary file..." My eyes have completely glazed over. If I add this to my app, what will it actually do? How can I even know?
- iainmerrick 6y ago
  I know how to atomically write a JSON file. Are you sure? I’ve had a lot of trouble getting that to work reliably myself across multiple OSes. (In hindsight I wish I’d used SQLite!) This article gives a good explanation of the many difficulties: https://danluu.com/deconstruct-files/ https://danluu.com/deconstruct-files/ My eyes have completely glazed over. If I add this to my app, what will it actually do? How can I even know? Well, fundamentally it’s very hard to get it exactly right, and I imagine that’s why the implementation is a little involved. But you could a) read through those docs, lengthy though they are, and/or b) trust the many testimonials saying SQLite is very, very robust and reliable.

petre 6y ago

Go ahead and use text files and then have fun with data corruption issues. We use CSV for sending commands to IoT devices and it's an issue. If this had been done with SQLite, then there were at least no data corruption issues. One could even use SQLite as a storage container for JSON if one whises to do so. They even have an extension that aids it with an useful set of functions: https://www3.sqlite.org/json1.html https://www3.sqlite.org/json1.html

millstone 6y ago
This sounds like your issue is avoiding data corruption: then atomic writes are sufficient, you don't need a SQL parser or query optimizer or etc.
- petre 6y ago
  Not only that, it enables us to to CRUD operations, list the commands, sort them by time, do limits, pagination, bundle a bunch of commands that enable a certain functionality in a transaction etc. SQLite has all of those and more and also avoids data corruption issues by design. Anyway, the path we took was to move everything to MySQL just because most of the other data is also in a MySQL database. Otherwise we would have definitely used SQLite.
JamesSwift 6y ago
The JSON features of SQLite are extremely robust and performant. There is no reason to use raw JSON as the storage when you can just shove it into SQLite and lose almost nothing.

yarcob 6y ago

File formats based on ZIP files only work for small files. For big files you have huge overheads; opening and saving a moderately sized documents takes seconds (vs. milliseconds for writing changes to an SQLite database). There's a reason why Excel files are limited to a million rows, while Access databases aren't. The complexity of including SQLite is trivial for practical purposes; it's already available on many systems, and if not you can include it by adding a single C file to your project. Setting up a workflow for Google Protocol Buffers (another popular alternative for document file formats) is a lot more complex than building or linking with SQLite, and it doesn't stop people from using them. One thing that speaks for SQLite is the quality of the project; it's one of the best maintained Open Source projects with fantastic quality assurance and support for almost every OS. This means that you are unlikely to run into issues compiling or working with SQLite, like you might have with alternative libraries like libxml2 or jsonc (which are still great libraries!!). EDIT: The big downside of SQLite is that it's unsuitable for documents that are exposed to the user because of the temporary files (like the WAL). If you have a ZIP based file format that you atomically rewrite from scratch on every save, it's almost impossible to corrupt. Your users can just take the file and email it and nothing bad will happen. I'm not sure what happens if you email an SQLite database file that is currently being used. I've done that in the past and have been surprised that some data seemed to be missing, but I don't recall the details. Hence SQLite is often used for application data files that are not directly exposed to the user.

slaymaker1907 6y ago
You could get SQLite to work as document files exposed to the user so long as you use sessions[1]. When a file is opened, copy the DB to a temporary file or to use memory and write all changes during operation to this new DB, recording them all in a session. When the user explicitly saves a document, apply the session to the real DB. [1]: https://www.sqlite.org/sessionintro.html#:~:text=1%20Introduction%20The%20session%20extension%20provide%20a%20mechanism,the%20sessions%20extension.%203.1.%20...%204%20Extended%20Functionality https://www.sqlite.org/sessionintro.html#:~:text=1%20Introdu...
- yarcob 6y ago

dragonwriter 6y ago

> Any thoughts from the other end, where the alternatives are JSON or XML or ZIP? ZIP isn’t a format alternative, its just a compression and/or packaging technique for files which you still need to choose a format for. JSON/YAML/XML are great for input and output formats, but not great for continuous, random read/write access.

millstone 6y ago
My understanding is that SQLite doesn't impose any format either?
- deleted 6y ago
  [deleted]
- deleted 6y ago
  [deleted]
- mb7733 6y ago
  I'm genuinely curious what you mean by this. Of course SQLite imposes a file format... That format is a SQLite database
  - Dylan16807 6y ago
    > Of course SQLite imposes a file format... That format is a SQLite database Well this is in a context that rejects zip as being a format. Do you do that? If the answer is no then skip the rest of my post and just note that they're talking about a different definition of 'format'. - But in that context: The amount of structure imposed on you by the sqlite database format is not much more than the structure imposed on you by a zip. I think it's fair to rate them similarly as formats. A zip file is basically a key-value store. "Zip full of csvs", while awful to use, would impose about the same amount of structure as sqlite does: not much. And zip+csv is not much more elaborate than zip on its own.
    - samatman 6y ago

imtringued 6y ago

During the alpha Minecraft divided the world into 16x16x128 grids of blocks called chunks. Each chunk was its own file. Large worlds suffered from very poor performance because there were tens of thousands of files in a single folder. Some random modder basically just put multiple chunks into one file so that each file is 2MB. If Notch had just put the game world into a SQLite database he wouldn't have had to reinvent the wheel. There are games that did that, such as the alpha of Cube World and they work just fine. Heck, notch went one step further and invented NBT aka named binary tag which is basically a weirdo binary file format that stores JSON like data.

iforgotpassword 6y ago
> Large worlds suffered from very poor performance because there were tens of thousands of files in a single folder. It was using subdirs for the chunks, two levels iirc, one was chunkX % 36, the next level chunkY % 36. So there weren't that many files per directory. The slowness came from the overhead of opening, read/write and closing so many files all the time. > Some random modder basically just put multiple chunks into one file so that each file is 2MB. Almost, it wasn't limited by file size, it was putting 32*32 chunks into one file that was similar to a simple file system. The format of the individual chunks within that file stayed almost the same. Yet it performed much better. NBT is indeed a little weird but fairly straight forward overall, I guess designing and implementing it just scratched an itch. It was a hobby project after all.
slaymaker1907 6y ago
I'm actually currently working on a user mode FS using Dokan for Windows that saves everything to a SQLite file for similar reasons. NTFS just doesn't do well at all with lots of small files.
- fomine3 6y ago
  Interesting. Did you tested any performance by non-NTFS? I remembered this post. https://github.com/microsoft/WSL/issues/873#issuecomment-425272829 https://github.com/microsoft/WSL/issues/873#issuecomment-425...

kstrauser 6y ago

There's an enormous amount of comments and tests in that codebase. As installed on my Mac, sqlite comprises a 1.3MB command line utility and a 1MB shared library. That's absolutely tiny given the functionality it provides.