Linux

Is there a reason you're not looking at tools explicitly built for this like orgmode, obsidian, task-warrior, etc? There's a plethora of these tools and my experience with this is you really don't want to over-engineer your productivity suite.

That said, if you go the SQL route, sqlite is the way to go. Other SQL databases must be run as a daemon whereas sqlite operates on a local file directly.

However any SQL database isnt going to have the CLI youre asking for. Its interface is... SQL, so you're scripts are going to have a bunch of SQL code embedded that isnt easily reusable. A non-sql database will probably be better. I'm not familiar with them but I think there's some that store their data as text files in a folder which is organized a certain way. But that starts looking like the tools I mentioned before.

Check out https://www.giuspen.net/cherrytree/, lightweight note-taking app with interesting scripting function built in.

Even if that's not your cup of tea, it has the option to save your notebook to a single sqlite file, so I take that as good enough proof it'll work for your similar purposes as well.

Counter-proposal: Perhaps use Obsidian instead?

@matcha_addict Anything beyond SQLite is too heavy, but if it were me I would use a lightweight wiki like dokuwiki. Having to run SQL to do all these sorts of things sounds like it would just get in the way of both getting and consuming your thoughts.

If you want something a bit more powerful than SQLite, MySQL and PostgreSQL both support CLI interactions and scripting too.

Definitely sounds like sqlite.