[simonw]

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]

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]

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

[eviks]

Except the markdown files are tied to a vendor outside of trivial formatting since it's a simplistic underpecified format without extensions, so all these obsidians specify their own extensions to add complexity, which your vscode does not support (and neither would git diff help to see data change in a sea of formatting changes)

And this spec is for complicated layouts, not trivial notes you're comparing it to, so your intuition from simple notes doesn't translate to this use case

[asa400]

> 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.

I'm not sure I understand. Can you clarify?

> 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.

For a graph with lots (thousands+) of nodes/edges, SQLite is probably capable of being more performant than a JSON file, depending on whatever specific kind of performance we're measuring. That said, to me, the most interesting thing that SQLite gives for applications when compared to flat files is data integrity, via transactions and schemas/constraints. Performance is nice but not even close to the most interesting thing about SQLite in most applications. Performance has never been the reason I've chosen SQLite over flat files for my applications.

> 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

In a sense you're right about this! I'll grant it's easier to open a JSON file in vscode and edit it if you already know vscode and JSON. That said, SQLite is in the public domain with a well-defined, stable format and there are countless free and open source database editors/viewers out there.

SQLite is self-describing, also. You open `sqlite3` and type `.schema` and it shows you the database schema. You enter a query and get some results. It's all right there. So, while a database might have a schema that was designed for a particular application, that doesn't mean you as the end user can't tinker with it, and the number of people who know SQL is rather large.

[pastage]

> SQLite is self-describing, also. You open `sqlite3` and type .schema

My feeling is that xml and json is always easier to parse than SQL. What I often see is that a large amount of application logic is hidden in how the tables are joined or selected. In SQL this is almost always the case and of course this is often the case in json as well to some extent but usually alot less.

In the end it comes down to that xml was not the end all of integration formats, neither are its replacements. Actually being able to read and understand what you parse without tooling is an immense help.