4 ms·

I've worked a few jobs now where application data was stored in text files of various kinds (homegrown as well as well-defined formats, JSON included) and it pr

by asa400 3y ago

I've worked a few jobs now where application data was stored in text files of various kinds (homegrown as well as well-defined formats, JSON included) and it pretty quickly becomes a mess when you start talking about modifying it over time, evolving its schema, validating it in the face of end-user edits, ensuring threads don't write to it concurrently, etc. This strikes me as exactly the type of application data that would benefit from being represented in SQLite. Of course, JSON is a `JSON.parse` away, but now you're building your own...everything else. Storage/retrieval, validation, querying/filtering/indexing, graph traversal, etc. It's all yours. There's so many benefits to building this kind of thing in SQLite. You get data validation, atomic transactions in memory and on disk, a high-level query interface, lazy loading (i.e., only load nodes at most 2 edges away), triggers (when I delete this node, automatically delete edges that point to it), and a stable on-disk format, to say nothing of the fact that SQLite itself is just about the most stable software there is. By the way, no disrespect to JSON Canvas, it looks like good work, just trying to offer the perspective of someone who has done stuff like this in the past.

simonw 3y ago
I'm usually the first person to suggest SQLite for just about anything, but in this particular case I do feel like JSON is a better default format. Interacting with SQLite from different programming languages is easier than most other formats, but you still need a SQLite binding. They're available for every language but that's still a not-completely-trivial dependency. I expect most tools that people build against JSON Canvas will run in a web browser. Adding SQLite as a dependency means you need SQLite running in WebAssembly - totally possible, and even officially supported these days (the SQLite team run their own WASM builds now) but still a sizable piece of extra complexity over just using JSON.parse(...) Also: SQLite files aren't very easy to diff, so they're not great for collaboration in version control. JSON is better for that. I'm 100% with you on the schema changes and versioning challenge. The best way to address this IMO would be for the spec to include a top-level "version": key which indicates the version of the spec that a file was created against. Handled carefully and introduced right at the start of the project this could ensure an ecosystem grows up around the standard such that older spec versions can always be opened by newer implementations, and any implementation can fail-fast if it is given a file that it doesn't yet know how to handle.
- asa400 3y ago
  Valid points and I generally agree! I think you're probably right that the SQLite dependency is possibly too much for some applications to pull in, though I will ask why we're so often willing to pull in more or less anything else regardless of weight (DOM manipulation, state management, animation, etc.) but a robust data layer is often a bridge too far. I wish I knew why this is, as it seems to be one of the larger cultural differences between folks who work mainly "on the backend" and "on the frontend". > Also: SQLite files aren't very easy to diff, so they're not great for collaboration in version control. JSON is better for that. Yeah, if we're talking about diffing the literal file itself, then that changes things. At that point, we're not just talking about a storage format, we're talking about interchange as well. In that case I'd ask - of course this is application specific, not general - what data are we putting on the wire? Where does that data live? For example, if the main state state lives in a your browser instance, and you ship updates (i.e., "CREATE", "EDIT", "DELETE" or some such) back and forth between collaborators, then diffing the state of whatever you have is fairly easy, SQLite or JSON or whatever else. But if we're shipping the actual file itself over the wire or attempting to version control it, then you're absolutely right and diffing the SQLite file is inferior. There are some interesting tradeoffs in this space. This is a fun discussion!
  - desmondl 3y ago
    I think you're talking about the trade offs between supporting features like "DOM manipulation, state management, animation, etc." and "shipping updates" out of the box, versus only storing the data as simple files and leaving everything else to the implementation. Sqlite as an application file format is great [1], but for a knowledge base / note taking app the benefits are not worth the tradeoffs in my opinion. Sqlite is more performant more performant and provides lots of built-in features. However, most note taking users do not have enough notes or files to benefit from that performance. Sqlite will also lock the user into the application, whereas a "pack of files" can be used in the shell as a text editor. Using markdown files + a open json format has the benefit of being supported by multiple applications (e.g. sometimes i open my obsidian vault in vscode), while a sqlite database would need a proprietary schema coupled with a single application I prefer an open file format that isn't tied to a vendor. A "data bridge" might handle syncing and diffing more efficiently than plain files, but it is still tied to the vendor. For example, I prefer not to pay for Obsidian Sync, and I'm able to use a git plugin and storing my files on nextcloud to sync between my devices. This leverages existing tech without having to implement features from the ground up [1] https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html
chrisweekly 3y ago
Crucial context: its provenance is embedded infinite canvas in Obsidian (amazing markdown-based notes app++), which supports JS but has no external datastore per se. SQLite is fantastic, but inappropriate for this use case.
- asa400 3y ago
  Oh I read the post and a few of the other linked posts so I'm aware. If we're storing a file, we're already storing a file of some kind, so we're just talking about what kind of file it is. I'm just talking about a different set of tradeoffs one can make if that file is something other than JSON.
  - LordDragonfang 3y ago
    >If we're storing a file, we're already storing a file of some kind It's not just a file "of some kind", it's a text file. That's one of Obsidian's key selling points - that a vault is simply a collection of text files. The "text" part is important to both Obsidian's philosophy and the majority of its users.
    - asa400 3y ago
      Sure! And in a lot of ways this is about values more than technology.
toddmorey 3y ago
Agree that SQLite is a great local format for this sort of thing, but seems to get tricky when trying to sync across clients without conflicts. I've seen most CDRT schemes working with json documents rather than SQLite, but curious if there's solid conflict-free syncing out there for SQLite files being used inside applications?
apitman 3y ago
SQLite is awesome, but it's still an order of magnitude more complex than JSON. If it wants to be the one file format to rule them all, we're going to need high quality and heavily used implementations in most languages. Adding C sqlite to a golang project adds a significant hit to build times and cross-compilation/static linking complexity[0]. When I looked into the native Go implementations of sqlite I came away with the feeling it wasn't worth the tradeoffs compared to using the C version, but now I still have to deal with the issues above. I haven't looked deeply into how sqlite works, but my instincts tell me the reason we don't have high quality implementations in every language is because it's actually too complex to treat as a protocol. I would love to see something fill the void between plain text and sqlite. [0]: https://www.arp242.net/static-go.html https://www.arp242.net/static-go.html
- mlunar 3y ago
  Check out https://github.com/zombiezen/go-sqlite https://github.com/zombiezen/go-sqlite if you're interested in trying out Sqlite in Go again. Nice interface, negligible compile time impact, fast, compiles without CGO. It's very comfortable. I agree that going from text to sqlite is a bit of a hurdle, especially if you're not writing C :)
exceptione 3y ago
SQLite has no real types. Use SQLite if data quality is of no concern. The applicability domain of SQLite is far smaller than people think. If you want to improve on JSON, you would have to go into an other direction. Maybe something like postgis would be helpful for extremely large canvasses. JSON Schema is pretty powerful by the way. Checkout the documentation. SQLite is absolutely no match there. What Sqlite could bring is speed, but I dont see how in the contxt of canvas it would be of any help here.
- asa400 3y ago
  SQLite's types are quite loose by default, no doubt. There is a somewhat recent STRICT mode that strengthens them: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
  - samatman 3y ago
    Exactly so. SQLite also has a rich collection of CHECK constraints which can raise errors if data is not to your liking in some fashion, this includes validating JSON. Not a JSON schema, admittedly, although (just like for Postgres) this is available as an extension. https://github.com/asg017/sqlite-jsonschema https://github.com/asg017/sqlite-jsonschema Saying that SQLite doesn't have "real types" is simply false. If one doesn't want to learn how to use a tool, blaming it for that failure is poor form.
    - exceptione 3y ago
      The author of SQLite is quite open about it. The lack of typing has been part of the design from the beginning. Sqlite has hardly any types: INT INTEGER REAL TEXT BLOB ANY Of course one can program all kind of check constraints, like one can program all kinds of value validations in javascript. Unfortunately, that is not the same as typing. Sqlite lacks typing because, as the sqlite author explains in the docs, flexibility is the goal. He continues with "But other developers are aghast", and so strict tables where born, but you can clearly see this cannot overcome real concerns. Try to look for the DATETIME datetype in that list. Deep bow to sqlite, its design goal was to be the ini file replacement and it has outperformed itself on that one. Thanks for the extension link. Although constraints are not reuable type definitions, they would still be helpful in this context. Pity that json doesn't have a type for dates, one has to rely on string formats: https://json-schema.org/understanding-json-schema/reference/string#dates-and-times https://json-schema.org/understanding-json-schema/reference/...
mch82 3y ago
The SQLite docs have a great page on the use case `asa400` describes. SQLite as an Application File Format, https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html
- eviks 3y ago
  What's the best example of this format used for complex docs, has anyone implemented their OpenDocument replacement idea?
  - SQLite 3y ago
    One example: Fossil (https://fossil-scm.org https://fossil-scm.org), the version control system used by SQLite itself. Fossil is Git-like in its underlying design but has a different interface. Fossil stores a complete source code repository as an SQLite database, rather than a pile-of-files as Git does. Storing content this way gives Fossil UI advantages over Git, such as the ability to easily find decedents of a check-in, and the ability to assign the same tag to multiple check-ins (ex: tagging every release with the "release" tag.)
dolmen 3y ago
SQLite doesn't even have strict validation of columns declared as integers.

by asa400 3y ago

simonw 3y ago

I'm usually the first person to suggest SQLite for just about anything, but in this particular case I do feel like JSON is a better default format. Interacting with SQLite from different programming languages is easier than most other formats, but you still need a SQLite binding. They're available for every language but that's still a not-completely-trivial dependency. I expect most tools that people build against JSON Canvas will run in a web browser. Adding SQLite as a dependency means you need SQLite running in WebAssembly - totally possible, and even officially supported these days (the SQLite team run their own WASM builds now) but still a sizable piece of extra complexity over just using JSON.parse(...) Also: SQLite files aren't very easy to diff, so they're not great for collaboration in version control. JSON is better for that. I'm 100% with you on the schema changes and versioning challenge. The best way to address this IMO would be for the spec to include a top-level "version": key which indicates the version of the spec that a file was created against. Handled carefully and introduced right at the start of the project this could ensure an ecosystem grows up around the standard such that older spec versions can always be opened by newer implementations, and any implementation can fail-fast if it is given a file that it doesn't yet know how to handle.

asa400 3y ago
Valid points and I generally agree! I think you're probably right that the SQLite dependency is possibly too much for some applications to pull in, though I will ask why we're so often willing to pull in more or less anything else regardless of weight (DOM manipulation, state management, animation, etc.) but a robust data layer is often a bridge too far. I wish I knew why this is, as it seems to be one of the larger cultural differences between folks who work mainly "on the backend" and "on the frontend". > Also: SQLite files aren't very easy to diff, so they're not great for collaboration in version control. JSON is better for that. Yeah, if we're talking about diffing the literal file itself, then that changes things. At that point, we're not just talking about a storage format, we're talking about interchange as well. In that case I'd ask - of course this is application specific, not general - what data are we putting on the wire? Where does that data live? For example, if the main state state lives in a your browser instance, and you ship updates (i.e., "CREATE", "EDIT", "DELETE" or some such) back and forth between collaborators, then diffing the state of whatever you have is fairly easy, SQLite or JSON or whatever else. But if we're shipping the actual file itself over the wire or attempting to version control it, then you're absolutely right and diffing the SQLite file is inferior. There are some interesting tradeoffs in this space. This is a fun discussion!
- desmondl 3y ago
  I think you're talking about the trade offs between supporting features like "DOM manipulation, state management, animation, etc." and "shipping updates" out of the box, versus only storing the data as simple files and leaving everything else to the implementation. Sqlite as an application file format is great [1], but for a knowledge base / note taking app the benefits are not worth the tradeoffs in my opinion. Sqlite is more performant more performant and provides lots of built-in features. However, most note taking users do not have enough notes or files to benefit from that performance. Sqlite will also lock the user into the application, whereas a "pack of files" can be used in the shell as a text editor. Using markdown files + a open json format has the benefit of being supported by multiple applications (e.g. sometimes i open my obsidian vault in vscode), while a sqlite database would need a proprietary schema coupled with a single application I prefer an open file format that isn't tied to a vendor. A "data bridge" might handle syncing and diffing more efficiently than plain files, but it is still tied to the vendor. For example, I prefer not to pay for Obsidian Sync, and I'm able to use a git plugin and storing my files on nextcloud to sync between my devices. This leverages existing tech without having to implement features from the ground up [1] https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html

chrisweekly 3y ago

Crucial context: its provenance is embedded infinite canvas in Obsidian (amazing markdown-based notes app++), which supports JS but has no external datastore per se. SQLite is fantastic, but inappropriate for this use case.

asa400 3y ago
Oh I read the post and a few of the other linked posts so I'm aware. If we're storing a file, we're already storing a file of some kind, so we're just talking about what kind of file it is. I'm just talking about a different set of tradeoffs one can make if that file is something other than JSON.
- LordDragonfang 3y ago
  >If we're storing a file, we're already storing a file of some kind It's not just a file "of some kind", it's a text file. That's one of Obsidian's key selling points - that a vault is simply a collection of text files. The "text" part is important to both Obsidian's philosophy and the majority of its users.
  - asa400 3y ago
    Sure! And in a lot of ways this is about values more than technology.

toddmorey 3y ago

Agree that SQLite is a great local format for this sort of thing, but seems to get tricky when trying to sync across clients without conflicts. I've seen most CDRT schemes working with json documents rather than SQLite, but curious if there's solid conflict-free syncing out there for SQLite files being used inside applications?

apitman 3y ago

SQLite is awesome, but it's still an order of magnitude more complex than JSON. If it wants to be the one file format to rule them all, we're going to need high quality and heavily used implementations in most languages. Adding C sqlite to a golang project adds a significant hit to build times and cross-compilation/static linking complexity[0]. When I looked into the native Go implementations of sqlite I came away with the feeling it wasn't worth the tradeoffs compared to using the C version, but now I still have to deal with the issues above. I haven't looked deeply into how sqlite works, but my instincts tell me the reason we don't have high quality implementations in every language is because it's actually too complex to treat as a protocol. I would love to see something fill the void between plain text and sqlite. [0]: https://www.arp242.net/static-go.html https://www.arp242.net/static-go.html

mlunar 3y ago
Check out https://github.com/zombiezen/go-sqlite https://github.com/zombiezen/go-sqlite if you're interested in trying out Sqlite in Go again. Nice interface, negligible compile time impact, fast, compiles without CGO. It's very comfortable. I agree that going from text to sqlite is a bit of a hurdle, especially if you're not writing C :)

exceptione 3y ago

SQLite has no real types. Use SQLite if data quality is of no concern. The applicability domain of SQLite is far smaller than people think. If you want to improve on JSON, you would have to go into an other direction. Maybe something like postgis would be helpful for extremely large canvasses. JSON Schema is pretty powerful by the way. Checkout the documentation. SQLite is absolutely no match there. What Sqlite could bring is speed, but I dont see how in the contxt of canvas it would be of any help here.

asa400 3y ago
SQLite's types are quite loose by default, no doubt. There is a somewhat recent STRICT mode that strengthens them: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- samatman 3y ago
  Exactly so. SQLite also has a rich collection of CHECK constraints which can raise errors if data is not to your liking in some fashion, this includes validating JSON. Not a JSON schema, admittedly, although (just like for Postgres) this is available as an extension. https://github.com/asg017/sqlite-jsonschema https://github.com/asg017/sqlite-jsonschema Saying that SQLite doesn't have "real types" is simply false. If one doesn't want to learn how to use a tool, blaming it for that failure is poor form.
  - exceptione 3y ago
    The author of SQLite is quite open about it. The lack of typing has been part of the design from the beginning. Sqlite has hardly any types: INT INTEGER REAL TEXT BLOB ANY Of course one can program all kind of check constraints, like one can program all kinds of value validations in javascript. Unfortunately, that is not the same as typing. Sqlite lacks typing because, as the sqlite author explains in the docs, flexibility is the goal. He continues with "But other developers are aghast", and so strict tables where born, but you can clearly see this cannot overcome real concerns. Try to look for the DATETIME datetype in that list. Deep bow to sqlite, its design goal was to be the ini file replacement and it has outperformed itself on that one. Thanks for the extension link. Although constraints are not reuable type definitions, they would still be helpful in this context. Pity that json doesn't have a type for dates, one has to rely on string formats: https://json-schema.org/understanding-json-schema/reference/string#dates-and-times https://json-schema.org/understanding-json-schema/reference/...

mch82 3y ago

The SQLite docs have a great page on the use case `asa400` describes. SQLite as an Application File Format, https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html

eviks 3y ago
What's the best example of this format used for complex docs, has anyone implemented their OpenDocument replacement idea?
- SQLite 3y ago
  One example: Fossil (https://fossil-scm.org https://fossil-scm.org), the version control system used by SQLite itself. Fossil is Git-like in its underlying design but has a different interface. Fossil stores a complete source code repository as an SQLite database, rather than a pile-of-files as Git does. Storing content this way gives Fossil UI advantages over Git, such as the ability to easily find decedents of a check-in, and the ability to assign the same tag to multiple check-ins (ex: tagging every release with the "release" tag.)

dolmen 3y ago

SQLite doesn't even have strict validation of columns declared as integers.