"Custom" would be the way Apple Notes or Evernote stores their data in opaque binary blobs. SQLite is standard and you can open it with plenty of third-party tools, but I see your point about plain text files being more convenient for certain tasks.
Tends not to play well with things like Dropbox or Syncthing though. Although I guess you can use litestream or something for the replication instead.
Both sync. You now have a conflict.
With text files, you at least have obvious merge strategies. With SQLite it isn't quite as easy.
I still don't mind SQLite in Joplin. It has advantages, as well.
See trilium: https://github.com/zadam/trilium
I was wondering though, since my Nextcloud storage is encrypted, does the SQLite file get synced as well?
Apple Notes uses SQLite.